mysql为什么会选错索引（mysql45讲）

文章目录

问题
小结

问题

在mysql中一张表是可以支持多个索引的，但是，写SQL语句的时候，并没有主动指定使用哪一个索引，也就是说，这种没有指定索引的情况下，使用哪个索引是由MYSQL决定的

场景

一条本来可以执行的很快的语句，由于MySql选错了索引，而导致执行速度变的很慢

举例说明

CREATE TABLE t(
	id INT(11) NOT NULL,
	a INT(11) DEFAULT NULL,
	b INT(11) DEFAULT NULL,
	PRIMARY KEY(id),
	KEY a(a),
	KEY b(b)
	)ENGINE=INNODB;
=====================================================
DELIMITER ;;
CREATE PROCEDURE idata()
BEGIN
	DECLARE i INT;
	SET i = 1;
	WHILE(i<=10000)DO
		INSERT INTO t VALUES(i,i,i);
		SET i = i + 1;
	END WHILE;
END;;
DELIMITER ;
CALL idata();

接下来，执行一条SQL语句：

SELECT * FROM t WHERE a BETWEEN 1000 AND 40000;

使用explain命令查看：explain SELECT * FROM t WHERE a BETWEEN 1000 AND 40000;

+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | t     | NULL       | ALL  | a             | NULL | NULL    | NULL | 10337 |    87.08 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+

这里没有走索引，是为什么?

mysql并不是在字段上建了索引就会走索引，而是使用优化器来判断是否选择索引，选择哪一个索引，优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句

为了获得最优的执行方案，一般都考虑3个点：

扫描行数
是否使用临时表
是否需要排序

这三个点里面，最重要的就是扫描行数，扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的CPU资源越少

优化器是怎么判断扫描行数的？

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

查看基数的语句：

mysql> show index from t;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t     |          0 | PRIMARY  |            1 | id          | A         |       10337 |     NULL | NULL   |      | BTREE      |         |               |
| t     |          1 | a        |            1 | a           | A         |       10000 |     NULL | NULL   | YES  | BTREE      |         |               |
| t     |          1 | b        |            1 | b           | A         |       10000 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

显然，这个表的三个索引的基数值并不同，而且其实都不准确
那么，Mysql是怎样得到索引的基数的呢?

采样统计

采样统计的时候，Mysql会选择N个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数
而数据表是会持续更新的，索引统计信息也不会固定不变，所以，当要变更的数据行数超过1/M的时候，会自动触发重新做一次索引统计

索引统计

在Mysql中有两种存储索引统计的方式：可以通过设置参数innodb_stats_persistent的值来选择：
- 设置为on时，表示统计信息会持久化存储，这是默认的是N=20,M=10
- 设置为off时，表示统计信息之存储在内存中，这时，默认的N是8，M是16

由于是采样统计，这些值有很大可能不准

别慌，判断值不只是扫描行数！！！

其实索引统计只是一个输入，对于一个具体的语句，优化器还要判断执行这个语句本身要扫描多少行使用普通索引还要把回表的代价算进去，和走主键索引比较

修正统计信息的命令

analyze table t;

如果在实践中，发现explain的结果和预估的row值相差的有点大，可以采用这个命令来处理，使用这个命令可以重新统计扫描行数哟

但是，优化器不只是看行数

看如下语句：

SELECT * FROM t WHERE (a BETWEEN 1000 AND 2000) AND (b BETWEEN 30000 AND 50000) ORDER BY b LIMIT 1;

由于要考虑排序，所以优化器极有可能会选择索引B，显然，这个返回值是空，选择索引a只用扫描1000行，选择索引b需要扫描20000行

索引选择异常和处理

使用force index 强行选择一个索引，mysql 会根据词法解析的结果分析出可能可以使用的索引作为候选项，然后在候选项列表里面一次判断每个索引可能会扫描多少行，如果force index在这个候选索引列表中，就直接执行这个索引，不在评估其他索引的执行代价
修改数据库语法，把 order by b limit 1改成Order by a,b limit 1
在有些场景下，我们可以新建一个更合适的索引，来提供给优化器做选择，或删除误用的索引

小结

这节主要讨论了关于索引选择的问题，如果我们在编写代码的时候不使用force index强制使用索引的话，索引的选择就会由mysql来完成，mysql在选择索引的时候很有可能把看起来正确的索引选成其他的索引，因为选择索引的条件是通过扫描行数，是否排序，是否使用临时表这三个条件结合进行判断，对于扫描行数，主要通过统计区分度的方式得出(使用analyze table t;重新统计)，还要考虑普通索引回表和主键索引之间的区别，其次还说了索引选择异常的三种处理方式，最好用的是force index

文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/202551.html