PHP 慢查询优化：执行计划分析与索引失效原因

嘿，各位！今天咱们来聊聊PHP慢查询优化这事儿，保证让你听完之后，以后看到慢查询就心里有数，不再抓瞎。别怕，我尽量说得接地气儿，让大家都能听明白。

慢查询：你的代码在“摸鱼”吗？

首先，啥叫慢查询？简单来说，就是你的PHP代码执行数据库查询的时候，像个老牛拉破车，慢得让人想摔键盘。这可不行啊，用户体验是王道，谁愿意对着loading图标发呆？

慢查询的常见症状：

• 网页加载慢，转圈圈半天出不来。
• 数据库CPU占用率居高不下，服务器压力山大。
• 半夜收到报警短信，提示数据库连接数超限。

第一步：找到“摸鱼”的罪魁祸首！

要优化慢查询，首先得找到是谁在“摸鱼”。这就需要开启MySQL的慢查询日志。

如何开启MySQL慢查询日志？

1. 修改MySQL配置文件（my.cnf 或 my.ini）：

   找到 [mysqld] 部分，添加或修改以下配置：

   slow_query_log = 1  # 开启慢查询日志
   slow_query_log_file = /var/log/mysql/mysql-slow.log  # 日志文件路径 (改成你自己的路径)
   long_query_time = 2  # 慢查询时间阈值，单位秒 (可以根据需要调整)
   log_queries_not_using_indexes = 1 # 记录没有使用索引的查询

2. 重启MySQL服务：

   修改配置后，必须重启MySQL服务才能生效。

3. 通过SQL命令动态开启 (临时生效):

   SET GLOBAL slow_query_log = 'ON';
   SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';
   SET GLOBAL long_query_time = 2;
   SET GLOBAL log_queries_not_using_indexes = 1;

   注意： 动态修改的配置在MySQL服务重启后会失效，所以建议还是修改配置文件。

慢查询日志分析工具：

有了慢查询日志，就可以用工具来分析了。常见的工具有：

• mysqldumpslow： MySQL自带的工具，可以统计查询次数、平均执行时间等。

  mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log  # 按时间排序，显示前10条

• pt-query-digest： Percona Toolkit工具包中的工具，功能更强大，可以生成更详细的报告。

  pt-query-digest /var/log/mysql/mysql-slow.log > report.txt

第二步：解读“执行计划”：你的SQL是“高速公路”还是“乡间小路”？

找到慢查询之后，下一步就是分析SQL语句的执行计划，看看MySQL是如何执行这条SQL的。执行计划可以告诉你SQL语句是否使用了索引，扫描了多少行数据，以及各种JOIN的类型等信息。

如何查看执行计划？

使用 EXPLAIN 关键字：

EXPLAIN SELECT * FROM users WHERE name = '张三' AND age > 20;

EXPLAIN 命令会返回一个表格，包含以下重要字段：

字段	含义
id	查询的序列号，表示查询中执行select子句或操作表的顺序。
select_type	查询类型，SIMPLE (简单查询), PRIMARY (最外层查询), SUBQUERY (子查询), DERIVED (派生表) 等。
table	正在访问的表名。
partitions	如果表进行了分区，会显示分区信息。
type	重要的！ 表示MySQL如何查找表中的行。常见的类型有：system > const > eq_ref > ref > range > index > ALL。 性能从好到差。
possible_keys	可能使用的索引。
key	重要的！ 实际使用的索引。 如果为 NULL，表示没有使用索引。
key_len	索引的长度，用于判断索引的覆盖范围。
ref	显示索引的哪一列被使用了。
rows	重要的！ MySQL估计需要扫描的行数。 行数越少越好。
filtered	表示经过搜索条件过滤后剩余记录的百分比。
Extra	重要的！ 包含一些额外的信息，例如：Using index (使用了覆盖索引), Using where (使用了WHERE子句), Using temporary (使用了临时表), Using filesort (使用了文件排序)。

