MySQL 入门（五）：索引与执行计划

MySQL 入门指南 · 第五章：理解查询怎样找到数据。

索引用额外的存储和维护成本，换取部分查询更有效的访问路径。它是否有用，取决于过滤条件、排序、返回列、数据分布和写入负担，不能仅根据“字段经常使用”就给每列加索引。

本章使用前面创建的 emp。固定数据只有六行，适合解释结果；优化器选择全表扫描也可能是合理决定，不能用这几行数据证明真实系统的性能。

一、InnoDB 的主键与二级索引

常见 InnoDB 索引可以理解为有序的 B+Tree 结构。聚簇索引的叶子记录保存行数据，通常以显式主键组织；二级索引保存索引键及用于定位行的主键值。

通过主键查找 → 聚簇索引 → 行数据

通过二级索引查找 → 索引键与主键
                         ↓ 若还需要其他列
                      聚簇索引 → 行数据

第二条路径中再取整行数据，通常称为回表。主键过宽还会增加二级索引的空间负担，因此主键设计也会影响整体存储。

创建约束时可能已经建立索引，外键也可能触发支持索引的创建。添加之前先看：

SHOW INDEX FROM emp;
SHOW CREATE TABLE emp;

二、为明确的查询建立联合索引

假设经常按部门和状态筛选员工，再按薪资返回：

SELECT id, salary
FROM emp
WHERE dept_id = 1 AND status = 1
ORDER BY salary DESC, id DESC;

可以考虑以下索引，创建一次即可：

CREATE INDEX idx_emp_dept_status_salary
ON emp (dept_id, status, salary);

它按部门、状态、薪资依次有序。InnoDB 二级索引还包含主键信息，但索引是否用于筛选或排序，应检查实际执行计划。

联合索引的前导列

这是理解联合索引的基本方法，不是“遗漏首列就绝不使用任何索引”的绝对规则。优化器可能选择跳跃扫描、全索引扫描或其他路径。

范围条件之后的列仍可能用于索引条件过滤或覆盖读取，不能简单说“后面的列全部失效”。是否还能完成排序，也要看具体条件和顺序。

三、覆盖索引与返回列

如果所需列都能从选定索引中得到，就可能减少回表：

SELECT id, salary
FROM emp
WHERE dept_id = 1 AND status = 1;

上例所选列与条件都在联合索引及其主键信息范围中。改为 SELECT * 后，需要的其他字段更多，可能必须读取完整记录。

覆盖索引是一条查询相对于某个索引的关系，不是另一个固定的索引种类。为了覆盖所有查询而建立很宽的索引，也会增加写入和缓存成本。

四、EXPLAIN：先看估计访问路径

EXPLAIN FORMAT=TRADITIONAL
SELECT id, salary
FROM emp
WHERE dept_id = 1 AND status = 1
ORDER BY salary DESC, id DESC;

常见字段：

type=index 通常表示全索引扫描，不等于按键精准查找。ALL 表示全表扫描，但小表或大比例命中时可能更合适，不能只按字段名称给计划打分。

不要误读 Extra

执行计划是解释访问路径的证据，要与结果行数、响应时间、并发和资源状态结合判断。

五、EXPLAIN ANALYZE 会真正执行

EXPLAIN ANALYZE
SELECT id, salary
FROM emp
WHERE dept_id = 1 AND status = 1;

它提供实际执行的时间、行数和循环等信息，可以比较估计与实际差异。它会执行所分析的语句，产生真实的读取、计算和可能的锁影响。 不要对高负载实例或未知代价的查询把它当作纯静态检查。

普通 EXPLAIN 主要用于计划估计；EXPLAIN ANALYZE 用于观察实际执行。语句支持范围和输出形式以具体版本为准，本指南只展示查询语句。

某些树形输出的行数、时间是每次循环的平均值，理解总工作量时还需要结合 loops，不能把每行数字直接相加。

六、让条件更容易利用索引

对列套函数

SELECT id, name
FROM emp
WHERE YEAR(entry_date) = 2023;

普通 entry_date 索引通常难以直接按这个表达式定位。可以改成日期区间：

SELECT id, name
FROM emp
WHERE entry_date >= '2023-01-01'
  AND entry_date <  '2024-01-01';

如果这类查询经常发生，可以根据工作负载考虑：

CREATE INDEX idx_emp_entry_date ON emp (entry_date);

函数索引等特性有其他设计方式，不必把“任何函数都一定不走索引”当作普遍规则。

类型与匹配方式

工号是字符串，条件也应绑定为字符串。隐式类型转换可能改变结果和索引使用，不要让应用把 'E0001' 或带前导零的编号当数值传入。

普通 B+Tree 对 LIKE '陈%' 可能有范围访问机会；对 LIKE '%陈%' 通常不能按同样方式定位前缀。全文检索或其他搜索需求，应选择与语言和匹配语义相符的方案。

不要只看扫描速度

查询返回很多大字段时，网络传输和客户端处理也可能成为主要成本。减少无用列和无界结果、采用合适分页，通常与索引设计同样重要。

七、索引的成本与调整流程

索引需要存储空间，插入、删除、更新相关列时还需要维护。越多的索引不等于越快的系统。

确认查询结果正确 → 观察真实慢查询与访问频率
                           ↓
                查看结构、数据分布和执行计划
                           ↓
                 选择少量候选变更并比较结果
                           ↓
                检查写入成本与并发影响后应用

统计信息明显偏离数据分布时，ANALYZE TABLE emp; 可以更新统计，但它是实际管理操作，也有开销和锁影响，不是普通 EXPLAIN 的同义命令。

删除索引的格式如下，仅在确认其用途和依赖后使用：

DROP INDEX 索引名 ON 表名;

不要删除仍用于唯一约束或外键支持的索引，也不要因为一条查询暂时未选择它，就断定它对所有业务都没有价值。

八、速查

上一篇：用户权限、事务与隔离级别 · 下一篇：视图、CTE 与窗口函数 · 指南目录。