MySQL的优化器的索引成本是怎么算出来的？

### 1. 前言 全表扫描适用于任何查询，但当表数据量较大时，全表扫描性能较差，因为需要扫描整个聚簇索引的叶子节点，带来大量的磁盘 I/O 和 CPU 开销。为了提升查询效率，可以给条件列添加索引，从而限制扫描区间，过滤大部分记录。在条件较复杂时，可以为多个列添加索引，MySQL 优化器会计算不同索引的执行成本，并选择成本最低的索引方案。了解索引成本的计算规则有助于理解优化器决策，并优化查询性能。 ### 2. 执行成本 MySQL 查询执行成本主要由两部分组成： - **I/O 成本**：记录存储在磁盘上，读取前需要从磁盘加载到内存。 - **CPU 成本**：加载到内存后，CPU 负责过滤、排序等操作。 InnoDB 引擎的两个重要成本常数： - 读取一个页面的 I/O 成本是 **1.0**。 - 检查一条记录是否符合条件的 CPU 成本是 **0.2**。 ### 2.1 单表查询成本 以查询 `SELECT * FROM T WHERE a BETWEEN 1 AND 100 AND b < 100 AND c LIKE '%xx%'` 为例，假设 `a` 和 `b` 有索引。查询过程如下： 1. **根据条件查找可能的索引**： 由于 `c` 列用到了左侧模糊匹配，不能使用索引。此查询可能使用 `idx_a` 和 `idx_b` 索引，执行方案有： - 全表扫描 - 使用 `idx_a` 索引查找符合条件的记录，再回表查询其他条件。 - 使用 `idx_b` 索引查找符合条件的记录，再回表查询其他条件。 2. **计算全表扫描成本**： 全表扫描即扫描整个聚簇索引的叶子节点，MySQL 简单计算整棵 B+ 树的扫描成本： - **I/O 成本**：页面数 × 1.0 - **CPU 成本**：总记录数 × 0.2 3. **计算不同索引的查询成本**： - **二级索引成本**：`idx_a` 索引的 `a BETWEEN 1 AND 100` 会形成一个扫描区间，优化器假设扫描一个区间的 I/O 成本为 1.0。 - **区间记录数**：如果 `a` 的区间记录数为 50 条，则二级索引 CPU 成本是 `50 * 0.2 = 10.0`。 - **回表成本**：50 次回表，每次 I/O 成本为 1.0，加上 CPU 成本 10.0。 - **最终成本**：`idx_a` 总成本为 `I/O 成本 + CPU 成本`。 优化器最终会选择成本最低的方案执行查询。 ### 2.2 基于索引统计数据的估算 在复杂查询（如 `WHERE a IN (1, 2, ..., 10000)`) 下，优化器可能使用基于索引统计数据的估算方式。使用 `SHOW INDEX FROM T;` 可查看索引统计数据，`Cardinality` 属性表示索引列中不重复值的数量，优化器可以据此预估区间记录数，避免耗时的 `Index dive`。 ### 2.3 修改成本常数 MySQL 提供了 `server_cost` 和 `engine_cost` 表以配置成本常数。例如： - `disk_temptable_row_cost` 表示向磁盘临时表写入一条记录的成本。 - `io_block_read_cost` 表示从磁盘读取一个块的 I/O 成本。 调整成本常数需谨慎，配置不当会影响查询效率。 ### 3. 总结 MySQL 优化器会评估所有可能的索引方案，并基于 I/O 和 CPU 成本选择最优方案执行查询。对于 InnoDB，引擎读取一个页面的 I/O 成本为 1.0，检查一条记录的 CPU 成本为 0.2。优化器在预估二级索引区间记录数时，优先使用 `Index dive`，如果预估开销大，则使用基于索引统计的简单估算。这一机制帮助 MySQL 实现高效的查询性能。