Schema的优化和索引 - 高性能的索引策略 - 前缀索引和索引的选择性

核心提示： mysql> SELECT COUNT(*) AS cnt, city -> FROM sakila.city_demo GROUP BY city ORDER BY cnt DESC LIMIT 10;发现每个城市出现的次数范围为45-65.现在我们在查找频率出现最高的前缀，从3个

mysql> SELECT COUNT(*) AS cnt, city
　　 　-> FROM sakila.city_demo GROUP BY city ORDER BY cnt DESC LIMIT 10;

发现每个城市出现的次数范围为45-65.现在我们在查找频率出现最高的前缀。从3个字母的前缀开始。

+-----+------+
| cnt | pref |
+-----+------+
| 483 | San 　|
| 195 | Cha 　|
| 177 | Tan 　|
| 167 | Sou 　|
| 163 | al- 　|
| 163 | Sal 　|
| 146 | Shi 　|
| 136 | Hal 　|
| 130 | Val 　|
| 129 | Bat 　|
+-----+------+

每个前缀都出现了很多次。因此有太多的唯一前缀要多于唯一全长度的city names.接下来的方法就是提高前缀的长度直到这个前缀的选择性接近于全长度的列。通过实验发现前缀长度为7最为合适。

mysql> SELECT COUNT(*) AS cnt, LEFT(city, 7) AS pref
　　 　-> FROM sakila.city_demo GROUP BY pref ORDER BY cnt DESC LIMIT 10;
+-----+---------+
| cnt | pref 　 　|
+-----+---------+
| 　70 | Santiag |
| 　68 | San Fel |
| 　65 | London 　|
| 　61 | Valle d |
| 　49 | Hiroshi |
| 　48 | Teboksa |
| 　48 | Pak Kre |
| 　48 | Yaound 　|
| 　47 | Tel Avi |
| 　47 | Shimoga |
+-----+---------+

另一种计算前缀的方法就是，计算整个列的选择性并且使前缀的选择性 接近于这个值。下面就是计算整列的选择性

mysql> SELECT COUNT(DISTINCT city)/COUNT(*) FROM sakila.city_demo;
+-------------------------------+
| COUNT(DISTINCT city)/COUNT(*) |
+-------------------------------+
| 　 　 　 　 　 　 　 　 　 　 　 　0.0312 |
+-------------------------------+