第 8 章 高阶 SQL 性能调优实战

经过前文对 MySQL 架构、索引结构、事务与锁机制、日志系统、内存管理等底层原理的系统学习，开发者已经具备了数据库内核层面的理论基础。本章内容基于 MySQL 5.6 及以上主流生产版本编写，依托 5.6 版本引入的 semi-join 半连接、ICP 索引下推等优化特性展开。但在真实生产环境中，大量性能故障并非源于架构设计或硬件瓶颈，而是由日常开发中不规范的 SQL 编写行为直接导致。一条在测试环境小数据量下运行流畅的 SQL，在上线后面对千万级业务数据时，往往会因索引失效、无效 IO 过多、内存资源挤占、临时表与文件排序等问题，演变为拖垮整个实例的慢查询。

本章以电商核心业务中用户表 user、订单表 order_info、商品表 goods 为统一案例模型，围绕线上真实发生的批量慢 SQL 雪崩场景展开，全小节统一采用故障现象还原、问题 SQL 示例、底层内核原理、执行计划验证、分层优化方案、开发规范落地的写作思路，把理论原理和线上生产场景深度融合，提炼可落地复用的 SQL 优化标准与编码规范，帮助开发者从代码编写层面规避各类高频数据库性能隐患。

8.1 场景复盘：语法无误，线上大面积慢 SQL、接口超时

某电商平台在完成功能测试后正式上线，初始运行平稳，但随着用户流量与订单数据量快速上涨，核心业务接口开始集中出现性能问题。商品列表查询、用户历史订单分页、用户手机号检索等接口响应时间从毫秒级飙升至数秒，大量请求阻塞导致应用服务线程池耗尽，触发熔断降级。

DBA 对数据库环境进行全面排查，服务器 CPU、内存、磁盘 IO 均无硬件瓶颈，事务与锁机制运行正常，数据表索引均按设计文档完成创建，无数据碎片与实例异常。最终通过慢查询日志定位，所有故障均由九类标准化劣质 SQL 导致，具体如下：

1. 字符串类型手机号使用数值常量查询，触发隐式类型转换，索引失效引发全表扫描；

2. 深度分页使用超大偏移量，数据库遍历并丢弃海量无效数据，产生大量无效 IO；

3. 旧版相关子查询写法，MySQL5.6 + 开启半连接优化后 IN 性能大幅改善，IN 参数超 500 建议改用 JOIN 关联查询；

4. GROUP BY 与 ORDER BY 组合缺少匹配的联合索引，产生临时表与磁盘文件排序；

5. LIKE 语句使用前后通配符模糊查询，无法利用索引有序性，强制全表遍历；

6. 索引字段嵌套 DATE、SUBSTR 等函数，破坏索引结构导致索引失效；

7. 多表 JOIN 关联字段字符集、数据类型不一致，触发隐式转换造成索引失效；

8. 批量 DELETE、UPDATE 单次处理数万条数据，形成长事务占用锁资源，阻塞业务写入；

9. 查询语句无合理筛选条件与分页限制，单次返回数十万条超大结果集，占用大量内存与网络带宽。

8.2 一条 SQL 从客户端到数据返回的全生命周期执行链路

掌握 SQL 完整执行流程，是理解优化器行为、定位慢查询根源、制定优化方案的核心理论基础。一条查询的执行过程可划分为 Server 层处理、存储引擎层交互、结果集返回三大阶段，各组件协同完成整个生命周期。

8.2.1 Server 层执行流程

1. 连接器

负责处理客户端发起的 TCP 连接请求，完成用户名与密码校验、权限验证，并初始化会话环境，包括字符集、事务隔离级别、时区等参数。连接建立后，当前会话的所有 SQL 语句均依托该连接执行。

2. 词法与语法解析

接收客户端发送的原始 SQL 语句，对语句进行拆分与解析，识别关键字、表名、字段名、条件表达式等结构，生成抽象语法树。若语句存在语法错误，该阶段会直接抛出异常，终止执行流程。

