随笔分类 -  mysql从入门到入焚

摘要:insert … select 是很常见的在两个表之间拷贝数据的方法。你需要注意,在可重复读隔离级别下,这个语句会给 select 的表里扫描到的记录和间隙加读锁。而如果 insert 和 select 的对象是同一个表,则有可能会造成循环写入。这种情况下,我们需要引入用户临时表来做优化。insert 语句如果出现唯一键冲突,会在冲突的唯一值上加共享的 next-key lock(S 锁)。因此,碰到由于唯一键约束导致报错后,要尽快提交或回滚事务,避免加锁时间过长。 阅读全文
posted @ 2024-12-01 21:04 guixiang 阅读(77) 评论(0) 推荐(0) 编辑
摘要:你现在可以不懂,但以后面试的时候,必须要知道的三个关于自增主键的点 第一:唯一键冲突和事务回滚是导致自增主键不连续的两种大原因,此外批量插入数据的语句,MySQL 批量申请自增 id 的策略也是一个隐藏原因 第二:MySQL设计中不允许自增值回退的原因,主要是为了提升性能还有数据准确性。强调了自增主键保证递增但不保证连续的特点。 数据准确性在于: 如果是高并发环境,a和b自增主键操作的好好的,a突然回滚,自增主键就会被打乱,如果允许自增值回退,那就要花更多的性能在排自增主键上(详细见课件),那不允许自增值回退,那不就相当于提升性能吗? 提升性能也在于: MySQL 有一个批量申请自增 id 的策略,不可能你插十万个数据,都要申请十万次自增id 第三点 MySQL在 5.7 及之前的版本,自增值保存在内存里,并没有持久化。每次重启后,第一次打开表的时候,都会去找自增值的最大值 max(id),然后将 max(id)+1 作为这个表当前的自增值。 阅读全文
posted @ 2024-11-29 21:55 guixiang 阅读(63) 评论(0) 推荐(0) 编辑
摘要:内存引擎和InnoDB引擎在数据组织方式上存在显著差异。InnoDB引擎将数据存储在主键索引上,而内存引擎则将数据和索引分开存放。导致了内存表的数据是按照写入顺序存放的,而InnoDB表的数据总是有序存放的。此外,内存表不支持行锁,只支持表锁,这会影响并发访问的性能。尽管内存引擎速度快且支持hash索引,但在生产环境中使用时需要注意锁粒度问题和数据持久化问题。因此,尽管内存引擎有其优势,但在生产环境中的使用需要谨慎考虑。 在高可用架构下,内存表的特性可能导致主备同步停止,甚至主库的内存表数据被意外删除。因此,普通内存表并不适合在生产环境中使用,而推荐使用InnoDB表代替。然而,内存临时表在数据量可控的情况下可以考虑使用,因为它不会受到主备同步和数据持久化的影响。 总的来说,内存表的特性决定了它并不适合作为普通数据表在生产环境中使用。在实际应用中,需要根据具体场景和需求来选择合适的存储引擎,以确保数据安全和性能优化。 阅读全文
posted @ 2024-11-22 21:53 guixiang 阅读(41) 评论(0) 推荐(0) 编辑
摘要:就两个点 在union语句中,内部临时表用于暂存数据并执行两个子查询的并集 第二:强调了通过索引优化group by语句的执行效率 如何group by优化查询? 1.如果没有排序要求,要在语句后面加 order by null; 2.尽量让 group by 过程用上表的索引 3.如果 group by 需要统计的数据量不大,尽量只使用内存临时表,避免用到磁盘临时表; 4.如果数据量实在太大,使用 SQL_BIG_RESULT 这个提示,来告诉优化器直接使用排序算法得到 group by 的结果。 阅读全文
posted @ 2024-11-16 21:07 guixiang 阅读(58) 评论(0) 推荐(0) 编辑
摘要:创建临时表,一部分为了优化查询,join在临时表里查询出结果后导入到正常表中,他也支持多session的查询优化,更重要一点是在session会话关闭后,临时表会自动销毁。嗯就这样 另外分清他和内存表的区别 内存表一定是从memory引擎创建的,临时表可以由memory引擎创建 阅读全文
posted @ 2024-11-06 19:33 guixiang 阅读(31) 评论(0) 推荐(0) 编辑
摘要:第三十四讲:join语句怎么优化? 简概: 万年不变的开头 ​ 在上一篇文章中,我和你介绍了 join 语句的两种算法,分别是 Index Nested-Loop Join(NLJ) 和 Block Nested-Loop Join(BNL)。我们发现在使用 NLJ 算法的时候,其实效果还是不错的, 阅读全文
posted @ 2024-11-05 20:28 guixiang 阅读(75) 评论(0) 推荐(0) 编辑
摘要:第三十三讲:到底可不可以使用join? 简概: 厌烦了平淡的开头 提出问题 ​ 在实际生产中,关于 join 语句使用的问题,一般会集中在以下两类: 我们 DBA 不让使用 join,使用 join 有什么问题呢? 如果有两个大小不同的表做 join,应该用哪个表做驱动表呢? 提出示例 ​ 今天这篇 阅读全文
posted @ 2024-11-04 20:52 guixiang 阅读(47) 评论(0) 推荐(0) 编辑
摘要:我的服务器数据库内存还有4个G,里面有8个G的表,现在,我要让我的客户机远程连接服务器后对数据库进行一次全表扫描,请问,我的服务器会不会因为内存被8个G的表塞得溢出而崩溃? 面试关如实问到 在我阅读这篇文章后,我会这么跟面试官说: 第一,内存不会打满,除非socket send buffer设置超过4G才有可能 第二,之所以不会,是因为mysql采用一边帮你查询,一边帮你把查询数据发给客户端的模式 但如果客户端接受查询数据特别慢,那数据就会滞留在服务器上,直到达到滞留上限(上限就是socket send buffer的值),这会极大影响服务器性能 你怎么确定服务端的数据是否滞留呢? 面试官问: 答案 show processlist; 可以查看线程状态,如果处于:“sending to client”,那就说明服务端处于滞留状态 阅读全文
posted @ 2024-11-03 19:22 guixiang 阅读(34) 评论(0) 推荐(0) 编辑
摘要:在一个阳光洒满教室的温暖午后,hr隔着腾讯会议的屏幕目光如炬得看着你,大喝一声,你在mysql实验中有没有遇到sql语句kill不了的情况,所! 每一个sql语句的线程执行都有一个或多个“埋雷点”,kill query或kill 线程id都是向这个埋雷点发信号激活,所以,当这个sql语句执行到被激活的埋雷点时,就会自动销毁 所以说,之所以kill不掉语句,基本就是因为sql线程始终触发不了“已经激活的埋雷点” kill 并不是马上停止的意思,而是告诉执行线程说,这条语句已经不需要继续执行了,可以开始“执行停止的逻辑了” 阅读全文
posted @ 2024-11-02 15:35 guixiang 阅读(41) 评论(0) 推荐(0) 编辑
摘要:hr问你:如果,我是说如果 有一天,你删了公司重要数据,你会怎么做? 我们分情况讨论就是 第一种:类似delete,update,insert修改,我会用Flashback工具回滚,如果多行要注意回滚逆序 第二种:删库,表 必须会用到“全量备份”!!!还有实时的binlog备份 大概就是全量备份-》临时库 经过修改误操作处理过的binglog-》临时库=恢复完成! 为了提高库恢复的时间,我会用到延时库,如果我设置延时10分钟,那我在误操作后的十分钟内都可以修改回来 为了预防误删库的一个情况出现,我建议还是账号分离比如只给业务开发同学 DML 权限,而不给 truncate/drop 权限,DBA 团队成员规定日常只使用只读账号 值得一提的是:预防远比处理的意义来得大! 阅读全文
posted @ 2024-10-30 17:36 guixiang 阅读(85) 评论(0) 推荐(0) 编辑
摘要:每次翻看前几天过完的章节,都会有新的感触与收获 mysql加锁规则:包含了两个“原则”、两个“优化”和一个“bug” 提炼文章精华,我看了这么多遍,凭心而言,最重要的是加锁规则,这是面试会问到的,还要了解加锁范围,这是分析死锁的切入点之一,还要了解show engine innodb status ,因为这是你在发生死锁事件后分析缘由的重要手段 但遗憾的是,面对死锁,我也就只能分析原因,除了回滚事务之外,我想不到其他办法,emmmm 阅读全文
posted @ 2024-10-10 21:47 guixiang 阅读(18) 评论(0) 推荐(0) 编辑
摘要:今天,hr见到你简历上写过“搭建过mysql主从,有使用的经验” 突然兴致来了一句“你在搭建的时候,如何判断要主备库切换,说一下你的理解” 我重生了,回到了面试的前一分钟,上一世,我没能回答这个问题,这一世,我将。。。。(编不下去了) 哎,你可以说用select 1 判断,但他成功返回并不能说明主库没问题,只是说这个线程还在 你也可以额外建一个表,为避免单行多库热点更新导致binlog混乱,你选择一库更新固定一行的方式,但他毕竟是外部统计的方式,可能因为update耗IO资源少而被优先执行,导致多轮轮询下来未能及时主备切换 再一种就是内部统计的方式使用performance_schema库统计IO请求时间 但它耗性能,约10%。 select 1 这样的方法是不是已经被淘汰了呢,但实际上使用非常广泛的 MHA(Master High Availability),默认使用的就是这个方法。 阅读全文
posted @ 2024-10-09 15:59 guixiang 阅读(80) 评论(0) 推荐(0) 编辑
摘要:今年秋招,面试官隔着电脑屏幕看着简历上“熟悉搭建过mysql集群,能排错” 对你说:在mysql集群中,一般是一主多从的方式,即一台mysql机器做公司业务的读,其他机器留给客户查询做负载均衡。 hr问你:“老板开了一家金融公司,他要求客户在频繁资金流动下,时刻要保证拿到最新数据,你也知道,mysql数据库是存在延迟的:主库更新后,从库数据要等一段时间才会改动,如果是你,你该怎么满足客户需求” 阅读全文
posted @ 2024-09-29 21:09 guixiang 阅读(55) 评论(1) 推荐(0) 编辑
摘要:文章介绍了两种主备切换方式,并讨论了主库宕机后,备库接手,从库在binlog从哪里开始同步备库:一种基于位点的主备切换,一种基于GTID的切换 这篇文章最为重要的是介绍了一种业务突发情况 就是x库有了(1,1),x是y库的从库,y现在插入了(1,1),括号内左边的1是主键,这个时候系统报主键错误,你要怎么解决? 你要做的是找到Y插入(1,1)的GTID 命令: show master status\G 或 show binlog events in 'mysql-bin.0000**'; 接下来执行 set gtid_next='GTID编号'; begin; commit; set gtid_next=automatic; start slave; 简直不要太完美 阅读全文
posted @ 2024-09-28 20:20 guixiang 阅读(21) 评论(0) 推荐(0) 编辑
摘要:这篇文章分析备库延迟的原因:执行日志的速度持续低于主库生成日志的速度 分析了很多版本备库并行复制的策略,就是为了5.7版本做铺垫,只要知道5.7.22并行复制的三个策略COMMIT_ORDER(只有在多线程模式下,prepare后即可并发。无单线程优势),WRITESET(计算事务每行hash 值,组成集合 没操作相同的行,就并行。单线程优势),WRITESET_SESSION(在前面writest多了一个约束,使主备执行顺序相同,单线程压力模式下退化成单线程复制,感觉只有特定情况才用它) 阅读全文
posted @ 2024-09-25 21:17 guixiang 阅读(16) 评论(0) 推荐(0) 编辑
摘要:为了让各位更好的了解文章,我归纳了下面几点最重要的: 1、MySQL 高可用系统的可用性,是依赖于主备延迟的。延迟的时间越小,主库故障的时候,服务恢复需要的时间就越短,可用性就越高。 2、主备延迟原因:备库用的机子不行(IOPS是和主库相同的,不要轻视备库)、备库压力太大,查询消耗了大量cpu(因为主库直接影响业务,大家用的克制,懂的都懂)、大事务(要是一个事务在主库执行10分钟,在备库那也得执行个10来分钟,这延迟不就来了) 3、主库备库切换策略有两个:可靠性(在切换中,主库备库将处于readonly状态,事务写不进去),可用性(直接主库备库秒切,但十分容易数据错乱,建议binglog用row格式) 哦,还有一个最重要的参数 命令:show slave status 你要看seconds_behind_master 的值,他是主备库的延迟时间,地位在高可用里面相当的重要 阅读全文
posted @ 2024-09-24 21:20 guixiang 阅读(41) 评论(0) 推荐(0) 编辑
摘要:第二十三讲:MySQL是怎么保证主备一致的? 简概 开篇 ​ 在前面的文章中,我不止一次地和你提到了 binlog,大家知道 binlog 可以用来归档,也可以用来做主备同步,但它的内容是什么样的呢?为什么备库执行了 binlog 就可以跟主库保持一致了呢?今天我就正式地和你介绍一下它。 毫不夸张地 阅读全文
posted @ 2024-09-23 20:49 guixiang 阅读(23) 评论(0) 推荐(0) 编辑
摘要:第二十二讲:MySQL是怎么保证数据不丢的? 简概 开篇 ​ 今天这篇文章,我会继续和你介绍在业务高峰期临时提升性能的方法。从文章标题“MySQL 是怎么保证数据不丢的?”,你就可以看出来,今天我和你介绍的方法,跟数据的可靠性有关。 在专栏前面文章和答疑篇中,我都着重介绍了 WAL 机制(你可以再回 阅读全文
posted @ 2024-09-22 21:00 guixiang 阅读(39) 评论(0) 推荐(0) 编辑
摘要:文章提出了数据库在高并发高请求的情况下出现的一些性能问题,这里警示DB人员饮鸩止渴提高性能操作:应对短连接风暴,切掉空闲进程(部分事务回滚),max connect参数(过高有损性能),还有SQL语句本身原因,正如之前讨论过的索引:没设计、选错了、有,但没用上,以及应对QPS(单位时间查询)过高必须下掉功能时:白名单,用户删除,语句重写(要分环境,看公司运维规范程度) 阅读全文
posted @ 2024-09-20 19:44 guixiang 阅读(16) 评论(0) 推荐(0) 编辑
摘要:该文章深刻揭示了一点:加索引=行锁+间隙锁=(next-key lock),分析了加锁的规则:对主键(唯一索引),普通非唯一索引进行等值与范围查询的加锁。这篇文章我认为收获最大的是让我们知道“明明对一行加了锁,为什么在他相邻部分,或是相相邻部分无法插入数据(这根主键类型,是单个还是范围查询有关,就算他的逻辑是一样的)”,值的一提的是事务的for update更新与delete加锁规则是一样的,这意味着在你在大量插入的业务下删除数据可能会导致“数据insert失败”,解决方式是加limit,减少间隙锁的范围。 阅读全文
posted @ 2024-09-14 22:02 guixiang 阅读(27) 评论(0) 推荐(0) 编辑

点击右上角即可分享
微信分享提示