MySQL索引选择及规则整理

sunren

　　索引选择性就是结果个数与总个数的比值。
　　用sql语句表示为：
　　SELECT COUNT(*) FROM table_name WHERE column_name/SELECT COUNT(*) FROM table_name
　　一般来说(例如书 “SQL Tuning“)，如果选择性超过 20% 那么全表扫描比使用索引性能更优。
　　但MySQL是没有计算索引的选择性的，只是预测逻辑IO操作的数量，因此对于MySQL索引要慎重选择。
　　举个栗子，tinyint类型的列，用以保存性别，就算用上“保密”和“变性”2项，选择性也最小也才25%，因此也就没有设置索引的必要了。
　　以下是整理出来的规则，以供参考：（由于很多实际项目都是查询操作占了绝大部分，因此不必太过呆板）
　　1、选择维度高的列
　　2、选择 where,on,group by,order by 中出现的列
　　3、选择较小的数据列，这样的索引文件更小，同时可装载更多的索引键
　　4、为较长的字符串使用前缀索引
　　5、组合索引能减低索引文件的大小，使用速度也优于多个单列索引
　　6、切勿滥用索引，因为除了磁盘空间的开销外，每次增删改都需要重新建立索引
　　7、索引不会包含有NULL值的列（若组合索引包含NULL值的列则整个组合索引无效）
　　另外，使用索引应当尽量避免 “OR” 、“否定查询” 、“模糊查询”、“NOT IN”、“<>” 等操作！
　　注意：前缀索引和组合索引是建立索引的一种技巧，并非索引类型。
　　组合索引
　　假设test表中有a，b，c三个列。
　　ALTER TABLE test ADD INDEX abc(a,b,c);
　　相当于分别建立了
　　a,b,c
　　a,b
　　a
　　这样的3组索引，也是“最左前缀”这个规则的结果。
　　举个使用该组合索引的栗子：
　　SELECT * FROM test WHERE a="1" AND b="2" SELECT * FROM test WHERE a="1"
　　以下则用不到索引：
　　SELECT * FROM test WHERE b="1" AND c="2" SELECT * FROM test WHERE c="1"
　　因此组合索引有一定优势，但在使用上需谨慎。
　　前缀索引
　　MySQL 前缀索引能有效减小索引文件的大小，提高索引的速度。但是前缀索引也有它的坏处：MySQL 不能在 ORDER BY 或 GROUP BY 中使用前缀索引，也不能把它们用作覆盖索引(Covering Index)。
　　语法如下：
　　ALTER TABLE table_name ADD KEY column_name(prefix_length);
　　建立前缀索引的关键在于"prefix_length"这个参数，并且前缀索引的选择性上也有一点特殊。
　　前缀索引的选择性公式为：SELECT COUNT(DISTINCT column_name)/COUNT(*) FROM table_name
　　继续举栗子！
　　现在有个user表，列 family_name varchar(50) 保存的是英文姓氏（我也想用中文姓名来举例，但是不大适合，看下去就明白了。。。）
　　要取得设置前缀索引最理想的"prefix_length"，我们首先要取得整列的选择性，如下：
　　SELECT COUNT(DISTINCT family_name)/COUNT(*) FROM user;
　　假设这里得到值是0.188。
　　然后我们继续去看看该列前1个字符的选择性又是多少
　　SELECT COUNT(DISTINCT LEFT(family_name,1))/COUNT(*) FROM user;
　　假设这里得到的结果是0.532，和整列的选择性出入太大，不可取，继续：
　　SELECT COUNT(DISTINCT LEFT(family_name,2))/COUNT(*) FROM user;
　　SELECT COUNT(DISTINCT LEFT(family_name,3))/COUNT(*) FROM user;
　　...
　　假设直接到“prefix_length”为5时，得到的值为0.189，非常接近！
　　而取6时得到的值为0.18891，这个选择性和5并没有太大的偏差。
　　再结合减少索引文件大小的这个思路
　　“prefix_length”值设置为5才是此处设置前缀索引的最优方案！
　　选择性讲完，还得再讲清楚这个前缀索引该怎么用！
　　书接上面的栗子~
　　正确的用法如下：
　　SELECT * FROM user WHERE family_name LIKE "lee%";
　　SELECT * FROM user WHERE family_name LIKE "david%";
　　以下则用不上该索引：
　　SELECT * FROM user WHERE family_name LIKE "_ee%";
　　SELECT * FROM user WHERE family_name LIKE "%en%";
　　SELECT * FROM user WHERE family_name LIKE "%ar%";
　　注意：SQL的模式缺省是忽略大小写的！
　　另外，“_”代表一个字符，“%”代表任意多个字符！
　　PS：如存在错误或不恰当之处，请不吝指正！