SQL 问题排查全纪实 — 从写SQL到上线全流程实战

业务表结构 — 技术场景

全文所有例子都基于这四张表。先看清楚表结构和数据量，后面每个场景才有感觉。

order_info 借书单表 — 约50万行

CREATE TABLE order_info ( id BIGINT NOT NULL AUTO_INCREMENT, order_no VARCHAR(20) NOT NULL COMMENT '借书单号', user_id BIGINT NOT NULL COMMENT '用户ID', store_id INT NOT NULL COMMENT '门店ID', product_id INT NOT NULL COMMENT '产品ID', amount DECIMAL(10,2) NOT NULL COMMENT '金额', status TINYINT NOT NULL DEFAULT 0 COMMENT '0待支付 1已支付 2制作中 3已完成 4已取消', create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no), INDEX idx_user_id (user_id), INDEX idx_store_id (store_id), INDEX idx_create_time (create_time) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

product 产品表 — 约15万行

CREATE TABLE product ( id INT NOT NULL AUTO_INCREMENT, product_name VARCHAR(50) NOT NULL COMMENT '产品名', category VARCHAR(20) NOT NULL COMMENT '分类：技术/图书/技术/甜品', price DECIMAL(8,2) NOT NULL, status TINYINT NOT NULL DEFAULT 1 COMMENT '1上架 0下架', create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), INDEX idx_category (category) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

user 用户表 — 约30万行

