面试题：MySQL前缀索引对查询性能及索引选择性的平衡考量

使用SHOW INDEX查看现有索引信息
- 可以使用SHOW INDEX FROM your_table_name;语句查看表中已有的索引情况。这能帮助了解当前索引设置，为后续前缀索引设计做参考。例如，如果表articles存储文章内容，现有主键索引等，可通过该语句查看其细节。
计算不同前缀长度的选择性
- 选择性是指索引中不同值的数目与表中记录数的比率。比率越高，索引的选择性越好。
- 可以通过以下SQL计算不同前缀长度的选择性：
```
SELECT COUNT(DISTINCT LEFT(column_name, prefix_length)) / COUNT(*) AS selectivity
FROM your_table_name;
```
- 比如对于存储用户邮箱的email列，想尝试3、5、8等不同前缀长度，可以这样计算：
```
-- 前缀长度为3
SELECT COUNT(DISTINCT LEFT(email, 3)) / COUNT(*) AS selectivity
FROM users;
-- 前缀长度为5
SELECT COUNT(DISTINCT LEFT(email, 5)) / COUNT(*) AS selectivity
FROM users;
-- 前缀长度为8
SELECT COUNT(DISTINCT LEFT(email, 8)) / COUNT(*) AS selectivity
FROM users;
```
- 通过对比不同前缀长度的选择性，选择选择性较高且上升趋势开始变缓的前缀长度。
考虑实际查询场景
- 如果实际查询经常是以某个固定长度的前缀开始，比如在一个存储网址的表中，查询经常是针对特定域名下的网址（如https://www.example.com/开头），那么可以参考这个常用前缀长度来设置前缀索引长度。
- 例如在一个新闻文章表news_articles中，文章链接article_url列，若多数查询是针对特定几个新闻源网站（如https://www.xxxnews.com/）的文章，可根据这些新闻源网址的长度来确定合适前缀长度，既能覆盖主要查询场景，又能提升查询性能。
测试与性能评估
- 在开发或测试环境中，针对不同前缀长度的索引设置，执行实际的查询语句，并使用EXPLAIN关键字来分析查询执行计划。例如：
```
EXPLAIN SELECT * FROM your_table_name WHERE column_name LIKE 'prefix_value%';
```
- 通过EXPLAIN的输出结果，查看索引的使用情况、查询成本等指标。选择能使查询使用索引且查询成本较低的前缀长度。同时，也可以使用性能测试工具，模拟大量并发查询，评估不同前缀长度索引下系统的整体性能表现，最终确定合适的前缀长度。

星途面试题库

面试题：MySQL前缀索引对查询性能及索引选择性的平衡考量

知识考点

面试题答案