검색결과 출력 및 성능 문제

우리에게 익숙한 모든 애플리케이션의 일반적인 시나리오 중 하나는 특정 기준에 따라 데이터를 검색하고 이를 읽기 쉬운 형식으로 표시하는 것입니다. 정렬, 그룹화 및 페이징을 위한 추가 옵션이 있을 수도 있습니다. 이론적으로 작업은 사소한 것이지만 문제를 해결할 때 많은 개발자가 많은 실수를 저지르며 나중에 생산성이 저하됩니다. 이 문제를 해결하기 위한 다양한 옵션을 고려하고 가장 효과적인 구현을 선택하기 위한 권장 사항을 작성해 보겠습니다.

페이징 옵션 #1

마음속에 떠오르는 가장 간단한 옵션은 가장 고전적인 형태로 검색 결과를 페이지별로 표시하는 것입니다.

애플리케이션이 관계형 데이터베이스를 사용한다고 가정해 보겠습니다. 이 경우 이 형식으로 정보를 표시하려면 두 가지 SQL 쿼리를 실행해야 합니다.

• 현재 페이지에 대한 행을 가져옵니다.
• 검색 기준에 해당하는 총 줄 수를 계산합니다. 이는 페이지를 표시하는 데 필요합니다.

테스트 MS SQL 데이터베이스를 예로 들어 첫 번째 쿼리를 살펴보겠습니다. 어드벤처웍스 2016년 서버용. 이를 위해 Sales.SalesOrderHeader 테이블을 사용합니다.

SELECT * FROM Sales.SalesOrderHeader
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY

위 쿼리는 목록에서 처음 50개의 주문을 추가 날짜를 기준으로 내림차순으로 정렬하여 반환합니다. 즉, 가장 최근 주문 50개를 반환합니다.

테스트 기반에서는 빠르게 실행되지만 실행 계획과 I/O 통계를 살펴보겠습니다.

Table 'SalesOrderHeader'. Scan count 1, logical reads 698, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

쿼리 런타임에서 SET STATISTICS IO ON 명령을 실행하여 각 쿼리에 대한 I/O 통계를 얻을 수 있습니다.

실행 계획에서 볼 수 있듯이 리소스를 가장 많이 사용하는 옵션은 소스 테이블의 모든 행을 추가된 날짜별로 정렬하는 것입니다. 문제는 테이블에 더 많은 행이 나타날수록 정렬이 "더 어려워진다"는 것입니다. 실제로는 이런 상황을 피해야 하므로 추가 날짜에 인덱스를 추가하여 리소스 소비가 변경되었는지 살펴보겠습니다.

Table 'SalesOrderHeader'. Scan count 1, logical reads 165, physical reads 0, read-ahead reads 5, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

확실히 많이 좋아졌습니다. 그런데 문제가 다 해결됐나요? 총 상품 비용이 $100를 초과하는 주문을 검색하도록 쿼리를 변경해 보겠습니다.

SELECT * FROM Sales.SalesOrderHeader
WHERE SubTotal > 100
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY

Table 'SalesOrderHeader'. Scan count 1, logical reads 1081, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

재미있는 상황이 있습니다. 쿼리 계획은 이전 계획보다 크게 나쁘지는 않지만 실제 논리적 읽기 수는 전체 테이블 스캔의 거의 두 배입니다. 탈출구가 있습니다. 기존 인덱스에서 복합 인덱스를 만들고 상품의 총 가격을 두 번째 필드로 추가하면 다시 165개의 논리적 읽기를 얻게 됩니다.

CREATE INDEX IX_SalesOrderHeader_OrderDate_SubTotal on Sales.SalesOrderHeader(OrderDate, SubTotal);

이 일련의 예는 오랫동안 계속될 수 있지만 여기서 표현하고 싶은 두 가지 주요 생각은 다음과 같습니다.

• 검색 쿼리에 새로운 기준이나 정렬 순서를 추가하면 검색 쿼리 속도에 상당한 영향을 미칠 수 있습니다.
• 그러나 검색어와 일치하는 모든 결과가 아닌 데이터의 일부만 빼야 하는 경우 이러한 쿼리를 최적화하는 방법은 여러 가지가 있습니다.

