PHP项目如何排查数据库索引失效？

wen PHP项目 2026-06-08 27

本文目录导读：

PHP项目如何排查数据库索引失效？

第一步：识别“问题SQL”
第二步：对照“常见索引失效场景”检查
第三步：在 PHP 项目中实施监控与预防
第四步：实用排查脚本与技巧
一个典型的排查流程

在PHP项目中排查数据库索引失效,通常需要结合数据库分析工具、慢查询日志和业务代码审查，索引失效通常源于SQL查询写法不当或数据分布变化。

以下是系统性的排查步骤和方法：

第一步：识别“问题SQL”

在开始排查前,需要先定位是哪些查询变慢了。

开启慢查询日志

MySQL：

-- 查看是否开启及设置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
-- 临时开启（生产环境谨慎，重启失效）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- 超过1秒的记录
SET GLOBAL log_queries_not_using_indexes = 'ON'; -- 记录没用到索引的查询

PostgreSQL： 设置 log_min_duration_statement 参数。

使用数据库分析工具
- MySQL： EXPLAIN 是核心工具。
```
EXPLAIN SELECT * FROM orders WHERE status = 'pending' AND create_time > '2023-01-01';
```
  重点关注列：
  - type：如果是 ALL（全表扫描）或 index（全索引扫描），基本就是失效或低效。
  - key：实际使用的索引，如果为 NULL，索引完全失效。
  - rows：扫描的行数，远大于期望值通常意味着索引使用不当。
  - Extra：如果出现 Using filesort（文件排序）、Using temporary（临时表）且字段有索引，往往是索引无法用于排序或分组。
- PostgreSQL： 使用 EXPLAIN ANALYZE。
启用数据库通用日志（开发环境）
- 临时记录所有SQL,然后配合 pt-query-digest（Percona Toolkit）等工具汇总，找出执行频率高且慢的查询。

第二步：对照“常见索引失效场景”检查

当你通过 EXPLAIN 发现某个查询没有使用索引，对照以下最常见的失效原因：

查询条件中使用了函数或计算
- 错误： WHERE DATE(create_time) = '2023-01-01' → 索引失效。
- 修复： 改为范围查询 WHERE create_time >= '2023-01-01' AND create_time < '2023-01-02'。
- 同理： WHERE id + 1 = 5 → 改为 WHERE id = 4。
隐式类型转换
- 错误： 列 user_id 是 VARCHAR 类型，但查询用 WHERE user_id = 123（整数）。
- 修复： 确保类型匹配 WHERE user_id = '123'。
- 排查方法： 在 EXPLAIN 的 Extra 列中看到 Using where（无其他信息）且 rows 很大时，需要检查字段类型和查询值类型。
LIKE 查询以通配符开头
- 错误： WHERE name LIKE '%keyword' → 索引完全失效。
- 修复： WHERE name LIKE 'keyword%' → 索引有效（范围查询）。
- 注意： 如果必须搜索中间词，需使用全文索引（FULLTEXT）或 Elasticsearch 等搜索引擎。
OR 条件导致全表扫描
- 错误： WHERE status = 'active' OR age > 18，如果只有 status 有索引，OR 会导致数据库放弃索引，进行全表扫描。
- 修复：
  - 将 OR 改为 UNION ALL。
  - 或者为 OR 两边的列都建立索引（MySQL 5.0+ 可能使用索引合并，但不确定，尽量用 UNION）。
WHERE 条件中的列参与范围查询，且不符合最左前缀原则
- 对于联合索引 (a, b, c)：
  - WHERE a = 1 AND c = 3 → 只能用到 a 列的索引，c 列索引失效（跳过了 b）。
  - WHERE a > 1 AND b = 2 → a 列索引有效（用于范围），但 b 列索引失效。
  - 核心原则： 联合索引中，第一个范围查询（>, <, BETWEEN, LIKE 'abc%'）之后的列索引全部失效。
数据分布不均（选择性太低）
- 即使有索引,如果某个值占比过大（status 字段，90% 都是 1），查询优化器评估后认为全表扫描比使用索引更快（因为回表开销大）。
- 排查： SELECT COUNT(*) FROM table WHERE status = 1 占总行数比 > 20-30%。
- 解决： 创建覆盖索引或考虑分区表。
索引列允许 NULL 值，但查询逻辑不匹配
- 错误： WHERE name != '张三' 或 WHERE name IS NULL 在某些情况下可能导致索引失效（取决于数据库实现和版本）。
- 建议： 尽量避免索引列使用 NULL，或确保查询明确处理 NULL。

第三步：在 PHP 项目中实施监控与预防

ORM 层监控（Laravel Eloquent / Doctrine / ThinkPHP）

监听数据库查询事件。

Laravel 示例：

// AppServiceProvider 中注册
\DB::listen(function ($query) {
    $sql = $query->sql;
    $bindings = $query->bindings;
    $time = $query->time;
    // 记录慢查询：执行时间 > 100ms
    if ($time > 100) {
        \Log::warning('Slow SQL: ' . $sql, ['bindings' => $bindings, 'time' => $time]);
    }
    // 可以在此处集成 EXPLAIN 检查（注意性能消耗，建议采样）
    // 对 SELECT 且时间较长的 SQL 执行 EXPLAIN
});

ThinkPHP： 使用 Db::listen 或 Profile 日志。

集成数据查询分析工具
- Laravel Telescope / Debugbar：开发环境必备，可视化显示每个 SQL 的执行时间、EXPLAIN 结果。
- 通用查询日志分析：使用 pt-query-digest 定期分析慢查询日志，形成报表。
使用数据库连接池/读写分离
- 读库专门处理 SELECT，减少写锁影响，但索引问题仍需独立排查。

第四步：实用排查脚本与技巧

快速检查全表扫描的查询（MySQL）
```
SELECT * FROM sys.statements_with_full_table_scans WHERE db = 'your_db_name';
```
（需要开启 Performance Schema）

检查未被使用的索引

SELECT * FROM sys.schema_unused_indexes WHERE object_schema = 'your_db_name';

这些索引可能冗余或查询没用到。

分析索引基数（Cardinality）
```
SHOW INDEX FROM orders;
```
Cardinality 值远小于表行数，说明该列重复值太多，索引效果差。

一个典型的排查流程

发现慢 API/页面 → 前端或监控报警。
查看慢查询日志 → 找到具体 SQL。
在数据库客户端执行 EXPLAIN [慢SQL] → 看到 type: ALL 或 rows: 1000000。
对照上述失效场景 → 检查 WHERE 条件：
- 有无函数包裹？
- 类型是否匹配？（如 user_id = '123' vs user_id = 123）
- 联合索引顺序是否吻合？
构造优化后的 SQL → 用 EXPLAIN 验证 type 变为 ref 或 range。
更新 PHP 代码中的查询条件。
观察指标下降 → 确认问题解决。

一个关键提醒： 索引失效排查最终是数据库查询优化问题，在 PHP 代码层面，最核心的动作是将原始 SQL 记录下来，然后用 EXPLAIN 去分析它，所有其他工具（Laravel Debugbar、慢查询日志）都是在帮你获取这个 SQL 文本。