表结构：
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%，那么优化器就不会用到索引，而是全部扫描。