如何防止MySQL索引失效导致行锁升级为表锁_强制要求SQL执行走索引路径-mysql教程-PHP中文网

where条件使用函数（如year()、date()）会导致索引失效，优化器放弃索引转为全表扫描并等效锁表；正确写法应改用范围表达式，如created_at >= '2024-01-01' and created_at

WHERE条件用了函数，索引直接失效

比如 WHERE YEAR(created_at) = 2024 或 WHERE DATE(update_time) > '2024-01-01'，MySQL 无法用索引的有序结构做范围比较，优化器会放弃该索引，退化为全表扫描——每扫一行加一个行锁，最终等效于锁表。

正确写法是改用范围表达式：

WHERE created_at >= '2024-01-01' AND created_at

WHERE update_time > '2024-01-01 00:00:00'

注意：哪怕只差一秒，也比函数调用安全；EXPLAIN 中 key 不为 NULL、rows 明显小于总行数，才算真正生效。

隐式类型转换让索引“看不见”

字段是 INT 类型，却传字符串值：WHERE user_id = '123'，MySQL 会把所有 user_id 值转成字符串再比对，索引的 B+ 树结构彻底失效。

这类问题极隐蔽，因为语句能执行、结果也对，但锁范围爆炸式扩大。

检查应用层拼 SQL 时是否做了类型兜底（如 Java 的 PreparedStatement.setInt()）

在 MySQL 配置中开启 sql_mode=STRICT_TRANS_TABLES，部分隐式转换会报错而非静默失败

用 SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 't' AND COLUMN_NAME = 'user_id' 确认字段真实类型

FORCE INDEX 并不能防止锁升级

UPDATE t SET status = 1 FORCE INDEX (idx_status) WHERE status IN (0,1,2,3,4) 看似走了索引，但实际可能锁住全部匹配记录及其间隙——如果这个索引选择性差（比如 status 只有 5 个值），rows 接近全表，锁行为和表锁无异。

MySQL
编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。

下载

FORCE INDEX 只解决“走不走索引”，不解决“锁多少行”。

强制后务必跑 EXPLAIN FORMAT=TRADITIONAL，看 key_len 和 rows 是否合理

优先建覆盖索引：比如 UPDATE t SET status = 1 WHERE id = 123 AND status = 0，索引应为 (id, status) 而非单列 status

避免对高频更新字段建低选择性索引，否则锁竞争从“分散”变成“热点”

怎么确认当前 SQL 真的没锁表？

别信 SHOW ENGINE INNODB STATUS\G 里模糊的 “HOLDS THE LOCK(S)” ——它只显示最近事务，漏掉静默持有者。

MySQL 8.0+ 唯一可信方式是查 performance_schema.data_locks：
SELECT LOCK_TYPE, LOCK_MODE, INDEX_NAME, OBJECT_NAME 
FROM performance_schema.data_locks 
WHERE OBJECT_SCHEMA = 'your_db' AND OBJECT_NAME = 'your_table';
若出现多条 LOCK_TYPE = 'RECORD' 且 INDEX_NAME 是主键或二级索引名，说明是行锁；一旦看到 LOCK_TYPE = 'TABLE'，就是真表锁（通常是 LOCK TABLES 或 DDL 引起）。

真正麻烦的从来不是“有没有索引”，而是“索引建对了没、WHERE 写对了没、类型对上了没”——这三个“对”，少一个，锁就可能从行级膨胀到整张表。