PHP 8.1 网站数据库索引失效怎么排查

前言

线上最典型的一幕是：明明在orders.user_id、orders.created_at上都建了索引，SHOW INDEX也看得见，可接口响应还是从 20ms 涨到 800ms，慢查询日志里那条 SQL 的Rows_examined高得离谱。把 SQL 贴进客户端一执行，EXPLAIN的key列写着NULL，type列写着ALL——索引在，但优化器没用它。

索引失效（index not used）很少是"索引坏了"，绝大多数时候是优化器主动放弃了它。原因分两层：一层在 SQL 写法上，让条件失去了可索引性（sargability，即"能被索引直接定位"的性质）；另一层在 PHP 代码里，PDO（PHP Data Objects）的参数绑定方式、类型转换、时区处理，会悄悄把一条本该走索引的 SQL 改写成全表扫描的形态。

本文按"先取证、再分类定位、最后落到 PHP 层"的顺序走一遍，配套一段可以直接跑起来看EXPLAIN输出的 PHP 脚本。示例基于 PHP 8.1 + MySQL 8.0，用 PDO 连接。

一、先取证：确认索引到底有没有被用

排查的第一步永远是拿证据，而不是猜。三样东西：SHOW INDEX、EXPLAIN、慢查询日志。

# 1) 看表上到底有哪些索引，注意 Cardinality（基数） mysql -uroot -p -e "SHOW INDEX FROM shop.orders\G" # 2) 看优化器怎么执行这条 SQL mysql -uroot -p -e "EXPLAIN SELECT * FROM shop.orders WHERE user_id = 42\G"

EXPLAIN里只盯三列就够定位大部分问题：

一个有价值的判断技巧：type=index比ALL更隐蔽。它代表"扫了整棵索引树"，数据量大时一样慢，但因为key列不为NULL，很多人误以为索引生效了。

再看Cardinality。如果user_id索引的基数只有 3，而表里有 500 万行，优化器算完代价后会认为"走索引回表比对全表扫还亏"，于是选ALL。这不是 bug，是代价模型（cost model）的正常决策——区分度太低的列，单列索引本来就不该建。

二、SQL 写法层面：什么叫"失去可索引性"

索引是一棵有序的 B+ 树。优化器只有在能把条件翻译成"树上的一个区间"时才会用它。任何破坏"列值有序可比较"的写法，都会让翻译失败。

1. 在索引列上套函数或表达式

-- ❌ 列被函数包住，B+ 树的有序性用不上 SELECT * FROM orders WHERE DATE(created_at) = '2026-09-01'; SELECT * FROM users WHERE LEFT(mobile, 3) = '138'; -- ✅ 改写成对列本身的区间比较 SELECT * FROM orders WHERE created_at >= '2026-09-01 00:00:00' AND created_at < '2026-09-02 00:00:00'; SELECT * FROM users WHERE mobile LIKE '138%';

2. 隐式类型转换

这是最阴的一类。mobile是VARCHAR(11)，SQL 写成WHERE mobile = 13800000000（数字字面量），MySQL 会把列转成数字再比较，索引直接作废，并且EXPLAIN的Extra里会出现Using where而不是Using index condition。

-- ❌ 字符串列和数字比较 SELECT * FROM users WHERE mobile = 13800000000; SELECT * FROM orders WHERE order_no = 20260901001;

反过来，整型列和字符串比较通常还能走索引（MySQL 把常量转成数字），但字符集/排序规则（collation）不一致的连表就不行了：一张表utf8mb4_general_ci，另一张utf8mb4_unicode_ci，JOIN时列上会被强制套一次CONVERT()，被驱动表的索引失效。

3. 前导通配符与OR

LIKE '%keyword'无法定位区间，只能扫。WHERE a = 1 OR b = 2里只要b没索引，整体就走全表；IN用的是同一套代价模型，值很多时也可能放弃索引。

4. 联合索引的最左前缀

联合索引(a, b, c)只对a、(a,b)、(a,b,c)生效。WHERE b = 1 AND c = 2用不上它。另外WHERE a > 10 AND b = 2中，a的范围扫描会让b也退化成过滤条件而非定位条件。

三、PHP 层：PDO 绑定的隐形改写

把 SQL 写对了，PHP 这边还可能把它改回去。

坑一：PDO::ATTR_EMULATE_PREPARES = true（MySQL 驱动下的默认值）。模拟预处理不会把参数送到服务端，而是由 PDO 在客户端做字符串拼接。给整型参数绑定的值会被加引号塞进 SQL，于是WHERE user_id = '42'这种形态出现了——MySQL 把user_id列转成数字比较，索引失效。

// ❌ 默认模拟预处理 + 不指定类型，整型参数可能被当成字符串 $pdo = new PDO($dsn, $user, $pass); $stmt = $pdo->prepare('SELECT * FROM orders WHERE user_id = ?'); $stmt->execute([$_GET['uid'] ?? 0]); // $_GET 里的值是 string // ✅ 关掉模拟预处理，让服务端拿到真正带类型的参数 $pdo = new PDO($dsn, $user, $pass, [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_EMULATE_PREPARES => false, // 交给 MySQL 真预处理 PDO::ATTR_STRINGIFY_FETCHES => false, ]); $stmt = $pdo->prepare('SELECT * FROM orders WHERE user_id = ?'); $stmt->bindValue(1, (int) $_GET['uid'], PDO::PARAM_INT); $stmt->execute();

