MySQL（进阶）万字吃透 MySQL SQL 调优：索引原理 + Explain 执行计划 + 全场景 SQL 优化 + 索引失效避坑实战

eternal__day

121人浏览 · 2026-07-10 21:57:25

eternal__day · 2026-07-10 21:57:25 发布

前言

线上业务随着用户量、数据量持续上涨，数据库很容易暴露出各类性能问题：单条慢 SQL 拖垮接口响应、高并发场景下大量线程阻塞卡顿、磁盘 IO 持续打满引发服务超时、数据库 CPU 长期 100% 等，这些问题绝大多数根源都集中在不合理的索引设计与不规范的 SQL 编写上。数据库调优是一套分层解决的完整体系，优化优先级从底层到上层依次为：硬件层→操作系统层→数据库配置层→表结构设计层→SQL 语句层。硬件扩容、参数调优只能缓解瓶颈，而 SQL 与索引优化属于低成本、高收益的核心优化手段，也是后端开发、DBA 日常工作中最常接触的内容，因此本文将全程聚焦 MySQL 索引与 SQL 语句调优实战。

读完本文你将完整掌握一套可直接落地的调优能力：熟练使用EXPLAIN执行计划分析 SQL 性能、吃透 InnoDB 索引底层原理、理解 MySQL 优化器内置的各类查询优化逻辑、熟记所有索引失效高频场景、形成一套线上慢 SQL 排查与优化标准化流程。

为保证所有理论均有实战验证，文中配套提供可直接执行的存储过程脚本，一键生成百万级测试数据表，文中全部执行计划对比、性能压测、索引优化案例均基于该测试表演示，方便大家本地复现、直观感受有无索引、不同索引写法带来的巨大性能差距。

第一部分：数据库调优整体认知（开篇铺垫）

1.1 数据库优化的多层维度

数据库性能瓶颈不能只盯着 SQL 修改，需要自上而下分层排查，不同层级优化成本、收益、实施难度差异巨大，完整优化体系分为四层：

一、硬件层面（兜底扩容，治标不治本）

当软件优化做到极致仍存在瓶颈，才考虑硬件升级，常见优化手段：

存储介质替换：传统机械硬盘 HDD 更换 SSD，大幅降低随机 IO 耗时（数据库绝大多数性能损耗来自随机磁盘读写）；
内存扩容：提升服务器物理内存，给 InnoDB 缓冲池分配更大空间，缓存热点数据减少磁盘访问；
CPU 升级：高并发计算、大量聚合 / 排序 SQL 场景，多核高主频 CPU 能缓解数据库 CPU 打满问题；
网络优化：数据库与应用服务器内网互通，降低网络传输延迟。

二、操作系统层面（底层基础优化）

数据库运行依托操作系统，系统参数不合理会限制数据库性能：

IO 调度策略调整、磁盘读写队列优化，减少磁盘阻塞；
内存相关内核参数：调整虚拟内存 swap 策略，避免 MySQL 数据被交换到磁盘；
文件句柄、进程最大连接数放开，支撑高并发数据库连接；
关闭不必要系统服务，释放服务器资源。

三、数据库软件层面（本文核心重点，低成本高收益）

硬件、系统优化只能缓解瓶颈，真正根治慢查询、高 IO 问题，核心集中在数据库软件本身，细分四大模块：

1. 表结构设计优化

表是数据存储最小单元，不合理表结构会从根源产生性能隐患：

字段类型选择：避免超大类型，数字优先用 tinyint/bigint 代替 varchar，减少存储空间；
存储引擎选型：读多写少业务可选 MyISAM，事务、并发更新业务强制使用 InnoDB；
行格式配置：归档冷数据使用压缩行格式，减少磁盘占用，提升 IO 效率；
约束规范：非空字段添加 NOT NULL，减少 NULL 值索引开销。

2. 索引设计优化（调优核心）

索引是提升查询速度最关键手段，也是 90% 慢 SQL 问题的根源：

单列索引、复合索引设计规范，遵循最左匹配原则；
覆盖索引设计，消除回表查询，降低随机 IO；
区分度低字段不建索引，避免索引失效、扫描效率低于全表；
控制索引数量，平衡查询速度与增删改索引维护成本。

3. SQL 语句优化（开发日常工作）

MySQL 优化器虽内置自动优化逻辑，但劣质 SQL 会直接绕过索引、产生大量临时表 / 文件排序：

WHERE 条件优化、范围查询、索引合并、索引下推等内置优化规则；
ORDER BY、GROUP BY、DISTINCT 去重优化，规避Using filesort/Using temporary；
多表 JOIN、LEFT/RIGHT 外连接优化，关联字段建立索引；
规避索引失效写法，不使用函数、隐式转换、前置模糊匹配。

4. MySQL 内存缓存参数配置

合理分配数据库内存资源，最大化缓存热点数据，减少磁盘交互：

innodb_buffer_pool_size：InnoDB 核心缓存，存放索引与数据页；
sort_buffer_size：排序内存，控制 filesort 是否落盘；
join_buffer_size：联表查询缓存；
read_buffer_size、read_rnd_buffer_size：读写缓冲区调优。

分层优化优先级总结

业务出现数据库卡顿、接口超时排查顺序： SQL & 索引优化 → 表结构优化 → MySQL 参数调优 → 操作系统内核优化 → 硬件扩容 先做软件层面低成本优化，最后再考虑硬件升级，避免盲目扩容造成资源浪费。

1.2 索引核心价值：百万数据对比实验

单纯讲解索引理论比较抽象，本节通过100 万 + 实测数据表，直观对比「有索引」和「无索引」两种场景的性能差距，让大家直观理解索引对查询速度的决定性作用。

1.2.1 实验环境：百万级测试表 index_demo 搭建

文档提供完整存储过程脚本，一键生成 100 万 + 测试数据，用于后续所有索引、执行计划演示。

表结构说明
- 主键：id（BIGINT 自增聚集索引）
- 普通字段：sn、name、mail、age、gender、password、class_id、create_time 等
- 初始仅给 class_id 创建单列普通索引，sn、age、name 无索引，方便做对照实验

存储过程使用步骤

修改语句结束符 delimiter //
创建存储过程 p_init_index_data，循环插入百万数据
还原结束符 delimiter ;
调用 CALL p_init_index_data(); 生成数据（耗时 20~100 分钟，视机器性能而定）

-- 修改SQL结束符
delimiter //

-- 创建存储过程
CREATE PROCEDURE p_init_index_data ()
BEGIN
  -- 生成学号和主键
  DECLARE id BIGINT DEFAULT 100000;
  -- 年龄
  DECLARE age TINYINT DEFAULT 18;
  -- 性别
  DECLARE gender BIGINT DEFAULT 1;
  -- 班级编号
  DECLARE class_id BIGINT DEFAULT 1;
  -- 循环计算
  DECLARE count INT DEFAULT 0;
  
  -- 创建表
  DROP TABLE IF EXISTS index_demo;
  CREATE TABLE index_demo (
    id bigint auto_increment,
    sn varchar(10) NOT NULL,
    name varchar(20) NOT NULL,
    mail VARCHAR(20),
    age TINYINT(1),
    gender TINYINT(1),
    password VARCHAR(36) NOT NULL,
    class_id bigint NOT NULL,
    create_time DATETIME NOT NULL,
    update_time DATETIME NOT NULL,
    PRIMARY KEY (id),
    index (class_id)
  );
  
  -- 插入一条测试数据
  INSERT INTO index_demo VALUES (100000, '100000', 'testUser', '100000@qq.com', 18, 1, UUID(), 1, NOW(), NOW());
  
  -- 循环构建数据
  WHILE count < 1000000 DO
    -- ID和学号
    SET id := id + 1;
    -- 年龄
    IF count % 10 = 0 THEN
      SET age := age + 1;
    END IF;
    
    IF age > 50 THEN
      SET age := 16;
    END IF;
    
    -- 性别
    IF  count % 3 = 0 THEN
      SET gender := 0;
    ELSE
      SET gender := 1;
    END IF;

    -- 班级编号
    SET class_id := class_id + 1;
    IF class_id > 10 THEN
      SET class_id := 1;
    END IF;
    
    -- 写入数据
    INSERT INTO index_demo VALUES (id, id, CONCAT('user_',id), CONCAT(id,'@qq.com'), age, gender, UUID(), class_id, NOW(), NOW());
    
    -- 更新count
    SET count := count + 1;
      
  END WHILE;

END //

-- 还原SQL结束符
delimiter ;

-- 调用存储过程，开始构建数据，大约20 - 100分钟左右
CALL p_init_index_data();

数据校验：执行 select count(*) from index_demo;，结果为 1000001 条数据，实验环境就绪。

1.2.2 对比 1：主键等值查询 VS 无索引字段查询

场景 A：主键 id 等值查询（命中主键索引 type=const）

sql

select id, sn, name, mail, age, gender, class_id 
from index_demo where id = 1020000;

执行耗时：0.00s（10ms 以内）
底层逻辑：主键聚集索引，通过 B + 树直接定位唯一一条数据，仅一次磁盘 IO。

场景 B：无索引字段 sn 等值查询（全表扫描 type=ALL）

sql

select id, sn, name, mail, age, gender, class_id 
from index_demo where sn = '1020000';

