MySQL查询优化是数据库性能调优中非常重要的环节，当业务数据量不断增长时，不合理的查询设计会导致接口响应变慢、数据库负载过高等问题，掌握系统的优化策略能有效提升整体系统的稳定性。
一、索引层面优化策略
索引是提升查询效率最直接的手段，合理的索引设计能让查询从全表扫描变为索引扫描，大幅减少数据扫描范围。
1. 避免全表扫描
对于经常作为查询条件的字段，应当创建合适的索引。如果查询语句没有使用到任何索引，MySQL会进行全表扫描，当表数据量达到百万级以上时，查询耗时会急剧上升。
可以通过以下语句查看查询是否使用了索引：
-- 查看查询执行计划，分析索引使用情况 EXPLAIN SELECT * FROM user WHERE age > 20;
2. 合理使用联合索引
联合索引遵循最左前缀匹配原则，创建联合索引时要把区分度高的字段放在前面。比如经常用age和city两个字段作为查询条件，可以创建联合索引(age, city)，而不是分别创建两个单列索引。
-- 创建联合索引示例 CREATE INDEX idx_age_city ON user(age, city); -- 能命中索引的查询 SELECT * FROM user WHERE age = 25 AND city = '北京'; -- 无法命中索引的查询，不满足最左前缀 SELECT * FROM user WHERE city = '北京';
3. 避免索引失效场景
以下情况会导致索引失效，需要尽量避免：
- 对索引字段进行函数运算或者表达式计算，比如
WHERE YEAR(create_time) = 2024 - 查询条件中使用
OR连接，且OR两侧的字段不是都有索引 - 使用
LIKE模糊查询时，通配符%放在最前面，比如LIKE '%张三' - 索引字段类型与查询值类型不匹配，比如索引字段是整型，查询时传入字符串类型的值
二、SQL语句编写优化策略
不合理的SQL语句写法即使有索引也可能无法发挥效果，优化SQL编写逻辑能从执行层面减少不必要的资源消耗。
1. 只查询需要的字段
避免使用SELECT *，只查询业务需要的字段，减少数据传输量和MySQL的解析负担。如果只需要查询用户id和姓名，就不要查询所有字段。
-- 不推荐写法 SELECT * FROM user WHERE age > 20; -- 推荐写法 SELECT id, name FROM user WHERE age > 20;
2. 优化分页查询
大分页查询时，使用LIMIT offset, size的写法会随着offset增大性能急剧下降，因为MySQL需要先扫描到offset位置再取数据。可以改用基于主键的游标分页方式。
-- 传统分页写法，offset越大性能越差 SELECT id, name FROM user ORDER BY id LIMIT 100000, 10; -- 优化后的分页写法，基于上一页最后一条记录的主键值查询 SELECT id, name FROM user WHERE id > 100000 ORDER BY id LIMIT 10;
3. 避免使用子查询
尽量把子查询改为关联查询，子查询会产生临时表，增加数据库的负担，关联查询的效率通常更高。
-- 不推荐的子查询写法 SELECT * FROM user WHERE id IN (SELECT user_id FROM order WHERE total > 100); -- 推荐改为关联查询 SELECT u.* FROM user u JOIN order o ON u.id = o.user_id WHERE o.total > 100;
三、数据库配置与架构层面优化
除了索引和SQL层面的优化，调整数据库配置和架构设计也能从整体上提升查询性能。
1. 调整InnoDB缓冲池大小
InnoDB的缓冲池用于缓存索引和数据，默认大小通常较小，可以根据服务器内存调整innodb_buffer_pool_size参数，一般设置为服务器可用内存的60%-80%，让更多热数据缓存在内存中，减少磁盘IO。
2. 读写分离与分库分表
当单表数据量超过千万级，或者读请求远高于写请求时，可以考虑读写分离，把查询请求分发到从库，减轻主库压力。如果单表数据量过大，可以进行分库分表，把数据拆分到多个表或者多个数据库中，降低单表的数据量。
3. 定期维护索引和表
随着数据的增删改，索引会产生碎片，定期执行ANALYZE TABLE更新索引统计信息，执行OPTIMIZE TABLE整理表碎片，能让索引保持更好的查询效率。
-- 更新表统计信息 ANALYZE TABLE user; -- 整理表碎片，注意该操作会锁表，需要在业务低峰期执行 OPTIMIZE TABLE user;