MySQL常见问题总结（MySQL实战笔记）

09 | 普通索引和唯一索引，应该怎么选择？
由于唯一索引用不上 change buffer 的优化机制（），因此如果业务可以接受（业务保证插入数据的唯一性），从性能角度出
发我建议你优先考虑普通索引

补充：change buffer 的优化机制
当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下InooDB 会将这些更新操作缓存在 changebuffer 中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。需要说明的是，虽然名字叫作 change buffer，实际上它是可以持久化的数据。也就是说，change buffer 在内存中有拷贝，也会被写入到磁盘上。

redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写），而 change buffer 主要节省的则是随机读磁盘的 IO 消耗。

10 | MySQL为什么有时候会选错索引？
对于由于索引统计信息不准确导致的问题（采样统计——采样的随机性导致基数计算不准确），你可以用 analyze table 来解决。而对于其他优化器误判的情况，你可以在应用端用 force index 来强行指定索引，也可以通过修改语句来引导优化器，还可以通过增加或者删除索引来绕过这个问题。

11 | 字符串字段创建索引的场景。可以使用的方式有：

1. 直接创建完整索引，这样可能比较占用空间；
2. 创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引；
3. 倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；
4. 创建 hash 字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描。
   在实际应用中，你要根据业务字段的特点选择使用哪种方式。

12 | 为什么我的MySQL会“抖”一下？
无论是你的查询语句在需要内存的时候可能要求淘汰一个脏页，还是由于刷脏页的逻辑会占用 IO 资源并可能影响到了你的更新语句，都可能是造成你从业务端感知到MySQL“抖”了一下的原因。要尽量避免这种情况，你就要合理地设置 innodb_io_capacity 的值，并且平时要多关注脏页比例，不要让它经常接近 75%。

13 | 为什么表数据删掉一半，表文件大小不变？
innodb_file_per_table 设置为 ON 每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中。delete 命令其实只是把记录的位置，或者数据页标记为了“可复用”，但磁盘文件的大小是不会变的。也就是说，通过 delete 命令是不能回收表空间的。这些可以复用，而没有被使用的空间，看起来就像是“空洞”。经过大量增删改的表，都是可能是存在空洞的。所以，如果能够把这些空洞去掉，就能达到收缩表空间的目的。而重建表，就可以达到这样的目的。

重建表的两种实现方式：
1、Online DDL（在重建表的过程中，允许对表 A 做增删改操作） 的方式是可以考虑在业务低峰期使用的，而
MySQL 5.5 及之前的版本，这个命令是会阻塞 DML 的，这个你需要特别小心。
2、 inplace DDL 原地重建表

DDL 过程如果是 Online 的，就一定是 inplace 的；. 反过来未必，也就是说 inplace 的 DDL，有可能不是 Online 的。截止到 MySQL 8.0，添加全文索引（FULLTEXT index）和空间索引 (SPATIAL index) 就属于这种情况。

（我们在删除整个表的时候，可以使用 drop table 命令回收表空间。）

14 | count(*)这么慢，我该怎么办？

count(*)为什么这么慢？
MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count() 的时候会直接返回这个数，效率很高；而 InnoDB 引擎就麻烦了，它执行 count() 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。这就是当你的记录数越来越多的时候，计算一个表的总行数会越来越慢的原因。

小结：MyISAM 表虽然 count() 很快，但是不支持事务；
show table status 命令虽然返回很快，但是不准确；
InnoDB 表直接 count() 会遍历全表，虽然结果准确，但会导致性能问题。

怎么解决count(*)这么慢？
把计数放在 Redis 里面，不能够保证计数和 MySQL 表里的数据精确一致的原因，是这两个不同的存储构成的系统，不支持分布式事务，无法拿到精确一致的视图。而把计数值也放在 MySQL 中，就解决了一致性视图的问题。

16 | “order by”是怎么工作的？

全字段排序 VS rowid 排序
我们来分析一下，从这两个执行流程里，还能得出什么结论。如果 MySQL 实在是担心排序内存太小，会影响排序效率，才会采用 rowid 排序算法，这样排序过程中一次可以排序更多行，但是需要再回到原表去取数据。如果 MySQL 认为内存足够大，会优先选择全字段排序，把需要的字段都放到 sort_buffer中，这样排序后就会直接从内存里面返回查询结果了，不用再回到原表去取数据。

怎么避免排序？
联合索引
覆盖索引：索引上的信息足够满足查询请求，不需要再回到主键索引上去取数据。

17 | 如何正确地显示随机消息？
order by rand() 这种写法都会让计算过程非常复杂，需要大量的扫描行数，因此排序过程的资源消耗也会很大。
内存临时表：磁盘临时表：如果临时表大小超过了 tmp_table_size，那么内存临时表就会转成磁盘临时表。
补充：优先队列排序算法，可以避免使用磁盘临时表

