MySQL 联合索引测试2

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

接上一篇文章：

http://www.cnblogs.com/xiaoit/p/4430300.html

1：首先删掉上一篇建立的索引，重新建立一个。

mysql> DROP INDEX idx1 ON tab_index;
Query OK, 55 rows affected (0.01 sec)
Records: 55  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE tab_index
    -> ADD INDEX idx2(age,dte);
Query OK, 55 rows affected (0.01 sec)
Records: 55  Duplicates: 0  Warnings: 0

按照上一篇的测试结论应该是：

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

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

重新测试看下：

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 | ref  | idx2          | idx2 | 5       | const |    1 | Using where |
+----+-------------+-----------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.01 sec)

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  | idx2          | idx2 | 5       | 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‘ ;
+----+-------------+-----------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table     | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-----------+-------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | tab_index | range | idx2          | idx2 | 14      | NULL |    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 | range | idx2          | idx2 | 14      | NULL |    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 | range | idx2          | idx2 | 14      | NULL |    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 | range | idx2          | idx2 | 14      | NULL |    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 | range | idx2          | idx2 | 14      | NULL |    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 | range | idx2          | idx2 | 14      | NULL |    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 | range | idx2          | idx2 | 14      | NULL |    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 | range | idx2          | idx2 | 14      | NULL |    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 | range | idx2          | idx2 | 14      | NULL |    1 | Using where |
+----+-------------+-----------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

结论正确！

因为质疑今天面试官的结论，回家后做个测试，果断的面试官不够细心。明天如果打确认入职电话要再考虑下咯。。。

郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。

MySQL 联合索引测试2