MySQL 联合索引测试

浏览数：26 / 时间：2015年06月12日

搭建测试环境

1：创建表

CREATE TABLE tab_index
(id int(5),
age int(3),
dte datetime);

2：插入测试数据

INSERT INTO tab_index
VALUES(1,‘2012-05-13‘,23);
INSERT INTO tab_index
VALUES(2,‘2012-05-13‘,23);
INSERT INTO tab_index
VALUES(3,‘2012-05-13‘,31);
INSERT INTO tab_index
VALUES(4,‘2012-05-13‘,32);
INSERT INTO tab_index
VALUES(5,‘2012-05-13‘,33);
INSERT INTO tab_index
VALUES(6,‘2012-06-13‘,34);
INSERT INTO tab_index
VALUES(7,‘2012-07-13‘,35);
INSERT INTO tab_index
VALUES(8,‘2012-08-13‘,36);
INSERT INTO tab_index
VALUES(9,‘2012-09-13‘,37);
INSERT INTO tab_index
VALUES(10,‘2012-05-17‘,38);
INSERT INTO tab_index
VALUES(11,‘2012-05-19‘,39);
INSERT INTO tab_index
VALUES(1,‘2012-05-13‘,23);
INSERT INTO tab_index
VALUES(2,‘2012-05-13‘,23);
INSERT INTO tab_index
VALUES(3,‘2012-05-13‘,31);
INSERT INTO tab_index
VALUES(4,‘2012-05-13‘,32);
INSERT INTO tab_index
VALUES(5,‘2012-05-13‘,33);
INSERT INTO tab_index
VALUES(6,‘2012-06-13‘,34);
INSERT INTO tab_index
VALUES(7,‘2012-07-13‘,35);
INSERT INTO tab_index
VALUES(8,‘2012-08-13‘,36);
INSERT INTO tab_index
VALUES(9,‘2012-09-13‘,37);
INSERT INTO tab_index
VALUES(10,‘2012-05-17‘,38);
INSERT INTO tab_index
VALUES(11,‘2012-05-19‘,39);
INSERT INTO tab_index
VALUES(1,‘2012-05-13‘,23);
INSERT INTO tab_index
VALUES(2,‘2015-05-13‘,23);
INSERT INTO tab_index
VALUES(3,‘2012-05-13‘,31);
INSERT INTO tab_index
VALUES(4,‘2012-05-13‘,32);
INSERT INTO tab_index
VALUES(5,‘2012-05-13‘,33);
INSERT INTO tab_index
VALUES(6,‘2012-06-13‘,34);
INSERT INTO tab_index
VALUES(7,‘2013-07-13‘,35);
INSERT INTO tab_index
VALUES(8,‘2012-08-13‘,36);
INSERT INTO tab_index
VALUES(9,‘2012-09-13‘,37);
INSERT INTO tab_index
VALUES(10,‘2012-05-17‘,38);
INSERT INTO tab_index
VALUES(11,‘2011-05-19‘,39);
INSERT INTO tab_index
VALUES(1,‘2012-05-13‘,23);
INSERT INTO tab_index
VALUES(2,‘2012-05-13‘,23);
INSERT INTO tab_index
VALUES(3,‘2010-05-13‘,31);
INSERT INTO tab_index
VALUES(4,‘2012-05-13‘,32);
INSERT INTO tab_index
VALUES(5,‘2010-05-13‘,33);
INSERT INTO tab_index
VALUES(6,‘2010-06-13‘,34);
INSERT INTO tab_index
VALUES(7,‘2012-07-13‘,35);
INSERT INTO tab_index
VALUES(8,‘2012-08-13‘,36);
INSERT INTO tab_index
VALUES(9,‘2011-09-13‘,37);
INSERT INTO tab_index
VALUES(10,‘2012-05-17‘,38);
INSERT INTO tab_index
VALUES(11,‘2012-05-19‘,39);
INSERT INTO tab_index
VALUES(1,‘2012-05-13‘,23);
INSERT INTO tab_index
VALUES(2,‘2012-05-13‘,23);
INSERT INTO tab_index
VALUES(3,‘2012-05-13‘,31);
INSERT INTO tab_index
VALUES(4,‘2012-05-13‘,32);
INSERT INTO tab_index
VALUES(5,‘2012-05-13‘,33);
INSERT INTO tab_index
VALUES(6,‘2012-06-13‘,34);
INSERT INTO tab_index
VALUES(7,‘2014-07-13‘,35);
INSERT INTO tab_index
VALUES(8,‘2012-08-13‘,36);
INSERT INTO tab_index
VALUES(9,‘2011-09-13‘,37);
INSERT INTO tab_index
VALUES(10,‘2012-05-17‘,38);
INSERT INTO tab_index
VALUES(11,‘2012-05-19‘,39);

3：创建id和age上的联合索引

CREATE INDEX idx1
ON tab_index(id,age);

4：开始测试

mysql> EXPLAIN SELECT id,dte,age
    -> FROM tab_index
    -> WHERE id=3;
+----+-------------+-----------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref   | rows | Extra       |
+----+-------------+-----------+------+---------------+------+---------+-------+------+-------------+
|  1 | SIMPLE      | tab_index | ref  | idx1          | idx1 | 5       | const |    5 | Using where |
+----+-------------+-----------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)

 id=3走索引,age=31不走索引。很容易理解

mysql> EXPLAIN SELECT id,dte,age
    -> FROM tab_index
    -> WHERE age=31;