3. 语义解析

在语法校验通过的基础上，进一步校验数据表、字段是否真实存在，校验当前用户是否具备对应访问权限，排除对象不存在、权限不足等合法性问题。

4. 查询优化器

作为 SQL 性能的核心决策模块，优化器基于数据表统计信息、IO 代价模型、数据分布预估，自主完成索引筛选、多表 JOIN 关联顺序确定、子查询语句逻辑改写。MySQL 5.6 新增 semi-join 半连接优化，大幅提升 IN 子查询执行效率；当 SQL 写法不规范、索引设计缺失或结构不匹配时，优化器无法依托索引有序性完成 GROUP BY、ORDER BY，便会在执行计划中选定临时存储与外置排序方案，也就是执行计划里的 Using temporary、Using filesort，同时不合理的条件书写也会造成索引无法选用，最终触发全表扫描。

5. 执行器

接收优化器生成的最终执行计划，调用存储引擎提供的标准数据访问接口，向 InnoDB 下发数据读取或修改指令，进入引擎层执行阶段。

8.2.2 InnoDB 引擎层执行流程

1. 数据页加载

根据执行计划中的索引信息，定位目标数据所在的数据页。InnoDB 优先从 BufferPool 缓冲池中读取数据，若缓存未命中，则从磁盘 ibd 文件中读取 16KB 标准数据页加载至内存。深度分页、无限制全表查询会批量淘汰缓冲热点数据，InnoDB 采用冷热分离 LRU 算法（新生代 + 老年代），冷数据优先进入新生代，不会直接淘汰热点缓存，频繁触发磁盘 IO，拉高查询耗时。

2. 读写类型区分

普通无锁 SELECT 语句为快照读，基于 MVCC 多版本并发控制实现无锁查询；UPDATE、DELETE、FOR UPDATE 等语句为当前读，需要对目标数据添加行锁、间隙锁等锁机制保证数据一致性。批量 DML 若无可用索引，行锁会退化全表锁，单次大批量 DML 会持续占用大范围锁，拉长事务时长，阻塞正常业务写入。

3. 数据修改与日志处理

数据修改操作在内存中完成，生成脏页，同时记录 Undo 日志用于事务回滚，记录 Redo 日志保证崩溃恢复能力。事务提交遵循两阶段提交机制，确保 Redo、Binlog 日志一致性，内存脏页由后台线程异步刷入磁盘。

4. 索引回表

使用二级索引且无法覆盖全部查询字段时，引擎通过二级索引获取主键值后，需要再次访问聚簇索引读取完整数据，该过程称为回表，会产生额外的随机 IO 开销。MySQL5.6 引入 ICP、MRR 优化机制可减少无效回表操作。

补充：Using index condition 仅代表满足 ICP 条件，优化器会基于成本判断是否真正启用索引下推，并非标识 ICP 一定生效。

8.2.3 结果组装与返回

InnoDB 将查询结果返回至 Server 层执行器，执行器对结果集进行整理封装，最终通过连接器返回给客户端，整条 SQL 执行生命周期结束。查询缺少筛选与分页、一次性返回海量数据，会占用大量内存与网络带宽，拖累整体服务稳定性。

8.3 Explain 执行计划全方位实战解析

Explain 是 MySQL 官方提供的查询分析工具，能够模拟优化器执行逻辑，输出 SQL 的具体执行计划，是线上慢查询排查的核心手段。本节围绕核心字段展开实战解析，帮助开发者快速判断索引使用、数据扫描、额外开销等关键信息。

1. type 字段：访问方式性能分级

type 字段标识数据表访问类型，直接决定查询性能优劣，性能由差至优排序如下。

