MySQL 服务层中的优化器是如何决定使用哪个索引的？它计算成本的依据是什么？

MySQL 服务层中的优化器（Optimizer）是一个基于成本的优化器（Cost-Based Optimizer, CBO）。它在面对一条 SQL 语句时，会评估各种可能的执行计划（比如全表扫描、使用索引 A、使用索引 B），然后计算每种方案的“代价”（Cost），最终选择成本最低的那个方案来执行。

以下是它计算成本的依据和具体过程：

一、 优化器计算成本的两大核心依据

优化器计算代价主要基于两个维度的资源消耗：

1. I/O 成本（I/O Cost）： MySQL 的数据和索引都存储在磁盘上。每次将一个数据页（Page，默认 16KB）从磁盘读取到内存中，就产生一次 I/O 成本。这通常是数据库查询中最耗时的部分。

   • 默认常量： 在 MySQL 5.7+ 中，读取一个数据页的默认 I/O 成本常数（io_block_read_cost）通常是 1.0。

2. CPU 成本（CPU Cost）： 将数据读入内存后，MySQL 需要读取记录，并验证这些记录是否满足 SQL 中的 WHERE 条件，或者对记录进行排序。比较记录的消耗就是 CPU 成本。

   • 默认常量： 比较一条记录的默认 CPU 成本常数（row_evaluate_cost）通常是 0.2。

总成本 (Total Cost) = I/O 成本 + CPU 成本

二、 优化器如何获取计算需要的数据？

为了计算上述成本，优化器不能真的去扫描一遍表。它依赖于底层的统计信息（Statistics）。InnoDB 会在后台自动（或通过 ANALYZE TABLE 手动）收集这些信息，存储在 mysql.innodb_table_stats 和 mysql.innodb_index_stats 等系统表中：

• 表的大小（Data Size / Pages）： 聚簇索引（即全表数据）占用了多少个数据页。

• 记录总数（Rows）： 表中大约有多少行记录（这是一个估算值）。

• 索引的基数（Cardinality）： 索引列中包含多少个“不重复”的值。基数越高，代表索引的区分度越好（比如身份证号基数极高，性别基数极低）。优化器通过计算 查询记录数 / 基数 来预估使用该索引会扫描多少行数据。

三、 选择索引的具体博弈过程（举例说明）

假设有一张 10 万条记录的 orders 表，有一个二级索引 idx_status。执行语句：SELECT * FROM orders WHERE status = 'PAID';

优化器会分别计算“全表扫描”和“使用 idx_status 索引”的成本：

方案 A：全表扫描 (Full Table Scan)

• I/O 成本： 假设表总共有 1000 个数据页。成本 = 1000 * 1.0 = 1000。

• CPU 成本： 表有 100,000 行，需要对每一行判断状态是否为 'PAID'。成本 = 100,000 * 0.2 = 20,000。

• 总成本： 21,000。

方案 B：使用二级索引 idx_status (Index Scan + 回表) 使用二级索引并执行 SELECT * 意味着需要先查索引，再回表（回到主键索引查其他列）。

1. 根据统计信息预估，状态为 'PAID' 的记录大约有 20,000 行。

2. 查询二级索引成本： 预估扫描二级索引页的 I/O 成本，以及比较这 20,000 行二级索引记录的 CPU 成本。

3. 回表成本（最致命的部分）： 这 20,000 行数据可能会分散在不同的数据页中，极端情况下每次回表都会产生一次随机 I/O。回表的 I/O 成本极高（大约 20,000 * 1.0 = 20,000），加上回表后读取完整记录的 CPU 成本。

4. 总成本： 可能会超过 25,000。

优化器的决定： 如果发现方案 B 的总成本（25,000） > 方案 A（21,000），优化器就会放弃使用 idx_status 索引，直接选择全表扫描。这就是为什么有时你建了索引，但因为查询的数据量占全表比例较大，MySQL 却依然走全表扫描的原因。