《MySQL实战45讲》个人笔记-实战篇

拜读了林晓斌大佬的《MySQL实战45讲》，特意做个知识点总结，以便后期回忆。

09、普通索引和唯一索引，应该怎么选择？

查询时：普通索引找到第一个后，还会往下找，直到碰到第一个不满足条件的为止，如果这个条件在页的最后，那么还会加载下一页继续查找；唯一索引找到第一个后直接返回。

更新时：

change buffer概念：当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。

将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭（shutdown）的过程中，也会执行 merge 操作。

记录要更新的目标页在内存中：这时，InnoDB 的处理流程如下：对于唯一索引来说，找到 3 和 5 之间的位置，判断到没有冲突，插入这个值，语句执行结束；对于普通索引来说，找到 3 和 5 之间的位置，插入这个值，语句执行结束。基本上没有差别。

记录要更新的目标页不在内存中：这时，InnoDB 的处理流程如下：对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束；对于普通索引来说，则是将更新记录在 change buffer，语句执行就结束了。这时普通索引change buffer效率更高。

change buffer有什么弊端：对于更新后立刻查询的情况，如果都是这样的操作，更新时写change buffer，查询时会进行merge操作，频繁的进行merge操作也会影响性能。如果是这样的场景建议关闭changer buffer。

change buffer 与 redo log：redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写），而 change buffer 主要节省的则是随机读磁盘的 IO 消耗。（数据页在内存，那就修改数据页，写redo log，如果数据页不在内存，修改的也不是唯一索引，而是普通索引，那就写change buffer。并把这个change buffer写入到redo log，防止更新丢失。）

 

10、MySQL为什么有时候会选错索引？

原因是：优化器在选择索引时会进行采样。采样统计的时候，InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。

对于由于索引统计信息不准确导致的问题，你可以用 analyze table 来解决。而对于其他优化器误判的情况，你可以在应用端用 force index 来强行指定索引，也可以通过修改语句来引导优化器，还可以通过增加或者删除索引来绕过这个问题。

 

11、怎么给字符串字段加索引？

邮箱类的字符串：可以把前几位作为索引，也就是说@163.com之前的，这样能有效减少索引空间

身份证类的字符串：由于身份证前几位没有区分度，可以把身份证倒序作为索引，或者后几位作为索引

其他字符串：使用hash方式，例如crc32('字符串')校验码作为一个索引，但可能有重复，查询时需要再判断一下：select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'

 

12、为什么我的MySQL会“抖”一下？

平时执行很快的更新操作，其实就是在写内存和日志，而 MySQL 偶尔“抖”一下的那个瞬间，可能就是在刷脏页（flush），也就是把内存中的数据同步到磁盘。

 

13、为什么表数据删除一半，表文件大小不变？

delete 命令只是逻辑删除，其实只是把记录的位置，或者数据页标记为了“可复用”，但磁盘文件的大小是不会变的。经过大量增删改的表，都是可能是存在空洞的。所以，如果能够把这些空洞去掉，就能达到收缩表空间的目的。而重建表，就可以达到这样的目的，可以使用 alter table A engine=InnoDB 命令来重建表。

Online DDL重建表过程：1、新建临时文件；2、把表A记录生成B+树存储到临时文件中；3、生成临时文件的过程中，将所有对 A 的操作记录在一个日志文件（row log）中；4、临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表 A 相同的数据文件；5、用临时文件替换表 A 的数据文件。

 

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

对于 count(主键 id) 来说：InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。

对于 count(1) 来说：InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。单看这两个用法的差别的话，你能对比出来，count(1) 执行得要比 count(主键 id) 快。因为从引擎返回 id 会涉及到解析数据行，以及拷贝字段值的操作。

对于 count(字段) 来说：如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不能为 null，按行累加；如果这个“字段”定义允许为 null，那么执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加。也就是前面的第一条原则，server 层要什么字段，InnoDB 就返回什么字段。

但是 count(*) 是例外：并不会把全部字段取出来，而是专门做了优化，不取值。count(*) 肯定不是 null，按行累加。

看到这里，你一定会说，优化器就不能自己判断一下吗，主键 id 肯定非空啊，为什么不能按照 count(*) 来处理，多么简单的优化啊。当然，MySQL 专门针对这个语句进行优化，也不是不可以。但是这种需要专门优化的情况太多了，而且 MySQL 已经优化过 count(*) 了，你直接使用这种用法就可以了。

所以结论是：按照效率排序的话，count(字段)<count(主键 id)<count(1)≈count(*)，所以我建议你，尽量使用 count(*)。

 

未完待更...