PHP 开发中数据库查询缺少索引问题详解

---

1. 引言

在 PHP 应用中，数据库查询性能往往是决定整个应用响应速度的关键。随着数据量的增长，原本“秒开”的页面可能变成“卡顿”，甚至超时。而其中最常见、最容易被忽视的罪魁祸首就是缺少索引。一个缺少索引的查询，即使逻辑再简单，当数据量达到百万级时，也可能需要数秒才能完成；而有了合适的索引，查询时间可能骤降至毫秒级。索引是数据库性能优化的基石，但许多 PHP 开发者在设计数据库或编写 SQL 时，往往只关注业务逻辑，忽略了索引的重要性，导致项目上线后出现严重的性能瓶颈。本文将深入剖析数据库查询缺少索引的成因、诊断方法，并提供从基础到高级的索引优化策略，帮助 PHP 开发者彻底告别慢查询。

---

2. 问题现象

• 页面响应缓慢：用户访问列表页或详情页时，需要等待数秒甚至数十秒。
• 数据库 CPU 飙升：top 或数据库监控显示 MySQL 进程 CPU 使用率接近 100%。
• 慢查询日志：开启了慢查询日志后，大量 SQL 语句被记录，且执行时间远超预期。
• EXPLAIN 结果出现全表扫描：执行计划中的 type 列为 ALL 或 index，rows 扫描行数极大。
• 索引未生效：明明创建了索引，但查询仍然很慢，EXPLAIN 显示 possible_keys 有索引，但 key 为 NULL。
• 数据库连接数飙升：查询慢导致事务长时间不提交，连接被占用，最终连接池耗尽。

---

3. 根本原因分析

3.1 索引的作用与原理

索引是数据库表中一种特殊的数据结构（如 B+Tree），它允许数据库快速定位到数据行，而无需扫描全表。可以将索引理解为书籍的目录：没有目录时，查找内容需要逐页翻阅（全表扫描）；有了目录，可以直接跳转到对应页码（索引查找）。

MySQL 中的索引类型：

• B-Tree 索引：最常用，适用于全值匹配、范围查询、排序等。
• 哈希索引：Memory 引擎支持，适用于精确查找。
• 全文索引：用于文本搜索。
• 空间索引：用于地理数据。

3.2 缺少索引的典型场景

场景	说明
未对 WHERE 条件列建索引	查询条件中的列没有索引，导致全表扫描。
JOIN 关联列未建索引	多表 JOIN 时，被驱动表的关联列没有索引，导致每次匹配都全表扫描。
ORDER BY / GROUP BY 列无索引	排序或分组操作未使用索引，导致 filesort 或临时表，性能低下。
复合索引顺序不当	创建了复合索引，但查询条件未遵循最左前缀原则，导致索引无法使用。
索引列参与函数或计算	WHERE YEAR(create_time) = 2023 会使索引失效，因为对列进行了计算。
隐式类型转换	字段类型为 INT，但传入字符串，导致索引失效。
LIKE 以通配符开头	LIKE '%keyword' 无法使用索引。
OR 条件未全部索引	WHERE a = 1 OR b = 2，如果 a 和 b 都有索引，则可能使用索引合并，否则全表扫描。
数据分布不均	即使有索引，如果某个值占比过高（如性别），优化器可能放弃索引。

3.3 索引失效的深层原因

• 优化器认为全表扫描更快：当查询需要访问表中大部分数据时，优化器可能选择全表扫描（如小表、或索引选择性差）。
• 索引统计信息过时：ANALYZE TABLE 未执行，导致优化器误判。
• 使用 != 或 NOT IN：这类操作通常无法使用索引。
• 使用 IS NULL / IS NOT NULL：部分情况下可能使用索引，但选择性差时不会。

3.4 缺少索引对应用的影响

• 响应时间线性增长：随着数据量增加，全表扫描的耗时与数据量成正比。
• 数据库锁竞争加剧：慢查询持有锁时间更长，导致并发性能下降。
• 资源消耗巨大：CPU、I/O 被大量占用，影响其他查询。
• PHP 进程阻塞：每个请求等待数据库返回的时间变长，PHP-FPM 进程被长时间占用，导致并发能力下降。

---

4. 诊断与定位方法

4.1 使用 EXPLAIN 分析执行计划

EXPLAIN 是分析 SQL 性能的首选工具。执行 EXPLAIN SELECT ... 查看关键字段：

• type：访问类型，从好到差依次为：
  • const：主键或唯一索引查询，返回一行。
  • eq_ref：唯一索引连接。
  • ref：非唯一索引查找。
  • range：范围查询（BETWEEN、>、<）。
  • index：全索引扫描。
  • ALL：全表扫描（最差）。
