mysql 联合索引测试

表数据 

mysql> select * from sys_user;
+----+----------+-----+-----+-------+
| id | username | sex | age | phone |
+----+----------+-----+-----+-------+
|  1 | zs       | ls  |  18 | 136   |
|  2 | ls       | nv  |  22 | 157   |
|  3 | 王五     | nan |  25 | 146   |
+----+----------+-----+-----+-------+

复合算一列  username, sex, age

mysql> explain select * from sys_user;
+----+-------------+----------+------+---------------+------+---------+------+------+-------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | sys_user | ALL  | NULL          | NULL | NULL    | NULL |    3 |       |
+----+-------------+----------+------+---------------+------+---------+------+------+-------+
1 row in set

单独使用 username 走所索引

mysql> explain select * from sys_user where username = 'zs';
+----+-------------+----------+------+---------------+------------+---------+-------+------+-------------+
| id | select_type | table    | type | possible_keys | key        | key_len | ref   | rows | Extra       |
+----+-------------+----------+------+---------------+------------+---------+-------+------+-------------+
|  1 | SIMPLE      | sys_user | ref  | user_index    | user_index | 768     | const |    1 | Using where |
+----+-------------+----------+------+---------------+------------+---------+-------+------+-------------+
1 row in set

使用 username 和 age 走索引

mysql> explain select * from sys_user where username = 'zs' and age='18
';
+----+-------------+----------+------+---------------+------------+---------+-------+------+-------------+
| id | select_type | table    | type | possible_keys | key        | key_len | ref   | rows | Extra       |
+----+-------------+----------+------+---------------+------------+---------+-------+------+-------------+
|  1 | SIMPLE      | sys_user | ref  | user_index    | user_index | 768     | const |    1 | Using where |
+----+-------------+----------+------+---------------+------------+---------+-------+------+-------------+
1 row in set

mysql> explain select * from sys_user where age='18' and username = 'zs'  ;
+----+-------------+----------+------+---------------+------------+---------+-------+------+-------------+
| id | select_type | table    | type | possible_keys | key        | key_len | ref   | rows | Extra       |
+----+-------------+----------+------+---------------+------------+---------+-------+------+-------------+
|  1 | SIMPLE      | sys_user | ref  | user_index    | user_index | 768     | const |    1 | Using where |
+----+-------------+----------+------+---------------+------------+---------+-------+------+-------------+

使用 username age sex 也走索引

mysql> explain select * from sys_user where username = 'zs' and age='18' and sex = 'ls
';
+----+-------------+----------+------+---------------+------------+---------+-------------------+------+-------------+
| id | select_type | table    | type | possible_keys | key        | key_len | ref               | rows | Extra       |
+----+-------------+----------+------+---------------+------------+---------+-------------------+------+-------------+
|  1 | SIMPLE      | sys_user | ref  | user_index    | user_index | 1031    | const,const,const |    1 | Using where |
+----+-------------+----------+------+---------------+------------+---------+-------------------+------+-------------+
1 row in set

mysql> explain select * from sys_user where  age='18' and sex = 'ls' and username = 'zs' 
;
+----+-------------+----------+------+---------------+------------+---------+-------------------+------+-------------+
| id | select_type | table    | type | possible_keys | key        | key_len | ref               | rows | Extra       |
+----+-------------+----------+------+---------------+------------+---------+-------------------+------+-------------+
|  1 | SIMPLE      | sys_user | ref  | user_index    | user_index | 1031    | const,const,const |    1 | Using where |
+----+-------------+----------+------+---------------+------------+---------+-------------------+------+-------------+
1 row in set

使用 age sex 不走索引

mysql> explain select * from sys_user where   age='18' and sex = 'ls';
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | sys_user | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
1 row in set

sex username 也走索引

mysql> explain select * from sys_user where  
 sex = 'ls' and username = 'zs' ;
+----+-------------+----------+------+---------------+------------+---------+-------------+------+-------------+
| id | select_type | table    | type | possible_keys | key        | key_len | ref         | rows | Extra       |
+----+-------------+----------+------+---------------+------------+---------+-------------+------+-------------+
|  1 | SIMPLE      | sys_user | ref  | user_index    | user_index | 1026    | const,const |    1 | Using where |
+----+-------------+----------+------+---------------+------------+---------+-------------+------+-------------+
1 row in set

单独 sex 不走索引

mysql> explain select * from sys_user where    
 sex = 'ls';
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | sys_user | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
1 row in set

单独age 不走索引

mysql> explain select * from sys_user where   age = '18
' ;
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | sys_user | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
1 row in set

 

总结 经过测试发现复合索引 建索引时最左侧字段必须有，否则 不走索引；