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

最新推荐文章于 2025-06-15 17:36:11 发布

全端工程师

最新推荐文章于 2025-06-15 17:36:11 发布

阅读量680

点赞数 18

分类专栏： MySQL 文章标签： mysql 数据库

本文链接：https://blog.youkuaiyun.com/weixin_44283682/article/details/145601560

版权

MySQL 专栏收录该内容

2 篇文章

订阅专栏

引言

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

问题描述

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

查询 1 (不带 ORDER BY)：

SELECT
  1
FROM
  wdm_recruit_sett wrs
  LEFT JOIN sys_user su ON su.id = wrs.user_id
LIMIT 10;

查询 2 (带 ORDER BY)：

SELECT
  1
FROM
  wdm_recruit_sett wrs
  LEFT 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: 1
  select_type: SIMPLE
        table: wrs
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5241
     filtered: 100
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: su
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5953
     filtered: 100
        Extra: Using where; Using join buffer (Block Nested Loop)

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

-- (之前的图片中的 EXPLAIN 输出)
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: wrs
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5241
     filtered: 100
        Extra: Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: su
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5953
     filtered: 100
        Extra: 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);