执行耗时：0.74s
性能差距：无索引查询耗时约为主键查询的 60 倍
底层逻辑：sn 无索引，MySQL 只能逐行遍历全表百万数据，大量顺序 IO 消耗时间。

核心结论：单条查询下，无索引就会触发全表扫描，查询效率断崖式下跌。

1.2.3 对比 2：mysqlslap 并发压测，验证无索引雪崩式性能衰减

单条 SQL 差距已经很大，高并发场景下无索引会引发性能雪崩，使用 MySQL 自带压测工具 mysqlslap 模拟并发请求验证。

压测 1：主键索引查询（100 并发、10000 次查询）

bash

运行

mysqlslap -uroot -p123456 --concurrency=100 --iterations=100
--create-schema=topic01 --engine=innodb --number-of-queries=10000 
--query="SELECT id, sn, name, mail, age, gender, class_id FROM index_demo WHERE id = 1020000;"

平均总耗时：0.203s
并发稳定，数据库 CPU、IO 无明显压力。

mysqlslap: [Warning] Using a password on the command line interface can be
insecure.
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 0.203 seconds -- 平均耗
时
Minimum number of seconds to run all queries: 0.203 seconds -- 最⼩耗
时
Maximum number of seconds to run all queries: 0.203 seconds -- 最⼤耗
时
Number of clients running queries: 100
Average number of queries per client: 100

压测 2：无索引 sn 字段查询（仅 3 并发、10 次查询）

bash

运行

mysqlslap -uroot -p123456 --concurrency=3 --iterations=1 \
--create-schema=topic01 --engine="innodb" --number-of-queries=10 \
--query "select id, sn, name, mail, age, gender, class_id from topic01.index_demo where sn = '1020000';"

平均总耗时：1001.484s
仅 3 个并发就产生上千秒耗时，并发量稍微提升，数据库线程会大量阻塞，接口直接超时。

mysqlslap: [Warning] Using a password on the command line interface can be
insecure.
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 1001.484 seconds -- 平均
耗时
Minimum number of seconds to run all queries: 1001.484 seconds -- 最⼩
耗时
Maximum number of seconds to run all queries: 1001.484 seconds -- 最⼤
耗时
Number of clients running queries: 100
Average number of queries per client: 100

现象总结

有索引：并发越高性能衰减平缓；无索引：少量并发就会造成 IO、CPU 打满，查询耗时指数级上涨，形成性能雪崩。

1.2.4 配套排查工具：show processlist

并发阻塞时，可通过该命令实时查看数据库运行线程，定位慢 SQL 阻塞根源：

sql

show processlist;

作用：展示当前所有连接、执行中的 SQL、执行状态、耗时；
典型异常：State 列为 Sending data，代表正在全表扫描大量数据，对应无索引慢查询；
线上定位手段：配合慢查询日志，长期记录执行超时 SQL，统一优化。

本小节总结

百万数据下，无索引查询速度比主键索引慢几十倍；
高并发场景，缺失索引会引发雪崩式性能衰减，严重影响业务；
索引的核心价值：大幅减少磁盘扫描行数，降低 IO 与 CPU 消耗；
配套工具：mysqlslap 做性能压测、show processlist 实时排查阻塞 SQL。

第二部分：Explain 执行计划核心详解（调优必备工具）

2.1 Explain 基础使用

1. 支持的 SQL 语句

EXPLAIN 不只是只能分析查询语句，DML 类语句全部支持，包含：SELECT、DELETE、UPDATE、REPLACE、INSERT。日常开发中绝大多数场景用于分析SELECT查询；更新、删除语句也可通过它提前判断是否走索引、是否全表扫描，避免线上大批量更新锁表、慢更新阻塞业务。

2. 两种常用语法

横向展示（默认）

sql

EXPLAIN SELECT * FROM index_demo WHERE id = 1020000;

输出是表格形式，字段多的时候横向阅读不方便。

竖排展示（推荐，\G 结尾）

sql

EXPLAIN SELECT * FROM index_demo WHERE id = 1020000\G

每条字段单独分行展示，字段含义一目了然，学习、排查慢 SQL 优先使用这种写法。

3. Explain 核心作用

判断 SQL 是否正常命中索引：查看key字段，为空代表无索引，大概率全表扫描；
评估扫描成本：通过rows预估扫描行数，数值越大性能越差；
识别性能隐患：通过Extra字段快速定位Using filesort文件排序、Using temporary临时表等严重损耗性能的行为；
看懂多表 / 子查询执行顺序：通过id区分主查询、子查询、UNION 合并查询；
对比优化前后差异：优化 SQL 前后分别执行 EXPLAIN，直观验证优化是否生效。

简单来说：Explain 是 SQL 调优的诊断工具，在 SQL 上线前执行，就能提前规避绝大多数慢 SQL 问题，不用等到线上出现卡顿再紧急排查。

2.2 Explain 输出 12 个字段逐行详解

执行 EXPLAIN \G 后会输出 12 个核心字段，每个字段都对应 SQL 的执行细节，是定位慢 SQL 的核心依据，下面逐个拆解含义 + 实战示例。

1. id：查询标识符

代表 SQL 中查询块的序号，用来区分主查询、子查询、UNION 合并查询，执行逻辑规则：

单表无嵌套查询：所有行 id 均为 1；
子查询：外层主查询 id=1，内层子查询 id 递增；
UNION 联合查询：第一条 SQL id=1，后续 union 语句 id 递增，最后 union 结果集 id 更大。

子查询示例

sql

EXPLAIN SELECT * FROM student 
WHERE id = (SELECT id FROM student WHERE name = '宋江')\G

第一行 id=1：外层主查询 PRIMARY
第二行 id=2：内层子查询 SUBQUERY

UNION 合并查询示例

sql

EXPLAIN SELECT * FROM student UNION SELECT * FROM student1\G

id=1：第一条查询 PRIMARY
id=2：union 第二条查询 UNION
id=3：union 合并结果集 UNION RESULT，表名显示 <union1,2>

2. select_type：查询类型

标记当前查询块的类别，常用值：

select_type 值	官方说明	详细解读 + 实战场景
SIMPLE	简单 SELECT (不使用 UNION 或子查询)	单表查询、无嵌套子查询、无 UNION 合并，最常见类型示例：`EXPLAIN SELECT * FROM index_demo WHERE id=100000\G`
PRIMARY	外层查询	存在子查询 / UNION 时，最外层主查询标记为 PRIMARY子查询示例中外层查询、UNION 第一条查询都是 PRIMARY
SUBQUERY	子查询中	`WHERE` 子句里独立子查询，无法被优化器展开到主查询示例：`SELECT * FROM student WHERE id=(SELECT id FROM student1)`，内层子查询行标记 SUBQUERY
UNION	UNION 中的第二个及之后的 SELECT 语句	UNION 拼接多条 SQL，第一条是 PRIMARY，第二条、第三条全部为 UNION例：`select * from a union select * from b union select * from c`，第 2、3 行 type 为 UNION
UNION RESULT	UNION 的结果	专门用来存放 UNION 合并后的临时数据集，table 列显示 `<union1,2>` 这种虚拟表，无实际索引扫描
INSERT	INSERT 语句	执行 `EXPLAIN INSERT ...` 时的查询类型，极少用来调优，一般只关注 SELECT
UPDATE	UPDATE 语句	`EXPLAIN UPDATE table SET xxx WHERE id=1` 时标识，可判断更新是否走索引、是否全表扫描锁全表
DELETE	DELETE 语句	`EXPLAIN DELETE FROM table WHERE id=1`，用于提前规避大批量无索引删除导致锁表、阻塞业务

3. table：当前操作表

表示当前行对应的查询表名称，存在两种特殊虚拟表标识：

<unionM,N>：代表合并 id=M、id=N 两条查询的结果；
<subqueryN>：代表子查询 id=N 产生的临时数据。

4. partitions：分区字段

仅分区表有值，普通 InnoDB/MyISAM 表统一为 NULL；如果业务做了分区分表，该字段会展示命中的分区名称。

5. type：连接类型（调优重中之重）

代表 MySQL 检索表数据的方式，性能从优到劣有完整排序，是衡量 SQL 好坏的核心指标，下文单独开完整章节详解。

6. possible_keys：可能使用的索引

优化器分析 WHERE 条件后，筛选出理论上可以使用的索引列表；注意：只是候选索引，不代表 SQL 一定会使用，最终实际索引看 key 字段。值为 NULL：当前查询没有任何可用索引，大概率全表扫描。

7. key：实际使用的索引

SQL 最终真正选用的索引，来自 possible_keys 候选集合；

key 不为 NULL：成功命中索引；
key 为 NULL：未使用任何索引，需要优化。

8. key_len：索引占用字节长度

代表本次索引检索用到的索引字节数，用来判断复合索引使用了前几列；规则：字段长度、是否允许为空都会影响 key_len 大小，数值越大代表使用的索引列越多。

9. ref：索引匹配对象

描述索引和什么值做等值匹配：

const：和常量匹配（如 where id=1001）；
字段名：和其他表的关联字段匹配（联表查询 eq_ref 场景）；
func：索引匹配函数计算结果，存在索引失效风险，可搭配 SHOW WARNINGS 查看完整函数。

10. rows：预估扫描行数