+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | tab_index | ALL  | NULL          | NULL | NULL    | NULL |   55 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT id,dte,age
    -> FROM tab_index
    -> WHERE id=3 AND age=31;
+----+-------------+-----------+------+---------------+------+---------+-------------+------+-------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref         | rows | Extra       |
+----+-------------+-----------+------+---------------+------+---------+-------------+------+-------------+
|  1 | SIMPLE      | tab_index | ref  | idx1          | idx1 | 10      | const,const |    1 | Using where |
+----+-------------+-----------+------+---------------+------+---------+-------------+------+-------------+
1 row in set (0.00 sec)

　id=3 AND age=31 和 age=31 AND id=3都走索引了，但是索引长度跟之前不同

mysql> EXPLAIN SELECT id,dte,age
    -> FROM tab_index
    -> WHERE age=31 AND id=3 ;
+----+-------------+-----------+------+---------------+------+---------+-------------+------+-------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref         | rows | Extra       |
+----+-------------+-----------+------+---------------+------+---------+-------------+------+-------------+
|  1 | SIMPLE      | tab_index | ref  | idx1          | idx1 | 10      | const,const |    1 | Using where |
+----+-------------+-----------+------+---------------+------+---------+-------------+------+-------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT id,dte,age
    -> FROM tab_index
    -> WHERE id=3 AND age=31 AND dte BETWEEN ‘2011-05-13 00:00:00‘ AND ‘2013-05-13 00:00:00‘;
+----+-------------+-----------+------+---------------+------+---------+-------------+------+-------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref         | rows | Extra       |
+----+-------------+-----------+------+---------------+------+---------+-------------+------+-------------+
|  1 | SIMPLE      | tab_index | ref  | idx1          | idx1 | 10      | const,const |    1 | Using where |
+----+-------------+-----------+------+---------------+------+---------+-------------+------+-------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT id,dte,age
    -> FROM tab_index
    -> WHERE id=3 AND dte BETWEEN ‘2011-05-13 00:00:00‘ AND ‘2013-05-13 00:00:00‘ AND age=31;
+----+-------------+-----------+------+---------------+------+---------+-------------+------+-------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref         | rows | Extra       |
+----+-------------+-----------+------+---------------+------+---------+-------------+------+-------------+
|  1 | SIMPLE      | tab_index | ref  | idx1          | idx1 | 10      | const,const |    1 | Using where |
+----+-------------+-----------+------+---------------+------+---------+-------------+------+-------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT id,dte,age
    -> FROM tab_index
    -> WHERE dte BETWEEN ‘2011-05-13 00:00:00‘ AND ‘2013-05-13 00:00:00‘ AND id=3 AND age=31;
+----+-------------+-----------+------+---------------+------+---------+-------------+------+-------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref         | rows | Extra       |
+----+-------------+-----------+------+---------------+------+---------+-------------+------+-------------+
|  1 | SIMPLE      | tab_index | ref  | idx1          | idx1 | 10      | const,const |    1 | Using where |
+----+-------------+-----------+------+---------------+------+---------+-------------+------+-------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT id,dte,age
    -> FROM tab_index
    -> WHERE age=31 AND id=3 AND dte BETWEEN ‘2011-05-13 00:00:00‘ AND ‘2013-05-13 00:00:00‘;
+----+-------------+-----------+------+---------------+------+---------+-------------+------+-------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref         | rows | Extra       |
+----+-------------+-----------+------+---------------+------+---------+-------------+------+-------------+
|  1 | SIMPLE      | tab_index | ref  | idx1          | idx1 | 10      | const,const |    1 | Using where |
+----+-------------+-----------+------+---------------+------+---------+-------------+------+-------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT id,dte,age
    -> FROM tab_index
    -> WHERE age=31 AND dte BETWEEN ‘2011-05-13 00:00:00‘ AND ‘2013-05-13 00:00:00‘ AND id=3 ;
+----+-------------+-----------+------+---------------+------+---------+-------------+------+-------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref         | rows | Extra       |
+----+-------------+-----------+------+---------------+------+---------+-------------+------+-------------+
|  1 | SIMPLE      | tab_index | ref  | idx1          | idx1 | 10      | const,const |    1 | Using where |
+----+-------------+-----------+------+---------------+------+---------+-------------+------+-------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT id,dte,age
    -> FROM tab_index
    -> WHERE dte BETWEEN ‘2011-05-13 00:00:00‘ AND ‘2013-05-13 00:00:00‘ AND age=31 AND id=3 ;
+----+-------------+-----------+------+---------------+------+---------+-------------+------+-------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref         | rows | Extra       |
+----+-------------+-----------+------+---------------+------+---------+-------------+------+-------------+
|  1 | SIMPLE      | tab_index | ref  | idx1          | idx1 | 10      | const,const |    1 | Using where |
+----+-------------+-----------+------+---------------+------+---------+-------------+------+-------------+
1 row in set (0.00 sec)

另外其他几种情况，同学们自己尝试下以加深印象。

总结如下：

(id)走索引，且索引长度最短
(id,dte)走索引，且索引长度最短

(id,age)走索引，且索引长度最长
(id,age,dte)走索引，且索引长度最长
(id,dte,age)走索引，且索引长度最长
(dte,id,age)走索引，且索引长度最长
(dte,age,id)走索引，且索引长度最长
(age,dte,id)走索引，且索引长度最长
(age,id,dte)走索引，且索引长度最长