mysql

MySQL 系列 内核优化

MySQL 技术内幕：InnoDB存储引擎

Posted by lichao modified on October 12, 2023

为了加快查询速度，针对一些特殊的场景，MySQL 分别做了相关的优化；

MySQL Join的原理与实践

MySQL只支持一种join算法：Nested-Loop Join（嵌套循环连接），但Nested-Loop Join有三种变种；

Simple Nested-Loop Join

简单来说嵌套循环连接算法就是一个双层for循环 ，通过循环外层表的行数据，逐个与内层表的所有行数据进行比较来获取结果，当执行select * from user tb1 left join level tb2 on tb1.id=tb2.user_id时，整个匹配过程会如下图： 索引示例 Simple Nested-Loop Join 简单粗暴容易理解，就是通过双层循环比较数据来获得结果，但是这种算法显然太过于粗鲁，如果每个表有1万条数据，那么对数据比较的次数=1万 * 1万 =1亿次，很显然这种查询效率会非常慢。当然MySQL肯定不会这么粗暴的去进行表的连接，所以就出现了后面的两种对Nested-Loop Join优化算法，在执行join查询时MySQL会根据情况选择后面的两种优join优化算法的一种进行join查询。

Index Nested-Loop Join（索引嵌套循环连接）

Index Nested-Loop Join的优化思路主要是为了减少内层表数据的匹配次数， 简单来说Index Nested-Loop Join 就是通过外层表匹配条件 直接与内层表索引进行匹配，避免和内层表的每条记录去进行比较， 这样极大的减少了对内层表的匹配次数，从原来的匹配次数=外层表行数*内层表行数，变成了外层表的行数*内层表索引的高度，极大的提升了join的性能。

例如查询 select * from user tb1 left join level tb2 on tb1.id=tb2.user_id时, 当level表的user_id为索引的时候执行过程会如下图： 索引示例 注意和Simple Nested-Loop Join的区别就是user_id属于user_level的索引列，因此无需遍历user_level即可找到与外表user_info的id相同的record。

Block Nested-Loop Join（缓存块嵌套循环连接）

Block Nested-Loop Join 其优化思路是减少内层表的扫表次数，通过简单的嵌套循环查询的图，我们可以看到，左表的每一条记录都会对右表进行一次扫表，扫表的过程其实也就是从内存读取数据的过程，那么这个过程其实是比较消耗性能的。 索引示例

所以缓存块嵌套循环连接算法意在通过一次性缓存外层表的多条数据，以此来减少内层表的扫表次数，从而达到提升性能的目的。如果无法使用Index Nested-Loop Join的时候，数据库是默认使用的是Block Nested-Loop Join算法的。

当level 表的 user_id 不为索引的时候，默认会使用Block Nested-Loop Join算法，匹配的过程类似下图。 索引示例

  1. 使用Block Nested-Loop Join算法需要开启优化器管理配置的optimizer_switch的设置block_nested_loop为on默认为开启，如果关闭则使用Simple Nested-Loop Join算法；
  2. 设置join buffer的大小，通过join_buffer_size参数可设置join buffer的大小；

小结

不论是Index Nested-Loop Join还是Block Nested-Loop Join都是在Simple Nested-Loop Join的算法的基础上进行优化，这里Index Nested-Loop Join和Nested-Loop Join算法是分别对Join过程中循环匹配次数和IO次数两个角度进行优化。

Index Nested-Loop Join是通过索引的机制减少内层表的循环匹配次数达到优化效果，而Block Nested-Loop Join是通过一次缓存多条数据批量匹配的方式来减少外层表的IO次数，同时也减少了内层表的扫表次数，通过理解join的算法原理我们可以得出以下表连接查询的优化思路。

  1. 永远用小结果集驱动大结果集(其本质就是减少外层循环的数据数量)
  2. 为匹配的条件增加索引(减少内层表的循环匹配次数)
  3. 增大join buffer size的大小（一次缓存的数据越多，那么内层包的扫表次数就越少）
  4. 减少不必要的字段查询（字段越少，join buffer 所缓存的数据就越多）

索引条件下推（Index Condition Pushdown）

优化背景

什么是回表? 查询二级索引获得主键后根据主键值从聚集索引中获取到完整记录到过程叫做回表。

1
2
3
4
5
6
7
8
9

CREATE TABLE person_info(
        id INT NOT NULL auto_increment,
        name VARCHAR(100) NOT NULL,
        birthday DATE NOT NULL,
        phone_number CHAR(11) NOT NULL,
        country varchar(100) NOT NULL,
        PRIMARY KEY (id),
        KEY idx_name_birthday_phone_number (name, birthday, phone_number)                                                 
);

查询语句是:

1

SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow';

这个时候会使用二级索引进行查询，具体到查询步骤：

  1. 从二级索引idx_name_birthday_phone_number中获取到满足条件（name > 'Asa' AND name < 'Barlow'）的记录。
  2. 由于需要返回所有的列但是二级索引中只包含几列（name, birthday, phone_number），需要根据从二级索引中获取到的主键去聚集索引中获取完整的记录返回给用户。

