MySQL- In 和 Exists的優化案例講解

文章目錄

• 生猛幹貨
• Demo Table
• in的邏輯
• • 優化原則
• exists的邏輯
• 搞定MySQL

帶你搞定MySQL實戰，輕松對應海量業務處理及高并發需求，從容應對大場面試

CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

create table t2 like t1;
           

兩個表 t1 和 t2 ， 一樣的，包括索引資訊

資料量 t1 ，t2 如下

mysql> select count(1) from t1;
+----------+
| count(1) |
+----------+
|    10000 |
+----------+
1 row in set

mysql> select count(1) from t2;
+----------+
| count(1) |
+----------+
|      100 |
+----------+
1 row in set

mysql> 
           

select * from t1 where id in (select id from t2) ;
           

這個SQL，先執行哪個呢？

看看執行計劃

可以了解為

for(select id from t2){
      select * from t1 where t1.id = t2.id
    }
           

原則：小表驅動大表，即小的資料集驅動大的資料集

當T2表的資料集小于T1表的資料集時，in優于exists

select * from A where exists (select 1 from B where B.id = A.id)

           

for(select * from A){
     select * from B where B.id = A.id
   }
           

當A表的資料集小于B表的資料集時，exists優于in

1. EXISTS (subquery)隻傳回TRUE或FALSE,是以子查詢中的SELECT * 也可以用SELECT 1替換,官方說法是實際執行時會忽略SELECT清單,是以沒有差別
2. EXISTS子查詢的實際執行過程可能經過了優化而不是我們了解上的逐條對比
3. EXISTS子查詢往往也可以用JOIN來代替，何種最優需要具體問題具體分析

mysql> explain select * from t2 where exists (select 1 from t1 where t1.id = t2.id) ;
+----+--------------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-------------+
| id | select_type        | table | partitions | type   | possible_keys | key     | key_len | ref           | rows | filtered | Extra       |
+----+--------------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-------------+
|  1 | PRIMARY            | t2    | NULL       | ALL    | NULL          | NULL    | NULL    | NULL          |  100 |      100 | Using where |
|  2 | DEPENDENT SUBQUERY | t1    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | artisan.t2.id |    1 |      100 | Using index |
+----+--------------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-------------+
2 rows in set