访问类型	说明
ALL	全表扫描，无可用索引，性能最差
index	索引全扫描，遍历整棵索引树；若为覆盖索引（Using index）则无需回表，普通二级索引全扫描仍需回表查询主键对应行数据
range	索引范围扫描（>、<、BETWEEN、IN、LIKE 前缀匹配），仅扫描索引指定范围
ref	普通索引等值匹配，日常业务主流高效访问方式
eq_ref	联表时主键 / 唯一索引匹配，多表查询最优类型
const	主键 / 唯一索引等值匹配，最多 1 行数据，优化器直接常量读取

2. key、key_len、rows 关键字说明

字段	释义
key	实际选用索引名称，NULL 代表未使用索引
key_len	索引实际占用字节，用于判断联合索引前缀命中情况
rows	优化预估扫描行数，数值越大查询开销越高

3. Extra 字段关键标识含义

标识	含义
Using index	触发覆盖索引，无需回表读取聚簇数据
Using index condition	仅满足 ICP 索引下推条件，优化器按需决定是否启用索引下推
Using filesort	无法利用索引排序，产生文件排序开销
Using temporary	需创建临时表，常见 GROUP BY、ORDER BY 缺索引场景

8.4 隐式类型转换引发索引失效深度复盘

8.4.1 故障现象

用户表 user 中 phone 字段为 VARCHAR 类型，建有唯一索引 idx_phone，用于存储手机号，业务使用数字常量查询，对应执行 SQL 如下：

mysql > explain SELECT * FROM user WHERE phone = 13800138000;

执行计划如下：

id: 1

select_type: SIMPLE

table: user

partitions: NULL

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 5

filtered: 20.00

Extra: Using where

执行计划 type 字段为 ALL，key 取值为空，代表索引失效，千万数据场景查询耗时显著升高。

8.4.2 底层原理

MySQL 存在隐式类型转换规则：当字符串类型字段与数值类型常量进行等值比较时，数据库会自动将字符串字段转换为数值类型参与运算。索引基于原始字符串有序结构构建，字段被函数式转换后，索引有序性被破坏，优化器无法通过 B + 树二分查找定位数据，只能执行全表扫描。

核心结论：索引字段无论显性还是隐性参与运算，都会直接导致索引失效。

8.4.3 优化方案

1. 短期应急优化

保持数据类型一致，为字符串常量添加引号，使索引正常生效。

mysql > explain SELECT * FROM user WHERE phone = '13800138000';

执行计划如下：

id: 1
select_type: SIMPLE
table: user
partitions: NULL
type: const
possible_keys: idx_phone
key: idx_phone
key_len: 83
ref: const
rows: 1
filtered: 100.00
Extra: NULL

修改后执行计划 type 变为 const，key 字段展示 idx_phone，索引正常生效。

2. 长期规范

前后端接口入参类型必须与数据库字段类型严格统一，从编码层面杜绝隐式转换。额外管控：关联查询两表同名字段字符集、数据类型保持一致，规避跨表隐式转换。

8.5 分页查询深度偏移性能与多级优化

8.5.1 故障现象

分页查询采用常规 LIMIT 写法

mysql > explain SELECT * FROM order_info LIMIT 100000,20;

分页页码较小时查询速度平稳，偏移量达到十万级别后耗时陡增，接口频繁超时，大量冷数据载入缓冲池，整体缓存命中率下降。

8.5.2 底层原理

MySQL LIMIT 语法无法直接定点跳转数据，需要从头遍历 offset 加 size 条记录，丢弃前段无用数据后返回结果。搭配 SELECT * 缺少覆盖索引，会频繁触发回表产生随机 IO。大批量冷数据载入 BufferPool 后依据 LRU 规则淘汰热点缓存，进一步拖累常规 SQL 查询效率。

8.5.3 优化方案

1. 主键书签分页（生产首选）

限制：仅支持顺序上下页，无法实现任意页码跳页

SELECT * FROM order_info WHERE id > 上一页最大ID LIMIT 20;

