페이지를 매긴 쿼리에서 OFFSET 및 LIMIT를 사용하지 마세요.

데이터베이스 성능 최적화에 대해 걱정할 필요가 없었던 시대는 지났습니다. 시간은 가만히 있지 않습니다. 모든 새로운 기술 기업가는 차세대 Facebook을 만들고 싶어하며, 손에 넣을 수 있는 모든 데이터를 수집하려고 노력합니다. 기업이 돈을 버는 데 도움이 되는 모델을 더 잘 훈련하려면 이 데이터가 필요합니다. 이러한 상황에서 프로그래머는 엄청난 양의 정보를 빠르고 안정적으로 처리할 수 있는 API를 만들어야 합니다.

오랫동안 애플리케이션이나 데이터베이스 백엔드를 설계해 왔다면 페이지를 매긴 쿼리를 실행하는 코드를 작성해 보았을 것입니다. 예를 들어 다음과 같습니다.

SELECT * FROM table_name LIMIT 10 OFFSET 40

그 방법은 무엇입니까?

하지만 이렇게 페이지 매김을 수행했다면 가장 효율적인 방식으로 수행하지 않았다는 점을 유감스럽게 생각합니다.

나에게 반대하고 싶나요? 너는 할 수있다. 아니 지출하다 시간. 느슨하게, Shopify и 믹스 맥스 그들은 제가 오늘 이야기하고 싶은 기술을 이미 사용하고 있습니다.

한 번도 사용해본 적이 없는 백엔드 개발자를 한 명 이상 언급하세요. OFFSET и LIMIT 페이지가 매겨진 쿼리를 수행합니다. MVP(Minimum Viable Product) 및 소량의 데이터가 사용되는 프로젝트에서 이 접근 방식은 상당히 적용 가능합니다. 말하자면 "그냥 작동합니다".

하지만 처음부터 안정적이고 효율적인 시스템을 만들어야 한다면 그러한 시스템에서 사용되는 데이터베이스를 쿼리하는 효율성에 대해 미리 주의해야 합니다.

오늘 우리는 일반적으로 사용되는(너무 나쁜) 페이지 매김 쿼리 엔진 구현의 문제점과 이러한 쿼리를 실행할 때 높은 성능을 달성하는 방법에 대해 이야기하겠습니다.

OFFSET과 LIMIT에 어떤 문제가 있나요?

이미 말했듯이, OFFSET и LIMIT 많은 양의 데이터를 다룰 필요가 없는 프로젝트에서 좋은 성능을 발휘합니다.

데이터베이스가 더 이상 서버 메모리에 맞지 않는 크기로 커지면 문제가 발생합니다. 그러나 이 데이터베이스로 작업할 때는 페이지를 매긴 쿼리를 사용해야 합니다.

이 문제가 나타나려면 DBMS가 페이지가 매겨진 각 쿼리에 대해 비효율적인 전체 테이블 스캔 작업을 수행하는 상황이 있어야 합니다(삽입 및 삭제 작업이 발생할 수 있으며 오래된 데이터는 필요하지 않습니다!).

"전체 테이블 스캔"(또는 "순차 테이블 스캔", 순차 스캔)이란 무엇입니까? 이는 DBMS가 테이블의 각 행, 즉 그 안에 포함된 데이터를 순차적으로 읽어서 주어진 조건에 맞는지 확인하는 작업이다. 이 유형의 테이블 스캔은 가장 느린 것으로 알려져 있습니다. 사실 이 프로그램이 실행될 때 서버의 디스크 하위 시스템과 관련된 많은 입력/출력 작업이 수행됩니다. 디스크에 저장된 데이터 작업과 관련된 지연과 디스크에서 메모리로 데이터를 전송하는 작업이 리소스를 많이 사용한다는 사실로 인해 상황이 더욱 악화됩니다.

예를 들어, 100000000명의 사용자 레코드가 있고 다음 구문을 사용하여 쿼리를 실행합니다. OFFSET 50000000. 즉, DBMS는 이러한 모든 레코드를 로드하고(필요하지도 않습니다!) 메모리에 저장한 후 예를 들어 20개의 결과를 가져와야 합니다. LIMIT.

"50000에서 50020에서 100000까지의 행을 선택하세요"와 같이 보일 수 있다고 가정해 보겠습니다. 즉, 시스템은 쿼리를 완료하기 위해 먼저 50000개의 행을 로드해야 합니다. 그녀가 얼마나 불필요한 일을 해야 하는지 아시나요?

내 말을 믿을 수 없다면 내가 기능을 사용하여 만든 예제를 살펴보세요. db-fiddle.com. 

db-fiddle.com의 예