InnoDB 基于索引统计信息估算需要扫描的数据行数，纯估算值，非精确值；核心判断标准：rows 数值越小，查询开销越低；百万级数据 rows 几十万、上百万代表全表扫描，必须优化。

11. filtered：过滤百分比

代表扫描到 rows 条数据后，经过 WHERE 条件过滤后剩余数据的占比，取值 0~100；计算公式：真实匹配行数 ≈ rows * filtered / 100 示例：rows=1000，filtered=50 → 最终符合条件数据约 500 条； filtered 越接近 100，过滤效率越高。

12. Extra：附加执行信息（性能隐患聚集地）

存储 MySQL 执行 SQL 的额外细节，绝大多数性能问题都在这里体现：

Using index：覆盖索引，无回表，最优标识；
Using where：需要服务层过滤数据；
Using index condition：开启索引下推 ICP，减少回表；
Using filesort：文件排序，无索引排序，严重损耗 IO / 内存；
Using temporary：创建临时表做 GROUP BY/DISTINCT，大表性能灾难；该字段会单独开设小节详细拆解各类标识优化方案。

2.3 type 列全类型详解（性能从优到劣排序）

type 是 EXPLAIN 里衡量 SQL 性能最核心指标，代表 MySQL 检索数据的访问方式，性能优先级从上到下依次变差，线上优化目标尽量达到 range/ref/const，坚决杜绝 ALL 全表扫描。下面逐个拆解定义、场景、实战 SQL。

1. system（最优，特殊场景）

说明：仅 MyISAM 引擎表，且表中仅有 1 条数据，是 const 的特殊极限情况；InnoDB 单条数据不会出现 system。
示例：

sql

CREATE TABLE t_system(id INT PRIMARY KEY,name VARCHAR(20)) ENGINE=MyISAM;
INSERT INTO t_system VALUES(1,"test");
EXPLAIN SELECT * FROM t_system;

优化建议：业务几乎遇不到，无需刻意关注。

2. const（常量查询，最优常用级别）

说明：通过主键索引 / 唯一索引做等值常量匹配，最多匹配 1 行，MySQL 可直接把数据当成常量处理，查询速度极快。
触发条件：主键=常量 / 唯一索引=常量
实战案例（主键查询）

sql

EXPLAIN SELECT id,sn,name FROM index_demo WHERE id = 1020000\G

补充：给 sn 建立唯一索引后，where sn='1020000' 同样 type=const。

3. eq_ref（多表关联最优）

说明：多表 JOIN 场景，关联条件使用主键 / 唯一非空索引等值匹配，关联表每行仅匹配一条数据。
适用场景：一对一关联，外键关联唯一主键。
实战：student 左连 account，关联字段 student_id 唯一索引

sql

EXPLAIN SELECT * FROM student s,account a WHERE s.id = a.student_id\G

第二张表 type 为 eq_ref。

4. ref（普通索引等值查询，高频理想级别）

说明：使用普通单列索引做等值匹配，可能匹配多条数据；日常开发最常见的优质索引类型。
实战：class_id 建立普通索引，等值查询

sql

EXPLAIN SELECT * FROM index_demo WHERE class_id = 1\G

5. ref_or_null

说明：在 ref 基础上，同时查询索引列等值 + IS NULL，索引列允许为 NULL 才会触发。
场景：where name = 'xxx' or name is null
执行计划 type=ref_or_null，Extra 常伴随Using index condition索引下推。

6. index_merge（多索引合并）

说明：单表同时使用多个独立索引扫描，再合并结果集，分为 3 种合并算法，在 Extra 中标识：
1. Using intersect：多索引 AND 取交集
2. Using union：多索引 OR 等值取并集
3. Using sort_union：多索引 OR 范围合并（需排序主键）
示例：where name='xxx' or id=123（name、id 分别存在独立索引）

sql

EXPLAIN SELECT * FROM index_demo WHERE name='user_1020021' OR id=1030300\G

type 会显示 index_merge。

7. unique_subquery

说明：IN 子查询匹配外层主键 / 唯一索引，优化器替换子查询，避免多次扫描。
语法模板：value IN (SELECT 主键 FROM 表 where ...)

8. index_subquery

说明：和 unique_subquery 逻辑一致，区别是子查询返回普通索引列而非主键。
语法模板：value IN (SELECT 普通索引列 FROM 表 where ...)

9. range（索引范围扫描，可接受级别）

说明：基于索引做区间检索，仅扫描索引指定范围，远优于全表扫描；
触发运算符：> < >= <= BETWEEN IN、前缀模糊LIKE 'abc%'
案例 1：主键区间查询

sql

EXPLAIN SELECT * FROM index_demo WHERE id>110000 AND id<200000\G

案例 2：IN 批量匹配

sql

EXPLAIN SELECT * FROM index_demo WHERE id IN (100000,110000,120000)\G

注意：复合索引中第一个范围字段之后，其他索引列失效。

10. index（全索引树扫描，需优化）

说明：遍历完整棵索引树，不扫描整张数据表；比 ALL 好，但依然会大量 IO，需要优化。
触发场景：
1. 查询仅索引列，无 WHERE 条件，直接 ORDER BY 索引；
2. 仅使用索引排序，无过滤条件；
示例：id 存在唯一索引，无 where 直接排序

sql

EXPLAIN SELECT * FROM index_demo ORDER BY id LIMIT 10\G

11. ALL（全表扫描，最差，必须优化）

说明：完整遍历整张数据表所有行，百万级数据耗时极长，并发场景直接引发接口超时、CPU 打满。
触发原因：无可用索引、索引失效、优化器判定索引效率更低。
示例：age 无索引，按 age 查询

sql

EXPLAIN SELECT * FROM index_demo WHERE age = 18\G

2.4 type 各类型适用场景 + 对应优化方案总结表

一、完整对照表（性能从优到劣）

表格

type 类型	适用业务场景	当前 SQL 问题	推荐优化手段
system	MyISAM 表仅 1 条数据，几乎无业务场景	无问题，天然最优	无需优化，不用刻意追求
const	1. 主键等值查询2. 唯一索引等值查询，最多 1 条匹配数据	无性能问题，查询毫秒级	高频单条查询优先使用主键 / 唯一键查询；高频字段加唯一索引
eq_ref	多表 JOIN 一对一关联，关联字段是主键 / 唯一索引	无性能问题，联表最优方案	关联外键建立唯一索引，优先内连接 / 左连接匹配主键
ref	普通单列索引等值查询，匹配多条数据（如班级、状态）	正常可接受，无优化强制要求	等值过滤字段建立普通索引；搭配覆盖索引消除回表
ref_or_null	索引列同时等值匹配 + IS NULL 查询（字段允许 NULL）	性能正常，属于索引下推优化场景	字段尽量设置 NOT NULL 规避；必须查 NULL 则保留索引即可
index_merge	WHERE 多条件 OR/AND，分别命中多个独立单列索引	效果一般，多索引合并开销略大	合并多个单列索引为复合索引，替代 index_merge，性能更好
unique_subquery	IN 子查询，子查询返回主键 / 唯一索引	优化器自动优化，性能良好	子查询返回主键，避免子查询返回普通字段
index_subquery	IN 子查询，子查询返回普通索引列	比 unique_subquery 弱，可优化	子查询尽量查询主键，替换普通索引列
range	索引区间查询：> < >= <= BETWEEN IN / 前缀 like 'xx%'	可接受，但注意复合索引范围失效规则	1. 复合索引范围条件放最后一列2. 避免超大范围扫描，分页限制数据量
index	无 WHERE 条件，仅依靠索引排序全索引树遍历	IO 开销大，必须整改	增加 WHERE 过滤条件缩小扫描范围；使用覆盖索引减少回表
ALL	无索引 / 索引失效，全表逐行扫描	严重性能灾难，高并发直接超时	1. WHERE 条件字段新增索引2. 改写 SQL 解决索引失效（隐式转换、函数、前置 % 等）

二、分场景快速选型 & 优化指南

1. 单条精准查询（根据唯一标识查详情）

目标 type：const
实现方式：使用主键、唯一索引等值查询
反面场景：无唯一索引触发ALL，必须建唯一索引

2. 多表关联查询（一对一一对多）

最优 type：eq_ref
优化要点：JOIN 关联字段建立主键 / 唯一索引；避免无索引关联触发ALL

3. 等值批量筛选（如班级、状态、类型）

目标 type：ref / ref_or_null
优化：筛选字段建普通索引；字段尽量 NOT NULL 减少 ref_or_null 场景

4. 多字段 OR 查询（多个独立索引）

当前 type：index_merge
优化方案：把多个单列索引合并为复合索引，直接消除 index_merge，性能提升明显

5. IN、区间、范围模糊查询

目标 type：range
避坑：复合索引第一个范围字段后，后续索引列失效；超大范围分页需限制 offset 深度

6. 子查询 IN 场景

优先unique_subquery，避免index_subquery
优化：子查询查询主键，不要查询普通索引字段

7. 无过滤条件仅排序分页

当前 type：index，存在性能损耗
优化：增加 WHERE 缩小数据范围；使用覆盖索引减少回表 IO

8. 全表扫描 type=ALL（线上红线）

触发原因汇总：

