MySQL 페이징 성능 최적화 가이드

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

성능이 좋지 않은 자주 사용되는 쿼리를 살펴보겠습니다.

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

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

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

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

SELECT *
FROM city
ORDER BY id DESC
LIMIT 100000, 15;

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

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

SELECT COUNT(*)
FROM city;

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

SELECT SQL_CALC_FOUND_ROWS *
FROM city
ORDER BY id DESC
LIMIT 100000, 15;

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

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

효율적인 행 수 계산

사용한 엔진이 MyISAM인 경우 COUNT(*)를 직접 실행하여 행 수를 구할 수 있습니다. 마찬가지로 힙 테이블에서는 행 번호도 테이블의 메타정보에 저장됩니다. 그러나 엔진이 InnoDB인 경우 상황은 더욱 복잡해집니다. InnoDB는 테이블에 특정 행 수를 저장하지 않기 때문입니다.
행 수를 캐시한 다음 데몬 프로세스를 통해 정기적으로 업데이트할 수 있습니다. 또는 일부 사용자 작업으로 인해 캐시가 무효화되는 경우 다음 명령문을 실행할 수 있습니다.

SELECT COUNT(*)
FROM city
USE INDEX(PRIMARY);

기록 가져오기

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

CREATE TABLE news(
   id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
   title VARCHAR(128) NOT NULL
) ENGINE=InnoDB;

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

SELECT *
FROM news WHERE id < $last_id
ORDER BY id DESC
LIMIT $perpage

현재 페이지의 첫 번째 ID를 역순으로 전달해야 한다는 점을 제외하면 이전 페이지를 쿼리하는 명령문은 유사합니다.

SELECT *
FROM news WHERE id > $last_id
ORDER BY id ASC
LIMIT $perpage

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

SELECT id
FROM (
   SELECT id, ((@cnt:= @cnt + 1) + $perpage - 1) % $perpage cnt
   FROM news 
   JOIN (SELECT @cnt:= 0)T
   WHERE id < $last_id
   ORDER BY id DESC
   LIMIT $perpage * $buttons
)C
WHERE cnt = 0;

通过上面的语句可以为每一个分页的按钮计算出一个offset对应的id。这种方法还有一个好处。假设，网站上正在发布一片新的文章，那么所有文章的位置都会往后移一位，所以如果用户在发布文章时换页，那么他会看见一篇文章两次。如果固定了每个按钮的offset 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;

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

以上就是MySQL分页性能优化指南的内容，更多相关内容请关注PHP中文网（www.php.cn）！