• possible_keys：可能使用的索引。
• key：实际使用的索引。
• key_len：使用的索引长度，可判断复合索引使用了几列。
• rows：预估扫描的行数。
• Extra：重要信息，如 Using filesort（需要额外排序）、Using temporary（使用临时表），通常表示需要优化。

示例：

EXPLAIN SELECT * FROM orders WHERE user_id = 123;

如果 type=ALL，rows=1000000，说明缺少 user_id 索引。

4.2 开启慢查询日志

在 MySQL 配置中开启慢查询日志，并设置阈值（如 long_query_time=1），记录执行时间超过 1 秒的 SQL。

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = ON;  -- 记录未使用索引的查询

通过分析慢查询日志，找出高频慢 SQL。

4.3 查看索引使用情况

SELECT * FROM sys.schema_unused_indexes;  -- 查看从未使用的索引
SELECT * FROM sys.schema_redundant_indexes; -- 查看冗余索引

这些信息可以帮助清理无用索引，并发现缺失索引的表。

4.4 使用性能分析工具

• pt-query-digest（Percona Toolkit）：分析慢查询日志，汇总统计。
• MySQL Enterprise Monitor：提供索引建议。
• Laravel Debugbar：在开发环境中显示查询次数和每个 SQL 的 EXPLAIN。

4.5 代码层面检查

• 审查 PHP 代码中的 SQL 语句，特别关注 WHERE、JOIN、ORDER BY 中的列。
• 检查是否使用了 ORM 的 where 条件，并确认这些列是否建立了索引。

---

5. 解决方案与最佳实践

5.1 索引设计原则

• 为 WHERE、JOIN、ORDER BY、GROUP BY 中的列建索引。
• 选择区分度高的列：索引列的值越唯一，索引效果越好（如主键 > 唯一索引 > 普通索引）。
• 避免过多索引：每个索引都会占用磁盘空间，并影响 INSERT、UPDATE、DELETE 的性能。
• 使用复合索引：当查询条件包含多个列时，使用复合索引，并遵循最左前缀原则。
• 尽量使用覆盖索引：如果索引包含了查询所需的所有列，则无需回表，性能极高。

5.2 复合索引的最左前缀原则

复合索引 (a, b, c) 可以用于以下条件：

• WHERE a = 1
• WHERE a = 1 AND b = 2
• WHERE a = 1 AND b = 2 AND c = 3
• WHERE a = 1 ORDER BY b
• WHERE a = 1 AND b > 2（范围查询，后面的列无法使用索引）

不能用于：

• WHERE b = 2（缺少最左列）
• WHERE a = 1 AND c = 3（跳过了 b，只能用到 a）

设计复合索引时，应将区分度高的列放在前面，并将范围查询的列放在最后。

5.3 避免索引失效

• 不要在索引列上进行函数或计算：WHERE DATE(create_time) = '2023-01-01' 无法使用索引，应改为 WHERE create_time >= '2023-01-01' AND create_time < '2023-01-02'。
• 避免隐式类型转换：如字段是 INT，传入字符串 '123'，MySQL 会转换，导致索引失效。确保参数类型与字段一致。
• LIKE 通配符：LIKE 'keyword%' 可以使用索引，LIKE '%keyword' 不能。尽量将通配符放在右侧。
• 使用 OR 时，确保所有列都有索引：否则全表扫描。可以用 UNION 代替。

5.4 使用 EXPLAIN 验证索引效果

在创建或修改索引后，务必用 EXPLAIN 验证执行计划，确保 type 不是 ALL 或 index，rows 尽可能小。

5.5 定期维护索引

• 更新统计信息：ANALYZE TABLE table_name; 让优化器掌握最新数据分布。
• 重建索引：OPTIMIZE TABLE table_name; 整理索引碎片（适用于频繁更新的表）。
• 删除冗余索引：重复或极少使用的索引会浪费空间并影响写入性能。

5.6 针对特定场景的索引策略

• 分页查询优化：对于 LIMIT 100000, 20 这类大偏移量分页，使用子查询或延迟关联，并保证索引覆盖。
• 排序优化：如果 ORDER BY 列有索引，且与 WHERE 条件列组成复合索引，可避免 filesort。
• JOIN 优化：确保被驱动表的关联列有索引。通常将小表作为驱动表，大表作为被驱动表。

5.7 监控与预警

• 定期分析慢查询日志，找出新增的慢 SQL。
• 使用监控工具（如 Prometheus + Grafana）展示数据库 QPS、慢查询数量、索引命中率等指标。
• 设置告警，当慢查询数量超过阈值时通知开发人员。

---

6. 案例实战

案例1：用户订单列表查询慢

场景：电商系统的订单表 orders 已有 500 万行数据。用户查看自己的订单列表时，执行 SQL：

