대규모 데이터베이스의 각 범주에서 최신 항목을 효율적으로 검색하기 위해 SQL 쿼리를 최적화하려면 어떻게 해야 합니까?

대량 데이터 세트에 대한 SQL 쿼리 성능 향상

특히 대규모 데이터베이스와 복잡한 데이터 검색을 처리할 때 데이터베이스 성능을 유지하려면 효율적인 SQL 쿼리가 필수적입니다. 이는 상호 연결된 여러 테이블로 작업할 때 특히 중요합니다.

과제: 카테고리별 최신 항목 검색

아이디별로 분류된 항목을 저장하는 데이터베이스를 상상해 보세요. 목표는 가장 최근에 추가된 4개의 항목과 함께 각 카테고리를 표시하는 것입니다. 카테고리별로 여러 쿼리를 사용하는 기존 방법은 특히 카테고리 수가 많은 경우 데이터베이스 부하가 과도하게 발생합니다.

최적화된 쿼리: 그룹화 및 조인 활용

다음 최적화된 쿼리는 외부 조인과 그룹화를 사용하여 단일 데이터베이스 패스에서 원하는 데이터를 검색합니다.

<code class="language-sql">SELECT i1.*
FROM item i1
LEFT OUTER JOIN item i2
  ON (i1.category_id = i2.category_id AND i1.item_id < i2.item_id)
GROUP BY i1.item_id
HAVING COUNT(*) < 4;</code>

이 쿼리는 각 항목(i1)을 동일한 카테고리 내의 최신 항목(i2)과 비교합니다. 최신 항목이 4개 미만인 경우 i1이 결과에 포함됩니다.

대체 접근 방식

각 범주 내 행 번호 지정을 위한 MySQL 사용자 변수와 같은 다른 방법은 대체 솔루션을 제공합니다.

<code class="language-sql">SELECT *
FROM (
    SELECT i.*, @r := IF(@g = category_id, @r+1, 1) AS rownum, @g := category_id
    FROM (SELECT @g:=null, @r:=0) AS _init
    CROSS JOIN item i
    ORDER BY i.category_id, i.date_listed
) AS t
WHERE t.rownum <= 4;</code>

MySQL 8.0.3 이상의 경우 SQL 창 함수는 표준화된 접근 방식을 제공합니다.

<code class="language-sql">WITH numbered_item AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY item_id DESC) AS rownum
  FROM item
)
SELECT * FROM numbered_item WHERE rownum <= 4;</code>

성능상의 이점

이러한 최적화된 쿼리는 카테고리 수가 증가함에 따라 성능을 크게 향상시킵니다. 데이터베이스 로드를 최소화하고 리소스 사용을 최적화함으로써 애플리케이션은 광범위한 데이터 세트가 있는 경우에도 효율적인 데이터 검색을 보장합니다.

위 내용은 대규모 데이터베이스의 각 범주에서 최신 항목을 효율적으로 검색하기 위해 SQL 쿼리를 최적화하려면 어떻게 해야 합니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!