SQL 인덱스 설명, Pt. 1

적절하게 사용하면 SQL 데이터베이스 인덱스는 마술처럼 보일 정도로 효과적일 수 있습니다. 그러나 다음 일련의 연습은 대부분의 SQL 인덱스의 논리와 이를 올바르게 사용하는 것이 매우 간단하다는 것을 보여줍니다.

SQL Indexes Explained 시리즈에서는 데이터에 액세스하기 위해 인덱스를 사용하고 모든 최신 RDBMS에서 수행하는 방식으로 인덱스를 설계하는 동기를 살펴봅니다. 그런 다음 특정 쿼리 패턴에 대한 데이터를 반환하는 데 사용되는 알고리즘을 살펴보겠습니다.

SQL Indexes Explained 를 따르기 위해 인덱스에 대해 많이 알 필요는 없습니다. 두 가지 전제 조건이 있습니다.

• 기본 SQL 지식
• 모든 프로그래밍 언어에 대한 기본 지식

SQL Indexes Explained 에서 다룰 주요 주제는 다음과 같습니다.

• SQL 데이터베이스 인덱스가 필요한 이유 인덱스를 사용하여 실행 계획 시각화
• 인덱스 디자인: 쿼리를 빠르고 효율적으로 만드는 인덱스
• 인덱스를 효과적으로 사용하기 위해 쿼리를 작성하는 방법
• 읽기/쓰기 효율성에 대한 SQL 인덱스 사용의 영향
• 커버링 인덱스
• 파티셔닝, 읽기 및 쓰기에 미치는 영향 및 사용 시기

이것은 단순한 SQL 인덱스 튜토리얼이 아닙니다. 인덱스의 기본 메커니즘을 이해하는 데 도움이 됩니다.

우리는 연습을 하고 문제 해결 방법을 분석하여 RDBMS가 인덱스를 사용하는 방법을 알아낼 것입니다. 우리의 운동 자료는 읽기 전용 Google 스프레드시트로 구성되어 있습니다. 연습을 하려면 Google 시트를 복사( 파일 → 복사본 만들기 )하거나 콘텐츠를 자신의 Google 시트에 복사할 수 있습니다.

모든 연습에서 Oracle 구문을 사용하는 SQL 쿼리를 보여줍니다. 날짜의 경우 ISO 8601 형식인 YYYY-MM-DD 를 사용합니다.

연습 1: 클라이언트의 모든 예약

첫 번째 작업(아직 하지 마십시오)은 호텔 예약 시스템의 특정 클라이언트에 대한 예약 스프레드시트의 모든 행을 찾아 자신의 스프레드시트에 복사하여 다음 쿼리의 실행을 시뮬레이션하는 것입니다.

 SELECT * FROM Reservations WHERE ClientID = 12;

그러나 우리는 특정한 방법을 따르기를 원합니다.

접근 방식 1: 정렬 없음, 필터링 없음

첫 번째 시도에서는 정렬 또는 필터링 기능을 사용하지 마십시오. 소요시간을 기록해 주세요. 결과 시트에는 73개의 행이 포함되어야 합니다.

이 의사 코드는 정렬 없이 작업을 수행하는 알고리즘을 보여줍니다.

 For each row from Reservations If Reservations.ClientID = 12 then fetch Reservations.*

이 경우 841행을 모두 체크하여 조건을 만족하는 73행을 반환하고 복사해야 했다.

접근 방식 2: 정렬만

두 번째 시도에서는 ClientID 열의 값에 따라 시트를 정렬합니다. 필터를 사용하지 마십시오. 시간을 기록하고 데이터를 정렬하지 않고 작업을 완료하는 데 걸린 시간과 비교합니다.

정렬 후 접근 방식은 다음과 같습니다.

 For each row from Reservations If ClientID = 12 then fetch Reservations.* Else if ClientID > 12 exit

이번에는 "만" 780개의 행을 확인해야 했습니다. 어떻게든 첫 번째 행으로 점프할 수 있다면 시간이 훨씬 덜 걸릴 것입니다.

