目前，有這麼一個SQL語句：
select * from t1 where a>? and b=?
可以肯定的是，必須在a和b字段上面創建索引，但是誰先誰後，可能很少有人會在意，認為都一樣，其實(a,b)和（b,a）還是不太一樣的，下面看看實驗。
表結構介紹：
MySQL> show create table t1;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
KEY `idx_b_a` (`b`,`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
表數據介紹：
MySQL> select * from t1;
+------+------+
| a | b |
+------+------+
| 127 | NULL |
| 3 | 1 |
| 2 | 3 |
| 3 | 4 |
| 2 | 6 |
| 3 | 6 |
| 7 | 8 |
+------+------+
7 rows in set (0.00 sec)
下面就4張查詢分別explain,仔細看看結果：
MySQL> explain select * from t1 where b>2 and a=3;
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
| 1 | SIMPLE | t1 | range | idx_b_a | idx_b_a | 5 | NULL | 3 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
MySQL> explain select * from t1 where b>2 and a>3;
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
| 1 | SIMPLE | t1 | range | idx_b_a | idx_b_a | 5 | NULL | 3 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
MySQL> explain select * from t1 where b=2 and a>3;
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
| 1 | SIMPLE | t1 | range | idx_b_a | idx_b_a | 10 | NULL | 1 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
MySQL> explain select * from t1 where b=2 and a=3;
+----+-------------+-------+------+---------------+---------+---------+-------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+---------+---------+-------------+------+--------------------------+
| 1 | SIMPLE | t1 | ref | idx_b_a | idx_b_a | 10 | const,const | 1 | Using where; Using index |
+----+-------------+-------+------+---------------+---------+---------+-------------+------+--------------------------+
1 row in set (0.00 sec)
通過上面explain的結果，我們可以很清晰地得出如下結論：
如果索引的第一字段在where條件中有該字段的等值條件，同時還有第二字段的其他條件（等值和非等值均含），該SQL語句可以完整地使用該索引，如果索引的第一字段在where條件中有該字段的非等值條件（如： > <等 ），同時還有第二字段的其他條件（等值和非等值均含），那麼只能使用該索引的前綴部分。所以以後在創建索引的時候，盡量把等值條件的字段作為第一索引字段，使之可以完整使用該索引。
