mysql 딥 페이징 문제를 해결하는 방법
- WBOY앞으로
- 2022-07-26 13:41:293390검색
이 기사에서는 mysql에 대한 관련 지식을 제공합니다. 주로 mysql 딥 페이징 문제에 대한 우아한 솔루션을 소개합니다. 이 기사에서는 mysql 테이블에 많은 양의 데이터가 있을 때 딥 페이징 문제를 최적화하는 방법에 대해 설명합니다. 느린 SQL 문제를 최적화하는 최근 사례의 의사 코드가 모든 사람에게 도움이 되기를 바랍니다.
추천 학습: mysql 비디오 튜토리얼
일일 수요 개발 과정에서 모두가 Limit에 대해 잘 알고 있을 것이라고 생각하지만 Limit을 사용할 때 오프셋(Offset)이 매우 클 때 쿼리를 찾을 수 있습니다. 효율성이 점점 느려지고 있습니다. 처음에 제한이 2000이면 필요한 데이터를 쿼리하는 데 200ms가 걸릴 수 있지만 제한이 4000 오프셋 100000인 경우 쿼리 효율이 이미 약 1S가 필요하다는 것을 알 수 있습니다. 점점 더 느려집니다.
요약
이 글에서는 mysql 테이블에 대용량 데이터가 있을 때 딥 페이징 문제를 최적화하는 방법에 대해 논의하고, 최근 느린 SQL 문제를 최적화한 사례의 의사코드를 첨부하겠습니다.
1. 딥 페이징 문제 설명 제한
먼저 테이블 구조를 살펴보겠습니다(예를 들어보겠습니다. 테이블 구조가 불완전하고 쓸모 없는 필드가 표시되지 않습니다)
CREATE TABLE `p2p_detail_record` ( `id` varchar(32) COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '主键', `batch_num` int NOT NULL DEFAULT '0' COMMENT '上报数量', `uptime` bigint NOT NULL DEFAULT '0' COMMENT '上报时间', `uuid` varchar(64) COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '会议id', `start_time_stamp` bigint NOT NULL DEFAULT '0' COMMENT '开始时间', `answer_time_stamp` bigint NOT NULL DEFAULT '0' COMMENT '应答时间', `end_time_stamp` bigint NOT NULL DEFAULT '0' COMMENT '结束时间', `duration` int NOT NULL DEFAULT '0' COMMENT '持续时间', PRIMARY KEY (`id`), KEY `idx_uuid` (`uuid`), KEY `idx_start_time_stamp` (`start_time_stamp`) //索引, ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='p2p通话记录详情表';
우리가 원하는 딥 페이징 SQL을 가정해 보겠습니다. 쿼리는 이렇게 생겼어요
select * from p2p_detail_record ppdr where ppdr .start_time_stamp >1656666798000 limit 0,2000
쿼리 효율이 94ms인데 빠른가요? 따라서 100000 또는 2000으로 제한하면 쿼리 효율성은 1.5S로 이미 매우 느립니다.
2.SQL이 느려지는 원인 분석
이 SQL의 실행 계획을 살펴보겠습니다
인덱스에도 가는데 왜 아직도 느린걸까요? 먼저 mysql 관련 지식 포인트를 검토해 보겠습니다.
클러스터형 인덱스 및 비클러스터형 인덱스
클러스터형 인덱스: 리프 노드는 전체 데이터 행을 저장합니다.
비클러스터형 인덱스: 리프 노드는 데이터의 전체 행에 해당하는 기본 키 값을 저장합니다.
비클러스터형 인덱스 쿼리를 사용하는 과정
- 비클러스터형 인덱스 트리를 통해 해당 리프 노드를 찾아 기본키 값을 얻는다.
- 그런 다음 기본 키 값을 가져오고 클러스터형 인덱스 트리로 돌아가 해당 데이터의 전체 행을 찾습니다. (전체 프로세스를 테이블 리턴이라고 합니다)
이 SQL이 느린 이유에 대한 질문으로 돌아가서, 그 이유는 다음과 같습니다
1 제한 문은 먼저 오프셋+n 행을 스캔한 다음 삭제합니다. 이전 오프셋 행. n개 행의 데이터를 반환한 후. 즉, limit 100000,10은 100010개의 행을 검색하고 limit 0,10은 10개의 행만 검색합니다. 여기서는 테이블을 100010번 반환해야 하며 테이블을 반환하는 데 많은 시간이 소요됩니다. limit 100000,10，就会扫描100010行，而limit 0,10，只扫描10行。这里需要回表100010次，大量的时间都在回表这个上面。
方案核心思路： 能不能事先知道要从哪个主键ID开始，减少回表的次数
常见解决方案
通过子查询优化
select * from p2p_detail_record ppdr where id >= (select id from p2p_detail_record ppdr2 where ppdr2 .start_time_stamp >1656666798000 limit 100000,1) limit 2000
相同的查询结果，也是10W条开始的第2000条，查询效率为200ms，是不是快了不少。
标签记录法
标签记录法： 其实标记一下上次查询到哪一条了，下次再来查的时候，从该条开始往下扫描。类似书签的作用
select * from p2p_detail_record ppdr where ppdr.id > 'bb9d67ee6eac4cab9909bad7c98f54d4' order by id limit 2000 备注：bb9d67ee6eac4cab9909bad7c98f54d4是上次查询结果的最后一条ID
使用标签记录法，性能都会不错的，因为命中了id솔루션의 핵심 아이디어:
- 일반적인 솔루션
- 하위 쿼리를 통해 최적화
CREATE TABLE `p2p_detail_record` ( `id` varchar(32) COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '主键', `batch_num` int NOT NULL DEFAULT '0' COMMENT '上报数量', `uptime` bigint NOT NULL DEFAULT '0' COMMENT '上报时间', `uuid` varchar(64) COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '会议id', `start_time_stamp` bigint NOT NULL DEFAULT '0' COMMENT '开始时间', `answer_time_stamp` bigint NOT NULL DEFAULT '0' COMMENT '应答时间', `end_time_stamp` bigint NOT NULL DEFAULT '0' COMMENT '结束时间', `duration` int NOT NULL DEFAULT '0' COMMENT '持续时间', PRIMARY KEY (`id`), KEY `idx_uuid` (`uuid`), KEY `idx_start_time_stamp` (`start_time_stamp`) //索引, ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='p2p通话记录详情表';
동일 쿼리 결과도 100,000개 항목에서 시작하여 2000번째 기사이며, 쿼리 효율성은 200ms로 훨씬 빠릅니다.
태그 기록 방법- 태그 기록 방법 : 사실, 지난번에 확인했던 항목을 표시해두고, 다음에 다시 확인할 때는 해당 항목부터 아래로 스캔하세요. 북마크와 유사
//最小ID
String lastId = null;
//一页的条数
Integer pageSize = 2000;
List<P2pRecordVo> list ;
do{
list = listP2pRecordByPage(lastId,pageSize); //标签记录法，记录上次查询过的Id
lastId = list.get(list.size()-1).getId(); //获取上一次查询数据最后的ID，用于记录
//对数据的操作逻辑
XXXXX();
}while(isNotEmpty(list));
<select id ="listP2pRecordByPage">
select *
from p2p_detail_record ppdr where 1=1
<if test = "lastId != null">
and ppdr.id > #{lastId}
</if>
order by id asc
limit #{pageSize}
</select>태그 기록 방식을 사용하면 id 인덱스를 적중시키므로 성능이 좋습니다. 하지만 이 방법에는 몇 가지 단점이 있습니다. 1. 여러 페이지가 아닌 연속된 페이지에서만 쿼리할 수 있습니다. 2. continuous auto-increment와 유사한 필드가 필요합니다(orber by id 사용 가능).
- 솔루션 비교
방법
이용: 페이지 간 쿼리가 가능하며, 원하는 페이지에서 데이터를 확인할 수 있습니다.
🎜단점: 🎜 🎜태그 기록 방법🎜만큼 효율적이지 않습니다. 🎜이유:🎜 예를 들어 100,000개의 데이터를 확인해야 하는 경우에는 Non-Clustered Index에 해당하는 1000번째 데이터도 먼저 쿼리한 다음 100,000번째부터 ID를 얻어 쿼리해야 합니다. 🎜🎜🎜🎜태그 기록 방법을 사용하면🎜🎜🎜🎜🎜장점: 🎜 쿼리 효율성이 매우 안정적이고 매우 빠릅니다. 🎜🎜🎜단점:🎜🎜- 不跨页查询，
- 需要一种类似连续自增的字段
关于第二点的说明： 该点一般都好解决，可使用任意不重复的字段进行排序即可。若使用可能重复的字段进行排序的字段，由于mysql对于相同值的字段排序是无序，导致如果正好在分页时，上下页中可能存在相同的数据。
实战案例
需求： 需要查询查询某一时间段的数据量，假设有几十万的数据量需要查询出来，进行某些操作。
需求分析 1、分批查询（分页查询），设计深分页问题，导致效率较慢。
CREATE TABLE `p2p_detail_record` ( `id` varchar(32) COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '主键', `batch_num` int NOT NULL DEFAULT '0' COMMENT '上报数量', `uptime` bigint NOT NULL DEFAULT '0' COMMENT '上报时间', `uuid` varchar(64) COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '会议id', `start_time_stamp` bigint NOT NULL DEFAULT '0' COMMENT '开始时间', `answer_time_stamp` bigint NOT NULL DEFAULT '0' COMMENT '应答时间', `end_time_stamp` bigint NOT NULL DEFAULT '0' COMMENT '结束时间', `duration` int NOT NULL DEFAULT '0' COMMENT '持续时间', PRIMARY KEY (`id`), KEY `idx_uuid` (`uuid`), KEY `idx_start_time_stamp` (`start_time_stamp`) //索引, ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='p2p通话记录详情表';
伪代码实现：
//最小ID
String lastId = null;
//一页的条数
Integer pageSize = 2000;
List<P2pRecordVo> list ;
do{
list = listP2pRecordByPage(lastId,pageSize); //标签记录法，记录上次查询过的Id
lastId = list.get(list.size()-1).getId(); //获取上一次查询数据最后的ID，用于记录
//对数据的操作逻辑
XXXXX();
}while(isNotEmpty(list));
<select id ="listP2pRecordByPage">
select *
from p2p_detail_record ppdr where 1=1
<if test = "lastId != null">
and ppdr.id > #{lastId}
</if>
order by id asc
limit #{pageSize}
</select>这里有个小优化点: 可能有的人会先对所有数据排序一遍，拿到最小ID，但是这样对所有数据排序，然后去min（id),耗时也蛮长的，其实第一次查询，可不带lastId进行查询，查询结果也是一样。速度更快。
总结
1、当业务需要从表中查出大数据量时，而又项目架构没上ES时，可考虑使用标签记录法的方式，对查询效率进行优化。
2、从需求上也应该尽可能避免，在大数据量的情况下，分页查询最后一页的功能。或者限制成只能一页一页往后划的场景。
推荐学习：mysql视频教程
위 내용은 mysql 딥 페이징 문제를 해결하는 방법의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!