그러나 작업을 위한 프로그램을 개발해야 한다면 이 솔루션은 첫 번째 솔루션보다 훨씬 느릴 것입니다. 모든 데이터를 먼저 정렬해야 하기 때문입니다. 즉, 각 행에 적어도 한 번은 액세스해야 합니다. 이 접근 방식은 시트가 이미 원하는 순서로 정렬된 경우에만 좋습니다.

연습 2: 지정된 날짜에 시작되는 예약 수

이제 작업은 2020년 8월 16일의 체크인 수를 계산하는 것입니다.

 SELECT COUNT (*) FROM Reservations WHERE DateFrom = TO_DATE('2020-08-16', 'YYYY-MM-DD')

연습 1의 스프레드시트를 사용합니다. 정렬을 사용하거나 사용하지 않고 작업을 완료하는 데 소요된 시간을 측정하고 비교합니다. 정확한 수는 91입니다.

정렬이 없는 접근의 경우 알고리즘은 기본적으로 연습 1의 알고리즘과 동일합니다.

정렬 방식도 이전 연습과 유사합니다. 루프를 두 부분으로 나눕니다.

 -- Assumption: Table reservation is sorted by DateFrom -- Find the first reservation from the 16th of August 2020. Repeat Read next row Until DateFrom = '2020-08-16' -- Calculate the count While DateFrom = '2020-08-16' Increase the count Read the next row

연습 3: 범죄 수사

경찰 조사관은 2020년 8월 13일과 14일에 호텔에 도착한 손님 목록을 보여달라고 요청합니다.

 SELECT ClientID FROM Reservations WHERE DateFrom BETWEEN ( TO_DATE('2020-08-13', 'YYYY-MM-DD') AND TO_DATE('2020-08-14', 'YYYY-MM-DD') ) AND HotelID = 3;

접근 방식 1: 날짜별로만 정렬

검사관은 목록을 빨리 원합니다. 도착 날짜에 따라 테이블/스프레드시트를 정렬하는 것이 더 낫다는 것을 이미 알고 있습니다. 연습 2를 마치면 테이블이 이미 정렬되어 있어 운이 좋습니다. 따라서 연습 2에서와 유사한 접근 방식을 적용합니다.

시간, 읽어야 했던 행 수, 목록에 있는 항목 수를 기록해 보십시오.

 -- Assumption: Table reservation is sorted by DateFrom -- Find the first reservation from the 13th of August 2020. Repeat Read next row Until DateFrom >= '2020-08-13' -- Prepare the list While DateFrom < '2020-08-15' If HotelID = 3 then write down the ClientID Read the next row

이 접근 방식을 사용하여 46명의 게스트 목록을 컴파일하기 위해 511개의 행을 읽어야 했습니다. 정확히 아래로 미끄러질 수 있다면 8월 13일 첫 번째 도착을 찾기 위해 반복 주기에서 실제로 324번의 읽기를 수행할 필요가 없었습니다. 그러나 손님이 HotelID 가 3 인 호텔에 도착했는지 확인하기 위해 여전히 100개 이상의 행을 읽어야 했습니다.

검사관은 그 시간을 기다렸지만 만족하지 않았습니다. 손님의 이름과 기타 관련 데이터 대신 의미 없는 ID 목록만 전달했습니다.

우리는 시리즈의 뒷부분에서 그 측면으로 돌아올 것입니다. 먼저 목록을 더 빨리 준비하는 방법을 찾아보겠습니다.

접근 방식 2: 호텔, 날짜순 정렬

HotelID 다음 DateFrom 에 따라 행을 정렬하려면 모든 열을 선택한 다음 Google 스프레드시트 메뉴 옵션 데이터 → 정렬 범위 를 사용할 수 있습니다.

 -- Assumption: Sorted according to HotelID and DateFrom -- Find the first reservation for the HotelID = 3. Repeat Read next row Until HotelID >= 3 -- Find the first arrival at the hotel on 13th of August While HotelID = 3 and DateFrom < '2020-08-13' Read the next row -- Prepare the list While HotelID = 3 and DateFrom < '2020-08-15' Write down the ClientID Read the next row

우리는 우리 호텔에 첫 번째 도착을 찾기 전에 처음 338개의 도착을 건너뛰어야 했습니다. 그 후, 우리는 8월 13일 첫 번째 도착자를 찾기 위해 103명의 더 일찍 도착했습니다. 마지막으로 ClientID 의 46개 연속 값을 복사했습니다. 세 번째 단계에서 연속 ID 블록을 복사할 수 있다는 점이 도움이 되었습니다. 안타깝게도 그 블록에서 첫 번째 행으로 어떻게든 점프할 수 없었습니다.

