MySQL5.7查詢性能改進

1.子查詢

1.1 MySQL5.5

mysql> explain extended select id,k,c,pad from sbtest1 where id in (select id from sbtest1 where k in ('50385','50011','43490','504922'));
+----+--------------------+---------+-----------------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type        | table   | type            | possible_keys | key     | key_len | ref  | rows   | filtered | Extra       |
+----+--------------------+---------+-----------------+---------------+---------+---------+------+--------+----------+-------------+
|  1 | PRIMARY            | sbtest1 | ALL             | NULL          | NULL    | NULL    | NULL | 612555 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | sbtest1 | unique_subquery | PRIMARY,k_1   | PRIMARY | 4       | func |      1 |   100.00 | Using where |
+----+--------------------+---------+-----------------+---------------+---------+---------+------+--------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

1.2 MySQL5.7

mysql> explain select id,k,c,pad from sbtest1 where id in (select id from sbtest1 where k in ('50385','50011','43490','50492'));
+----+-------------+---------+------------+--------+---------------+---------+---------+-------------------+------+----------+--------------------------+
| id | select_type | table   | partitions | type   | possible_keys | key     | key_len | ref               | rows | filtered | Extra                    |
+----+-------------+---------+------------+--------+---------------+---------+---------+-------------------+------+----------+--------------------------+
|  1 | SIMPLE      | sbtest1 | NULL       | range  | PRIMARY,k_1   | k_1     | 4       | NULL              |  253 |   100.00 | Using where; Using index |
|  1 | SIMPLE      | sbtest1 | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | sbtest.sbtest1.id |    1 |   100.00 | NULL                     |
+----+-------------+---------+------------+--------+---------------+---------+---------+-------------------+------+----------+--------------------------+
2 rows in set, 1 warning (0.00 sec)

2.union all

2.1 MySQL5.5,會將結果存在臨時表中

mysql> explain (select k from sbtest1 order by k) union all (select k from sbtest2 order by k);
+----+--------------+------------+-------+---------------+------+---------+------+--------+-------------+
| id | select_type  | table      | type  | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+--------------+------------+-------+---------------+------+---------+------+--------+-------------+
|  1 | PRIMARY      | sbtest1    | index | NULL          | k_1  | 4       | NULL | 612555 | Using index |
|  2 | UNION        | sbtest2    | index | NULL          | k_2  | 4       | NULL | 615365 | Using index |
| NULL | UNION RESULT | <union1,2> | ALL   | NULL          | NULL | NULL    | NULL |   NULL |             |
+----+--------------+------------+-------+---------------+------+---------+------+--------+-------------+
3 rows in set (0.00 sec)

2.2 MySQL5.7,直接展示結果

mysql> explain (select k from sbtest1 order by k) union all (select k from sbtest2 order by k);
+----+-------------+---------+------------+-------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+------+---------+------+--------+----------+-------------+
|  1 | PRIMARY     | sbtest1 | NULL       | index | NULL          | k_1  | 4       | NULL | 597600 |   100.00 | Using index |
|  2 | UNION       | sbtest2 | NULL       | index | NULL          | k_2  | 4       | NULL | 597744 |   100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+------+---------+------+--------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

3 in查詢

3.1 MySQL5.5

mysql> explain select * from sbtest1 where (k,pad) in ((43490,'24909597713-10795827686-60824686337-78820064088-50914299985'),(50088,'56702105543-74313438035-88959810983-96828764563-29757615888'));
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | sbtest1 | ALL  | NULL          | NULL | NULL    | NULL | 612555 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

3.2 MySQL5.7

mysql> explain select * from sbtest1 where (k,pad) in ((43490,'24909597713-10795827686-60824686337-78820064088-50914299985'),(50088,'56702105543-74313438035-88959810983-96828764563-29757615888'));
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | sbtest1 | NULL       | range | k_1           | k_1  | 4       | NULL |   77 |    20.00 | Using where |
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)