MySQL索引之组合索引

1.概念

 对于某些业务情况,当单列索引满足不了现实的业务,为了提高搜索效率,就需要考虑运用于组合索引.

 组合索引:即一个索引包含多个列;

2. 添加索引

ALTER TABLE people ADD INDEX `index_name` ( `column1`,`column2`,`column3`);

3.测试

建立基于departID,doctorID,scheduledTime的组合索引,执行计划如下:

4.最左前缀索引失效问题

之前创建的IDX_MBScheding_Group的组合索引,相当于创建了(departID,doctorCode,scheduledTime)、(departID,doctorCode)、(departID,scheduledTime)这些列组合上的索引.但是因为"mysql组合索引最左前缀"（Leftmost Prefixing）的结果.则不存在(doctorID,scheduledTime)等这样的组合索引.

最左前缀索引有效与无效列表：

//The following queries can use the Leftmost Prefixing index:
//where后过滤字段可不按该排序
select ID,ScheduledDate from TB_MB_Scheduling where departID = 309 and doctorCode = '0003' and ScheduledTime = 'SW'
select ID,ScheduledDate from TB_MB_Scheduling where departID = 309 and doctorCode = '0003' 
select ID,ScheduledDate from TB_MB_Scheduling where departID = 309 and ScheduledTime = 'SW'
select ID,ScheduledDate from TB_MB_Scheduling where departID = 309
//The following queries cannot use the Leftmost Prefixing index at all:
select ID,ScheduledDate from TB_MB_Scheduling where doctorCode = '0003' and ScheduledTime = 'SW'
select ID,ScheduledDate from TB_MB_Scheduling where doctorCode = '0003'
select ID,ScheduledDate from TB_MB_Scheduling where ScheduledTime = 'SW'

不存在(doctorID,scheduledTime)等这样的组合索引：

从执行计划可以看出最左前缀索引已经失效,直接进行全表扫描

- 那么问题来了,创建的索引顺序与where语句后顺序是否需要一致呢?

     在满足最左前缀索引情况下,查看where语句后字段不同的排序后的执行计划,发现:创建组合索引时字段的排序与where语句后顺序是不需要一致的.

总结:最左前缀是不是失效其实就是取决于你创建索引时,哪个字段排在首位,只要你的搜索条件包含有排在首位的那个字段就能走创建的索引,即满足最左前缀.

比如:基于(departID,doctorID,scheduledTime)创建的索引IDX_MBScheding_Group,只要where含有过滤字段departID=XXX,无论过滤字段顺序先后,都走索引IDX_MBScheding_Group.

- 那么问题又来了,怎么才能确定合适的索引列顺序呢?

5.选择合适的索引列顺序

正确的顺序依赖于使用该索引的查询,并且同时需要考虑如何更好的满足排序和分组的需要(本小节只适合B-Tree索引;哈希或者其他类型的索引并不会像B-Tree索引一样按顺序存储数据)

当不需要考虑排序和分组时,将选择性最高的列放在前面通常时很好的.这时候索引的作用只是用于优化where条件的查找.

以下面查询为例

select * from TB_MB_Scheduling where departID = 309 and doctorCode = '0003' and ScheduledTime = 'SW'

是应该创建一个(departID,doctorCode,scheduledTime)还是颠倒一下顺序?还是?

5.1 具体查询法

可以先跑一下来确定在这个表中值的分布情况,并确定哪个列的选择性更高.先查看各个where条件的分支对应的数据基数有多大:

所以应该把索引列doctorCode放在前面,因为对应的条件值doctorCode数量更小.然后根据doctorCode的条件值,查看对应的其他2列的选择性如何:

(注:查询结果根据你选定条件的具体值而定)

5.2 经验法

经验法则考虑的是全局基数和选择性.而不是具体的某个查询.

doctorCode的选择性更高,所以应该将其作为第一列.

       

参考来自:

1.<<高性能MySQL>>

2.http://blog.csdn.net/xtdhqdhq/article/details/17582779