为什么MySQL选错索引
show index命令
当你需要查看某张表的索引详情时，可以使用命令：
1
show index from t13;
字段解释：
- Non_unique：如果是唯一索引，则值为 0，如果可以有重复值，则值为 1;
- Key_name：索引名字;
- Seq_in_index：索引中的列序号，比如联合索引 idx_a_b_c (a,b,c) ，那么三个字段分别对应 1,2,3;
- Column_name：字段名;
- Collation：字段在索引中的排序方式，A 表示升序，NULL 表示未排序;
- Cardinality：索引中不重复记录数量的预估值，该值等会儿会详细讲解;
- Sub_part：如果是前缀索引，则会显示索引字符的数量；如果是对整列进行索引，则该字段值为 NULL;
- Null：如果列可能包含空值，则该字段为 YES；如果不包含空值，则该字段值为 ’ ’;
- Index_type：索引类型，包括 BTREE、FULLTEXT、HASH、RTREE 等
Cardinality
Cardinality 表示该索引不重复记录数量的预估值。如果该值比较小，那就应该考虑是否还有必要创建这个索引。
Cardinality 不是每次操作都重新统计的，而是通过采样的方法来完成的。 Cardinality 统计信息的更新发生在两个操作中：INSERT 和 UPDATE。当然也不是每次 INSERT 或 UPDATE 就更新的，其更新时机为：
- 表中 1/16 的数据已经发生过变化
- 表中数据发生变化次数超过 2000000000
Cardinality 值是怎样统计和更新的呢？
InnoDB 表取出 B+ 树索引中叶子节点的数量，记为 a；随机取出 B+ 树索引中的 8 个（这个数量有参数 innodb_stats_transient_sample_pages 控制，默认为 8）叶子节点，统计每个页中不同记录的个数（假设为 b1，b2，b3，…，b8）。则 Cardinality 的预估值为：
1
（b1 + b2 + b3 + … b8）* a/8
所以 Cardinality 的值是对8个叶子节点进行采样获取的，显然这个值并不准确，只供参考。
统计 Cardinality 涉及到的几个参数：
innodb_stats_transient_sample_pages：设置统计 Cardinality 值时每次采样页的数量，默认值为 8。innodb_stats_method：用来判断如果对待索引中出现的 NULL 值记录，默认为 nulls_equal，表示将 NULL 值记录视为相等的记录。另外还有 nulls_unequal 和 nulls_ignored。nulls_unequal 表示将 NULL 视为不同的记录，nulls_ignored 表示忽略 NULL 值记录。innodb_stats_persistent：是否将 Cardinality 持久化到磁盘。好处是：比如数据库重启，不需要再计算 Cardinality 的值。innodb_stats_on_metadata：当通过命令 show table status、show index 及访问 information_chema 库下的 tables 表和 statistics 表时，是否需要重新计算索引的 Cardinality。目的是考虑有些表数据量大，并且辅助索引多时，执行这些操作可能会比较慢，而使用者可能并不需要更新 Cardinality。
统计信息不准确导致选错索引
在 MySQL 中，优化器控制着索引的选择。一般情况下，优化器会考虑扫描行数、是否使用临时表、是否排序等因素，然后选择一个最优方案去执行 SQL 语句。
而 MySQL 中扫描行数并不会每次执行语句都去计算一次，因为每次都去计算，数据库压力太大了。实际情况是通过统计信息来预估扫描行数。可能就是因为某些值不精准导致选错了索引。
重新分析命令：
1
analyze table t13;
单次选取的数据量过大导致选错索引
有时，我们也会遇到这种情况，如果单次选取的数据量过大，可能也会导致 “选错” 索引。
1
2
select a from t13 where a>80000 limit 1000; /* sql1 */
select a from t13 where a>70000 limit 1000; /* sql2 */
sql1 满足条件的数据量相对较少时，是走的索引 idx_a；而在 sql2 中，满足条件的数据量相对比较多时，就走了主键索引。
使用 force index 来强制走索引 idx_a，sql 如下：
1
select a from t13 force index(idx_a) where a>70000 limit 1000;
