在业务系统运行过程中，SQL查询性能直接影响整体接口的响应速度，很多慢接口的根源都是未经优化的数据库查询语句。合理的SQL优化能够在不需要升级硬件的情况下，让查询效率提升数倍甚至数十倍。
一、索引优化是SQL性能提升的基础
索引是提升查询效率最有效的手段之一，但错误的索引设计反而会导致写入性能下降，需要遵循以下核心原则：
- 优先给where条件、join关联字段、order by排序字段创建索引，避免给频繁更新的字段创建过多索引
- 联合索引要遵循最左前缀匹配原则，把区分度高的字段放在联合索引的最左侧
- 避免在索引字段上使用函数、类型转换或者模糊查询的左模糊，这会导致索引失效
- 定期检查冗余索引和未使用的索引，及时清理无用索引减少维护成本
以下是创建联合索引和使用索引的正确示例：
-- 创建用户表的联合索引，优先匹配user_status，再匹配create_time CREATE INDEX idx_user_status_time ON user_table(user_status, create_time); -- 正确使用联合索引的查询语句，会命中上面的索引 SELECT id, user_name FROM user_table WHERE user_status = 1 ORDER BY create_time DESC LIMIT 10;
二、查询语句编写的优化技巧
很多慢查询问题不是缺少索引，而是查询语句本身的编写方式不合理，常见的优化方向包括：
- 避免使用select *，只查询需要的字段，减少数据传输量和回表次数
- 尽量用join代替子查询，尤其是多层嵌套的子查询，优化器很难生成高效的执行计划
- 合理使用分页查询，大偏移量的分页可以先通过索引定位起始ID，再查询后续数据
- 避免在where条件中使用!=、<>、is null、is not null等可能导致全表扫描的操作
以下是大分页优化和避免子查询的示例：
-- 普通大分页查询，偏移量10000时性能很差 SELECT id, user_name FROM user_table ORDER BY id LIMIT 10000, 10; -- 优化后的大分页查询，先通过索引找到起始ID，再查询数据 SELECT id, user_name FROM user_table WHERE id >= (SELECT id FROM user_table ORDER BY id LIMIT 10000, 1) ORDER BY id LIMIT 10; -- 用join代替子查询的示例 -- 低效的子查询写法 SELECT * FROM order_table WHERE user_id IN (SELECT id FROM user_table WHERE user_status = 1); -- 优化后的join写法 SELECT o.* FROM order_table o JOIN user_table u ON o.user_id = u.id WHERE u.user_status = 1;
三、执行计划分析与慢查询排查
当遇到慢查询时，不能靠猜测优化，需要通过执行计划明确查询的执行路径：
- 使用
EXPLAIN命令查看SQL的执行计划，重点关注type（访问类型）、key（使用的索引）、rows（扫描行数）、Extra（额外信息）字段 - 访问类型type最好达到range及以上，避免ALL全表扫描，ref和eq_ref是较优的索引访问类型
- 开启数据库的慢查询日志，定期收集执行时间超过阈值的SQL语句，针对性进行优化
- 对于复杂查询，可以拆分查询逻辑，把大查询拆成多个小查询，减少单次查询的资源消耗
以下是使用EXPLAIN分析查询的示例：
-- 查看查询的执行计划 EXPLAIN SELECT id, user_name FROM user_table WHERE user_status = 1 ORDER BY create_time DESC LIMIT 10;
执行结果中如果type显示为ALL，说明没有命中索引，需要检查索引是否存在或者查询条件是否合理；如果rows数值很大，说明扫描了很多行数据，也需要进一步优化索引或者查询条件。
四、其他常见优化细节
除了上述核心方法，还有一些容易被忽略的优化点：
- 控制事务的长度，尽量缩短事务持有锁的时间，避免长事务导致的锁等待和阻塞
- 对于频繁查询的热点数据，可以考虑使用缓存层减少数据库的查询压力
- 定期分析表的统计信息，保证数据库优化器能够生成合理的执行计划
- 批量操作尽量合并成单条语句，减少和数据库的交互次数，比如批量插入使用values多值语法
以下是批量插入的优化示例：
-- 低效的多次单条插入
INSERT INTO user_table(user_name, age) VALUES('张三', 20);
INSERT INTO user_table(user_name, age) VALUES('李四', 22);
INSERT INTO user_table(user_name, age) VALUES('王五', 25);
-- 优化后的单条批量插入语句
INSERT INTO user_table(user_name, age) VALUES('张三', 20),('李四', 22),('王五', 25);