mysql两表联联查分页排序效率优化mybatis-plusIPage
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 utf
8mb4_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',
inputstream和fileinputstream区别`url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '请求地址',mysql面试题sql语句多表联查
`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.params AS params,
record.clazz AS clazz,
FROM_UNIXTIME( ate_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.loading_time AS loading_time
FROM
r_api_record record
LEFT JOIN r_api_result result ON record.id = d_id
${ew.customSqlSegment}
</select>
2 第⼆种 0-10页耗时 0.27秒左右 ⼤家往下看实现⽅法
<select id="findApiRecordByPage"resultType="map">
SELECT r_api_record.*,
FROM_UNIXTIME( r_ate_time / 1000, '%Y-%m-%d %H:%i:%s' ) AS createTime , r_s_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.Uri()).ifPresent((v)->{
if(StrUtil.isNotBlank(v)){
//这是条件
queryWrapper.eq("r_api_record.uri", im(v));
html5自定义视频播放器}
});
//这是拼接的sql
queryWrapper.apply("r_d_id=r_api_record.id");
//这是排序
Page<ApiRecordBean> page =new Page<>(currPage, pageSize);
page.setOptimizeCountSql(true);
IPage<Map<String, Object>> apiRecordByPage = apiRecordMapper
.
findApiRecordByPage(page, queryWrapper);
log.info("分页查询记录当前第 {}页，每页 {}条，总共 {}页，总共 {}条", Current(), Size(), Pages(), Total()); 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);
php的梗是怎么来的}
2.4原始sql运⾏结果
==> Preparing: SELECT COUNT(*) FROM r_api_result, r_api_record WHERE (r_d_id = r_api_record.id)
<== Total:1
==>SELECT r_api_record.*,FROM_UNIXTIME( r_ate_time /1000,'%Y-%m-%d %H:%i:%s')
AS createTime , r_s_status, r_api_result.loading_time FROM
r_api_result,r_api_record WHERE (r_d_id=r_api_record.id)
ORDER BY r_ate_time DESC LIMIT ?,?
<== Total:10
什么是hibernate一级缓存1. 2.5 使⽤插件⽅式的疑惑
2. IPage 查询总条数 如果两个表数据能确定⼀对⼀关系都有数据 那么执⾏的sql查询总条数会联表查询效率会降低很多
3. 本⼈ 也很疑问的⼀个问题 IPage 的 LIMIT未到在mybaits的sql中指定位置知的⽅法 效率提⾼只能通过第三种原⽣的Mybats进⾏
分页查询 如果可以指定LIMIT的位置请各位⼤神告知下。
2.5 最后⼀种执⾏最快 推荐⼤家使⽤这⼀种查询 时间 0.2秒
#先把r_api_record做为条件先进⾏排序分页最⼤化的优化排序
select t.*, FROM_ate_time / 1000, '%Y-%m-%d %H:%i:%s' )
AS createTime , r_s_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_d_id=t.id
总结 优化的⽅式 sql语句使⽤第三种 然后建⽴索引 博主这个是⽇志表 ⽇志现在越来越多了下⼀步就是去优化这些数据 数据量⼤了就分库分表分区 .
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论