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)
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)
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)
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)
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)
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)
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。