Mysql优化，ICP、BNL算法、BKA算法、MMR算法

ICP（Index Condition Pushdown，索引条件下推）是MySQL5.6版本中的新特性，是一种在存储引擎层使用索引过滤数据的一种优化方式。

  • 出现原因：ICP出现Mysql5.6以前，Mysql查询数据是通过索引查询到主键数据，然后再根据数据行回到Mysql Server层做Using where回表查询检索，这样子把查询回到了Mysql服务层中；Mysql5.6的ICP则是通过将此部分过滤条件直接放到存储引擎层完成，避免更多的回Mysql服务层做操作，通过存储引擎层把满足数据的行读取出
  • 目的：ICP能减少引擎层访问基表的次数和MySQL Server访问存储引擎的次数，减少IO次数，提高查询语句性能；减少全行读取次数；

场景

索引列：user_id_user_name(user_id, user_name)
  • desc select * from user where user_id = 1 and user_name like '%23%';
  • desc select user_id from user where user_id = 1 and user_name like '%23%';
  1. ICP开启时的执行计划含有Using index condition提示，表示优化器使用了ICP对数据访问进行优化
  2. ICP关闭时的执行计划显示Using where; Using index
优点
1. ICP的目标是减少从基表中读取操作的数量，从而降低IO操作
2. 当使用ICP优化时，执行计划Desc的Extra列显示Using index condition提示
3. 数据库配置set optimizer_switch='index_condition_pushdown=on';
局限性
1. Mysql5.6中支持MyISAM、InnoDB
2. ICP只能用于二级索引，不能用于主索引
3. 当SQL使用覆盖索引时但只检索部分数据时，ICP 无法使用。例如：select * from可以用到，而select user_id, user_name from 不能使用到ICP。必须查询整行数据
4. Mysql5.6中不支持分区表的ICP，从MySQL 5.7.3开始支持分区表的ICP
5. ICP的加速效果取决于在存储引擎内通过ICP筛选掉的数据的比例
5. ICP的优化策略可用于range、ref、eq_ref、ref_or_null 类型的访问数据方法

BNL（Block Nested-Loop Join）算法。NLJ的原理是内外两层循环，对外循环中的每条记录，都要再内循环中做一次检索。foreacht(){foreacht(){}}

两种情况会导致NLJ性能降低
1. 外循环的结果集大小
2. 内循环扫描数据的效率（所以增加在on上增加索引即是为了提升效率，这也是为什么on上要加索引的原因）

常见的优化方案是在驱动表（外循环）表上加上尽可能的where条件并创建合适索引，使得外循环的结果集更小，读取效率更高；内循环为了扫描效率提高，通常需要在关联字段上加索引

使用条件
1. 只有当join类型是all/index/range时才可以，也就是内表不适用索引或者索引效率很低时不得不使用到BNL
2. 对于使用到BNL特性且性能较差的SQL，建议在session级别将join_buffer_size临时增大来提高性能

 

BKA（Batched Key Access）算法

通过缓存外层循环读的行，来降低内层的读取次数。例如，10行数据读入buffer中，然后buffer被传递到内层循环，内层表读出的每一行都会跟这个缓存10行依次做对比，这样就降低了内层表数据的读取次数

  • 出现原因：因为BNL使用条件是内表关联字段没有索引或效率很低才不得不使用，但大部分join通常的效率较高的索引来做ref或eq_ref方式进行连接，这种情况下BNL无法使用到。为了优化Join，引入了MRR和BKA
BAK局限性，使用BKA来做Join，很多情况下可以提高连接效率，但对Join也有一定的条件限制
1. 一个条件是连接的列要求是唯一索引或普通索引，但不能是主键（这也是join on条件的字段一定要加索引的原因）
2. 另一个是要有对非主键列的查询操作，否则优化器就可以通过覆盖索引直接得到需要的数据，不需要回表

 

MRR（Multi-Range Read Optimization）MRR通过把随机磁盘读，转化为顺序磁盘读，从而提高了索引查询的性能。适用于range ref eq_ref类型的查询

  • 本质: MRR 在本质上是一种用空间换时间的算法
  • 原理：目的就是为了减少磁盘的随机访问，Innodb由于聚集索引的特点，如果查询使用辅助索引，并且用到表中非索引列，那么需要回表读取数据做后续处理，过于随机的回表会伴随着大量的随机IO。而MRR的优化并不是每次通过辅助索引读取到数据就回表，而是通过范围扫描将数据存入read_rnd_buffer_size,然后对其按照Primary Key排序，最后使用排序好的数据进行顺序回表，因为Innodb中叶子节点数据是按照PrimaryKey 排序，这样就转换随机IO为顺序IO了，对瓶颈为IO的SQL查询语句将带来极大的性能提升。

解读：MRR的特点类似在编程中，禁止使用循环去查询数据库的概念。而是先将结果集查询出来，然后通过排序，将排序的结果集存储在缓冲区内，当缓冲区满后，将使用该结果集再次去Mysql Server中查询，此时的结果集是已经排好序的，查询的时候也是顺序IO，查询速度加快，并且是一次查询，而非N次循环查询。

  • MRR的使用在单表和多表join查询中都可以使用，其中，单表通常通过范围查询；多表join方式如果是ref/eg_ref，则先通过BKA算法批量提取key到join buffer，然后将buffer中的key作为参数传入MRR的调用接口，MRR高效读取需要的数据返回。当优化器使用了MRR时，执行计划的 Extra 列会出现 "Using MRR"
  • 参数read_rnd_buffer_size用来控制rowid排序的内存的大小
SQL整体过程如下：
1. 优化器通过二级索引的记录放到一块缓冲区中
2. 如果二级索引扫描到文件的末尾或者缓冲区已满，则使用快速排序对缓冲区中的内容按照主键进行排序
3. 用户线程调用MRR接口取cluster index，然后根据cluster index 取行数据
4. 当根据缓冲区中的 cluster index取完数据，则继续调用过程 2) 3)，直至扫描结束
BKA是MySQL5.6引入的新算法，结合MRR特性进行高效Join操作，具体操作如下
1. 将外循环表中相关的列放入JoinBuffer中
2. 批量的将Key（索引键值）发送到MRR接口
3. MRR通过收到的Key，根据其对应的PrimaryKey进行排序，然后再根据排序后的PrimaryKey顺序读取聚集索引，得到需要的列数据
4. 返回结果集给客户端

 

posted @ 2021-01-28 23:02  将来-小志  阅读(908)  评论(0)  编辑  收藏  举报