坑二：LIMIT绑参数。在模拟预处理下LIMIT ?, ?会因为引号变成LIMIT '0', '20'而报语法错误；关掉模拟预处理后可以传整型，但更稳的做法是把分页数字在 PHP 里用max(0, min((int)$n, 100))夹紧后直接内插。

坑三：时区不一致导致的范围失效。PHP 里date('Y-m-d 00:00:00')用的是date.timezone，MySQL 的NOW()用的是连接时区。两者差 8 小时时，你算出来的区间会把当天的数据切掉一半，看起来"索引用了但没查到数据"，实际是区间本身就错了。

四、代码实战：一个能直接跑的 EXPLAIN 对比脚本

下面这个脚本同时跑两条 SQL 并打印EXPLAIN，把"字符串参数 vs 整型参数"的差别摆出来。第一次运行前请把$pdo的 DSN、账号改成你自己的，并把orders表的创建语句跑一遍。

<?php declare(strict_types=1); // 需要 PHP 8.1+，ext-pdo_mysql 扩展 $dsn = 'mysql:host=127.0.0.1;dbname=shop;charset=utf8mb4'; function connect(string $dsn, array $options): PDO { return new PDO($dsn, 'root', 'secret', $options + [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, ]); } function explain(PDO $pdo, string $sql, array $params, string $label): void { $stmt = $pdo->prepare('EXPLAIN ' . $sql); $stmt->execute($params); $row = $stmt->fetch(); printf( "%-34s type=%-8s key=%-12s rows=%-8s extra=%s\n", $label, $row['type'] ?? '-', $row['key'] ?? 'NULL', $row['rows'] ?? '-', $row['Extra'] ?? '-' ); } // 模拟预处理开着（PDO 默认）：整型参数会被加引号 $emulated = connect($dsn, [PDO::ATTR_EMULATE_PREPARES => true]); explain($emulated, 'SELECT * FROM orders WHERE user_id = ?', ['42'], '模拟预处理, 传字符串 "42"'); // 真预处理：交给 MySQL 判断类型 $native = connect($dsn, [PDO::ATTR_EMULATE_PREPARES => false]); explain($native, 'SELECT * FROM orders WHERE user_id = ?', [42], '真预处理, 传 int 42'); // 列上套函数：索引一定失效 explain($native, "SELECT * FROM orders WHERE DATE(created_at) = '2026-09-01'", [], 'DATE(created_at) = ...'); // 等价的区间写法 explain( $native, 'SELECT * FROM orders WHERE created_at >= ? AND created_at < ?', ['2026-09-01 00:00:00', '2026-09-02 00:00:00'], 'created_at 区间比较' );

预期输出形态（具体rows取决于你的数据量和统计信息，请以实测为准）：

模拟预处理, 传字符串 "42" type=ALL key=NULL rows=498213 extra=Using where 真预处理, 传 int 42 type=ref key=idx_user_id rows=6 extra=Using index condition DATE(created_at) = ... type=ALL key=NULL rows=498213 extra=Using where created_at 区间比较 type=range key=idx_created rows=1820 extra=Using index condition

拿到结果后，如果现实中的表仍然显示key=NULL，用EXPLAIN ANALYZE（MySQL 8.0.18+）看实际行数与预估行数差多少。差距在数量级以上，说明统计信息过期，跑一次ANALYZE TABLE orders;通常就能让优化器重新选对索引。

常见坑点

1. 只看"有没有索引"，不看"用没用上"

❌ 建完索引就以为万事大吉，SHOW INDEX有记录就觉得没问题。 ✅ 每次上线新 SQL 都过一遍EXPLAIN，把type和key列入 Code Review 检查项。

2. 用数字字面量去比字符串列

❌WHERE order_no = 20260901001，order_no是VARCHAR。 ✅WHERE order_no = '20260901001'，让字面量类型和列类型一致。

3. 给低区分度列建单列索引就当万事大吉

❌status只有 0/1/2 三个值，建了单列索引后依然全表扫。 ✅ 改成联合索引(status, created_at)，或者干脆不建，靠业务分表解决。

4.pdo->prepare()之后用execute($array)传所有参数

❌ 一个数组塞进去，类型全交给默认推断，整型变字符串。 ✅ 需要精确控制类型时逐个bindValue($pos, $val, PDO::PARAM_INT)。

5. 在 WHERE 里对参数做运算，却以为是列的问题

❌WHERE created_at > NOW() - INTERVAL 7 DAY混在一条带别的条件的 SQL 里，排查时只怀疑索引。 ✅ 把时间基准统一到一端：要么全用 SQL 侧时间函数，要么全在 PHP 里算好再传常量。

6. 连表时字段类型/字符集不一致

❌orders.user_id INT连users.id VARCHAR(32)，被驱动表索引失效。 ✅ 定表结构时就把关联列的类型、字符集、排序规则对齐，并在 CI 里加校验。

7. 用SELECT *破坏覆盖索引

❌ 查询只需要id, amount，却SELECT *，导致无法走Using index，必须回表。 ✅ 只取需要的列，联合索引覆盖后Extra会出现Using index。

8. 统计信息长期不更新

❌ 大批量导入后不跑ANALYZE TABLE，优化器拿着十年前的基数做决策。 ✅ 批量数据变更后执行ANALYZE TABLE，并把innodb_stats_auto_recalc的配置纳入运维检查。

总结

索引失效本质上是"优化器看不懂你的意图"。排查顺序固定为：EXPLAIN取证 → 判断是 SQL 写法问题还是类型问题 → 回溯到 PHP 的参数绑定与字面量类型。把这三步做扎实，绝大多数慢查询不需要加新索引就能解决。