关注重点：

• type： 尽量避免 ALL 和 index 类型。ALL 表示全表扫描，index 表示全索引扫描。
• key： 确保SQL语句使用了索引。
• rows： 扫描的行数越少越好。
• Extra： 避免 Using temporary 和 Using filesort。

第三步：索引失效的常见原因：你的索引是不是“罢工”了？

如果执行计划显示没有使用索引，或者使用了不合适的索引，那么就需要分析索引失效的原因。

1. WHERE 子句中的条件顺序：

MySQL优化器会根据代价选择最佳的执行计划，但有时候条件顺序也会影响索引的使用。

-- 假设 users 表有 (name, age) 联合索引
EXPLAIN SELECT * FROM users WHERE age > 20 AND name = '张三'; -- 可能不会使用索引 (MySQL版本和数据分布有关)
EXPLAIN SELECT * FROM users WHERE name = '张三' AND age > 20; -- 更可能使用索引

建议： 将选择性高的条件放在前面。 选择性是指该条件能够过滤掉更多的数据。 name 通常比 age 选择性更高。

2. 数据类型不匹配：

-- 假设 users 表的 id 字段是 INT 类型
EXPLAIN SELECT * FROM users WHERE id = '123'; -- 可能会导致索引失效 (隐式类型转换)
EXPLAIN SELECT * FROM users WHERE id = 123;   -- 正确使用索引

建议： 确保WHERE子句中的数据类型与表字段的数据类型一致。

3. 函数或表达式：

在WHERE子句中使用函数或表达式，会导致索引失效。

-- 假设 users 表有 created_at 字段 (DATETIME 类型)
EXPLAIN SELECT * FROM users WHERE DATE(created_at) = '2023-10-26'; -- 索引失效
EXPLAIN SELECT * FROM users WHERE created_at >= '2023-10-26 00:00:00' AND created_at < '2023-10-27 00:00:00'; -- 使用索引

建议： 尽量避免在WHERE子句中使用函数或表达式。如果必须使用，考虑创建函数索引（MySQL 5.7+ 支持）。

4. OR 条件：

-- 假设 users 表有 name 和 email 字段，分别有索引
EXPLAIN SELECT * FROM users WHERE name = '张三' OR email = '[email protected]'; -- 索引失效 (通常)

解决方案：

• UNION ALL： 将OR条件拆分成多个SELECT语句，然后使用UNION ALL连接。

  SELECT * FROM users WHERE name = '张三'
  UNION ALL
  SELECT * FROM users WHERE email = '[email protected]';

• 使用覆盖索引 (如果可能): 如果查询只需要返回索引中的字段，可以考虑使用覆盖索引。

• 创建复合索引 (如果条件允许): 将 name 和 email 字段创建在一个复合索引中。

5. LIKE ‘%keyword%’：

模糊查询 LIKE '%keyword%' 会导致索引失效。

-- 假设 users 表有 name 字段，有索引
EXPLAIN SELECT * FROM users WHERE name LIKE '%张三%'; -- 索引失效
EXPLAIN SELECT * FROM users WHERE name LIKE '张三%';  -- 可以使用索引 (前缀匹配)

解决方案：

• 全文索引： 如果需要支持任意位置的模糊查询，可以考虑使用全文索引。

  ALTER TABLE users ADD FULLTEXT INDEX idx_name_fulltext (name);
  
  SELECT * FROM users WHERE MATCH(name) AGAINST('张三' IN BOOLEAN MODE);

• 使用搜索引擎： 将数据同步到 Elasticsearch 或 Solr 等搜索引擎，利用搜索引擎的全文检索能力。

6. NOT IN 和 !=：

NOT IN 和 != 通常会导致索引失效。

-- 假设 users 表有 id 字段，有索引
EXPLAIN SELECT * FROM users WHERE id NOT IN (1, 2, 3); -- 索引失效
EXPLAIN SELECT * FROM users WHERE id != 1;          -- 索引失效