查询字段无任何索引
SQL 导致索引失效（函数运算、隐式转换、like % xxx、不满足最左匹配等）
过滤条件区分度极低，优化器放弃索引走全表扫描统一优化方案：

高频查询字段新建复合索引（等值在前，范围在后）
改写 SQL 规避所有索引失效场景
低区分度字段不要单独建索引，配合其他字段组成复合索引

三、线上调优分级标准（落地执行规范）

最优档（无需优化）：system / const / eq_ref
合格档（无需改动，可微调提升）：ref / ref_or_null / range / index_merge / unique_subquery
整改档（必须优化后上线）：index / index_subquery
禁止档（绝对不能上线）：ALL，必须重构索引或改写 SQL

2.5 Extra 列关键标识解读（线上高频性能坑）

Extra 字段存储 SQL 执行的附加逻辑，是排查性能瓶颈最核心的字段，出现 Using filesort、Using temporary 代表 SQL 存在严重性能损耗，必须优先优化；Using index 是最优标识，代表实现覆盖索引无回表。下面逐个拆解含义、产生场景、示例、优化方案。

1. Using index（最优标识：覆盖索引，无回表）

含义

查询需要的所有字段全部存在当前使用的索引中，InnoDB 仅扫描二级索引树即可拿到全部数据，不需要回主键聚集索引查询完整行数据，大幅减少随机 IO。

触发条件

查询字段全部是索引包含的列（含主键，InnoDB 二级索引自带主键）；
WHERE 条件使用该索引；

实战示例

复合索引 idx_mail_age_classId(mail,age,class_id)

sql

EXPLAIN SELECT mail,age,class_id FROM index_demo WHERE mail = '1020000@qq.com'\G

Extra 输出 Using index，实现索引覆盖。

优化建议

开发规范：禁止 select *，只查询业务需要的字段，配合复合覆盖索引，消除回表。

2. Using where

含义

MySQL 服务层需要对存储引擎返回的数据再进行条件过滤，分两种完全不同的场景，好坏不能一概而论：

type=ALL（全表扫描 + Using where）：最差。无索引，逐行扫描全表，再过滤条件，百万数据耗时极高；示例：EXPLAIN SELECT * FROM index_demo WHERE age = 18\G
type=range/ref（索引扫描 + Using where）：正常现象。索引只能过滤部分条件，剩余条件在服务层过滤，性能可接受；示例：主键范围查询 EXPLAIN SELECT * FROM index_demo WHERE id < 102000\G

优化判断

只有搭配 type=ALL 时才需要建索引优化；搭配索引扫描无需处理。

3. Using filesort（高危：文件排序，必须优化）

含义

无法利用索引有序性完成排序，MySQL 需要开辟内存sort_buffer_size进行排序；若结果集超出内存上限，会落地磁盘临时文件排序，IO 暴涨、接口超时。

触发场景

ORDER BY 字段无索引；
ORDER BY 不遵循复合索引最左匹配；
排序字段使用函数、运算、跨索引排序；

实战示例

sql

EXPLAIN SELECT * FROM index_demo WHERE id < 1020000 ORDER BY age LIMIT 10\G

Extra 出现 Using where; Using filesort

优化方案

建立联合索引，将 WHERE 条件字段、ORDER BY 字段按顺序放入复合索引；
调大 sort_buffer_size 仅临时缓解，治本还是优化索引。

4. Using temporary（高危：临时表，大表灾难）

含义

执行 GROUP BY、DISTINCT 分组去重时，没有可用有序索引，MySQL 创建临时内存表存储分组数据，大数据量内存不足则落盘磁盘，性能暴跌。

触发场景

GROUP BY / DISTINCT 字段无索引、不满足复合索引最左原则；

实战示例

sql

EXPLAIN SELECT AVG(age) FROM index_demo GROUP BY gender\G

Extra 输出 Using temporary

优化方案

给 GROUP BY 分组字段建立复合索引，利用索引天然有序特性，跳过临时表创建。

5. Using index condition（ICP 索引下推优化，减少回表）

含义

索引下推优化，MySQL 将复合索引中剩余过滤条件下推到存储引擎层执行过滤，不用全部回表后再过滤，大幅减少回表次数。

触发场景

复合索引、范围查询、模糊前缀匹配，同时 WHERE 包含索引内其他字段条件；

实战示例

复合索引 idx_age_class_id(age, class_id)

sql

EXPLAIN SELECT * FROM index_demo WHERE age < 18 AND class_id > 1\G

Extra 出现 Using index condition

补充说明

MySQL 5.6 及以上默认开启 ICP，无需手动配置，是内置优化手段，出现该标识代表索引利用更充分。

补充：多标识组合常见情况

Using where; Using filesort：有索引过滤，但排序无索引，优先优化排序索引；
Using index condition; Using where：索引下推正常生效，无需优化；
Using temporary; Using filesort：分组 + 排序均无索引，双重性能损耗，重点优化；
Using index：纯覆盖索引，最优执行状态。

2.6 Explain 实战案例对比

本节通过两组对比 SQL，直观体现有主键索引和无索引字段两种场景下执行计划的巨大差异，帮你快速读懂调优核心指标。

案例 1：主键等值查询（最优，type=const，rows=1）

测试 SQL

sql

EXPLAIN SELECT id, sn, name, mail, age, gender, class_id 
FROM index_demo WHERE id = 1020000\G

执行计划关键字段解读

plaintext

select_type: SIMPLE
table: index_demo
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: const
rows: 1
filtered: 100.00
Extra: NULL

核心优势拆解

type=const：主键唯一等值匹配，MySQL 直接把匹配行当作常量，无需遍历数据；
key=PRIMARY：成功命中主键聚集索引；
rows=1：预估仅扫描 1 行数据，无多余 IO；
filtered=100%：索引直接精准匹配，无多余数据过滤；
耗时表现：百万数据表查询耗时 0.00s，并发压测性能稳定。

案例 2：无索引字段等值查询（最差，type=ALL，百万级扫描）

前置说明

sn 字段未创建唯一索引前，无任何索引，执行相同条件查询：

sql

EXPLAIN SELECT id, sn, name, mail, age, gender, class_id 
FROM index_demo WHERE sn = '1020000'\G

执行计划关键字段解读

plaintext

select_type: SIMPLE
table: index_demo
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 982666
filtered: 10.00
Extra: Using where

性能问题拆解

type=ALL：全表扫描，最差访问类型；
possible_keys/key 均为 NULL：无任何索引可用；
rows≈98万：需要遍历近百万条数据逐行匹配；
filtered=10%：98 万行扫描后仅 10% 数据符合条件，大量无效扫描；
Extra: Using where：全表扫描后在 MySQL 服务层过滤数据，CPU、磁盘 IO 消耗巨大；
耗时表现：单条查询 0.59s，并发场景下耗时指数级上涨，极易造成接口超时、数据库阻塞。

两组案例对比总结

表格

指标	主键索引查询	无索引字段查询
type	const（最优）	ALL（最差）
key	PRIMARY	NULL
rows	1	近百万
Extra	无额外开销	Using where 全表过滤
单条耗时	<10ms	0.59s

调优结论

等值查询优先使用主键 / 唯一索引，实现type=const极致性能；
业务高频查询字段若无索引，会触发全表扫描，必须新建索引优化；
上线前必须通过EXPLAIN校验 SQL，杜绝type=ALL的慢 SQL 上线。

拓展优化演示

给 sn 创建唯一索引后再次执行第二条 SQL，执行计划会直接变为type=const，扫描行数降至 1，性能和主键查询持平。

sql

ALTER TABLE index_demo ADD UNIQUE un_sn(sn);

第三部分：索引核心概念：覆盖索引 & 回表查询

3.1 InnoDB 索引底层基础铺垫

要搞懂覆盖索引、回表查询，必须先理解 InnoDB 的 B + 树聚集索引存储结构，这是两个概念的底层根源。

1. InnoDB 默认索引结构：B + 树聚集索引

聚集索引（主键索引）
- 每张 InnoDB 表必须有且仅有一个聚集索引，默认以主键作为聚集索引；
- B + 树叶子节点完整存储整行数据（所有字段的值）；
- 主键有序排列，范围查询、主键等值查询效率极高。
二级索引（普通索引 / 唯一索引 / 复合索引）
- 所有自建索引都属于二级索引；
- 二级索引 B + 树叶子节点不存完整行数据，只存储两部分内容：索引列的值 + 该行对应的主键值；
- 二级索引树独立于聚集索引树，体积远小于整表。

2. 回表的底层逻辑（提前铺垫）

当我们通过二级索引查到索引列 + 主键后，如果 SQL 需要查询不在索引内的字段，MySQL 必须拿着叶子节点里存储的主键，再去聚集索引 B + 树中完整读取一行数据，这个二次查表动作就是回表查询。回表会产生大量随机 IO，是慢 SQL 重要诱因。

3. 覆盖索引底层逻辑（提前铺垫）

如果一条 SQL 需要查询的所有字段，全部包含在二级索引本身中，不需要借助主键去聚集索引查找数据，直接从二级索引叶子节点拿到全部所需数据，跳过回表操作，这种场景就是覆盖索引，执行计划 Extra 会显示Using index。

4. 举个简易结构示例

表：index_demo，主键id，复合索引 idx_mail_age_classId(mail,age,class_id) 二级索引叶子存储结构： (mail值, age值, class_id值, 主键id)

