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