MySQL为什么会选错索引

优化器的逻辑

MySQL server层的优化器负责选择索引。而优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。在数据库里面，扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的 CPU 资源越少。当然，扫描行数并不是唯一的判断标准，优化器还会结合是否使用临时表、是否排序等因素进行综合判断。

话外音：优化器也是根据抽样去选择index，复杂情况下抽样肯定会有问题。

扫描行数是怎么判断的

MySQL 在真正开始执行语句之前，并不能精确地知道满足这个条件的记录有多少条，而只能根据统计信息来估算记录数。这个统计信息就是索引的“区分度”。显然，一个索引上不同的值越多，这个索引的区分度就越好。而一个索引上不同的值的个数，我们称之为“基数”（cardinality）。也就是说，这个基数越大，索引的区分度越好。

那么，MySQL 是怎样得到索引的基数的呢？这里，我给你简单介绍一下 MySQL 采样统计的方法。为什么要采样统计呢？因为把整张表取出来一行行统计，虽然可以得到精确的结果，但是代价太高了，所以只能选择“采样统计”。采样统计的时候，InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。而数据表是会持续更新的，索引统计信息也不会固定不变。所以，当变更的数据行数超过 1/M 的时候，会自动触发重新做一次索引统计。

举个例子：

取n=3个数据页统计，假设共有10个索引数据页；page1：10个不同值；page2：20个不同值；page3：15 个不同值；索引基数=(10+20+15)/3*10=150；即n个数据页的平均不同值乘上总的页面数得到索引基数，其实就是一种随机取样统计，因为选择全部数据页取平均值代价太高不可取，被取得数据页就是样本。注：下面提到n其实有默认值，提到n=3只是为了更好理解计算过程。

数据行数的1/M，比如有10万条，M=10，那么结果就是10000，也就是说当变更的数据行数超过10000的时候，就会重新做一次索引统计。

话外音：涉及到cardinality数据量大都会精度丢失，比如：ElasticSearch近似聚合cardinality 。

使用 show index from table_name 命令，查看表索引的基数 2. 使用 analyze table table_name 命令，重新统计索引信息，解决采样导致的扫描行数出错的问题。

优化器优化时会考虑的因素：

• 扫描行数
• 是否使用临时表
• 是否需要排序 话外音：涉及到这个点，都要考虑你的index 是否生效。

索引选择异常和处理

• 采用 force index 强行选择一个索引。MySQL 会根据词法解析的结果分析出可能可以使用的索引作为候选项，然后在候选列表中依次判断每个索引需要扫描多少行。如果 force index 指定的索引在候选索引列表中，就直接选择这个索引，不再评估其他索引的执行代价。
• 可以考虑修改语句，引导 MySQL 使用我们期望的索引。语义的逻辑是相同的。
• 在有些场景下，我们可以新建一个更合适的索引，来提供给优化器做选择，或删掉误用的索引。不需要的索引也可以通过ignore index的方式忽略。

什么情况要用force index ？

1. SQL比较复杂的情况下，order by字段和where条件后面出现多种符合条件（本质造成扫描行数不准确，以及排序）
2. 嵌套子查询（本质是临时表）

但是尽量不要用！force index也会有一些问题，不优雅，如果索引改了名字，这个语句也得改，还得发版，显得很麻烦。在这里就不说迁移数据库的问题了。

话外音：能通过调整索引和调整SQL语句的、甚至调整应用程序的都不要使用force index。

每周一句：你对别人怎么样，别人就对你怎么样。己所不欲，勿施于人！