当前位置：首页 > news >正文

MySQL 索引失效案例：字符集不匹配的隐蔽影响

news 来源：原创 2025/5/16 4:01:11

引言

在 MySQL 数据库世界里，索引失效往往是性能问题的罪魁祸首。你是否曾遇到过这样的情况：明明加了索引，查询却慢如蜗牛？你是否曾以为小表查询就一定高效？本文将揭示一个真实的案例，一个容易被忽视的“隐形杀手”—— 字符集不匹配，它如何悄无声息地破坏你的索引，让你的查询在小表上也能崩溃。更令人惊讶的是，LIMIT 子句竟然在某种程度上“掩盖”了这个问题的严重性，直到 ORDER BY 的出现，才让真相大白。准备好迎接这场关于 MySQL 字符集、索引、LIMIT 和 ORDER BY 的深度探险了吗？让我们一起揭开这个性能谜团！

问题描述

我们有两个表：wdm_recruit_sett (患者结算表) 和 sys_user (用户表)，数据量都在 5000 条左右。我们进行以下两种查询：

查询 1 (不带 ORDER BY)：

SELECT1
FROMwdm_recruit_sett wrsLEFT JOIN sys_user su ON su.id = wrs.user_id
LIMIT 10;

查询 2 (带 ORDER BY)：

SELECT1
FROMwdm_recruit_sett wrsLEFT JOIN sys_user su ON su.id = wrs.user_id
ORDER BY wrs.create_time DESC  --  create_time 没有索引
LIMIT 10;

表结构（简化）：

wdm_recruit_sett：

CREATE TABLE wdm_recruit_sett (id VARCHAR(32) NOT NULL PRIMARY KEY,user_id VARCHAR(64) NULL,create_time DATETIME NULL,-- ... 其他字段 ...INDEX idx_user_id (user_id)
);  -- 字符集：数据库默认的 utf8mb4

sys_user：

CREATE TABLE sys_user (id VARCHAR(32) NOT NULL PRIMARY KEY-- ... 其他字段 ...
) CHARACTER SET utf8;  -- 字符集：显式指定为 utf8

性能差异巨大：

查询 1 (无 ORDER BY)：执行时间在 毫秒级。
查询 2 (有 ORDER BY)：执行时间飙升至 十几秒！

问题分析

我们分别查看两个查询的 EXPLAIN 执行计划：

查询 1 (无 ORDER BY) 的执行计划：

-- (图片中的 EXPLAIN 输出)
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: wrspartitions: NULLtype: ALL
possible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 5241filtered: 100Extra: NULL
*************************** 2. row ***************************id: 1select_type: SIMPLEtable: supartitions: NULLtype: ALL
possible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 5953filtered: 100Extra: Using where; Using join buffer (Block Nested Loop)

查询 2 (带 ORDER BY) 的执行计划 (来自之前的图片)：

-- (之前的图片中的 EXPLAIN 输出)
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: wrspartitions: NULLtype: ALL
possible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 5241filtered: 100Extra: Using temporary; Using filesort
*************************** 2. row ***************************id: 1select_type: SIMPLEtable: supartitions: NULLtype: ALL
possible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 5953filtered: 100Extra: Using where; Using join buffer (Block Nested Loop)

分析要点：

两个查询都是全表扫描 (type: ALL)： 由于 sys_user.id (utf8) 和 wdm_recruit_sett.user_id (utf8mb4) 的字符集不匹配，MySQL 无法使用任何索引进行连接，导致对两个表都进行了全表扫描。
LIMIT 10 的“掩盖”作用（查询 1）： 在没有 ORDER BY 的情况下，MySQL 在找到 10 条匹配的记录后就可以停止扫描，即使是全表扫描，由于只需要很少的数据，执行速度也很快（毫秒级）。这掩盖了字符集不匹配导致的索引失效问题。
ORDER BY 暴露问题（查询 2）： ORDER BY wrs.create_time DESC 迫使 MySQL 必须对 wrs 表进行完整的全表扫描，并进行文件排序 (filesort)，才能找到最大的 10 条记录。这时，全表扫描的代价就完全暴露出来了，导致执行时间飙升至十几秒。
possible_keys 为 NULL: 即使 sys_user 有主键,字符集不匹配导致优化器在评估阶段就排除了所有索引.

根本原因：字符集不匹配

sys_user.id (utf8) 和 wdm_recruit_sett.user_id (utf8mb4) 的字符集不一致，是导致索引失效、全表扫描的根本原因。

解决方案：统一字符集

将 sys_user 表的 id 字段（以及其他相关文本字段）的字符集修改为 utf8mb4，与 wdm_recruit_sett 表一致。

-- (请务必先备份数据!)
-- 1. 查找并删除引用 sys_user.id 的外键约束 (如果有)
-- 2. 删除 sys_user 表的主键
ALTER TABLE sys_user DROP PRIMARY KEY;
-- 3. 修改 id 列的字符集
ALTER TABLE sys_user MODIFY id VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;
-- 4. 重新添加主键
ALTER TABLE sys_user ADD PRIMARY KEY (id);

修改字符集后, 不管有没有order by，MySQL 都能高效地利用索引，性能将得到极大提升。同时, 建议在wrs表上创建create_time索引, 以优化order by。

总结

这个案例揭示了一个容易被忽视的 MySQL 性能陷阱：

字符集不匹配是隐蔽的性能杀手： 它会导致索引失效，即使在小表上也会产生严重影响。
LIMIT 可能会掩盖问题： 在没有 ORDER BY 的情况下，LIMIT 可能会让全表扫描看起来很快，从而掩盖了潜在的性能问题。
ORDER BY 暴露问题： ORDER BY 非索引列会迫使 MySQL 进行完整扫描和排序，放大了潜在的性能问题。
EXPLAIN 是诊断利器： EXPLAIN 可以帮助我们看清 MySQL 的执行计划，揭示问题的根源。
数据库设计规范至上： 从一开始就应该统一使用 utf8mb4 字符集，避免后期出现各种难以排查的性能问题。

引言

问题描述

问题分析

根本原因：字符集不匹配

解决方案：统一字符集

总结

希望这个案例能帮助大家更深入地理解 MySQL 的查询优化，避免类似的性能陷阱。

相关文章：