查询 select mail,age,class_id from table where mail='xxx'：仅读取索引内字段，无需回表 → 覆盖索引
查询 select * from table where mail='xxx'：需要 name、password 等不在索引的字段，拿着 id 去主键树查完整行 → 回表查询

3.2 覆盖索引（Using index）

3.2.1 覆盖索引完整定义

查询所需的所有返回字段，全部存在于当前使用的二级索引叶子节点中，MySQL 仅扫描二级索引树就能拿到全部数据，不需要拿着主键去聚集索引查询完整行数据，跳过回表流程，这种场景就是覆盖索引。在 EXPLAIN 的 Extra 字段会标识 Using index，是 SQL 性能最优标识之一。

底层原理回顾：InnoDB 二级索引叶子节点 = 索引列 + 主键；主键本身天然存在于索引内，查询主键不会触发回表。

3.2.2 实战：创建复合索引

基于测试表 index_demo 创建复合索引：

sql

CREATE INDEX idx_mail_age_classId ON index_demo(mail, age, class_id);

该索引叶子节点存储数据结构：mail, age, class_id, 主键id。

3.2.3 覆盖索引触发案例

场景 1：仅查询索引包含的字段

sql

EXPLAIN SELECT mail, age, class_id FROM index_demo 
WHERE mail = '1020000@qq.com'\G

执行计划关键信息：

type: ref 正常走索引
Extra: Using index 触发覆盖索引，无回表操作，仅扫描二级索引树即可返回数据。

场景 2：查询包含主键（同样满足覆盖索引）

主键自带在二级索引叶子中，查询主键 + 索引字段依然生效：

sql

EXPLAIN SELECT id, mail, age FROM index_demo 
WHERE mail = '1020000@qq.com'\G

Extra 依旧为 Using index。

反例：不满足覆盖索引（触发回表）

使用 select * 查询所有字段，包含 name、password、create_time 等不在索引中的字段：

sql

EXPLAIN SELECT * FROM index_demo WHERE mail = '1020000@qq.com'\G

Extra 无 Using index，需要拿着索引里的主键 id 回聚集索引读取完整行。

3.2.4 覆盖索引核心性能优势

消除随机 IO，大幅降低磁盘开销 回表需要根据主键随机访问聚集索引，随机 IO 速度远慢于顺序索引扫描；覆盖索引只遍历一棵索引树，全是顺序 IO。
索引体积更小，内存缓存命中率更高 二级索引远小于整表数据，更容易被 InnoDB Buffer Pool 缓存，减少磁盘读取。
减少数据传输量 索引只存储业务需要的少量字段，不用读取整行冗余字段，网络、内存开销降低。
高并发场景性能提升明显 规避大量回表带来的磁盘争抢，数据库 CPU、IO 压力显著下降，接口响应速度大幅缩短。

3.3 回表查询

3.3.1 回表查询定义

InnoDB 二级索引叶子节点只保存索引列 + 主键 id，不会存储表中全部字段。当 SQL 查询的字段包含不在当前索引内的列时，MySQL 只能先通过二级索引匹配到数据，拿到主键 id，再根据主键去聚集索引（主键 B + 树）查找完整行数据，这个二次查表的动作就叫回表查询。

3.3.2 实战案例（触发回表）

沿用复合索引 idx_mail_age_classId(mail, age, class_id)

sql

EXPLAIN SELECT * FROM index_demo WHERE mail = '1020000@qq.com'\G

执行计划关键特征：

key: idx_mail_age_classId：正常命中二级索引；
Extra 无 Using index；
底层流程： 1）扫描二级索引，匹配 mail 条件，得到 mail,age,class_id,id； 2）需要 name、password、create_time 等不在索引里的字段； 3）拿着 id 去主键聚集索引读取完整一行数据，完成回表。

3.3.3 回表带来的性能损耗

产生大量随机 IO 二级索引扫描是顺序 IO；但回表是通过主键随机定位聚集索引节点，随机 IO 磁盘读写速度远慢于顺序 IO，数据量大时 IO 直接打满。
内存开销翻倍 需要同时加载二级索引页 + 聚集索引数据页，Buffer Pool 缓存压力上升，缓存命中率下降，频繁刷磁盘。
高并发场景性能雪崩 并发量越高，回表次数成倍增加，数据库 CPU、磁盘 IO 持续拉满，接口超时、线程阻塞频发。
分页、列表查询损耗放大 分页查询几十上百条数据，每条都要回表，扫描成本成倍上涨。

3.4 统一优化方案

1. 业务代码禁止 `SELECT *`

只查询业务真正需要的字段，减少额外字段引发的回表。

反面：select * from index_demo where mail = ?
正面：select id,mail,age,class_id from index_demo where mail = ?

2. 高频查询构建覆盖复合索引

把 WHERE 过滤字段、SELECT 查询字段、ORDER BY 排序字段整合进同一复合索引，实现 Using index 完全消除回表。示例：业务经常根据 mail 查询，返回 mail、age、class_id，直接创建覆盖索引：

sql

CREATE INDEX idx_mail_age_classId ON index_demo(mail,age,class_id);

3. 折中方案：无法做覆盖索引时减少回表行数

增加 WHERE 条件缩小扫描范围，减少回表总次数；
使用 LIMIT 限制返回行数，避免一次性大量回表；
避免超大范围查询（如不带分页的全量范围扫描）。

4. 落地判断标准

执行 SQL 后用 EXPLAIN 校验：

Extra 出现 Using index：无回表，优化达标；
Extra 无 Using index：存在回表，需要调整查询字段或新增覆盖索引。

第四部分：MySQL 内置查询优化器全场景优化规则

MySQL 自带查询优化器，会在执行 SQL 前自动重写、简化、调整执行逻辑，减少扫描开销；理解内置优化逻辑，能写出更贴合优化器、充分利用索引的 SQL。

4.1 WHERE 子句自动优化

优化器会自动化简冗余、无效逻辑，减少检索成本：

语法化简
- 去除多余括号，合并多层 AND/OR；
- 合并常量表达式，提前计算固定值；
- 剔除永远成立 / 永远不成立的无效条件。示例：(a<256 AND a TINYINT UNSIGNED) 直接优化为恒真条件，不再走索引扫描。
执行顺序优化：优先扫描常量表 执行联表 / 多条件查询时，优先读取 system、const 类型表（主键 / 唯一索引等值单条数据），缩小后续关联扫描范围。
HAVING 与 WHERE 合并 无聚合函数、GROUP BY 时，HAVING 条件会直接合并到 WHERE，提前过滤数据，减少分组行数。
COUNT (*) 无 WHERE 快速优化 MyISAM 直接读取表总行数；InnoDB 会选用最小二级索引扫描，不用遍历全表。

4.2 范围查询优化（Range 访问逻辑）

range 是索引区间扫描，是业务最常用的索引访问方式，优化器有固定解析规则：

单字段索引可触发 range 的运算符 =、<=>、IN、BETWEEN、>、<、>=、<=、前缀模糊 LIKE 'xxx%'。
复合索引核心限制：范围字段截断索引 复合索引中第一个出现范围条件的列，该列之后所有索引列失效，无法再使用索引过滤。例：索引 (age, class_id)，where age < 18 and class_id = 1，仅 age 走索引，class_id 无法索引过滤。
优化器自动区间合并 多条重叠、连续的范围条件会自动合并为一个区间；无交集、无效区间直接剔除，减少索引扫描次数。

4.3 索引合并优化 Index Merge

适用场景

单表存在多个独立单列索引，WHERE 使用 AND / OR 连接不同索引字段，优化器同时扫描多个索引，合并主键结果集，执行计划 type = index_merge。

三种合并算法

intersect（交集） 多个索引用 AND 连接，取各索引匹配主键的交集，适用于等值查询。
union（并集） 多个索引用 OR 连接，多索引等值匹配，合并主键集合。
sort_union（排序并集） 多索引范围查询 OR 拼接，各索引返回主键排序后再合并，开销高于 union。

调优提示

index_merge 属于兜底优化，性能不如复合索引；多条件查询优先创建复合索引替代多单列索引。

4.4 索引下推 ICP（Index Condition Pushdown）

MySQL 5.6+ 默认开启，大幅减少回表次数：

无 ICP 执行流程 仅用索引前导列筛选范围 → 全部回表读取完整行 → MySQL 服务层再过滤剩余条件；大量无效回表。
开启 ICP 流程 存储引擎层直接利用复合索引内所有字段完成全部 WHERE 过滤，只把符合全部条件的数据回表。
识别标识 Extra 字段出现 Using index condition。

4.5 IS NULL 优化

可空索引列 IS NULL 能走 range 索引 索引允许 NULL 时，col IS NULL 会被识别为范围条件，正常使用索引，type=range/ref_or_null。
NOT NULL 字段查询 IS NULL 直接失效 字段约束非空，where id IS NULL 逻辑永远不成立，优化器标记 Impossible WHERE，直接返回空结果，不扫描任何数据。
开发规范 业务字段尽量设置 NOT NULL，规避 IS NULL 查询，减少 ref_or_null 场景，索引利用率更高。

4.6 ORDER BY 排序优化（规避 filesort）

核心底层原理

