单个索引与复合索引

单个索引与复合索引

在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。
如果我们创建了(username,sex,age)的复合索引，那么其实相当于创建了：
(username,sex,age)，(username,sex)、(username,age)、(username)四个索引，这被称为最佳左前缀特性。
因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减。
例：

select * from test where username='11'
select * from test where username='11' and sex=1
select * from test where username='11' and age=20
select * from test where username='11' and sex=1 and age=20

以上有索引。

select * from test where sex=11
select * from test where sex=1 and age=20

以上无索引。

添加单个索引

ALTER TABLE `tf_user_index` ADD INDEX(`username`);
ALTER TABLE `tf_user_index` ADD INDEX(`sex`);
ALTER TABLE `tf_user_index` ADD INDEX(`age`);

添加联合索引

ALTER TABLE `tf_user_index` ADD INDEX `username_sex_age` (`username`, `sex`, `age`);
ALTER TABLE `tf_user_index` ADD INDEX `sex_age` ( `sex`, `age`);

mysql> explain select count(*) from tf_user_index where sex='1';
+----+-------------+---------------+------+---------------+------+---------+-------+--------+-------------+
| id | select_type | table         | type | possible_keys | key  | key_len | ref   | rows   | Extra       |
+----+-------------+---------------+------+---------------+------+---------+-------+--------+-------------+
|  1 | SIMPLE      | tf_user_index | ref  | sex,sex_age   | sex  | 1       | const | 100071 | Using index |
+----+-------------+---------------+------+---------------+------+---------+-------+--------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from tf_user_index where username='user1';
+----+-------------+---------------+------+---------------------------+----------+---------+-------+------+-----------------------+
| id | select_type | table         | type | possible_keys             | key      | key_len | ref   | rows | Extra                 |
+----+-------------+---------------+------+---------------------------+----------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | tf_user_index | ref  | username,username_sex_age | username | 152     | const |    1 | Using index condition |
+----+-------------+---------------+------+---------------------------+----------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

mysql> explain select * from tf_user_index where age='11';
+----+-------------+---------------+------+---------------+------+---------+-------+------+-------+
| id | select_type | table         | type | possible_keys | key  | key_len | ref   | rows | Extra |
+----+-------------+---------------+------+---------------+------+---------+-------+------+-------+
|  1 | SIMPLE      | tf_user_index | ref  | age           | age  | 1       | const | 3911 | NULL  |
+----+-------------+---------------+------+---------------+------+---------+-------+------+-------+
1 row in set (0.00 sec)

mysql> explain select * from tf_user_index where username='user1' and sex='1';
+----+-------------+---------------+------+---------------------------------------+----------+---------+-------+------+------------------------------------+
| id | select_type | table         | type | possible_keys                         | key      | key_len | ref   | rows | Extra                              |
+----+-------------+---------------+------+---------------------------------------+----------+---------+-------+------+------------------------------------+
|  1 | SIMPLE      | tf_user_index | ref  | username,sex,username_sex_age,sex_age | username | 152     | const |    1 | Using index condition; Using where |
+----+-------------+---------------+------+---------------------------------------+----------+---------+-------+------+------------------------------------+
1 row in set (0.00 sec)

mysql> mysql> explain select * from tf_user_index where username='user1' and age='18';
+----+-------------+---------------+------+-------------------------------+----------+---------+-------+------+------------------------------------+
| id | select_type | table         | type | possible_keys                 | key      | key_len | ref   | rows | Extra                              |
+----+-------------+---------------+------+-------------------------------+----------+---------+-------+------+------------------------------------+
|  1 | SIMPLE      | tf_user_index | ref  | username,age,username_sex_age | username | 152     | const |    1 | Using index condition; Using where |
+----+-------------+---------------+------+-------------------------------+----------+---------+-------+------+------------------------------------+
1 row in set (0.00 sec)