由于二级索引中的值会根据列值进行排序，所以第一步从二级索引中获取数据时，获取的数据在磁盘上的存储是连续的，分布在一个或者相邻的几个数据页中，这样对于磁盘的读取是顺序io。

而在第二个步骤中由于从第一个步骤中获取到的每一条记录的主键值大概率都不相同，而聚集索引是按照主键排序的，这个时候访问聚集索引中的每一条记录都大概率分布在不同的数据页中，对于磁盘的读取就大概率是随机io。

相信大家都知道对于磁盘而言，随机io通常都比顺序io慢很多。总结一下，发生回表的时候首先使用两个索引，一个二级索引，一个聚集索引。同时二级索引上的磁盘io是顺序io，聚集索引上的磁盘io是随机io。这样就会出现一个现象就是需要回表的记录越多，使用二级索引的效率就越低。

优化思路

MySQL 5.6引入的索引下推优化（index condition pushdown)。在满足最左匹配的基础上，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

Index Condition Pushdown是一种根据索引进行查询的优化方式。在支持Index Condition Pushdown之后，MySQL数据库会在取出索引的同时，判断是否可以进行where条件的过滤，也就是将 where 的部分过滤操作放在了存储引擎层。

1

SELECT * FROM s1 WHERE name > 'Asa' AND name LIKE '%b';
  • 如果没有使用index condition pushdown这个优化的时候。查询步骤是这样的:
    1. 先根据过滤条件name > 'Asa'将符合条件的记录从二级索引中找出来。
    2. 根据从二级索引中获取到记录的主键值回表从聚集索引中获取到完整的数据。再检测记录是不是满足这个name LIKE '%b'过滤条件，符合的记录返回给用户。 可以看到上述过程中name > 'Asa'可以使用到索引，name LIKE '%b'却不能使用到索引。
  • 使用index condition pushdown优化的时候，查询步骤是这样的：
    1. 先根据过滤条件name > 'Asa'将符合条件的记录从二级索引中找出来。
    2. 获取到二级索引的记录后先不回表，先检测一下是不是满足name LIKE '%b'这个过滤条件，符合条件的再进行回表。 之前我们提到了回表操作是一个耗时的过程，使用了index condition pushdown这个优化后可以减少回表的记录，从而加快了查询速度。

Fast Index Creation 快速索引创建

对于辅助索引的创建，InnoDB存储引擎会对创建索引的表加一个S锁。在创建的过程中，不需要重建表，因此速度较之前提高很多，并且数据库的可用性也得到了提高。 由于加了S锁，在创建过程中只能对该表进行读操作。 对于主键的创建和删除需要重建一张表。

Multi-Range Read 多范围读

Multi-Range Read 优化的目的是减少磁盘的随机访问，并且将随机访问转化为较为顺序的数据访问。 MySQL 将根据辅助索引获取的结果集根据主键进行排序，将随机访问化为较为顺序的数据访问，可以按照主键顺序书签查找（回表） 按照主键顺序进行访问，可以避免频繁的离散读操作导致的缓存中页被替换出缓存池，然后又不断写入缓存池的现象。 MRR还可以将某些范围查询，将查询条件进行拆分，拆分为键值对（在拆分的过程中，直接过滤一些不符合查询条件的数据），以此来进行批量的数据查询。

Index Merge

一般情况下我们只能利用单个二级索引进行查询。接下来我们提到的index merge就是一种特殊的情况，在这种特殊情况下可以使用多个二级索引查询。

Index merge主要分为以下三类：

  • intersection merge（交集合并）

intersection merge（交集合并）

一个查询可以使用多个二级索引查询，返回给用户的结果取多个二级索引查询的结果的交集。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

CREATE TABLE single_table (
        id INT NOT NULL AUTO_INCREMENT,
        key1 VARCHAR(100),
        key2 INT,
        key3 VARCHAR(100),
        key_part1 VARCHAR(100),
        key_part2 VARCHAR(100),
        key_part3 VARCHAR(100),
        common_field VARCHAR(100),
        PRIMARY KEY (id),
        KEY idx_key1 (key1),
        UNIQUE KEY idx_key2 (key2),
        KEY idx_key3 (key3),
        KEY idx_key_part(key_part1, key_part2, key_part3)                                          
) Engine=InnoDB CHARSET=utf8;

