Mysql 천만 레벨 빠른 페이징을 최적화하는 방법

많은 애플리케이션에서 최신 또는 가장 인기 있는 기록만 표시하는 경우가 많지만 기존 기록을 계속 액세스할 수 있도록 페이지 지정 탐색 표시줄이 필요합니다. 그러나 MySQL 을 통해 페이지를 더 잘 구현하는 방법은 항상 골치 아픈 문제입니다. 가지고 오지 않으면 사용할 수 있는 해결 방법은 없지만 데이터베이스의 밑바닥을 알면 페이지 조회를 최적화하는 데 도움이 된다.

먼저 일반적이지만 성능이 좋지 않은 쿼리를 살펴보겠습니다.

select *

from city

order by id desc

limit0 소, 이 문의에 무슨 문제가 있나요? 실제로 이 질의문과 매개 변수는 아래 표의 기본 키를 사용하고 15 개의 레코드만 읽기 때문에 문제가 없습니다.

create tablecity (

id int (10) unsigned not null auto _ increment,

진짜 문제는 다음과 같이 offset (페이징 오프셋) 이 큰 경우입니다.

select *

from city <

위 쿼리는 2M 행 레코드가 있을 때 0.22sec 가 필요합니다. EXPLAIN 을 통해 SQL 실행 계획을 보면 SQL 이 100015 행을 검색했지만 결국 15 행만 필요합니다. 페이지 오프셋이 크면 사용되는 데이터가 증가하고 MySQL 은 결국 사용되지 않는 많은 양의 데이터를 메모리로 로드합니다. 대부분의 웹 사이트 사용자가 처음 몇 페이지의 데이터만 액세스한다고 가정하더라도 큰 페이지 오프셋 요청은 전체 시스템에 해를 끼칠 수 있습니다. 페이스북은 이를 인식하고 있지만 페이스북은 초당 더 많은 요청을 처리할 수 있도록 데이터베이스를 최적화하는 것이 아니라 요청 응답 시간의 차이를 줄이는 데 초점을 맞추고 있다.

페이징 요청의 경우 총 * * * 레코드 수라는 정보도 중요합니다. 우리는 아래의 질의를 통해 총 레코드 수를 쉽게 얻을 수 있다.

count 선택 (*)

from city;

그러나 위 SQL 은 InnoDB 를 스토리지 엔진으로 사용하는 데 9.28sec 가 필요합니다. 잘못된 최적화는 SQL_CALC_FOUND_ROWS 를 사용하는 것입니다. SQL_CALC_FOUND_ROWS 는 페이지 질의를 할 때 조건에 맞는 레코드 수를 미리 준비한 다음 select found _ rows 만 실행할 수 있습니다 총 기록 수를 얻을 수 있습니다. 그러나 대부분의 경우 질의문이 짧다고 해서 성능이 향상되는 것은 아닙니다. 불행히도, 이 페이지 쿼리 방법은 많은 주류 프레임워크에서 사용됩니다. 이 문의 쿼리 성능을 살펴보겠습니다.

select SQL _ calc _ found _ rows *

from city

order by iii

이 명령문은 20.02sec 가 소요되며 이전 명령문의 두 배입니다. SQL_CALC_FOUND_ROWS 를 페이징으로 사용하는 것은 나쁜 생각임이 밝혀졌습니다.

최적화 방법을 살펴보겠습니다. 문장 부분은 두 부분으로 나뉩니다. 첫 번째 부분은 총 레코드 수를 얻는 방법이고 두 번째 부분은 실제 레코드를 얻는 방법입니다.

효율적인 계산된 행 수

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

행 수를 캐시한 다음 데몬을 통해 정기적으로 업데이트하거나 사용자의 특정 작업으로 인해 캐시가 무효화될 경우

select count (*)

문을 실행할 수 있습니다

레코드 가져오기

이 문장 중 가장 중요한 부분으로 이동하여 페이지에 표시할 레코드를 가져옵니다. 앞서 언급했듯이 큰 오프셋은 성능에 영향을 줄 수 있으므로 쿼리 문을 다시 작성해야 합니다. 데모를 위해 새로운 표 "news" 를 만들어 시간별 정렬 (최근 발표된 것이 맨 앞에 있음) 을 기준으로 고성능 페이지를 만들었습니다. 간단히 하기 위해서, 우리는 새로 발표된 뉴스의 Id 도 가장 크다고 가정한다.

create table news (

id int unsigned primary key auto _ increment,

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

select *

from news where id lt; $ last _ Id

order by id desc

limit $ perpage

이전 페이지를 질의하는 명령문과 유사합니다.

select *

from news where id gt; $ last _ id

order by id ASC

limit $ perpage

위 쿼리 방법은 간단한 페이지 매김 구현에 적합합니다 그러나 실제 페이지 탐색을 달성하기가 어렵다면 다른 방법을 살펴 보겠습니다.

select id

from (

select id, (@ CNT: = @ CNT+; $ last _ id

order by id desc

limit $ perpage * $ buttons

위 문을 통해 각 페이지 지정 버튼에 대해 offset 의 id 를 계산할 수 있습니다. 이 방법에는 또 하나의 장점이 있다. 웹 사이트에 새로운 문장 한 장이 게시되고 있다면 모든 문장 위치가 한 단계 뒤로 이동하므로 사용자가 문장 게시 시 페이지를 변경하면 문장 한 편을 두 번 볼 수 있습니다. 각 버튼의 offset Id 를 고정하면 이 문제가 해결됩니다.

Mark Callaghan 은 조합 인덱스와 두 개의 위치 변수를 활용하는 유사한 블로그를 발표했지만 기본 사상은 일치했다.

테이블의 레코드가 거의 삭제 또는 수정되지 않은 경우 레코드에 해당하는 페이지 번호를 테이블에 저장하고 해당 열에 적절한 색인을 만들 수도 있습니다. 이런 식으로 새 레코드를 추가할 때 다음 질의를 수행하여 해당 페이지 번호를 다시 생성해야 합니다.

set p: = 0;

업데이트 news set page = ceil ((p: = p+1)/$ perpage) order by id desc;

물론 데몬으로 유지 관리할 수 있는 페이징 전용 테이블을 추가할 수도 있습니다.

업데이트 paginationt

join (

select id, ceil ((p: = p

이제 어떤 페이지든 원하는 요소를 쉽게 얻을 수 있습니다.

select *

from news a

joiii

< P > 는 데이터 세트가 비교적 작고 사용 가능한 색인이 없는 경우 (예: 검색 결과를 처리할 때) 페이지를 나누는 것과 유사한 또 다른 방법이 있습니다. 일반 서버에서 다음 쿼리를 수행하면 2M 개의 레코드가 있을 때 약 2sec 가 소요됩니다. 이 방법은 간단합니다. 모든 Id 를 저장할 임시 테이블을 만들면 됩니다 (성능이 가장 많이 소모되는 곳이기도 함).

create temporary table _ tmp (키 시작 (random))

select id, floor (r

alter table _ tmp add offset int unsigned primary key auto _ increment, DROP INDEX SORT, ORDER BY random；.

이제 다음과 같이 페이지 지정 쿼리를 수행할 수 있습니다.

select *

from _ tmp

where offset gt; = $ offset

order by offset

limit $ perpage;

간단히 말해서 페이징에 대한 최적화는 다음과 같습니다. 。 。 데이터 양이 많을 때 너무 많은 레코드를 스캔하지 마십시오.