MySQL: 페이징 최적화

인터뷰 질문, MySQL 테이블에 많은 양의 데이터가 있을 때 페이징을 수행하는 방법. . . . 당시에는 데이터 양이 많을 때 테이블로 나눌 수 있다는 것만 알았지, 테이블을 나누지 않고서는 어떻게 해야 할지 몰랐습니다. . . . 아아, 에이전트에게 데이터 몇 개만 갖고 이를 완전히 보관할 수 있는 간단한 제한과 오프셋(얼굴 가리개)을 요청했습니다. . .

많은 애플리케이션은 최신 또는 가장 인기 있는 기록만 표시하는 경향이 있지만, 이전 기록에 계속 액세스하려면 페이징 탐색 모음이 필요합니다. 그러나 MySQL을 통해 페이징을 더 잘 구현하는 방법은 항상 골치 아픈 일이었습니다. 기성 솔루션은 없지만 데이터베이스의 기본 계층을 이해하면 페이지를 매긴 쿼리를 최적화하는 데 도움이 될 수 있습니다.

성능이 좋지 않은 일반적으로 사용되는 쿼리를 살펴보겠습니다.

이 쿼리에는 0.00초가 걸립니다. 그렇다면 이 쿼리에 어떤 문제가 있나요? 실제로 이 쿼리문과 매개변수는 아래 테이블의 기본키를 사용하고 15개의 레코드만 읽기 때문에 문제가 없다.

실제 문제는 다음과 같이 오프셋(페이징 오프셋)이 매우 클 때입니다.

SELECT *
FROM city
ORDER BY id DESC
LIMIT 0, 15

위의 쿼리는 2M 행의 레코드가 있을 때 0.22초가 소요됩니다. EXPLAIN을 통해 SQL 실행 계획을 보면 SQL이 100015개의 행을 검색했지만 결국 15개의 행만 필요한 것을 알 수 있습니다. 페이징 오프셋이 크면 사용되는 데이터가 늘어나고 MySQL은 궁극적으로 사용되지 않을 많은 데이터를 메모리에 로드합니다. 대부분의 웹사이트 사용자가 데이터의 처음 몇 페이지에만 액세스한다고 가정하더라도 페이지 오프셋이 큰 소수의 요청으로 인해 전체 시스템이 손상될 수 있습니다. Facebook은 이를 알고 있지만 초당 더 많은 요청을 처리하기 위해 데이터베이스를 최적화하는 대신 요청 응답 시간의 변동을 줄이는 데 중점을 둡니다.

페이징 요청의 경우 매우 중요한 또 다른 정보가 있는데, 바로 총 레코드 수입니다. 다음 쿼리를 통해 총 레코드 수를 쉽게 얻을 수 있습니다.

그러나 위의 SQL은 InnoDB를 스토리지 엔진으로 사용할 경우 9.28초가 소요됩니다. 잘못된 최적화는 SQL_CALC_FOUND_ROWS를 사용하는 것입니다. SQL_CALC_FOUND_ROWS는 페이징 쿼리 중에 조건을 충족하는 레코드 수를 미리 준비한 다음 select FOUND_ROWS()를 실행하여 총 레코드 수를 가져올 수 있습니다. 그러나 대부분의 경우 쿼리 문이 짧다고 해서 성능이 향상되는 것은 아닙니다. 불행하게도 이 페이징 쿼리 방법은 많은 주류 프레임워크에서 사용됩니다. 이 문의 쿼리 성능을 살펴보겠습니다.

이 명령문은 이전 명령문보다 2배 늘어난 20.02초가 걸립니다. 페이징에 SQL_CALC_FOUND_ROWS를 사용하는 것은 매우 나쁜 생각입니다.

최적화 방법을 살펴보겠습니다. 글은 크게 두 부분으로 나누어져 있는데, 첫 번째 부분은 총 레코드 수를 구하는 방법, 두 번째 부분은 실제 레코드를 구하는 방법입니다.

행 수를 효율적으로 계산

사용된 엔진이 MyISAM인 경우 COUNT(*)를 직접 실행하여 행 수를 얻을 수 있습니다. 마찬가지로 힙 테이블에서는 행 번호도 테이블의 메타정보에 저장됩니다. 그러나 엔진이 InnoDB인 경우 상황은 더욱 복잡해집니다. InnoDB는 테이블에 특정 행 수를 저장하지 않기 때문입니다.