이제 처음에 언급한 두 번째 쿼리, 즉 검색 기준을 충족하는 레코드 수를 계산하는 쿼리로 넘어가겠습니다. 동일한 예를 들어보겠습니다. 즉, $100가 넘는 주문을 검색합니다.

SELECT COUNT(1) FROM Sales.SalesOrderHeader
WHERE SubTotal > 100

위에 표시된 종합 지수를 고려하면 다음을 얻습니다.

Table 'SalesOrderHeader'. Scan count 1, logical reads 698, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

쿼리가 전체 인덱스를 통과한다는 사실은 놀라운 일이 아닙니다. 왜냐하면 SubTotal 필드가 첫 번째 위치에 있지 않아 쿼리가 이를 사용할 수 없기 때문입니다. SubTotal 필드에 다른 인덱스를 추가하면 문제가 해결되며 결과적으로 48개의 논리적 읽기만 제공됩니다.

수량 계산 요청에 대한 몇 가지 예를 더 제시할 수 있지만 본질은 동일하게 유지됩니다. 데이터 조각을 수신하고 총 금액을 계산하는 것은 근본적으로 다른 두 가지 요청입니다., 각각은 최적화를 위한 자체 조치가 필요합니다. 일반적으로 두 쿼리 모두에 대해 동일하게 작동하는 인덱스 조합을 찾을 수 없습니다.

따라서 이러한 검색 솔루션을 개발할 때 명확히 해야 할 중요한 요구 사항 중 하나는 발견된 개체의 총 개수를 확인하는 것이 비즈니스에 정말 중요한지 여부입니다. 그렇지 않은 경우가 종종 있습니다. 그리고 제 생각에는 특정 페이지 번호로 탐색하는 것은 범위가 매우 좁은 솔루션입니다. 대부분의 페이징 시나리오가 "다음 페이지로 이동"처럼 보이기 때문입니다.

페이징 옵션 #2

사용자가 발견된 개체의 총 개수를 아는 데 관심이 없다고 가정해 보겠습니다. 검색 페이지를 단순화해 보겠습니다.

실제로 변경된 유일한 점은 특정 페이지 번호로 이동할 수 있는 방법이 없다는 점이며, 이제 이 테이블은 이를 표시하기 위해 페이지 번호가 몇 개인지 알 필요가 없습니다. 그러나 질문이 생깁니다. (“다음” 링크를 올바르게 표시하기 위해) 다음 페이지에 대한 데이터가 있는지 테이블이 어떻게 알 수 있습니까?

대답은 매우 간단합니다. 표시에 필요한 것보다 하나 더 많은 레코드를 데이터베이스에서 읽을 수 있으며, 이 "추가" 레코드가 있으면 다음 부분이 있는지 여부가 표시됩니다. 이렇게 하면 한 페이지의 데이터를 가져오기 위해 하나의 요청만 실행하면 되므로 성능이 크게 향상되고 해당 기능을 더 쉽게 지원할 수 있습니다. 실제로는 총 레코드 수 계산을 거부하면 결과 전달 속도가 4~5배 빨라진 경우가 있었습니다.

이 접근 방식에는 여러 가지 사용자 인터페이스 옵션이 있습니다. 위의 예에서와 같이 "뒤로" 및 "앞으로" 명령, 표시된 결과에 단순히 새 부분을 추가하는 "더 보기" 버튼, 작동하는 "무한 스크롤" "더 로드"라는 원칙에 따라 다음 부분을 가져오라는 신호는 사용자가 표시된 모든 결과를 끝까지 스크롤하라는 것입니다. 시각적 솔루션이 무엇이든 데이터 샘플링의 원칙은 동일하게 유지됩니다.

페이징 구현의 뉘앙스

위에 제공된 모든 쿼리 예제는 쿼리 자체에서 결과 행의 순서와 반환해야 하는 행 수를 지정하는 경우 "오프셋 + 개수" 접근 방식을 사용합니다. 먼저, 이 경우 매개변수 전달을 구성하는 가장 좋은 방법을 살펴보겠습니다. 실제로 저는 여러 가지 방법을 접했습니다.

