MySQL not in巢狀查詢改寫成外連線方式
在MySQL中,not in 巢狀查詢會在資料庫裡面建立一張臨時表,導致執行效率很低,可以改成外連線的方式處理,效率會好很多。
not in方式
mysql> select * from dept where deptno not in (select deptno from emp);
+--------+------------+---------+
| deptno | dname | loc |
+--------+------------+---------+
| 40 | OPERATIONS | BOSTON |
| 50 | Research | BeiJing |
+--------+------------+---------+
2 rows in set (0.00 sec)
mysql> explain select * from dept where deptno not in (select deptno from emp);
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | PRIMARY | dept | ALL | NULL | NULL | NULL | NULL | 5 | Using where |
| 2 | SUBQUERY | emp | ALL | NULL | NULL | NULL | NULL | 14 | NULL |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
2 rows in set (0.00 sec)
外連線方式
mysql> select * from dept e left join emp d on e.deptno=d.deptno where d.deptno is null;+--------+------------+---------+-------+-------+------+------+----------+------+------+--------+
| deptno | dname | loc | empno | ename | job | mgr | hiredate | sal | com | deptno |
+--------+------------+---------+-------+-------+------+------+----------+------+------+--------+
| 40 | OPERATIONS | BOSTON | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 50 | Research | BeiJing | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+--------+------------+---------+-------+-------+------+------+----------+------+------+--------+
2 rows in set (0.00 sec)
mysql> explain select * from dept e left join emp d on e.deptno=d.deptno where d.deptno is null;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
| 1 | SIMPLE | e | ALL | NULL | NULL | NULL | NULL | 5 | NULL |
| 1 | SIMPLE | d | ALL | NULL | NULL | NULL | NULL | 14 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
2 rows in set (0.00 sec)
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/26506993/viewspace-2123663/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 資料庫 - 連線查詢、巢狀查詢、集合查詢資料庫巢狀
- Mysql 巢狀查詢100例子MySql巢狀
- MySQL——優化巢狀查詢和分頁查詢MySql優化巢狀
- 巢狀子查詢巢狀
- 查詢重寫對全外連線無效
- 查詢重寫對全外連線無效(二)
- 【janes】多表查詢 外連線
- MySQL之連線查詢MySql
- 對比SQL中簡單巢狀查詢與非巢狀查詢CFSQL巢狀
- Mysql中的巢狀子查詢問題QSBSMySql巢狀
- MYSQL學習筆記23: 多表查詢(自連線內連線+左右外連線)MySql筆記
- MySQL之連線查詢和子查詢MySql
- mysql查詢語句5:連線查詢MySql
- EleasticSearch6.0 巢狀查詢AST巢狀
- Sql Server系列:巢狀查詢SQLServer巢狀
- MySQL In不能用內連線改寫MySql
- 資料庫查詢優化:巢狀查詢資料庫優化巢狀
- MySQL學習-連線查詢MySql
- MySQL8:連線查詢MySql
- MySql的join(連線)查詢 (三表 left join 寫法)MySql
- MySQL執行狀況查詢方式介紹MySql
- 兩表連線一:巢狀迴圈連線巢狀
- mysql-分組查詢-子查詢-連線查詢-組合查詢MySql
- MySQL內連線查詢語句MySql
- es中如何使用巢狀物件查詢巢狀物件
- 巢狀關聯會查詢兩次巢狀
- MongoDB之資料查詢(巢狀集合)MongoDB巢狀
- SQL語句巢狀查詢問題SQL巢狀
- Access/VBA/Excel-13-巢狀查詢Excel巢狀
- PostgreSQL優化案例之 反連線與外連線等價改寫SQL優化
- [冷楓推薦]:資料庫操作,內外聯查詢,分組查詢,巢狀查詢,交叉查詢,多表查詢,語句小結。資料庫巢狀
- MySQL 連線查詢超全詳解MySql
- PHP連線、查詢MySQL資料庫PHPMySql資料庫
- MyBatis(六) sql片段定義、級聯查詢、巢狀查詢MyBatisSQL巢狀
- Oracle的表連線方法(二)巢狀迴圈連線Oracle巢狀
- 3.DQL資料查詢語言(內連線,外連線,自連線)
- not exists改為外連線
- 連線查詢