多个单列索引和一个复合索引的区别

mybatis代码:
<where>
    info.datastatus != 3
    <if test="_parameter.containsKey('province')">
        and geo.provinceName=#{province}
    </if>
    <if test="_parameter.containsKey('year')">
        and left(info.permitId,4)=#{year}
    </if>
    <if test="_parameter.containsKey('parameter')">
        and (info.leadOrg like '%${parameter}%' or info.productName like '%${parameter}%' or specItem like '%${parameter}%')
    </if>
</where>
像这种查询如何建立索引呢?是给 province,year,parameter分别建索引还是建一个这三列的复合索引呢?

建一个测试表,添加一个三列的复合索引,用procedure插入测试数据

CREATE TABLE `user_mulindex` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`number` varchar(32) DEFAULT NULL COMMENT '编号',
`name` varchar(32) DEFAULT NULL COMMENT '姓名',
`area_id` char(8) DEFAULT NULL COMMENT '区域ID',
`birthday` datetime DEFAULT NULL COMMENT '生日',
PRIMARY KEY (`id`),
KEY `index_three` (`number`,`name`,`area_id`)
) ENGINE=MyISAM AUTO_INCREMENT=23000012 DEFAULT CHARSET=utf8 COMMENT='用户';

再建一个测试表,添加三个单列索引并插入测试数据

CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`number` varchar(32) DEFAULT NULL COMMENT '编号',
`name` varchar(32) DEFAULT NULL COMMENT '姓名',
`area_id` char(8) DEFAULT NULL COMMENT '区域ID',
`birthday` datetime DEFAULT NULL COMMENT '生日',
PRIMARY KEY (`id`),
KEY `index_number` (`number`),
KEY `index_name` (`name`),
KEY `index_areaid` (`area_id`)
) ENGINE=MyISAM AUTO_INCREMENT=23000012 DEFAULT CHARSET=utf8 COMMENT='用户';

先看user_mulindex表的执行计划:

EXPLAIN select * from user_mulindex where number='num1' and name='name-1' and area_id='9999-108'

 

 

 

 

 

 三个条件都有时用到了复合索引且从索引长度看三个索引列都用到了

EXPLAIN select * from user_mulindex where number='num1' and name='name-1' 

 

 

 当只有前两个条件时也用到了复合索引,从长度看用到了前两个

explain select * from user_mulindex where   number='num1'

 

 

 当只有最左边的条件时也用到了复合索引,且只有一个

explain select * from user_mulindex where   name='name-1'

 

 

 EXPLAIN select * from user_mulindex where  name='name-1' and area_id='9999-108'

 

 

 

 由上面两个查询可以看出当查询条件 不合符最左前缀原则时不会走索引

最左前缀原则：只要你的查询语句涉及的字段满足已有辅助索引的左侧出现顺序（或者匹配字符串的左侧n个字符），而不出现越过某个字段的情况，查询就可以走这个辅助索引，这就是最左前缀原则.个人对最左前缀原则的理解:多个列的联合索引B+树建立的过程是根据列的顺序建立的,比如当前例子是按先保证number值有序前提下保证name有序,再保证area_id有序,在不确定number值的前提下根据name,area_id查数据,name,area的顺序是没意义的.

 

联合索引字段顺序
通过上面的分析，对于一个辅助索引（a, b）来说，不需要为a单独再建立索引，但可以再给b单独建立辅助索引（因为b为查询条件不满足辅助索引的最左前缀原则），那么思考一下，如果调整联合索引的顺序为（b, a），那么就不用单独为b建立辅助索引，而需要为a建立辅助索引。此时（a, b）、b方案与（b, a）、a方案都能满足对（a，b）、a、b三个字段的查询调用辅助索引，差别在于哪？

 

空间！这里比较好的方案是看a与b哪个字段长，则将其放在联合索引的前部，而需要额外建立辅助索引的用较短的字段，这样综合可以减少空间的使用（如果a字段长，则必有2a+b > 2b+a的空间使用）

 

同样 (column1,column2,column3)的复合索引,相当于建了column1,(column1,column2),(column1,colum2,column3)这三个索引

再看user表,这个表建了三个单列索引:

EXPLAIN select * from user where number='num1' 

 

EXPLAIN select * from user where number='num1' and name='name-1'

 

 EXPLAIN select * from user where number='num1' and name='name-1' and  area_id='9999-108'

 

 上面三个查询可以看出当有一个条件时就用了一个索引,有两个条件时possible_keys是两个,但key还是一个,三个条件时possible_keys是三个但key还是一个.

对比可以发现建立多个单列索引时即使查询条件用到了多个条件,最终也只会用到一个索引,建立一个复合索引时查询条件需要符合左前缀原则时才会用到复合索引.

所以前面mybatis的例子中如果能确定三个条件都会有,建立复合索引最好,如果不确定前面的条件是否有,可以建三个单列索引或者建复合索引以及对复合索引失效的情况再建索引