单个索引与联合索引

联合索引和单个索引的区别：

如果我们创建了(area, age,salary)的复合索引，那么其实相当于创建了：

(area,age,salary)，(area,age)、(area)三个索引，这被称为最佳左前缀

特性。因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减。

例：

select * from test where area='11'

select * from test where area='11' and age=1

select * from test where area='11' and age=1 and salary=2.0

以上有索引

select * from test where age=11

select * from test where age=1 and salary=2.0

以上无索引

如果在查询中需要匹配多个字段的条件，可以把这几个字段做个联合索引，效率要比在每个字段上加索引高多了

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)