저기 왼쪽 들판에 Schema SQL, 데이터베이스에 100000개의 행을 삽입하는 코드가 있고 오른쪽에는 필드에 Query SQL, 두 개의 쿼리가 표시됩니다. 첫 번째 느린 것은 다음과 같습니다.

SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;

그리고 동일한 문제에 대한 효과적인 해결책인 두 번째 방법은 다음과 같습니다.

SELECT *
FROM `docs`
WHERE id > 85000
LIMIT 10;

이러한 요청을 이행하려면 버튼을 클릭하세요. Run 페이지 상단에 있습니다. 이를 수행한 후 쿼리 실행 시간에 대한 정보를 비교합니다. 비효율적인 쿼리를 실행하는 것은 두 번째 쿼리를 실행하는 것보다 최소 30배 더 오래 걸리는 것으로 나타났습니다(이 시간은 실행마다 다릅니다. 예를 들어 시스템은 첫 번째 쿼리를 완료하는 데 37ms가 걸렸다고 보고할 수 있지만 초 - 1ms).

그리고 더 많은 데이터가 있으면 모든 것이 더 나빠 보일 것입니다. (이를 확인하려면 내 예 천만 행).

방금 논의한 내용을 통해 데이터베이스 쿼리가 실제로 처리되는 방식에 대한 통찰력을 얻을 수 있습니다.

값이 높을수록 주의하세요. OFFSET — 요청을 완료하는 데 시간이 더 오래 걸립니다.

OFFSET과 LIMIT의 조합 대신 무엇을 사용해야 합니까?

조합 대신 OFFSET и LIMIT 다음 구성표에 따라 구축된 구조를 사용하는 것이 좋습니다.

SELECT * FROM table_name WHERE id > 10 LIMIT 20

이는 커서 기반 페이지 매김을 사용한 쿼리 실행입니다.

현재 항목을 로컬에 저장하는 대신 OFFSET и LIMIT 각 요청과 함께 이를 전송하려면 마지막으로 수신한 기본 키를 저장해야 합니다(일반적으로 이는 ID) 그리고 LIMIT, 결과적으로 위와 유사한 질의를 얻게 된다.

왜? 요점은 읽은 마지막 행의 식별자를 명시적으로 지정함으로써 필요한 데이터 검색을 시작해야 하는 위치를 DBMS에 알려준다는 것입니다. 또한 키를 사용하면 검색이 효율적으로 수행되므로 지정된 범위를 벗어난 줄로 인해 시스템이 산만해질 필요가 없습니다.

다양한 쿼리에 대한 성능 비교를 다음과 같이 살펴보겠습니다. 다음은 비효율적인 쿼리입니다.

느린 요청

다음은 이 요청의 최적화된 버전입니다.

빠른 요청

두 쿼리 모두 정확히 동일한 양의 데이터를 반환합니다. 그런데 첫 번째는 완료하는 데 12,80초가 걸리고, 두 번째는 0,01초가 걸립니다. 차이점을 느끼시나요?

가능한 문제

제안된 쿼리 방법이 효과적으로 작동하려면 테이블에 정수 식별자와 같은 고유한 순차적 인덱스가 포함된 열이 있어야 합니다. 일부 특정 경우에는 이러한 쿼리를 사용하여 데이터베이스 작업 속도를 높이는 성공 여부가 결정될 수 있습니다.

당연히 쿼리를 구성할 때 테이블의 특정 아키텍처를 고려하고 기존 테이블에 가장 잘 작동하는 메커니즘을 선택해야 합니다. 예를 들어, 대량의 관련 데이터가 포함된 쿼리 작업을 수행해야 하는 경우 흥미로울 수 있습니다. 이 조.

예를 들어 다대다 관계의 테이블이 있는 경우와 같이 기본 키가 누락되는 문제에 직면한 경우 다음을 사용하는 전통적인 접근 방식을 사용합니다. OFFSET и LIMIT, 우리에게 적합하다는 것이 보장됩니다. 그러나 이를 사용하면 쿼리 속도가 느려질 수 있습니다. 이러한 경우에는 페이지가 매겨진 쿼리를 처리하는 데만 필요하더라도 자동 증가 기본 키를 사용하는 것이 좋습니다.

이 주제에 관심이 있다면 - 여기에, 여기에 и 여기에 - 몇 가지 유용한 자료.

결과

우리가 도출할 수 있는 주요 결론은 우리가 말하는 데이터베이스의 크기에 관계없이 항상 쿼리 실행 속도를 분석해야 한다는 것입니다. 요즘에는 솔루션의 확장성이 매우 중요하며, 특정 시스템에서 작업 시작부터 모든 것이 올바르게 설계되면 앞으로 개발자가 많은 문제를 겪지 않게 할 수 있습니다.

데이터베이스 쿼리를 어떻게 분석하고 최적화합니까?

출처 : habr.com