MySQL查询执行计划学习
来源:互联网 发布:天津大学网络远程教育 编辑:程序博客网 时间:2024/05/20 17:40
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)
0 0
- MySQL查询执行计划学习
- 《mysql学习》-- explain执行计划
- mysql查询缓存和执行计划
- mysql查询缓存和执行计划
- MySQL执行计划 EXPLAIN查询解释
- mysql学习笔记(1)--Explain执行计划
- pg 查询执行计划
- mysql阅读查询执行计划的简要说明
- explain mysql 执行计划
- Mysql查看执行计划
- MySQL执行计划解读
- MySQL执行计划解读
- Mysql执行计划
- MySQL执行计划
- MYSQL 执行计划
- MySQL执行计划解读
- Mysql执行计划
- MySQL执行计划
- VC++用第三方类实现串口通信
- 八皇后
- Android异步加载网络图片
- WEB设计
- svn使用教程-windows和Linux下常用操作总结
- MySQL查询执行计划学习
- SharedPreferences详解(二)——存取对象
- So you want to program the web with FSharp?
- 【OpenCV入门教程之七】 玩转OpenCV源代码:生成OpenCV工程解决方案与OpenCV源码编译
- thinkphp找不到common.php下的函数
- C++ CLI 类型 Object[]
- JSON-lib框架,转换JSON、XML不再困难
- Android图片管理组件(双缓存+异步加载)
- 编程算法 - 完全背包问题 代码(C)