• MySQL 多表关联查询优化实践和原理解析

    目录

    一、前言

          索引是为了高效查询排好序的数据结构，当表数据量到达一个量级没有对应索引帮助查询耗时会很长，MySQL资源开销也会非常大，对于多表关联查询来说没有对应索引辅助查询资源开销是灾难级的，当然索引也不能随意创建，要做到尽量少的索引解决尽量多的问题，这里会对一些业务场景做索引优化演示，也会讲解多表关联查询的底层原理。

    二、表数据准备

    这里准备10w条订单数据和1000条用户数据，数据量越大能看到的效果越明显。

    • 订单信息表和数据准备
    # 创建订单信息表
DROP TABLE IF EXISTS `order_info`;
CREATE TABLE `order_info` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '订单ID',
  `order_no` varchar(100)  NOT NULL COMMENT '订单编号',
  `customer_id` bigint(20) NOT NULL COMMENT '客户ID',
  `customer_no` varchar(100) NOT NULL COMMENT '客户编号',
  `goods_id` bigint(20) NOT NULL COMMENT '商品ID',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB COMMENT='订单信息表';

## 创建一个插入数据的存储过程
DROP PROCEDURE IF EXISTS insert_order_procedure;
delimiter;;
CREATE PROCEDURE insert_order_procedure() 
BEGIN
  DECLARE i INT DEFAULT 1;
  DECLARE customer_id BIGINT;
  DECLARE t_error INTEGER DEFAULT 0;
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;
  START TRANSACTION;
  WHILE ( i <= 100000 ) DO
    SET customer_id = CEIL(RAND() * 1000);
    INSERT INTO `order_info`(`order_no`,`customer_id`,`customer_no`, `goods_id`, `create_time`) VALUES (CONCAT('ON00000',i), customer_id, CONCAT('C00000',customer_id), CEIL(RAND() * 100), NOW());
    SET i = i + 1;
    
  END WHILE;
  IF t_error=1 THEN
    ROLLBACK;
  ELSE
    COMMIT;
  END IF;
END;;
delimiter;

# 调用存储过程插入数据 我本地插入10w条数据耗时20s
CALL insert_order_procedure();

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 客户信息表和数据准备
    # 创建客户信息表
DROP TABLE IF EXISTS `customer_info`;
CREATE TABLE `customer_info` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '客户ID',
  `customer_no` varchar(100) DEFAULT NULL COMMENT '客户编号',
  `nick_name` varchar(20) DEFAULT NULL COMMENT '昵称',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='客户信息表';

## 创建一个插入数据的存储过程
DROP PROCEDURE IF EXISTS insert_customer_procedure;
delimiter;;
CREATE PROCEDURE insert_customer_procedure() 
BEGIN
  DECLARE i INT DEFAULT 1;
  DECLARE t_error INTEGER DEFAULT 0;
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;
  START TRANSACTION;
  WHILE ( i <= 1000 ) DO
	  INSERT INTO `customer_info`(`customer_no`,`nick_name`, `age`, `create_time`) VALUES (CONCAT('C00000',i),CONCAT('Kerwin',i), CEIL(RAND() * 100), NOW());
    SET i = i + 1;
  END WHILE;
  IF t_error=1 THEN
    ROLLBACK;
  ELSE
    COMMIT;
  END IF;
END;;
delimiter;