依托主键 B + 树二分检索，仅少量磁盘 IO 即可锁定数据起始位置，查询耗时不受页码大小影响。

2. 覆盖索引分步查询

SELECT id FROM order_info WHERE user_id = 10086 LIMIT 100000,20;

SELECT * FROM order_info WHERE id IN (查询到的主键集合);

先借助索引拿到主键，减少大范围回表次数。

3. 架构优化

订单属于时序数据，采用按月分表或者按用户分表，控制单表数据体量，规避超大偏移分页。

8.5.4 开发规范

前端页面只提供上一页与下一页，关闭大页码手动输入；报表导出类业务统一采用书签分页，禁用大偏移 LIMIT 写法。

8.6 视图外层筛选无法下推带来的性能隐患

8.6.1 故障现象

1. 构造测试视图，视图关联三表并包含 GROUP BY 逻辑

CREATE VIEW v_order_stat AS

SELECT

  o.id,

o.order_no,

o.pay_status,

o.create_time,

  u.phone,

g.name,

o.total_amount 

FROM order_info o 

JOIN user u ON o.user_id = u.id

JOIN goods g ON o.goods_id = g.id

GROUP BY o.id;

视图内部包含 GROUP BY 分组，MySQL 优化器无法使用 MERGE 合并算法，强制降级为 TEMPTABLE 临时表算法。

2. 批量生成十万级测试数据

DELIMITER //
CREATE FUNCTION rand_date() RETURNS datetime
DETERMINISTIC
BEGIN
  DECLARE start_dt DATETIME DEFAULT '2024-01-01';
  RETURN start_dt + INTERVAL FLOOR(RAND() * 365) DAY;
END //

CREATE PROCEDURE generate_test_data(IN user_count INT, IN goods_count INT, IN order_count INT)
BEGIN
  DECLARE i INT DEFAULT 1;
  TRUNCATE TABLE `user`;
  WHILE i <= user_count DO
    INSERT INTO `user`(phone, username, status)
    VALUES (CONCAT('138', FLOOR(10000000 + RAND()*90000000)), CONCAT('user_',i), 1);
    SET i = i + 1;
  END WHILE;
  SET i = 1;
  TRUNCATE TABLE goods;
  WHILE i <= goods_count DO
    INSERT INTO goods(name, price, stock)
    VALUES (CONCAT('goods_',i), ROUND(RAND()*1000,2), FLOOR(RAND()*1000));
    SET i = i + 1;
  END WHILE;
  SET i = 1;
  TRUNCATE TABLE order_info;
  WHILE i <= order_count DO
    INSERT INTO order_info(order_no, user_id, goods_id, pay_status, total_amount, create_time)
    VALUES (
      CONCAT('ORDER', DATE_FORMAT(NOW(),'%Y%m%d'),i),
      FLOOR(1 + RAND()*user_count),
      FLOOR(1 + RAND()*goods_count),
      FLOOR(RAND()*2),
      ROUND(RAND()*10000,2),
      rand_date()
    );
    SET i = i + 1;
  END WHILE;
END //
DELIMITER ;

-- 生成1000用户、100商品、100000条订单数据
CALL generate_test_data(1000, 100, 100000);

3. 对比两条相同条件 SQL 执行效果

直接查询基表：

mysql > explain select * from order_info WHERE create_time >= '2025-01-01';

执行计划如下：

id: 1

select_type: SIMPLE

table: order_info

partitions: NULL

type: range

possible_keys: idx_order_createtime

key: idx_order_createtime

key_len: 5

ref: NULL

rows: 1

filtered: 100.00

Extra: Using index condition

1 row in set, 1 warning (0.00 sec)

执行 type 为 range，key 命中 idx_order_createtime，Extra 显示 Using index condition，筛选条件正常下推，索引完全可用。

查询视图：

mysql > explain SELECT * FROM v_order_stat WHERE create_time >= '2025-01-01';

