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的表關聯常見的有以下兩種演算法：

1. Nested-Loop Join 演算法 ，
2. 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的大致流程如下：

1. 從表 t2 中讀取一行資料；
2. 從第 1 步的資料中，取出關聯欄位 a，到表 t1 中查找；
3. 取出表 t1 中滿足條件的行，跟 t2 中獲取到的結果合并，作為結果回傳給客戶端；
4. 重復上面 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的大致流程如下：

1. 把 t2 的所有資料放入到 join_buffer 中 ；
2. 把表 t1 中每一行取出來，跟 join_buffer 中的資料做對比；
3. 回傳滿足 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的優化

1. 關聯欄位加索引，讓mysql做join操作時盡量選擇NLJ演算法；

2. 小表驅動大表，寫多表連接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已經代表指定了表的執行順序）

3. 盡可能讓優化器去判斷，因為大部分情況下mysql優化器是比人要聰明的，使用straight_join一定要慎重，因為部分情況下人為指定的執行順序并不一定會比優化引擎要靠譜，

in和exist優化

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

1. 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 
   } 
   

2. 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欄位應建立索引
   

總結：

1. EXISTS (subquery)只回傳TRUE或FALSE,因此子查詢中的SELECT * 也可以用SELECT 1 替換,官方說法是實際執行時會忽略SELECT清單,因此沒有區別；

2. EXISTS子查詢的實際執行程序可能經過了優化而不是我們理解上的逐條對比；

3. 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的方法，

1. 查詢mysql自己維護的總行數，
   對于myisam存盤引擎的表做不帶where條件的count查詢性能是很高的，因為myisam存盤引擎的表的總行數會被 mysql存盤在磁盤上，查詢不需要計算 ，

   EXPLAIN SELECT COUNT(*) FROM myisamdemo;
   

   
   從Extra和table中可以看到查詢并沒有表和索引，像之前取最小id一樣，其效率是非常高的，對于innodb存盤引擎的表mysql不會存盤表的總記錄行數，查詢count需要實時計算 ，

2. show table status，
   如果只需要知道表總行數的估計值可以用如下sql查詢，性能很高 ，

   SHOW TABLE STATUS LIKE 'employees';
   

   對于MyIsam存盤引擎的表，該值是準確的，

3. 將總數維護到Redis里，
   插入或洗掉表資料行的時候同時維護redis里的表總行數key的計數值(用incr或decr命令)，但是這種方式可能不準，很難保證表操作和redis操作的事務一致性，

4. 增加計數表，
   插入或洗掉表資料行的時候同時維護計數表，讓他們在同一個事務里操作，