学习笔记--mysql索引（三） 独立的列，前缀索引和索引的选择性

索引可以提高查询效率，但需要正确的索引策略才能发挥效果（测试中的示例数据库为mysql的sakila数据库，链接：http://dev.mysql.com/doc/sakila/en/）。

1. 独立的列，索引不能是表达式的一部分。比如：
   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
2. 前缀索引。对于内容很长的列，比如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));