前言:一般不会修改mysql的优化器的算法，mysql优化器会根据内置的算法选择你所执行语句最优的算法。

1.MySQL-Change buffffer

比如insert，update，delete 数据。
对于聚簇索引会立即更新。
对于辅助索引，不是实时更新的。
在InnoDB 内存结构中，加入了insert buffer（会话），现在版本叫change buffer。
Change buffer 功能是临时缓冲辅助索引需要的数据更新。
当我们需要查询新insert 的数据，会在内存中进行merge(合并)操作，此时辅助索引就是最新的。

2,优化器算法查询

show varialbes like ‘%switch%’
select @@optimizer_switch;
index_merge=on,
index_merge_union=on,
index_merge_sort_union=on,
index_merge_intersection=on,
engine_condition_pushdown=on,
index_condition_pushdown=on, ##
hash_join=on ##
mrr=on,mrr_cost_based=on,
block_nested_loop=on,
batched_key_access=off,
materialization=on,
semijoin=on,
loosescan=on,
firstmatch=on,
duplicateweedout=on,
subquery_materialization_cost_based=on,
use_index_extensions=on,
condition_fanout_filter=on,
derived_merge=on

3.设置修改优化器算法

  1. my.cnf ##不建议，固定优化器的某种算法
    optimizer_switch=’batched_key_access=on’
    2. set global optimizer_switch=’batched_key_access=on’; 不建议，固定优化器的某种算法
    3. hints
    SELECT /+ NO_RANGE_OPTIMIZATION(t3 PRIMARY, f2_idx) / f1 ##给某一个语句设置一个优化器算法
    FROM t3 WHERE f1 > 30 AND f1 < 33;
    SELECT /+ BKA(t1) NO_BKA(t2) / FROM t1 INNER JOIN t2 WHERE …;
    SELECT /    + NO_ICP(t1, t2) / FROM t1 INNER JOIN t2 WHERE …;
    SELECT /+ SEMIJOIN(FIRSTMATCH, LOOSESCAN) / FROM t1 …;
    EXPLAIN SELECT /    + NO_ICP(t1) / FROM t1 WHERE …;
    https://dev.mysql.com/doc/refman/5.7/en/optimizer-hints.html ##官方文档

    4.icp优化器算法

    作用： SQL层做完过滤后，只能用a,b的部分辅助索引，将c列条件的过滤下推到engine层进行再次过滤。 排除无用的数据页。 最终去磁盘上拿数据页。 大大减少无用IO的访问。
    开启方式
    select @@optimizer_switch; ## 查看是否开启了icp算法
    mysql> SET global optimizer_switch=’index_condition_pushdown=ON’
    4.1MRR : Multi Range Read
    作用： 减少回表。
    5.关于多表关联的算法
    SNLJ ：普通的嵌套循环连接
    5.1优化器如何选择驱动表？
    5.1.2优化器会自己选，选择结果集小的(数量少的)
    5.1.3人工也可以干预 left join 原则上可以强制驱动表。
    5.1.4按照on的条件列，是否有索引，索引的类型选择。
    5.1.5 在on条件中，优化器优先选择有索引的列为非驱动表。
    5.1.6如果两个列都有索引，优化器会按照执行的代价去选择驱动表和非驱动表。
    INLG ：普通索引多表连接如果是唯一索引，就有eq_ref的特性点查询
    BNLJ ：block nest loop join,一般是在被驱动表索引连接条件不走的时候
    在 A和B关联条件匹配时，不再一次一次进行循环。
    而是采用一次性将驱动表的关联值和非驱动表匹配.一次性返回结果 主要优化了， CPU消耗，减少了IO次数
    BNLJ+MRR就升级为 BKA
    BKA：主要作用，使用来优化非驱动表的关联列有辅助索引。 BNL+ MRR的功能。
    开启方式：
    mysql> set global optimizer_switch=’mrr=on,mrr_cost_based=off’;
    mysql> set global optimizer_switch=’batched_key_access=on’;
    8.0.18之后加入了一个hash_join算法
    8.0.20讲原来使用BNL算法的查询，转换为hash_join
    8 8.0 版本索引的新特性
    a. 不可见索引。invisable/visable index
    针对优化器不可见。但是索引还在磁盘存在，还会自动维护。
    对于索引维护时，不确定索引是否还有用。这时可以临时设定为invisable。
    ALTER TABLE t1 ALTER INDEX i_idx INVISIBLE;
    ALTER TABLE t1 ALTER INDEX i_idx VISIBLE;
    b. 倒序索引。
    select * from t1 where c = order by a ASC , b desc ;
    alter table t1 add index idx_name(c,a, b desc )

hash_join介绍:
被动表的关联条件，索引应用的不好，全表关联，没有太多谓词条件的时候，hash_join 必须包含等值条件，如果没有等值条件将会转换为 最普通的SNLP算法。
a jonin b on a.id=b.id
1.选择驱动表
2.选择优化器算法——>hasjoin
hash_join工作过程:
build阶段驱动表进行处理也就是a表构建hash表
select a.name,b.age from a join b on a.id=b.id
做hash运算(id列，关联列)，每个值都会生成一个 key,value 是相关查询列的名字(a.name,b.age)
probe探索阶段:
非驱动表的处理B表处理,对B表的关联列进行hash运算，用a表关联列的数据逐行扫描B表的数据(加载到内存进行扫描)，想等的数据进行展示。
注意hash会将数据拿到内存当中如果内存，不够用他会分批次的去加载到内存在进行关联，显示。