mysql索引选择什么列_mysql索引之七：组合索引中选择合适的索引列顺序

组合索引(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