揭秘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 查询优化器

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

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

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。

推断：

MySQL 优化器假设数据是均匀分布的，据此估算出每扫描 501 条数据，便可找到一条符合条件的记录。这样查询的效率比通过非聚簇索引再回表的效率高。
当使用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 );