解决方案：

• 使用 NOT EXISTS：

  SELECT * FROM users WHERE NOT EXISTS (SELECT 1 FROM blacklist WHERE blacklist.user_id = users.id);

• 使用范围查询： 如果可能，将 NOT IN 或 != 转换为范围查询。

7. 联合索引不满足最左前缀原则：

如果创建了联合索引 (a, b, c)，那么只有在查询条件中包含了 a，或者 a 和 b，或者 a、b 和 c 时，才能使用该索引。

-- 假设 users 表有联合索引 (name, age, city)
EXPLAIN SELECT * FROM users WHERE age > 20 AND city = '北京'; -- 索引失效 (缺少 name)
EXPLAIN SELECT * FROM users WHERE name = '张三' AND city = '北京'; -- 部分使用索引 (name)
EXPLAIN SELECT * FROM users WHERE name = '张三' AND age > 20 AND city = '北京'; -- 完全使用索引

8. 索引列参与计算
索引列不能参与计算，否则索引失效

-- 假设 order 表有 create_time 字段，有索引
EXPLAIN SELECT * FROM order WHERE YEAR(create_time) = 2024; -- 索引失效
EXPLAIN SELECT * FROM order WHERE create_time BETWEEN '2024-01-01 00:00:00' AND '2024-12-31 23:59:59';-- 可以使用索引

第四步：优化你的SQL：让你的代码跑得更快！

分析完索引失效的原因，就可以开始优化SQL语句了。

1. 优化索引：

• 创建合适的索引： 根据查询条件，创建单列索引或联合索引。
• 删除无用的索引： 过多的索引会影响性能，删除不再使用的索引。

• 重建索引： 定期重建索引，可以优化索引的存储结构。

  OPTIMIZE TABLE users;

2. 重写SQL语句：

• 避免使用 SELECT *： 只查询需要的字段。
• 优化 JOIN： 尽量使用 INNER JOIN，避免使用 LEFT JOIN 或 RIGHT JOIN。
• 使用 LIMIT： 如果只需要返回部分数据，使用 LIMIT 限制返回的行数。
• 分解大查询： 将复杂的查询分解成多个简单的查询。

3. 优化表结构：

• 选择合适的数据类型： 使用占用空间更小的数据类型。
• 垂直分割： 将不常用的字段拆分到另一张表中。
• 水平分割： 将数据量大的表分割成多个小表。

4. 缓存：

• 使用MySQL Query Cache (已废弃)： MySQL 5.7 及之前版本可以使用 Query Cache，但 MySQL 8.0 已经移除了该功能。
• 使用Redis或Memcached： 将查询结果缓存到Redis或Memcached中。
• 使用HTTP缓存： 使用浏览器缓存或CDN缓存。

5. 硬件升级：

• 增加内存： 增加MySQL服务器的内存。
• 使用SSD： 使用SSD硬盘可以提高IO性能。
• 升级CPU： 升级CPU可以提高计算性能。

一些额外的建议：

• 定期分析慢查询日志： 养成定期分析慢查询日志的习惯，及时发现和解决问题。
• 使用性能监控工具： 使用性能监控工具（例如：Prometheus + Grafana）可以实时监控数据库的性能指标。
• 代码审查： 定期进行代码审查，可以发现潜在的性能问题。
• 压测： 在上线之前进行压测，模拟真实的用户访问，评估系统的性能。

总结：

慢查询优化是一个持续的过程，需要不断地学习和实践。希望今天的分享能帮助大家更好地理解慢查询的原理和优化方法。记住，找到“摸鱼”的罪魁祸首，分析执行计划，优化索引和SQL，你的代码就能跑得飞快！

好啦，今天就到这里，如果还有什么问题，随时可以提问！祝大家都能写出高性能的PHP代码！