mysql> explain select * from tf_user_index where username='user1' and sex='1'  and age='18';
+----+-------------+---------------+------+-------------------------------------------+----------+---------+-------+------+------------------------------------+
| id | select_type | table         | type | possible_keys                             | key      | key_len | ref   | rows | Extra                              |
+----+-------------+---------------+------+-------------------------------------------+----------+---------+-------+------+------------------------------------+
|  1 | SIMPLE      | tf_user_index | ref  | username,sex,age,username_sex_age,sex_age | username | 152     | const |    1 | Using index condition; Using where |
+----+-------------+---------------+------+-------------------------------------------+----------+---------+-------+------+------------------------------------+
1 row in set (0.00 sec)

mysql> explain select * from tf_user_index where  sex='1'  and age='18' and username='user1';
+----+-------------+---------------+------+-------------------------------------------+----------+---------+-------+------+------------------------------------+
| id | select_type | table         | type | possible_keys                             | key      | key_len | ref   | rows | Extra                              |
+----+-------------+---------------+------+-------------------------------------------+----------+---------+-------+------+------------------------------------+
|  1 | SIMPLE      | tf_user_index | ref  | username,sex,age,username_sex_age,sex_age | username | 152     | const |    1 | Using index condition; Using where |
+----+-------------+---------------+------+-------------------------------------------+----------+---------+-------+------+------------------------------------+
1 row in set (0.00 sec)

mysql> explain select * from tf_user_index where  sex='1'  and age='18';
+----+-------------+---------------+------+-----------------+---------+---------+-------------+------+-------+
| id | select_type | table         | type | possible_keys   | key     | key_len | ref         | rows | Extra |
+----+-------------+---------------+------+-----------------+---------+---------+-------------+------+-------+
|  1 | SIMPLE      | tf_user_index | ref  | sex,age,sex_age | sex_age | 2       | const,const | 1962 | NULL  |
+----+-------------+---------------+------+-----------------+---------+---------+-------------+------+-------+
1 row in set (0.00 sec)

mysql> explain select * from tf_user_index where  age='18' and sex='1';
+----+-------------+---------------+------+-----------------+---------+---------+-------------+------+-------+
| id | select_type | table         | type | possible_keys   | key     | key_len | ref         | rows | Extra |
+----+-------------+---------------+------+-----------------+---------+---------+-------------+------+-------+
|  1 | SIMPLE      | tf_user_index | ref  | sex,age,sex_age | sex_age | 2       | const,const | 1962 | NULL  |
+----+-------------+---------------+------+-----------------+---------+---------+-------------+------+-------+
1 row in set (0.01 sec)

mysql> explain select * from tf_user_index where username like '%user1' and sex='1'  and age='18';
+----+-------------+---------------+------+-----------------+---------+---------+-------------+------+-------------+
| id | select_type | table         | type | possible_keys   | key     | key_len | ref         | rows | Extra       |
+----+-------------+---------------+------+-----------------+---------+---------+-------------+------+-------------+
|  1 | SIMPLE      | tf_user_index | ref  | sex,age,sex_age | sex_age | 2       | const,const | 1962 | Using where |
+----+-------------+---------------+------+-----------------+---------+---------+-------------+------+-------------+
1 row in set (0.01 sec)

mysql> explain select * from tf_user_index where username like 'user1%' and sex='1'  and age='18';
+----+-------------+---------------+------+-------------------------------------------+---------+---------+-------------+------+-------------+
| id | select_type | table         | type | possible_keys                             | key     | key_len | ref         | rows | Extra       |
+----+-------------+---------------+------+-------------------------------------------+---------+---------+-------------+------+-------------+
|  1 | SIMPLE      | tf_user_index | ref  | username,sex,age,username_sex_age,sex_age | sex_age | 2       | const,const | 1962 | Using where |
+----+-------------+---------------+------+-------------------------------------------+---------+---------+-------------+------+-------------+
1 row in set (0.01 sec)

