MySQL5.7 Query Performance Improvements

1. Sub-query 1.1 MySQL5.5

  mysql> Explain extended select Id,k,c,pad from Sbtest1 where ID in (select ID from Sbtest1 where k in (' 5038 5 ', ' 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 (the 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 all2.1 MySQL5.5, the result exists in a temporary table

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, direct display of results

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 Query 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)

MySQL5.7 Query Performance Improvements