MySQL建立索引的注意事项

大家都知道对数据库表字段建立索引可以有效的提升数据的查询效率，但索引也不是随便建立的，需要我们对建立索引的规范有一定的了解，才能更好的发挥出索引的优势，避免因为滥建索引反而带来整体性能的下降。

1、哪些列上建立索引？
一般我们会建议在经常被查询的列上建立索引，但也需要注意如果该列更新比较频繁或者区分度不高（比如只有一个状态0/1的标识），那么就需要考虑是否值得建立索引了。

在更新比较频繁的列上建立索引，会降低更新时的效率，因为每次更新都需要对索引的存储进行维护。

一般经常被查询的列、排序的列、关联查询的列、分组的列考虑建立索引。

2、单列索引还是组合索引
如果单列和组合索引都可以实现的情况下，建议使用组合索引，因为一个单列索引就会建立一个b+tree的存储结构，太多单列索引会占用过多的空间。

使用组合索引时需要注意最左匹配原则，避免索引失效，区分度高的列放在最左边，组合数量不宜过多。

3、单表建立索引的数量
单表建立的索引数量建议控制在3-5个，建立太多的索引会占用过多的空间，同时也会造成插入、更新变慢，降低数据库的整体性能。

4、建立唯一索引
对唯一属性的列上建立唯一索引，可以更快的检索数据。

5、索引的长度
有时候我们并不需要把列中的所有值都作为索引列来存储，可以通过计算索引列的区分度，来控制索引的长度，减少索引占用的空间，使得一次IO能够读取更多的索引列到内存中，提高索引的查询效率。

区分度计算：
select count(distinct left(索引列,索引长度))/count(*) from table;

SELECT count(DISTINCT LEFT(order_no, 20)) / count(*) AS '20', count(DISTINCT LEFT(order_no, 22)) / count(*) AS '22', count(DISTINCT LEFT(order_no, 24)) / count(*) AS '24', count(DISTINCT LEFT(order_no, 26)) / count(*) AS '26', count(DISTINCT LEFT(order_no, 28)) / count(*) AS '28', count(DISTINCT LEFT(order_no, 30)) / count(*) AS '30', count(DISTINCT LEFT(order_no, 32)) / count(*) AS '32' FROM test;

order_no字段长度是32，可以看出从获取长度为26开始，区分度已经接近1，再增加长度性价比已经不高了。

举个例子解释一下：

比如我们对email列上建立索引，一般邮箱都是.com结尾的，那么后面这一部分我们就可以截取掉不作为索引列存储了，因为带不带这一部分对我们的检索并不造成影响。

假如有些重复值在前面，比如url一般都以http://www.存储，那么我们可以进行倒序存储，然后截取后面这一部分建立索引。

在有固定重复值的列上建立索引，建议设置索引长度。