
知识总结来源于极客时间专栏《MySQL实战45讲》,此文仅为本人日后复习所用,不用再去一篇篇文章翻阅。
https://time.geekbang.org/column/article/70848
索引选择
上篇总结聊到了索引,这次就从索引开始,继续讨论。建数据库时候,有时候纠结是建唯一索引好,还是普通索引好?要知道哪个好,要得从两方面进行分析,分别为查询过程、更新过程。
查询过程
唯一索引等值查询时候,找到结果就会直接返回了,不会再去进行额外判断。普通索引找到结果后,还要继续往下判断,直到找到第一个不满足条件的记录为止。
所以从上面来看,查询时候,仅仅是多了一些判断而已,这个影响大不大呢?答案是微乎其微。
首先要知道,引擎读写数据是按数据页为单位进行读写,一个数据页默认16k,可以存放很多叶子节点了,一般读取某个数据时候,就是先把整个数据页读到内存里面了,在内存里面进行数据判断,速度是非常快的,所以查询时候,两者的影响是微乎其微。
更新过程
要了解更新过程的差异,就得先知道 change buffer。
当需要更新一个数据页时,如果数据页在内存中就直接更新,而如果数据页还没有在内存中,在不影响数据一致性的前提下,InnoDB 会将这些更新操作缓存在 change buffer 中,这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候,将数据页读入内存,然后执行 change buffer 中与这个页有关的操作。
change buffer 不仅存在内存中,也会写到磁盘上。change buffer 缓存的操作写到对应数据页的过程称为 merge。访问这个数据页会触发 merge,系统也有后台线程会定期 merge。
什么条件下使用 change buffer ?
唯一索引的更新用不上 change buffer,只有普通索引可以使用。理由很简单,唯一索引插入时候,要判断数据是否冲突,那就需要将对应数据页加载进内存,都已经在内存中了,我们直接在内存中进行更新岂不是更好。
change buffer 的好处:减少随机磁盘访问,对更新性能的提升很明显。
这里给生产环境DB阻塞提供了一个排查思路:如果把普通索引换成唯一索引,在业务有大量数据插入的情况下,因为用不上 change buffer,所以每次读写都要频繁读取磁盘上的数据页,插入语句就会比较缓慢了,有可能就阻塞了。
change buffer 的使用场景
change buffer 累计的操作越多,收益就越大。如果业务是写多读少,写完之后不用马上读取,那么这样使用 change buffer 的效果是最好的。在上家公司搞的运维系统,就是写入之后就马上要刷新页面,这种场景就很不合适了。写多读少类似账单类、日志类系统。
所以在业务能保证唯一性的情况下,尽量使用普通索引。

这个提个疑问,change buffer 是存在内存中,也会写到磁盘,那假如在 change buffer 还没来得及写到磁盘中时,数据库崩溃了,那是不是这部分数据就丢失了呀?
答案是不会。因为事务提交时候,change buffer 的操作也会写到 redo log 里面。
MySQL 选错索引
选择索引是优化器的工作,优化器是根据扫描的行数、是否使用临时表、是否排序等因素来决定使用那个索引。
扫描行数是怎么判断的?
首先要知道扫描的行数是一个估算值,要想知道扫描的行数,得先知道总行数,这也是个估算值,这个值叫索引的基数,基数的统计是通过采样的方式,InnoDB默认选择 N 个数据页,统计这些页面上的不同值,得到一个平均值,然后乘以这个索引的页面树,就得到了索引的基数。
基数里面扫描的行数可以这样看,用explain命令,这个行数不仅包括了索引本身扫到的行数,也包括了回表的行数。

如果你觉得这个行数与实际行数相差太大,可以用 analyze table t 命令,来重新统计索引信息。如果这样还是选错索引,可以用 force index 来指定正确的索引。
字符串加索引有门路
如果字符串不是很长,我会选择直接把完整字符串作为索引的一部分,一旦字符串很长,比如身份证、邮箱这种,如果选择完整字符串做索引,会导致索引占用空间比较大,这时候应该怎么办才好呢?
前缀索引
这种时候,我们可以使用前缀索引,类似 index (email(6)),既然是前缀索引,那我们就得找出具体多少位能给我们一个比较好的区分度,
## 看具体字段有多少个不同值
mysql> select count(distinct email) as L from SUser;
## 看取不同的位,能提供的不同值,两者一比,就可以拿到一个区分度比例
mysql> select
count(distinct left(email,4))as L4,
count(distinct left(email,5))as L5,
count(distinct left(email,6))as L6,
count(distinct left(email,7))as L7,
from SUser;
使用前缀索引有利有弊:
- 好处:使用前缀索引,定义好长度,可以做到既省空间,同时又不用增加太多的查询成本。
- 坏处:使用前缀索引,就不能使用覆盖索引了,必须得回表才行,因为前缀索引的值要到主键索引上,看看是不是正确的值。
其他方式
倒序存储
还有可能遇到一种情况是,前缀索引要加很多位才有足够的区分度,这种时候,就得换个方法了,可以考虑使用倒序存储,反过来区分度也许就好了,缺点是每次都要使用 MySQL 提供的 reverse 函数进行计算。
加多个hash字段
我们可以加多一个字段,并且给这个字段加个索引,缺点是每次都要进行 crc32 函数进行 hash 计算。比如
mysql> alter table t add id_card_crc int unsigned, add index(id_card_crc);
需要特别注意了,这两种方式查询性能稳定,但都不支持范围查询。

