大家好，我是小耶，写功课只是为了我踩过的坑，你们别再踩了！
索引建了，EXPLAIN也显示走了索引，但查询就是慢——这种情况比“没走索引”更让人崩溃。
因为你知道问题出在哪，但不知道怎么排查。
字符集和排序规则不一致，就是导致这种情况的最隐蔽原因之一。它不会报错，不会给你任何提示，但会让索引“假装在工作”——EXPLAIN显示用了索引，实际上索引的过滤效果大打折扣。
今天把字符集与排序规则导致索引失效的三种场景彻底拆开讲清楚。
先搞懂几个词：
字符集（Character Set）：数据库中存储字符的编码方式。常见的有utf8mb4、utf8、latin1、gbk。
排序规则（Collation）：同一字符集下，字符的比较和排序规则。比如utf8mb4_general_ci和utf8mb4_unicode_ci，前者比较快但不够精确，后者更精确但稍慢。
隐式转换：当两个不同字符集或排序规则的值进行比较时，MySQL会自动做类型转换。转换过程可能导致索引失效。
一、场景一：JOIN关联字段字符集不一致
这是最常见的字符集索引失效场景。
-- 表A的user_id是utf8mb4
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
user_id VARCHAR(64) CHARACTER SET utf8mb4,
amount DECIMAL(10,2),
INDEX idx_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 表B的user_id是utf8
CREATE TABLE users (
id BIGINT PRIMARY KEY,
user_id VARCHAR(64) CHARACTER SET utf8,
username VARCHAR(50)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 关联查询
SELECT o.id, o.amount, u.username
FROM orders o
JOIN users u ON o.user_id = u.user_id
WHERE u.username = '张三';
这条SQL看起来没问题，但orders.user_id是utf8mb4，users.user_id是utf8。MySQL在JOIN比较时，需要将两个字段转换为同一字符集。
问题在于：转换的方向决定了索引能否使用。
MySQL的隐式转换规则是：将字符集较小的值转换为字符集较大的值。utf8mb4是utf8的超集，所以users.user_id（utf8）会被转换为utf8mb4再比较。
这意味着orders.user_id上的索引idx_user_id仍然可以使用，但users.user_id上的索引无法使用——因为索引是按照原始字符集utf8排序的，转换后的值无法在索引中直接定位。
结果：orders表走了索引，users表全表扫描。如果users表有100万行，这个JOIN就会慢得离谱。
解决方案：统一关联字段的字符集。建表时统一使用utf8mb4，不要混用utf8和utf8mb4。
二、场景二：排序规则不一致引发隐式转换
字符集相同但排序规则不同，同样会导致索引失效。
sql
-- 表A的name是utf8mb4_general_ci
CREATE TABLE products (
id BIGINT PRIMARY KEY,
name VARCHAR(100) COLLATE utf8mb4_general_ci,
INDEX idx_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- 表B的name是utf8mb4_unicode_ci
CREATE TABLE categories (
id BIGINT PRIMARY KEY,
name VARCHAR(100) COLLATE utf8mb4_unicode_ci
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- 关联查询
SELECT p.id, p.name
FROM products p
JOIN categories c ON p.name = c.name;
两张表的name字段字符集都是utf8mb4，但排序规则不同——一个是utf8mb4_general_ci，一个是utf8mb4_unicode_ci。
MySQL在比较时需要将两个字段转换为同一排序规则。转换后，products.name上的索引idx_name无法使用——索引按照utf8mb4_general_ci排序，转换后的值无法在索引中定位。
排查方法：
SHOW FULL COLUMNS FROM products LIKE 'name';
SHOW FULL COLUMNS FROM categories LIKE 'name';
Collation列会显示每个字段的排序规则。如果不一致，就是问题所在。
解决方案：统一排序规则。建表时统一指定COLLATE utf8mb4_unicode_ci或utf8mb4_general_ci，不要混用。
三、场景三：WHERE条件中字符串与数字隐式转换
这个场景和字符集关系不大，但同样是隐式转换导致的索引失效。
-- phone字段是VARCHAR类型，有索引
CREATE TABLE users (
id BIGINT PRIMARY KEY,
phone VARCHAR(20),
INDEX idx_phone (phone)
);
-- ❌ 失效：传入了数字，触发隐式类型转换
SELECT * FROM users WHERE phone = 13800138000;
-- ✅ 生效：传入字符串
SELECT * FROM users WHERE phone = '13800138000';
当VARCHAR类型的字段与数字比较时，MySQL会将字符串转换为数字再比较。这意味着索引列上发生了函数运算——索引失效。
排查方法：
EXPLAIN SELECT * FROM users WHERE phone = 13800138000;
-- type=ALL，全表扫描
EXPLAIN SELECT * FROM users WHERE phone = '13800138000';
-- type=ref，索引查找
SHOW WARNINGS;
-- 会显示隐式转换的警告信息
四、排查字符集问题的通用方法
方法一：查看表和字段的字符集
-- 查看表的字符集和排序规则
SHOW TABLE STATUS LIKE 'orders'\G
-- 查看字段的字符集和排序规则
SHOW FULL COLUMNS FROM orders;
方法二：用EXPLAIN识别索引失效
EXPLAIN SELECT ...;
关注以下信号：
-
type=ALL：全表扫描 -
key=NULL：没有使用索引 -
rows很大但实际返回行数很少：索引过滤效果差
方法三：用SHOW WARNINGS查看隐式转换
EXPLAIN SELECT * FROM users WHERE phone = 13800138000;
SHOW WARNINGS;
如果输出中包含“Converting column 'phone' from VARCHAR to INT”之类的信息，说明发生了隐式转换。
五、真实案例：从3秒到0.05秒
某电商平台的订单查询接口，响应时间从平均200ms突然涨到3秒。慢查询日志显示，问题出在一条JOIN查询上：
SELECT o.id, o.amount, u.username
FROM orders o
JOIN users u ON o.user_id = u.user_id
WHERE o.create_time >= '2026-09-01';
orders表走了idx_create_time索引，但users表的JOIN字段没有走索引——因为orders.user_id是utf8mb4，users.user_id是utf8。
排查过程：
-
用
EXPLAIN确认执行计划：users表type=ALL -
用
SHOW FULL COLUMNS检查两个字段的字符集 -
确认字符集不一致
解决方案：将users.user_id的字符集从utf8改为utf8mb4。
ALTER TABLE users MODIFY user_id VARCHAR(64) CHARACTER SET utf8mb4;
优化后：查询响应时间从3秒降到0.05秒。users表的JOIN字段走了索引，不再全表扫描。
六、小结
字符集与排序规则导致的索引失效是最隐蔽的性能问题之一。JOIN关联字段字符集不一致、排序规则不匹配、WHERE条件中字符串与数字隐式转换——这三种场景不会报错，EXPLAIN也可能显示走了索引，但实际性能差了几十倍。排查的核心方法是：用SHOW FULL COLUMNS检查字段字符集，用EXPLAIN确认索引使用情况，用SHOW WARNINGS查看隐式转换。建表时统一字符集和排序规则，是避免这类问题的最根本方法。
小耶在手，SQL 不愁
还有什么想了解的，欢迎留言！小耶一定知无不言言无不尽……我们下次见~