SELECT * FROM orders WHERE user_id = 12345 ORDER BY created_at DESC LIMIT 10;

该查询耗时约 3 秒。

分析：使用 EXPLAIN，发现 type=ALL，rows=5000000，说明全表扫描。虽然 user_id 和 created_at 都没有索引。

解决方案：

1. 为 user_id 创建索引：ALTER TABLE orders ADD INDEX idx_user_id (user_id);
2. 因为查询还涉及 ORDER BY created_at，可创建复合索引：ALTER TABLE orders ADD INDEX idx_user_created (user_id, created_at); 这样既能快速筛选用户，又能直接按 created_at 排序，避免 filesort。

效果：查询时间降至 0.01 秒。

案例2：商品搜索中 LIKE 导致全表扫描

场景：商品搜索功能允许模糊搜索商品名称，SQL：

SELECT * FROM products WHERE name LIKE '%手机%';

表中有 200 万商品，查询耗时 8 秒。

分析：由于 LIKE '%手机%' 以通配符开头，无法使用 name 列上的普通 B-Tree 索引，导致全表扫描。

解决方案：

• 如果业务允许，可以改为 LIKE '手机%'，并建立 name 索引。
• 或使用全文索引：ALTER TABLE products ADD FULLTEXT(name);，然后 SELECT * FROM products WHERE MATCH(name) AGAINST('手机' IN NATURAL LANGUAGE MODE);。全文索引适用于模糊搜索，且性能较好。

效果：全文索引后，查询耗时降至 0.1 秒。

案例3：JOIN 缺少索引导致性能差

场景：查询订单详情，关联用户表：

SELECT o.*, u.name FROM orders o JOIN users u ON o.user_id = u.id WHERE o.id = 12345;

orders 表有 500 万行，users 表有 200 万行。查询耗时 2 秒。

分析：EXPLAIN 显示 users 表为全表扫描（type=ALL），因为 users.id 是主键，但 orders.user_id 没有索引。MySQL 选择 orders 作为驱动表（type=const，因为 o.id 是主键），然后对 users 进行全表扫描。

解决方案：为 orders.user_id 添加索引：ALTER TABLE orders ADD INDEX idx_user_id (user_id);

效果：查询耗时降至 0.01 秒。

案例4：复合索引顺序不当导致索引未用

场景：经常需要按 status 和 created_at 查询订单：

SELECT * FROM orders WHERE status = 1 AND created_at > '2023-01-01' ORDER BY created_at DESC LIMIT 10;

创建了复合索引 (status, created_at)，但查询仍然慢。

分析：EXPLAIN 显示使用了该索引，但 key_len 只用了 status 部分，created_at 部分因范围查询（>）而无法用于排序，导致 Using filesort。

解决方案：将范围查询的列放在复合索引的后面：(status, created_at) 其实已经符合，但排序仍然需要额外操作。如果排序需求强烈，可以尝试 (created_at, status)，但 WHERE 中 status 是等值条件，created_at 是范围，应把等值列放在前面。正确做法是保持 (status, created_at)，并确保索引覆盖排序（索引已按 created_at 排序）。但这里 ORDER BY created_at 与索引顺序一致，按理不会 filesort。再检查发现 created_at 是 > 范围，在索引中，范围查询后的列无法用于排序，所以 filesort 仍然出现。优化方式：要么创建索引 (status, created_at) 并让排序依赖索引，但范围查询会使排序失效；要么将 LIMIT 改写为延迟关联，或者接受 filesort 但确保数据量不大。

最终方案：如果 status=1 的数据量较少，可以直接用该索引；如果数据量大，可考虑使用覆盖索引或缓存。

---

7. 总结

数据库查询缺少索引是导致 PHP 应用性能问题的最常见原因之一。通过合理的索引设计，可以显著提升查询效率，降低数据库负载。作为 PHP 开发者，应当：

• 养成索引意识：在编写 SQL 或使用 ORM 时，考虑查询条件是否适合建立索引。
• 使用 EXPLAIN 验证：每次上线前，对关键查询执行 EXPLAIN，确保没有全表扫描。
• 遵循最左前缀原则：设计复合索引时，将等值条件放在前面，范围条件放在后面。
• 避免索引失效：注意函数、类型转换、LIKE 通配符等问题。
• 定期维护索引：分析慢查询日志，清理冗余索引，更新统计信息。
• 结合业务数据特征：对于选择性差的列（如性别），谨慎使用索引。

索引不是万能的，但缺失索引是万万不能的。在数据量增长的过程中，索引的缺失会逐步放大，最终成为系统崩溃的导火索。只有从开发之初就重视索引，才能让 PHP 应用在面对海量数据时依然保持流畅。

---