单列索引和复合索引的使用

表结构：

mysql> desc car;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(3)      | NO   | PRI | NULL    | auto_increment |
| brand    | varchar(16) | YES  | MUL | NULL    |                |
| name     | varchar(30) | YES  | MUL | NULL    |                |
| emission | varchar(6)  | YES  | MUL | NULL    |                |
| country  | varchar(4)  | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

索引：

mysql> show index from car;
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| car   |          0 | PRIMARY     |            1 | id          | A         |          39 |     NULL | NULL   |      | BTREE      |         |               |
| car   |          1 | IX_brand    |            1 | brand       | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
| car   |          1 | IX_name     |            1 | name        | A         |          39 |     NULL | NULL   | YES  | BTREE      |         |               |
| car   |          1 | IX_emission |            1 | emission    | A         |          39 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)

三个字段的单列索引。

数据：

mysql> SELECT * FROM car;
+----+--------+-----------+----------+---------+
| id | brand  | name      | emission | country |
+----+--------+-----------+----------+---------+
|  1 | 大众   | 捷达      | 1.6L     | 德系    |
|  2 | 大众   | 桑塔纳    | 1.6L     | 德系    |
|  3 | 大众   | 桑塔纳    | 1.8L     | 德系    |
|  4 | 大众   | 桑塔纳    | 2.0L     | 德系    |
|  5 | 斯柯达 | 晶锐      | 1.6L     | 德系    |
|  6 | 斯柯达 | 晶锐      | 1.4L     | 德系    |
|  7 | 大众   | POLO      | 1.4L     | 德系    |
|  8 | 大众   | POLO      | 1.6L     | 德系    |
|  9 | 大众   | 新宝来    | 1.L      | 德系    |
| 10 | 大众   | 新宝来    | 1.6L     | 德系    |
| 11 | 大众   | 新宝来    | 1.4TSI   | 德系    |
| 12 | 大众   | 新速腾    | 1.4TSI   | 德系    |
| 13 | 大众   | 新速腾    | 1.6L     | 德系    |
| 14 | 大众   | 新速腾    | 1.8TSI   | 德系    |
| 15 | 大众   | 高尔夫GTI | 2.0TSI   | 德系    |
| 16 | 大众   | 高尔夫    | 1.6L     | 德系    |
| 17 | 大众   | 高尔夫    | 1.4TSI   | 德系    |
| 18 | 大众   | 朗逸      | 1.6L     | 德系    |
| 19 | 大众   | 朗逸      | 2.0L     | 德系    |
| 20 | 大众   | 朗逸      | 1.4TSI   | 德系    |
| 21 | 大众   | 帕萨特    | 1.8TSI   | 德系    |
| 22 | 大众   | 帕萨特    | 2.0L     | 德系    |
| 23 | 大众   | 帕萨特    | 2.8L     | 德系    |
| 24 | 大众   | 迈腾      | 1.8TSI   | 德系    |
| 25 | 大众   | 迈腾      | 1.4TSI   | 德系    |
| 26 | 大众   | 迈腾      | 2.0TSI   | 德系    |
| 27 | 斯柯达 | 明锐      | 2.0L     | 德系    |
| 28 | 斯柯达 | 明锐      | 1.6L     | 德系    |
| 29 | 斯柯达 | 明锐      | 1.4TSI   | 德系    |
| 30 | 斯柯达 | 明锐      | 1.8TSI   | 德系    |
| 31 | 大众   | 途观      | 1.8TSI   | 德系    |
| 32 | 大众   | 途观      | 1.4TSI   | 德系    |
| 33 | 大众   | 途观      | 2.0TSI   | 德系    |
| 34 | 斯柯达 | 昊锐      | 1.8TSI   | 德系    |
| 35 | 斯柯达 | 昊锐      | 2.0TSI   | 德系    |
| 36 | 斯柯达 | 昊锐      | 1.4TSI   | 德系    |
| 37 | 大众   | 辉腾      | 3.6L     | 德系    |
| 38 | 大众   | 辉腾      | 4.2L     | 德系    |
| 39 | 大众   | 辉腾      | 6.0L     | 德系    |
+----+--------+-----------+----------+---------+
39 rows in set (0.00 sec)

====================================================================

测试1：

mysql> explain SELECT * FROM car WHERE brand='大众' AND NAME='新速腾' ORDER BY emission DESC;
+----+-------------+-------+------+------------------+---------+---------+-------+------+-----------------------------+
| id | select_type | table | type | possible_keys    | key     | key_len | ref   | rows | Extra                       |
+----+-------------+-------+------+------------------+---------+---------+-------+------+-----------------------------+
|  1 | SIMPLE      | car   | ref  | IX_brand,IX_name | IX_name | 63      | const |    3 | Using where; Using filesort |
+----+-------------+-------+------+------------------+---------+---------+-------+------+-----------------------------+
1 row in set (0.00 sec)

