【MySql】为什么我的查询没有走索引？详解mysql优化器中的成本计算

发表于 2025/03/25 更新于 2025/03/26

作者 张子禾

20 分钟阅读

1.为什么我的查询语句没有走索引？

当我们执行查询语句时使用order by column，若在column字段上创建了索引，理论上说，为了避免排序，mysql会查询column字段的非聚簇索引，然后再回表查询数据。这样可以节省排序成本，也可以避免全表扫描。

然而，实际的情况，可能比想象中复杂。

举个🌰

  
# student
CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20),
    age INT,
    score INT,
    INDEX idx_age_score (age, score)
);

-- 插入 10,000 条数据
INSERT INTO students (name, age, score)
SELECT
    CONCAT('Student', n),
    FLOOR(RAND() * 40) + 10, -- age: 10-49
    FLOOR(RAND() * 101)      -- score: 0-100
FROM (
    SELECT a.n + b.n * 10 + c.n * 100 + d.n * 1000 + 1 AS n
    FROM (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
          UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a
    CROSS JOIN (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
                UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b
    CROSS JOIN (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
                UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) c
    CROSS JOIN (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) d
    WHERE a.n + b.n * 10 + c.n * 100 + d.n * 1000 < 10000
) t;
-- 重新分析表，计算基数
ANALYZE TABLE students;

我们使用上述sql语句创建了students表并插入10000条数据然后，使用ANALYZE TABLE students重新计算基数，关于基数的概念，后文会提到。

此时，students存在普通索引INDEX idx_age_score (age, score)。我们执行如下sql

  
#根据age排序，期望效果是先查idx_age_score非聚簇索引，再回表
# sql 1
EXPLAIN SELECT * FROM `students` ORDER BY age

得到的结果却和期望的不太一样

+----+-------------+----------+------------+------+---------------+--------+---------+--------+-------+----------+----------------+
| id | select_type | table    | partitions | type | possible_keys | key    | key_len | ref    | rows  | filtered | Extra          |
+----+-------------+----------+------------+------+---------------+--------+---------+--------+-------+----------+----------------+
| 1  | SIMPLE      | students | <null>     | ALL  | <null>        | <null> | <null>  | <null> | 10125 | 100.0    | Using filesort |
+----+-------------+----------+------------+------+---------------+--------+---------+--------+-------+----------+----------------+

可以看到

type = all //全表扫描
key = NULL //没有使用索引
rows = 10125 //查询行数=表的总行数
Extra = 'Using filesort'  //使用了文件扫描

为什么order by 字段上建立了索引，执行计划中却显示没有使用索引呢？

如果我们在查询条件中加上WHERE限制：

  
# sql 2
EXPLAIN SELECT * from `students` WHERE age BETWEEN 15 AND 35 ORDER BY age

  
+----+-------------+----------+------------+------+---------------+--------+---------+--------+-------+----------+-----------------------------+
| id | select_type | table    | partitions | type | possible_keys | key    | key_len | ref    | rows  | filtered | Extra                       |
+----+-------------+----------+------------+------+---------------+--------+---------+--------+-------+----------+-----------------------------+
| 1  | SIMPLE      | students | <null>     | ALL  | idx_age_score | <null> | <null>  | <null> | 10125 | 51.92    | Using where; Using filesort |
+----+-------------+----------+------------+------+---------------+--------+---------+--------+-------+----------+-----------------------------+

此时可以看到，执行计划中的possible_keys变为我们期望的idx_age_score，但实际也并没有使用索引(key != idx_age_score)

如果我们只查询age字段呢

  
# sql 3
EXPLAIN SELECT age from `students` WHERE age BETWEEN 15 AND 35 ORDER BY age

得到的结果如下：

  
+----+-------------+----------+------------+-------+---------------+---------------+---------+--------+------+----------+--------------------------+
| id | select_type | table    | partitions | type  | possible_keys | key           | key_len | ref    | rows | filtered | Extra                    |
+----+-------------+----------+------------+-------+---------------+---------------+---------+--------+------+----------+--------------------------+
| 1  | SIMPLE      | students | <null>     | range | idx_age_score | idx_age_score | 5       | <null> | 5257 | 100.0    | Using where; Using index |
+----+-------------+----------+------------+-------+---------------+---------------+---------+--------+------+----------+--------------------------+

可以看到，此时key=idx_age_score,type=range，查询语句使用了索引

2.索引忽隐忽现，其实是优化器在’作祟’

我们都知道，从客户端发送sql语句，到服务端返回数据，通常经历了以下几个阶段：

1.客户端发送sql
2.服务端收到sql，解析sql
3.服务端校验收到的sql,检查语法错误(如关键字错误等)，检查语义错误(表不存在、字段不存在)
4.服务端将sql解析为内部语法树
5.服务端分析语法树，生成多种执行计划,并基于成本模型选择最优计划
6.使用最优计划查询数据，有缓存的话，会从缓存中读数据
7.服务端返回数据

而优化器’做祟’的点，就在于第五步：生成多种执行计划并选择最优计划

2.1.生成执行计划

  
EXPLAIN SELECT * FROM `students` ORDER BY age

还是以第一个sql语句为例，由于索引的存在，mysql会生成两种执行计划：

全表扫描
先扫描分聚簇索引，再根据查询出的数据回表

2.2.选择最优计划

首先，mysql中的查询语句性能成本，主要集中在I/O，排序，数据类型转换等步骤中。

其中I/O性能由磁盘性能决定，占性能成本的大头排序、数据类型转换等性能由CPU决定，单次成本低，但是往往执行次数多

对于查询语句的性能评估,mysql有一套成本模型，即：

I/O操作，权重为1
排序、数据类型等操作，权重为0.2
由I/O权重*I/O次数+排序权重*排序次数+其他权重*其他次数... = 最终得到当前执行计划的性能成本

那么，前面提到的执行计划，他们的性能成本分别是多少呢？

2.3.执行计划性能分析

2.3.1.全表扫描

I/O 成本

分析一下students表的行有多大

1.INT(4) + VARCHAR(20) + INT(4) + INT(4) = 40 Byte(大约)

2.可预估数据总量 = 40Byte * 10000 = 400000 Byte

3.已知mysql中I/O的单位Page大小为16KB，也就是说，一个Page可以携带16*1024 = 16384 Byte 的数据

4.因此，全表扫描总共会经历400000/16384 = 24.4 = 25 (大约) 次I/O

排序成本 由于聚簇索引并没有按照age排序，因此排序查出来后再进行排序，成本为(nlogn)= 133,000

全表扫描总成本=25 * 1 + 133,000 * 0.2 = 26625单位

2.3.2.先扫描非聚簇索引，再根据查询出的数据回表

I/O成本 1.扫描全部非聚簇索引

非聚簇索引单个节点长度 = INT(4) + INT(4) + 指针(6-8)= 14 (大约)

扫描全部非聚簇索引的I/O次数为(14 * 10000) / 16384 = 9 (大约)

2.通过查询出的id进行回表，I/O次数与全表扫描一致，25次I/O

排序成本 非聚簇索引已经排好序了，这里不需要进行排序，成本为0

使用索引成本=(25+9) * 1 + 0 * 0.2 = 34 单位

可以看到,全表扫描成本26625是远大于使用索引成本34的，但是为什么EXPLAIN执行计划中，mysql还是选择使用了全表扫描呢？原因就在于，顺序I/O与随机I/O的次数与性能差异

2.3.4.顺序I/O vs 随机I/O

执行全表扫描时，由于主键id是默认自增的，优化器会认为只需进行顺序I/O即可。顺序I/O的单次成本权重为1，因此,顺序I/O的总成本=25*1 = 25 单位

然而，在索引方案上，由于从非聚簇索引查出的id是无序的，甚至可能是散列的，优化器会认为需要进行更多次I/O 这里指的’更多次I/O’没有一个固定值，视id散列程度而定，散列程度越高，I/O次数就越多

若从非聚簇索引查出id是有序的，回表成本=全表扫描的回表成本=25 单位
若从聚簇索引查出的id是散列的，可能每行数据都需要进行回表，即10000次回表，而默认随机I/O的成本权重为顺序I/O的四倍即4。因此，可能的最多回表成本为10000*4 = 40000 = 40000 单位

回到上面的成本计算，优化器认为：

1-全表扫描总成本为25 * 1 + 133,000 * 0.2 = 26625单位
2-使用索引总成本为10000*4 + 0 * 0.2 = 40000单位
使用全表扫描

再看上面的sql 2与sql 3 ，同样的条件，不同的查询字段，得到了不同的执行计划。

原因就在于sql 3使用了覆盖索引，避免了回表(随机I/O)，而使用索引又免去了文件排序(filesort)，使用索引的总成本远低于全表扫描的，优化器选择了使用索引。

2.4.基数对优化器判断执行计划的影响

以sql2为例：

# sql 2
EXPLAIN SELECT * from `students` WHERE age BETWEEN 15 AND 35 ORDER BY age

  
+----+-------------+----------+------------+------+---------------+--------+---------+--------+-------+----------+-----------------------------+
| id | select_type | table    | partitions | type | possible_keys | key    | key_len | ref    | rows  | filtered | Extra                       |
+----+-------------+----------+------------+------+---------------+--------+---------+--------+-------+----------+-----------------------------+
| 1  | SIMPLE      | students | <null>     | ALL  | idx_age_score | <null> | <null>  | <null> | 10125 | 51.92    | Using where; Using filesort |
+----+-------------+----------+------------+------+---------------+--------+---------+--------+-------+----------+-----------------------------+

略微改造一下，修改一下age的范围条件

  
# sql 2 改造
EXPLAIN SELECT * from `students` WHERE age BETWEEN 30 AND 35 ORDER BY age

得到结果

+----+-------------+----------+------------+-------+---------------+---------------+---------+--------+-------+----------+-----------------------+
| id | select_type | table    | partitions | type  | possible_keys | key           | key_len | ref    | rows  | filtered | Extra                 |
+----+-------------+----------+------------+-------+---------------+---------------+---------+--------+-------+----------+-----------------------+
| 1  | SIMPLE      | students | <null>     | range | idx_age_score | idx_age_score | 5       | <null> | 57136 | 100.0    | Using index condition |
+----+-------------+----------+------------+-------+---------------+---------------+---------+--------+-------+----------+-----------------------+

可以看到，改造后的sql 2 使用了索引idx_age_score

分析原因之前，我们需要先了解一个概念:索引基数

基数：即索引中唯一值的数量，基数越大，表示索引数据的独特性更高，反之更低。
一张表的索引基数可以使用：ANALYZE TABLE `table_name` 主动计算。
mysql也会自动采样更新：当表数据变化超过一定比例(1/16)是更新。

基数对索引的使用有什么影响呢？举个🌰

当你在一张表的`gender`(性别)上建立了索引，由于`gender`的值只可能有两种(常规情况下)，我们说`gender`索引基数为2。
mysql在尝试使用`gender`索引时，完成非聚簇索引的查询后，理想情况下需要回表的次数为:((数据总量/基数)*行数据长度)/16384 = 理想回表次数

这很好理解，因为(数据总量/基数)可以大致估算出某个索引值对应了多少行表数据

现在我们就知道为什么说’不要在区分度不高的列上建索引’了 区分度不高的列建索引 -> 索引基数低 -> 从非聚簇索引中查出的行id多 -> 回表次数多 -> I/O次数多(甚至是随机I/O)

回到上面对sql 2 进行改造的例子，我们先查看age索引的索引基数

  
SHOW INDEX FROM `students`

+----------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table    | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| students | 0          | PRIMARY       | 1            | id          | A         | 199977      | <null>   | <null> |      | BTREE      |         |               | YES     | <null>     |
| students | 1          | idx_age_score | 1            | age         | A         | 38          | <null>   | <null> | YES  | BTREE      |         |               | YES     | <null>     |
| students | 1          | idx_age_score | 2            | score       | A         | 4100        | <null>   | <null> | YES  | BTREE      |         |               | YES     | <null>     |
| students | 1          | idx_name      | 1            | name        | A         | 199977      | <null>   | <null> | YES  | BTREE      |         |               | YES     | <null>     |
+----------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

可以看到，age索引的基数(Cardinality)为38 预估单个age值对应的行数量为:数据总量/基数= 10000/38 = 263 行(预估)

我们再看看sql 2 与sql 2 改造，这两个sql语句的区别在于range字段不同

sql 2 : range 15 TO 35 ,总共21个索引值
sql 2 改造: range 30 TO 35,总共6个索引值

回表次数公式为：((索引值数量 * 每个值对应的行数量) * 行数据大小) / 16384 = 索引值数量 * ((每个值对应的行数量 * 行数据大小)/16384) 由此我们可以分别得到改造前和改造后的理想回表次数：

改造前：21 * ((每个值对应的行数量 * 行数据大小)/16384)
改造后：6 * ((每个值对应的行数量 * 行数据大小)/16384)
全部回表：38 * ((每个值对应的行数量 * 行数据大小)/16384)

理想情况下，改造前的回表数量全部回表的21/38,改造后的则为6/38

叠加上前面说的随机I/O成本基数为顺序I/O的4倍 优化器认为21/38的成本减少不足以抵消随机I/O产生的额外成本开销而6/38的成本减少是一个比较客观的数量级，可以抵消随机I/O产生的额外成本开销

于是，我们就看到了sql 2 和 sql 2 改造两个sql语句，截然不同的执行计划。

3.总结

常规意义下，索引会在某些特定场景失效

函数使用：WHERE UPPER(name) = ‘STUDENT1’，索引 idx_name 失效。
类型转换：WHERE name = 123，name 是 VARCHAR，触发转换。
复合索引未用左前缀：WHERE score = 90，idx_age_score 失效。
OR 跨索引：WHERE age = 25 OR name = ‘Student1’，无法合并。
LIKE 开头通配：WHERE name LIKE ‘%udent1’，无法前缀匹配。
低选择性：WHERE age > 0，覆盖大部分行。
排序不匹配：ORDER BY score，idx_age_score 仅支持 age。
不等操作：WHERE age != 25，范围优化受限。
统计过时：未 ANALYZE TABLE，优化器误判。
小表效应：表小，索引 + 回表成本高。

然而在常规意义之外，优化器也会基于成本计算而不使用索引因此，我们在构建索引时，需要综合表数据量、索引基数、回表次数等指标，谨慎使用索引。

另外，索引覆盖能对查询性能有很大的提升，在项目中我们也应该提高覆盖索引的使用。一套底层组合拳下来，能够让索引更加优雅好用。

技术

mysql

本文由作者按照 CC BY 4.0 进行授权