V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
th3ee9ine
V2EX  ›  MySQL

解密 MySQL:索引—优化数据库性能的关键(二)

  •  
  •   th3ee9ine · 2023-11-15 17:10:41 +08:00 · 1308 次点击
    这是一个创建于 372 天前的主题,其中的信息可能已经有所发展或是发生改变。

    摘要:通过上一篇的内容,我们对 MySQL 索引的基础知识有了一个大致的了解,知道了如何创建与使用索引了,但是这些内容还不足以让我们用好索引,接下来我们将继续深入了解索引的其他知识点。

    上篇回顾:解密 MySQL:索引—优化数据库性能的关键(一)

    一、执行计划

    1 、什么是执行计划?

    执行计划( Execution Plan )是数据库管理系统( DBMS )在执行一个查询时生成的一种计划或路线图。这个计划描述了 DBMS 如何获取所需数据并执行查询,包括了操作的顺序、访问表的方式、索引的使用,以及连接操作等信息。执行计划是数据库查询优化器生成的,它的目标是使查询尽可能高效地执行,减少资源消耗和响应时间。

    执行计划通常以树形结构的形式表示,被称为执行计划树。这颗树包含了一系列执行操作,每个操作都代表了一个步骤,从中获取或处理数据,然后将数据传递给下一个步骤。这些操作可以包括表扫描、索引扫描、连接操作、排序、过滤等等。

    数据库查询优化器负责生成最优的执行计划,它会分析查询语句、表结构、索引的可用性等因素,以确定如何最有效地执行查询。生成的执行计划会被 DBMS 执行引擎用于实际执行查询操作。

    2 、如何生成和解释 MySQL 查询的执行计划?

    在 MySQL 中,您可以使用 EXPLAIN 语句来生成和解释查询的执行计划。

    在 MySQL 命令行或查询工具中,输入您要分析的查询语句,然后在查询语句之前加上 EXPLAIN 关键字。例如:

    
    EXPLAIN SELECT column1, column2 FROM your_table WHERE condition;
    
    # 例:
    EXPLAIN SELECT * FROM user_info WHERE id > 1;
    
    

    执行上述 EXPLAIN 语句。MySQL 将返回一个描述查询执行计划的表格,其中包括有关查询的各种信息,如操作顺序、表、索引、连接类型等。

    以下是执行计划中常见字段的含义:

    • id:操作的唯一标识符,按照查询执行的顺序分配的。通常,id 值越小,操作越先执行。
    • select_type:操作的类型,表示操作的性质。常见的值包括:
      • SIMPLE:简单的 SELECT 查询,不包含子查询或 UNION 。
      • PRIMARY:最外层查询。
      • SUBQUERY:子查询。
      • DERIVED:派生表,通常用于嵌套查询。
      • UNION:UNION 操作的第二个查询。
    • table:操作涉及的表的名称。这是指在查询中被访问的表。
    • partitions:指定分区的名称,如果查询涉及分区表。
    • type:查询的连接类型,描述了如何访问表的数据。常见的值包括:
      • system:仅有一行数据(常用于系统表)。
      • const:使用主键或唯一键访问一个行。
      • eq_ref:使用索引查找,通常用于连接操作。
      • ref:使用非唯一索引查找。
      • range:在索引范围内查找。
      • index:全表扫描索引。
      • all:全表扫描。
    • possible_keys:可以用于查询的潜在索引的列表。这些是查询优化器认为可能用于查询的索引。
    • key:实际选择用于查询的索引。这是实际用于查询的索引名称。
    • key_len:索引键的长度,表示在索引中使用的字节数。
    • ref:表示查询中使用的索引的比较值,通常与索引列和常数值相关。
    • rows:估计的匹配行数,指示查询期望扫描的行数。
    • filtered:对行的过滤百分比,即查询的结果集中的行数占估计行数的百分比。
    • Extra:包含其他有关查询执行的附加信息,例如是否使用了临时表、文件排序、临时文件等。

    这些字段提供了有关查询执行计划的详细信息,可以帮忙我们了解查询是如何执行的,是否使用了索引,连接类型是什么,以及是否存在潜在的性能瓶颈。通过分析这些字段,我们可以更好地优化查询和数据库性能。

    我们在后面的内容中,会频繁使用 EXPLAIN 语句,用于验证索引是否被正确使用。

    二、最左匹配原则

    1 、什么是最左匹配原则

    在 MySQL 建立联合索引时会遵守最左前缀匹配原则,即最左优先,在检索数据时从联合索引的最左边开始匹配。

    我们在上篇文章 解密 MySQL:索引—优化数据库性能的关键(一) 中,我们已经了解了索引的底层数据结构是一颗 B+树。

    由于构建一棵 B+树只能根据一个值来确定索引关系,所以数据库依赖联合索引最左的字段来构建。

    举例:创建一个( a,b )的联合索引,那么它的索引树就是下图的样子。

    最左前缀匹配原则

    可以看到 a 的值是有顺序的,1 ,1 ,2 ,2 ,3 ,3 ,而 b 的值是没有顺序的 1 ,2 ,1 ,4 ,1 ,2 。

    但是我们又可发现 a 在等值的情况下,b 值又是按顺序排列的,但是这种顺序是相对的。

    这是因为 MySQL 创建联合索引的规则是首先会对联合索引的最左边第一个字段排序,在第一个字段的排序基础上,然后在对第二个字段进行排序。

    所以 b=2 这种查询条件没有办法利用索引。

    2 、使用场景分析

    场景:user_info 表有一个( a ,b )的索引,下面的 sql 语句是否会走到这个索引:

    
    # sql1
    SELECT * FROM user_info WHERE a = '张三' AND b = '三年级';
    # sql2
    SELECT * FROM user_info WHERE b = '三年级' AND a = '张三';
    # sql3
    SELECT * FROM user_info WHERE b = '三年级' OR a = '张三';
    # sql4
    SELECT * FROM user_info WHERE a = '张三' AND b LIKE '三%';
    # sql5
    SELECT * FROM user_info WHERE a = '张三';
    # sql6
    SELECT * FROM user_info WHERE b = '三年级';
    # sql7 ,这里假设 b 字段,为出生日期
    SELECT * FROM user_info WHERE a = '张三' AND YEAR(b) = 2000;
    
    • sql1
      • 是否会走到( a ,b )索引:会
      • 分析:这个查询条件正好按照索引的顺序( a ,b )进行,因此可以充分利用这个索引。
    • sql2
      • 是否会走到( a ,b )索引:会
      • 分析:这个查询条件并不按照索引列的顺序,但是列都被索引包括,大多数数据库优化器能够重新排序条件,使其与索引相匹配。因此,也会走索引。
    • sql3
      • 是否会走到( a ,b )索引:不会
      • 分析:对于 OR 条件,大多数情况下数据库优化器可能不会选择使用联合索引。这是因为联合索引对于 OR 条件的优化并不是很高效。它更适合处理前缀匹配或范围查询。在这种情况下,可能会选择全表扫描而不是使用该联合索引。
    • sql4
      • 是否会走到( a ,b )索引:会
      • 分析:在这种情况下可以被使用,但是可能无法充分利用整个联合索引。通常情况下,索引 (a, b) 对于 a = '张三' 部分可以使用,但 b LIKE '三%' 可能会导致无法有效使用索引。在这种情况下,可能会先使用索引筛选 a = '张三',然后再对结果集进行后续过滤,这可能会导致较慢的查询性能。
    • sql5
      • 是否会走到( a ,b )索引:会
      • 分析:a = '张三' 与索引 (a, b) 的第一个列 a 完全匹配。这种情况下,MySQL 查询优化器通常会选择使用索引 (a, b),因为它可以快速定位到所有具有匹配值 '张三' 的行,而不需要全表扫描。
    • sql6
      • 是否会走到( a ,b )索引:不会
      • 分析:b = '三年级'涉及到索引 (a, b) 的第二个列 b ,而不涉及第一个列 a ,不符合最左匹配原则,所以不会走到该索引。
    • sql7
      • 是否会走到( a ,b )索引:不会
      • 分析:在这种情况下,函数 YEAR(b) 可能会阻止索引的使用,因为它涉及到列 b 的运算,导致无法直接使用索引来完成查询。

    3 、创建索引的一些建议

    创建联合索引的时候应该注意的问题:

    • 1 、选择合适的列:
      • 选择在查询中频繁一起出现的列来创建联合索引,以提高查询性能。
      • 避免在不常用于查询条件的列上创建索引,以免增加不必要的索引和维护成本。
    • 2 、列的顺序:
      • 确保列的顺序根据查询中的最常见顺序选择,使得索引能最有效地支持常见的查询条件。
      • 对于涉及范围查询的情况,索引列的顺序可能会影响查询性能。
    • 3 、避免过度索引:
      • 避免创建过多和过于具体的索引,这可能会导致维护成本增加,而不一定能提高所有查询的性能。
    • 4 、考虑覆盖索引(下一小节内容会具体介绍):
      • 对于特定查询,考虑创建包含所需查询的所有列的覆盖索引,以避免额外的访问实际数据行。
    • 5 、数据类型和长度:
      • 考虑索引列的数据类型和长度,过长的索引列可能会增加索引的大小和降低索引效率。
    • 6 、频繁更新的列:
      • 对于频繁更新的列,索引可能会增加维护成本。在此情况下,需要权衡索引的收益和维护成本。

    创建联合索引还是多个单列索引:

    • 1 、如果 a ,b ,c 经常作为单独的查询条件, 则可考虑分别为 a ,b ,c 创建单个索引
    • 2 、如果 a ,b ,c 经常按 a ,b ,c 联合形式查询, 则创建组合索引 (a ,b ,c), 这里包含索引: (a),(a ,b),(a ,b ,c),满足最左匹配原则
    • 3 、如果 a ,b ,c 经常按 a ,b 联合形式查询和单独查询 c ,则可考虑创建组合索引 (a ,b) 和 单独索引(c)

    三、前缀索引

    1 、什么是前缀索引?

    前缀索引是一种特殊的索引,它只针对索引列的前若干个字符创建索引,而不是整个列。通常用于处理文本类型的数据列。

    MySQL 数据库支持在字符串类型的列上创建前缀索引,语法如下:

    # 这里 length 表示创建索引的前缀长度。
    CREATE INDEX index_name ON table_name (column_name(length));
    # 例子
    CREATE INDEX idx_title_prefix ON article (title(10));
    

    前缀索引的特点如下:

    • 只针对字符串类型的列创建,且必须指定前缀长度。
    • 只索引列的前 length 个字符,可以大幅缩小索引大小。
    • 查询时只能使用索引列的前 length 个字符进行匹配查找。
    • InnoDB 引擎要求长度不能超过 767 字节。
    • 更适合查找重复度不高的静态数据列。
    • 可以优化 LIKE 查询的匹配速度。

    根据上面的特点,我们可以发现前缀索引更适用于查找变化较小的文本列,如名称、标题等静态信息。需要注意避免前缀值重复太多导致过滤效果不明显。总之,前缀索引是处理文本字段的重要手段,可以有效缩减索引体积,提升文本查找速度。

    四、回表( Index Lookups )

    在 MySQL 中,回表( Index Lookups )是指在使用非聚簇索引进行查询时,MySQL 需要根据该索引的键值去聚簇索引中查找对应的数据行的过程。

    举例来说:

    假设有一个包含学号(主键索引)、姓名和年龄的表,并在姓名字段上创建了非聚簇索引。当使用姓名索引进行查询时,MySQL 会首先在该索引中定位到满足条件的记录的主键值,然后再根据这些主键值去聚簇索引(通常是主键索引)中查找对应的数据行。这个额外的查找聚簇索引的过程就是回表。

    解密 MySQL:索引—优化数据库性能的关键(一) 文章中,我们了解到在 InnoDB 存储引擎中,非聚簇索引的 data 域存储相应记录主键的值而不是地址。所以导致在利用非聚簇索引进行查询时,需要进行回表。

    回表操作可能导致额外的 IO 开销,影响查询性能,特别是当查询的列不包含在非聚簇索引中时,具体造成的影响如下所示:

    • 性能下降:回表操作涉及额外的 IO 操作,需要访问聚簇索引来获取完整的数据行,导致查询性能下降。特别是在大规模数据表上或高并发的查询场景下,回表操作可能会成为性能瓶颈。
    • 增加数据库负载:回表操作会引起额外的数据库负载,包括磁盘读取和内存消耗。当频繁进行回表操作时,可能会导致数据库服务器的负载过高,影响整体性能。
    • 降低查询效率:由于回表需要额外的 IO 访问,查询的速度变慢,从而降低了查询效率,影响了用户体验。
    • 增加网络开销:如果数据库服务器和应用服务器位于不同的节点或机器上,回表操作会增加网络开销,进一步影响查询性能。

    为了优化查询性能,一般可以使用覆盖索引和索引条件下推来避免回表操作,提高查询效率。

    五、覆盖索引

    1 、什么是覆盖索引

    覆盖索引是指一个索引包含了查询所需的所有列,从而数据库可以直接使用索引返回查询结果,而不需要再去访问实际的数据行。

    举例:如果有一个包含列 A 和列 B 的表,并且创建了 (A, B) 的联合索引,如果某个查询仅仅需要使用列 A ,并且 A 已经包含在了索引中,那么查询就会利用到覆盖索引,直接从索引中获取所需的数据,而无需再访问实际数据行。

    覆盖索引的优势:

    • 减少磁盘 I/O 操作:因为不再需要访问实际的数据行,只需要通过索引即可获取所需的数据,从而提高了查询的性能。
    • 减少 CPU 和内存的使用:直接使用索引获取数据减少了数据库引擎处理数据的开销,从而提高了效率。
    • 提高查询性能:特别是在大型表和频繁查询的情况下,覆盖索引可以显著提高查询的性能。

    覆盖索引在以下情况下会被使用到:

    • 查询仅需要索引列:当查询中只涉及到覆盖索引中包含的列,数据库引擎可以直接使用覆盖索引返回结果,而无需再访问实际数据行。
    • 索引覆盖查询条件:如果查询条件匹配了索引中的列,覆盖索引可能会被使用。
    • 索引覆盖排序和聚合:当查询涉及排序或聚合操作时,如果覆盖索引包含了排序或聚合所需的列,数据库可能会使用覆盖索引来满足查询需求。

    2 、使用场景分析

    假设有一个 user_info 表,其中包括一个联合索引 (a, b) 和一个主键索引 (a)。下面的 sql 语句是否会使用覆盖索引:

    # sql1—仅需主键列的查询
    SELECT a FROM user_info WHERE a = 100;
    # sql2—范围查询涉及联合索引的第一个列
    SELECT a FROM user_info WHERE a > 100 AND a < 200;
    # sql3—基于联合索引的查询,只选择包含在索引中的列
    SELECT a, b FROM user_info WHERE a = 100;
    # sql4—按联合索引的第一个列进行排序
    SELECT * FROM user_info ORDER BY a;
    # sql5—使用聚合函数,仅涉及索引列
    SELECT COUNT(a) FROM user_info WHERE a > 50;
    # sql6—覆盖索引列满足查询条件
    SELECT a, b FROM table_name WHERE a = 100 AND b = 'other_value';
    # sql7—查询需要不在索引中的列
    SELECT a, c FROM table_name WHERE a = 100;
    # sql8—or
    SELECT a, b FROM table_name WHERE a = 100 OR b = 'other_value';
    
    • sql1
      • 是否会使用覆盖索引:会。
      • 分析:因为只查询了索引列 a 。
    • sql2
      • 是否会使用覆盖索引:会。
      • 分析:因为只查询了索引列 a 。
    • sql3
      • 是否会使用覆盖索引:会。
      • 分析:因为只查询了联合索引中的列 a 和 b 。
    • sql4
      • 是否会使用覆盖索引:不会。
      • 分析:因为查询了所有列,而不是仅索引列。
    • sql5
      • 是否会使用覆盖索引:不会。
      • 分析:因为使用了聚合函数 COUNT(),无法仅从索引中获取结果。
    • sql6
      • 是否会使用覆盖索引:会。
      • 分析:因为条件只涉及联合索引中的列 a 和 b 。
    • sql7
      • 是否会使用覆盖索引:不会。
      • 分析:因为查询了不在索引中的列 c 。
    • sql8
      • 是否会使用覆盖索引:不会。
      • 分析:因为 OR 条件会导致联合索引失效。

    使用覆盖索引的要点总结:

    • 只访问索引包含的列
    • 避免使用聚合函数
    • 使用符合最左前缀规则的条件表达式
    • 注意条件中不要包含 or
    • 尽量使用索引列进行排序

    六、ICP (Index Condition Pushdown)

    1 、什么是 ICP ( Index Condition Pushdown )?

    索引条件下推( Index Condition Pushdown ,ICP )是一种数据库查询优化技术,其主要作用是在查询执行过程中利用索引预先过滤数据,减少读取的数据量。

    通过 解密 MySQL:一条 SQL 语句的执行过程 文章,我们可以很容易的想到,索引条件下推是在 sql 执行的优化阶段进行的。但是想弄明白索引条件下推是什么,我们还需要了解一下,MySQL 的组成,具体如下图所示:

    MySQL 结构图

    从上图我们可以看出 MySQL 从上至下分为以下几层:

    • MySQL 服务层:包括 NoSQL 和 SQL 接口、查询解析器、优化器、缓存等组件。
    • 存储引擎层:各种插件式的表格存储引擎,实现事务、索引等各种存储引擎相关的特性。
    • 文件系统层: 读写物理文件。

    MySQL 服务层负责 SQL 语法解析、触发器、视图、内置函数、binlog 、生成执行计划等,并调用存储引擎层去执行数据的存储和检索。“索引下推”的“下”其实就是指将部分上层(服务层)负责的事情,交给了下层(存储引擎)去处理。

    其大致执行流程如下:

    • 在查询优化阶段,数据库查询优化器分析查询语句,找出其中可以利用索引过滤数据的条件。
    • 将这些过滤条件传递给存储引擎,让存储引擎在索引中应用这些条件进行预过滤。
    • 存储引擎使用索引按条件过滤数据,只返回符合条件的记录。
    • 数据库引擎接受过滤后的记录结果集,进一步处理查询。

    这样可以避免全表扫描,锁定需要读取的数据范围,有效利用索引提升查询效率。

    优点总结如下:

    • 减少存储引擎需要扫描的记录数。
    • 减少存储引擎需要读取的块数。
    • 减少在存储引擎与数据库引擎之间传输的记录数。

    2 、场景举例

    有一张 products 表,如下所示:

    CREATE TABLE products (
        id int(10) primary key,
        name varchar(100),
        price int(10),
        cate varchar(50),
        sold int(10)
    );
    
    CREATE INDEX idx_name ON products(name);
    CREATE INDEX idx_cate_sold ON products(cate, sold);
    

    假设我们需要执行下面这条 sql:

    select * from products
        where name like 'iphone%' 
          and cate = 'phone'
          and sold > 10000;
    

    不使用 ICP 的情况,执行计划如下所示:

    不使用 ICP

    通过执行计划,我们可以发现 MySQL 进行了全表扫描 all rows 和 Where 条件过滤。

    使用 ICP 的情况,执行计划如下所示:

    使用 ICP

    通过执行计划,我们可以发现 MySQL 利用索引 idx_name 和 idx_cate_sold 分别过滤,记录数降低,效率提高了。

    所以正确使用索引条件下推可以显著优化查询计划,减少无效记录访问。

    3 、支持索引下推的 MySQL 版本和存储引擎

    MySQL 中支持索引条件下推(ICP)的版本和存储引擎如下:

    版本方面:

    • MySQL 5.6 开始支持 ICP 。
    • MySQL 5.7 对 ICP 进行了增强和优化。
    • MySQL 8.0 继续改进了 ICP 的查询计划选择。

    所以建议使用 MySQL 5.6 及以上版本,可以利用 ICP 带来的查询性能提升。

    存储引擎方面:

    • InnoDB 引擎从 5.6 版本开始支持 ICP 。
    • MyISAM 不支持 ICP 。

    InnoDB 是 MySQL 的默认存储引擎,因此大多数情况下,只要使用 MySQL 5.6+ 版本与 InnoDB 引擎组合,就可以直接使用 ICP 。MyISAM 不支持 ICP 也是其逐步被 InnoDB 取代的一个重要原因之一。

    七、MRR (Multi-Range Read)

    1 、什么是 MRR

    在 MySQL ( 5.6 及更高版本,且使用 InnoDB 存储引擎) 中的 MRR 指的是 Multi-Range Read ,即多范围读取。MRR 是一种优化查询的技术,它可以在读取多个索引范围时减少磁盘 I/O 和 CPU 消耗。

    通常情况下,在执行查询时,MySQL 会遍历整个索引树,以找到所有匹配的行。但是,对于大型数据集,这种方式可能会导致性能下降,因为它需要大量的磁盘 I/O 和 CPU 资源。

    MRR 通过将索引分成多个范围并在内存中缓存结果来避免这种情况。在使用 MRR 时,MySQL 会尝试将查询范围分成多个不重叠的部分,并使用范围扫描技术来查找每个部分中的匹配行。这种方式可以有效地减少磁盘 I/O 和 CPU 消耗,从而提高查询性能。

    简单说:MRR 通过把「随机磁盘读」,转化为「顺序磁盘读」,从而提高了索引查询的性能。需要注意的是,MRR 适用于某些类型的查询,例如使用等于、大于、小于等操作符的查询。对于使用 LIKE 操作符的模糊查询,则不适合使用 MRR 。

    2 、MRR 开启与关闭

    通过参数 optimizer_switch 的标记来控制是否使用 MRR 。

    当设置 mrr=on 时,表示启用 MRR 优化。mrr_cost_based 表示是否通过 cost base 基于成本的方式来启用 MRR 。

    如果选择 mrr=on,mrr_cost_based=off 则表示总是开启 MRR 优化,参数 read_rnd_buffer_size 用来控制键值缓冲区的大小。

    默认情况下:mrr=on,mrr_cost_based=on

    3 、原理分析

    在不使用 MRR 时,优化器需要根据二级索引返回的记录来进行回表,这个过程一般会有较多的随机 I/O 。

    使用 MRR 时,SQL 语句的执行过程是这样的:

    • 1 )优化器将二级索引查询到的记录放到一块缓冲区中;
    • 2 )如果二级索引扫描到文件的末尾或者缓冲区已满,则使用快速排序对缓冲区中的内容按照主键进行排序;
    • 3 )用户线程调用 MRR 接口取聚簇索引,然后根据聚簇索引取行数据;
    • 4 )当根据缓冲区中的聚簇索引取完数据,则继续调用过程 2 ) 3 ),直至扫描结束;

    通过上述过程,优化器将二级索引随机的 I/O 进行排序,转化为主键的有序排列,从而实现了随机 I/O 到顺序 I/O 的转化,提升性能。

    八、BNL ( Block Nested-Loop )与 BKA ( Batched Key Access )

    1 、BNL

    MySQL 5.5 版本前,MySQL 本身只支持一种表间关联方式,就是嵌套循环( Nested-Loop )。如果关联表的数据量很大,则 join 关联的执行时间会非常长。

    在 5.5 版本中,MySQL 通过引入 BNL ( Block Nested-Loop Join )算法来优化嵌套执行。BNL 将外层循环的行/结果集存入到 join buffer ,然后每次遍历被驱动表都与 join buffer 中的数据进行比较,以此来减少全表扫描的次数。

    例如,下面这个语句:

    select * 
    from t1 straight_join t2 
        on t1.a = t2.b;
    

    假设字段 b 上是没有建立索引的。这时候,被驱动表上没有可用的索引,流程如下所示:

    • 1 、把表 t1 的数据读入线程内存 join_buffer 中,由于我们这个语句中写的是 select *,因此是把整个 t1 表放入了内存;
    • 2 、扫描表 t2 ,把表 t2 中的每一行取出来,跟 join_buffer 中的数据做对比,满足 join 条件的,作为结果集的一部分返回。

    虽然 BNL 算法是全表扫描,但是是在内存中进行的判断操作,速度上会快很多。

    join_buffer 的大小是由参数 join_buffer_size 设定的,默认值是 256k 。如果放不下表 t1 的所有数据话,则会进行分批处理,流程如下所示:

    • 1 )顺序读取数据行放入 join_buffer 中,直到 join_buffer 满了。
    • 2 )扫描被驱动表跟 join_buffer 中的数据做对比,满足 join 条件的,作为结果集的一部分返回。
    • 3 )清空 join_buffer ,重复上述步骤。

    虽然分成多次放入 join_buffer ,但是判断等值条件的次数还是不变的。

    2 、BKA

    MySQL 在 5.6 版本开始增加了提高表 join 性能的算法:Batched Key Access (BKA),即批量索引访问。

    BKA 其实就等价于 MRR + INLJ ( Index Nested-Loops Join ,基于索引的嵌套循环联接)。

    使用 BKA 的表的 JOIN 过程如下:

    • 1 )将外部表中相关的列放入 join_buffer 中。
    • 2 )批量的将 Key (索引键值)发送到 Multi-Range Read ( MRR )接口。
    • 3 ) Multi-Range Read ( MRR )通过收到的 Key ,根据其对应的 row_id 进行排序,然后再进行数据的读取操作。
    • 4 )返回结果集给客户端。

    可以看出,BKA 将有序主建投递到存储引擎是通过 MRR 的接口的调用来实现的,所以 BKA 依赖 MRR 。

    BNL 和 BKA 都是批量的提交一部分行给被 join 的表,从而减少访问的次数,那么它们有什么区别呢?

    • BKA 主要适用于 join 的表上有索引可利用时,无索引只能使用 BNL 。
    • BKA 主要是指在被 join 表上有索引可以利用,那么就在行提交给被 join 的表之前,对这些行按照索引字段进行排序,因此减少了随机 I/O 。

    参考资料

    • 高性能 MySQL(第三版)
    • MySQL 官方手册

    原文链接:解密 MySQL:索引—优化数据库性能的关键(二)

    微信公众号:啊杰在拱趴

    求关注!!!!

    6 条回复    2023-11-16 14:32:22 +08:00
    th3ee9ine
        1
    th3ee9ine  
    OP
       2023-11-15 17:11:41 +08:00
    新人公众号,求关注!!!
    微信公众号:啊杰在拱趴
    chenduke
        2
    chenduke  
       2023-11-15 20:03:38 +08:00
    在这个群普及基础的编程知识咋会有人看哦,这里都是一群大佬划水摸鱼的地方。
    th3ee9ine
        3
    th3ee9ine  
    OP
       2023-11-16 11:25:38 +08:00
    捞一下
    siweipancc
        4
    siweipancc  
       2023-11-16 13:42:46 +08:00 via iPhone
    比起这个我还是喜欢 pc 上那种博客,俗话说常看常新,基础知识不一定只能在新手时期看
    siweipancc
        5
    siweipancc  
       2023-11-16 13:43:57 +08:00 via iPhone
    给你个收藏,什么时候发第三节?
    th3ee9ine
        6
    th3ee9ine  
    OP
       2023-11-16 14:32:22 +08:00
    @siweipancc 一周左右吧,下一章可能是其他内容了,不是索引了。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   3360 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 25ms · UTC 11:38 · PVG 19:38 · LAX 03:38 · JFK 06:38
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.