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

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

mysql> 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 sakila.city_demo;
+--------+--------+--------+--------+--------+
| sel3 　 | sel4 　 | sel5 　 | sel6 　 | sel7 　 |
+--------+--------+--------+--------+--------+
| 0.0239 | 0.0293 | 0.0305 | 0.0309 | 0.0310 |
+--------+--------+--------+--------+--------+

　　这个查询显示出，7字符的前缀长度最佳。

　　看平均的选择性也不是太好的主意。你也需要考虑最坏情况的选择性的时候。平均的选择性可能使你看到4，5长度的前缀也是不错的。但是如果你的数据是不均匀的。这可能就出现问题了。如果你查看最常见城市的前缀为4的出现数目，你就会清楚的发现不均匀的数据。

mysql> SELECT COUNT(*) AS cnt, LEFT(city, 4) AS pref
-> FROM sakila.city_demo GROUP BY pref ORDER BY cnt DESC LIMIT 5;
+-----+------+
| cnt | pref |
+-----+------+
| 205 | San |
| 200 | Sant |
| 135 | Sout |
| 104 | Chan |
| 91 | Toul |
+-----+------+

　　4个字符，出现前缀的频率比全部值的频率要高。因此选择性就要比平均选择性要低很多。如果你有真实的数据，你可能看到这种影响非常大。比如，你在真实的城市名称中创建4个字符前缀的索引，会带来非常差的选择性。

　　对于我们的示例数据，我们已经发现合适的值了。来看看怎样在这个列创建索引：

　　mysql> ALTER TABLE sakila.city_demo ADD KEY (city(7));

　　前缀的索引可以使索引更小并且更快，但是它也有明显的缺点。MySQL不能在ORDER BY或GROUP BY语句中使用前缀索引，也不能把它们作为覆盖索引进行使用。