知识总结来源于极客时间专栏《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 索引上遍历会更快点。