mysql> desc test2;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| tid | int(11) | YES | MUL | NULL | |
| name | varchar(10) | YES | | NULL | |
| age | int(3) | YES | | NULL | |
| address | varchar(200) | YES | | NULL | |
| jointime | datetime | YES | MUL | NULL | |
+----------+--------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
tid（tid,name,age,address）组合索引。jointime单列索引。
mysql> show index from test2;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test2 | 0 | PRIMARY | 1 | id | A | 10 | NULL | NULL | | BTREE | | |
| test2 | 1 | tid | 1 | tid | A | 10 | NULL | NULL | YES | BTREE | | |
| test2 | 1 | tid | 2 | name | A | 10 | NULL | NULL | YES | BTREE | | |
| test2 | 1 | tid | 3 | age | A | 10 | NULL | NULL | YES | BTREE | | |
| test2 | 1 | tid | 4 | address | A | 10 | NULL | NULL | YES | BTREE | | |
| test2 | 1 | jointime | 1 | jointime | A | 10 | NULL | NULL | YES | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
6 rows in set (0.00 sec)
mysql> explain select name from test2 where tid=5 and name='ee' and age=13 and address='hb' order by jointime;
+----+-------------+-------+------+---------------+------+---------+-------------------------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------------------------+------+-----------------------------+
| 1 | SIMPLE | test2 | ref | tid | tid | 226 | const,const,const,const | 1 | Using where; Using filesort |
+----+-------------+-------+------+---------------+------+---------+-------------------------+------+-----------------------------+
1 row in set (0.00 sec)
没有用上索引。
=========================================================================================================================================
tid（tid,name,age,address,jointime）组合索引。
mysql> show index from test2;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test2 | 0 | PRIMARY | 1 | id | A | 10 | NULL | NULL | | BTREE | | |
| test2 | 1 | tid | 1 | tid | A | 10 | NULL | NULL | YES | BTREE | | |
| test2 | 1 | tid | 2 | name | A | 10 | NULL | NULL | YES | BTREE | | |
| test2 | 1 | tid | 3 | age | A | 10 | NULL | NULL | YES | BTREE | | |
| test2 | 1 | tid | 4 | address | A | 10 | NULL | NULL | YES | BTREE | | |
| test2 | 1 | tid | 5 | jointime | A | 10 | NULL | NULL | YES | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
6 rows in set (0.00 sec)
mysql> explain select name from test2 where tid=5 and name='ee' and age=13 and address='hb' order by jointime;
+----+-------------+-------+------+---------------+------+---------+-------------------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------------------------+------+--------------------------+
| 1 | SIMPLE | test2 | ref | tid | tid | 226 | const,const,const,const | 1 | Using where; Using index |
+----+-------------+-------+------+---------------+------+---------+-------------------------+------+--------------------------+
1 row in set (0.00 sec)
用上索引。
=========================================================================================================================================
字段顺序不一致：
mysql> explain select name from test2 where tid=5 order by age;
+----+-------------+-------+------+---------------+------+---------+-------+------+------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------+------+------------------------------------------+
| 1 | SIMPLE | test2 | ref | tid | tid | 5 | const | 1 | Using where; Using index; Using filesort |
+----+-------------+-------+------+---------------+------+---------+-------+------+------------------------------------------+
1 row in set (0.00 sec)
没有用上索引。
mysql> explain select name from test2 where tid=5 order by name;
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
| 1 | SIMPLE | test2 | ref | tid | tid | 5 | const | 1 | Using where; Using index |
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)
用上索引。
在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会 被使用，并且应尽可能的让字段顺序与索引顺序相一致。
本文转自 liang3391 51CTO博客，原文链接:http://blog.51cto.com/liang3391/830066