揭秘MySQL优化器：为何索引在手却选择全表扫描？

前言：一个令人困惑的选择

你是否曾遇到这样的情况：明明表上有合适的索引，但explain的结果却显示 MySQL 选择了全表扫描？这背后其实是一个看不见的指挥家——MySQL 优化器——基于一系列「成本常数」做出的决策。

今天，我们将深入探索 MySQL 成本常数的奥秘，揭开查询优化背后的神秘面纱。

1 一个费解的SQL现象

1.1 表结构

CREATE TABLE
`mapping_filter_record` (
    `id`bigint (20) NOTnull AUTO_INCREMENT,
    `source_type`int (11) NOTnullCOMMENT'来源类型',
    `source_id`varchar(64) NOTnullCOMMENT'来源方id',
    -- ... 其他字段省略
    PRIMARY KEY (`id`),
    KEY`idx_source_type` (`source_type`, `update_time`) USING BTREE,
    KEY`idx_source_id` (`source_id`, `source_type`, `state`) USING BTREE
  ) ENGINE = InnoDB AUTO_INCREMENT = 290240042300201321DEFAULTCHARSET = utf8mb4 COMMENT = '商品发布拦截记录表';

1.2 耗时较久的SQL(10秒以上)

select *
from dbzz_ypofflinemart.mapping_filter_record
WHERE (source_type = 9401003 and source_id = '1814613774586351713')
order by id asc
LIMIT 1;

1.3 分析下执行计划

需要表数据符合一定情况才会发生以下情况。

explain
select *
from dbzz_ypofflinemart.mapping_filter_record
WHERE (source_type = 9401003 and source_id = '1814613774586351713')
order by id asc
LIMIT 1;

执行计划结果：

令人困惑的是：使用了主键索引（PRIMARY），而非期望的 idx_source_id 索引。

1.4 explain的进阶用法

explain可以输出四种格式：传统格式、json格式、tree格式以及可视化输出。

传统的explain工具只告诉我们结果，没有告诉我们为什么。而json格式是四种格式里面输出信息最详尽的格式，里面包含了执行的成本信息。

我们加上format=json分析下结果。

执行 命令1 (未指定索引)：

explain format = json
select *
from dbzz_ypofflinemart.mapping_filter_record
WHERE (source_type = 9401003 and source_id = '1814613774586351713')
order by id asc
LIMIT 1;

得到执行计划1：

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "3865.20"
    },
    "ordering_operation": {
      "using_filesort": false,
      "table": {
        "table_name": "mapping_filter_record",
        "access_type": "index",
        "possible_keys": [
          "idx_source_type",
          "idx_source_id"
        ],
        "key": "PRIMARY",
        "used_key_parts": [
          "id"
        ],
        "key_length": "8",
        "rows_examined_per_scan": 501,
        "rows_produced_per_join": 3221,
        "filtered": "4.26",
        "cost_info": {
          "read_cost": "3221.00",
          "eval_cost": "644.20",
          "prefix_cost": "3865.20",
          "data_read_per_join": "92M"
        }
      }
    }
  }
}

强制指定使用idx_source_id索引，再分析执行计划。 执行 命令2 (指定索引)：

explain format = json
select *
from dbzz_ypofflinemart.mapping_filter_record
FORCE INDEX(idx_source_id)
WHERE (source_type = 9401003 and source_id = '1814613774586351713')
order by id asc
LIMIT 1;

得到执行计划2：

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "3865.20"
    },
    "ordering_operation": {
      "using_filesort": true,
      "table": {
        "table_name": "mapping_filter_record",
        "access_type": "ref",
        "possible_keys": [
          "idx_source_id"
        ],
        "key": "idx_source_id",
        "used_key_parts": [
          "source_id",
          "source_type"
        ],
        "key_length": "262",
        "ref": [
          "const",
          "const"
        ],
        "rows_examined_per_scan": 3221,
        "rows_produced_per_join": 3221,
        "filtered": "100.00",
        "cost_info": {
          "read_cost": "3221.00",
          "eval_cost": "644.20",
          "prefix_cost": "3865.20",
          "data_read_per_join": "92M"
        }
      }
    }
  }
}

1.5 分析执行计划

对比两个 SQL 的执行成本和排序：

命令	query_cost	using_filesort
命令1	3865.20	false
命令2	3865.20	true

优化器认为使用PRIMARY聚簇索引和idx_source_id二级索引的查询数据成本相同，但是使用PRIMARY聚簇索引可以按索引顺序读取，无需再次进行排序操作，因此优化器选择了使用PRIMARY聚簇索引来执行该 SQL。

2 查询 SQL 语句执行流程

2.1 查询优化器

优化器的工作流程可以简化为四个步骤：

1. 解析 SQL，理解查询意图；
2. 生成多种可能的执行方案；
3. 基于成本常数计算每种方案的代价；
4. 选择成本最低的方案执行。

