提高MySQL索引策略二:前缀索引和索引的可选择度

有的时候你需要对包含很长的文本数据进行索引,这不仅让索引占用非常大的空间,索引的检索效率也会非常低下,有一种策略是在该列上建立哈希类型的索引,但是有时候我们不想因为索引值的特殊性(长度)而增加它的维护成本,所以,我们有一种更通用的方式来解决上面的问题.

 

首先应该想到的是截断索引长度,通过对文本数据的前缀字符进行索引,达到不仅能节省空间,而且会使一部分查询变得快速,但是如果文本数据的可选择度很低,这种方式的性能提升就不是那么明显了.这里,索引的可选择度指的是索引列包含的唯一值数量与索引列所有数据行数的比例(T),一个高选择度的列索引会使MySQL在检索时过滤掉更多的数据,最好的一种情况是唯一索引,它的可选择度为1.

 

一般来讲,前缀索引都能提供不错的性能提升.如果对BLOB或者TEXT等可能会包含长文本数据的列进行索引,你应该定义一个前缀索引,MySQL默认会不会以文本的总长度进行索引.有个简单的办法就是选择足够长但是又能节省空间的索引长度来使可选择度的最大.一种简单的方式就是通过查询出不同指定长度的数据和所有数据的比例来进行比较,如果这种比例随着长度的增大而开始趋于平滑则选用其长度作为索引长度,如下sql

mysql> SELECT COUNT(DISTINCT LEFT(field, 3))/COUNT(*) AS sel3,
-> COUNT(DISTINCT LEFT(field, 4))/COUNT(*) AS sel4,
-> COUNT(DISTINCT LEFT(field, 5))/COUNT(*) AS sel5,
-> COUNT(DISTINCT LEFT(field, 6))/COUNT(*) AS sel6,
-> COUNT(DISTINCT LEFT(field, 7))/COUNT(*) AS sel7
-> FROM table;

 ,上面的方式虽然简单但是总有一些最坏情况,比如查询时总是查询出那些重复出现次数较大的数据,这样虽然平均值趋于平滑,但是索引在过滤数据时总是不能够去除这些重复度较高的索引值,所以,这种方式有时不能够得到一个很好的索引长度.

 

另一种获得较好的索引长度的方法是将不同长度的数据值以及它的出现次数,如下sql

mysql> SELECT COUNT(*) AS cnt, LEFT(field, 3) AS pref
-> FROM table GROUP BY pref ORDER BY cnt DESC LIMIT 10;

 对不同的长度进行查询,最后将结果和最长索引长度的查询结果进行比较,如果出现次数较高的若干个重复值和它相差不大,则可以选用其作为合适的索引长度