MySQL索引学习笔记02——高性能的索引策略（前缀索引&索引选择性）

正确地创建和使用索引是实现高性能查询的基础，针对特殊案例，或针对特定行为的优化，选用不同的索引

1. 独立的列

独立的列是指索引列不能是表达式的一部分，也不能是函数的参数。如果查询中的列不是独立的，则MySQL就不会使用索引，如：SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;

这个查询无法使用actor_id列的索引，我们知道上面的actor_id=4，但MySQL无法自动解析这个方程式，我们应该有简化WHERE条件的习惯，始终将索引列单独放在比较符号的一侧

---

2. 前缀索引和索引选择性

当索引是很长的字符列时，这会让索引的效率变低，除了可以用模拟哈希索引,还可以使用索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。但这样会降低索引的选择性（索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也最高）

2.1 前缀索引

对于BLOB、TEXT或者很长的VARCHAR类型的列，必须使用前缀索引，要选择足够长的前缀以保证较高的选择性，同时又不能太长

2.1.1例如有一个城市表格

每个值都出现了45～65次。现在查找到最频繁出现的城市前缀，先从3个前缀字母开始：

每个前缀都比原来的城市出现的次数更多，因此唯一前缀比唯一城市要少得多。然后我们增加前缀长度，直到这个前缀的选择性接近完整列的选择性。经过实验后发现前缀长度为7时比较合适：

计算合适的前缀长度的另外一个办法就是计算完整列的选择性，并使前缀的选择性接近于完整列的选择性。下面显示如何计算完整列的选择性：

一般前缀的选择性能够接近0.031，基本上就可用了；下面给出了如何在同一个查询中计算不同前缀长度的选择性：

查询显示当前缀长度到达7的时候，再增加前缀长度，选择性提升的幅度已经很小了

2.1.2 创建前缀索引

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

2.1.3 前缀索引缺点：

MySQL无法使用前缀索引做ORDER BY和GROUP BY，也无法使用前缀索引做覆盖扫描

备注：有时候后缀索引（suffix index）也有用途（例如，找到某个域名的所有电子邮件地址）

---

该学习笔记学习自《高性能MySQL》／（美）Schwartz，（美）Zaitsev，（美）Tkachenko著 宁海元等译