InnoDB B + 树索引叶子节点数据本身按索引字段有序存储，若排序字段完全匹配索引顺序，MySQL 可直接顺着索引有序返回数据，无需额外排序；若无法利用索引有序性，则触发 Using filesort 文件排序，消耗大量内存 / 磁盘 IO。

一、可利用索引、无 filesort 的场景

完整按复合索引顺序升序 / 降序排序索引 (a,b,c)，ORDER BY a ASC,b ASC、ORDER BY a DESC,b DESC 均可；
WHERE 等值锁定索引前导列，仅对后续索引列排序 WHERE a=100 ORDER BY b,c，a 固定后，b、c 在索引内天然有序；
索引列同向 / 反向统一，支持索引反向扫描；
查询仅包含索引字段 + 主键（覆盖索引场景，无回表也能直接排序）。

二、无法使用索引、触发 Using filesort 的场景

排序字段不满足复合索引最左匹配，跳过前导索引；
WHERE 过滤索引与 ORDER BY 排序索引不是同一套；
排序字段参与函数、四则运算（ORDER BY ABS(age)、ORDER BY -id）；
同时使用多个不同索引的字段排序；
排序升降序混乱（一部分 ASC、一部分 DESC，优化器无法利用索引）。

三、filesort 性能调优说明

内存参数 sort_buffer_size：排序优先使用该内存；
若结果集超出内存容量，会落地磁盘临时文件排序，性能断崖式下跌；
临时调大 sort_buffer_size 仅缓解，治本方案是建立匹配排序逻辑的复合索引。

4.7 GROUP BY 分组优化（规避 Using temporary）

底层原理

索引有序存储，相同分组值的数据连续排列，MySQL 可直接遍历索引完成分组，不用创建临时表存储分组数据；Extra 出现 Using temporary 代表分组无索引支撑，大表性能极差。

可走索引、不生成临时表的条件

GROUP BY 字段严格遵循复合索引最左匹配原则；
聚合函数仅使用 MIN() / MAX()，搭配索引分组列；
WHERE 使用等值锁定索引前导字段，GROUP BY 使用后续索引列。

无法走索引、触发临时表的场景

GROUP BY 字段不满足复合索引最左前缀；
使用 SUM/AVG/COUNT 等聚合函数，且未加 DISTINCT；
分组字段无任何索引。

4.8 DISTINCT 去重优化

MySQL 优化器会将 DISTINCT 等价转换为 GROUP BY 执行，二者优化规则完全通用；
若 DISTINCT 字段存在有序索引，可直接利用索引有序去重，无需临时表、文件排序；
无索引时，DISTINCT 会同时触发 Using temporary + Using filesort，双重性能损耗。

4.9 函数表达式优化

MySQL 区分确定性函数、非确定性函数，二者索引适配能力完全不同：

确定性函数：相同入参返回固定结果（如 POW、SUBSTR）函数放在常量侧可正常走索引：WHERE id = POW(2,3)；
非确定性函数：每次执行结果不同（RAND()、UUID()）直接写在查询条件中无法使用索引；
非确定函数解决方案：提前用变量存储计算结果，再用变量做等值匹配。

sql

SET @rand_val = FLOOR(1 + RAND() * 100);
EXPLAIN SELECT * FROM index_demo WHERE id = @rand_val;

4.10 外连接 LEFT/RIGHT JOIN 优化

MySQL 不支持全外连接；执行时所有 RIGHT JOIN 会自动转换为等价 LEFT JOIN，统一执行逻辑；
外连接转内连接判断：WHERE 条件过滤右表可为 NULL 的字段（如 s.score IS NOT NULL），过滤掉补空行，等价内连接；
联表索引规范：JOIN 关联字段必须建立索引，主表驱动、被关联表使用主键 / 唯一索引实现 eq_ref 最优关联；
性能坑：无索引关联会出现大量 type=ALL 全表扫描，笛卡尔积数据量爆炸。

小节统一优化总结

ORDER BY、GROUP BY、DISTINCT 优先依托复合索引有序性，杜绝 filesort、temporary；
索引列禁止套函数、运算，避免索引失效；随机函数提前用变量预处理；
多表关联字段必须建索引，右表空值过滤可简化外连接为内连接提升效率；
复合索引设计标准：等值条件字段放最前，范围、排序、分组字段后置。

第五部分：索引失效全场景汇总（面试高频考点）

前言

索引设计完成后，若 SQL 写法不规范，会直接导致索引失效、触发type=ALL全表扫描，是线上慢 SQL 最高发根源。下面整理 8 类高频失效场景，附带原理、案例、修复方案。

5.1 复合索引不满足最左匹配原则

原理

复合索引 B + 树按索引字段顺序排序，必须先匹配最左侧第一列，才能向后使用后续索引列；跳过首列，整个索引无法使用。

案例

索引：idx_mail_age_classId(mail,age,class_id) 失效 SQL：

sql

SELECT * FROM index_demo WHERE age = 20;

执行计划type=ALL，完全不走索引。

优化方案

查询条件带上复合索引首列；
单独给 age 新建单列索引。

5.2 OR 条件中存在无索引字段

原理

OR 两边条件只要任意一侧无索引，MySQL 无法使用索引合并，直接放弃索引走全表扫描。

案例

id 有主键索引，age 无索引：

sql

SELECT * FROM index_demo WHERE id = 100000 OR age = 20;

优化方案

给 OR 后的字段建立索引，触发 index_merge；
拆分两条 SQL，代码中合并结果；
调整业务逻辑，避免跨字段 OR 查询。

5.3 复合索引首个范围条件后，后续索引列全部失效

原理

范围运算符（< > <= >= BETWEEN）会截断索引匹配，范围字段之后的索引列无法通过索引过滤，只能服务层过滤。

案例

索引idx_age_class_id(age, class_id)

sql

SELECT * FROM index_demo WHERE age < 18 AND class_id > 1;

仅 age 走索引，class_id 无法利用索引。

优化方案

复合索引设计遵循：等值条件放前面，范围条件放最后。

5.4 LIKE 模糊查询以 % 开头 `%xxx`

原理

B + 树索引前缀有序，后缀无序；通配符在前无法利用索引有序性检索。

案例

sql

SELECT * FROM index_demo WHERE name LIKE '%user_1022000';

全表扫描，索引失效。

优化方案

业务允许则使用后缀匹配LIKE 'user%'；
海量模糊检索使用全文索引 / ES 搜索引擎。

5.5 字段隐式类型转换

原理

索引字段与查询值数据类型不一致，MySQL 自动转换字段值，等同于索引列参与运算，索引失效。

案例

sn 字段为 varchar 字符串，不加引号传入数字：

sql

SELECT * FROM index_demo WHERE sn = 1020000;

优化方案

字符串查询常量统一加单引号，保证类型匹配：sn = '1020000'。

5.6 WHERE 索引字段参与函数、四则运算

原理

索引存储原始字段值，若对索引列做计算 / 函数，索引树无法匹配，只能逐行计算过滤。

案例 1：算术运算

sql

SELECT * FROM index_demo WHERE id + 1 = 1000020;

案例 2：内置函数

sql

SELECT * FROM index_demo WHERE LENGTH(name) = 11;

优化方案

改写条件，把运算放到常量侧，保持索引列纯净：

sql

SELECT * FROM index_demo WHERE id = 1000020 - 1;

5.7 使用！=、<>、NOT IN 不等值查询

原理

不等于、非包含会过滤大部分数据，优化器评估索引扫描成本高于全表，直接放弃索引。

案例

sql

SELECT * FROM index_demo WHERE name <> 'user1020000';

优化方案

业务拆分，用 IN / 等值多条件替代不等值；
数据量小时可接受全表，大数据量调整查询逻辑。

5.8 索引列区分度极低，优化器主动放弃索引

原理

索引字段重复值占比极高（如性别、状态，仅 0/1 两种值），索引扫描后仍需回表大量数据，优化器判定全表扫描更快。

场景

百万数据，gender 只有 0、1 两种值，单独对 gender 建索引，where gender=1大概率走 ALL。

优化方案

低区分度字段不单独建索引；
与高区分度字段组合为复合索引，放在后置位置。

索引失效通用避坑总结

复合索引遵守最左前缀，等值在前、范围在后；
索引列不做运算、不套函数，杜绝隐式类型转换；
模糊查询禁止%前置；
OR 条件全部字段必须有索引；
低基数字段不单独建索引；
少用<>、NOT IN 大范围排除查询。

第六部分：索引设计规范与使用原则（落地实操）

6.1 主键设计强制规范

所有 InnoDB 业务表必须设置主键，无主键时 MySQL 自动生成隐藏 6 字节 rowid 聚集索引，性能差且无法利用；
主键类型推荐 BIGINT 自增，相比字符串主键优势：
- 占用存储空间小，二级索引叶子主键更短，减少 IO；
- 自增有序写入，避免主键页分裂，插入性能更高；
不推荐 UUID、字符串、复合字段作为主键，会造成索引碎片化、占用大量空间。

6.2 适合建立索引的字段

只要字段频繁出现在以下场景，就需要考虑建索引：

WHERE 等值 / 范围过滤条件；
JOIN 关联条件（左右表关联字段均建议建索引）；
ORDER BY、GROUP BY、DISTINCT 排序分组去重字段；
高频列表分页查询、覆盖索引所需字段。

