在mysql的索引设计中，索引长度的选择和存储优化是直接影响数据库性能的重要环节。过长的索引会占用更多存储空间，同时降低索引的缓存效率，而过短的索引又可能导致区分度不足，无法有效过滤数据。掌握科学的索引长度选择方法和存储优化技巧，是数据库性能调优的必备能力。
一、mysql如何选择索引长度
1. 核心判断依据：索引区分度
索引长度的选择核心要看字段的区分度，也就是不同值的比例。区分度越高，索引过滤数据的效果越好，查询时扫描的行数就越少。我们可以通过计算字段不同前缀长度的区分度，来确定最优的索引长度。
计算区分度的SQL语句如下：
-- 计算user表的name字段不同前缀长度的区分度
-- 先获取表的总行数
SELECT COUNT(*) AS total_rows FROM user;
-- 分别计算前缀长度为5、10、15时的不同值数量
SELECT
COUNT(DISTINCT LEFT(name, 5)) / (SELECT COUNT(*) FROM user) AS prefix_5_ratio,
COUNT(DISTINCT LEFT(name, 10)) / (SELECT COUNT(*) FROM user) AS prefix_10_ratio,
COUNT(DISTINCT LEFT(name, 15)) / (SELECT COUNT(*) FROM user) AS prefix_15_ratio
FROM user;
一般来说，当某个前缀长度的区分度达到90%以上时，就可以作为合适的索引长度。比如上面的查询结果中，如果前缀长度为10时区分度已经达到95%，那么就没必要使用更长的索引。
2. 不同字段类型的索引长度选择
- 字符串类型：varchar、char类型的字段优先使用前缀索引，根据区分度计算结果选择最短的有效长度。比如邮箱字段通常前缀长度取10-15就能达到很高的区分度。
- 整数类型：int、bigint等整数类型如果不需要范围查询，可以考虑使用更小的整数类型，比如能用mediumint就不用int，减少索引存储占用。
- 时间类型：datetime类型如果只需要按日期查询，可以存储为date类型，或者使用函数转换为日期格式再建索引，减少索引长度。
3. 索引长度选择的注意事项
需要注意的是，前缀索引不支持覆盖索引扫描，因为前缀索引只存储了字段的前N个字符，无法获取完整的字段值。如果业务需要用到覆盖索引，那么就不能使用前缀索引，需要权衡存储和查询的需求。
另外，索引长度还会影响联合索引的使用效果，联合索引中靠前的字段如果长度过长，会导致整个联合索引的存储占用变大，需要合理调整每个字段的索引长度。
二、mysql索引存储优化技巧
1. 优先使用前缀索引减少存储
对于长字符串字段，前缀索引是最直接的存储优化方式。比如存储用户地址的字段，可能完整长度有100个字符，但实际前20个字符就能达到很高的区分度，那么创建前缀索引就能减少80%的存储占用。
创建前缀索引的SQL示例：
-- 给user表的address字段创建长度为20的前缀索引 CREATE INDEX idx_user_address ON user(address(20));
2. 选择合适的索引类型
mysql支持多种索引类型，不同的索引类型存储结构不同，适用场景也不同：
| 索引类型 | 存储特点 | 适用场景 |
|---|---|---|
| BTREE索引 | 默认索引类型，按树结构存储，支持范围查询 | 大部分常规查询场景 |
| HASH索引 | 基于哈希表存储，等值查询速度快，不支持范围查询 | 只做等值查询的场景 |
| 全文索引 | 专门存储文本内容的词项，支持全文检索 | 大文本字段的模糊查询场景 |
如果没有特殊需求，优先使用BTREE索引，避免不必要的HASH索引使用，因为HASH索引的适用场景比较有限，且不支持排序和范围查询。
3. 避免冗余索引和重复索引
冗余索引指的是一个索引是另一个索引的前缀，比如已经存在联合索引(a,b)，再创建索引(a)就是冗余索引，因为(a,b)已经可以支持a字段的单独查询。重复索引则是同一个字段创建了多个相同的索引，这两种情况都会浪费存储空间，还会降低写入性能。
我们可以通过查询information_schema.statistics表来排查冗余和重复索引：
-- 查询某个表的索引信息，排查冗余索引
SELECT
TABLE_NAME,
INDEX_NAME,
SEQ_IN_INDEX,
COLUMN_NAME,
CARDINALITY
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'your_database_name'
AND TABLE_NAME = 'your_table_name'
ORDER BY INDEX_NAME, SEQ_IN_INDEX;
4. 控制单表索引数量
单表的索引数量不宜过多，一般建议不超过5个。因为每次写入、更新、删除数据时，都需要同步维护所有相关的索引，索引越多，写入性能下降越明显。同时过多的索引也会占用更多的存储空间，影响缓冲池的利用率。
5. 利用覆盖索引减少回表
覆盖索引指的是索引包含了查询所需要的所有字段，不需要回到主表查询数据。覆盖索引可以避免回表操作，减少IO次数，同时也能减少索引的存储压力，因为不需要额外存储主表的数据页。
比如查询用户id和名称，我们可以创建联合索引(id,name)，这样查询时就可以直接从索引中获取数据：
-- 创建覆盖索引 CREATE INDEX idx_user_id_name ON user(id, name); -- 查询时直接走覆盖索引，不需要回表 SELECT id, name FROM user WHERE id > 100;
三、索引优化效果验证
在调整索引长度和存储优化之后，我们可以通过EXPLAIN命令来验证优化效果。主要关注key_len字段，这个字段表示使用的索引长度，数值越小说明索引存储占用越少；同时关注rows字段，数值越小说明扫描的行数越少，查询效率越高。
-- 使用EXPLAIN分析查询语句的索引使用情况 EXPLAIN SELECT * FROM user WHERE name LIKE '张%';
通过对比优化前后的key_len和rows数值，就能直观看到索引优化的效果。