mysql作为常用的关系型数据库，查询操作是日常开发中使用频率最高的功能之一，掌握高效的查询技巧能够大幅提升系统的响应速度，减少数据库的资源消耗。
一、合理使用索引提升查询速度
索引是优化查询最有效的手段之一，正确使用索引可以避免全表扫描，大幅减少查询耗时。
1. 遵循最左前缀原则
对于联合索引，查询条件需要从索引的最左列开始匹配，否则索引无法生效。例如创建联合索引idx_user_age_name包含age和name两个字段，只有查询条件包含age时索引才会被使用。
-- 创建联合索引 CREATE INDEX idx_user_age_name ON user_table(age, name); -- 索引生效的查询 SELECT * FROM user_table WHERE age = 20; -- 索引不生效的查询，未使用最左列age SELECT * FROM user_table WHERE name = '张三';
2. 避免在索引列上做函数运算
如果在索引列上使用函数或者进行运算，会导致索引失效，触发全表扫描。
-- 错误写法，索引失效 SELECT * FROM user_table WHERE YEAR(create_time) = 2024; -- 正确写法，索引生效 SELECT * FROM user_table WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01';
二、优化查询条件写法
查询条件的写法直接影响查询效率，不合理的条件写法会导致索引失效或者增加不必要的计算。
1. 避免使用SELECT * 查询所有字段
只查询需要的字段可以减少数据传输量，同时如果查询的字段都包含在索引中，还可以触发覆盖索引，避免回表操作。
-- 不推荐写法 SELECT * FROM user_table WHERE age = 20; -- 推荐写法，只查询需要的字段 SELECT id, name, age FROM user_table WHERE age = 20;
2. 用EXISTS代替IN处理大结果集
当子查询结果集较大时，使用IN会导致查询效率下降，此时用EXISTS可以提升性能。
-- 不推荐写法，子查询结果集大时效率低 SELECT * FROM user_table WHERE id IN (SELECT user_id FROM order_table WHERE amount > 100); -- 推荐写法 SELECT * FROM user_table u WHERE EXISTS (SELECT 1 FROM order_table o WHERE o.user_id = u.id AND o.amount > 100);
三、分页查询优化技巧
深度分页是查询中常见的性能瓶颈，当LIMIT偏移量很大时，查询会扫描大量无用数据。
可以通过子查询先获取主键id，再关联查询完整数据来优化深度分页。
-- 普通深度分页，偏移量10000时效率低 SELECT * FROM user_table ORDER BY id LIMIT 10000, 10; -- 优化后的分页查询 SELECT * FROM user_table u JOIN (SELECT id FROM user_table ORDER BY id LIMIT 10000, 10) tmp ON u.id = tmp.id;
四、关联查询优化技巧
进行多表关联查询时，需要注意驱动表的选择和关联条件的索引使用。
小表驱动大表是关联查询的基本原则，同时关联字段需要建立索引，避免嵌套循环时的大表全扫描。
-- 假设user_table是大表，order_table是小表，关联字段user_id建立索引 SELECT u.name, o.order_no FROM order_table o LEFT JOIN user_table u ON o.user_id = u.id WHERE o.status = 1;
五、慢查询分析技巧
当查询出现性能问题时，可以通过开启慢查询日志定位问题语句，再使用EXPLAIN分析语句的执行计划，查看索引使用情况、扫描行数等信息，针对性进行优化。
-- 分析查询语句的执行计划 EXPLAIN SELECT * FROM user_table WHERE age = 20 AND name = '张三';
执行计划中的type字段表示访问类型，从好到坏依次为system、const、eq_ref、ref、range、index、ALL，尽量让查询的type达到ref及以上级别，避免ALL全表扫描。