6.3 索引不是越多越好，控制索引数量

弊端

表每新增、更新、删除一行，表上所有索引都需要同步维护（修改 B + 树）；索引越多，DML 操作 CPU、IO 开销越大，高写入业务会严重拖慢更新速度。

落地标准

普通业务表索引控制在 5 个以内；
高并发写入表（订单、日志）尽量少建索引，优先复合索引代替多个单列索引；
长期无人使用的冗余索引及时删除。

6.4 复合索引黄金设计规则

顺序优先级：高频等值字段放最前，范围条件放最后 例：WHERE class_id=1 AND age<30 → 索引 (class_id, age)
严格遵循最左匹配原则，保证单条索引覆盖多类查询；
复合索引自带前缀索引能力：idx(a,b,c) 等价于同时拥有 (a)、(a,b)、(a,b,c) 三组索引，无需重复创建单列索引。

6.5 低区分度字段禁止单独建立索引

区分度定义：字段唯一值数量 / 总数据量；
典型低区分度字段：性别 gender、状态 status、是否删除 is_del（仅 0/1 两种值）；
问题：单独索引查询后仍会匹配大量数据，优化器直接放弃索引走全表扫描；
解决方案：将低区分度字段放在复合索引后置位置，搭配高区分度字段联合过滤。

6.6 强制指定索引语法（特殊场景兜底）

优化器有时会选错索引，可手动强制指定索引，仅应急使用，优先优化索引结构而非强制语法：

sql

-- 推荐：告诉优化器优先使用该索引，不强制
SELECT * FROM index_demo USE INDEX(idx_mail_age_classId) WHERE mail='xxx';
-- 强制：忽略其他所有索引，必须使用指定索引
SELECT * FROM index_demo FORCE INDEX(idx_mail_age_classId) WHERE mail='xxx';

6.7 创建索引线上注意事项

建索引语句会锁表，避开大事务、业务高峰期执行；
MySQL5.6 + 支持在线 DDL（ALTER TABLE ... ADD INDEX 不阻塞读写），低版本需 pt-online-schema-change 工具；
新增索引前先通过EXPLAIN验证收益，避免创建无用索引；
定期巡检：删除长期未使用、重复冗余的索引，降低写入损耗。

6.8 字段设计配套规范

业务字段尽量设置 NOT NULL；
允许为 NULL 的字段会增加索引存储开销，同时容易触发ref_or_null；
减少IS NULL查询场景，非空字段查询 NULL 会被优化器判定为无效 SQL，无扫描开销。

本章节落地速记总结

表必有自增 BIGINT 主键；
过滤 / 关联 / 排序分组字段建索引，高写入少建索引；
复合索引：等值在前、范围在后，复用前缀覆盖多查询；
低区分度字段不单独建索引；
字段统一 NOT NULL，减少 NULL 查询损耗；
线上建索引避开高峰，定期清理废弃索引。

第七部分：线上 SQL 调优完整落地流程

整套标准化流程，从发现慢 SQL→分析根源→针对性优化→验证效果→长期维护，企业开发 / DBA 通用落地步骤。

7.1 第一步：定位线上慢 SQL（3 种核心手段）

1. 慢查询日志（长期监控首选）

提前开启 MySQL 慢日志，设置阈值（如超过 100ms 的 SQL 自动记录），定期分析日志批量抓取慢 SQL；优点：离线批量统计，可汇总 Top 耗时、高频慢语句；缺点：无法实时查看当前阻塞 SQL。

2. show processlist（实时紧急排查）

线上出现接口超时、数据库卡顿，立刻执行查看当前运行线程：

观察 State 字段：Sending data、Copying to tmp table、Sorting result 均为慢 SQL 阻塞特征；
查看 Info 字段拿到完整 SQL，快速定位卡死语句。

3. mysqlslap 压测工具（预上线验证）

新功能、新 SQL 上线前，模拟高并发压测：

对比有无索引、优化前后平均执行耗时；
提前发现并发下性能雪崩问题，避免线上故障。

7.2 第二步：Explain 解析执行计划，定位性能瓶颈

拿到慢 SQL 后，优先执行EXPLAIN \G，重点盯三类高危标识：

type = ALL：全表扫描，最高优先级优化；
Extra 包含Using filesort：无索引排序，IO 开销巨大；
Extra 包含Using temporary：分组 / 去重生成临时表，大表灾难。同时辅助查看：rows 预估扫描行数、key 是否命中索引、是否存在回表（无 Using index）。

7.3 第三步：分场景针对性优化方案

场景 1：type=ALL，无索引 / 索引失效

若字段从未建索引：根据业务查询逻辑，创建复合覆盖索引；
若存在索引但失效：按索引失效 8 大场景改写 SQL
- 去掉索引列函数、算术运算；
- 修正字符串不加引号导致的隐式转换；
- 模糊查询去掉前置%；
- OR 两侧全部字段补充索引；
- 复合索引补充最左前缀条件。

场景 2：Extra Using filesort（文件排序）

调整复合索引顺序，把 WHERE 等值字段、ORDER BY 字段按索引有序规则放入索引；
缩小分页查询范围，避免超大结果集排序；
临时缓解：适度调大sort_buffer_size（不建议长期依赖参数调优）。

场景 3：Extra Using temporary（临时表分组 / 去重）

GROUP BY、DISTINCT 字段加入复合索引，利用索引有序特性消除临时表；
拆分复杂聚合查询，程序内存二次聚合代替数据库分组；

场景 4：存在回表（无 Using index）

禁止SELECT *，只查询业务所需字段，构建覆盖索引消除回表随机 IO。

7.4 第四步：优化后验证性能（双重校验）

再次执行 Explain 校验：
- type 提升至 ref/range/const；消除 filesort、temporary；出现 Using index 代表覆盖索引生效。
mysqlslap 并发压测对比：
- 优化前后平均耗时、最大耗时；
- 同等并发下数据库 CPU、磁盘 IO 指标；
灰度上线小流量验证，观察接口响应时间是否恢复正常。

7.5 第五步：长期线上维护机制

定期巡检冗余索引：删除长期未使用、重复复合索引，降低增删改开销；
持续监控慢查询日志，新增业务 SQL 及时优化；
上线规范：所有新增复杂查询，必须本地 Explain 校验后再提测；
定期数据归档：超大表分表 / 冷热分离，减少单表数据量带来索引扫描损耗。

落地流程极简总结

监控工具抓慢 SQL → Explain 定位瓶颈 → 分场景优化索引 / 改写 SQL → 压测验证优化效果 → 长期监控清理冗余索引

第八部分：总结与线上避坑清单

8.1 全文核心知识点总结

1. EXPLAIN 是 SQL 调优唯一核心诊断工具，看懂两个核心维度即可完成 90% 调优

（1）type 字段性能优先级（从上到下性能递减，开发优化目标）

system > const > eq_ref > ref > ref_or_null > index_merge > range > index > ALL

理想标准：单条查询达到const/ref/range，联表达到eq_ref；
红线标准：禁止出现index/ALL，一旦出现代表存在大量 IO 扫描，并发极易超时；
核心判断：type 越靠左，扫描行数越少、磁盘 IO 越低、数据库 CPU 占用越低。

（2）Extra 列 3 类关键标识，直接区分好坏

最优标识：Using index = 覆盖索引，无回表，随机 IO 清零，查询性能拉满；
高危性能坑（出现必须优化）： ① Using filesort：无法利用索引排序，开辟内存排序，内存不足落磁盘，海量 IO； ② Using temporary：分组 / 去重无有序索引，创建临时表，大数据量直接卡死；
正常无害标识：Using where、Using index condition（索引下推 ICP，MySQL 自动优化）。

（3）配套辅助字段判断依据

key 为 NULL：无索引 / 索引失效，type 大概率 ALL；
rows 百万级：扫描数据过多，必须缩小范围或新增索引；
filtered 百分比过低：扫描大量无效数据，过滤逻辑不合理。

2. 覆盖索引是性价比最高的优化手段，核心解决回表痛点

InnoDB 二级索引叶子只存「索引字段 + 主键 ID」，查询不在索引内的字段就会触发回表；
回表本质是随机磁盘 IO，随机 IO 速度远慢于顺序索引扫描，高并发场景性能直接暴跌；
覆盖索引把 WHERE 过滤字段、SELECT 返回字段全部放进复合索引，全程只扫描二级索引，跳过回表；
落地收益：接口响应从几百 ms 降低至 10ms 内，数据库 IO、CPU 压力大幅下降。

3. 线上 90% 慢 SQL，只由四类问题导致

全表扫描 type=ALL：无索引、SQL 写法导致索引失效；
文件排序 Using filesort：ORDER BY 无匹配有序索引；
临时表 Using temporary：GROUP BY/DISTINCT 无匹配有序索引；
大量回表无 Using index：使用 select *，未构建覆盖索引。绝大多数线上数据库卡顿、接口超时、慢查询堆积，根源都逃不出这四类问题。

4. MySQL 优化器具备自动化简能力，但无法替代人工规范编码

优化器自动做的优化（无需人工处理）

简化冗余括号、合并多层 AND/OR 逻辑；
计算常量表达式，剔除永远成立 / 不成立的条件；
自动合并重叠范围、剔除无效区间；
索引下推 ICP、索引合并 Index Merge、IS NULL 逻辑优化；
无 GROUP 时合并 WHERE 与 HAVING、COUNT (*) 轻量化处理。