• 요청된 페이지의 일련번호(pageIndex), 페이지 크기(pageSize).
• 반환될 첫 번째 레코드의 일련 번호(startIndex), 결과의 최대 레코드 수(count)입니다.
• 반환될 첫 번째 레코드의 시퀀스 번호(startIndex), 반환될 마지막 레코드의 시퀀스 번호(endIndex)입니다.

언뜻 보면 이는 너무 초보적이어서 차이가 없는 것처럼 보일 수 있습니다. 그러나 이것은 그렇지 않습니다. 가장 편리하고 보편적인 옵션은 두 번째 옵션(startIndex, count)입니다. 여기에는 몇 가지 이유가 있습니다.

• 위에 제공된 +1 항목 교정 접근 방식의 경우 pageIndex 및 pageSize를 사용하는 첫 번째 옵션은 매우 불편합니다. 예를 들어 페이지당 50개의 게시물을 표시하려고 합니다. 위의 알고리즘에 따르면 필요한 것보다 하나 더 많은 레코드를 읽어야 합니다. 이 "+1"이 서버에 구현되지 않은 경우 첫 번째 페이지에서는 1부터 51까지, 두 번째 페이지에서는 51부터 101까지의 레코드를 요청해야 합니다. 페이지 크기를 51로 지정하고 pageIndex를 늘리면 두 번째 페이지는 52에서 102로 반환됩니다. 따라서 첫 번째 옵션에서 다음 페이지로 이동하는 버튼을 적절하게 구현하는 유일한 방법은 서버가 "추가" 줄을 교정하도록 하는 것인데, 이는 매우 암묵적인 뉘앙스가 될 것입니다.
• 세 번째 옵션은 전혀 의미가 없습니다. 대부분의 데이터베이스에서 쿼리를 실행하려면 마지막 레코드의 인덱스 대신 개수를 전달해야 하기 때문입니다. endIndex에서 startIndex를 빼는 것은 간단한 산술 연산일 수 있지만 여기서는 불필요한 작업입니다.

이제 "오프셋 + 수량"을 통해 페이징을 구현할 때의 단점을 설명해야 합니다.

• 각 후속 페이지를 검색하는 것은 이전 페이지보다 비용이 많이 들고 느립니다. 왜냐하면 데이터베이스는 여전히 검색 및 정렬 기준에 따라 "처음부터" 모든 레코드를 검색한 다음 원하는 조각에서 중지해야 하기 때문입니다.
• 모든 DBMS가 이 접근 방식을 지원할 수 있는 것은 아닙니다.

대안이 있지만 불완전하기도 합니다. 이러한 접근 방식 중 첫 번째는 "키 세트 페이징" 또는 "탐색 방법"이라고 하며 다음과 같습니다. 부분을 받은 후 페이지의 마지막 레코드에 있는 필드 값을 기억한 다음 이를 사용하여 얻을 수 있습니다. 다음 부분. 예를 들어 다음 쿼리를 실행했습니다.

SELECT * FROM Sales.SalesOrderHeader
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY

그리고 마지막 레코드에서는 주문 날짜 값 '2014-06-29'를 얻었습니다. 그런 다음 다음 페이지를 얻으려면 다음을 수행하십시오.

SELECT * FROM Sales.SalesOrderHeader
WHERE OrderDate < '2014-06-29'
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY

문제는 OrderDate가 고유하지 않은 필드이고 위에 지정된 조건에서 많은 필수 행이 누락될 가능성이 있다는 것입니다. 이 쿼리에 명확성을 추가하려면 조건에 고유 필드를 추가해야 합니다(75074가 첫 번째 부분의 기본 키의 마지막 값이라고 가정).

SELECT * FROM Sales.SalesOrderHeader
WHERE (OrderDate = '2014-06-29' AND SalesOrderID < 75074)
   OR (OrderDate < '2014-06-29')