# 调用存储过程插入数据
CALL insert_customer_procedure();

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34

    三、表关联查询原理和两种算法

          在MySQL中表关联查询有两种算法，在关联字段有索引时使用的是 嵌套循环连接 Nested-Loop Join(NLJ) 算法，在没有索引时使用的是 基于块的嵌套循环连接 Block Nested-Loop Join(BNL)算法，下面会对两种算法做解释。

    3.1、研究关联查询算法必备知识点

    研究关联查询首先要知道什么是驱动表和什么是被驱动表，MySQL常用三种关联查询分别为左外连接（LEFT JOIN ON）、右外连接（RIGHT JOIN ON）、内连接（INNER JOIN ON），对于这三种关联查询驱动表和被驱动表也是不同的，下面举例说明。

    • 左外连接（LEFT JOIN ON）
    EXPLAIN SELECT * FROM order_info t1 LEFT JOIN customer_info t2 ON t1.customer_no=t2.customer_no;

    • 1

    在这里插入图片描述

    执行计划中如果id相同那么执行顺序是由上到下，在执行计划里我们可以看到 t1(order_info)在上，t2(customer_info)在下，关联查询中谁在上那么谁就是驱动表，所以 t1(order_info)为驱动表，t2(customer_info)为被驱动表。

    • 右外连接（RIGHT JOIN ON）
    EXPLAIN SELECT * FROM order_info t1 RIGHT JOIN customer_info t2 ON t1.customer_no=t2.customer_no;

    • 1

    在这里插入图片描述

    执行计划中如果id相同那么执行顺序是由上到下，在执行计划里我们可以看到t2(customer_info)在上， t1(order_info)在下，关联查询中谁在上那么谁就是驱动表，所以 t2(customer_info)为驱动表，t1(order_info)为被驱动表。

    • 内连接（INNER JOIN ON）

      • 查看执行计划SQL
      EXPLAIN SELECT * FROM order_info t1 INNER JOIN customer_info t2 ON t1.customer_no=t2.customer_no;

      • 1
      • 两张表的customer_no字段都没有索引
        在这里插入图片描述
      • 两张表的customer_no字段都有索引
        # 添加索引
ALTER TABLE `customer_info` ADD INDEX `idx_customerNo`(`customer_no`);
ALTER TABLE `order_info` ADD INDEX `idx_customerNo`(`customer_no`);

        • 1
        • 2
        • 3
        在这里插入图片描述
      • customer_info表有customer_no字段索引order_info表没有
        # 删除索引
ALTER TABLE `order_info` DROP INDEX `idx_customerNo`;

        • 1
        • 2
        在这里插入图片描述
      • order_info表有customer_no字段索引customer_info表没有
        # 删除索引
ALTER TABLE `customer_info` DROP INDEX `idx_customerNo`;
# 添加索引
ALTER TABLE `order_info` ADD INDEX `idx_customerNo`(`customer_no`);

        • 1
        • 2
        • 3
        • 4
        在这里插入图片描述

    • 总结
      驱动表和被驱动表的辨认方式就在其执行顺序，在上的是驱动表，在下的是被驱动表，左连接查询那么左边的表一定是驱动表，右连接查询那么右边的表一定是驱动表，内连接会根据表的大小还有是否有索引来选择驱动表和被驱动表，因为customer_info只有1000条数据而order_info表有10w条数据在两张表都没有索引的情况下内连接选择了customer_info做驱动表，在两张表都有索引的情况下会内连接选择小表做驱动表，在一张表有索引一张表没有索引的情况下内连接会选择没有索引的表作为驱动表，原因会在下面说明。

    3.2、嵌套循环连接 Nested-Loop Join(NLJ) 算法

           嵌套循环连接算法一次一行循环地从第一张表（称为驱动表）中读取行，在这行数据中取到关联字段，根据关联字段在另一张表（被驱动表）里取出满足条件的行，然后取出两张表的结果合集，嵌套循环连接中被驱动表关联字段一定是有索引，如果没有索引那用的就是基于块的嵌套循环连接算法，下面演示一下。

    • 因为嵌套循环连接需要索引，这里先对order_info表的customer_no创建一个普通索引：
    # 如果没有删除customer_info表中的idx_customerNo索引需要先删除