CREATE TABLE user ( id BIGINT NOT NULL AUTO_INCREMENT, username VARCHAR(30) NOT NULL, phone VARCHAR(20) DEFAULT NULL, vip_level TINYINT NOT NULL DEFAULT 0 COMMENT '0普通 1银卡 2金卡 3钻石', register_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_phone (phone) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

store 门店表 — 约200行

CREATE TABLE store ( id INT NOT NULL AUTO_INCREMENT, store_name VARCHAR(50) NOT NULL, city VARCHAR(20) NOT NULL, address VARCHAR(200) NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

样例数据

-- order_info 样例 | id | order_no | user_id | store_id | product_id | amount | status | create_time | |----|---------------|---------|----------|------------|--------|--------|---------------------| | 1 | 202501150001 | 10001 | 5 | 120 | 28.00 | 1 | 2025-01-15 09:12:33 | | 2 | 202501150002 | 10086 | 3 | 85 | 35.50 | 3 | 2025-01-15 10:05:17 | | 3 | 202501150003 | 20045 | 5 | 120 | 28.00 | 2 | 2025-01-15 10:22:08 | -- product 样例 | id | product_name | category | price | status | |-----|--------------------|----------|-------|--------| | 85 | 机械键盘 | 技术 | 35.50| 1 | | 120 | 编程入门书 | 技术 | 28.00| 1 | | 210 | 算法精解 | 图书 | 22.00| 1 | -- user 样例 | id | username | phone | vip_level | register_time | |-------|----------|-------------|-----------|---------------------| | 10001 | 小明 | 13800000001 | 2 | 2024-03-15 10:00:00 | | 10086 | 小红 | 13900000001 | 3 | 2024-06-20 14:30:00 | | 20045 | 小李 | 13700000001 | 1 | 2024-09-01 08:15:00 |

B+树索引结构图（以 idx_user_id 为例）

🔽 根节点（Root）

key: 10001 → ptr key: 20000 → ptr key: 30000 → ptr

↓ ↓ ↓

🔽 非叶子节点（Non-Leaf）

key: 5025 → ptr key: 10001 → ptr

key: 15000 → ptr key: 20000 → ptr

key: 25000 → ptr key: 30000 → ptr

↓ ↓ ↓

🔽 叶子节点（Leaf）— 存完整行数据

id:1 user_id:10001 amount:28.00 status:1

id:2 user_id:10086 amount:35.50 status:3

id:3 user_id:20045 amount:28.00 status:2

← ↔ ↔ ↔ ↔ ↔ ↔ ↔ ↔ ↔ ↔ ↔ ↔ ↔ ↔ ↔ ↔ ↔ ↔ ↔ ↔ ↔ ↔ ↔ ↔ →
叶子节点之间双向链表连接

非叶子节点只存 key + 指针叶子节点存完整行数据

全表扫描 vs 索引扫描对比

❌ 全表扫描（type: ALL）

从第1行开始，逐行扫描...
行1: user_id=10001 → 比较 ❌
行2: user_id=10086 → 比较 ❌
行3: user_id=20045 → 比较 ❌
... (继续逐行扫描)
行499,999: user_id=... → 比较 ❌
行500,000: user_id=... → 比较 ❌
100万次比较 ⏱️ 极慢

磁盘IO：需要读取所有数据页

✅ 索引扫描（type: ref）

通过B+树索引，3层定位到数据
第1层(根节点): 找到 user_id=10086 → ptr
第2层(非叶子): 缩小范围 → ptr
第3层(叶子): 精确命中! user_id=10086 ✅
3次IO搞定 ⚡ 极快

磁盘IO：只需读取3个页面

全表扫描：O(n) → 索引扫描：O(log n)

场景1：对字段使用函数导致索引失效

慢查询索引失效开发阶段发现

开发阶段 — 写SQL

小张接到需求："查1月15号这天下单的所有借书单"。他在IDEA里打开OrderMapper.xml，写了这个查询：

OrderMapper.xml — milk-tea-order-service

OrderService.java

OrderMapper.xml

OrderMapper.java

📂 src/main

📂 java/com/milktea

📄 OrderService.java

📄 OrderMapper.java

📂 resources

📂 mapper

📄 OrderMapper.xml

1<?xml version="1.0" encoding="UTF-8"?>

2<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

3 "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

4<mapper namespace="com.milktea.mapper.OrderMapper">

6

7 <select id="selectByDate" resultType="Order">

8 SELECT * FROM order_info

9 WHERE DATE(create_time) = #{date}

10 </select>

12</mapper>

UTF-8 XML ✓ 无错误

对应的SQL就是：

SELECT * FROM order_info WHERE DATE(create_time) = '2025-01-15';

EXPLAIN 验证

小张在DataGrip里跑了一下EXPLAIN，看看执行计划：

EXPLAIN SELECT * FROM order_info WHERE DATE(create_time) = '2025-01-15';

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	order_info	ALL	NULL	NULL	NULL	NULL	498231	Using where

1 row in set (0.00 sec) | 扫描行数: 498,231

小张的疑惑：create_time上明明有索引idx_create_time啊！为什么key=NULL没走索引？

想通了：我对create_time字段包了一层DATE()函数。MySQL需要对每一行数据先执行DATE(create_time)计算出结果，再跟'2025-01-15'比较。因为经过了函数计算，B+树索引的有序性就被破坏了，MySQL没法利用索引做查找，只能全表扫描50万行。

修复方案 — 改成范围查询

核心思路：不要对字段用函数，而是用范围条件把函数"转移"到参数上：

OrderMapper.xml — 修改后

OrderService.java

OrderMapper.xml

📂 src/main

📄 OrderMapper.xml

6

7 <select id="selectByDate" resultType="Order">

8 SELECT * FROM order_info

9 WHERE create_time >= #{startDate}

10 AND create_time < #{endDate}

11 </select>

-- 修复后的SQL：startDate = '2025-01-15 00:00:00', endDate = '2025-01-16 00:00:00' SELECT * FROM order_info WHERE create_time >= '2025-01-15 00:00:00' AND create_time < '2025-01-16 00:00:00';

验证修复

EXPLAIN SELECT * FROM order_info WHERE create_time >= '2025-01-15 00:00:00' AND create_time < '2025-01-16 00:00:00';

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	order_info	range	idx_create_time	idx_create_time	6	NULL	2847	Using index condition

1 row in set (0.00 sec) | 扫描行数: 2,847（从498,231降到2,847）

修复前
type: ALL（全表扫描）
key: NULL（没走索引）
rows: 498,231
执行时间: 2.3s

修复后
type: range（范围扫描）
key: idx_create_time ✅
rows: 2,847
执行时间: 0.02s

场景结论：开发阶段就通过EXPLAIN发现并修复了问题，没有流到生产环境。对字段使用函数（DATE、YEAR、MONTH、SUBSTRING等）会导致索引失效，应改用范围查询。

💡 同类问题举一反三

以下写法都会导致索引失效，原理和DATE()一样——对字段做了计算/函数：

-- ❌ YEAR()函数 WHERE YEAR(create_time) = 2025 -- ✅ 改成范围 WHERE create_time >= '2025-01-01' AND create_time < '2026-01-01' -- ❌ 计算表达式 WHERE id + 1 = 100 -- ✅ 把计算移到参数这边 WHERE id = 100 - 1 -- ❌ SUBSTRING函数 WHERE SUBSTRING(order_no, 1, 8) = '20250115' -- ✅ 改成LIKE右模糊 WHERE order_no LIKE '20250115%'

场景2：隐式类型转换导致索引失效

慢查询类型不匹配容易踩坑

开发阶段 — 写SQL

小张写了一个根据借书单号查借书单的接口。order_no字段是VARCHAR(20)类型，但Java代码里传参时不小心没加引号：

OrderService.java — milk-tea-order-service

OrderService.java

OrderMapper.xml

📂 src/main/java

📄 OrderService.java

📄 OrderMapper.java

1public class OrderService {

3 @Autowired

4 private OrderMapper orderMapper;

6 public Order getByOrderNo(String orderNo) {

7 // orderNo = "202501150001"

8 return orderMapper.selectByOrderNo(orderNo);

9 }

10}

看起来没问题？但如果在DataGrip里直接测试，手写SQL时漏了引号：

-- 注意：order_no是VARCHAR(20)，但传了个数字（没加引号） SELECT * FROM order_info WHERE order_no = 202501150001;

EXPLAIN 验证

EXPLAIN SELECT * FROM order_info WHERE order_no = 202501150001;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	order_info	ALL	NULL	NULL	NULL	NULL	498231	Using where

1 row in set (0.00 sec) | 扫描行数: 498,231

为什么索引失效？order_no是VARCHAR类型，但查询条件给的是数字。MySQL的类型转换规则是"把字符串转成数字"（因为数字类型优先级高），所以实际执行变成了：

CAST(order_no AS SIGNED) = 202501150001

这跟场景1一样——对字段做了隐式的CAST函数，索引就被废掉了。

更坑的是：这个bug在Java代码里用MyBatis可能不会触发（因为MyBatis的#{date}会正确处理类型），但在手动写SQL、存储过程、或者用${}拼接时就会踩到！

类型不匹配示意

字段	定义类型	查询值类型	MySQL行为
order_no	VARCHAR(20)	BIGINT (数字)	把VARCHAR转数字 → 索引失效
order_no	VARCHAR(20)	VARCHAR (字符串)	类型匹配 → 走唯一索引

修复方案 — 加引号

-- 修复：加引号，保证类型匹配 SELECT * FROM order_info WHERE order_no = '202501150001';

EXPLAIN SELECT * FROM order_info WHERE order_no = '202501150001';

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	order_info	const	uk_order_no	uk_order_no	62	const	1	NULL

1 row in set (0.00 sec) | 扫描行数: 1（从498,231降到1）

修复前（没引号）
type: ALL | key: NULL
rows: 498,231
执行时间: 1.8s

修复后（有引号）
type: const | key: uk_order_no
rows: 1
执行时间: 0.001s

场景结论：字段是字符串类型，查询条件也必须传字符串。MySQL会"帮你"做隐式转换，代价是索引失效。MyBatis用#{param}通常不会有这个问题（框架帮你处理了类型），但手写SQL、${}拼接、存储过程中一定要小心。

💡 哪种情况会实际触发这个bug？

在Java/MyBatis项目中，以下几种写法都可能触发隐式类型转换：

-- 1. MyBatis 用 ${} 拼接（不会自动处理类型） WHERE order_no = ${orderNo} -- 如果orderNo是String "202501150001" -- 生成: WHERE order_no = 202501150001 （没引号！） -- 2. MyBatis 用 #{ } 但 parameterType 不匹配 -- 如果Mapper方法参数是Integer类型但字段是VARCHAR -- 3. 存储过程中用变量拼接SQL SET @sql = CONCAT('SELECT * FROM order_info WHERE order_no = ', v_order_no); -- 如果v_order_no是数字变量，拼接出来的就没引号 -- 4. 在DataGrip/Navicat里手动测试SQL时忘记加引号

场景3：LIKE '%xxx' 左模糊查询无法走索引

左模糊全表扫描测试阶段发现

开发阶段 — 写SQL

运营说"我要搜产品名包含'键盘'的所有技术"，小张直接写了LIKE：

ProductMapper.xml — milk-tea-product-service

📂 resources/mapper

📄 ProductMapper.xml

1

2<select id="searchByName" resultType="Product">

3 SELECT * FROM product

4 WHERE product_name LIKE CONCAT('%', #{keyword}, '%')

5</select>

-- 实际执行的SQL SELECT * FROM product WHERE product_name LIKE '%键盘%';

EXPLAIN 验证

EXPLAIN SELECT * FROM product WHERE product_name LIKE '%键盘%';

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	product	ALL	NULL	NULL	NULL	NULL	156803	Using where

扫描行数: 156,803（全表扫描了15万行产品表）

B+树索引的查找原理：索引是按字段值从左到右排序的。比如product_name的索引大概是这样排列的：

入门键盘 → 蓝牙鼠标 → 机械键盘 → 游戏手柄 → 编程入门书 → ...

当你用LIKE '键盘%'（右模糊），MySQL可以直接跳到"键盘"开头的位置，然后顺序往后读。
但LIKE '%键盘%'（两边都有%），MySQL不知道从B+树的哪个节点开始找，只能遍历所有节点——全表扫描。

修复方案

方案1：只用右模糊（如果业务允许）

-- 只有右模糊 LIKE 'xxx%'，可以走索引 SELECT * FROM product WHERE product_name LIKE '键盘%';

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	product	range	idx_product_name	idx_product_name	152	NULL	86	Using index condition

扫描行数: 86（从156,803降到86）

方案2：FULLTEXT全文索引（需要真正的全文搜索）

-- 给product_name加全文索引 ALTER TABLE product ADD FULLTEXT INDEX ft_product_name(product_name); -- 用MATCH AGAINST做全文搜索 SELECT * FROM product WHERE MATCH(product_name) AGAINST('键盘' IN BOOLEAN MODE);

方案3：接入Elasticsearch（海量数据推荐）

如果产品表数据量到百万级以上，MySQL的FULLTEXT也不够用，应该把商品数据同步到ES，用ES做全文搜索。这是后端的常规做法：MySQL负责事务，ES负责搜索。

LIKE '%键盘%' (左模糊)
type: ALL | rows: 156,803
执行时间: 0.45s
全表扫描15万行

LIKE '键盘%' (右模糊)
type: range | rows: 86
执行时间: 0.003s
走索引，只扫86行

💡 LIKE三种写法对比

写法	能否走索引	适用场景
LIKE '键盘%'	✅ 可以	前缀匹配（搜"以XX开头"的产品名）
LIKE '%键盘'	❌ 不行	后缀匹配（几乎用不到）
LIKE '%键盘%'	❌ 不行	包含匹配（最常见但最慢，用FULLTEXT或ES替代）

场景4：ORDER BY导致Using filesort

filesort 排序开销

开发阶段 — 写SQL

需求："查最近100个借书单，按下单时间倒序"。小张写了：

SELECT * FROM order_info ORDER BY create_time DESC LIMIT 100;

EXPLAIN 验证

EXPLAIN SELECT * FROM order_info ORDER BY create_time DESC LIMIT 100;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	order_info	ALL	NULL	NULL	NULL	NULL	498231	Using filesort

扫描行数: 498,231 | Extra: Using filesort ⚠️

Using filesort 是什么意思？

MySQL把49万行数据全部读出来，然后在内存里做了一次快速排序（filesort），最后取前100条返回。明明只需要100条数据，却对50万行做了排序——太浪费了！

原因：create_time上没有索引（或者索引没有被使用），MySQL没法利用索引的有序性来避免排序操作。

修复 — 给排序字段加索引

-- 给create_time加索引（如果DDL里没加的话） ALTER TABLE order_info ADD INDEX idx_create_time (create_time);

-- 加了索引后再看EXPLAIN EXPLAIN SELECT * FROM order_info ORDER BY create_time DESC LIMIT 100;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	order_info	index	NULL	idx_create_time	6	NULL	100	NULL

扫描行数: 100 | Extra: NULL（无filesort，直接按索引顺序读取）

发生了什么变化？

加了索引后，B+树叶子节点本身就是按create_time排序的。MySQL从索引的最后一个节点（DESC倒序）开始，直接读100条就完事了。不需要排序，不需要扫描50万行。

type=index 表示"索引全扫描"，但因为LIMIT 100，实际上只读了100行索引节点，然后回表取数据。

修复前（无索引）
type: ALL
rows: 498,231
Extra: Using filesort
执行时间: 1.8s

修复后（有索引）
type: index
rows: 100
Extra: NULL（无排序）
执行时间: 0.003s

深分页扫描可视化 — LIMIT 200000, 20 到底做了什么？

SQL: SELECT * FROM order_info ORDER BY id LIMIT 200000, 20

📖 读取 200,020 行 → 🗑️ 丢弃前 200,000 行 → ✅ 返回最后 20 行

浪费！200,000 行白白读取
然后被丢弃

→

真正需要的
仅 20 行

99.99% 的IO是浪费的 → 需要优化：游标分页 / 延迟关联

场景5：深翻页（LIMIT深分页）

深翻页越翻越慢后台管理常见

开发阶段 — 写SQL

后台管理系统要"导出第10000页的数据，每页20条"。前端传过来page=10000, size=20：

-- 第10000页，每页20条 → OFFSET = 10000 * 20 = 200000 SELECT * FROM order_info ORDER BY id LIMIT 200000, 20;

EXPLAIN 验证

EXPLAIN SELECT * FROM order_info ORDER BY id LIMIT 200000, 20;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	order_info	index	NULL	PRIMARY	8	NULL	200020	NULL

扫描行数: 200,020 — 扫了20万行只取20条

为什么深翻页这么慢？

MySQL的LIMIT 200000, 20不是"跳到第200000行"再取20条。它的实际执行过程是：
1. 从第1行开始扫描
2. 扫描并丢弃前200,000行
3. 把接下来的20行返回给你

所以翻到第10000页，MySQL其实扫描了200,020行！翻得越深越慢，这个查询的时间和页数成正比。

测试验证 — 实际测量翻页时间

SQL	OFFSET	扫描行数	执行时间
LIMIT 0, 20	0	20	0.002s
LIMIT 100000, 20	100,000	100,020	0.8s
LIMIT 200000, 20	200,000	200,020	1.6s
LIMIT 500000, 20	500,000	500,020	4.2s

翻页越深，扫描行数越多，时间线性增长

修复方案1：游标翻页（推荐）

OrderController.java — 游标翻页

📂 controller

📄 OrderController.java

1@GetMapping("/orders/cursor")

2public PageResult<Order> listByCursor(

3 @RequestParam("lastId", defaultValue = "0") Long lastId) {

4 // 前端传上一页最后一条的id

5 return orderMapper.selectByCursor(lastId, 20);

-- 游标翻页：WHERE id > 上次最后一条id，只扫20行 SELECT * FROM order_info WHERE id > 200000 ORDER BY id LIMIT 20;

EXPLAIN SELECT * FROM order_info WHERE id > 200000 ORDER BY id LIMIT 20;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	order_info	range	PRIMARY	PRIMARY	8	NULL	20	Using where

扫描行数: 20 | 执行时间: 0.002s

修复方案2：子查询优化（兼容传统分页）

如果前端必须要传page参数（不支持游标），可以用子查询先拿到id，再回表取数据：

-- 子查询优化：先在索引上翻页取id（覆盖索引，不需要回表） SELECT * FROM order_info WHERE id IN ( SELECT id FROM order_info ORDER BY id LIMIT 200000, 20 );

原始写法
LIMIT 200000, 20
rows: 200,020
时间: 1.6s
扫20万行取20条

游标翻页
WHERE id > 200000 LIMIT 20
rows: 20
时间: 0.002s
利用主键索引直接定位

💡 深翻页方案选择指南

方案	优点	缺点	适用场景
游标翻页	性能最好，恒定0.002s	只能"上一页/下一页"，不能跳页	App无限滚动列表
子查询优化	兼容传统分页参数	仍然需要扫描OFFSET行索引	后台管理系统
限制最大页数	最简单	用户体验受限	大部分用户不会翻超过100页

场景6：OR条件导致索引失效

OR条件索引选择困难

开发阶段 — 写SQL

需求："查用户10086的所有借书单，或者门店5的所有借书单"（后台管理要看某用户或某门店的借书单）：

SELECT * FROM order_info WHERE user_id = 10086 OR store_id = 5;

EXPLAIN 验证

EXPLAIN SELECT * FROM order_info WHERE user_id = 10086 OR store_id = 5;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	order_info	ALL	idx_user_id,idx_store_id	NULL	NULL	NULL	498231	Using where

possible_keys有两个索引但 key=NULL，MySQL选择了全表扫描

为什么两个索引都没走？

MySQL看到了两个选择：
选择A：走idx_user_id找到user_id=10086的行，再逐行检查store_id=5
选择B：走idx_store_id找到store_id=5的行，再逐行检查user_id=10086
选择C：用Index Merge（两个索引都走，再合并结果）

MySQL优化器计算了一下，发现A和B都需要额外检查大量行，C的合并成本也高。最终判断：全表扫描50万行的成本反而最低。

解决方法：把OR拆成两个独立的查询，用UNION ALL合并结果。这样每个查询都能独立走自己的索引。

修复方案 — UNION ALL拆分

SELECT * FROM order_info WHERE user_id = 10086 UNION ALL SELECT * FROM order_info WHERE store_id = 5 AND user_id != 10086;

EXPLAIN SELECT * FROM order_info WHERE user_id = 10086 UNION ALL SELECT * FROM order_info WHERE store_id = 5 AND user_id != 10086;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	order_info	ref	idx_user_id	idx_user_id	8	const	156	NULL
2	UNION	order_info	ref	idx_user_id,idx_store_id	idx_store_id	4	const	24912	Using where

第一个查询走idx_user_id扫156行，第二个走idx_store_id扫24,912行。总扫描约25,068行（远小于498,231）

OR写法
type: ALL
rows: 498,231
执行时间: 2.1s

UNION ALL写法
两个查询各走索引
rows: 156 + 24,912 = 25,068
执行时间: 0.05s

场景7：GROUP BY产生Using temporary临时表

Using temporary Using filesort 统计报表常见

开发阶段 — 写SQL

运营要一个报表："统计每个门店每天的借书单数量"。小张写了：

SELECT store_id, DATE(create_time) AS dt, COUNT(*) AS cnt FROM order_info GROUP BY store_id, DATE(create_time);

EXPLAIN 验证

EXPLAIN SELECT store_id, DATE(create_time) AS dt, COUNT(*) AS cnt FROM order_info GROUP BY store_id, DATE(create_time);

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	order_info	ALL	NULL	NULL	NULL	NULL	498231	Using temporary; Using filesort

Extra: Using temporary; Using filesort — 两个坏消息同时出现！

两个坏消息：

Using temporary：MySQL创建了一个内部临时表来处理GROUP BY。因为GROUP BY里有DATE(create_time)，MySQL需要先计算每一行的日期，再把结果写入临时表做分组。50万行数据在内存里建临时表，如果内存不够还会写到磁盘。

Using filesort：GROUP BY隐含了排序操作，MySQL对分组结果又做了一次排序。

双重打击：全表扫描50万行 + 临时表 + 排序。在50万行数据上这个查询可能要跑好几秒甚至十几秒。

修复方案

两个优化点：1）加复合索引 2）避免在GROUP BY里用函数（加时间范围限制）

-- 1. 加复合索引 ALTER TABLE order_info ADD INDEX idx_store_time (store_id, create_time); -- 2. 改写SQL：加时间范围 + 避免GROUP BY里用函数 SELECT store_id, DATE(create_time) AS dt, COUNT(*) AS cnt FROM order_info WHERE create_time >= '2025-01-01 00:00:00' AND create_time < '2025-02-01 00:00:00' GROUP BY store_id, create_time;

-- 优化后的EXPLAIN EXPLAIN SELECT store_id, DATE(create_time) AS dt, COUNT(*) AS cnt FROM order_info WHERE create_time >= '2025-01-01' AND create_time < '2025-02-01' GROUP BY store_id, create_time;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	order_info	range	idx_store_time	idx_store_time	14	NULL	42156	Using index condition

Using temporary消失了！Using filesort也消失了！扫描行数从498,231降到42,156

修复前
type: ALL | rows: 498,231
Extra: Using temporary; Using filesort
执行时间: 5.8s

修复后
type: range | rows: 42,156
Extra: Using index condition
执行时间: 0.15s

场景8：子查询优化 → 改JOIN

子查询改JOIN 性能灾难

开发阶段 — 写SQL

需求："查下单过'键盘'类产品的用户信息"。小张习惯性用了子查询嵌套：

DataGrip — 原始子查询

-- 三层子查询嵌套：看起来逻辑清晰，但性能可能很差 SELECT * FROM user WHERE id IN ( SELECT user_id FROM order_info WHERE product_id IN ( SELECT id FROM product WHERE product_name LIKE '%键盘%' ) );

EXPLAIN 验证

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	product	ALL	PRIMARY	NULL	NULL	NULL	156803	Using where; Using join buffer
1	SIMPLE	order_info	ref	idx_user_id	idx_user_id	8	milk_tea.user.id	5	Using where
1	SIMPLE	user	eq_ref	PRIMARY	PRIMARY	8	func	1	Using where

product表全表扫描156,803行 + Using join buffer — 效率很低

问题在哪？

虽然MySQL 5.6+对子查询做了优化（不再完全是"逐行执行子查询"），但这个查询仍然：
1. product表全表扫描（因为LIKE '%键盘%'走不了索引）
2. 三张表做了嵌套循环连接
3. Using join buffer说明MySQL需要在内存里缓存中间结果

改成JOIN：逻辑更清晰，MySQL优化器更容易选择最优的执行计划。

修复方案 — 改写成JOIN

-- 改成JOIN：三张表连接，逻辑等价但执行计划更优 SELECT DISTINCT u.* FROM user u JOIN order_info o ON u.id = o.user_id JOIN product p ON o.product_id = p.id WHERE p.product_name LIKE '%键盘%';

-- JOIN版本的EXPLAIN EXPLAIN SELECT DISTINCT u.* FROM user u JOIN order_info o ON u.id = o.user_id JOIN product p ON o.product_id = p.id WHERE p.product_name LIKE '%键盘%';

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	p	ALL	PRIMARY	NULL	NULL	NULL	156803	Using where; Using temporary
1	SIMPLE	o	ref	idx_user_id,idx_product_id	idx_product_id	4	milk_tea.p.id	3	NULL
1	SIMPLE	u	eq_ref	PRIMARY	PRIMARY	8	milk_tea.o.user_id	1	NULL

JOIN版本：order_info走idx_product_id（每次只扫3行），user走主键（每次1行）。整体效率大幅提升

对比分析：

product表的LIKE '%键盘%'走不了索引，全表扫描——这个没法避免（除非用ES）。

但关键是后面的连接方式变了：
子查询版本：MySQL先找product里的键盘，然后对每个用户去order_info里查 → 产生了join buffer
JOIN版本：MySQL先找product里的键盘，然后通过idx_product_id精确找到对应的order_info行，再通过主键找到user → 利用索引直接定位

JOIN让MySQL优化器有更多自由度来选择连接顺序和方式。

子查询版本
product: ALL 156,803行
+ Using join buffer
执行时间: 3.2s

JOIN版本
product: ALL（无法避免）
但后续连接走索引，每次3行+1行
执行时间: 0.35s

单条INSERT vs 批量INSERT对比（插入1000条数据）

❌ 单条INSERT — 1000次网络往返

INSERT INTO order_info VALUES (...);
→ 网络1次往返 → 提交事务
INSERT INTO order_info VALUES (...);
→ 网络1次往返 → 提交事务
INSERT INTO order_info VALUES (...);
→ 网络1次往返 → 提交事务
... 重复 997 次 ...

1000次网络往返

每次都要：发送SQL → 解析 → 执行 → 返回

✅ 批量INSERT — 1次网络往返

INSERT INTO order_info VALUES
(...), (...), (...),
(...), (...), (...),
... 共 1000 条 ...
→ 网络1次往返 → 批量提交

1次网络往返

一次发送所有数据 → 批量解析执行

1000次IO ≈ 数十秒 → 1次IO ≈ 亚秒级

场景9：批量INSERT性能优化

批量插入 MyBatis BATCH 网络往返

开发阶段 — 循环单条INSERT

小张要插入1000条借书单数据（比如从Excel导入）。他写了最直觉的for循环：

OrderImportService.java — ❌ 低效写法

OrderImportService.java

OrderMapper.java

📂 service

📄 OrderImportService.java

1@Service

2public class OrderImportService {

4 @Autowired

5 private OrderMapper orderMapper;

7 public void importOrders(List<Order> orders) {

8 // ❌ 循环1000次，每次一条INSERT

9 for (Order order : orders) {

10 orderMapper.insert(order); // 1000次网络往返！

11 }

12 }

13}

对应的SQL会被执行1000次：

-- 这条SQL会被执行1000次 INSERT INTO order_info (order_no, user_id, store_id, product_id, amount, status, create_time) VALUES ('202501150001', 10001, 5, 120, 28.00, 0, '2025-01-15 09:12:33');

为什么慢？

每次INSERT都需要：
1. Java → MySQL 建立网络请求
2. MySQL 解析SQL
3. MySQL 执行插入（更新索引、写redo log、写binlog）
4. MySQL → Java 返回结果

1000次INSERT = 1000次网络往返 + 1000次SQL解析 + 1000次事务提交（如果autoCommit=true）
网络IO和事务提交是最大的性能杀手。

优化方案1：批量INSERT

-- 一条INSERT插入多行值，减少SQL解析和事务提交次数 INSERT INTO order_info (order_no, user_id, store_id, product_id, amount, status, create_time) VALUES ('202501150001', 10001, 5, 120, 28.00, 0, '2025-01-15 09:12:33'), ('202501150002', 10086, 3, 85, 35.50, 0, '2025-01-15 10:05:17'), ('202501150003', 20045, 5, 120, 28.00, 0, '2025-01-15 10:22:08'), ... -- 1000行value ;

优化方案2：MyBatis BATCH模式（推荐）

OrderImportService.java — ✅ 批量模式

OrderImportService.java

📂 service

📄 OrderImportService.java

1@Service

2public class OrderImportService {

4 @Autowired

5 private SqlSessionFactory sqlSessionFactory;

7 public void importOrders(List<Order> orders) {

8 // ✅ 使用BATCH模式的SqlSession

9 try (SqlSession session = sqlSessionFactory.openSession(

10 ExecutorType.BATCH)) {

11 OrderMapper mapper = session.getMapper(OrderMapper.class);

12 for (Order order : orders) {

13 mapper.insert(order); // 不立即执行，攒着

14 }

15 session.flushStatements(); // 一次性发给MySQL

16 session.commit();

17 }

18 }

19}

性能对比实测

方式	网络往返次数	SQL解析次数	事务提交次数	执行时间
循环单条INSERT	1000次	1000次	1000次	12.5s
批量INSERT（一条SQL）	1次	1次	1次	0.8s
MyBatis BATCH模式	1次	1次	1次	0.5s

BATCH模式比循环单条快 25倍

注意事项：批量INSERT要注意MySQL的max_allowed_packet限制。如果一次插入太多行，SQL包可能超过大小限制导致失败。

# 查看当前max_allowed_packet大小 mysql> SHOW VARIABLES LIKE 'max_allowed_packet'; +--------------------+----------+ | Variable_name | Value | +--------------------+----------+ | max_allowed_packet | 4194304 | # 4MB，默认值 +--------------------+----------+ # 如果批量INSERT太大，可以临时调大 mysql> SET GLOBAL max_allowed_packet = 67108864; # 调到64MB # 经验值：1000条INSERT约 200KB-500KB，通常不会超过4MB # 如果超过1万条，建议分批提交（每1000条commit一次）

场景10：生产实战 — 线上慢查询排查全流程

线上事故 CPU飙高完整排查流程

这不是一个简单的SQL优化，而是一次完整的线上故障排查过程。从告警到定位到修复到复盘，还原真实的生产排障经历。

14:30 钉钉告警

🔔 技术-数据库监控告警群

14:30:15

🤖

监控机器人

⚠️ 数据库告警
实例: mysql-prod-01
CPU使用率: 92%（阈值80%）
持续时间: 5分钟
当前活跃连接数: 285
请立即排查！

14:30:45

王

TL老王

@小张数据库CPU飙了，看下是不是有慢SQL

张

小张

收到，马上看

14:32 登录MySQL查看当前执行的SQL

# 小张SSH到生产服务器，登录MySQL $ mysql -u admin -p milk_tea_shop # 查看当前正在执行的SQL进程 mysql> SHOW PROCESSLIST; +-----+------+-----------------+---------------+---------+------+--------------+--------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +-----+------+-----------------+---------------+---------+------+--------------+--------------------------------------------------+ | 12 | app | 10.0.1.5:42321 | milk_tea_shop | Query | 180 | Sending data | SELECT * FROM order_info WHERE status = 1 ... | | 25 | app | 10.0.1.5:42322 | milk_tea_shop | Query | 45 | Sending data | SELECT * FROM order_info WHERE status = 1 ... | | 38 | app | 10.0.1.5:42323 | milk_tea_shop | Query | 12 | Sending data | SELECT * FROM order_info WHERE status = 1 ... | | ... | ... | ... | ... | ... | ... | ... | ... | | 285 | app | 10.0.1.5:42600 | milk_tea_shop | Sleep | 0 | NULL | NULL | +-----+------+-----------------+---------------+---------+------+--------------+--------------------------------------------------+ ↑ 发现一条SQL执行了180秒还没完！而且同样的SQL有大量并发在执行！

14:35 查看慢查询日志

# 查看慢查询日志是否开启 mysql> SHOW VARIABLES LIKE 'slow_query%'; +---------------------+-----------------------------------+ | Variable_name | Value | +---------------------+-----------------------------------+ | slow_query_log | ON | | slow_query_log_file | /var/lib/mysql/mysql-slow.log | +---------------------+-----------------------------------+ mysql> SHOW VARIABLES LIKE 'long_query_time'; +-------------------+----------+ | Variable_name | Value | +-------------------+----------+ | long_query_time | 1.000000 | # 超过1秒的查询会被记录 +-------------------+----------+ # 用mysqldumpslow分析最近10条最慢的SQL $ mysqldumpslow -s t -t 10 /var/lib/mysql/mysql-slow.log Reading mysql slow query log from /var/lib/mysql/mysql-slow.log Count: 342 Time=45.62s (15593s) Lock=0.00s Rows=245816.0 SELECT * FROM order_info WHERE status = 'S' AND create_time > 'S' ORDER BY create_time DESC LIMIT N ↑ 这条SQL出现了342次！平均每次45.62秒！总耗时15593秒！ Count: 15 Time=3.21s (48s) Lock=0.00s Rows=1.0 SELECT * FROM user WHERE phone = 'S'

14:38 定位到问题SQL

问题SQL找到了！


      SELECT * FROM order_info WHERE status = 1 AND create_time > '2025-01-01' ORDER BY create_time DESC LIMIT 20

这条SQL被大量并发执行，每次耗时45秒。285个数据库连接中大部分都在执行这个查询。

🔔 技术-数据库监控告警群

14:38

张

小张

找到问题了！运营刚上了一个新功能"按状态筛选最近借书单"，SQL走全表扫描。50万行数据，没走索引。

王

TL老王

先紧急加个索引，别让数据库挂了

14:40 EXPLAIN分析问题SQL

-- 问题SQL的EXPLAIN EXPLAIN SELECT * FROM order_info WHERE status = 1 AND create_time > '2025-01-01' ORDER BY create_time DESC LIMIT 20;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	order_info	ALL	idx_create_time	NULL	NULL	NULL	498231	Using where; Using filesort

type=ALL 全表扫描 + Using filesort + rows=498,231。双重打击。

分析原因：

1. WHERE条件有两个：status=1 和 create_time>'2025-01-01'
2. create_time有索引（idx_create_time），但status没有索引
3. ORDER BY create_time DESC需要排序
4. MySQL优化器的判断：走idx_create_time索引的话，需要在索引上找到create_time>'2025-01-01'的行（大约45万行），然后逐行检查status=1，最后排序取20条。成本还不如直接全表扫描。

解决方案：给(status, create_time)建复合索引。这样MySQL可以：
先通过索引找到status=1的行，然后在索引上已经按create_time排好序了，直接取最新的20条，不需要filesort。

14:45 紧急加索引

# 紧急加复合索引（注意：线上DDL可能会锁表，MySQL 5.7+可以用Online DDL） mysql> ALTER TABLE order_info ADD INDEX idx_status_create (status, create_time), ALGORITHM=INPLACE, LOCK=NONE; Query OK, 0 rows affected (8.52 sec) Records: 0 Duplicates: 0 Warnings: 0 索引创建完成！耗时8.52秒

线上加索引注意事项：
1. MySQL 5.6+ 支持 Online DDL（ALGORITHM=INPLACE），不会完全锁表
2. 加LOCK=NONE允许并发DML操作
3. 但大表（百万行以上）加索引仍然需要几分钟，建议在低峰期操作
4. 更安全的做法是用 pt-online-schema-change 工具

14:48 验证修复效果

-- 加索引后的EXPLAIN EXPLAIN SELECT * FROM order_info WHERE status = 1 AND create_time > '2025-01-01' ORDER BY create_time DESC LIMIT 20;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	order_info	ref	idx_status_create	idx_status_create	7	const	86245	Using index condition

type=ref 走索引了！Using filesort消失了！rows从498,231降到86,245

修复前
type: ALL
rows: 498,231
Extra: Using where; Using filesort
执行时间: 45s

修复后
type: ref
rows: 86,245
Extra: Using index condition
执行时间: 0.08s

14:50 Grafana监控恢复正常

📊 Grafana mysql-prod-01 | Last 30 minutes

MySQL CPU Usage

15% ↓ from 92%

14:30告警 → 14:45加索引 → 14:50恢复

Active Connections

35 ↓ from 285

Slow Queries / min

0 ↓ from 45

🔔 技术-数据库监控告警群

14:52

🤖

监控机器人

✅ 告警恢复
实例: mysql-prod-01
CPU使用率: 15%
活跃连接数: 35
持续时间: 22分钟

张

小张

已恢复！加了个复合索引(status, create_time)，查询从45秒降到0.08秒。等下写个复盘文档。

王

TL老王

好的，顺便review一下运营那边新上的查询，其他SQL也过一遍EXPLAIN

15:00 事故复盘

📋 故障复盘 — 2025-01-15 数据库CPU飙高事故

14:25 运营上线新功能 — "按状态筛选最近借书单"页面上线，触发新SQL查询

14:28 CPU开始飙升 — 新SQL走全表扫描，每个请求耗时45秒，大量并发连接堆积

14:30 钉钉告警触发 — 数据库CPU使用率达92%，超过80%阈值

14:32 小张开始排查 — SHOW PROCESSLIST 发现大量同一条SQL在执行

14:35 分析慢查询日志 — mysqldumpslow定位到问题SQL出现342次

14:40 EXPLAIN分析 — 确认是全表扫描 + filesort，缺少(status, create_time)索引

14:45 紧急加索引 — ALTER TABLE 加复合索引，耗时8.52秒

14:48 验证修复 — EXPLAIN确认走索引，查询从45s降到0.08s

14:50 监控恢复 — CPU降到15%，连接数恢复正常

改进措施

根因：新上线的SQL没有经过EXPLAIN验证就直接发布到生产环境

改进1：SQL上线流程 — 所有新SQL必须经过EXPLAIN验证，确认type不是ALL、Extra没有filesort/temporary

改进2：慢查询监控 — 新增"慢查询数量突增"告警规则，而不是等CPU飙高才告警

改进3：索引规范 — 建表时要把常用查询条件的字段组合考虑进去，提前建好复合索引

改进4：代码Review — 新增的Mapper XML必须经过Review，重点检查SQL是否有索引失效风险

线上排查工具箱 — 记住这些命令

# 1. 查看当前正在执行的SQL（快速定位长事务） mysql> SHOW PROCESSLIST; # 如果只想看执行超过5秒的： mysql> SELECT * FROM information_schema.PROCESSLIST WHERE TIME > 5; # 2. 查看慢查询日志配置 mysql> SHOW VARIABLES LIKE 'slow_query%'; mysql> SHOW VARIABLES LIKE 'long_query_time'; # 3. 开启慢查询日志（如果没开的话） mysql> SET GLOBAL slow_query_log = ON; mysql> SET GLOBAL long_query_time = 1; # 超过1秒记录 # 4. 用mysqldumpslow分析慢日志 $ mysqldumpslow -s t -t 10 /var/lib/mysql/mysql-slow.log # 按时间排序，取前10条 $ mysqldumpslow -s c -t 10 /var/lib/mysql/mysql-slow.log # 按次数排序，取前10条 # 5. 查看当前事务和锁 mysql> SELECT * FROM information_schema.INNODB_TRX; # 正在执行的事务 mysql> SELECT * FROM information_schema.INNODB_LOCKS; # 当前的锁 # 6. 查看InnoDB状态（死锁信息） mysql> SHOW ENGINE INNODB STATUS; # 7. 查看索引使用情况 mysql> SELECT * FROM sys.schema_unused_indexes; # 哪些索引从来没用过 mysql> SELECT * FROM sys.schema_redundant_indexes; # 哪些索引是重复的

全文总结 — 10种SQL问题速查表

场景	症状	EXPLAIN特征	修复方案
1. 字段用函数	DATE(col) = 'xxx'	type=ALL, key=NULL	改用范围查询
2. 隐式类型转换	VARCHAR字段传数字	type=ALL, key=NULL	加引号，类型匹配
3. 左模糊LIKE	LIKE '%xxx%'	type=ALL, key=NULL	右模糊/全文索引/ES
4. ORDER BY无索引	排序慢	Using filesort	给排序字段加索引
5. 深翻页	越翻越慢	rows=OFFSET+N	游标翻页/子查询
6. OR索引失效	两个条件用OR	key=NULL（有索引不走）	UNION ALL拆分
7. GROUP BY临时表	分组统计慢	Using temporary + filesort	复合索引 + 避免函数
8. 子查询优化	多层嵌套	DEPENDENT SUBQUERY	改成JOIN
9. 批量INSERT	循环插入慢	—	批量INSERT/BATCH
10. 线上排查	CPU飙高	SHOW PROCESSLIST + 慢日志	定位 → 加索引 → 验证

核心方法论：写完SQL一定要跑EXPLAIN！看三个关键指标：type（不要是ALL）、key（不要是NULL）、Extra（不要有Using filesort / Using temporary）。这三项没问题，SQL基本就不会有大问题。

EXPLAIN type 字段速查 — 从好到差排序

type值	含义	性能	示例场景
system	表中只有一行数据	极快	const的特例
const	通过主键/唯一索引匹配一行	极快	WHERE id = 1
eq_ref	JOIN时被驱动表通过主键/唯一索引匹配	很快	JOIN ... ON t1.id = t2.id
ref	通过普通索引匹配多行	快	WHERE user_id = 10086
range	索引范围扫描（BETWEEN, >, <, IN）	较快	WHERE create_time > '2025-01-01'
index	全索引扫描（比ALL好一点）	一般	ORDER BY indexed_col LIMIT N
ALL	全表扫描	慢！	WHERE 条件无法使用索引

记忆口诀：system > const > eq_ref > ref > range > index > ALL，目标是至少到 ref 或 range。日常开发中最常见的是 ref（普通索引等值匹配）和 range（索引范围扫描），这两个都是可以接受的。

EXPLAIN Extra 字段 — 这些关键字要警惕

Extra值	含义	严重程度
Using filesort	MySQL在内存/磁盘上做了额外排序。ORDER BY没走索引。	⚠️ 高危
Using temporary	MySQL创建了临时表（可能在内存也可能在磁盘）。GROUP BY/DISTINCT没走索引。	⚠️ 高危
Using join buffer	JOIN时被驱动表没有可用索引，MySQL用缓冲区做嵌套循环。需要加索引。	⚠️ 中危
Using index	覆盖索引！查询的列都在索引里，不需要回表。这是最理想的情况。	✅ 很好
Using index condition	索引下推（ICP）。MySQL在索引层面先过滤一部分数据再回表，比Using where好。	✅ 不错
Using where	MySQL在存储引擎返回数据后，在Server层做了额外过滤。本身不是问题，但配合type=ALL就需要关注。	看情况

SQL上线自查清单 — 每次写完SQL对照一遍

✅ 1. 跑EXPLAIN — 确认type不是ALL，key不是NULL

✅ 2. 检查Extra — 没有Using filesort / Using temporary

✅ 3. 没有对字段用函数 — WHERE DATE(col) → 改成范围查询

✅ 4. 参数类型匹配 — VARCHAR字段必须传字符串，不能传数字

✅ 5. LIKE不是左模糊 — 不能用 '%xxx%'，至少保证右边不是%

✅ 6. 深翻页有优化 — 超过1000页的翻页要改用游标或子查询

✅ 7. 大批量操作分批提交 — INSERT/UPDATE/DELETE超过1000条要分批

✅ 8. 测试环境用生产量级数据验证 — 10条数据看不出问题，50万才能暴露

常见误区

误区1："给所有字段都加索引就好了"
错！索引会占用磁盘空间，而且每次INSERT/UPDATE/DELETE都需要更新索引。索引越多，写操作越慢。一般一张表的索引不超过5-6个。

误区2："有索引就一定会走"
错！正如本文10个场景所展示的：函数、类型转换、左模糊、OR、深翻页……很多情况会导致索引"有但走不了"。MySQL优化器会根据成本计算决定走不走索引。

误区3："测试环境没问题，生产也不会有问题"
错！测试环境100条数据，全表扫描0.001秒。生产50万条数据，同样的SQL跑45秒。SQL优化必须在接近生产数据量的环境下测试。

误区4："慢查询一定是SQL写得烂"
不一定。也可能是：数据库连接池配置太小、磁盘IO瓶颈（机械硬盘）、内存不够（innodb_buffer_pool_size太小）、并发量突增。排查时要综合看。

误区5："加了索引就万事大吉"
不一定。MySQL优化器可能"判断失误"——计算出走索引的成本比全表扫描还高（比如表很小，或者筛选条件匹配了大部分行），就选择不走索引。这时可能需要用FORCE INDEX强制指定索引。

实际经验总结：SQL优化不是一次性的工作，而是持续的过程。数据量增长后，之前没问题的SQL可能突然变慢。建议定期（每周/每月）检查慢查询日志，及时发现和优化新出现的慢SQL。