mysql联合索引的选择性

联合索引就是同时对表中多个列进行索引，联合索引会依次根据联合索引中的列排序存储。

下面通过下面的实验来探讨下联合索引的使用选择性：

版本：percona 5.6.27

create table test(

a int,

b int,

c int,

name varchar(32),

PRIMARY key(a),

key index_a_b_c(a,b,c)) ENGINE=INNODB

insert into test values(1,1,1,3,'leo');

insert into test values(2,1,2,1,'mike' );

insert into test values(3,1,3,1,'exo' );

insert into test values(4,1,2,3,'jhon' );

insert into test values(5,1,1,3,'lucy' );

insert into test values(6,2,2,3,'leo' );

insert into test values(7,3,1,2,'dv' );

insert into test values(8,2,1,3,'men' );

一：where条件对联合索引的选择性

mysql> explain select * from test where a=2;

+----+-------------+-------+------+---------------+-------------+---------+-------+------+-------+

| id | select_type | table | type | possible_keys | key         | key_len | ref   | rows | Extra |

+----+-------------+-------+------+---------------+-------------+---------+-------+------+-------+

|  1 | SIMPLE      | test  | ref  | index_a_b_c   | index_a_b_c | 5       | const |    2 | NULL  |

+----+-------------+-------+------+---------------+-------------+---------+-------+------+-------+

mysql> explain select * from test where a=2 and b=1;

+----+-------------+-------+------+---------------+-------------+---------+-------------+------+-------+

| id | select_type | table | type | possible_keys | key         | key_len | ref         | rows | Extra |

+----+-------------+-------+------+---------------+-------------+---------+-------------+------+-------+

|  1 | SIMPLE      | test  | ref  | index_a_b_c   | index_a_b_c | 10      | const,const |    1 | NULL  |

+----+-------------+-------+------+---------------+-------------+---------+-------------+------+-------+

mysql> explain select * from test where a=2 and b=2 and c=3;

+----+-------------+-------+------+---------------+-------------+---------+-------------------+------+-------+

| id | select_type | table | type | possible_keys | key         | key_len | ref               | rows | Extra |

+----+-------------+-------+------+---------------+-------------+---------+-------------------+------+-------+

|  1 | SIMPLE      | test  | ref  | index_a_b_c   | index_a_b_c | 15      | const,const,const |    1 | NULL  |

+----+-------------+-------+------+---------------+-------------+---------+-------------------+------+-------+

这三个是正常的使用方法,都走了索引

mysql> explain select * from test where a=2 and c=3;

+----+-------------+-------+------+---------------+-------------+---------+-------+------+-----------------------+

| id | select_type | table | type | possible_keys | key         | key_len | ref   | rows | Extra                 |

+----+-------------+-------+------+---------------+-------------+---------+-------+------+-----------------------+

|  1 | SIMPLE      | test  | ref  | index_a_b_c   | index_a_b_c | 5       | const |    2 | Using index condition |

+----+-------------+-------+------+---------------+-------------+---------+-------+------+-----------------------+

1 row in set (0.00 sec)

如果把b漏掉，同样走了索引

mysql> explain select * from test where b=2 and c=3;

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

|  1 | SIMPLE      | test  | ALL  | NULL          | NULL | NULL    | NULL |    8 | Using where |

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

1 row in set (0.00 sec)

mysql> explain select * from test where c=3;

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

|  1 | SIMPLE      | test  | ALL  | NULL          | NULL | NULL    | NULL |    8 | Using where |

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

如果把a漏掉，则不会走索引

结论：必须有联合索引的第一个字段作为wehre条件

二：联合索引排序选择性

联合索引的排序会按照（a,b,c）的顺序进行排序

测试数据在联合索引的排序会是(1,1,3), (1,2,1), (1,2,3), (1,3,1), (2,1,3), (2,2,3), (3,1,2)顺序存储

mysql> explain select * from test where a=2 order by b;

+----+-------------+-------+------+---------------+-------------+---------+-------+------+-------------+

| id | select_type | table | type | possible_keys | key         | key_len | ref   | rows | Extra       |

+----+-------------+-------+------+---------------+-------------+---------+-------+------+-------------+

|  1 | SIMPLE      | test  | ref  | index_a_b_c   | index_a_b_c | 5       | const |    2 | Using where |

+----+-------------+-------+------+---------------+-------------+---------+-------+------+-------------+

mysql> explain select * from test where a=2 order by c;

+----+-------------+-------+------+---------------+-------------+---------+-------+------+-----------------------------+

| id | select_type | table | type | possible_keys | key         | key_len | ref   | rows | Extra                       |

+----+-------------+-------+------+---------------+-------------+---------+-------+------+-----------------------------+

|  1 | SIMPLE      | test  | ref  | index_a_b_c   | index_a_b_c | 5       | const |    2 | Using where; Using filesort |

+----+-------------+-------+------+---------------+-------------+---------+-------+------+-----------------------------+

可以看出第二个Using filesort使用了临时表排序了，效率低。从联合索引的排序就可以知道当指定a的值的时候，这些值会按b的值排序，不是按c的值排序，故order by b不用再filesort排序，反之order by b需要重新排序。

所以select * from test where a=2 and b=2 order by c;不会 filesort排序

mysql> explain select * from test where a=2 and b=2 order by c;

+----+-------------+-------+------+---------------+-------------+---------+-------------+------+-------------+

| id | select_type | table | type | possible_keys | key         | key_len | ref         | rows | Extra       |

+----+-------------+-------+------+---------------+-------------+---------+-------------+------+-------------+

|  1 | SIMPLE      | test  | ref  | index_a_b_c   | index_a_b_c | 10      | const,const |    1 | Using where |

+----+-------------+-------+------+---------------+-------------+---------+-------------+------+-------------+

结论：当针对联合索引中的某个字段进行排序的时候，最优的方法是有联合索引排序字段之前的字段过滤条件

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29989552/viewspace-2132647/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29989552/viewspace-2132647/