执行计划如下：

id: 1

select_type: PRIMARY

table: <derived2>

partitions: NULL

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 99884

filtered: 33.33

Extra: Using where

id: 2

select_type: DERIVED

table: g

partitions: NULL

type: ALL

possible_keys: PRIMARY

key: NULL

key_len: NULL

ref: NULL

rows: 100

filtered: 100.00

Extra: Using temporary; Using filesort

id: 2

select_type: DERIVED

table: o

partitions: NULL

type: ref

possible_keys: PRIMARY,idx_order_no,idx_user_id,idx_goods_id,idx_order_createtime

key: idx_goods_id

key_len: 4

ref: group_hfjz.g.id

rows: 998

filtered: 100.00

Extra: Using index condition

id: 2

select_type: DERIVED

table: u

partitions: NULL

type: eq_ref

possible_keys: PRIMARY

key: PRIMARY

key_len: 4

ref: group_hfjz.o.user_id

rows: 1

filtered: 100.00

Extra: Using where

4 rows in set, 1 warning (0.00 sec)

执行计划中出现了 <derived2> 派生表，外层查询对派生表执行全表扫描，key 为 NULL，无法使用任何索引。虽然关联阶段基表 order_info 用到了 idx_goods_id 索引，但外层的 create_time 筛选条件没有下推到基表，而是在临时表生成完成后才进行过滤，导致必须先加载大量数据，无法利用时间索引实现高效查询。

8.6.2 底层原理

MySQL 视图分为 MERGE 合并、TEMPTABLE 临时表两种执行逻辑。MERGE 模式会拼接视图与外层 SQL，筛选条件可以正常下推至原表；视图带有 GROUP BY、聚合、DISTINCT 时启用 TEMPTABLE 模式，先全量生成临时表再过滤，原表索引无法使用。本案例视图存在分组，筛选条件不能下推至订单基表。

8.6.3 优化方案

统计类带分组聚合逻辑禁止封装视图，筛选条件写在原生 SQL 内部；无聚合的简单视图可以正常使用；存量视图改造，筛选条件改用参数动态拼接。上线前执行 Explain，出现 DERIVED+Using temporary 需要整改。

8.6.4 拓展思考

删除视图 GROUP BY 字段，视图切换 MERGE 算法，筛选条件正常下推；DISTINCT、UNION、聚合函数同样触发临时表。UNION（会自动去重），同样会触发临时表，导致筛选无法下推、索引失效；而使用 UNION ALL 不会生成临时表，不影响索引使用，外层筛选仍可正常下推。

8.7 LIKE 模糊查询索引边界与落地优化

8.7.1 故障现象

订单编号字段已建立索引，业务使用全模糊匹配查询。

mysql > explain SELECT * FROM order_info WHERE order_no LIKE '%2025%';

执行计划如下：

id: 1

select_type: SIMPLE

table: order_info

partitions: NULL

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 99885

filtered: 11.11

Extra: Using where

1 row in set, 1 warning (0.00 sec)

语句执行全表扫描，索引无法使用，大数据量场景下查询效率极低，接口响应缓慢。

8.7.2 底层原理

B + 树索引按照字段值的前缀有序组织，仅支持前缀匹配。LIKE '2025%' 这类右模糊查询可以利用索引有序性执行范围扫描；LIKE '%2025' 左模糊与 LIKE '%2025%' 全模糊查询无法确定前缀范围，优化器无法使用索引，只能遍历全表。

8.7.3 优化方案

业务允许的场景统一使用右模糊前缀匹配，保证索引正常使用；小数据量场景可采用 MySQL 内置 InnoDB 全文索引 MATCH AGAINST 实现全模糊检索，全文检索类需求从 MySQL 剥离，使用 Elasticsearch 等专业搜索引擎实现，避免数据库承担非核心检索压力。

8.8 NOT IN 高危陷阱与最优实践