ORDER BY OrderDate DESC, SalesOrderID DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY

이 옵션은 올바르게 작동하지만 일반적으로 조건에 OR 연산자가 포함되어 있으므로 최적화하기가 어렵습니다. OrderDate가 증가함에 따라 기본 키 값이 증가하는 경우 SalesOrderID를 기준으로 필터만 남겨두면 조건이 단순화될 수 있습니다. 그러나 기본 키 값과 결과가 정렬되는 필드 사이에 엄격한 상관 관계가 없으면 대부분의 DBMS에서는 이 OR을 피할 수 없습니다. 내가 아는 예외는 튜플 비교를 완벽하게 지원하는 PostgreSQL이며, 위의 조건은 "WHERE (OrderDate, SalesOrderID) < ('2014-06-29', 75074)"로 작성할 수 있습니다. 이 두 필드가 포함된 복합 키가 주어지면 이와 같은 쿼리는 매우 쉽습니다.

두 번째 대체 접근법은 예를 들어 다음에서 찾을 수 있습니다. ElasticSearch 스크롤 API 또는 코스모스 DB — 요청이 데이터 외에 데이터의 다음 부분을 가져올 수 있는 특수 식별자를 반환하는 경우. 이 식별자의 수명이 무제한인 경우(Comsos DB에서와 같이) 이는 페이지 간 순차적 전환을 사용하여 페이징을 구현하는 좋은 방법입니다(위에서 언급한 옵션 #2). 가능한 단점: 모든 DBMS에서 지원되지는 않습니다. 결과적인 다음 청크 식별자는 수명이 제한될 수 있으며 이는 일반적으로 사용자 상호 작용(예: ElasticSearch 스크롤 API)을 구현하는 데 적합하지 않습니다.

복잡한 필터링

작업을 더욱 복잡하게 만들어 보겠습니다. 온라인 상점의 모든 사람에게 매우 친숙한 소위 패싯 검색을 구현해야 한다는 요구 사항이 있다고 가정합니다. 주문 테이블을 기반으로 한 위의 예는 이 경우에는 그다지 설명적이지 않으므로 AdventureWorks 데이터베이스에서 Product 테이블로 전환해 보겠습니다.

패싯 검색의 아이디어는 무엇입니까? 사실은 각 필터 요소에 대해 이 기준을 충족하는 레코드 수가 표시된다는 것입니다. 다른 모든 카테고리에서 선택된 필터를 고려합니다..

예를 들어, 이 예에서 자전거 카테고리와 검은색 색상을 선택하면 테이블에는 검은색 자전거만 표시되지만 다음과 같습니다.

• 카테고리 그룹의 각 기준에 대해 해당 카테고리의 제품 수가 검은색으로 표시됩니다.
• "색상" 그룹의 각 기준에 대해 해당 색상의 자전거 수가 표시됩니다.

다음은 이러한 조건에 대한 결과 출력의 예입니다.

'의류' 카테고리도 체크하면, 재고가 있는 검은색 옷도 표에 표시됩니다. "색상" 섹션의 검정색 제품 수도 새 조건에 따라 다시 계산되며 "카테고리" 섹션에서만 아무것도 변경되지 않습니다... 이러한 예가 일반적인 패싯 검색 알고리즘을 이해하는 데 충분하기를 바랍니다.

이제 이것이 관계형 기반에서 어떻게 구현될 수 있는지 상상해 봅시다. 카테고리 및 색상과 같은 각 기준 그룹에는 별도의 쿼리가 필요합니다.

SELECT pc.ProductCategoryID, pc.Name, COUNT(1) FROM Production.Product p
  INNER JOIN Production.ProductSubcategory ps ON p.ProductSubcategoryID = ps.ProductSubcategoryID
  INNER JOIN Production.ProductCategory pc ON ps.ProductCategoryID = pc.ProductCategoryID
WHERE p.Color = 'Black'
GROUP BY pc.ProductCategoryID, pc.Name
ORDER BY COUNT(1) DESC

SELECT Color, COUNT(1) FROM Production.Product p
  INNER JOIN Production.ProductSubcategory ps ON p.ProductSubcategoryID = ps.ProductSubcategoryID
WHERE ps.ProductCategoryID = 1 --Bikes
GROUP BY Color
ORDER BY COUNT(1) DESC

이 솔루션에 어떤 문제가 있나요? 매우 간단합니다. 확장이 잘 되지 않습니다. 각 필터 섹션에는 수량을 계산하기 위한 별도의 쿼리가 필요하며 이러한 쿼리는 가장 쉽지 않습니다. 온라인 상점에서는 일부 카테고리에 수십 개의 필터 섹션이 있을 수 있으며 이는 심각한 성능 문제가 될 수 있습니다.

일반적으로 이러한 진술 후에는 다음과 같은 몇 가지 해결책이 제공됩니다.

• 모든 수량 개수를 하나의 쿼리로 결합합니다. 기술적으로 이는 UNION 키워드를 사용하여 가능하지만 성능에 큰 도움이 되지 않습니다. 데이터베이스는 여전히 각 조각을 처음부터 실행해야 합니다.
• 캐시 수량. 이것은 내가 문제를 설명할 때마다 거의 제안됩니다. 주의할 점은 이것이 일반적으로 불가능하다는 것입니다. 10개의 "패싯"이 있고 각 패싯에는 5개의 값이 있다고 가정해 보겠습니다. 이는 동일한 온라인 상점에서 볼 수 있는 것과 비교하면 매우 "보통" 상황입니다. 하나의 패싯 요소를 선택하면 다른 9개 요소의 수량에 영향을 미칩니다. 즉, 각 기준 조합에 대해 수량은 다를 수 있습니다. 이 예에서는 사용자가 선택할 수 있는 기준이 총 50개이므로 가능한 조합은 250개입니다. 이러한 데이터 배열을 채울 메모리나 시간이 부족합니다. 여기에서 모든 조합이 실제는 아니며 사용자가 5~10개 이상의 기준을 거의 선택하지 않는다고 이의를 제기하고 말할 수 있습니다. 예, 지연 로딩을 수행하고 선택한 항목만 캐시하는 것이 가능합니다. 그러나 선택 항목이 많을수록 캐시의 효율성이 떨어지고 응답 시간 문제가 더 눈에 띄게 됩니다(특히 다음과 같은 경우). 데이터 세트는 정기적으로 변경됩니다).

