mysql join优化策略

mysql join使用的规则以及注意事项

join 算法分类

NLJ 算法，白话，以及触发的时机
- 两个或多个表join查询的时候，在查询被驱动表的时候走索引查询，驱动表查询被驱动表的时候，是从驱动表中一条条去查询被驱动表
BNL 算法，白话，以及触发的时机
- 两个或多个表join查询的时候，在查询被驱动表的时候非索引查询，将驱动表存放的join-buffer中，然后全表扫描被驱动表，在内存中进行匹配过滤的算法，主要耗费在过滤的时候计算数据太大
NLJ 算法的升级优化 ---》 MRR(索引顺序读)算法
- 驱动表一条条查询被驱动表的时候，索引顺序可能是随机，而且不是批量；改进的方式就是将索引统一在内存中排序，然后批量查询被驱动表，数据库中默认不支持，因此需要手动开启
- ```
set optimizer_switch="mrr_cost_based=off"
```
- 尝试的使用了一下，发现如果开启mrr的话，普通的查询速度会有所下降，原因未知；
NLJ 算法的升级优化 ---》BAK(批量读) 算法
- mrr算法的提升，批量将驱动表上的索引在被动表上进行顺序查找
join使用临时表进行优化查询的方式
- 假如A,B两个表需要进行join查询，但是B表上对应的字段没有添加索引，而且在业务上也没有必要添加索引，如果此时直接进行查询的话，算法是BNL算法，会导致在内存中的计算量过大，因此最好的方式是利用临时表的特性进行处理
```
  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进行优化查询
- 上述问题还可以直接使用业务逻辑上进行处理，也就是所说的hash-join，以小表作为驱动表，然后存入hash，在全表扫描打表，直接去进行hash判断，这样的话，计算的数据也就是打表的全量数据
BNL算法在使用过程中对数据库IO和数据库缓存的影响，如何影响Bufffer Pool的LRU算法；
在使用BNL查询的时候，buffer-pool 中lru的规则是，首先将数据放到old区，如果1秒后有使用的话，将数据移动到头部，因为是bnl在查询的时候，会进行不断的扫描内存中的数据，如果对象大的话，可能会在几秒一个轮回，导致这些冷数据移动到了buffer-pool的头部，影响正常数据的命中；
大表join操作虽然对IO有影响，但是在语句执行结束后，对IO的影响也就结束了。但是，对Buffer Pool的影响就是持续性的，需要依靠后续的查询请求慢慢恢复内存命中率。
为了减少这种影响，你可以考虑增大join_buffer_size的值，减少对被驱动表的扫描次数。
也就是说，BNL算法对系统的影响主要包括三个方面：
1 可能会多次扫描被驱动表，占用磁盘IO资源；
2 判断join条件需要执行M*N次对比（M、N分别是两张表的行数），如果是大表就会占用非常多的CPU资源；
3 可能会导致Buffer Pool的热数据被淘汰，影响内存命中率。
多张表联查的时候，优化方案设计

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；在执行sql的时候，explain查看是否使用索引

-- 如果没有使用索引直接强制使用c  --》EXPLAIN select * from t1 force index(c) ，确保驱动表尽可能的小
ALTER TABLE t1 ADD INDEX `c` ( c );

-- 给t2表的a字段加上索引,将BNL算法优化成BKA算法
alter table t2 add index `a` (a);

-- 同理给t3表的b字段上加上索引，将BNL算法优化成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的驱动表的数据集，越小越好，因为这样我们的驱动表就会越小