8.8.1 核心结论

MySQL 5.6 及以上版本提供 semi-join（半连接）优化 能力，IN 子查询会被优化器自动转换为 semi-join 半连接执行计划，等价于多表关联的高效执行逻辑，因此 IN 与 EXISTS 在索引合理的前提下性能基本持平，开发中无需刻意改写。

什么是 semi-join 半连接？

semi-join（半连接） 是 MySQL 优化器针对 IN(子查询) 做的高效优化：只检查主表数据是否在子查询中存在，不需要返回子表数据，比传统循环执行快很多，是专门优化 IN 子查询的核心机制。

举例说明：

SELECT * FROM user WHERE user.id IN (SELECT order.user_id FROM order);

优化前（老式执行）：循环查 user → 逐条去 order 里匹配，效率低

优化后（semi-join 半连接）：MySQL 把它变成高效关联查询，只判断存在性，不重复返回数据，性能接近 JOIN。

重要说明：半连接优化仅针对 IN、=ANY 类子查询，NOT IN 无法触发半连接优化，本节核心要点：NOT IN 存在逻辑缺陷与性能风险，生产环境禁止使用。

8.8.2 生产禁用：NOT IN

mysql > explain SELECT * FROM order_info WHERE user_id NOT IN (SELECT id FROM user WHERE status = 0);

执行计划如下：

id: 1

select_type: PRIMARY

table: order_info

partitions: NULL

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 99885

filtered: 100.00

Extra: Using where

id: 2

select_type: SUBQUERY

table: user

partitions: NULL

type: ALL

possible_keys: PRIMARY

key: NULL

key_len: NULL

ref: NULL

rows: 1000

filtered: 10.00

Extra: Using where

2 rows in set, 1 warning (0.00 sec)

执行计划显示主查询与子查询均执行全表扫描，未使用任何索引。即便相关字段建有索引，NOT IN 语法也会导致索引无法生效。

NOT IN 存在两类严重问题。

第一类是逻辑风险，子查询结果集包含 NULL 时，外层查询直接返回空数据，造成业务结果异常。

第二类是性能风险，子查询无法有效使用索引，极易触发全表扫描，大数据量场景查询效率极低。

8.8.3 标准替代方案：NOT EXISTS

mysql > explain SELECT * FROM order_info o WHERE NOT EXISTS ( SELECT 1 FROM user u WHERE u.id = o.user_id AND u.status = 0);

执行计划如下：

id: 1

select_type: PRIMARY

table: o

partitions: NULL

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 99885

filtered: 100.00

Extra: Using where

id: 2

select_type: DEPENDENT SUBQUERY

table: u

partitions: NULL

type: eq_ref

possible_keys: PRIMARY

key: PRIMARY

key_len: 4

ref: group_hfjz.o.user_id

rows: 1

filtered: 10.00

Extra: Using where

2 rows in set, 2 warnings (0.00 sec)

执行计划显示子查询使用主键索引 eq_ref 精准匹配，单条数据定位，无全表扫描，查询性能稳定。特别说明：上述执行计划中 order_info 表出现 ALL（全表扫描），是因为主查询未添加任何 WHERE 筛选条件，与 NOT EXISTS 语法无关；若主查询带上合理筛选条件且字段建有索引，主表即可走 ref/range 索引匹配， NOT EXISTS 本身不会天然触发全表扫描。 NOT EXISTS 不会出现 NULL 导致的业务异常，是反向查询的最优方案。

8.8.4 开发规范

1. NOT IN 语法全场景禁用。

2. 反向查询逻辑必须使用 NOT EXISTS 实现。

3. IN 属于正常查询语法，满足索引条件可直接使用，无需替换为 EXISTS。

4. IN 内部传入批量参数过多时（超 500 个建议改用 JOIN），会导致优化器失效、性能下降，生产需控制参数数量，避免超大列表传入。