ALTER TABLE `customer_info` DROP INDEX `idx_customerNo`;
# 添加order_info表索引
ALTER TABLE `order_info` ADD INDEX `idx_customerNo`(`customer_no`);

    • 1
    • 2
    • 3
    • 4
    • 查看执行计划
    EXPLAIN SELECT * FROM order_info t1 INNER JOIN customer_info t2 ON t1.customer_no=t2.customer_no;

    • 1

    在这里插入图片描述
    这里可以看到内连接使用了customer_info作为驱动表，使用order_info作为被驱动表，还使用到了我们刚刚创建的idx_customerNo索引。

    • 上面sql的大致流程如下：
      • 1、从表 t2 中读取一行数据（如果t2表有查询过滤条件的，会从过滤结果里取出一行数据。
      • 2、从第 1 步的数据中，取出关联字段 customer_no，到表 t1 中查找。
      • 3、取出表 t1 中满足条件的行，跟 t2 中获取到的结果合并，作为结果返回给客户端。
      • 4、 重复上面 3 步。

           整个过程会读取 t2 表的所有数据(扫描1000行)，然后遍历这每行数据中字段 customer_no 的值，根据 t2 表中 customer_no 的值索引扫描 t1 表中的对应行(扫描1000次 t1 表的索引，因为我们这里是一对多关系1次扫描可能最终扫描 t1 表对应多行完整数据，如果是一对一关系，也就是总共 t1 表也扫描了1000行)，为了方便计算假设一个用户编号只能下一个订单因此整个过程扫描了 2000 行。
           如果被驱动表的关联字段没索引，使用NLJ算法性能会比较低(下面有详细解释)，MySQL会选择Block Nested-Loop Join算法。

    3.3、基于块的嵌套循环连接 Block Nested-Loop Join(BNL)算法

    基于块的嵌套循环连接算法和嵌套循环连接算法区别就在于被驱动表的关联字段没索引，基于块的嵌套循环连接算法会把驱动表的数据读入到 join_buffer 中，然后扫描被驱动表，把被驱动表每一行取出来跟 join_buffer 中的数据做对比。

    • 先将表中的二级索引全删除
    # 删除索引
ALTER TABLE `customer_info` DROP INDEX `idx_customerNo`;
ALTER TABLE `order_info` DROP INDEX `idx_customerNo`;

    • 1
    • 2
    • 3

    • 查看执行计划
      在这里插入图片描述
      这里可以看到内连接使用了customer_info作为驱动表，使用order_info作为被驱动表，而且在Extra中出现了Using join buffer (Block Nested Loop)说明该关联查询使用的是 BNL 算法。

    • 上面sql的大致流程如下：

      • 1、把 t2 的所有数据放入到 join_buffer 中
      • 2、把表 t1 中每一行取出来，跟 join_buffer 中的数据做对比
      • 3、返回满足 join 条件的数据

           整个过程对表 t1 和 t2 都做了一次全表扫描，因此扫描的总行数为100000(表 t1 的数据总量) + 1000(表 t2 的数据总量) =101000。并且 join_buffer 里的数据是无序的，因此对表 t1 中的每一行，都要做 1000 次判断，所以内存中的判断次数是1000 * 100000= 1 亿次。

           这个例子里表 t2 才 1000 行，要是表 t2 是一个大表，join_buffer 放不下怎么办呢？join_buffer 的大小是由参数 join_buffer_size 设定的，默认值是 256k。如果放不下表 t2 的所有数据话，策略很简单，就是分段放。

           比如 t2 表有1000行记录， join_buffer 一次只能放800行数据，那么执行过程就是先往 join_buffer 里放800行记录，然后从 t1 表里取数据跟 join_buffer 中数据对比得到部分结果，然后清空 join_buffer ，再放入 t2 表剩余200行记录，再次从 t1 表里取数据跟 join_buffer 中数据对比。所以就多扫了一次 t1 表。

    3.4、被驱动表的关联字段没索引为什么要选择使用 BNL 算法而不使用 Nested-Loop Join 呢？

           如果上面第二条sql使用 Nested-Loop Join，那么扫描行数为 1000 * 100000 = 1亿次，这个是磁盘扫描。很显然，用BNL磁盘扫描次数少很多，相比于磁盘扫描，BNL的内存计算会快得多。
           因此MySQL对于被驱动表的关联字段没索引的关联查询，一般都会使用 BNL 算法。如果有索引一般选择 NLJ 算法，有索引的情况下 NLJ 算法比 BNL算法性能更高。

    四、多表关联查询优化实践

           在前面说明了表关联查询原理和两种算法，其实基本优化思路就已经有了，那就是一定要小表驱动大表，给表关联字段加索引，下面会对一些情况做举例说明。

    PS：这里例子使用的MySQL版本为5.7，MySQL8.0连接查询看着是没什么变化但是底层好像做了一些优化，我试了几个例子有索引反而会比没索引慢一点，

    4.1、使用左连接查询 全部订单列表信息返回订单编号和客户昵称

    # 删除索引
