开启掘金成长之旅！这是我参与「掘金日新计划 · 12 月更文挑战」的第29天，点击查看活动详情
【MySQL进阶】关于前缀索引与索引选择性的探讨
之前我们说过，如于长字符串的字段（如 url），我们可以用伪哈希索引的形式来创建索引，以避免索引变得既大又慢，除此之外其实还可以用前缀索引（字符串的部分字符）的形式来达到我们的目的，那么这个前缀索引应该如何选取呢，这叫涉及到一个叫索引选择性的概念
索引选择性：不重复的索引值（也称为基数，cardinality）和数据表的记录总数的比值，比值越高，代表索引的选择性越好，唯一索引的选择性是最好的，比值是 1。
画外音：我们可以通过 SHOW INDEXES FROM table 来查看每个索引 cardinality 的值以评估索引设计的合理性
怎么选择这个比例呢，我们可以分别取前 3，4，5，6，7 的前缀索引，然后再比较下选择这几个前缀索引的选择性，执行以下语句
SELECT
COUNT(DISTINCT LEFT(city,3))/COUNT(*) as sel3,
COUNT(DISTINCT LEFT(city,4))/COUNT(*) as sel4,
COUNT(DISTINCT LEFT(city,5))/COUNT(*) as sel5,
COUNT(DISTINCT LEFT(city,6))/COUNT(*) as sel6,
COUNT(DISTINCT LEFT(city,7))/COUNT(*) as sel7
FROM city_demo
得结果如下
| sel3 | sel4 | sel5 | sel6 | sel7 |
|---|---|---|---|---|
| 0.0239 | 0.0293 | 0.0305 | 0.0309 | 0.0310 |
可以看到当前缀长度为 7 时，索引选择性提升的比例已经很小了，也就是说应该选择 city 的前六个字符作为前缀索引，如下
ALTER TABLE city_demo ADD KEY(city(6))
我们当前是以平均选择性为指标的，有时候这样是不够的，还得考虑最坏情况下的选择性，以这个 demo 为例，可能一些人看到选择 4，5 的前缀索引与选择 6，7 的选择性相差不大，那就得看下选择 4，5 的前缀索引分布是否均匀了
SELECT
COUNT(*) AS cnt,
LEFT(city, 4) AS pref
FROM city_demo GROUP BY pref ORDER BY cnt DESC LIMIT 5
可能会出现以下结果
| cnt | pref |
|---|---|
| 305 | Sant |
| 200 | Toul |
| 90 | Chic |
| 20 | Chan |
可以看到分布极不均匀，以 Sant，Toul 为前缀索引的数量极多，这两者的选择性都不是很理想，所以要选择前缀索引时也要考虑最差的选择性的情况。
前缀索引虽然能实现索引占用空间小且快的效果，但它也有明显的弱点，MySQL 无法使用前缀索引做 ORDER BY 和 GROUP BY ，而且也无法使用前缀索引做覆盖扫描，前缀索引也有可能增加扫描行数。
假设有以下表数据及要执行的 SQL
| id | |
|---|---|
| 1 | zhangssxyz@163.com |
| 2 | zhangs1@163.com |
| 3 | zhangs1@163.com |
| 4 | zhangs1@163.com |
SELECT id,email FROM user WHERE email='zhangssxyz@xxx.com';
如果我们针对 email 设置的是整个字段的索引，则上表中根据 「zhangssxyz@163.com」查询到相关记记录后,再查询此记录的下一条记录，发现没有，停止扫描，此时可知只扫描一行记录，如果我们以前六个字符（即 email(6)）作为前缀索引，则显然要扫描四行记录，并且获得行记录后不得不回到主键索引再判断 email 字段的值，所以使用前缀索引要评估它带来的这些开销。
另外有一种情况我们可能需要考虑一下，如果前缀基本都是相同的该怎么办，比如现在我们为某市的市民建立一个人口信息表，则这个市人口的身份证虽然不同，但身份证前面的几位数都是相同的，这种情况该怎么建立前缀索引呢。
一种方式就是我们上文说的，针对身份证建立哈希索引，另一种方式比较巧妙，将身份证倒序存储，查的时候可以按如下方式查询:
SELECT field_list FROM t WHERE id_card = reverse('input_id_card_string');
这样就可以用身份证的后六位作前缀索引了，是不是很巧妙 ^_^
实际上上文所述的索引选择性同样适用于联合索引的设计，如果没有特殊情况，我们一般建议在建立联合索引时，把选择性最高的列放在最前面，比如，对于以下语句：
SELECT * FROM payment WHERE staff_id = xxx AND customer_id = xxx;
单就这个语句而言， (staff_id，customer_id) 和 (customer_id, staff_id) 这两个联合索引我们应该建哪一个呢，可以统计下这两者的选择性。
SELECT
COUNT(DISTINCT staff_id)/COUNT(*) as staff_id_selectivity,
COUNT(DISTINCT customer_id)/COUNT(*) as customer_id_selectivity,
COUNT(*)
FROM payment
结果为: ;
staff_id_selectivity: 0.0001
customer_id_selectivity: 0.0373
COUNT(*): 16049
从中可以看出 customer_id 的选择性更高，所以应该选择 customer_id 作为第一列。