18 | 为什么这些SQL语句逻辑相同，性能却差异巨大？
1、对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。
2、隐式类型转换
select * from tradelog where tradeid=110717; 等价于 select * from tradelog where CAST(tradid AS signed int) = 110717;//表中每条记录都要转换，需要全表扫描
//tradeid 的字段类型是 varchar(32)，而输入的参数却是整型，所以需要做类型转换。
而select * from tradelog where id=“83126”;//只需要对"83126"转成int类型，所以可以走主键索引
3、隐式字符编码转换

19 | 为什么我只查一行的语句，也执行这么慢？
第一类：查询长时间不返回
是首先执行一下 show processlist 命令，看看当前语句处于什么状态。
1、等 MDL 锁（元数据锁）：这个状态表示的是，现在有一个线程正在表 t 上请求或者持有 MDL 写锁，把 select语句堵住了。
2、等 flush
3、等 行锁

第二类：查询慢
select * from t where id=1；//一致性读，需要回滚到之前的状态
select * from t where id=1 lock in share mode；//当前读
补充：在 MySQL 5.5 版本中引入了 MDL（元数据锁），当对一个表做增删改查操作的时候，加 MDL
读锁；当要对表做结构变更操作的时候，加 MDL 写锁。
读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。
读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个
线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

20 | 幻读是什么，幻读有什么问题？
幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。
我需要对“幻读”做一个说明：

1. 在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在“当前读
   ”_select * from temp where id = 1 for update_下才会出现。
2. 上面 session B 的修改结果，被 session A 之后的 select 语句用“当前读”看到，不能称为幻读。幻读仅专指“新插入的行”。

幻读有什么问题？
首先是语义上的。其次，是数据一致性的问题。

如何解决幻读？
间隙锁，锁的就是两个值之间的空隙。间隙锁和 next-key lock（间隙锁+行锁）的引入，帮我们解决了幻读的问题，
但同时也带来了一些“困扰”。间隙锁的引入，可能会导致同样的语句锁住更大的范围，这其实是影响了并发度的。

21 | 为什么我只改一行的语句，锁这么多？
我总结的加锁规则里面，包含了两个“原则”、两个“优化”和一个“bug”。

1. 原则 1：加锁的基本单位是 next-key lock。希望你还记得，next-key lock 是前开后闭区间。
2. 原则 2：查找过程中访问到的对象才会加锁。
3. 优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
4. 优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，nextkey lock 退化为间隙锁。
5. 一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

22 | MySQL有哪些“饮鸩止渴”提高性能的方法？

短连接风暴
第一种方法：先处理掉那些占着连接但是不工作的线程。
第二种方法：减少连接过程的消耗。（有的业务代码会在短时间内先大量申请数据库连接做备用，如果现在数据库确认是被连接行
为打挂了，那么一种可能的做法，是让数据库跳过权限验证阶段。）

慢查询性能问题

1. 索引没有设计好；这种场景一般就是通过紧急创建索引来解决。最高效的做法就是直接执行 alter table 语句。
2. SQL 语句没写好；通过改写 SQL 语句来处理
3. MySQL 选错了索引。应急方案就是给这个语句加上 force index

QPS 突增问题
最理想的情况是让业务把这个功能下掉，服务自然就会恢复。
一种是由全新业务的 bug 导致的。假设你的 DB 运维是比较规范的，也就是说白名单是一个个加的。这种情况下，如果你能够确定业务方会下掉这个功能，只是时间上没那么快，那么就可以从数据库端直接把白名单去掉。如果这个新功能使用的是单独的数据库用户，可以用管理员账号把这个用户删掉，然后断开现有连接。这样，这个新功能的连接不成功，由它引发的 QPS 就会变成 0。如果这个新增的功能跟主体功能是部署在一起的，那么我们只能通过处理语句来限制。这时，我们可以使用上面提到的查询重写功能，把压力最大的 SQL 语句直接重写成"select 1"返回。（当然，这个操作的风险很高，需要你特别细致。）

它可能存在两个副作用：

1. 如果别的功能里面也用到了这个 SQL 语句模板，会有误伤；
2. 很多业务并不是靠这一个语句就能完成逻辑的，所以如果单独把这一个语句以 select 1的结果返回的话，可能会导致后面的业务逻辑一起失败。所以，方案 3 是用于止血的，跟前面提到的去掉权限验证一样，应该是你所有选项里优先级最低的一个方案。）

23 | MySQL是怎么保证数据不丢的？
WAL 机制 先写日志后写磁盘，redo log 和binlog

24 | MySQL是怎么保证主备一致的？
所以，MySQL 就取了个折中方案，也就是有了 mixed 格式的 binlog。mixed 格式的意思是，MySQL 自己会判断这条 SQL 语句是否可能引起主备不一致，如果有可能，就用row 格式，否则就用 statement 格式。

因为有些 statement 格式的 binlog 可能会导致主备不一致，所以要使用 row 格式。但 row 格式的缺点是，很占空间。比如你用一个 delete 语句删掉 10 万行数据，用statement 的话就是一个 SQL 语句被记录到 binlog 中，占用几十个字节的空间。但如果用 row 格式的 binlog，就要把这 10 万条记录都写到 binlog 中。这样做，不仅会占用更大的空间，同时写 binlog 也要耗费 IO 资源，影响执行速度。

