SQL语句从初期能正常返回结果，到可以支撑业务长期稳定运行，需要经过多层面的优化和调整，核心目标是平衡查询效率、资源占用和系统稳定性，避免随着数据量和并发量增长出现性能劣化问题。
第一步：优化SQL基础逻辑，减少无效计算
很多SQL能跑但效率低，根源是基础逻辑存在冗余。首先要避免SELECT *操作，只查询业务需要的字段，减少数据传输和内存占用。其次要规避在WHERE条件中对字段使用函数或运算，这会导致索引失效。比如要避免WHERE YEAR(create_time) = 2024这种写法，改为范围查询更合适。
还存在子查询嵌套过深的问题，多层嵌套子查询会增加优化器的解析成本，尽量用JOIN替代多层子查询。下面是优化前后的对比示例：
-- 优化前：嵌套子查询
SELECT u.id, u.name
FROM user u
WHERE u.id IN (
SELECT o.user_id
FROM order o
WHERE o.status = 1
AND o.amount > 100
);
-- 优化后：JOIN替代子查询
SELECT DISTINCT u.id, u.name
FROM user u
JOIN order o ON u.id = o.user_id
WHERE o.status = 1
AND o.amount > 100;
第二步：合理设计索引，避免索引失效
索引是提升SQL性能的核心手段，但索引设计不合理反而会拖慢系统。首先要遵循最左前缀原则，联合索引的字段顺序要和查询条件的顺序匹配。其次要避免索引冗余，重复功能的索引会占用额外存储空间，还会降低写入性能。
还要注意索引失效的常见场景：字段类型不匹配、使用LIKE '%xxx'左模糊查询、查询条件使用OR且部分字段没有索引、范围查询后的字段无法使用联合索引后续列。可以通过EXPLAIN命令查看索引使用情况，下面是分析示例：
-- 分析查询语句的执行计划 EXPLAIN SELECT id, name, create_time FROM user WHERE age > 18 AND status = 1 ORDER BY create_time LIMIT 10;
第三步：分析执行计划，定位性能瓶颈
执行计划是SQL优化的核心依据，通过EXPLAIN输出的结果，可以查看表的访问方式、索引使用情况、扫描行数、排序方式等信息。重点关注type字段，最好达到ref或range级别，避免出现ALL全表扫描。还要关注rows字段，扫描行数越少性能越好。
如果执行计划显示使用了临时表或者文件排序，说明查询存在性能问题，需要调整索引或者查询逻辑。比如排序字段没有索引时，会出现Using filesort，给排序字段添加索引即可优化。
第四步：规范事务使用，避免长事务和锁冲突
长事务是SQL长期运行的重大隐患，会长时间占用数据库连接、持有锁资源，导致其他请求阻塞。首先要控制事务的范围，尽量把事务内的逻辑精简，避免在大事务内做无关的业务操作。其次要设置合理的事务超时时间，避免事务无限期挂起。
还要注意锁的使用，避免间隙锁范围过大，尽量使用主键或者唯一索引作为更新条件，减少锁冲突的概率。下面是规范事务的示例：
// 错误示例：事务范围过大
@Transactional
public void updateUserAndOrder() {
// 查询用户
User user = userMapper.selectById(1);
// 无关的业务逻辑，耗时较长
doSomeOtherThing();
// 更新订单
orderMapper.updateStatus(1001, 2);
}
// 正确示例：缩小事务范围
public void updateUserAndOrder() {
// 无关逻辑放在事务外
doSomeOtherThing();
// 只把数据库操作放在事务内
transactionTemplate.execute(status -> {
orderMapper.updateStatus(1001, 2);
return null;
});
}
第五步：治理慢查询，建立长期监控机制
SQL优化不是一次性的工作，需要建立长期的慢查询监控机制。首先要开启数据库的慢查询日志，设置合理的慢查询阈值，比如超过1秒的查询记录到日志中。其次要定期分析慢查询日志，对高频出现的慢查询进行针对性优化。
还可以建立SQL审核机制，上线前对新增的SQL语句进行执行计划分析，避免问题SQL流入生产环境。对于数据量大的表，要定期做数据归档，删除或者迁移历史冷数据，减少单表数据量，从根源上降低SQL的查询压力。
长期运行的额外注意事项
除了上述优化步骤，还要注意数据库的参数配置，比如连接池大小、缓存大小、日志刷新策略等，根据业务场景调整参数。还要避免SQL注入风险，使用参数化查询而不是字符串拼接SQL，既提升安全性也避免因为注入导致SQL逻辑异常。
定期对核心SQL做压力测试，模拟高并发场景下的运行情况，提前发现潜在的性能问题。当单表数据量超过千万级时，要考虑分库分表或者读写分离方案，从架构层面支撑SQL的长期稳定运行。