数据库索引优化策略

 

1、为什么要建立索引？

一句话，为了加快查询效率。注意这里的“查询”，而不是增删改。建立索引的列，一旦发生了增加、更新或删除操作，索引是需要维护的，此外不宜建立大量的索引，索引也占用磁盘空间。建立索引，应该权衡（查询）与（磁盘占用、维护索引）两者的代价，从而使整个数据库的性能最优。

 

2、联合索引和前缀索引

2.1、联合索引（复合索引）

首先介绍一下联合索引。联合索引其实很简单，相对于一般索引只有一个字段，联合索引可以为多个字段创建一个索引。它的原理也很简单，比如，我们在（a,b,c）字段上创建一个联合索引，则索引记录会首先按照A字段排序，然后再按照B字段排序然后再是C字段，因此，联合索引的特点就是：

• 第一个字段一定是有序的
• 当第一个字段值相等的时候，第二个字段又是有序的，比如下表中当A=2时所有B的值是有序排列的，依次类推，当同一个B值得所有C字段是有序排列的

| A | B | C | 

| 1 | 2 | 3 | 

| 1 | 4 | 2 | 

| 1 | 4 | 4 | 

| 2 | 3 | 5 | 

| 2 | 4 | 4 | 

| 2 | 4 | 6 | 

| 2 | 5 | 5 |

其实联合索引的查找就跟查字典是一样的，先根据第一个字母查，然后再根据第二个字母查，或者只根据第一个字母查，但是不能跳过第一个字母从第二个字母开始查。这就是所谓的最左前缀原理。

2.2、前缀索引

除了联合索引之外，对mysql来说其实还有一种前缀索引。前缀索引就是用列的前缀代替整个列作为索引key，当前缀长度合适时，可以做到既使得前缀索引的选择性接近全列索引，同时因为索引key变短而减少了索引文件的大小和维护开销。

一般来说以下情况可以使用前缀索引：

• 字符串列(varchar,char,text等)，需要进行全字段匹配或者前匹配。也就是=‘xxx’ 或者 like ‘xxx%’
• 字符串本身可能比较长，而且前几个字符就开始不相同。比如我们对中国人的姓名使用前缀索引就没啥意义，因为中国人名字都很短，另外对收件地址使用前缀索引也不是很实用，因为一方面收件地址一般都是以XX省开头，也就是说前几个字符都是差不多的，而且收件地址进行检索一般都是like ’%xxx%’，不会用到前匹配。相反对外国人的姓名可以使用前缀索引，因为其字符较长，而且前几个字符的选择性比较高。同样电子邮件也是一个可以使用前缀索引的字段。
• 前一半字符的索引选择性就已经接近于全字段的索引选择性。如果整个字段的长度为20，索引选择性为0.9，而我们对前10个字符建立前缀索引其选择性也只有0.5，那么我们需要继续加大前缀字符的长度，但是这个时候前缀索引的优势已经不明显，没有太大的建前缀索引的必要了。

一些文章中也提到：

MySQL 前缀索引能有效减小索引文件的大小，提高索引的速度。但是前缀索引也有它的坏处：MySQL 不能在 ORDER BY 或 GROUP BY 中使用前缀索引，也不能把它们用作覆盖索引(Covering Index)。

 

3、建立索引原则（优化策略）：

• 在经常查询的列上建立索引。
• 对 where,on,group by,order by 中出现的列使用索引
• 在需要进行join的字段上建立索引，哪些被join起来的字段，也应该是相同的类型。
• 为主键、外键上建立索引，可以确保主键的唯一性。
• 为较长的字符串使用前缀索引
• 对多个列可以建立联合索引，查询语句需要满足最左前缀原则。
• 数据长度比较小的列上，可以建立索引，一来索引文件比较小，二来内存中可以装载更多的索引键。
• 尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，一般要求0.1以上。
• 索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);

• 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可
• 不要过多创建索引, 权衡索引个数与DML之间关系，DML也就是插入、删除数据操作。这里需要权衡一个问题，建立索引的目的是为了提高查询效率的，但建立的索引过多，会影响插入、删除数据的速度，因为我们修改的表数据，索引也需要进行调整重建

 

4、哪些情况避免使用索引：

• 以增删改为主的数据库，应该避免建立过多的索引。
• 区分度比较小的列，或者是查询基本用不到的列，应该避免建立索引。
• 表的记录太少的话，避免建立索引，此时建立索引与不建立索引的效果一致，而此时还要建立索引的话，会占用额外的空间。

 

5、哪些sql语句不会使用索引？

• where条件数据类型不匹配
• like语句中%出现在首位
• 数值类型进行不等操作。select name from book where sell_num> 100。
• 对索引字段进行表达式或者函数操作。select name from book where num/2=100。

可以将sql语句改为 select name from book where num=200

• 使用or进行连接。select name from book where autho ‘tom’ or author='jack'。应改为select name from book where author='tom' union all select name from book where author='jack'
• 进行null值判断。select name from book where translator(译者，varchar类型) is null。可以在插入书籍信息的时候，译者为null的话就存入0，之后使用select name from book where translator='0'来查询译者为null的书籍名。
• 隐式类型转换。select name from book where version(版本，varchar类型)=1.0，这里是从varchar类型转化为浮点类型，不会走索引，应将1.0改为‘1.0’。
• 使用正则表达式。这应该很好理解,所以为什么在SQL中很难看到regexp关键字的原因
• 组合索引中的查询不满足最左前缀原则。如果我们对book表建立联合索引(a,b,c)，那么以下的语句是走索引的

select * from book where a='x' ;

select * from book where a='x' and b='y' ;

select * from book where a='x' and c='z ;

select * from book where a='x' and b='y and c='z' ;

而以下语句不走索引

select * from book where b='y' ;

select * from book where c='z' ;

select * from book where b='y' and c='z' ;