MySQL创建索引需要遵循的原则

 

基本准则：

1、选择唯一性索引；

　　值是唯一的，例如学生表中的学号

2、为经常需要排序、分组和联合操作的字段建立索引；

　　经常需要order by、group by、distinct和union等操作的字段，排序操作费时。建立索引后可以有效避免排序操作。

3、为经常作为查询条件的字段建立索引；

　　如果某个字段经常作为查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建立索引，可以提供整个表的查询速度。

4、限制索引的数目；

　　索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。修改表时，对索引的重构和更新很麻烦。索引越多，更新表越费时。

5、尽量使用数据量少的索引；

　　如果索引的值很长，那么查询的速度会受到影响。

6、尽量使用前缀来索引；

　　如果索引字段的值很长，最好使用值的前缀来索引。

7、删除不再使用或很少使用的索引

 

其他原则：

1、最左匹配原则

　　MySQL会一直向右匹配，直到遇到范围查询（如<、>、between、like）就停止匹配。比如 a =1 and b = 2 and c > 3 and d = 4，如果建立（a,b,c,d）顺序的索引，d是用不到索引的，如果建立（a,b,d,c）顺序的索引，则都可以用到，且a，b，d的顺序可以任意调整。

2、= 和 in 可以乱序

　　比如 a = 1 and b = 2 and c =3，建立（a，b，c）索引，a，b，c可以任意顺序，MySQL的查询优化器会帮你优化成索引可以识别的形式。

3、尽量选择区分度高的列作为索引

　　区分度计算公式：count(distinct(col)) / count(*)，表示字段不重复的比例，比例越大，扫描的记录数越少。

4、索引列不能参与计算，保持“列干净”

　　比如 from_unixtime（create_time）= ‘2018-07-25’ 就不能用到索引，因为，B+树中存的是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，成本太大。语句应该写成 create_time = unix_timestamp（‘2018-07-25’）

5、尽量的扩展索引，不要新建索引

 

哪些字段适合建立索引？

表的主键、外键；值唯一；数据量小；区分度高；经常被用作查询条件；经常需要作排序、分组、联合操作的字段；

哪些字段不适合建立索引？

数据量大（如text类型字段）、区分度低，不经常被使用的字段；

 

参考：

http://book.51cto.com/art/201012/240955.htm

https://blog.csdn.net/u013412790/article/details/51612304