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)

用上索引。

在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会 被使用，并且应尽可能的让字段顺序与索引顺序相一致。