什么时候该用 CREATE INDEX
加 USING BTREE
指定前缀长度
只有对
VARCHAR、
TEXT这类可变长字符串字段建索引时，才需要考虑前缀索引。MySQL 不允许对全文本建索引（比如
VARCHAR(1000)字段直接
INDEX(col)），否则可能超出单索引最大长度限制（InnoDB 默认 767 字节，utf8mb4 下约 191 个字符）。这时必须显式指定前缀长度：
CREATE INDEX idx_name ON users (email(191));
注意：
USING BTREE是默认引擎行为，一般不用写；但如果你在建联合索引且其中一列是长字符串，前缀必须放在最后，否则会报错：
-- ✅ 正确：长字段放最后，并带前缀 CREATE INDEX idx_u ON users (status, email(191)); <p>-- ❌ 错误：长字段放前面又没前缀，或前缀位置不合法 CREATE INDEX idx_u ON users (email(191), status); -- 部分版本允许，但排序/查询效率受损
SHOW INDEX
怎么确认前缀长度生效了
执行
SHOW INDEX FROM table_name后，重点看
Sub_part列：值为
NULL表示全列索引；数字（如
191）表示前缀长度。如果建了
email(191)却看到
Sub_part是
NULL，说明建索引语句没生效——常见原因是字段实际类型是
TEXT，而你忘了加前缀，MySQL 自动忽略索引定义。 检查
Key_name是否存在，避免重名冲突覆盖 确认
Collation是
A（Ascending），不是
NULL（表示未启用） 如果
Sub_part显示
191但查询仍走全表扫描，大概率是
WHERE条件没用上最左前缀，比如联合索引
(a, b(50))，却只查
WHERE b = 'xxx'
前缀长度选 10、50 还是 191？怎么测
不能拍脑袋定。先用
COUNT(DISTINCT)算不同前缀的区分度：
SELECT COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) AS sel_10, COUNT(DISTINCT LEFT(email, 50)) / COUNT(*) AS sel_50, COUNT(DISTINCT LEFT(email, 191)) / COUNT(*) AS sel_191 FROM users;
目标是选一个最小长度，让选择性（
sel_x）接近全字段的选择性（通常 ≥ 0.95 即可）。但要注意： 区分度高 ≠ 查询快：过短前缀会导致大量重复值，优化器可能放弃使用该索引 utf8mb4 下中文占 3 字节，英文数字占 1 字节，按字节数算而非字符数——
LEFT(col, 50)在混合内容下实际覆盖字节数不稳定 如果字段常以相同前缀开头（比如一堆
user_123@xxx），再长的前缀也难提升区分度，此时应考虑哈希字段 + 普通索引
前缀索引不能用在哪些地方
前缀索引本质是“截断存储”，所以这些场景直接失效：
ORDER BY和
GROUP BY：无法按完整字段值排序或分组，MySQL 会回表或用 filesort
SELECT中直接引用该字段做计算或拼接：索引只存前缀，查不到完整值，必须回主键取数据 覆盖索引（
Using index）：只要
EXPLAIN的
Extra列出现
Using where; Using index就说明命中了覆盖，但前缀索引几乎不可能触发——因为索引里没有完整字段内容 唯一约束：
UNIQUE INDEX (email(191))允许不同完整值但前缀相同的记录插入，失去唯一性语义
真正要保唯一性，要么改用完整列索引（确保长度合规），要么加生成列：
ALTER TABLE users ADD COLUMN email_hash CHAR(32) AS (MD5(email)) STORED，再对
email_hash建唯一索引。
