mysql 딥 페이징 문제를 해결하는 방법

이 기사에서는 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솔루션의 핵심 아이디어:

테이블 반환 수를 줄이기 위해 어떤 기본 키 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 중국어 웹사이트의 기타 관련 기사를 참조하세요!