MySQL对长字符串字段建全列索引会导致索引体积暴增，因InnoDB按最大可能长度（如VARCHAR(255)在utf8mb4下达1020字节）预估空间，降低单页存储密度、增加B+树深度；应通过选择性计算（COUNT(DISTINCT LEFT(col,N))/COUNT(*)>0.95）确定最优前缀长度，或用生成列存哈希/摘要等更紧凑方式建索引。
MySQL 中长字符串字段（比如 VARCHAR(255) 或 TEXT）建索引时，容易导致索引体积暴增，甚至超过数据本身。根本原因不是“不能建”，而是默认索引整个列值 —— 一个 255 字符的 UTF8MB4 字段，单个索引项可能占 1020 字节（255 × 4），B+ 树深度和页数量随之飙升。
为什么 CREATE INDEX 直接对长字段建索引会吃掉大量空间
InnoDB 的二级索引存储的是“索引列值 + 主键”，长字段全量索引意味着每条索引记录都携带完整字符串。更关键的是：innodb_page_size=16K 下，单页能存的索引行数锐减，B+ 树层级变深，不仅占空间，还拖慢查询。
- UTF8MB4 下，
VARCHAR(255)最大物理长度是 1020 字节，远超单个 B+ 树页内可高效管理的键长 - 即使字段实际内容很短（如邮箱前缀），只要定义长度大，InnoDB 在构建索引时仍按最大可能长度预估空间
-
SHOW INDEX FROM table_name查到的Sub_part列为NULL，说明是全列索引；若为数字（如10），才是前缀索引
用前缀索引替代全列索引：选多长才够用
前缀索引的核心是“用足够区分数据的前 N 个字符代替全量”。但 N 不是拍脑袋定的，得算选择性（selectivity）：即 COUNT(DISTINCT LEFT(col, N)) / COUNT(*)。理想值应 > 0.95。
MySQL8 CRM Schema Design Expert
下载
面向 CRM 系统的 MySQL 8 数据库模式设计。当用户需要为客户关系管理（CRM）系统设计、评审、优化或生成数据库模式时，应使用此项技能。
- 先试几个常见长度，比如 5、10、15、20，用下面 SQL 快速估算：
SELECT COUNT(*) AS total, COUNT(DISTINCT LEFT(email, 10)) AS prefix_10_uniq, ROUND(COUNT(DISTINCT LEFT(email, 10)) / COUNT(*), 4) AS sel_10 FROM users;
- 如果
sel_10已达 0.97，就没必要试 15；如果只有 0.82，就得加长再测 - 注意：不要在超大表上直接跑全表
COUNT(DISTINCT)，可先LIMIT 100000抽样，或用ANALYZE TABLE后查information_schema.STATISTICS - 前缀长度不是越长越好 —— 超过阈值后选择性提升极小，但索引体积线性增长
DROP INDEX 和 ADD INDEX 的顺序与风险
不能直接 ALTER INDEX 改前缀长度，必须删旧建新。这个过程会锁表（除非用 ALGORITHM=INPLACE 且满足条件），线上表务必避开高峰。
- 先确认原索引名：
SHOW INDEX FROM users WHERE Key_name = 'idx_email'; - 删除旧索引：
DROP INDEX idx_email ON users; - 创建前缀索引：
CREATE INDEX idx_email ON users (email(10)); - 验证是否生效：
SHOW INDEX FROM users看Sub_part是否变成10 - 特别注意：前缀索引不支持
ORDER BY email DESC或LIKE '%xxx'这类后缀匹配，只加速=和LIKE 'xxx%'
比前缀索引更省空间的替代方案
当字段语义明确（如邮箱、URL、哈希值），纯前缀可能仍不够紧凑。这时可主动控制索引键内容，而非依赖字段原始值。
- 加生成列（Generated Column）存哈希前缀：
ALTER TABLE users ADD COLUMN email_prefix CHAR(8) STORED AS (SUBSTR(SHA2(email, 256), 1, 8));，再对email_prefix建普通索引 - 对 URL 类字段，用
MD5(url)或SUBSTRING_INDEX(url, '/', 3)提取域名部分建索引，比全量url索引小一个数量级 - 如果业务允许，把长文本摘要逻辑移到应用层，数据库只索引确定性短标识（如 UUID、base32 编码的哈希）
真正难的不是算出那个“刚好够用”的前缀长度，而是意识到：索引字段的值，不一定要和业务字段完全一致 —— 可计算、可衍生、可截断，只要能支撑核心查询路径就行。
