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()
BEGIN
DECLARE v int DEFAULT 1;
WHILE v < 800000
DO
INSERT into test2
VALUES(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只能走 index full scan,或者index fast full scan，因为引导列没过滤条件，走不了index range scan,  最多走index skip scan，不过index skip scan代价过高，因为index skip scan要求 引导列选择性很低，但是ID这里选择性很高。

如果name列作为引导列，那么优化器就可以选择index range scan,这样相比index full scan, index fast full scan肯定要少扫描很多leaf block,逻辑读就会相对较少。 

 

其实到这里，是否可以总结一下建立组合索引的原则总结：

1. 引导列要选择过滤条件的列作为引导列，比如 where a.xxx='xxx' 或者 a.xxx> 或者 a.xxx<  
2. 引导列的选择性越高越好，因为选择性越高，扫描的leaf block就越少，效率就越高  (正确的顺序依赖于使用该索引的查询，并且同时需要考虑如何更好的满足排序和分组的需要。在一个多列B-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列。所以，索引可以按照升序或者降序进行扫描，以满足精确符合列顺序的ORDER BY、GROUP BY和DISTINCT等子句的查询需求。对于如何选择索引顺序有一个经验法则：将选择性较高的列放到索引的最前列。)
3. 尽量把join列放到组合索引最后面