虽然存在3个索引，但MYSQL只能使用一个索引，那个它认为最有效率的。

mysql> SELECT * FROM car WHERE brand='大众' AND NAME='新速腾' ORDER BY emission DESC;        
+----+-------+--------+----------+---------+
| id | brand | name   | emission | country |
+----+-------+--------+----------+---------+
| 14 | 大众  | 新速腾 | 1.8TSI   | 德系    |
| 13 | 大众  | 新速腾 | 1.6L     | 德系    |
| 12 | 大众  | 新速腾 | 1.4TSI   | 德系    |
+----+-------+--------+----------+---------+
3 rows in set (0.00 sec)

====================================================================

测试2：

复合索引（brand，name，emission）

mysql> show index from car;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| car   |          0 | PRIMARY  |            1 | id          | A         |          39 |     NULL | NULL   |      | BTREE      |         |               |
| car   |          1 | IX_b_n_e |            1 | brand       | A         |          39 |     NULL | NULL   | YES  | BTREE      |         |               |
| car   |          1 | IX_b_n_e |            2 | name        | A         |          39 |     NULL | NULL   | YES  | BTREE      |         |               |
| car   |          1 | IX_b_n_e |            3 | emission    | A         |          39 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)

mysql> explain SELECT * FROM car WHERE brand='大众' AND NAME='新速腾' ORDER BY emission DESC;
+----+-------------+-------+------+---------------+----------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys | key      | key_len | ref         | rows | Extra       |
+----+-------------+-------+------+---------------+----------+---------+-------------+------+-------------+
|  1 | SIMPLE      | car   | ref  | IX_b_n_e      | IX_b_n_e | 98      | const,const |    3 | Using where |
+----+-------------+-------+------+---------------+----------+---------+-------------+------+-------------+
1 row in set (0.00 sec)

用上索引。

====================================================================

测试3：

mysql> explain SELECT * FROM car WHERE NAME='新速腾' ORDER BY emission DESC;                        
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                       |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | car   | ALL  | NULL          | NULL | NULL    | NULL |   39 | Using where; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)

如果复合索引第一个字段没有用上，那么就不会使用到索引。

====================================================================

测试4：name和brand字段互换位置

mysql> explain SELECT * FROM car WHERE NAME='新速腾' and brand='大众' ORDER BY emission DESC;        
+----+-------------+-------+------+---------------+----------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys | key      | key_len | ref         | rows | Extra       |
+----+-------------+-------+------+---------------+----------+---------+-------------+------+-------------+
|  1 | SIMPLE      | car   | ref  | IX_b_n_e      | IX_b_n_e | 98      | const,const |    3 | Using where |
+----+-------------+-------+------+---------------+----------+---------+-------------+------+-------------+
1 row in set (0.00 sec)

虽然字段的顺序和索引不一致，但因为在过滤条件上使用了3个索引的字段，索引还是可以用上索引。

====================================================================

测试5：

mysql> explain SELECT * FROM car WHERE brand='斯柯达' ORDER BY emission;
+----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key      | key_len | ref   | rows | Extra                       |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------------+
|  1 | SIMPLE      | car   | ref  | IX_b_n_e      | IX_b_n_e | 35      | const |    9 | Using where; Using filesort |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------------+
1 row in set (0.00 sec)

字段的顺序和索引不一致，不能用到索引。因为只使用了2个索引的字段。

改成字段的顺序和索引的顺序一致，就可以用到索引了。

mysql> explain SELECT * FROM car WHERE brand='斯柯达' ORDER BY name;                 
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key      | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
|  1 | SIMPLE      | car   | ref  | IX_b_n_e      | IX_b_n_e | 35      | const |    9 | Using where |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
1 row in set (0.00 sec)

====================================================================

测试6：

mysql> explain SELECT * FROM car WHERE brand='大众' and name > '' ORDER BY emission DESC;
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                       |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | car   | ALL  | IX_b_n_e      | NULL | NULL    | NULL |   39 | Using where; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)

虽然是复合索引，虽然字段的顺序和索引的顺序一致，但name > ''，检索的记录要大约实际表记录的20%，那么优化器就不会用到索引，而是全部扫描。

本文转自 liang3391 51CTO博客，原文链接:http://blog.51cto.com/liang3391/830622