MySQL作为最常用的关系型数据库之一，查询性能的优劣直接影响上层业务的运行效率。当单表数据量达到百万甚至千万级别时，没有经过优化的查询可能会出现全表扫描，导致响应时间从毫秒级飙升到秒级甚至更长。优化查询性能的核心方向主要围绕索引设计和执行计划分析展开，二者结合能从根源上解决大部分查询慢的问题。
一、索引优化核心技巧
1.1 索引设计的基本原则
索引的本质是排好序的快速查找数据结构，合理设计索引需要遵循以下原则：
- 优先为经常作为查询条件、排序条件、分组条件的字段创建索引
- 尽量使用覆盖索引，避免回表查询，减少IO消耗
- 控制索引数量，单表索引建议不超过5个，避免写入时索引维护成本过高
- 选择区分度高的字段作为索引，区分度计算公式：count(distinct 列名)/count(*)，值越接近1区分度越高
- 避免对频繁更新的字段创建索引，更新字段时需要同步更新索引，增加开销
1.2 联合索引的最左前缀原则
联合索引是指多个字段组合的索引，使用时需要遵循最左前缀原则，即查询条件必须包含联合索引最左边的字段，索引才会生效。例如创建联合索引(a,b,c)，以下查询可以使用索引：
-- 可以使用索引的查询 SELECT * FROM table WHERE a = 1; SELECT * FROM table WHERE a = 1 AND b = 2; SELECT * FROM table WHERE a = 1 AND b = 2 AND c = 3; SELECT * FROM table WHERE a = 1 AND c = 3; -- 仅使用a字段的索引部分 -- 无法使用索引的查询 SELECT * FROM table WHERE b = 2; SELECT * FROM table WHERE b = 2 AND c = 3;
1.3 索引失效的常见场景
即使创建了索引，以下场景也可能导致索引失效，查询走全表扫描：
- 查询条件中对索引字段使用函数或表达式计算，例如
WHERE YEAR(create_time) = 2023 - 使用
LIKE以通配符开头，例如WHERE name LIKE '%张三' - 查询条件中使用
OR，且OR两边的字段不全有索引 - 字符串类型的索引字段查询时未加单引号，例如
WHERE phone = 13800138000（phone为varchar类型） - 查询优化器判断全表扫描比走索引更快，例如查询返回表中大部分数据
二、执行计划解读与优化
2.1 如何查看执行计划
在查询语句前加上EXPLAIN关键字即可查看该查询的执行计划，语法如下：
EXPLAIN SELECT * FROM user WHERE age > 18 AND city = '北京';
执行后会返回多个字段，核心字段的含义如下：
| 字段名 | 含义 |
|---|---|
| id | 查询的序列号，id越大越先执行，id相同则从上到下执行 |
| select_type | 查询类型，常见值有SIMPLE（简单查询）、PRIMARY（主查询）、SUBQUERY（子查询）等 |
| table | 当前查询涉及的表名 |
| type | 访问类型，性能从好到坏依次为：system > const > eq_ref > ref > range > index > ALL |
| possible_keys | 查询可能使用的索引 |
| key | 查询实际使用的索引，为NULL表示未使用索引 |
| rows | 预估需要扫描的行数，值越小越好 |
| Extra | 额外信息，常见值有Using index（覆盖索引）、Using where（需要回表过滤）、Using filesort（需要额外排序）、Using temporary（使用临时表） |
2.2 基于执行计划的优化案例
假设存在用户表user，结构如下：
CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) DEFAULT NULL, `age` int(11) DEFAULT NULL, `city` varchar(50) DEFAULT NULL, `create_time` datetime DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_age_city` (`age`,`city`) -- 联合索引 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
执行以下查询并查看执行计划：
EXPLAIN SELECT * FROM user WHERE city = '上海' AND age > 20;
此时执行计划中key为NULL，type为ALL，说明没有使用索引，原因是查询条件没有遵循联合索引的最左前缀原则，把age放在前面即可使用索引：
EXPLAIN SELECT * FROM user WHERE age > 20 AND city = '上海';
优化后执行计划中key为idx_age_city，type为range，扫描行数大幅减少，查询性能得到提升。
2.3 优化Using filesort和Using temporary
Using filesort表示查询需要的排序无法利用索引完成，需要额外进行排序操作，Using temporary表示查询需要创建临时表来存储中间结果，二者都会严重影响查询性能。
优化方案：如果查询有排序需求，尽量让排序字段和查询条件的索引字段匹配，例如联合索引(a,b)，查询WHERE a = 1 ORDER BY b可以利用索引排序，不会出现Using filesort。如果查询需要分组，尽量让分组字段使用索引，避免产生临时表。
三、综合优化实践
实际业务中优化查询性能通常需要结合索引和执行计划共同分析，步骤如下：
- 开启慢查询日志，记录执行时间超过阈值的SQL语句
- 对慢查询语句使用
EXPLAIN查看执行计划，定位问题点 - 检查是否有合适的索引，没有则根据查询条件创建合理的索引
- 检查索引是否失效，调整查询条件写法避免索引失效
- 优化查询语句本身，避免查询不必要的字段，减少返回数据量
- 再次查看执行计划，确认优化效果，直到查询性能满足要求
通过以上步骤，大部分查询性能问题都可以得到有效解决，同时要注意定期分析业务查询的变化，及时调整索引策略，避免索引冗余或者索引缺失的情况。