SQL调优
SQL调优是后端开发的核心技能。数据库数据存储在磁盘中，每次查询都需要IO操作，编写高效的SQL减少IO次数是关键。底层B+树也是为提高查询效率而设计的。本文档从索引、SQL编写、架构设计等维度系统梳理SQL优化技巧。
一、索引优化
1.1 索引的本质
索引就类似于书本的目录，建立索引可以实现快速查找，避免全表扫描。索引是SQL优化最基础、最重要的手段。
1.2 创建索引的规则
- 索引数量控制：单表索引不超过6个。过多的索引会降低INSERT和UPDATE操作，因为增删改操作可能需要重建索引，B+树需要进行裂变
- 高区分度优先：如性别(sex)这种区分度极低的字段，建立索引几乎无效（只有男女两个值），不值得建索引
- 联合索引：多个条件经常一起查询时，建立联合索引提高效率。遵循最左前缀原则
- 使用EXPLAIN分析：通过EXPLAIN查看SQL语句的执行计划，判断是否走索引、扫描行数、使用的索引等
1.3 EXPLAIN 执行计划解读
|
关键字段 |
含义 |
|---|---|
|
type |
访问类型，从好到差：const(主键/唯一索引常量匹配) → eq_ref(唯一join) → ref(非唯一索引) → range(范围扫描) → index(全索引扫描) → ALL(全表扫描，最差需避免) |
|
key |
实际使用的索引名（NULL表示未使用索引） |
|
rows |
MySQL估算需要扫描的行数（越小越好） |
|
Extra |
Using index(覆盖索引，好) / Using where / Using filesort(需排序优化) / Using temporary(需临时表优化) |
|
key_len |
使用的索引长度，可判断联合索引用了几个字段 |
二、避免索引失效
索引失效是SQL性能问题的头号原因。以下6种常见场景会导致索引失效。
2.1 左模糊查询
- 失效原因：SELECT * FROM table WHERE name LIKE '%周'——%放在最前面，B+树无法利用索引有序性
- 解决：大量模糊搜索需求推荐使用ElasticSearch，基于倒排索引，专为全文搜索设计
- 注意：LIKE '周%'可以使用索引（右模糊），因为索引有序
2.2 使用OR连接
- 失效原因：OR连接后面的字段如果没有建立索引，整个查询可能不走索引
- 解决：OR连接的所有字段都要有索引；或用UNION ALL替代OR查询
2.3 索引列存在计算/函数操作
- 失效原因：对索引列做函数计算或表达式计算，如 WHERE DATE(create_time) = '2025-01-01'
- 解决：将计算移到等号右边：WHERE create_time >= '2025-01-01' AND create_time < '2025-01-02'
- 同理：WHERE age + 10 = 30 不会走索引，改为 WHERE age = 20
2.4 数据类型不匹配（隐式类型转换）
当查询条件中的值类型与索引列的数据类型不匹配时，数据库会自动进行隐式类型转换。索引是基于原始数据类型构建的，类型转换意味着数据库必须对所有记录逐一扫描完成转换，无法直接利用索引。
- 典型场景：列是INT类型，查询写成 WHERE id = '123'（字符串）→ MySQL会自动将字符串转数字，不会失效。但反过来，列是VARCHAR，WHERE phone = 13800138000（数字）→ 字符串索引会失效
- 解决：传参时保持类型一致，后端代码中传String就用引号，传Integer就不加引号
2.5 不等于/范围查询
- 使用 != 或 <> 可能导致索引失效；范围查询(>, <, BETWEEN)在数据量大时索引效率降低
- 联合索引中，范围查询后的字段索引失效（如联合索引(a,b,c)，WHERE a=1 AND b>10 AND c=5，c不走索引）
三、SQL编写优化
3.1 避免SELECT *
SELECT * 不会走覆盖索引，存在大量回表操作，导致SQL性能降低。需要哪些列就查哪些列。
覆盖索引 (Covering Index)
- 定义：查询语句中需要的所有列都能从索引中获取，不需要回表（访问实际数据表）
- 优势：只读索引即可返回结果，减少一次IO回表操作
- 示例：联合索引(user_id, user_name, age)，SELECT user_id, user_name FROM user WHERE user_id = 1 → Using index，直接从索引获取，不需要回表
回表详解
- 二级索引叶子节点存储的是索引列 + 主键值。若查询列不全在索引中，需用主键去聚簇索引中查完整行记录——这就是回表
- 索引覆盖可以完全避免回表，但索引列越长则索引文件越大
3.2 小表驱动大表
MySQL表关联使用Nest Loop Join算法，以驱动表的结果集作为循环基础数据，一条一条地去关联表中查询匹配数据。
- 原则：用小表（结果集小的表）作为驱动表，减少外层循环次数
- 例如：140条记录的小表作为驱动表去关联20万条记录的大表，外层循环140次；反过来20万次外层循环性能差很多
- 实践：LEFT JOIN时，左表是驱动表；INNER JOIN时，MySQL优化器通常自动选小表做驱动表
3.3 使用LIMIT优化
- 单条查询：查询某条件是否存在时，用 LIMIT 1 查到第一条即返回，不用扫描全部满足条件的数据
- 分批处理：大批量操作时用LIMIT分批次执行，避免长事务和锁表
3.4 连接查询代替子查询
- 原因：子查询会产生临时表，查询结束后需删除。如果临时表数据量大，性能和内存消耗严重
- 推荐：能用JOIN解决的尽量用JOIN，MySQL对JOIN的优化更成熟
3.5 UNION ALL 代替 UNION
|
对比 |
说明 |
|---|---|
|
UNION |
合并两个SELECT结果并自动去重（需额外排序和去重计算），性能较低 |
|
UNION ALL |
合并所有结果保留重复行，不需要去重计算，性能更高 |
- 推荐：如果业务逻辑上不需要去重（或数据本身不重复），优先使用UNION ALL
3.6 优化 GROUP BY
- HAVING vs WHERE：WHERE在分组前过滤数据（减少分组数据量），HAVING在分组后过滤。能用WHERE的先WHERE，缩小数据量再GROUP BY
- 示例：GROUP BY user_id HAVING user_id <= 200 → 改为 WHERE user_id <= 200 GROUP BY user_id（先缩小范围再分组，效率更高）
四、深度分页优化
4.1 问题分析
LIMIT 10000000, 100 会扫描前面全部1000万条数据，然后丢弃，只取最后100条。越往后翻页越慢，这就是深度分页问题。
4.2 游标分页（推荐）
- 原理：WHERE id > 上次最后一条的id ORDER BY id LIMIT 10（利用主键索引快速定位）
- 前提：主键自增或有序，适合无限滚动场景。缺点：不支持跳页
4.3 子查询优化
核心思路：把条件转移到主键索引树，减少回表操作。先在二级索引上拿到主键ID，省去大量回表，再根据主键ID查数据。
4.4 延迟关联优化
核心思路与子查询优化相同：把条件转移到主键索引树，减少回表。区别是使用INNER JOIN代替子查询。
五、批量操作优化
5.1 批量INSERT
- 问题：每条数据都执行一次INSERT，每次都需要建立网络连接、事务开启/提交、索引更新
- 方案：批量INSERT，一次插入多条记录。建议每次500条左右执行一次批量插入
- 注意：不能过度批量（如一次插入几万条），会导致长事务、锁等待、undo log膨胀
六、SQL调优总结清单
|
# |
优化手段 |
关键点 |
|---|---|---|
|
1 |
建合理索引 |
高区分度字段、联合索引最左前缀、不超过6个 |
|
2 |
避免索引失效 |
禁止左模糊、OR全索引、列上计算、隐式转换 |
|
3 |
避免SELECT * |
只查需要的列，利用覆盖索引减少回表 |
|
4 |
小表驱动大表 |
JOIN时小结果集做驱动表 |
|
5 |
连接代替子查询 |
避免大量临时表创建 |
|
6 |
UNION ALL代替UNION |
不需要去重时避免额外排序开销 |
|
7 |
LIMIT优化 |
配合索引，单条查询用LIMIT 1 |
|
8 |
优化GROUP BY |
WHERE前置过滤缩小分组范围 |
|
9 |
深度分页优化 |
游标分页 / 子查询 / 延迟关联，条件转移到主键索引 |
|
10 |
批量操作 |
批量INSERT减少网络IO和事务开销 |
|
11 |
EXPLAIN分析 |
关注type/rows/Extra，避免ALL全表扫描 |
核心原则：SQL调优的底层逻辑是减少磁盘IO——索引让查询快速定位数据页，覆盖索引减少回表IO，合理的SQL编写避免不必要的扫描和临时表创建。一切优化围绕"少读数据、快读数据"展开。