# 查询sql是：
SELECT * FROM single_table WHERE key1 = 'a' AND key3 = 'b';
  • 不使用intersection merge优化时候的查询步骤：
    1. 从idx_key1二级索引中获取满足过滤条件的记录。
    2. 根据idx_key1二级索引获取到的主键值回表，获取到完整的记录数据后判断key3 = ‘b’过滤条件是否满足。

    或者

    1. 从idx_key3二级索引中获取满足过滤条件的记录。
    2. 根据idx_key3二级索引获取到的主键值回表，获取到完整的记录数据后判断key1 = ‘a’过滤条件是否满足。
  • 使用intersection merge优化时候的查询步骤：
    1. 首先从idx_key1二级索引中获取到满足过滤条件的记录
    2. 再从idx_key3二级索引中获取满足过滤条件的记录
    3. 计算出这两个二级索引记录的主键交集，根据交集中的主键值回表将完整记录从聚集索引中读取出来返回给用户。
  • 使用intersection merge的条件：
    • 二级索引是等值匹配，对于联合索引需要联合索引的每个列都等值匹配;
    1
2
3
4
5
6

    # 可以使用 intersection merge                                        
SELECT * FROM single_table WHERE key1 = 'a' AND key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c'; 
# 不可以使用intersection merge
SELECT * FROM single_table WHERE key1 > 'a' AND key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c'; 
# 不可以使用intersection merge
SELECT * FROM single_table WHERE key1 = 'a' AND key_part1 = 'a';
    • 主键列可以是范围匹配;
    1
2

    #可以使用intersection merge
SELECT * FROM single_table WHERE id > 100 AND key1 = 'a';

对于 InnoDB 的二级索引来说，记录先是按照索引列进行排序，如果该二级索引是一个联合索引，那么会按照联合索引中的各个列依次排序。而二级索引的用户记录是由 索引列 + 主键 构成的，二级索引列的值相同的记录可能会有好多条，这些索引列的值相同的记录又是按照主键的值进行排序的。所以之所以在二级索引列都是等值匹配的情况下才可能使用 Intersection索引合并，是因为只有在这种情况下根据二级索引查询出的结果集是按照主键值排序的。这样的好处是因为排序后求交集的效率高。

另外，不仅是多个二级索引之间可以采用 Intersection 索引合并，可以有聚簇索引参加，也就是我们上边写的情况二 :在搜索条件中有主键的范围匹配的情况下也可以使用 Intersection 索引合并索引合并。

假设这个查询可以采用 Intersection 索引合并，我们理所当然的以为这个查询会分别按照 id > 100 这个条件从聚簇索引中获取一些记录，在通过 key1 = ‘a’ 这个条件从 idx_key1 二级索引中获取一些记录，然后再求交集， 其实这样就把问题复杂化了，没必要从聚簇索引中获取一次记录。别忘了二级索引的记录中都带有主键值的，所以可以在从 idx_key1 中获取到的主键值上直接运用条件 id > 100 过滤就行了，这样多简单。所以涉及主键的搜索条件只不过是为了从别的二级索引得到的结果集中过滤记录罢了，是不是等值匹配不重要。

union merge（并集合并）

一个查询可以使用多个二级索引查询，返回给用户的结果取多个二级索引查询的结果的并集。

交集合并更适合不同索引的过滤条件之间使用and连接的情况。那并集合并就更适合不同过滤条件之间是使用or连接的情况，例如：

1

SELECT * FROM single_table WHERE key1 = 'a' OR key3 = 'b'

使用union merge的条件：

  1. 二级索引是等值匹配，对于联合索引需要联合索引的每个列都等值匹配
  2. 主键列可以是范围匹配
  3. 使用intersection meger的过滤条件
1

SELECT * FROM single_table WHERE key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c' OR (key1 = 'a' AND key3 = 'b');

优化器可能采用这样的方式来执行这个查询:

  1. 先按照搜索条件key1 = 'a' AND key3 = 'b'从索引idx_key1和idx_key3中使用 Intersection 索引合并的方式得到一个主键集合。
  2. 再按照搜索条件key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c'从联合索引idx_key_part中得到另一个主键集合。采用 Union 索引合并的方式把上述两个主键集合取并集，然后进行回表操作，将结果返回给用户。

sort-union merge （排序并集合并）

一个查询可以使用多个二级索引查询，先将多个二级索引查询结果按照主键值排序， 之后返多个二级索引查询的结果的并集返回给用户。

由于union merge的使用条件太苛刻，必须保证每个二级索引都是等值匹配才能使用，而下面这个例子中的查询就无法使用union merge： SELECT * FROM single_table WHERE key1 < ‘a’ OR key3 > ‘z’; 无法使用的原因是因为从每个二级索引获取到记录的主键值不是排序的。因此出现了sort-union merge这种优化，具体执行的步骤：

  1. 首先根据过滤条件从idx_key1二级索引中获取记录并且根据主键值排序
  2. 然后根据过滤条件从idx_key3二级索引中获取记录并且根据主键值排序
  3. 最后将两个结果集取并集后回表。
CATALOG
    FEATURED TAGS
    redis go mysql algorithm distribute jvm network spring java kafka mongo mq concurrent index design_pattern io rocket_mq store netty os concurrency nosql mesh etcd graph hbase kitex lang zookeeper
    FRIENDS