MySQL查询执行计划学习

最新推荐文章于 2024-01-13 18:42:40 发布

onion____

最新推荐文章于 2024-01-13 18:42:40 发布

阅读量910

点赞数 1

分类专栏： MySQL

本文链接：https://blog.csdn.net/codeforme/article/details/37914835

版权

MySQL 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

SELECT_TYPE：

1） SIMPLE：简单的SELECT，不实用UNION或者子查询

mysql> explain select * from t2;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL |  100 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

2） PRIMARY：最外层SELECT。

mysql> explain select * from (select * from t2 where id2=2) b;
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
|  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |  100 | NULL        |
|  2 | DERIVED     | t2         | ALL  | NULL          | NULL | NULL    | NULL |  100 | Using where |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
2 rows in set (0.00 sec)

3） UNION：第二层，在SELECT之后使用了UNION。

mysql> explain select * from t1 union select * from t2;
+----+--------------+------------+------+---------------+------+---------+------+-------+-----------------+
| id | select_type  | table      | type | possible_keys | key  | key_len | ref  | rows  | Extra           |
+----+--------------+------------+------+---------------+------+---------+------+-------+-----------------+
|  1 | PRIMARY      | t1         | ALL  | NULL          | NULL | NULL    | NULL | 10208 | NULL            |
|  2 | UNION        | t2         | ALL  | NULL          | NULL | NULL    | NULL |   100 | NULL            |
| NULL | UNION RESULT | <union1,2> | ALL  | NULL          | NULL | NULL    | NULL |  NULL | Using temporary |
+----+--------------+------------+------+---------------+------+---------+------+-------+-----------------+
3 rows in set (0.00 sec)

4） DEPENDENT UNION：UNION语句中的第二个SELECT，依赖于外部子查询。

mysql> explain select * from t1 where id1 in (select id2 from t2 where id2<10 union select id3 from t3 where id3<5);
+----+--------------------+------------+------+---------------+------+---------+------+-------+--------------------------+
| id | select_type        | table      | type | possible_keys | key  | key_len | ref  | rows  | Extra                    |
+----+--------------------+------------+------+---------------+------+---------+------+-------+--------------------------+
|  1 | PRIMARY            | t1         | ALL  | NULL          | NULL | NULL    | NULL | 10208 | Using where              |
|  2 | DEPENDENT SUBQUERY | t2         | ALL  | NULL          | NULL | NULL    | NULL |   100 | Using where              |
|  3 | DEPENDENT UNION    | t3         | ref  | id3           | id3  | 5       | func |     1 | Using where; Using index |
| NULL | UNION RESULT       | <union2,3> | ALL  | NULL          | NULL | NULL    | NULL |  NULL | Using temporary          |
+----+--------------------+------------+------+---------------+------+---------+------+-------+--------------------------+
4 rows in set (0.00 sec)

5） UNION RESULT：UNION的结果。

mysql> explain select * from t1 union select * from t2;
+----+--------------+------------+------+---------------+------+---------+------+-------+-----------------+
| id | select_type  | table      | type | possible_keys | key  | key_len | ref  | rows  | Extra           |
+----+--------------+------------+------+---------------+------+---------+------+-------+-----------------+
|  1 | PRIMARY      | t1         | ALL  | NULL          | NULL | NULL    | NULL | 10208 | NULL            |
|  2 | UNION        | t2         | ALL  | NULL          | NULL | NULL    | NULL |   100 | NULL            |
| NULL | UNION RESULT | <union1,2> | ALL  | NULL          | NULL | NULL    | NULL |  NULL | Using temporary |
+----+--------------+------------+------+---------------+------+---------+------+-------+-----------------+
3 rows in set (0.00 sec)

6） SUBQUERY：子查询中的第一个SELECT。

mysql> explain select * from t1 where id1=(select id2 from t2 where id2=2);
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|  1 | PRIMARY     | t1    | const | PRIMARY       | PRIMARY | 4       | const |    1 | NULL        |
|  2 | SUBQUERY    | t2    | ALL   | NULL          | NULL    | NULL    | NULL  |  100 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
2 rows in set (0.00 sec)

7） DERIVED：被驱动的SELECT子查询

mysql> explain select * from (select * from t2 where id2=2) b;
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
|  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |  100 | NULL        |
|  2 | DERIVED     | t2         | ALL  | NULL          | NULL | NULL    | NULL |  100 | Using where |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
2 rows in set (0.00 sec)