MySQL突然很慢如何排查
注意了,我这里说的是突然,不属于没有加索引的情况,如果 sql 平时都正常,突然变得很慢,这种时候就要考虑是不是在刷“脏页”了。
当内存数据页跟磁盘数据页内容不一致的时候,我们称这个内存页为“脏页”。内存数据写入到磁盘后,内存和磁盘上的数据页的内容就一致了,成为“干净页”。
平时操作突然变得很慢,可能就是在刷脏页了。
触发数据库刷脏页的场景:
- InnoDB 的 redo log 写满了,要先把部分 redo log 里面对应的脏页 flush 到磁盘。
- 系统内存不足。要淘汰一些数据页,给别的数据页使用。如果淘汰的是“脏页”,就要先将脏页写到磁盘
- MySQL 会在系统空闲时候,安排线程来进行脏页处理。
- MySQL 要准备关闭时候,也会把脏页刷到磁盘上。
第三、第四点都是数据库的正常情况,第二点也不是大问题,内存总是有限的,用完就得淘汰一些,只不过一下子淘汰的脏页太多就会影响查询时间。日志写满这种情况就要极力避免了。
MySQL 刷脏页速度可以重点看 innodb_io_capacity 这个参数的值,如果是 SSD 硬盘,那就可以设大点了,太小会被 MySQL 认为你的系统性能一般,导致慢慢刷。
count(*) 必知必会
不同的 MySQL 引擎,count() 有不同的实现方式。MyISAM 是把表的总数存到了磁盘上,所以 count()时候,直接拿这个数返回就行。InnoDB 统计表的总数时候,是自己一行行加。
看到这里应该会有个疑惑,为啥 InnoDB 不也存一下总数吗?那是因为,InnoDB 是支持事务的,因为 MVCC 的存在,事务在不同时刻看到的数据是不一样的,所以没法存起来。
不要再纠结 count() 和 count(1) 和 count(字段)哪个更快了,无脑 Count() 就完事了,因为 MySQL 专门优化过count(*)。绝对不要 count(字段),count字段会去判断没个字段是不是 null,不是 null 再加1,浪费时间。
count(*) 统计正确做法
如果表比较大,对统计数量有需求的,可以把数量保存起来,那具体保存到哪里?有两个选择,一个是Redis,一个是数据库。
保存到 Redis
保存数据到Redis,有可能出现数据不一致的情况。

A先插,B读Redis,如果A的Redis计数还没加1,那就会对不上了。归根结底是因为这属于分布式事务,没有比较好的解决方案。
保存到数据库
可以把数量保存到单独的表中,利用 InnoDB 的事务特性,不同时刻开启的事务,根据 MVCC 只能看到对应版本的数据。
order by 的工作原理
首先要知道 order by 排序时候,会用到一块内存,这块内存被成为 sort_buffer。排序有两种算法,分别为全字段排序和rowid排序。MySQL 会根据查询的字段和索引情况来选择合适的算法。
假如现在就有个这样的sql,索引是city, 查询杭州的人信息,并且按名字排序
select city,name,age from t where city='杭州' order by name limit 1000 ;
如果我们要查询的字段很多,就像现在有 city、name、age,这三个加起来的长度一旦超过了 MySQL
内置的 max_length_for_sort_data 参数指定的值,会使用 rowid 排序,如果还在范围内,那就使用全字段排序。
全字段排序
这种情况下,先通过 city 索引树逐条找出符合条件的记录,然后根据 主键id 去遍历主键索引,拿出语句需要的字段。就上面的语句而言,就拿出city、name、age 这个三个字段,放到 sort_buffer 中,进行排序,但内存总是有限的,如果需要排序的内容过多,则会借用磁盘来进行辅助排序。磁盘排序就是把要排序的内容分成好几个文件,每个文件先排序,最后再汇总。

rowid排序
如果要排序的字段很多,sort_buffer 里面要放的字段数太多,这样内存中能够存放的行数就少,要分成很多个临时文件,排序性能会变差。
现在流程变为,city 索引树取出主键id,去主键索引上获取 name 字段,然后把 name,id 放入到 sort_buffer 中,排完序后,再到主键索引中找出完整的数据记录,返回给用户。

这种排序算法缺点就是要回表造成多磁盘读,不会被有限选择,除非内存不够了。
优化方案
不是所有的语句,都要在 sort_buffer 中进行排序,如果索引树上有要排序的字段,那只要我们利用起来这个索引,拿到的记录自然就是有序的。这就可以好好利用联合索引了,更有甚者,利用起覆盖索引,回表也不用了。但也不是无脑用覆盖索引,索引的维护是有代价的。

像这个语句就用到了覆盖索引,Extra列有个 Using indx,这就是覆盖索引独有的信息。
select namg,age, city from T order by create_time
大家可以猜下上面的语句会不会用到 create_time 索引,答案是不会的,因为优化器认为走了 create_time 索引,还得回表到主键索引上,代价较高,索引就直接使用主键索引了。
select namg,age, city from T order by create_time limit 1000
这个语句就会用到 create_time 索引了,为啥呢?因为用到 limit,减少了要回表的数量,那就现在 create_time 索引上遍历会更快点。