접근 방식 3: 호텔만 기준으로 정렬

이제 HotelID 에서만 정렬된 스프레드시트를 사용하여 동일한 연습을 시도해 보십시오.

HotelID 로만 정렬된 테이블에 적용된 알고리즘은 HotelID 및 DateFrom 으로 정렬할 때보다 효율성이 떨어집니다(순서대로).

 -- Assumption: Sorted according to HotelID -- Find the first reservation for the HotelID = 3. Repeat Read next row Until HotelID >= 3 -- Prepare the list While HotelID = 3 If DateFrom between '2020-08-13' and '2020-08-14' Write down the ClientID Read the next row

이 경우 HotelID 가 3 인 호텔에 도착한 166개 모두를 읽고 각각에 대해 DateFrom 이 요청된 간격에 속하는지 확인해야 합니다.

접근 방식 4: 날짜, 호텔 순으로 정렬

먼저 HotelID 를 기준으로 정렬한 다음 DateFrom 을 기준으로 정렬하거나 그 반대로 정렬하는 것이 중요합니까? 알아보자. 먼저 DateFrom 을 기준으로 정렬한 다음 HotelID 를 기준으로 정렬해 보세요.

 -- Assumption: Sorted according to DateFrom and HotelID -- Find the first arrival on 13th of August While DateFrom < '2020-08-13' Read the next row --Find the first arrival at the Hotel While HotelID < 3 and DateFrom < '2020-08-15' Read the next row Repeat If HotelID = 3 Write down the ClientID Read the next row Until DateFrom > '2020-08-14' or (DateFrom = '2020-08-14' and HotelID > 3)

우리는 관련 날짜가 있는 첫 번째 행을 찾은 다음 호텔에 처음 도착할 때까지 더 많은 것을 읽습니다. 그 후 여러 행에 대해 올바른 날짜와 올바른 호텔이라는 두 가지 조건이 모두 충족되었습니다. 그러나 호텔 3에 도착한 후 같은 날짜에 호텔 4, 5 등에도 도착했습니다. 그 후, 우리는 관심 있는 호텔에 연속적으로 도착하는 것을 읽을 수 있을 때까지 호텔 1과 2에 대한 다음 날 행을 다시 읽어야 했습니다.

우리가 볼 수 있듯이 모든 접근 방식은 부분적으로 일치하는 데이터를 나타내는 전체 행 집합의 중간에 단일 연속 데이터 블록을 가지고 있습니다. 접근 방식 2와 4는 부분 일치의 끝에 도달하기 전에 논리를 통해 알고리즘을 완전히 중지할 수 있는 유일한 방법입니다.

접근법 4는 두 블록에 완전히 일치하는 데이터를 가지고 있지만 접근법 2는 대상 데이터가 모두 하나의 연속 블록에 있는 유일한 것입니다.

숫자로 볼 때 이 경우 접근 방식 2가 가장 큰 이점이 있음이 분명합니다.

SQL 인덱스 설명: 결론 및 다음 단계

이러한 연습을 수행하면 다음 사항이 명확해집니다.

1. 적절하게 정렬된 테이블에서 읽는 것이 더 빠릅니다.
2. 테이블이 아직 정렬되지 않은 경우 정렬은 정렬되지 않은 테이블에서 읽는 것보다 시간이 더 걸립니다.
3. 정렬된 테이블 내에서 검색 조건과 일치하는 첫 번째 행으로 점프하는 방법을 찾는 것은 많은 읽기를 절약할 것입니다.
4. 테이블을 미리 정렬하는 것이 도움이 될 것입니다.
5. 가장 빈번한 쿼리에 대해 테이블의 정렬된 복사본을 유지 관리하면 도움이 됩니다.

이제 테이블의 정렬된 복사본은 거의 데이터베이스 인덱스처럼 들립니다. SQL Indexes Explained 의 다음 기사에서는 기본적인 인덱스 구현을 다룹니다. 읽어 주셔서 감사합니다!