mysql作为常用的关系型数据库，SQL语句的性能直接影响业务系统的响应速度，不合理的语句可能导致全表扫描、资源浪费等问题。在实际开发中，掌握SQL优化的核心技巧能够显著提升数据库运行效率。
索引优化技巧
索引是提升查询效率的核心手段，合理创建和使用索引能避免全表扫描。
选择合适的索引列
优先在where条件、join关联字段、order by和group by涉及的列上创建索引，避免对频繁更新的列创建过多索引，因为索引维护会消耗额外资源。
避免索引失效场景
很多写法会导致索引无法生效，常见情况包括：对索引列进行函数运算、使用like以通配符开头、索引列参与算术运算、查询条件中使用or且部分字段无索引等。
以下是一个索引失效的示例：
-- 错误写法，对索引列name进行函数运算，索引失效 SELECT * FROM user WHERE LEFT(name, 2) = '张'; -- 正确写法，避免函数运算 SELECT * FROM user WHERE name LIKE '张%';
查询语句编写规范
编写规范的SQL语句能从根源上减少性能损耗。
避免查询不必要的数据
不要使用SELECT * 查询所有字段，只查询业务需要的字段，减少数据传输量和数据库IO消耗。如果只需要判断数据是否存在，使用SELECT 1代替查询全字段。
-- 错误写法，查询所有字段 SELECT * FROM order WHERE user_id = 1001; -- 正确写法，只查询需要的字段 SELECT order_id, order_amount FROM order WHERE user_id = 1001; -- 判断数据是否存在的正确写法 SELECT 1 FROM user WHERE id = 1001 LIMIT 1;
优化join查询
join查询时，小表驱动大表，即把数据量少的表放在join的左边，减少循环匹配次数。同时保证关联字段上有索引，避免笛卡尔积。
减少子查询使用
子查询会创建临时表，消耗额外资源，尽量使用join代替子查询，尤其是嵌套子查询。
-- 不推荐的子查询写法 SELECT * FROM user WHERE id IN (SELECT user_id FROM order WHERE amount > 100); -- 推荐的join写法 SELECT u.* FROM user u JOIN order o ON u.id = o.user_id WHERE o.amount > 100;
慢查询分析与处理
mysql提供了慢查询日志功能，能够记录执行时间超过阈值的SQL语句，方便开发者定位性能问题。
开启慢查询日志
可以通过配置文件或者命令行开启慢查询，设置慢查询的时间阈值，比如执行超过1秒的语句就记录到日志中。
-- 查看慢查询是否开启 SHOW VARIABLES LIKE 'slow_query_log'; -- 开启慢查询 SET GLOBAL slow_query_log = 'ON'; -- 设置慢查询时间阈值为1秒 SET GLOBAL long_query_time = 1; -- 查看慢查询日志存储路径 SHOW VARIABLES LIKE 'slow_query_log_file';
分析慢查询语句
使用EXPLAIN关键字可以分析SQL语句的执行计划，查看是否使用索引、扫描行数、查询类型等信息，根据分析结果调整索引或者语句结构。
-- 分析查询语句的执行计划 EXPLAIN SELECT order_id FROM order WHERE user_id = 1001 AND amount > 50;
执行EXPLAIN后，重点关注type字段，尽量让查询类型达到ref或者range级别，避免all全表扫描；同时关注rows字段，扫描行数越少越好。
其他实用优化技巧
- 批量操作代替循环单条操作，比如批量插入数据，减少数据库连接和提交次数
- 合理使用limit分页，大偏移量的分页可以先通过索引定位到起始位置再查询，避免扫描大量无用数据
- 避免在where条件中对字段进行null值判断，尽量给字段设置默认值，否则可能导致索引失效
- 定期对大表进行碎片整理，尤其是频繁更新的表，提升存储和查询效率
SQL优化是一个持续的过程，需要结合业务场景和实际执行情况进行动态调整，定期分析慢查询日志，不断优化语句和索引结构，才能保障数据库长期稳定高效运行。