mysql explain extra using filesort 实验

先准备表数据， 并创建联合索引

mysql> create table test05(
    -> id int primary key not null auto_increment,
    -> c1 char(10),
    -> c2 char(10),
    -> c3 char(10),
    -> c4 char(10),
    -> c5 char(10)
    -> );
Query OK, 0 rows affected (0.01 sec)


mysql> insert into test05(c1, c2,c3,c4,c5) values('a1', 'a2', 'a3', 'a4', 'a5');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test05(c1, c2,c3,c4,c5) values('b1', 'b2', 'b3', 'b4', 'b5');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test05(c1, c2,c3,c4,c5) values('c1', 'c2', 'c3', 'c4', 'c5');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test05(c1, c2,c3,c4,c5) values('d1', 'd2', 'd3', 'd4', 'd5');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test05(c1, c2,c3,c4,c5) values('e1', 'e2', 'e3', 'e4', 'e5');
Query OK, 1 row affected (0.00 sec)

mysql> create index idx_test05_1234 on test05(c1, c2, c3, c4);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

 测试一：然后针对不同情况，查看执行计划

mysql> explain select * from test05 where c1='a1' and c2='a2' and c3='a3' and c4='a4';
+----+-------------+--------+------------+------+-----------------+-----------------+---------+-------------------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys   | key             | key_len | ref                     | rows | filtered | Extra |
+----+-------------+--------+------------+------+-----------------+-----------------+---------+-------------------------+------+----------+-------+
|  1 | SIMPLE      | test05 | NULL       | ref  | idx_test05_1234 | idx_test05_1234 | 164     | const,const,const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+-----------------+-----------------+---------+-------------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from test05 where c1='a1' and c2='a2' and c3='a3';
+----+-------------+--------+------------+------+-----------------+-----------------+---------+-------------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys   | key             | key_len | ref               | rows | filtered | Extra |
+----+-------------+--------+------------+------+-----------------+-----------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | test05 | NULL       | ref  | idx_test05_1234 | idx_test05_1234 | 123     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+-----------------+-----------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)


mysql> explain select * from test05 where c1='a1' and c2='a2';
+----+-------------+--------+------------+------+-----------------+-----------------+---------+-------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys   | key             | key_len | ref         | rows | filtered | Extra |
+----+-------------+--------+------------+------+-----------------+-----------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | test05 | NULL       | ref  | idx_test05_1234 | idx_test05_1234 | 82      | const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+-----------------+-----------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from test05 where c1='a1' and c2='a2' order by c3;
+----+-------------+--------+------------+------+-----------------+-----------------+---------+-------------+------+----------+-----------------------+
| id | select_type | table  | partitions | type | possible_keys   | key             | key_len | ref         | rows | filtered | Extra                 |
+----+-------------+--------+------------+------+-----------------+-----------------+---------+-------------+------+----------+-----------------------+
|  1 | SIMPLE      | test05 | NULL       | ref  | idx_test05_1234 | idx_test05_1234 | 82      | const,const |    1 |   100.00 | Using index condition |
+----+-------------+--------+------------+------+-----------------+-----------------+---------+-------------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from test05 where c1='a1' and c2='a2' order by c4;
+----+-------------+--------+------------+------+-----------------+-----------------+---------+-------------+------+----------+---------------------------------------+
| id | select_type | table  | partitions | type | possible_keys   | key             | key_len | ref         | rows | filtered | Extra                                 |
+----+-------------+--------+------------+------+-----------------+-----------------+---------+-------------+------+----------+---------------------------------------+
|  1 | SIMPLE      | test05 | NULL       | ref  | idx_test05_1234 | idx_test05_1234 | 82      | const,const |    1 |   100.00 | Using index condition; Using filesort |
+----+-------------+--------+------------+------+-----------------+-----------------+---------+-------------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)

可以看到最后一种情况出现 using filesort，会造成性能极大下降，应该避免。

测试二：下边这两种情况，结果是一样的，这是因为sql查询优化器在起作用，在执行sql之前，帮我们把条件语句中的条件顺序更改，使索引能发挥最大作用。

mysql> explain select * from test05 where c1='a1' and c2='a2' and c3>'a3' and c4='a4';
+----+-------------+--------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys   | key             | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | test05 | NULL       | range | idx_test05_1234 | idx_test05_1234 | 123     | NULL |    1 |    20.00 | Using index condition |
+----+-------------+--------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from test05 where c1='a1' and c2='a2' and c4='a4' and c3>'a3';
+----+-------------+--------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys   | key             | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | test05 | NULL       | range | idx_test05_1234 | idx_test05_1234 | 123     | NULL |    1 |    20.00 | Using index condition |
+----+-------------+--------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

测试三：下边的测试，第二种情况，没有出现filesort， 这是因为c2有出现在where条件中，并且c2是一个指定的常量，所以排序时，c2是以常量的方式出现的， 排序时可以不考虑他只考虑c3。

可以想象一下select后的结果,c2的值都是一样的，所以拿不拿他排序都没有关系。

mysql> explain select * from test05 where c1='a1' order by c3, c2;
+----+-------------+--------+------------+------+-----------------+-----------------+---------+-------+------+----------+---------------------------------------+
| id | select_type | table  | partitions | type | possible_keys   | key             | key_len | ref   | rows | filtered | Extra                                 |
+----+-------------+--------+------------+------+-----------------+-----------------+---------+-------+------+----------+---------------------------------------+
|  1 | SIMPLE      | test05 | NULL       | ref  | idx_test05_1234 | idx_test05_1234 | 41      | const |    1 |   100.00 | Using index condition; Using filesort |
+----+-------------+--------+------------+------+-----------------+-----------------+---------+-------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from test05 where c1='a1' and c2='a2' order by c3, c2;
+----+-------------+--------+------------+------+-----------------+-----------------+---------+-------------+------+----------+-----------------------+
| id | select_type | table  | partitions | type | possible_keys   | key             | key_len | ref         | rows | filtered | Extra                 |
+----+-------------+--------+------------+------+-----------------+-----------------+---------+-------------+------+----------+-----------------------+
|  1 | SIMPLE      | test05 | NULL       | ref  | idx_test05_1234 | idx_test05_1234 | 82      | const,const |    1 |   100.00 | Using index condition |
+----+-------------+--------+------------+------+-----------------+-----------------+---------+-------------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)