索引可以提高查询效率,但需要正确的索引策略才能发挥效果(测试中的示例数据库为mysql的sakila数据库,链接:http://dev.mysql.com/doc/sakila/en/)。
- 独立的列,索引不能是表达式的一部分。比如:
EXPLAIN SELECT * from sakila.actor WHERE actor_id=5;
在sakila.actor表中,actor_id是主键,有主键索引,索引exmplain出来结果就是:
可见这次查询使用了PRIMARY KEY来优化查询,如果变成这样:
EXPLAIN SELECT * from sakila.actor WHERE actor_id + 1=5;
结果就是:
可见没有在直接使用索引了,优化器预估的需要读取的行数也由1增长到了200 - 前缀索引。对于内容很长的列,比如blob, text或者很长的varchar列,必须使用前缀索引,mysql不允许索引这些列的完整长度。
所以问题就在于要选择合适长度的前缀,前缀太短,选择性太低,前缀太长,索引占用空间太大。
索引的选择性是指不重复的索引值(也叫基数)和数据表记录总数的比值,选择性越高查询效率越高,因为选择性高的索引可以让MySQL查找时过滤跟多的行,唯一索引选择性是 1,性能也是最好的。
要计算长度的选择性需要先知道完整列的选择性,以sakila.city_demo为例(制造方法见《高性能mySql》索引章节)
SELECT count(DISTINCT city)/COUNT(*) from sakila.city_demo;
结果是0.0312,直观的说sakila.city_demo的city列中一共有19200行数据,其中distinct(不重复)的数据有599列,所以选择性就是599/19200=0.0312,这个值就是最大的选择性。
那么,下一步就是列出不同索引长度对应的选择性,sql语句如下:
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
执行结果如下:
可以看到,到4以后,选择性提升就比较小了,但除了考虑选择性以外,还要考虑分布的均匀性,如果选择4,来看看它的均匀性:
SELECT count(*) as cnt, LEFT(city, 4) as pref from sakila.city_demo group by pref order by cnt desc limit 5;
运行结果:
可见分布不够均匀,综合考虑,选择7比较合适(前缀具体选择多少没有具体公式,根据选择性和均匀性自己决定),接着就可以建立前缀索引:
ALTER TABLE sakila.city_demo add KEY (city(7));