Mysql - 优化器阶段的索引选择过程

    当一个表创建了索引，但是执行查询时可能没有走索引，即索引失效。表上创建了多个索引，在执行查询时却选择错了索引。导致没有按照我们写sql和建索引时想要的路径走，这是谁的锅？Mysql架构图中分析了，选择执行的索引由优化器完成，我们可以根据执行计划explain查看选择的索引和执行的路径，此时就需要分析优化器是根据什么来选择的，怎么才能引导按照我们的意图执行。优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。

    优化器选择索引的依据有（或者说下面的条件都可能对选择产生影响）：

1. 扫描的行数（基于采样）
2. 是否回表操作
3. 是否使用临时表
4. 是否使用排序或者文件排序
5. ......

   如果执行计划（explain）不是按照我们的预期执行，那么可以采用一些引导或强制措施：

1、使用 force index 强行选择一个索引

    select * from t force index(索引名称) where ...

2、可以考虑修改语句，引导需要期望的结果

    比如：order by b limit 1  -> order by b,a limit 1

3、在某些场景下可以新建一个更适合的索引，提供给优化器选择，或者删除可能误导的索引【与业务耦合度很高】

 

    是否回表、是否使用临时表、是否排序本身与sql和业务有紧密的连线，这需要后面对 join、order by等执行流程有很深的理解才能更清晰的理解。 但是扫描的行数是怎么计算出来的呢？ 比如一个表有 1千万数据，一定是计算出准确的会扫描的行数吗？那不是天荒地老了，其实是根据采样率确定计算的，只要能计算大概就能大致判断和引导选择。

索引采样统计信息

    区别度：一个索引的不同值的个数， 我们建立索引的时候有一个原则就是区别度越大性能优化越大，索引一般类似性别这样的字段不适合建索引，只能排除概率一半左右的数据

    基数：统计样本的条数

    采样率：InnoDB一般不会使用所有表的行数作为基数进行分析，否则对性能影响很大。则会通过配置项 N 个数据页上统计区别度的平均值，再乘以所有数据页数，得到基数，所以基数只是一个近似值。数据会不断地更新，那么统计信息也会不准却，触发重新统计的条件就是 当数据行数超过 1/M时，而其中的 M和N值是根据  innerdb_stats_persistent配置项觉定的，当设置为 ON时表示统计信息持久化，M = 20，N = 10； 当设置为 OFF时，表示不持久化统计信息（内存中）， M = 8 N = 16。

    我们也可以执行命令 analyze table 表名  手动触发重新采用统计，如下：

    此时可以执行命令 show index from 表名  查看新的索引统计信息，其中 Cardinality就是采样计算的统计基数，是一个近似值，如下图：