用 binlog 来恢复数据的标准做法是，用 mysqlbinlog 工具解析出来，然后把解析结果整个发给 MySQL 执行。

循环复制问题？

1. 从节点 A 更新的事务，binlog 里面记的都是 A 的 server id；
2. 传到节点 B 执行一次以后，节点 B 生成的 binlog 的 server id 也是 A 的 server id；
3. 再传回给节点 A，A 判断到这个 server id 与自己的相同，就不会再处理这个日志。所以，死循环在这里就断掉了。

只要主库执行更新生成的所有 binlog，都可以传到备库并被正确地执行，备库就能达到跟主库一致的状态，这就是最终一致性。

25 | MySQL是怎么保证高可用的？
MySQL 高可用系统的基础，就是主备切换逻辑
主备延迟的来源
首先，有些部署条件下，备库所在机器的性能要比主库所在的机器性能差。
第二种常见的可能了，即备库的压力大。这种情况，我们一般可以这么处理：
1. 一主多从。除了备库外，可以多接几个从库，让这些从库来分担读的压力。
2. 通过 binlog 输出到外部系统，比如 Hadoop 这类系统，让外部系统提供统计类查询的能力。
第三种可能了，即大事务（一次性地用 delete 语句删除太多数据；大表 DDL，建议使用gh-ost方案）还有一个大方向的原因，备库的并行复制能力

由于主备延迟的存在，所以在主备切换的时候，就相应的有不同的策略。

可靠性优先策略

1. 判断备库 B 现在的 seconds_behind_master，如果小于某个值（比如 5 秒）继续下一步，否则持续重试这一步；
2. 把主库 A 改成只读状态，即把 readonly 设置为 true；
3. 判断备库 B 的 seconds_behind_master 的值，直到这个值变成 0 为止；
4. 把备库 B 改成可读写状态，也就是把 readonly 设置为 false；
5. 把业务请求切到备库 B。

可用性优先策略
如果我强行把步骤 4、5 调整到最开始执行，也就是说不等主备数据同步，直接把连接切到备库 B，并且让备库 B 可以读写，那么系统几乎就没有不可用时间了。我们把这个切换流程，暂时称作可用性优先流程。这个切换流程的代价，就是可能出现数据不一致的情况。

因此，大多数情况下，我都建议你使用可靠性优先策略。毕竟对数据服务来说的话，数据的可靠性一般还是要优于可用性的。

有没有哪种情况数据的可用性优先级更高呢？
我曾经碰到过这样的一个场景：有一个库的作用是记录操作日志。这时候，如果数据不一致可以通过 binlog 来修补，而这个短暂的不一致也不会引发业务问题。同时，业务系统依赖于这个日志写入逻辑，如果这个库不可写，会导致线上的业务操作无法执行。

26 | 备库为什么会延迟好几个小时？
如果备库执行日志的速度持续低于主库生成日志的速度，那这个延迟就有可能成了小
时级别。而且对于一个压力持续比较高的主库来说，备库很可能永远都追不上主库的节奏。

备库并行复制能力

MySQL 多线程复制

1. 不能造成更新覆盖。这就要求更新同一行的两个事务，必须被分发到同一个 worker中。
2. 同一个事务不能被拆开，必须放到同一个 worker 中。
   各个版本的多线程复制，都遵循了这两条基本原则。

MySQL 5.5 版本的并行复制策略

按表分发策略
这个按表分发的方案，在多个表负载均匀的场景里应用效果很好。但是，如果碰到热点表，
比如所有的更新事务都会涉及到某一个表的时候，所有事务都会被分配到同一个 worker
中，就变成单线程复制了。

按行分发策略
相比于按表并行分发策略，按行并行策略在决定线程分发的时候，需要消耗更多的计算资源。

MySQL 5.6 版本的并行复制策略
支持了并行复制，只是支持的粒度是按库并行

MariaDB 的并行复制策略

1. 在一组里面一起提交的事务，有一个相同的 commit_id，下一组就是 commit_id+1；
2. commit_id 直接写到 binlog 里面；
3. 传到备库应用的时候，相同 commit_id 的事务分发到多个 worker 执行；
4. 这一组全部执行完成后，coordinator 再去取下一批。
   这个策略仍然是一个很漂亮的创新。因为，它对原系统的改造非常少，实现也很优雅。

MySQL 5.7 的并行复制策略

1. 同时处于 prepare 状态的事务，在备库执行时是可以并行的；
2. 处于 prepare 状态的事务，与处于 commit 状态的事务之间，在备库执行时也是可以并行的。

MySQL 5.7.22 的并行复制策略

1. writeset 是在主库生成后直接写入到 binlog 里面的，这样在备库执行的时候，不需要解析 binlog 内容（event 里的行数据），节省了很多计算量；
2. 不需要把整个事务的 binlog 都扫一遍才能决定分发到哪个 worker，更省内存；
3. 由于备库的分发策略不依赖于 binlog 内容，所以 binlog 是 statement 格式也是可以的。
   因此，MySQL 5.7.22 的并行复制策略在通用性上还是有保证的。

未完，待续…