행 수를 캐시한 다음 데몬 프로세스를 통해 정기적으로 업데이트하거나 일부 사용자 작업으로 인해 캐시가 유효하지 않게 되는 경우 다음 명령문을 실행할 수 있습니다.

CREATE TABLE city (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  city varchar(128) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

기록을 받아보세요

이제 이 기사의 가장 중요한 부분을 입력하고 페이지 매김에 표시할 레코드를 얻으십시오. 위에서 언급한 것처럼 큰 오프셋은 성능에 영향을 미치므로 쿼리문을 다시 작성해야 합니다. 시연을 위해 새 테이블 "news"를 만들고 주제별로 정렬한 다음(최신 릴리스가 맨 위에 있음) 고성능 페이징을 구현합니다. 단순화를 위해 최신 보도자료의 ID도 가장 크다고 가정합니다.

보다 효율적인 방법은 사용자가 표시한 마지막 뉴스 ID를 기반으로 합니다. 다음 페이지를 쿼리하는 구문은 다음과 같습니다. 현재 페이지에 표시되는 마지막 ID를 전달해야 합니다.

이전 페이지를 쿼리하는 명령문은 현재 페이지의 첫 번째 ID를 전달해야 하고 순서를 반대로 해야 한다는 점을 제외하면 비슷합니다.

위의 쿼리 방법은 간단한 페이징에 적합합니다. 즉, 특정 페이지 탐색이 표시되지 않고 "이전 페이지"와 "다음 페이지"만 표시됩니다. 예를 들어 블로그의 바닥글에는 "이전 페이지"와 "다음 페이지"가 ​​표시됩니다. " 버튼. 하지만 여전히 실제 페이지 탐색이 어렵다면 다른 방법을 살펴보겠습니다.

위의 구문을 통해 각 페이징 버튼별로 오프셋에 해당하는 id를 계산할 수 있습니다. 이 접근 방식에는 또 다른 이점이 있습니다. 웹사이트에 새 기사가 게시되고 있다고 가정하면 모든 기사의 위치가 한 위치 뒤로 이동하므로 사용자가 기사를 게시할 때 페이지를 변경하면 기사가 두 번 표시됩니다. 각 버튼의 오프셋 ID를 고정하면 이 문제가 해결됩니다. Mark Callaghan은 결합된 인덱스와 두 개의 위치 변수를 사용하여 유사한 블로그를 게시했지만 기본 아이디어는 동일합니다.

　　如果表中的记录很少被删除、修改，还可以将记录对应的页码存储到表中，并在该列上创建合适的索引。采用这种方式，当新增一个记录的时候，需要执行下面的查询重新生成对应的页号。

SET p:= 0;
UPDATE news SET page=CEIL((p:= p + 1) / $perpage) ORDER BY id DESC;

　　当然，也可以新增一个专用于分页的表，可以用个后台程序来维护。

UPDATE pagination T
JOIN (
   SELECT id, CEIL((p:= p + 1) / $perpage) page
   FROM news
   ORDER BY id
)C
ON C.id = T.id
SET T.page = C.page;

　　现在想获取任意一页的元素就很简单了：

SELECT *
FROM news A
JOIN pagination B ON A.id=B.ID
WHERE page=$offset;

　　还有另外一种与上种方法比较相似的方法来做分页，这种方式比较试用于数据集相对小，并且没有可用的索引的情况下—比如处理搜索结果时。在一个普通的服务器上执行下面的查询，当有2M条记录时，要耗费2sec左右。这种方式比较简单，创建一个用来存储所有Id的临时表即可(这也是最耗费性能的地方)。

CREATE TEMPORARY TABLE _tmp (KEY SORT(random))
SELECT id, FLOOR(RAND() * 0x8000000) random
FROM city;

ALTER TABLE _tmp ADD OFFSET INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, DROP INDEX SORT, ORDER BY random;

　　接下来就可以向下面一样执行分页查询了。

SELECT *
FROM _tmp
WHERE OFFSET >= $offset
ORDER BY OFFSET
LIMIT $perpage;

　　简单来说，对于分页的优化就是。。。避免数据量大时扫描过多的记录。

　　博客比较长，所以翻译的有些粗糙。。。，之后会在好好检查一遍的。在自己做测试时，有些查询时间与作者有点不一致，不过作者这篇博客是写于2011年的，so~不要在意具体数据，领会精神吧~~

위 내용은 MySQL: 페이징 최적화의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!