ALTER TABLE `customer_info` DROP INDEX `idx_customerNo`;
ALTER TABLE `order_info` DROP INDEX `idx_customerNo`;

    • 1
    • 2
    • 3
    SELECT t1.order_no,t2.nick_name FROM order_info t1 LEFT JOIN customer_info t2 ON t1.customer_no=t2.customer_no;

    • 1

    • 无索引情况查询测试
      在这里插入图片描述
      在这里插入图片描述
      使用左连接查询无索引时耗时12.497s，因为使用了左连接所以会已左边的表为驱动表，右连接则会用右边表作为驱动表。

    • 添加order_info表中的customer_no字段索引查询测试

      # 添加索引
ALTER TABLE `order_info` ADD INDEX `idx_customerNo`(`customer_no`);

      • 1
      • 2

      -

      在这里插入图片描述
      添加了order_info表的customer_no字段索引查询耗时和不添加基本一致，也没有使用到创建的索引。

    • 添加customer_info表中的customer_no字段索引查询测试

      ALTER TABLE `customer_info` ADD INDEX `idx_customerNo`(`customer_no`);

      • 1

      在这里插入图片描述
      在这里插入图片描述
      添加customer_info表中的customer_no字段索引查询耗时0.664s效率有大大提升，这里也看到了被驱动表有使用到索引会使用NLJ算法。

    4.2、使用内连接查询 全部订单列表信息返回订单编号和客户昵称

    # 删除索引
ALTER TABLE `customer_info` DROP INDEX `idx_customerNo`;
ALTER TABLE `order_info` DROP INDEX `idx_customerNo`;

    • 1
    • 2
    • 3
    SELECT t1.order_no,t2.nick_name FROM order_info t1 INNER JOIN customer_info t2 ON t1.customer_no=t2.customer_no;

    • 1

    • 无索引情况查询测试
      在这里插入图片描述
      在这里插入图片描述
      MySQL选择了小表customer_info作为驱动表，在没有创建索引时查询耗时12.589s，执行计划中显示使用了BNL算法，下面我们加上索引试一下。

    • 添加order_info表中的customer_no字段索引查询测试

      # 添加索引
ALTER TABLE `order_info` ADD INDEX `idx_customerNo`(`customer_no`);

      • 1
      • 2

      在这里插入图片描述
      在这里插入图片描述
      添加上索引后查询耗时0.306s性能有明显提升，MySQL还是选择了customer_info表做驱动表，这里使用了NLJ算法。

    • 添加customer_info表中的customer_no字段索引查询测试

      # 添加索引
ALTER TABLE `customer_info` ADD INDEX `idx_customerNo`(`customer_no`);

      • 1
      • 2

      在这里插入图片描述
      在这里插入图片描述
      customer_info表中的customer_no字段索引查询耗时0.690s性能比不加索引高很多，比在order_info表中加索引性能又差一些，当只有小表有索引时，MySQL也会将小表作为被驱动表，这里大表驱动小表肯定会比上面小表驱动大表性能稍差一些。

    4.3、使用内连接查询 客户编号C00000999全部订单列表信息返回订单编号和客户昵称

    # 删除索引
