mysql 两表联联查 分页排序效率优化 mybatis-plus IPage

mysql 联表条件查询优化 以日志表为例子

---

联表sql查询效慢 问什么 废话不多说直击痛点

• 在多表联查分页排序，最大的瓶颈在排序，因此我们应该最大化的优化排序

---

1 结果表

CREATE TABLE `r_api_result`  (
  `id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `record_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'api记录id',
  `res_status` int(10) NULL DEFAULT NULL COMMENT '返回状态码',
  `res_content_type` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '返回消息类型',
  `res_msg` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '返回消息',
  `loading_time` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '消耗时间',
  `result` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '返回结果',
  `create_time` bigint(20) NULL DEFAULT NULL COMMENT '创建时间戳',
  `update_time` bigint(20) NULL DEFAULT NULL COMMENT '更新时间戳',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

2 记录表

CREATE TABLE `r_api_record`  (
  `id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '主键',
  `ip` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'ip',
  `host` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'host',
  `url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '请求地址',
  `method` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '请求方式',
  `params` varchar(3000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '请求参数',
  `clazz` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'clazz',
  `create_time` bigint(20) NULL DEFAULT NULL COMMENT '创建时间戳',
  `update_time` bigint(20) NULL DEFAULT NULL COMMENT '更新时间戳',
  `uri` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '请求URI',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

现在需要对这两张表进行分页排序联查 两个表均有50w数据

本人使用的是mybatis-plus插件IPage 3中实现方式 单效果都不理想

1.最慢 50万条LEFT JOIN 直接卡死

<select id="findApiRecordByPage"  resultType="map">
    SELECT
    record.id AS id,
    record.ip AS ip,
    record.host AS host,
    record.uri AS uri,
    record.url AS url,
    record.method AS method,
    record.params AS params,
    record.clazz AS clazz,
    FROM_UNIXTIME( record.create_time / 1000, '%Y-%m-%d %H:%i:%s' ) AS create_time,
    FROM_UNIXTIME(record.update_time / 1000, '%Y-%m-%d %H:%i:%s') AS update_time,
    result.res_status AS res_status,
    result.loading_time AS loading_time
    FROM
    r_api_record record
    LEFT JOIN r_api_result result ON record.id = result.record_id
    ${ew.customSqlSegment}
  </select>

2 第二种 0-10页耗时 0.27秒左右 大家往下看实现方法

  <select id="findApiRecordByPage"  resultType="map">
  SELECT r_api_record.*,
  FROM_UNIXTIME( r_api_record.create_time / 1000, '%Y-%m-%d %H:%i:%s' ) AS  createTime ,
  r_api_result.res_status, r_api_result.loading_time
  FROM r_api_result,r_api_record
    ${ew.customSqlSegment}
  </select>

2.1 Mapper接口

 IPage<Map<String, Object>> findApiRecordByPage(@Param("page") Page page,
      @Param(Constants.WRAPPER) Wrapper<ApiRecordBean> wrapper);

2.2 定义实业务类

 public R findApiRecordByPage(ApiRecordBean apiRecordBean, int currPage, int pageSize)
      throws GomaiBusinessException {
    log.info("findApiRecordByPage 分页查询API记录 apiRecordBean {}, currPage {}, pageSize {}",
        apiRecordBean, currPage, pageSize);
    QueryWrapper<ApiRecordBean> queryWrapper = new QueryWrapper<>();
    Optional.ofNullable(apiRecordBean.getUri()).ifPresent((v) -> {
      if (StrUtil.isNotBlank(v)) {
          //这是条件
        queryWrapper.eq("r_api_record.uri", StrUtil.trim(v));
      }
    });
     //这是拼接的sql
    queryWrapper.apply("r_api_result.record_id=r_api_record.id");
     //这是排序
    queryWrapper.orderByDesc("r_api_record.create_time");
    Page<ApiRecordBean> page = new Page<>(currPage, pageSize);
    page.setOptimizeCountSql(true);
    IPage<Map<String, Object>> apiRecordByPage = apiRecordMapper
        .findApiRecordByPage(page, queryWrapper);
    log.info("分页查询记录 当前第 {}页,每页 {}条,总共 {}页，总共 {}条", apiRecordByPage.getCurrent(),
        apiRecordByPage.getSize(), apiRecordByPage.getPages(), apiRecordByPage.getTotal());
    return GomaiResult.success(apiRecordByPage);
  }

2.3 Controller

  @GetMapping("/record/page")
  @ApiOperation(value = "findApiRecordByPage", notes = "分页查询记录")
  @ApiImplicitParams({
      @ApiImplicitParam(name = "ApiRecordBean", value = "apiRecordBean", dataTypeClass = ApiVo.class),
      @ApiImplicitParam(name = "currPage", value = "当前页", dataType = "int"),
      @ApiImplicitParam(name = "pageSize", value = "每页行数", dataType = "int"),
  })
  public R findApiRecordByPage(ApiRecordBean apiRecordBean,
      @RequestParam(value = "currPage", defaultValue = "1") int currPage,
      @RequestParam(value = "pageSize", defaultValue = "10") int pageSize)
      throws GomaiBusinessException {
    return iApiRecordService
        .findApiRecordByPage(apiRecordBean, currPage, pageSize);
  }

2.4原始sql运行结果

==>  Preparing: SELECT COUNT(*) FROM r_api_result, r_api_record WHERE (r_api_result.record_id = r_api_record.id)
<==      Total: 1
==>SELECT r_api_record.*, FROM_UNIXTIME( r_api_record.create_time / 1000, '%Y-%m-%d %H:%i:%s' )
AS createTime , r_api_result.res_status, r_api_result.loading_time FROM
r_api_result,r_api_record WHERE (r_api_result.record_id=r_api_record.id)
ORDER BY r_api_record.create_time DESC LIMIT ?,?
 <==      Total: 10

1. 2.5 使用插件方式的疑惑

2. IPage 查询总条数 如果两个表数据能确定一对一关系都有数据 那么执行的sql查询总条数会联表查询效率会降低很多

3. 本人 也很疑问的一个问题 IPage 的 LIMIT未找到在mybaits的sql中指定位置知的方法 效率提高只能通过第三种原生的Mybats进行分页查询 如果可以指定LIMIT的位置请各位大神告知下。

---

2.5 最后一种执行最快 推荐大家使用这一种查询 时间 0.2秒

#先把r_api_record做为条件先进行排序分页   最大化的优化排序
select t.*, FROM_UNIXTIME(t.create_time / 1000, '%Y-%m-%d %H:%i:%s' ) 
AS createTime , r_api_result.res_status, r_api_result.loading_time
FROM r_api_result 
join (SELECT * from r_api_record order by create_time desc limit 2,10)t where  
r_api_result.record_id=t.id

总结 优化的方式 sql语句使用第三种 然后建立索引 博主这个是日志表 日志现在越来越多了下一步就是去优化这些数据 数据量大了就分库分表分区 .