8.9 GROUP BY、ORDER BY 临时表与排序优化

8.9.1 故障现象

场景 1 错误 SQL，一定会产生临时表与文件排序

mysql > explain SELECT pay_status,COUNT(*) FROM order_info WHERE user_id = 10086 GROUP BY pay_status ORDER BY create_time;

执行计划如下：

id: 1
select_type: SIMPLE
table: order_info
partitions: NULL
type: ref
possible_keys: idx_user_id,idx_user_pay_createtime
key: idx_user_pay_createtime
key_len: 4
ref: const
rows: 1
filtered: 100.00
Extra: Using where; Using index; Using temporary; Using filesort

场景 2 优化 SQL，无临时表无排序

mysql > explain SELECT pay_status,COUNT(*) FROM order_info WHERE user_id = 10086 GROUP BY pay_status ORDER BY pay_status;

执行计划如下：

id: 1
select_type: SIMPLE
table: order_info
partitions: NULL
type: ref
possible_keys: idx_user_id,idx_user_pay_createtime
key: idx_user_pay_createtime
key_len: 4
ref: const
rows: 1
filtered: 100.00
Extra: Using where; Using index

场景 1 执行计划 Extra 显示 Using temporary 与 Using filesort，大数据量下查询性能极差。分组聚合后 create_time 属于明细字段，结果集中已不存在，无法依托索引完成排序。

8.9.2 底层原理

GROUP BY 合并明细数据后，未出现在分组与聚合中的字段不会保留。如果排序字段被剔除，即使存在联合索引也无法避免临时表。

GROUP BY 与 ORDER BY 组合缺少匹配索引时，MySQL 无法利用索引有序性，必须创建临时表存储分组数据，内存不足时临时表会落盘，并触发磁盘文件排序，产生巨大性能开销。

8.9.3 优化方案

分组排序查询想要彻底消除临时表与文件排序，必须遵循联合索引设计规则：WHERE 等值字段在前，GROUP BY 字段居中，ORDER BY 字段在后，三类字段连续组合建立联合索引。

筛选后的数据天然按分组与排序字段有序，MySQL 直接使用索引有序性完成分组与排序，无需临时表与二次排序。

最优联合索引：

CREATE INDEX idx_user_pay_createtime ON order_info(user_id, pay_status, create_time);

8.9.4 关键禁忌与核心总结

1. 索引顺序不能错乱，否则无法实现数据裁剪，依然会产生临时表与文件排序。

2. 范围条件会导致后续索引失效，WHERE 中出现 > < BETWEEN 等条件时，范围字段处于索引中间则后续分组与排序无法利用索引有序性，范围字段在索引末尾不受影响。

3. 无 WHERE 条件时，直接按 GROUP BY、ORDER BY 顺序建立索引即可。

8.10 索引列函数与运算导致索引失效

8.10.1 故障现象

mysql > explain SELECT * FROM order_info WHERE DATE(create_time) = '2025-01-01';

执行计划如下：

id: 1

select_type: SIMPLE

table: order_info

partitions: NULL

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 99885

filtered: 100.00

Extra: Using where

1 row in set, 1 warning (0.00 sec)

create_time 字段已建立索引，但语句执行全表扫描，索引无法生效。

8.10.2 底层原理

索引字段被 DATE、SUBSTR、四则运算等函数或计算操作包裹时，索引有序结构被破坏，优化器无法通过 B+ 树执行快速查找，只能放弃索引并进行全表扫描。

8.10.3 优化方案

将运算逻辑转移到常量一侧，保持索引字段以原生形态出现。

mysql > explain SELECT * FROM order_info WHERE create_time BETWEEN '2025-01-01 00:00:00' AND '2025-01-01 23:59:59';

执行计划如下：

id: 1

select_type: SIMPLE

table: order_info

partitions: NULL

type: range

possible_keys: idx_order_createtime

key: idx_order_createtime

