MySQL索引（三）：字符串索引优化之前缀索引

MySQL系列文章
在数据库优化中，字符串字段的索引设计往往是个棘手的问题。过长字符串的完整索引会占用大量空间，而不合适的索引又会导致查询性能低下。今天我们来探讨一个平衡的艺术——前缀索引。

字符串索引的现实挑战

假设我们正在开发一个内容管理平台，文章表结构如下：

1. CREATE TABLE articles (
2.     id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
3.     title VARCHAR(200) NOT NULL COMMENT '文章标题',
4.     content TEXT NOT NULL COMMENT '文章内容',
5.     author_id INT NOT NULL COMMENT '作者ID',
6.     created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
7.     KEY idx_title (title)
8. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

复制代码

随着数据量增长，我们面临一个问题：文章标题字段上的完整索引占据了大量存储空间，但大多数查询只需要匹配标题的前面部分：

1. -- 常见查询模式
2. SELECT id, title FROM articles WHERE title LIKE 'MySQL优化%';

复制代码

这种情况下，前缀索引就能发挥重要作用。
什么是前缀索引？

前缀索引允许只对字符串的前N个字符建立索引，而不是整个字符串。这与最左前缀原则密切相关：最左前缀原则指出索引可以用于匹配最左前缀的查询，而前缀索引正是这一原则在字符串字段上的具体应用。
创建语法对比

1. -- 完整索引
2. ALTER TABLE articles ADD INDEX idx_title_full (title);
4. -- 前缀索引（只索引前10个字符）
5. ALTER TABLE articles ADD INDEX idx_title_prefix (title(10));

复制代码

最左前缀原则与前缀索引的关系

最左前缀原则有两个层面的含义：

• 对于联合索引，可以匹配最左连续的N个字段
  
• 对于字符串索引，可以匹配最左的M个字符
  

前缀索引正是基于第二个层面的实现。它允许我们只索引字符串的最左部分字符，既能节省空间，又能支持基于前缀的查询。

上一讲已经具体介绍了最左前缀原则和索引其他的相关特性
MySQL索引（二）：覆盖索引、最左前缀原则与索引下推详解

前缀索引的工作原理

存储结构差异

完整索引存储整个字符串值，而前缀索引只存储前N个字符。这种差异带来了存储空间和查询效率的权衡。
查询过程分析

对于查询：

1. SELECT id, content, title FROM articles WHERE title = 'MySQL索引优化实战指南';

复制代码

使用完整索引：

• 在索引树中找到精确匹配的记录
  
• 直接获取对应的主键ID
  
• 回表查询获取完整数据
  

使用前缀索引(10)：

• 在索引树中找到前缀匹配'MySQL索引优化实'的记录
  
• 获取所有可能匹配的主键ID
  
• 回表查询完整数据行
  
• 逐行比对完整的标题是否匹配
  

如何选择合适的前缀长度？

选择合适的长度是关键：太短会导致区分度不足,额外增加太多回表的查询成本，太长则失去节省空间的意义。
计算不同前缀长度的区分度

1. -- 计算不同前缀长度的区分度占比
2. SELECT
3.     ROUND(COUNT(DISTINCT LEFT(title, 5)) * 100.0 / COUNT(DISTINCT title), 2) AS prefix_5_pct,
4.     ROUND(COUNT(DISTINCT LEFT(title, 10)) * 100.0 / COUNT(DISTINCT title), 2) AS prefix_10_pct,
5.     ROUND(COUNT(DISTINCT LEFT(title, 15)) * 100.0 / COUNT(DISTINCT title), 2) AS prefix_15_pct,
6.     ROUND(COUNT(DISTINCT LEFT(title, 20)) * 100.0 / COUNT(DISTINCT title), 2) AS prefix_20_pct
7. FROM articles;

复制代码

如果要保证前缀索引的查询效率接近完整索引，不额外增加太多回表的查询成本，通常会要求业务区分度达到95%以上。
实际选择策略

假设计算结果：

1. prefix_5_pct | prefix_10_pct | prefix_15_pct | prefix_20_pct
2. -----------------------------------------------------------
3.     45.67    |     82.34     |     96.78     |     99.12

复制代码

这种情况下，选择前缀长度15是最佳选择（96.78% > 95%）。
前缀索引的优缺点对比

优点缺点显著减少索引存储空间可能增加查询扫描次数提升写入性能无法使用覆盖索引提高缓存效率ORDER BY/GROUP BY可能失效支持前缀匹配查询需要仔细选择前缀长度实际应用场景

场景一：文章标题前缀索引

1. -- 分析标题字段的区分度
2. SELECT
3.     ROUND(COUNT(DISTINCT LEFT(title, 10)) * 100.0 / COUNT(DISTINCT title), 2) AS pct_10,
4.     ROUND(COUNT(DISTINCT LEFT(title, 15)) * 100.0 / COUNT(DISTINCT title), 2) AS pct_15,
5.     ROUND(COUNT(DISTINCT LEFT(title, 20)) * 100.0 / COUNT(DISTINCT title), 2) AS pct_20
6. FROM articles;
8. -- 创建合适的前缀索引
9. ALTER TABLE articles ADD INDEX idx_title_prefix (title(15));

复制代码

如果业务上都是像WHERE title LIKE 'MySQL优化%'这种短文字查询条件，一般对区分度要求不会太高。（根据项目具体业务选择）

场景二：长内容字段的前缀索引

对于内容搜索，可以建立前缀索引支持模糊查询：

1. -- 支持内容前缀搜索
2. ALTER TABLE articles ADD INDEX idx_content_prefix (content(50));
4. SELECT id, title FROM articles
5. WHERE content LIKE '在前端开发中%';

复制代码

前缀索引的局限性

无法使用覆盖索引

由于前缀索引只包含部分字符，无法完全满足覆盖索引的需求：

1. -- 即使查询只涉及索引完整字段，仍需回表
2. EXPLAIN SELECT title FROM articles WHERE title = 'MySQL优化指南';

复制代码

因为系统并不确定前缀索引的定义是否截断了完整信息。

排序和分组限制

前缀索引无法完全支持排序和分组操作：

1. -- 可能无法正确排序
2. SELECT title FROM articles ORDER BY title LIMIT 10;
4. -- 解决方案：对排序需求高的字段使用完整索引

复制代码

最佳实践建议

• 数据分析先行：在应用前缀索引前，必须分析数据的实际分布
  
• 95%原则：要前缀索引的查询效率接近完整索引，需确保前缀索引的区分度达到95%以上
  
• 业务导向：根据实际查询模式选择合适的前缀长度
  
• 监控调整：定期监控索引效果，随数据变化调整策略
  
• 混合策略：对重要字段可同时使用前缀索引和完整索引
  

性能实践对比

通过实际测试对比不同策略的性能：

1. -- 创建测试环境
2. CREATE TABLE article_test (
3.     id INT PRIMARY KEY AUTO_INCREMENT,
4.     title VARCHAR(200) NOT NULL,
5.     INDEX idx_full (title),
6.     INDEX idx_prefix_10 (title(10)),
7.     INDEX idx_prefix_15 (title(15))
8. );
10. -- 性能测试查询
11. EXPLAIN ANALYZE
12. SELECT id, title FROM article_test WHERE title = '深入理解MySQL索引优化';

复制代码

测试结果通常会显示：合适长度的前缀索引在存储空间和查询性能之间取得了最佳平衡。
结语

前缀索引是字符串字段优化的有效手段，它基于最左前缀原则，通过权衡存储空间和查询性能，为大数据量的字符串字段提供了实用的解决方案。在实际应用中，需要根据数据特性和业务需求精心设计，才能发挥其最大价值。
正确使用前缀索引，不仅能够节省存储空间，还能维持良好的查询性能，是每个数据库开发者都应该掌握的优化技巧。

来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！