MySQL索引对数据检索的性能至关重要,盲目的增加索引不仅不能带来性能的提升,反而会消耗更多的额外资源,本篇总结了一些MySQL索引实战经验.

索引是用于快速查找记录的一种数据结构.索引就像是数据库中数据的目录,数据库在查询时,首先在索引中找到匹配的值,然后根据这个匹配值找到对应的数据行.

名词解释

聚簇索引

聚簇索引的顺序便是数据的物理存储顺序,索引中数据域存储的便是实际的数据,一个表最多只能有一个聚簇索引,适用于查询多行数据,不适用于频繁修改的列,一般在主键上创建.

非聚簇索引

索引顺序与数据物理排列顺序无关,索引中存储的内容为实际数据的地址,适应于查询单行数据.

普通索引

即平时创建的普通索引.

唯一索引

索引所在的列或列组合的值是全表唯一的.

全文索引

MySQL从3.23.23版开始支持全文索引,它查找的是文中的关键词,而不是直接比拟索引中的值.

单列索引

在单列上创建的索引.

组合索引

在多个列上创建的索引.

最左前缀

where子句中有a、b、c三个查询条件,创建一个组合索引abc(a,b,c),最左前缀的概念是说以组合索引最左边的列a组合成的查询条件,如(a,b,c)、(a,b)、(a,c),这三种情况的查询条件都会使用abc索引,和where子句中a、b、c出现的顺序没关系,可以是where c=? and b=? and a=?,但(b,c)组合不会使用索引,即where c=? and b=?.

适合创建索引的列

1.经常作为查询条件的列；

2.经常作为排序条件的列；

3.经常作为join条件的列；

4.经常被查询的列.

不适合创建索引的列

1.数据频繁被修改的列,数据被修改,索引必要做相应的修改,消耗资源；

2.区分度不是很高的列,如性别,列值重复性太大,索引效果不是很明显；

3.不是经常被作为查询条件、排序条件、连接条件的列.

实战经验总结

1.列上进行函数计算将不会使用索引；

2.对于创建索引的列,避免存储NULL,NULL会使索引更加复杂、效率变低,可以使用NOT NULL进行约束；

3.对于模糊查询like '%abc%',将不会使用索引,而like 'abc%'将会使用索引；

4.对于not in、not exists、!=等负向查询将不会使用索引；

5.每次查询只使用一个索引,如果where条件使用了索引,order by将不再使用索引；

6.对于where子句中有多个查询条件的,单列索引的效率不如复合索引,因为查询每次只能使用一个索引；

7.MySQL只对以下操作符才使用索引：<、<=、=、>、>=、between、in,但是必要注意in的范围值不要太多；

8.union all可以使用索引,但自己效率不是很高,不建议使用；

9.列上进行类型转换的将不会使用索引；

10.老版本MySQL对OR条件不使用索引,新版本才支持,不建议使用OR.

关于索引的实战经验总结后续还会赓续更新.