key_len: 5

ref: NULL

rows: 1

filtered: 100.00

Extra: Using index condition

1 row in set, 1 warning (0.01 sec)

8.10.4 开发规范

WHERE 条件中，索引字段禁止使用任何函数、四则运算，必须保持索引字段原生形态，确保索引正常使用。

8.11 超大结果集与批量 DML 优化

8.11.1 超大结果集查询

无筛选、无分页的全表查询会一次性返回海量数据，占用大量内存与网络带宽，导致数据库性能下降。

错误示例：

mysql > explain SELECT * FROM order_info;

执行计划如下：

id: 1
select_type: SIMPLE
table: order_info
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 99885
filtered: 100.00
Extra: NULL

优化方案：

所有查询必须携带 WHERE 筛选条件，并使用 LIMIT 限制返回条数，避免无限制全表扫描。

mysql > explain SELECT * FROM order_info WHERE user_id = 10086 LIMIT 100;

执行计划如下：

id: 1
select_type: SIMPLE
table: order_info
partitions: NULL
type: ref
possible_keys: idx_user_id,idx_user_pay_createtime
key: idx_user_pay_createtime
key_len: 4
ref: const
rows: 1
filtered: 100.00
Extra: Using where; Using index

8.11.2 大批量 DML 操作

单次执行大规模 DELETE 或 UPDATE 会产生长事务，长时间占用锁资源，阻塞正常业务写入，同时导致 Undo 日志膨胀。

错误示例：

DELETE FROM order_info WHERE create_time < '2024-01-01';

优化方案：

采用主键循环分批处理，大字段表单次 100~200 条、普通小行表单次 500~1000 条，短事务快速提交，拆分锁占用时长。

DELETE FROM order_info WHERE id BETWEEN 10001 AND 10500;

8.12 高并发写入场景 SQL 优化

8.12.1 批量写入优化

高并发插入场景下，大量单条 INSERT 会频繁触发事务提交与日志刷盘，造成写入性能瓶颈。将多条单条插入合并为批量插入，可大幅减少 IO 与网络开销，显著提升写入效率。

低效批量插入示例：

INSERT INTO order_info(order_no,user_id) VALUES('202501',1001);

INSERT INTO order_info(order_no,user_id) VALUES('202502',1002);

优化批量插入示例：

INSERT INTO order_info(order_no,user_id) VALUES('202501',1001),('202502',1002);

8.12.2 避免大范围更新

高并发场景禁止无筛选条件的全表更新，防止临键锁锁定大范围索引区间，造成大量写入阻塞，影响业务稳定性。

大范围更新错误示例：

UPDATE order_info SET pay_status=2;

8.12.3 主键有序性

使用自增 ID 或雪花、UUIDv7 等有序 ID作为主键，随机 UUID（UUIDv4）无序插入导致频繁索引页分裂与数据碎片，有序主键提升高并发写入的稳定性与速度。

8.13 本章小结

本章开篇依托线上电商业务慢查询雪崩事故，梳理出 9 类高频劣质 SQL 引发的性能故障。先讲解 SQL 完整执行链路与 Explain 执行计划核心字段，建立调优基础；随后针对隐式类型转换、超大分页偏移、视图筛选无法下推、模糊查询索引失效、NOT IN 逻辑陷阱、分组排序临时表、索引列函数运算、超大结果集、批量 DML 长事务等典型问题逐一拆解。

每个案例统一采用故障现象 + 执行计划分析 + 底层原理 + 优化方案 + 开发规范的实战结构，内容紧扣 MySQL 5.6 特性，兼顾应急修复与长期规范，所有优化方案均来自生产实践，可直接落地。

通过本章学习，你能熟练使用 Explain 快速定位索引失效、临时表、文件排序等慢 SQL 根源，在开发阶段规避常见陷阱，从源头降低慢 SQL 上线风险，保障数据库高并发场景下的稳定运行。