在数据库查询中，我们常常会遇到这样的困惑：明明已经为表创建了合适的索引，但执行计划（EXPLAIN）却显示 MySQL 选择了全表扫描。这背后隐藏着一个关键因素——MySQL 优化器，它依据一系列预设的"成本常数"来做出查询计划的决策。

本文将深入探讨 MySQL 成本常数的机制，揭开查询优化背后不为人知的决策过程。

一、 索引失效的 SQL 场景

1.1 表结构示例

CREATE TABLE `mapping_filter_record` (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `source_type` INT(11) NOT NULL COMMENT '来源类型',
    `source_id` VARCHAR(64) NOT NULL COMMENT '来源方id',
    -- ... 其他字段
    PRIMARY KEY (`id`),
    KEY `idx_source_type` (`source_type`, `update_time`),
    KEY `idx_source_id` (`source_id`, `source_type`, `state`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = '商品发布拦截记录表';

1.2 耗时查询 SQL 以下 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;

执行计划的输出显示，MySQL 选择了主键索引（PRIMARY），而非我们期望的 idx_source_id 索引。

1.4 EXPLAIN 高级用法：JSON 格式 EXPLAIN 命令支持多种输出格式，其中 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（JSON 格式）：

{
  "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"
        }
      }
    }
  }
}

执行命令 2（强制指定 idx_source_id 索引）：

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（JSON 格式）：

{
  "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）的查询成本是相同的。然而，使用主键索引可以直接按索引顺序读取数据，避免了额外的排序操作（using_filesort: false）。因此，优化器选择了主键索引。

二、 SQL 查询执行流程解析

2.1 查询优化器 查询优化器的工作流程可概括为以下四步：

1. 解析 SQL，理解查询意图。
2. 生成多种可能的执行方案。
3. 基于内部的成本估算模型，计算每种方案的资源消耗。
4. 选择成本最低的方案执行。 （此处可插入流程图）

2.2 执行成本构成 SQL 查询的总执行成本由 CPU 成本和 I/O 成本构成。

• CPU 成本： 指在执行查询过程中，CPU 用于处理数据（如条件判断、排序等）所消耗的时间。
• I/O 成本： 指将数据从磁盘加载到内存所需的时间。对于 InnoDB 和 MyISAM 等存储引擎，数据和索引都存储在磁盘上，查询操作需要先将相关数据页读入内存。

2.3 MySQL 5.7 默认成本常数 MySQL 优化器使用一系列预定义的"成本常数"来估算执行计划的 I/O 和 CPU 开销。 Server 层成本常数示例： （此处可插入 Server 层成本常数表格或图片） 存储引擎层成本常数示例： （此处可插入存储引擎层成本常数表格或图片）

三、 执行成本详细分析

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）定位到主键 ID，然后进行回表查询。 （此处可插入命令 2 的执行计划图片，突出显示回表过程） 成本估算：

• 非聚簇索引查找 CPU 成本：近似为 1 (定位) * 0.2 (行评估成本)
• 非聚簇索引查找 I/O 成本：近似为 1 (定位) * 1 (块读成本)
• 回表查找 CPU 成本：3221 (行数) * 0.2 (行评估成本)
• 回表查找 I/O 成本：3221 (行数) * 1 (块读成本)
• 总成本：近似等于回表成本，约为 3865.2。这与执行计划中的 query\_cost: "3865.20" 相符。

3.3 命令 1（未指定索引）成本分析 虽然看起来是全表扫描（但实际访问方式是 index，使用主键索引），但优化器给出的成本与使用 idx_source_id 相同，这似乎与直觉相悖。 关键依据：

• rows\_examined\_per\_scan 的值： 命令 1 显示为 501。
• 数据分布观察： 表总数据量约 161 万，符合 WHERE 条件的数据约 3221 条。1615460 / 3221 ≈ 501。 推断：

1. MySQL 优化器基于"数据均匀分布"的假设，估算出平均每扫描 501 条记录就能找到一条符合条件的记录。它认为这种方式比使用 idx\_source\_id 并进行回表更优。
2. 对于 LIMIT 1 的查询，当优化器估算出的扫描行数（501）远小于通过 idx\_source\_id 索引扫描并排序所需的行数（3221）时，它会倾向于选择前者。 （此处的分析基于 MySQL 5.7 版本，可能存在官方未明确说明的内部逻辑，欢迎讨论指正。）

四、 优化策略

上述场景中，优化器的"数据均匀分布"假设在实际数据倾斜时失效，导致通过主键索引需要扫描大量数据（尤其是当符合条件的数据位于索引的较后部分时），造成性能瓶颈。当多个此类查询并发执行时，会显著增加数据库负载。 为解决此问题，可以引导优化器优先使用高效的二级索引。一种常用的方法是使用子查询：

SELECT *
FROM mapping_filter_record
WHERE id = (
    SELECT id
    FROM mapping_filter_record
    WHERE source_type = 9401003 AND source_id = '1814613774586351713'
    ORDER BY id ASC
    LIMIT 1
);

通过这种方式，内部子查询可以明确地利用 idx_source_id 索引，然后外部查询再根据获取到的 id 进行主键查找，从而显著提高查询效率。

五、 总结

• MySQL 优化器依赖成本估算模型而非直接的直观判断来选择执行计划。
• 成本常数是优化器进行决策的核心依据。
• 表统计信息的准确性直接影响优化器的选择。
• 深入理解优化器的成本计算机制是 SQL 性能优化的关键。 通过理解 MySQL 优化器的内部工作原理，我们可以更有效地设计索引策略和优化查询语句，最终提升数据库的整体性能。