mysql> explain select * from tf_user_index where username like 'user1%' and sex like '1%'  and age='18';
+----+-------------+---------------+------+-------------------------------------------+------+---------+-------+------+-------------+
| id | select_type | table         | type | possible_keys                             | key  | key_len | ref   | rows | Extra       |
+----+-------------+---------------+------+-------------------------------------------+------+---------+-------+------+-------------+
|  1 | SIMPLE      | tf_user_index | ref  | username,sex,age,username_sex_age,sex_age | age  | 1       | const | 3896 | Using where |
+----+-------------+---------------+------+-------------------------------------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from tf_user_index where username like 'user1%' and sex like '1%'  and age like '18%';
+----+-------------+---------------+-------+-------------------------------------------+------------------+---------+------+--------+--------------------------+
| id | select_type | table         | type  | possible_keys                             | key              | key_len | ref  | rows   | Extra                    |
+----+-------------+---------------+-------+-------------------------------------------+------------------+---------+------+--------+--------------------------+
|  1 | SIMPLE      | tf_user_index | range | username,sex,age,username_sex_age,sex_age | username_sex_age | 152     | NULL | 100071 | Using where; Using index |
+----+-------------+---------------+-------+-------------------------------------------+------------------+---------+------+--------+--------------------------+
1 row in set (0.00 sec)

mysql> explain select * from tf_user_index where username='user1' and sex like '1%'  and age like '18%';
+----+-------------+---------------+------+-------------------------------------------+----------+---------+-------+------+------------------------------------+
| id | select_type | table         | type | possible_keys                             | key      | key_len | ref   | rows | Extra                              |
+----+-------------+---------------+------+-------------------------------------------+----------+---------+-------+------+------------------------------------+
|  1 | SIMPLE      | tf_user_index | ref  | username,sex,age,username_sex_age,sex_age | username | 152     | const |    1 | Using index condition; Using where |
+----+-------------+---------------+------+-------------------------------------------+----------+---------+-------+------+------------------------------------+
1 row in set (0.00 sec)

用了%号，索引就会失效。
经过测试，加索引，速度会快一些。

mysql> explain select * from tf_user_index where username='user1' or sex='2';
+----+-------------+---------------+-------------+---------------------------------------+--------------+---------+------+--------+----------------------------------------+
| id | select_type | table         | type        | possible_keys                         | key          | key_len | ref  | rows   | Extra                                  |
+----+-------------+---------------+-------------+---------------------------------------+--------------+---------+------+--------+----------------------------------------+
|  1 | SIMPLE      | tf_user_index | index_merge | username,sex,username_sex_age,sex_age | username,sex | 152,1   | NULL | 100072 | Using union(username,sex); Using where |
+----+-------------+---------------+-------------+---------------------------------------+--------------+---------+------+--------+----------------------------------------+
1 row in set (0.00 sec)

mysql> explain select * from tf_user_index where username='user1' or (sex='2' and age='18');
+----+-------------+---------------+-------------+-------------------------------------------+------------------+---------+------+------+--------------------------------------------+
| id | select_type | table         | type        | possible_keys                             | key              | key_len | ref  | rows | Extra                                      |
+----+-------------+---------------+-------------+-------------------------------------------+------------------+---------+------+------+--------------------------------------------+
|  1 | SIMPLE      | tf_user_index | index_merge | username,sex,age,username_sex_age,sex_age | username,sex_age | 152,2   | NULL | 1934 | Using union(username,sex_age); Using where |
+----+-------------+---------------+-------------+-------------------------------------------+------------------+---------+------+------+--------------------------------------------+
1 row in set (0.00 sec)

增加一个score字段之后。

mysql> explain select * from tf_user_index where username='user1' or (sex='2' and age='18' and score=60);
+----+-------------+---------------+-------------+-------------------------------------------+------------------+---------+------+------+--------------------------------------------+
| id | select_type | table         | type        | possible_keys                             | key              | key_len | ref  | rows | Extra                                      |
+----+-------------+---------------+-------------+-------------------------------------------+------------------+---------+------+------+--------------------------------------------+
|  1 | SIMPLE      | tf_user_index | index_merge | username,sex,age,username_sex_age,sex_age | username,sex_age | 152,2   | NULL | 1934 | Using union(username,sex_age); Using where |
+----+-------------+---------------+-------------+-------------------------------------------+------------------+---------+------+------+--------------------------------------------+
1 row in set (0.00 sec)