MySQL索引自优化算法

编程 · 02-22 · 119 人浏览

InnoDB架构图:
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
Theme Jasmine by Kent Liao