Mysql優化四：關聯、count、in及exist優化

join關聯查詢優化

接下來通過範例來了解一下join關聯查詢的執行原理及優化的思路。首先來看一下兩個範例表的結構：
t1表的結構如下：

DROP TABLE IF EXISTS `t1`;
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 AUTO_INCREMENT=20001 DEFAULT CHARSET=utf8;

t2表的結構如下：

DROP TABLE IF EXISTS `t2`;
CREATE TABLE `t2` (
  `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 AUTO_INCREMENT=101 DEFAULT CHARSET=utf8;

t1表及t2表的資料連線如下：指令碼連線

Mysql的表關聯常見的有以下兩種演演算法：

Nested-Loop Join 演演算法。
Block Nested-Loop Join 演演算法。

接下來來看這兩種演演算法在join查詢中應用。

Nested-Loop Join 演演算法

一次一行迴圈地從第一張表（稱為驅動表）中讀取行，在這行資料中取到關聯欄位，根據關聯欄位在另一張表（被驅動表）裡取出滿足條件的行，然後取出兩張表的結果合集。

EXPLAIN SELECT * FROM t1 INNER JOIN t2 WHERE t1.a = t2.a;

在這裡插入圖片描述
從執行計劃中可以看到這些資訊：
兩條資料的id均為1，而他表在前，所以先執行t2表，所以驅動表是 t2，被驅動表是 t1。先執行的就是驅動表(執行計劃結果的id如果一樣則按從上到下順序執行sql)；優化器一般會優先選擇小表做驅動表。所以使用 inner join 時，排在前面的表並不一定就是驅動表。一般 join 語句中，如果執行計劃 Extra 中未出現 Using join buffer 則表示使用的 join 演演算法是 NLJ。
上面sql的大致流程如下：

從表 t2 中讀取一行資料；
從第 1 步的資料中，取出關聯欄位 a，到表 t1 中查詢；
取出表 t1 中滿足條件的行，跟 t2 中獲取到的結果合併，作為結果返回給使用者端；
重複上面 3 步。

整個過程會讀取 t2 表的所有資料(掃描100行)，然後遍歷這每行資料中欄位 a 的值，根據 t2 表中 a 的值索引掃描 t1 表中的對應行(掃描100次 t1 表的索引，1次掃描可以認為最終只掃描 t1 表一行完整資料，也就是總共 t1 表也掃描了100行)。因此整個過程掃描了 200 行。如果被驅動表的關聯欄位沒索引，使用NLJ演演算法效能會比較低(下面有詳細解釋)，mysql會選擇Block Nested-Loop Join演演算法。

基於塊的巢狀迴圈連線 Block Nested-Loop Join(BNL)演演算法

把驅動表的資料讀入到 join_buffer 中，然後掃描被驅動表，把被驅動表每一行取出來跟 join_buffer 中的資料做對比。

EXPLAIN SELECT * FROM t1 INNER JOIN t2 WHERE t1.b = t2.b;

在這裡插入圖片描述
Extra 中的Using join buffer (Block Nested Loop)說明該關聯查詢使用的是 BNL 演演算法。
上面sql的大致流程如下：

把 t2 的所有資料放入到 join_buffer 中；
把表 t1 中每一行取出來，跟 join_buffer 中的資料做對比；
返回滿足 join 條件的資料；

整個過程對錶 t1 和 t2 都做了一次全表掃描，因此掃描的總行數為10000(表 t1 的資料總量) + 100(表 t2 的資料總量) = 10100。並且 join_buffer 裡的資料是無序的，因此對錶 t1 中的每一行，都要做 100 次判斷，所以記憶體中的判斷次數是100 * 10000= 100 萬次。
被驅動表的關聯欄位沒索引為什麼要選擇使用 BNL 演演算法而不使用 Nested-Loop Join 呢？
如果上面第二條sql使用 Nested-Loop Join，那麼掃描行數為 100 * 10000 = 100萬次，這個是磁碟掃描。
很顯然，用BNL磁碟掃描次數少很多，相比於磁碟掃描，BNL的記憶體計算會快得多。因此MySQL對於被驅動表的關聯欄位沒索引的關聯查詢，一般都會使用 BNL 演演算法。如果有索引一般選擇 NLJ 演演算法，有索引的情況下 NLJ 演演算法比 BNL演演算法效能更高。

關聯查詢總結

對於關聯sql的優化

關聯欄位加索引。讓mysql做join操作時儘量選擇NLJ演演算法；
小表驅動大表，寫多表連線sql時如果明確知道哪張表是小表可以用straight_join寫法固定連線驅動方式，省去mysql優化器自己判斷的時間。

straight_join解釋：
straight_join功能同join類似，但能讓左邊的表來驅動右邊的表，能改表優化器對於聯表查詢的執行順序。比如：
select * from t2 straight_join t1 on t2.a = t1.a;
代表制定mysql選著 t2 表作為驅動表。 straight_join只適用於inner join，並不適用於left join，right join。（因為left join，right join已經代表指定了表的執行順序）
儘可能讓優化器去判斷，因為大部分情況下mysql優化器是比人要聰明的。使用straight_join一定要慎重，因為部分情況下人為指定的執行順序並不一定會比優化引擎要靠譜。

in和exist優化

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

in：當B表的資料集小於A表的資料集時，in優於exists 。

select * from A where id in (select id from B) 
#等價於： 
for(select id from B){ 
select * from A where A.id = B.id 
}

exists：當A表的資料集小於B表的資料集時，exists優於in 。
將主查詢A的資料，放到子查詢B中做條件驗證，根據驗證結果（true或false）來決定主查詢的資料是否保留。
```
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表的ID欄位應建立索引
```

總結：

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

count的優化

在優化之前關閉快取防止快取對查詢效率產生影響：

set global query_cache_size=0;
set global query_cache_type=0;

EXPLAIN SELECT COUNT(1) FROM employees;

在這裡插入圖片描述

EXPLAIN SELECT COUNT(id) FROM employees;

在這裡插入圖片描述

EXPLAIN SELECT COUNT(name) FROM employees;

在這裡插入圖片描述

EXPLAIN SELECT COUNT(*) FROM employees;

在這裡插入圖片描述
四個sql的執行計劃一樣，說明這四個sql執行效率應該差不多，區別在於根據某個欄位count不會統計欄位為null值的資料行。
為什麼mysql最終選擇輔助索引而不是主鍵聚集索引？因為二級索引相對主鍵索引儲存資料更少，檢索效能應該更高。

常見優化count的方法。

查詢mysql自己維護的總行數。
對於myisam儲存引擎的表做不帶where條件的count查詢效能是很高的，因為myisam儲存引擎的表的總行數會被 mysql儲存在磁碟上，查詢不需要計算。
```
EXPLAIN SELECT COUNT(*) FROM myisamdemo;
```
從Extra和table中可以看到查詢並沒有表和索引，像之前取最小id一樣，其效率是非常高的。對於innodb儲存引擎的表mysql不會儲存表的總記錄行數，查詢count需要實時計算。
show table status。
如果只需要知道表總行數的估計值可以用如下sql查詢，效能很高。
```
SHOW TABLE STATUS LIKE 'employees';
```
對於MyIsam儲存引擎的表，該值是準確的。
將總數維護到Redis裡。
插入或刪除表資料行的時候同時維護redis裡的表總行數key的計數值(用incr或decr命令)，但是這種方式可能不準，很難保證表操作和redis操作的事務一致性。
增加計數表。
插入或刪除表資料行的時候同時維護計數表，讓他們在同一個事務裡操作。

Mysql優化四：關聯、count、in及exist優化

目錄

join關聯查詢優化

Nested-Loop Join 演演算法

基於塊的巢狀迴圈連線 Block Nested-Loop Join(BNL)演演算法

關聯查詢總結

in和exist優化

count的優化