mysql> show create table t\G
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`a` int(11) NOT NULL,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`a`),
KEY `ix_x` (`b`,`d`,`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
现在要执行SQL语句如下：
select a from t where b = 5 and d = 10 order by c;
假设我们有一个索引ix_x(b,d,c),通过explain得到如下输出：
mysql> explain select a from t where b = 5 and d = 10 order by c;
+----+-------------+-------+------+---------------+------+---------+-------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------------+------+--------------------------+
| 1 | SIMPLE | t | ref | ix_x | ix_x | 10 | const,const | 1 | Using where; Using index |
+----+-------------+-------+------+---------------+------+---------+-------------+------+--------------------------+
1 row in set (0.00 sec)
可以看到,查询语句使用了联合索引中的b和d两列来过滤数据。
如果我们定义的联合索引不是`ix_x(b,d,c)`,而是`ix_x(b, c, d)`,通过explain得到的输入如下：
mysql> alter table t drop index ix_x;
mysql> alter table t add index ix_x(b, c, d);
mysql> explain select a from t where b = 5 and d = 10 order by c;
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
| 1 | SIMPLE | t | ref | ix_x | ix_x | 5 | const | 2 | Using where; Using index |
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)
key_len为5,也就是说,只用到了联合索引中的第一列,可以看到,虽然联合索引包含了我们要查询的所有列,但是,由于定义的顺序问题,SQL语句并不能够充分利用索引。