다행스럽게도 이러한 문제는 오랫동안 대용량 데이터에서 예측 가능하게 작동하는 매우 효과적인 솔루션을 제공해 왔습니다. 이러한 옵션의 경우 패싯 재계산과 결과 페이지 수신을 서버에 대한 두 개의 병렬 호출로 나누고 패싯별로 데이터를 로드하는 것이 디스플레이를 "방해하지 않는" 방식으로 사용자 인터페이스를 구성하는 것이 합리적입니다. 검색 결과.

• 가능한 한 드물게 "패싯"의 완전한 재계산을 호출하십시오. 예를 들어 검색 기준이 변경될 때마다 모든 것을 다시 계산하지 말고 대신 현재 조건과 일치하는 총 결과 수를 찾아 사용자에게 "1425개의 레코드를 찾았습니다. 표시하시겠습니까?"라는 메시지를 표시하도록 요청합니다. 사용자는 계속해서 검색어를 변경하거나 "표시" 버튼을 클릭할 수 있습니다. 두 번째 경우에만 결과를 얻고 모든 "패싯"의 수량을 다시 계산하기 위한 모든 요청이 실행됩니다. 이 경우 쉽게 알 수 있듯이 총 결과 수와 최적화를 얻기 위한 요청을 처리해야 합니다. 이 방법은 많은 소규모 온라인 상점에서 찾을 수 있습니다. 분명히 이것이 이 문제에 대한 만병통치약은 아니지만 간단한 경우에는 좋은 절충안이 될 수 있습니다.
• 검색 엔진을 사용하여 Solr, ElasticSearch, Sphinx 등과 같은 결과를 찾고 측면을 계산합니다. 이들 모두는 "패싯"을 구축하도록 설계되었으며 반전된 인덱스로 인해 이를 매우 효율적으로 수행합니다. 검색 엔진의 작동 방식, 이러한 경우 범용 데이터베이스보다 더 효과적인 이유, 관행 및 함정은 무엇입니까? 이것은 별도의 기사에 대한 주제입니다. 여기서는 검색 엔진이 기본 데이터 저장소를 대체할 수 없으며 추가로 사용된다는 사실에 주목하고 싶습니다. 검색과 관련된 기본 데이터베이스의 모든 변경 사항은 검색 색인에 동기화됩니다. 검색 엔진은 일반적으로 검색 엔진과만 상호 작용하며 기본 데이터베이스에는 액세스하지 않습니다. 여기서 가장 중요한 점 중 하나는 이 동기화를 안정적으로 구성하는 방법입니다. 그것은 모두 "반응 시간" 요구 사항에 따라 다릅니다. 기본 데이터베이스의 변경 사항과 검색 시 "표시" 사이의 시간이 중요하지 않은 경우 최근 변경된 기록을 몇 분마다 검색하고 색인화하는 서비스를 만들 수 있습니다. 가능한 가장 짧은 응답 시간을 원한다면 다음과 같은 것을 구현할 수 있습니다. 거래 발신함 검색 서비스에 업데이트를 보냅니다.

