InnoDB架构图：
查看优化器算法开关：select @@optimizer_switch;
修改，例如：set global optimizer_switch='batched_key_access=on';，退出客户端，重新进入客户端查看修改结果。
设置方法也可以使用优化器提示Hints的形式，或者设置my.cnf配置文件。
索引自优化
AHI
Adaptive Hash Index，即自适应哈希索引，作用是自动评估热内存索引page，生成Hash索引表，帮助InnoDB快速读取索引页，加快索引读取的速度，相当于索引的索引。
Change Buffer
比如insert、update、delete数据，对于聚簇索引会立即更新，而对于辅助索引不是实时更新的。
在InnoDB内存结构中，加入了Insert Buffer，现在版本叫Change Buffer。Change Buffer功能是临时缓冲辅助索引需要的数据更新。当我们需要査询新insert的数据，会在内存中进行merge合并操作，此时辅助索引就是最新的。
优化器算法
ICP
Index Condition Pushdown，即索引下推，作用是解决联合索引只能部分应用的情况。为了减少没必要的数据页被扫描，将不走索引的条件，在engine层取数据之前先做二次过滤，一些无关数据就会被提前过滤掉。
例如，有一个索引index(a,b,c)，查询语句：select * from t where a= and c=，在server层先做a列过滤条件的索引优化，在将c列的过滤下推到engine层先做过滤，再加载数据页。
MRR
Multi-Range Read，是优化器将随机磁盘IO转化为顺序磁盘IO以降低查询过程中IO开销的一种手段，是一种回表优化。
回表是指先走二级索引，找到对应的主键值，再根据主键值再到聚集索引当中，拿到一行行的数据。由于二级索引上引用的主键值不一定是有序的，因此有可能造成大量的随机IO，如果回表前把主键值给排下序，那么在回表时就可以用顺序IO取代原本的随机IO。
具体可参考：https://mariadb.com/kb/en/multi-range-read-optimization/
SNLJ
Index Nested-Loop Join，INLJ算法，即嵌套循环连接，常用于连接小表或是一个表中数据量不大的子集。具体实现方式是先遍历外表，对于每一行，再遍历内表，找到符合连接条件的行，返回结果。
Simple Nested-Loop Join，即SNLJ算法。具体实现方式是驱动表全表扫描取出所有字段，逐行匹配被驱动表，而被驱动表未使用到索引，每次匹配页都进行一次全表扫描。
BNL
Block Nested-Loop Join，即块嵌套循环连接。该算法使用了join buffer作为优化，其中由参数join_buffer_size设定join_buffer大小，默认值是256K。
在A表和B表关联条件匹配时，不再一次一次进行循环，而是采用一次性将驱动表的关联值和非驱动表匹配.一次性返回结果。BNLJ算法主要优化了CPU消耗，减少了IO次数。
BKA
Batched Key Access，主要是用来优化非驱动表关联列有辅助索引，相当于BNL+MRR的功能。要启动KBA算法优化，先设置：set global optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';，退出客户端，重新进入客户端查看修改结果。
- 转载请保留原文链接：MySQL索引自优化算法 https://aboss.top/post/658/
- 本站所有资源文章出自互联网收集整理，本站不参与制作，如果侵犯了您的合法权益，请联系本站我们会及时删除。
- 本站发布资源来源于互联网，可能存在水印或者引流等信息，请用户擦亮眼睛自行鉴别，做一个有主见和判断力的用户。
- 本站资源仅供研究、学习交流之用，若使用商业用途，请购买正版授权，否则产生的一切后果将由下载用户自行承担。
- 联系方式（#替换成@）：mail#aboss.top
评论