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

最新推荐文章于 2025-05-25 11:45:00 发布
原创 最新推荐文章于 2025-05-25 11:45:00 发布 · 2.5k 阅读 · 6 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#java #sql #索引

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

MyBatis-SQL分析组件：优化数据库访问效率
bug_syntax119的博客
09-17 200
在这篇文章中，我们将介绍MyBatis的SQL分析组件，该组件可以帮助我们分析和优化数据库查询。MyBatis-SQL分析组件为我们提供了一个强大的工具来分析和优化数据库查询。通过使用该组件，我们可以更好地理解和调优我们的SQL语句，从而提高数据库操作的效率和性能。一旦我们获得了SQL语句的分析结果，我们可以使用这些信息来优化查询。要使用MyBatis-SQL分析组件，我们需要在项目的依赖中引入相应的库。对象将包含分析结果的各种信息，例如SQL语句中的参数、表名、列名等。首先，我们需要创建一个。
【SpringBoot】26、SpringBoot中整合MyBatis-Plus实现数据库操作
热门推荐
Asurplus
07-23 21万+
MyBatis 框架相信大家都用过，虽然 MyBatis 可以直接在 xml 中通过 SQL 语句操作数据库，很是灵活。但正其操作都要通过 SQL 语句进行，就必须写大量的 xml 文件，很是麻烦。于是 MyBatis-Plus 应运而生，作为 MyBatis 的增强工具，更是为我们开发效率得到了质的飞跃。 一、简介 1、MyBatis MyBatis 是一款优秀的持久层框架，它支持自定义 SQL、存储过程以及高级映射。MyBatis 免除了几乎所有的 JDBC 代码以及设置参数和获取结果集的工作。MyBa
使用Mybatis-plus实现两个表联合查询分页
crescent97的博客
06-13 2611
使用Mybatis-plus实现两个表联合查询分页
MyBatis分页插件 MyBatis Plus 分页查询优化
最新发布
草原孤狼的专栏
05-25 1404
Mybatis 之 MyBatis Plus 分页查询优化之道
mybatis与mysql调优_MySQL + mybatis的SQL优化方案
weixin_39687468的博客
01-21 223
sql优化方案:1.添加索引,在条件参数,关联参数上建立参数,2.字段优化,需要什么字段查什么字段3.模糊查询尽量使用: select * from tableName a where a.name like 'name%'避免使用 '%name%' 和 '%name';3.利用explain 分析SQLidSELECT识别符。这是SELECT的查询序列号select_typeSELECT类型,可...
Elasticsearch——分页查询From&Size VS scroll
进击30K的专栏
09-11 418
Elasticsearch——分页查询From&Size VS scroll Elasticsearch中数据都存储在分片中，当执行搜索时每个分片独立搜索后，数据再经过整合返回。那么，如果要实现分页查询该怎么办呢？ 更多内容参考Elasticsearch资料汇总 按照一般的查询流程来说，如果我想查询前10条数据： 1 客户端请求发给某个节点 2 节点转发给个个分片，查询每个分片上的前10条 3 结果返回给节点，整合数据，提取前10条 4 返回给请求客户端 那么当我想要查询第10条到第
两张表联合查询带分页
dingtuo8679的博客
01-24 2001
select * from (SELECT ROW_NUMBER() OVER (ORDER BY B.OrderID) as num, B.*FROM(SELECT h.OrderNumber,k.OrderID,k.OrdeMumber,k.CustomerName,k.ProductCodefrom sales_orderinfo as h right join orderdeta...
使用 MyBatis-Plus 实现联表查询分页
小李同学的博客
08-13 4459
使用 MyBatis-Plus 实现联表查询分页
mybatis-plus自定义多表分页查询
qq_62038093的博客
03-11 2042
mybati-plus多表分页查询
mybatis-plus 踩坑
Jonny的博客
07-22 517
下面为手动配置分页插件，如果是用的starter可以在配置文件中进行配置。mybatis-plu自带分页多表联查导致分页和总数据对应不上。查询超出的页码返回了第一页或者最后一页的数据。需要设置关闭coun优化。
MyBatis-Plus完成链表分页查询
future_n的博客
08-22 778
【代码】MyBatis-Plus完成链表分页查询。
查询两张表的数据如何分页
Echouuu的博客
04-02 4251
查询两张表的数据如何分页 使用Mysql的UNION和UNION ALL 1.UNION会把合并的数据去重，UNION ALL允许重复的数据； 2.需要两张表有相同类型的字段; 3.查询时SELECT语句查询的列的个数、顺序都要保持一致; SELECT column_name1,column_name2 FROM table_a UNION ALL SELECT column_name1,column_name2 FROM table_b LIMIT 0,2; ...
MySQL-查询两张表按照某字段排序并分页
会飞的蜗牛
02-02 3538
SELECT * FROM ( SELECT a.id AS "id", a.`name` AS "name", a.unit_type AS "unitType", a.price_show AS "priceShow", a.price_sell AS "priceCustom", a.price_cost AS "p
yii框架中的两表联查+分页
CZY_1214的博客
10-22 883
view视图部分 use yii\widgets\LinkPager; foreach($models as $k=>$v){     echo "";     echo "".$v['u_id']."";     echo "".$v['u_name']."";     echo "".$v['u_state']."";     echo ""; } echo LinkPa
MYSQL分页limit速度太慢优化方法
weixin_33709219的博客
07-17 130
http://www.fienda.com/archives/110 在mysql中limit可以实现快速分页，但是如果数据到了几百万时我们的limit必须优化才能有效的合理的实现分页了，否则可能卡死你的服务器哦。 当一个表数据有几百万的数据的时候成了问题！ 如 * from table limit 0,10 这个没有问题 当 limit 200000,10 的时候数据读取就...
两表查询及分页
weixin_42592992的博客
01-27 1128
—分页问题 使用pagehelper后端分页，导入相关依赖， ajax返回的data.list.length是数据的长度 data.pages总页数 data.total总条数 //第一个参数的意思为：当前页数，第二个参数的意思为：每页显示多少条记录 PageHelper.startPage(pageNum,pageSize); 这句要写在SQL执行语句前一行。 —多表查询问题 ########### yml文件中，开启mybatis驼峰命名 ########### mybatis.configurat
oracle两表联查分页公式,Oracle中分页查询和联表查询
weixin_36229293的博客
04-05 601
1.使用ROWNUM伪列查询1.1.查询十条数据(rownum<=n)SELECT ROWNUM,A.* FROM v_sjjx_unit_info A WHERE ROWNUM<=10 1.2.查询实体十条数据(between...and...)SELECT * FROM v_sjjx_xsjbsjxx WHERE ROWNUM BETWEEN 0 AND 10 1.3.查询第二到第...
mybatisplus page排序_mybatis 分页插件 mybatis-plus-page
weixin_39789525的博客
12-19 1634
mybatis-plus-page 详细介绍mybatis分页插件MicroPageInterceptor 特点：1， 支持mysql和oracle分页2， 不必在xml编写统计count的sql3， 使用RowBounds子类PageInfo作为分页信息和记录总数的载体，不必像其他分页插件那样要求输入输出参数必须继承特殊父类。4， 可在PageInfo中填写自定义排序sql串，提高查询性能和排序...
如果我不想使用MyBatis-Plus原生分页API，而是使用mybatis-plus的selectList怎么做
03-12
<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、付费专栏及课程。

余额充值