前言：一个令人困惑的选择
你是否曾遇到这样的情况：明明表上有合适的索引，但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 );5 总结
这个案例深刻揭示了：
MySQL 优化器基于成本计算而非直觉进行决策；
成本常数是优化器评估执行计划的核心依据；
统计信息的准确性直接影响优化器的选择；
理解成本计算模型是 SQL 性能优化的关键。
通过深入理解 MySQL 优化器的工作原理，我们能够更好地设计索引和优化查询，提升数据库整体性能。
思考题：在你的项目中，是否遇到过类似索引失效的情况？欢迎在评论区分享你的经验和解决方案！