MySQL之Prefix Index

作者：老王

细节决定结果！可惜人们往往无视细节的存在。很多人在给表加索引的时候，喜欢直接使用PHPMyAdmin操作，因为这样做操作简单，甚至不用记ALTER TABLE语法，用鼠标点一下就OK了，但是这样做的一个缺点是让使用者忽视了“索引长度”的存在，从而使用数据的完整长度去建立索引，这本身并没有什么逻辑错误，但是很多时候，我们并不需要使用数据的完整长度去建立索引，比如说有两个老王，一个是北京回龙观的老王，一个是上海徐家汇的老王。如果你按照数据的完整长度去建立索引以便定位老王的话，那么就要完整的索引北京回龙观和上海徐家汇这样的信息，但实际上在本例中回龙观，徐家汇这些地址信息是不必要的，只要提供一下城市信息是北京还是上海，就能定位要找的老王了，这也正是Prefix Index的含义所在，它的意义在于会大大缩减索引文件的大小，从而加快数据检索的速度。

在经典的《High Performance MySQL》里有现成的例子，我就直接拿过来用了：

CREATE TABLE sakila.city_demo(city VARCHAR(50) NOT NULL);

现在我们建立了一个测试表，如果要让测试效果明显点，至少得加入几百条数据，并让数据分布尽可能合理，这些就不提供了，大家可以自己找点实际数据测试，下面要给city字段建立索引，我们看看多长合适：

先看原始数据的汇总结果：

mysql> SELECT COUNT(*) AS cnt, city
    -> FROM sakila.city_demo GROUP BY city ORDER BY cnt DESC LIMIT 10;
65: London
49: Hiroshima
48: Teboksary
48: Pak Kret
48: Yaound
47: Tel Aviv-Jaffa
47: Shimoga
45: Cabuyao
45: Callao
45: Bislig

下面看看如果按照前三个字符去汇总的话结果会是怎样：

mysql> SELECT COUNT(*) AS cnt, LEFT(city, 3) AS pref
    -> FROM sakila.city_demo GROUP BY pref ORDER BY cnt DESC LIMIT 10;
483: San
195: Cha
177: Tan
167: Sou
163: al-
163: Sal
146: Shi
136: Hal
130: Val
129: Bat

可以发现按前三个字符去汇总的话，数据重复率太高了，而且选出来的东西和真实汇总结果差别太大，这时索引效率不会很好，那么按前七个字符做呢：

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

结果和真实的汇总数据比较接近了，所以说在本例中，7是比较合理的索引长度。

另一种判断合理索引长度的方法是通过数据的选择度情况来判断，先看原始数据的选择度：

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

再来计算一下不同长度的选择度：

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;
0.0239: sel3
0.0293: sel4
0.0305: sel5
0.0309: sel6
0.0310: sel7

从结果中可以看出，随着长度的增加越来越接近原始数据的选择度，但是4、5、6、7几个选择似乎都不错，哪个更合理呢？

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

可以发现数据重复率太高了，而且选出来的东西和真实汇总结果差别太大，所以4不是一个合适的选择。

把可能的选择都尝试一下，很容易就能确定合理的长度，假如说是7，剩下的就好办了，别用PHPMyAdmin了，ALTER TABLE的语法并不难记：

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

还可以通过核对cardinality来判断索引长度是否合理，这个参数在SHOW INDEX FROM ...的时候能看到。

还有一点需要注意，那就是生产环境里，你的数据分布情况是在不断变化的，所以合理的索引长度也可能是在不断变化的，为了适应变化，有时候你不得不时常调整索引，但是在MySQL里，ALTER TABLE属于剧烈运动，要小心对待，为了降低ALTER TABLE建立索引对在线服务的影响，可以采用主从服务器结构，具体的描述可以参考我以前的文章：MySQL主从服务器的一些技巧。