Mysql面试（三）（sql优化相关）

尽量使用聚簇索引，或者使用覆盖索引
聚簇索引的叶子节点存放的是整行数据，而非主键索引也就是二级索引的叶子节点是主键的值，所以根据二级索引去查询的话会根据拿到的主键id回到聚簇索引里面在查一次，称为回表。
而使用覆盖索引就是说在查询的时候只查索引树上已经有的值，比如直接查询主键id，这样就不需要回表了。

使用explain查看语句的预执行行数是否符合预期值，判断mysql是不是选错索引了
优化器在选择索引的时候，往往会根据预计扫描的行数，是否需要回表等来判断选择哪个索引，也就是说预计扫描行数是mysql选索引的因素。
使用explain可以查看语句的执行情况，其中就有rows字段显示语句的预计扫描行数，但是这个值是不准确的。所以有时统计信息不对，我们可以使用analyze table t 命令，重新统计索引信息。或者直接在语句中指定使用某个索引，force index，但是不建议使用force index，万一索引改名字了，语句又得改，或者迁移到了别得数据库，语法可能不兼容。

如果业务代码保证了唯一性，尽量使用普通索引
对于唯一索引，在查询得时候，查找到第一个满足条件得记录后，就停止检索。
对于普通索引，在查询得时候，查到到满足条件得记录后，需要查找下一个记录，直到碰到第一个不满足条件得记录。
而这样得性能差距微乎其微，因为innodb是按数据页来读写得，读的时候将整个数据页读入内存，默认16kb，而普通索引要做得只不过是多一次查找和判断，在内存中操作所以差距不大。

而对于更新语句，两种索引差距还是很明显得。当我们要更新数据得时候，如果数据页在内存中，则直接更新，如果数据页不在内存中，在不影响一致性得前提下，innodb会将这些操作缓存在change buffer中，这样就不需要从磁盘读入数据页了。下次查询需要这个数据页得时候，将数据页读入内存然后执行change bugger 中与这个页有关得操作。
将change buffer中得操作应用到数据页，称为merge。除了访问这个数据页会触发merge，系统后台线程会定期merge。数据库正常关闭得时候也会出发merge。显然change buffer减少读磁盘，语句执行速度会明显提升。

而对于唯一索引，更新操作都需要判断操作是不是会违反唯一性，也就是说要将数据页读入内存才能判断，既然数据页在内存中，那就直接更新内存得了。
所以，change buffer只有普通索引可以使用。
change buffer 使用得是buffer pool里得内存，可以使用参数 innodb_change_buffer_max_size设置。
对于数据页不在内存中，执行插入操作得话，唯一索引：将数据页读入内存，判断唯一性，没冲突得话，插入数据，执行结束。
普通索引：将更新记录在change buffer，执行结束。

将数据从磁盘读入内存涉及随机 IO 的访问，是数据库里面成本最高的操作之一。change buffer 因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。

避免在条件字段中使用函数操作
如果对字段做了函数计算，就用不上索引了，这是MySQL的规定。
对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。

隐式类型转换
select * from t where id = 1
如果id是字符类型的，1是数字类型的，你用explain会发现走了全表扫描，根本用不上索引。
因为MySQL底层会对你的比较进行转换，相当于加了 CAST( id AS signed int) 这样的一个函数，函数会导致走不上索引。

隐式字符编码转换
如果两个表的字符集不一样，一个是utf8mb4，一个是utf8，因为utf8mb4是utf8的超集，所以一旦两个字符比较，就会转换为utf8mb4再比较。
转换的过程相当于加了CONVERT(id USING utf8mb4)函数，又跟上面一样，用到函数就用不上索引了。

调整innodb刷脏页flush
innodb在处理更新语句的时候，只是更新内存和写完redo log后，就表示更新成功了。而总是得找个时间把内存里得数据写到磁盘，这个过程称为flush。
当内存数据页里的数据和磁盘上得数据不一致时，称为脏页。内存写到磁盘后，内存和磁盘得数据一致，称为干净页。
而flush可能会引起mysql短暂时间的‘暂停’。
redo log写满的话，会停止所有更新操作，把check point推进，让redo log留出空间可以继续写。而redo log 写满了就要flush脏页了。
系统内存不足时，当需要新的内存页，而内存不够用，就要淘汰掉一些，如果淘汰的是脏页，那就得把脏页写到磁盘先。

innodb_io_capacity 这个参数可以告诉innodb你的磁盘能力，如果你的磁盘能力很强，而你设置得很低，那么innodb会认为你的系统能力就是那么弱，导致刷脏页特别慢。而且也不能让innodb全力的刷脏页，总得留些磁盘能力给其他的把。
innodb刷盘的速度主要有两个因素：脏页的比例，redo log的写盘速度。
参数 innodb_max_dirty_pages_pct 是脏页比例上限，默认值是 75%。尽量不要让它超过75%。