优化器做不到，必须人工控制

不会自动创建索引，索引需要开发提前设计；
无法修复不规范 SQL 造成的索引失效（函数、隐式转换、前置 % 等）；
不会自动调整复合索引顺序，无法解决 filesort、temporary；
不会主动改写复杂逻辑，无法优化超大范围查询。结论：优化器只是辅助工具，规范索引与 SQL 写法才是根本。

8.2 开发写 SQL 强制避坑清单（每条附带原理 + 反面示例 + 正确写法）

1. 严禁使用 SELECT *，只查询业务需要的字段

原理：select * 会读取整张表所有字段，几乎无法触发覆盖索引，频繁回表；
反面：SELECT * FROM index_demo WHERE class_id = 1；
正确：SELECT id,name,mail FROM index_demo WHERE class_id = 1；
附加收益：减少网络传输数据、降低内存占用、更容易构建覆盖索引。

2. 索引列禁止参与函数、四则运算，规避隐式类型转换

场景 A：索引列做运算

原理：索引存储原始值，运算后无法匹配索引树，直接失效全表扫描反面：WHERE id + 5 = 100000 正确：WHERE id = 100000 - 5

场景 B：索引列套内置函数

反面：WHERE LENGTH(mail) = 10 正确：提前增加长度字段，或使用原生字段等值匹配

场景 C：字符串索引传入数字（隐式转换）

sn 字段 varchar，建立唯一索引反面：WHERE sn = 1020000 正确：WHERE sn = '1020000'

3. LIKE 模糊匹配禁止 % 放在最前面

原理：B + 树索引前缀有序，后缀无序，%xxx无法走索引；
反面：WHERE name LIKE '%user123'；
正确：业务允许使用WHERE name LIKE 'user123%'；
兜底方案：海量模糊检索引入 Elasticsearch 全文检索。

4. 复合索引严格遵守最左匹配，范围条件放索引末尾

黄金索引顺序规则：等值条件字段 → 范围条件字段；
反面：索引(age,class_id)，WHERE age<20 AND class_id=1，class_id 无法使用索引；
正确：索引(class_id,age)，等值在前，范围在后；
避坑：查询条件不能跳过复合索引首列，否则整个索引失效。

5. ORDER BY、GROUP BY、DISTINCT 配套设计索引，消除文件排序 / 临时表

问题：排序、分组无有序索引，必然触发 filesort、temporary，千万数据性能雪崩；
规范：把 WHERE 等值字段 + 排序 / 分组字段组合为复合索引；
示例：业务常根据 class_id 查询并按 age 排序，索引创建(class_id,age)。

6. 控制单表索引数量，高写入业务少建索引

底层代价：INSERT/UPDATE/DELETE 修改数据时，表上所有索引都要同步更新 B + 树，索引越多写入越慢；
落地标准：普通业务表索引≤5 个，日志、订单等高写入表≤3 个；
清理机制：定期删除长期未使用、重复冗余索引。

7. 统计行数统一使用 COUNT (*)，不要纠结 count (1)

MySQL 5.7.18 及以上，InnoDB 对 count (*)、count (1) 处理逻辑完全一致，无性能差距；
MyISAM 无 WHERE 条件时 count (*) 直接读取表总行数，速度极快；
SQL 标准推荐写法为 COUNT (*)，可读性更强，团队统一规范。

补充额外避坑点

OR 条件两侧字段必须全部有索引，否则直接全表扫描；
低区分度字段（gender、is_del）不要单独建索引；
建表字段尽量设置 NOT NULL，减少 ref_or_null 低效索引类型；
避免超大无分页范围查询，limit 限制返回行数，减少扫描量。

8.3 拓展延伸：进阶调优全方向（基础索引调优之外的优化方案）

一、MySQL 内存参数调优（实例级性能优化）

1. innodb_buffer_pool_size（最重要参数）

作用：缓存 InnoDB 索引 + 数据页，减少磁盘 IO；
推荐配置：专用数据库服务器设置物理内存 50%~70%；
痛点：配置过小，热点数据无法缓存，频繁刷磁盘，查询缓慢。

2. sort_buffer_size

作用：filesort 排序使用的内存缓冲区；
问题：数值过小，大排序落地磁盘，性能暴跌；
注意：会话级参数，不要设置过大，避免多并发内存溢出。

3. join_buffer_size

作用：无索引联表时的关联缓冲区；
优化思路：优先给关联字段建索引，而非单纯调大该参数。

4. read_buffer_size / read_rnd_buffer_size

顺序读、随机读缓冲区，优化批量查询、分页 IO 效率。

二、架构层高阶优化（千万级大数据、高并发场景）

1. 读写分离

架构：一主多从，主库负责 INSERT/UPDATE/DELETE 写入，从库承载 SELECT 查询；
收益：分摊单库 CPU、IO 压力，隔离写入阻塞查询的问题；
适配场景：读远大于写的业务（用户列表、商品、订单查询）。

2. 分库分表

水平分表：按用户 ID、订单号哈希拆分，单表控制在 500 万以内；
垂直分库：按业务模块拆分库（用户库、订单库、商品库）；
收益：缩小单表索引体积，索引扫描速度大幅提升，规避超大表全表扫描。

3. 冷热数据分离

方案：线上热表只保留近 3~6 个月数据，历史冷数据归档至归档库；
适用：订单、日志、交易类海量时序数据。

4. 中间件缓存

Redis 缓存高频单条查询结果，直接绕过 MySQL，减轻数据库压力。

三、线上慢 SQL 自动化监控平台搭建思路

1. 数据采集层（采集慢 SQL 来源）

解析 MySQL 慢查询日志，抓取超过阈值的 SQL；
数据库审计日志、Binlog 日志补充采集；
应用层埋点，记录接口执行 SQL 耗时。

2. 数据统计分析层

聚合指标：SQL 执行次数、平均耗时、最大耗时、扫描行数；
自动执行 EXPLAIN，解析 type、filesort、全表扫描等风险；
聚合 Top50 高频慢 SQL，按耗时排序。

3. 告警推送层

触发规则：

单次执行超时阈值（如 200ms）；
每日全表扫描 SQL 累计次数超标；
出现大量 filesort/temporary 高危 SQL；推送渠道：钉钉、企业微信、邮件告警给开发 / DBA。

4. 优化辅助层

自动生成索引优化建议；
记录优化前后执行计划对比；
慢 SQL 优化工单流转，跟踪整改记录。

结尾

到这里，整套 MySQL SQL 与索引调优实战内容就全部讲解完毕。我们从线上真实慢 SQL 痛点切入，逐层拆解数据库优化分层思想，完整吃透EXPLAIN执行计划每一项指标，理清覆盖索引、回表的底层逻辑，梳理 MySQL 优化器内置各类自动优化机制，汇总所有索引失效典型场景，落地一套可直接套用的索引设计规范，同时给出从故障定位、优化整改到长期维护的标准化线上调优流程，最后整理开发日常编码避坑清单与高阶架构优化拓展方向。

数据库性能优化从来不是靠临时扩容服务器、盲目加索引解决问题，核心在于开发阶段就遵循索引规范、上线前用EXPLAIN校验 SQL 执行效率，从源头规避全表扫描、文件排序、临时表、频繁回表等性能隐患。MySQL 优化器虽能自动简化部分查询逻辑，但无法弥补不规范 SQL、不合理索引带来的性能损耗，规范的 SQL 编写与科学的索引设计才是低成本、长效稳定的最优解。

对于日常业务开发同学，吃透本文基础索引与 SQL 调优内容，足以应对 90% 线上数据库慢查询问题；如果是 DBA、负责高并发海量数据系统，可基于文中拓展方向，结合内存参数调优、读写分离、分库分表、慢 SQL 监控平台做更深层次架构优化。

后续实操建议：本地搭建百万级测试表，复现文中所有EXPLAIN案例、索引失效场景，亲手对比优化前后执行计划与耗时差距，把理论转化为实战能力。如果工作中遇到特殊慢 SQL、索引优化难题，也可以结合执行计划对照本文知识点逐一排查定位。

openEuler 社区

openEuler 是由开放原子开源基金会孵化的全场景开源操作系统项目，面向数字基础设施四大核心场景（服务器、云计算、边缘计算、嵌入式），全面支持 ARM、x86、RISC-V、loongArch、PowerPC、SW-64 等多样性计算架构

更多推荐

嵌入式系统：从幕后走向台前的技术基石

openEuler 社区

嵌入式系统进阶：芯片选型、工具链到系统架构全链路拆解

openEuler 社区

参编国家新标准！东软睿驰筑牢车控操作系统硬核安全底线

车控操作系统是支撑智能网联汽车智能化、网联化发展的核心软件底座，是保障整车控制安全、提升硬件适配效率与智能化水平的核心支撑，已成为决定智能汽车产业安全与技术自主能力的关键环节。依托千万级装车量产规模与100余家车企及Tier1客户的规模化落地实践，NeuSAR OS已完成多芯片、多架构、多车型平台的全场景量产验证，广泛应用在智能辅助驾驶、底盘动力、车身控制等核心域控制器产品中，形成标准能力在产业侧