2.2 执行成本

下面需要先介绍一些比较枯燥的概念。

SQL执行总成本 = CPU成本 + I/O成本

• CPU成本

读取以及检测记录是否满足对应的搜索条件、对结果集进行排序等这些操作损耗的时间称之为CPU成本。

• I/O成本

我们的表经常使用的MyISAM、InnoDB存储引擎都是将数据和索引都存储到磁盘上的，当我们想查询表中的记录时，需要先把数据或者索引加载到内存中然后再操作。将数据从磁盘加载到内存的过程所损耗的时间，称为I/O成本。

2.3 MySQL 5.7 版本的默认成本常数

在 MySQL 中，成本常数（Cost Constants）是查询优化器用来评估不同执行计划的资源消耗的固定数值。这些常数帮助优化器估算执行计划的I/O和CPU成本，从而选择最优的执行计划。

Server层一些操作对应的成本常数：

存储引擎层一些操作对应的成本常数：

3 执行成本分析

3.1 表统计信息

查询表的一些预估信息，用于成本计算。

show table status like 'mapping_filter_record';

Rows	Avg_row_length	Data_length	Max_data_length	Index_length	Data_free
1615460	9396	15180234752	0	552239104	4194304

3.2 命令2 (指定索引) 的执行成本分析

先根据非聚簇索引（idx_source_id）查询出对应数据的主键，然后通过主键回表查询、筛选需要的数据。

对命令2的执行成本计算如下：

• 非聚簇索引CPU成本 = 读取的记录数 × 读取一条记录的成本 = 1（等值查询定位到单个索引位置） × 0.2（row_evaluate_cost）
• 非聚簇索引I/O成本：1（等值查询定位到单个索引位置） × 1（io_block_read_cost）
• 回表CPU成本 = 3221（rows_examined_per_scan） × 0.2（row_evaluate_cost）
• 回表IO成本：3221（rows_examined_per_scan）× 1（io_block_read_cost）
• 总成本 = 3865.2（非聚簇索引的访问成本相对于回表成本可以忽略不计）

计算的成本3865.2和执行计划中的成本3865.20是一致的。

3.3 命令1 (未指定索引) 的执行成本分析

命令1使用主键索引，全表扫描的成本是要比正确使用非聚簇索引的成本要高很多的。实际得到的成本确实相同的。

依据1： 我们注意到rows_examined_per_scan(扫描行数)为501这是个很突兀的值。增加需要的结果数量得到以下的数据：

执行语句	使用的索引	扫描行数	实际执行时间
select * from xxx WHERE xxx order by id asc LIMIT 1;	PRIMARY	501	19.4秒
select * from xxx WHERE xxx order by id asc LIMIT 2;	PRIMARY	1003	20.2秒
select * from xxx WHERE xxx order by id asc LIMIT 6;	PRIMARY	3009	20.24秒
select * from xxx WHERE xxx order by id asc LIMIT 7;	idx_source_id	3221	0.026秒

依据2： 表中总数据为 1,615,460 条，符合WHERE条件的数据共 3,221，1,615,460 除以 3,221 约等于 501。

推断：

1. MySQL 优化器假设数据是均匀分布的，据此估算出每扫描 501 条数据，便可找到一条符合条件的记录。这样查询的效率比通过非聚簇索引再回表的效率高。
2. 当使用limit时，MySQL 的优化器会尝试通过全表扫描的方式来查询数据。当扫描行数小于非聚簇索引的扫描行数时，优化器以扫描行数 3221 作为依据计算成本。

以上是基于我遇到的情况基于 MySQL 5.7版本进行的分析，并未找到明确官方说明，有不当之处欢迎大家讨论、指正。

4 优化

虽然 MySQL 按照数据均匀分布的假设使用了主键索引，但实际的情况查询的数据大多在表中靠后的位置，就导致了需要扫描百万行才能找到第一条符合条件的数据。多个此类 SQL 同时执行，会造成数据库负载过高，进而对相关业务服务产生重大影响。

针对这种情况有很多优化思路。本例中我采用的优化方法是改为子查询，引导优化器优先使用高效的索引，避免其因成本误判而选择全表扫描。

SELECT *
FROM mapping_filter_record 
WHEREid = (
    SELECTid
    FROM mapping_filter_record 
    WHERE source_type = 9401003AND source_id = '1814613774586351713'
    ORDERBYidASC
    LIMIT1
);

5 总结

这个案例深刻揭示了：

• MySQL 优化器基于成本计算而非直觉进行决策；
• 成本常数是优化器评估执行计划的核心依据；
• 统计信息的准确性直接影响优化器的选择；
• 理解成本计算模型是 SQL 性能优化的关键。

通过深入理解 MySQL 优化器的工作原理，我们能够更好地设计索引和优化查询，提升数据库整体性能。
揭秘MySQL优化器：为何索引在手却选择全表扫描？ (qq.com)