ALTER TABLE `customer_info` DROP INDEX `idx_customerNo`;
ALTER TABLE `order_info` DROP INDEX `idx_customerNo`;

    • 1
    • 2
    • 3
    SELECT t1.order_no,t2.nick_name FROM order_info t1 LEFT JOIN customer_info t2 ON t1.customer_no=t2.customer_no WHERE t1.customer_no='C00000999';

    • 1

    • 无索引情况查询测试
      在这里插入图片描述
      在这里插入图片描述
      查询耗时0.078s，在无索引时还是选择的t2表作为驱动表，但是查询时间确是快了很多。

    • 添加order_info表中的customer_no字段索引查询测试

      # 添加索引
ALTER TABLE `order_info` ADD INDEX `idx_customerNo`(`customer_no`);

      • 1
      • 2

      在这里插入图片描述
      在这里插入图片描述
      这里查询耗时0.045s快了1倍，而且加上order_info表中的索引后MySQL选择了order_info作为驱动表，MySQL认为我们给了一个条件t1.customer_no=‘C00000999’，在order_info表中符合条件的数据会比较少，小于customer_info中的1000条数据，所以这里采用order_info作为驱动表，并且使用到了加的customer_no索引。

    • 删除order_info表中索引添加customer_info表中的customer_no字段索引查询测试

      # 删除索引
ALTER TABLE `order_info` ADD INDEX `idx_customerNo`(`customer_no`);
# 添加索引
ALTER TABLE `customer_info` ADD INDEX `idx_customerNo`(`customer_no`);

      • 1
      • 2
      • 3
      • 4

      在这里插入图片描述
      在这里插入图片描述
      耗时0.066s，比不加索引快一点，这里还是选择了customer_info作为驱动表。

    • 给order_info表和customer_info表中的customer_no字段都添加索引查询测试

      # 添加索引
ALTER TABLE `customer_info` ADD INDEX `idx_customerNo`(`customer_no`);
# 添加索引
ALTER TABLE `order_info` ADD INDEX `idx_customerNo`(`customer_no`);

      • 1
      • 2
      • 3
      • 4

      在这里插入图片描述
      在这里插入图片描述
      耗时0.032s效率又有提升，而且这里两个索引都使用到了，customer_info表因为索引后数据还是比order_info少所有MySQL还是选择了customer_info做驱动表。

    五、总结

    • 对于关联sql的优化
      • 关联字段加索引
        • 对于关联字段来说被驱动表最好加上索引，如果使用内连接不确定那张表是被驱动表时最好驱动表和被驱动表都加上。
      • 小表驱动大表
        • 在做左连接或右连接查询的时候如果业务允许一定要用小表驱动大表，或者使用内连接交给where条件去判断业务。
      • where的条件也是要添加对应索引的
        • 两张表关联查询不加条件t1 100w条数据，t2 1000条数据，内连接肯定选择t2做驱动表，但是如果有where条件并且有索引，t1表筛选后只剩10条，t2还是1000条，那么这个时候就会使用到t1做驱动表，查询效率会有所提升。
      • 关于MySQL5.7和MySQL8.0连接查询效率问题
        • 我本地跑了一些连接查询的例子MySQL8.0在有些情况比MySQL5.7的确要快而且是快很多，而且有些情况MySQL8.0没有索引时连接查询也很快，甚至比我加了索引走NLJ算法还快，看了执行计划和MySQL5.7是一样的，不知道是不是因为我本地部署两个版本配置是否有什么区别，MySQL8.0做了很多优化等以后研究明白再来详细说明。
  • 相关阅读:
    双网卡网络设置：有线网卡优先级高于无线网卡
    SpringBoot+Vue+Element-UI实现自动排课系统
    插件收集(idea Communtity Edtion)
    网络通信深入解析：探索TCP/IP模型
    imx6ull-arm开发板和电脑网线直连通信
    你真的懂ArrayList吗？
    htb-cronos
    INTERSPEECH 2022 | 钉钉蜂鸣鸟音频实验室论文入选国际顶会，创新提出窄带滤波网络架构
    Java线程间的共享和协作
    JDBC快速入门及API详解&MySQL学习
  • 原文地址：https://blog.csdn.net/weixin_44606481/article/details/133542964