mysql 优化例子：IN 换 INNER JOIN
MySQL > explain SELECT-> count(*)
-> FROM
-> t_cmdb_app_version a
-> INNER JOIN
-> (SELECT
-> pid
-> FROM
-> t_cmdb_app_relation UNION SELECT
-> rp_id
-> FROM
-> t_cmdb_app_relation) b ON a.id = b.pid;
+----+--------------+---------------------+--------+---------------+---------+---------+-------+------+--------------------------+
| id | select_type| table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+---------------------+--------+---------------+---------+---------+-------+------+--------------------------+
|1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL|766 | Using where |
|1 | PRIMARY | a | eq_ref | PRIMARY | PRIMARY | 4 | b.pid | 1 | Using where; Using index |
|2 | DERIVED | t_cmdb_app_relation | ALL | NULL | NULL | NULL | NULL|383 | NULL |
|3 | UNION | t_cmdb_app_relation | ALL | NULL | NULL | NULL | NULL|383 | NULL |
| NULL | UNION RESULT | | ALL | NULL | NULL | NULL | NULL| NULL | Using temporary |
+----+--------------+---------------------+--------+---------------+---------+---------+-------+------+--------------------------+
5 rows in set (0.00 sec)
页:
[1]