接上一篇文章:
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)
结论正确!