mysql join優(yōu)化策略

mysql join使用的規(guī)則以及注意事項

join 算法分類

NLJ 算法蘑斧，白話，以及觸發(fā)的時機
- 兩個或多個表join查詢的時候烈涮，在查詢被驅動表的時候走索引查詢适揉，驅動表查詢被驅動表的時候，是從驅動表中一條條去查詢被驅動表
BNL 算法聪轿，白話爷肝，以及觸發(fā)的時機
- 兩個或多個表join查詢的時候，在查詢被驅動表的時候非索引查詢陆错，將驅動表存放的join-buffer中灯抛，然后全表掃描被驅動表，在內(nèi)存中進行匹配過濾的算法音瓷，主要耗費在過濾的時候計算數(shù)據(jù)太大
NLJ 算法的升級優(yōu)化 ---》 MRR(索引順序讀)算法
- 驅動表一條條查詢被驅動表的時候对嚼，索引順序可能是隨機，而且不是批量绳慎；改進的方式就是將索引統(tǒng)一在內(nèi)存中排序纵竖，然后批量查詢被驅動表漠烧，數(shù)據(jù)庫中默認不支持，因此需要手動開啟
- ```
set optimizer_switch="mrr_cost_based=off"
```
- 嘗試的使用了一下靡砌，發(fā)現(xiàn)如果開啟mrr的話已脓，普通的查詢速度會有所下降，原因未知通殃；
NLJ 算法的升級優(yōu)化 ---》BAK(批量讀) 算法
- mrr算法的提升摆舟，批量將驅動表上的索引在被動表上進行順序查找
join使用臨時表進行優(yōu)化查詢的方式
- 假如A,B兩個表需要進行join查詢，但是B表上對應的字段沒有添加索引邓了，而且在業(yè)務上也沒有必要添加索引，如果此時直接進行查詢的話媳瞪，算法是BNL算法骗炉，會導致在內(nèi)存中的計算量過大，因此最好的方式是利用臨時表的特性進行處理
```
  create temporary table temp_t(id int primary key, a int, b int, index(b))engine=innodb;
  insert into temp_t select * from t2 where b>=1 and b<=2000;
  select * from t1 join temp_t on (t1.b=temp_t.b);
```
使用hash join進行優(yōu)化查詢
- 上述問題還可以直接使用業(yè)務邏輯上進行處理蛇受，也就是所說的hash-join句葵，以小表作為驅動表，然后存入hash兢仰，在全表掃描打表乍丈，直接去進行hash判斷，這樣的話把将，計算的數(shù)據(jù)也就是打表的全量數(shù)據(jù)
BNL算法在使用過程中對數(shù)據(jù)庫IO和數(shù)據(jù)庫緩存的影響轻专，如何影響B(tài)ufffer Pool的LRU算法；
在使用BNL查詢的時候察蹲，buffer-pool 中l(wèi)ru的規(guī)則是请垛，首先將數(shù)據(jù)放到old區(qū)，如果1秒后有使用的話洽议，將數(shù)據(jù)移動到頭部宗收，因為是bnl在查詢的時候，會進行不斷的掃描內(nèi)存中的數(shù)據(jù)亚兄，如果對象大的話混稽，可能會在幾秒一個輪回，導致這些冷數(shù)據(jù)移動到了buffer-pool的頭部审胚，影響正常數(shù)據(jù)的命中匈勋；
大表join操作雖然對IO有影響，但是在語句執(zhí)行結束后菲盾，對IO的影響也就結束了颓影。但是，對Buffer Pool的影響就是持續(xù)性的懒鉴，需要依靠后續(xù)的查詢請求慢慢恢復內(nèi)存命中率诡挂。
為了減少這種影響碎浇，你可以考慮增大join_buffer_size的值，減少對被驅動表的掃描次數(shù)璃俗。
也就是說奴璃，BNL算法對系統(tǒng)的影響主要包括三個方面：
1 可能會多次掃描被驅動表，占用磁盤IO資源城豁；
2 判斷join條件需要執(zhí)行M*N次對比（M苟穆、N分別是兩張表的行數(shù)），如果是大表就會占用非常多的CPU資源唱星；
3 可能會導致Buffer Pool的熱數(shù)據(jù)被淘汰雳旅，影響內(nèi)存命中率。
多張表聯(lián)查的時候间聊，優(yōu)化方案設計

select * from t1 join t2 on(t1.a=t2.a) join t3 on (t2.b=t3.b) where t1.c>=X and t2.c>=Y and t3.c>=Z;

-- 初始化表
CREATE TABLE `t1` (
`id` int(11) NOT NULL,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
create table t2 like t1;
create table t3 like t2;

delimiter;;
CREATE PROCEDURE idata ( ) 
BEGIN
  DECLARE i INT;
  SET i = 1;
  WHILE
          ( i <= 1000 ) DO
          INSERT INTO t1
      VALUES
          ( i, 1000-i, i, 1000-i );
      SET i = i + 1;  
  END WHILE;  
END;;
delimiter;

insert into t2 (select *  from t1);
insert into t2 (select *  from t1);

-- 開啟mrr算法和bka算法
SET optimizer_switch = 'mrr=on,mrr_cost_based=off,batched_key_access=on';

-- 假如 X>Y>Z,也就是t1 是驅動表攒盈，需要在t1上建立一個索引，c哎榴；在執(zhí)行sql的時候型豁，explain查看是否使用索引

-- 如果沒有使用索引直接強制使用c  --》EXPLAIN select * from t1 force index(c) ，確保驅動表盡可能的小
ALTER TABLE t1 ADD INDEX `c` ( c );

-- 給t2表的a字段加上索引,將BNL算法優(yōu)化成BKA算法
alter table t2 add index `a` (a);

-- 同理給t3表的b字段上加上索引尚蝌，將BNL算法優(yōu)化成BKA算法
alter table t3 add index `b` (b);

-- 最終的sql形式因為我的t1表的索引一直不走c因此強制加上索引
EXPLAIN select * from t1 force index(c) LEFT JOIN t2 on(t1.a=t2.a) LEFT JOIN t3 on (t2.b=t3.b) where t1.c>=300 and t2.c>=200 and t3.c>=100;
-- 結果
*************************** 1. row ***************************
         id: 1
select_type: SIMPLE
      table: t1
 partitions: NULL
       type: range
possible_keys: c
        key: c
    key_len: 5
        ref: NULL
       rows: 700
   filtered: 100.00
      Extra: Using index condition
*************************** 2. row ***************************
         id: 1
select_type: SIMPLE
      table: t2
 partitions: NULL
       type: ref
possible_keys: a,c
        key: a
    key_len: 5
        ref: testJoin.t1.a
       rows: 1
   filtered: 80.00
      Extra: Using where
*************************** 3. row ***************************
         id: 1
select_type: SIMPLE
      table: t3
 partitions: NULL
       type: ref
possible_keys: b,c
        key: b
    key_len: 5
        ref: testJoin.t2.b
       rows: 1
   filtered: 90.00
      Extra: Using where
3 rows in set, 1 warning (0.00 sec)

整體的思路就是迎变，盡量讓每一次參與join的驅動表的數(shù)據(jù)集，越小越好飘言，因為這樣我們的驅動表就會越小