조사 결과

1. 서버 측 페이징을 구현하는 것은 상당히 복잡하며 빠르게 성장하거나 단순히 대규모 데이터 세트에만 적합합니다. "큰" 또는 "빠른 성장"을 평가하는 방법에 대한 정확한 방법은 없지만 다음 접근 방식을 따릅니다.
   • 서버 시간과 네트워크 전송을 고려하여 완전한 데이터 수집을 수신하여 일반적으로 성능 요구 사항에 맞는 경우 서버 측에서 페이징을 구현하는 것은 의미가 없습니다.
   • 데이터가 적지만 수집되는 데이터가 지속적으로 늘어나고 있기 때문에 가까운 시일 내에 성능 문제가 없을 것으로 예상되는 상황이 있을 수 있습니다. 미래의 일부 데이터 집합이 더 이상 이전 지점을 충족하지 못할 경우 즉시 페이징을 시작하는 것이 좋습니다.
2. 전체 결과 수 또는 페이지 번호 표시에 대한 비즈니스 측의 엄격한 요구 사항이 없고 시스템에 검색 엔진이 없는 경우 이러한 사항을 구현하지 않고 옵션 #2를 고려하는 것이 좋습니다.
3. 패싯 검색에 대한 명확한 요구 사항이 있는 경우 성능 저하 없이 두 가지 옵션이 있습니다.
   • 검색 기준이 변경될 때마다 모든 수량을 다시 계산하지 마십시오.
   • Solr, ElasticSearch, Sphinx 등과 같은 검색 엔진을 사용하십시오. 그러나 이는 기본 데이터베이스를 대체할 수 없으며 검색 문제를 해결하기 위해 기본 저장소에 추가로 사용해야 한다는 점을 이해해야 합니다.
4. 또한 패싯 검색의 경우 검색 결과 페이지 검색과 계산을 두 개의 병렬 요청으로 분할하는 것이 합리적입니다. 수량을 계산하는 것은 결과를 얻는 것보다 더 오래 걸릴 수 있지만 결과는 사용자에게 더 중요합니다.
5. 검색을 위해 SQL 데이터베이스를 사용하는 경우 이 부분과 관련된 모든 코드 변경은 적절한 데이터 양(라이브 데이터베이스의 볼륨 초과)에 대한 성능을 잘 테스트해야 합니다. 또한 데이터베이스의 모든 인스턴스, 특히 "라이브" 인스턴스에서 쿼리 실행 시간을 모니터링하는 것이 좋습니다. 개발 단계에서는 쿼리 계획으로 모든 것이 괜찮았더라도 데이터의 양이 증가함에 따라 상황이 눈에 띄게 바뀔 수 있습니다.

출처 : habr.com