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
-
2.5 使用插件方式的疑惑
-
IPage 查询总条数 如果两个表数据能确定一对一关系都有数据 那么执行的sql查询总条数会联表查询效率会降低很多
-
本人 也很疑问的一个问题 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语句使用第三种 然后建立索引 博主这个是日志表 日志现在越来越多了下一步就是去优化这些数据 数据量大了就分库分表分区 .
类似资料：