组合索引(concatenated index):由多个列构成的索引,如create index idx_emp on emp(col1, col2, col3, ……),则我们称idx_emp索引为组合索引。
在组合索引中有一个重要的概念:引导列(leading column),在上面的例子中,col1列为引导列。当我们进行查询时可以使用”where col1 = ? ”,也可以使用”where col1 = ? and col2 = ?”,这样的限制条件都会使用索引,但是”where col2 = ? ”查询就不会使用该索引。所以限制条件中包含先导列时,该限制条件才会使用该组合索引。
创建2张测试表:
mysql> desc test1;+-------+--------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+--------------+------+-----+---------+-------+| id | int(11) | NO | | NULL | || name | varchar(45) | YES | MUL | NULL | || dept | varchar(50) | YES | | NULL | || desc | varchar(100) | YES | | NULL | |+-------+--------------+------+-----+---------+-------+4 rows in set (0.00 sec)mysql> desc test2;+---------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+---------+-------------+------+-----+---------+-------+| id | int(11) | NO | PRI | NULL | || email | varchar(50) | YES | | NULL | || address | varchar(50) | YES | | NULL | |+---------+-------------+------+-----+---------+-------+3 rows in set (0.00 sec)mysql>
通过存储过程模拟一些数据:
delimiter $$create procedure mock_insert_test1()BEGIN DECLARE v int DEFAULT 1; WHILE v < 800000 DO INSERT into test1 VALUES(v,CONCAT("name",v),CONCAT("dept",v),NULL); SET v = v + 1; END WHILE;end$$delimiter ;delimiter $$create procedure mock_insert_test2()BEGINDECLARE v int DEFAULT 1;WHILE v < 800000DOINSERT into test2VALUES(v,CONCAT("email",v),CONCAT("addr",v));SET v = v + 1;END WHILE;end$$delimiter ;
执行存储过程灌一些数据,
mysql> SELECT * from test1 limit 10;+----+----------+-------+------+| id | name | dept | desc |+----+----------+-------+------+| 1 | duan | yanfa | NULL || 2 | zhangsan | renli | NULL || 3 | lisi | renli | NULL || 1 | name1 | dept1 | NULL || 2 | name2 | dept2 | NULL || 3 | name3 | dept3 | NULL || 4 | name4 | dept4 | NULL || 5 | name5 | dept5 | NULL || 6 | name6 | dept6 | NULL || 7 | name7 | dept7 | NULL |+----+----------+-------+------+10 rows in set (0.00 sec)mysql>
引导列是id时,索引是这样的:inx_id_name:id,name
创建索引后,
结果:
引导列是name时,索引是这样的:
结果:
为什么要以name为引导列?因为ID是join列,并且ID列上面没过滤条件,如果以ID列作为引导列,由于没过滤条件那么CBO只能走indexfullscan,或者indexfastfullscan,因为引导列没过滤条件,走不了indexrangescan, 最多走indexskipscan,不过indexskipscan代价过高,因为indexskipscan要求引导列选择性很低,但是ID这里选择性很高。
如果name列作为引导列,那么优化器就可以选择indexrangescan,这样相比indexfullscan,indexfastfullscan肯定要少扫描很多leafblock,逻辑读就会相对较少。
其实到这里,是否可以总结一下建立组合索引的原则总结:引导列要选择过滤条件的列作为引导列,比如wherea.xxx="xxx"或者a.xxx>或者a.xxx