[msyql]实战：关于回表的一次查询优化实战

1. 起因与前置环境
2. 思考与解决方案
   1. 第一个理解与方法——分块分页
   2. 第二个理解与方法——拆分子查询
   3. 第三个理解与方法——拆分子查询+分块分页
3. 原理浅析与总结
   1. 回表和索引覆盖的浅解
      1. 原理简单说明
      2. MYSQL中回表的实现
   2. 总结与收获

---

起因与前置环境

目前在职的公司是已经运转挺久的电商类型公司，这个过程中其实因为版本不断迭代和很多历史问题。会出现一些慢sql的情况。而且很多时候其实本来会感觉是不应该出现慢sql的地方莫名其妙就出现慢sql了。这些地方其实最适合我们学习数据库的知识。这一次的优化也证明了底层原理知识真的很重要。

前置环境

各位老板放心，公司真实的数据我都不会放出来的，只是利用自己的服务器数据库模拟数据，代码部分直接语言解析一下就好了。

先放sql脚本：

CREATE TABLE `xm_order_items` (
  `order_item_no` char(50) CHARACTER SET utf8mb3 COLLATE utf8_general_ci NOT NULL COMMENT '订单项号',
  `order_no` char(50) CHARACTER SET utf8mb3 COLLATE utf8_general_ci NOT NULL COMMENT '订单号',
  `product_no` char(50) CHARACTER SET utf8mb3 COLLATE utf8_general_ci DEFAULT NULL COMMENT '产品ID',
  `created_at` datetime NOT NULL COMMENT '创建时间',
  `updated_at` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`order_item_no`) USING BTREE,
  KEY `xm_items_order_no` (`order_no`) USING BTREE,
  KEY `xm_items_updated_at` (`updated_at`) USING BTREE,
  KEY `xm_items_created_at` (`created_at`) USING BTREE,
  KEY `xm_items_product_no_created_at` (`product_no`,`created_at`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 MAX_ROWS=1000000000 AVG_ROW_LENGTH=15000 ROW_FORMAT=COMPACT COMMENT='订单明细';
1
2
3
4
5
6
7
8
9
10
11
12

在这说一下对应的一个数据量是六百万左右，我是利用navicat直接生成的数据，所以各位也可以用不同的数据类型去直接生成对应数据量的测试数据。而且我们使用crerated_at这个创建时间去去排查，数据量大概是1000~2000 条\天这样吧。

其中，对应的慢sql是：
1
2
3

SELECT
      xoi.order_no AS orderNo, xoi.order_item_no AS orderItemNo
    FROM
      xm_order_items xoi
    WHERE
        xoi.product_no = 'HG-HGRS-0029'
        AND xoi.created_at >= '2022-07-01 00:00:00'
        AND xoi.created_at <= '2022-09-30 23:59:59';
1
2
3
4
5
6
7
8

对应的耗时解释：
1

1. 起因与前置环境
2. 思考与解决方案
   1. 第一个理解与方法——分块分页
   2. 第二个理解与方法——拆分子查询
   3. 第三个理解与方法——拆分子查询+分块分页
3. 原理浅析与总结
   1. 回表和索引覆盖的浅解
      1. 原理简单说明
      2. MYSQL中回表的实现
   2. 总结与收获

---

起因与前置环境

目前在职的公司是已经运转挺久的电商类型公司，这个过程中其实因为版本不断迭代和很多历史问题。会出现一些慢sql的情况。而且很多时候其实本来会感觉是不应该出现慢sql的地方莫名其妙就出现慢sql了。这些地方其实最适合我们学习数据库的知识。这一次的优化也证明了底层原理知识真的很重要。

前置环境

各位老板放心，公司真实的数据我都不会放出来的，只是利用自己的服务器数据库模拟数据，代码部分直接语言解析一下就好了。

先放sql脚本：

CREATE TABLE `xm_order_items` (
  `order_item_no` char(50) CHARACTER SET utf8mb3 COLLATE utf8_general_ci NOT NULL COMMENT '订单项号',
  `order_no` char(50) CHARACTER SET utf8mb3 COLLATE utf8_general_ci NOT NULL COMMENT '订单号',
  `product_no` char(50) CHARACTER SET utf8mb3 COLLATE utf8_general_ci DEFAULT NULL COMMENT '产品ID',
  `created_at` datetime NOT NULL COMMENT '创建时间',
  `updated_at` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`order_item_no`) USING BTREE,
  KEY `xm_items_order_no` (`order_no`) USING BTREE,
  KEY `xm_items_updated_at` (`updated_at`) USING BTREE,
  KEY `xm_items_created_at` (`created_at`) USING BTREE,
  KEY `xm_items_product_no_created_at` (`product_no`,`created_at`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 MAX_ROWS=1000000000 AVG_ROW_LENGTH=15000 ROW_FORMAT=COMPACT COMMENT='订单明细';
1
2
3
4
5
6
7
8
9
10
11
12

在这说一下对应的一个数据量是六百万左右，我是利用navicat直接生成的数据，所以各位也可以用不同的数据类型去直接生成对应数据量的测试数据。而且我们使用crerated_at这个创建时间去去排查，数据量大概是1000~2000 条\天这样吧。

其中，对应的慢sql是：
1
2
3

SELECT
      xoi.order_no AS orderNo, xoi.order_item_no AS orderItemNo
    FROM
      xm_order_items xoi
    WHERE
        xoi.product_no = 'HG-HGRS-0029'
        AND xoi.created_at >= '2022-07-01 00:00:00'
        AND xoi.created_at <= '2022-09-30 23:59:59';
1
2
3
4
5
6
7
8

对应的耗时解释：
1

思考与解决方案

第一个理解与方法——分块分页

直接从explain可以看出，其实我们已经使用了索引进行查询。作为一个初级程序员的菜鸟。我立刻想到这个在索引上已经没有优化空间。所以我认为这是因为每次获取的数据量太大了，因为是一次性获取三万的数据出来这样。第二个是觉得自己应该是时间跨度太大了。

所以考虑使用java对时间进行分块，比如一周的数据分成一块。然后limit成1000条。这样也能避免一次获取的数据太多的问题。真实数据库的接口能快4倍这样子吧。
1
2
3

SELECT
      xoi.order_no AS orderNo, xoi.order_item_no AS orderItemNo
    FROM
      xm_order_items xoi
    WHERE
        xoi.product_no = 'HG-HGRS-0029'
        AND xoi.created_at >= '2022-07-01 00:00:00'
        AND xoi.created_at <= '2022-07-06 23:59:59';
1
2
3
4
5
6
7
8

第二个理解与方法——拆分子查询

第一个方法虽然能优化个几倍的性能。但是事实上的问题还是很明显，而且数据量一直在增长。所以我还是回家偷偷内卷了一段时间，研究了一下性能优化部分的内容。这里主要发现影响到查询效率的是索引覆盖和回表这两个操作。所以考虑了利用java分成两次查询。这样就可以避免回表问题了。
1

第一次：list = SELECT
  xoi.order_item_no AS orderItemNo
    FROM
      xm_order_items xoi
    WHERE
        xoi.product_no = 'HG-HGRS-0029'
        AND xoi.created_at >= '2022-07-01 00:00:00'
        AND xoi.created_at <= '2022-09-30 23:59:59';

第二次：SELECT
  xoi.order_no AS orderNo 
FROM
  xm_order_items AS xoi 
WHERE
  xoi.order_item_no IN ( list );
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

第三个理解与方法——拆分子查询+分块分页

最后的方法说是考虑到了后面数据增长的问题，增加了分页，如果时间跨度继续扩大，就进行时间分块的方式。
1

第一次：list = SELECT
  xoi.order_item_no AS orderItemNo
    FROM
      xm_order_items xoi
    WHERE
        xoi.product_no = 'HG-HGRS-0029'
        AND xoi.created_at >= '2022-07-01 00:00:00'
        AND xoi.created_at <= '2022-09-30 23:59:59';

第二次：SELECT
  xoi.order_no AS orderNo 
FROM
  xm_order_items AS xoi 
WHERE
  xoi.order_item_no IN ( list ) limit 5000;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

原理浅析与总结

1. 回表和索引覆盖的浅解

   1. 原理简单说明

      什么是回表和索引覆盖呢？

      这里和我们使用的mysql中Innodb 引擎中的索引储存方式，可以理解为你构建了一个索引树（非主键）以后。Innodb会生成一个包含了索引的key + 主键 的节点。每次查找数据的时候如果你直接在索引树上可以命中你需要的所有数据，就会直接返回数据。

      但是如果你像本次分享的sql一样product_no的索引没有order_no中的数据。所以下一步需要回表。

      回表其实就是直接使用索引中的主键去再一次查询数据。

2. 总结与收获
   1. 总结一下，其实这次算是本菜鸟的一次成长吧，真正的研究了一下索引覆盖，回表等mysql中查询相关的知识点。当然不得不承认的是sql的性能优化不能只是单纯的看索引覆盖和回表还有缓冲区之类的挺多东西后面遇到了再分享出来吧。大家有什么想讨论的可以在下面只有留言。

思考与解决方案

第一个理解与方法——分块分页

直接从explain可以看出，其实我们已经使用了索引进行查询。作为一个初级程序员的菜鸟。我立刻想到这个在索引上已经没有优化空间。所以我认为这是因为每次获取的数据量太大了，因为是一次性获取三万的数据出来这样。第二个是觉得自己应该是时间跨度太大了。

所以考虑使用java对时间进行分块，比如一周的数据分成一块。然后limit成1000条。这样也能避免一次获取的数据太多的问题。真实数据库的接口能快4倍这样子吧。
1
2
3

SELECT
      xoi.order_no AS orderNo, xoi.order_item_no AS orderItemNo
    FROM
      xm_order_items xoi
    WHERE
        xoi.product_no = 'HG-HGRS-0029'
        AND xoi.created_at >= '2022-07-01 00:00:00'
        AND xoi.created_at <= '2022-07-06 23:59:59';
1
2
3
4
5
6
7
8

第二个理解与方法——拆分子查询

第一个方法虽然能优化个几倍的性能。但是事实上的问题还是很明显，而且数据量一直在增长。所以我还是回家偷偷内卷了一段时间，研究了一下性能优化部分的内容。这里主要发现影响到查询效率的是索引覆盖和回表这两个操作。所以考虑了利用java分成两次查询。这样就可以避免回表问题了。
1

第一次：list = SELECT
  xoi.order_item_no AS orderItemNo
    FROM
      xm_order_items xoi
    WHERE
        xoi.product_no = 'HG-HGRS-0029'
        AND xoi.created_at >= '2022-07-01 00:00:00'
        AND xoi.created_at <= '2022-09-30 23:59:59';

第二次：SELECT
  xoi.order_no AS orderNo 
FROM
  xm_order_items AS xoi 
WHERE
  xoi.order_item_no IN ( list );
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

第三个理解与方法——拆分子查询+分块分页

最后的方法说是考虑到了后面数据增长的问题，增加了分页，如果时间跨度继续扩大，就进行时间分块的方式。
1

第一次：list = SELECT
  xoi.order_item_no AS orderItemNo
    FROM
      xm_order_items xoi
    WHERE
        xoi.product_no = 'HG-HGRS-0029'
        AND xoi.created_at >= '2022-07-01 00:00:00'
        AND xoi.created_at <= '2022-09-30 23:59:59';

第二次：SELECT
  xoi.order_no AS orderNo 
FROM
  xm_order_items AS xoi 
WHERE
  xoi.order_item_no IN ( list ) limit 5000;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

原理浅析与总结

1. 回表和索引覆盖的浅解

   1. 原理简单说明

      什么是回表和索引覆盖呢？

      这里和我们使用的mysql中Innodb 引擎中的索引储存方式，可以理解为你构建了一个索引树（非主键）以后。Innodb会生成一个包含了索引的key + 主键 的节点。每次查找数据的时候如果你直接在索引树上可以命中你需要的所有数据，就会直接返回数据。

      但是如果你像本次分享的sql一样product_no的索引没有order_no中的数据。所以下一步需要回表。

      回表其实就是直接使用索引中的主键去再一次查询数据。

2. 总结与收获
   1. 总结一下，其实这次算是本菜鸟的一次成长吧，真正的研究了一下索引覆盖，回表等mysql中查询相关的知识点。当然不得不承认的是sql的性能优化不能只是单纯的看索引覆盖和回表还有缓冲区之类的挺多东西后面遇到了再分享出来吧。大家有什么想讨论的可以在下面只有留言。