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

本文针对MySQL联表查询效率低的问题,提供了具体的优化方案。通过对日志表进行联表查询并分页排序,对比不同查询方式的效果,最终推荐了一种高效查询方法。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

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语句使用第三种 然后建立索引 博主这个是日志表 日志现在越来越多了下一步就是去优化这些数据 数据量大了就分库分表分区 .

<think>好的,用户想了解如何在不使用MyBatis-Plus原生的分页API的情况下,通过selectList方法实现分页。我需要先回忆一下MyBatis-Plus的相关知识。 首先,MyBatis-Plus的selectList通常用于查询所有符合条件的记录,但用户不想用Page对象和分页插件,那可能需要手动分页。记得原生的分页API会自动处理分页逻辑,包括count查询和实际数据查询,而手动的话可能需要自己处理这些步骤。 接下来,用户可能需要知道如何构造分页参数,比如current页和size页的大小,然后在查询时通过limit和offset来实现。不过MyBatis-Plus的Wrapper是否有直接支持limit的方法呢?好像是的,比如在QueryWrapper中使用last方法添加limit语句。 另外,需要注意数据库类型,不同的数据库limit语法可能不同,但MyBatis-Plus应该能处理这些差异。不过用户可能需要自己计算offset,比如(current - 1) * size。 然后,用户可能还需要获取总记录数,以便计算总页数,这时候需要单独执行一个count查询,使用selectCount方法,传入同样的查询条件Wrapper。 可能存在性能问题,因为每次分页都要执行次查询,一次count,一次数据。如果数据量大,count可能会慢,这时候需要提醒用户考虑缓存或者其他优化措施。 另外,还要注意SQL注入的问题,手动拼接limit参数时,要确保参数是安全的,MyBatis-Plus的Wrapper应该已经做了参数绑定,所以使用last方法应该是安全的,比如wrapper.last("LIMIT " + offset + "," + size)。 总结步骤的话,应该是:1. 创建查询条件Wrapper;2. 计算offset;3. 使用last方法添加limit;4. 执行selectList获取数据;5. 执行selectCount获取总数;6. 组装分页结果。 可能还需要提醒用户,这种方式虽然灵活,但不如原生分页API方便,尤其是在处理一些复杂的分页场景时,比如排序优化count查询等。如果遇到问题,可能需要考虑切换回原生API或者结合使用。 最后,给出一个具体的代码示例,展示如何实现手动分页,包括Wrapper的创建、分页参数计算、执行查询和获取总数。同时提醒注意事项,比如性能SQL注入和分页计算的正确性。</think>如果您希望绕过MyBatis-Plus原生的分页API(如 `Page`、`IPage`),直接通过 `selectList` 实现分页查询,可以通过以下步骤实现: --- ### 一、核心思路 1. **手动计算分页参数**:通过 `current`(当前页)和 `size`(每页条数)计算 `offset` 2. **使用Wrapper拼接LIMIT**:通过QueryWrapper的 `last()` 方法直接注入 `LIMIT` SQL片段 3. **独立查询总数**:通过 `selectCount` 单独获取总记录数 --- ### 二、具体实现代码示例 ```java // 1. 创建查询条件(可选) QueryWrapper<User> queryWrapper = new QueryWrapper<>(); queryWrapper.eq("status", 1); // 示例条件 // 2. 计算分页参数 int current = 2; // 当前页(从1开始) int size = 10; // 每页条数 int offset = (current - 1) * size; // 3. 拼接LIMIT语句(注意空格避免SQL错误) queryWrapper.last("LIMIT " + offset + ", " + size); // 4. 执行分页查询 List<User> records = userMapper.selectList(queryWrapper); // 5. 查询总记录数(需要重置LIMIT) queryWrapper.clear(); // 清除之前的LIMIT queryWrapper.eq("status", 1); // 重新添加条件 Long total = userMapper.selectCount(queryWrapper); // 6. 构建分页结果对象(自定义或Map) Map<String, Object> pageResult = new HashMap<>(); pageResult.put("records", records); pageResult.put("total", total); pageResult.put("current", current); pageResult.put("size", size); ``` --- ### 三、关键点说明 1. **`last()`方法的作用**: - 直接在SQL尾部追加内容(需自行保证语法正确) - 示例生成的SQL:`SELECT ... FROM user WHERE status = 1 LIMIT 10, 10` 2. **分页参数计算**: - `offset = (current - 1) * size` - 例如第2页每页10条:`LIMIT 10, 10`(跳过前10条,取接下来的10条) 3. **总数查询注意事项**: - 必须清除之前的 `LIMIT` 条件(`queryWrapper.clear()`) - 重新添加查询条件避免总数计算错误 --- ### 四、缺点对比 | **方案** | **势** | **缺点** | |---------------------|-----------------------------------|---------------------------------------| | 原生分页API | 自动处理总数、分页逻辑 | 强制返回`IPage`对象 | | 手动`selectList` | 灵活控制返回格式 | 需手动处理总数查询和分页参数计算 | --- ### 五、注意事项 1. **SQL注入风险**:确保 `offset` 和 `size` 是可信数值 2. **性能问题**:大数据量时`selectCount`可能较慢,建议添加索引 3. **数据库兼容性**:`LIMIT`语法需与当前数据库类型匹配(MySQL适用) 如果需要更复杂的场景(如多联查分页),建议仍先考虑原生分页API + 自定义SQL的混合方案。
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值