MySQL5.7 query performance improvement

1. Subquery 1. 1 MySQL5.5mysql > explain extended select id,k,c,pad from sbtest1 where id in (select id from sbtest1 where k in ('50385, 55011, 43490, 504922)) +- -+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -+ | 1 | PRIMARY | sbtest1 | ALL | NULL | 612555 | 100.00 | Using where | | 2 | DEPENDENT SUBQUERY | sbtest1 | unique_subquery | PRIMARY Kroom1 | PRIMARY | 4 | func | 1 | 100.00 | Using where | + -- + 2 rows in set 1 warning (0.00 sec) 1.2 MySQL5.7mysql > explain select id,k,c,pad from sbtest1 where id in (select id from sbtest1 where k in) +- -+-+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + -+-- + | 1 | SIMPLE | sbtest1 | NULL | range | PRIMARY KPI 1 | KBN 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 will be stored in the 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 | KFL1 | 4 | NULL | 612555 | Using index | 2 | UNION | sbtest2 | index | NULL | KF2 | 4 | NULL | 615365 | Using index | | NULL | UNION RESULT | ALL | NULL | | +-+ -+-+ 3 rows in set (0.00 sec) 2.2 MySQL5.7 Directly display the result 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 | KFL1 | 4 | NULL | 597600 | 100.00 | Using index | | 2 | UNION | sbtest2 | NULL | index | NULL | KF2 | 4 | NULL | 597744 | | 100.00 | Using index | +-- +-| -+ 2 rows in set 1 warning (0.00 sec) 3 in query 3.1 MySQL5.5mysql > explain select * from sbtest1 where (KMagnePad) in ((43490Magneto 24909597713-10795827686-60824686337-78820064088-50914299985'), (5008Magneto 56702105543-743438035-88959810983-6828764563-29757615888')) +-+ | id | select_type | table | type | possible_keys | key | key_len | | ref | rows | Extra | +-- + | 1 | SIMPLE | sbtest1 | ALL | NULL | 612555 | Using where | +-+ 1 row in set (0.00 sec) 3.2 MySQL5.7mysql > explain select * from sbtest1 where (k Pad) in (24909597713-10795827686-60824686337-78820064088-50914299985'), (50088 penny 56702105543-74313438035-88959810983-9828764563-29757615888') +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +-+-+ -+ | 1 | SIMPLE | sbtest1 | NULL | range | KF1 | KFL1 | 4 | NULL | 77 | 20.00 | Using where | + -+ 1 row in set 1 warning (0.00 sec)