SQL 인덱스 설명, Pt. 2

SQL Indexes Explained 의 첫 번째 수업에서 정렬을 사용하여 데이터 검색 속도를 높이는 방법을 배웠습니다. 행을 정렬한 후 쿼리 실행이 더 빠르지만 정렬에는 모든 행을 적어도 한 번 읽고 이동하는 작업이 포함됩니다. 따라서 단순히 전체 테이블을 순차적으로 읽는 것보다 메서드가 더 느리고 덜 효율적입니다.

논리적인 결론은 주어진 테이블의 정렬된 복사본(공식적으로 IX_ 접두사가 붙은 SQL 인덱스 라고 부를 것)을 유지해야 한다는 것입니다. 그러면 첫 번째 기사의 검색 알고리즘을 적용할 수 있으며 시작하기 전에 테이블을 정렬할 필요가 없습니다.

테이블의 정렬된 복사본으로서의 인덱스

다시 Google 스프레드시트를 사용하여 그 아이디어의 문자 그대로 구현을 살펴보겠습니다. 예약 스프레드시트는 동일한 데이터가 포함된 5개의 시트 모음이 됩니다. 각 시트는 다른 열 집합에 따라 정렬됩니다.

여기에 있는 연습은 이전 SQL 인덱스 자습서 기사보다 덜 정확하기 위한 것입니다. 타이머와 행 수보다 느낌으로 더 많이 수행할 수 있습니다. 일부 연습은 매우 유사해 보이지만 이번에는 다음을 탐색합니다.

1. 정렬된 기본 테이블이 아닌 별도의 인덱스를 사용할 때 데이터를 보다 효율적으로 검색하는 방법
2. 데이터 수정 시 각 인덱스와 테이블의 순서를 유지 하는 방법

이전 자습서는 읽기에 중점을 두었지만 호텔 예약을 포함하여 많은 일반적인 실제 데이터 역학에서 새 데이터를 삽입하고 기존 데이터를 업데이트할 때 쓰기 성능에 대한 인덱싱의 영향을 고려해야 합니다.

예비 연습: 예약 취소

정렬된 테이블 전략을 사용하여 SQL 인덱스 성능에 대한 느낌을 얻으려면 2020년 8월 22일부터 호텔 4에서 클라이언트 12에 대한 예약을 삭제해야 합니다. 올바른 정렬을 유지하십시오.

완료? 테이블의 여러 정렬된 복사본을 유지하는 아이디어는 생각만큼 좋지 않다는 것이 분명해야 합니다. 그래도 의심이 가는 경우 방금 삭제한 예약을 다시 삽입하거나 기존 예약의 날짜를 변경할 수도 있습니다.

테이블의 정렬된 복사본을 사용하면 더 빠르게 검색할 수 있지만, 방금 배운 것처럼 데이터 수정은 악몽입니다. 기존 행을 추가, 삭제 또는 업데이트해야 할 때마다 테이블의 모든 복사본을 검색하고 행 및/또는 추가 또는 이동해야 하는 위치를 찾은 다음 마지막으로 데이터 블록을 이동해야 합니다.

행 주소를 사용하는 SQL 인덱스

이 스프레드시트에는 다른 접근 방식을 사용하는 색인이 포함되어 있습니다. 인덱스 행은 여전히 ​​특정 기준에 따라 정렬되지만 인덱스 행에 다른 모든 정보를 보관하지는 않습니다. 대신 테이블 자체를 나타내는 예약 시트의 행 주소인 "행 주소"만 H열에 유지합니다.

모든 RDBMS 구현은 운영 체제 수준 기능을 사용하여 물리적 주소를 사용하여 디스크에서 블록을 빠르게 찾습니다. 행 주소는 일반적으로 블록 주소와 블록 내의 행 위치로 구성됩니다.

이 인덱스 디자인이 어떻게 작동하는지 배우기 위해 몇 가지 연습을 해 봅시다.

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

첫 번째 기사에서와 같이 다음 SQL 쿼리의 실행을 시뮬레이션할 것입니다.

 SELECT * FROM Reservations WHERE ClientID = 12;

다시 말하지만, 두 가지 합리적인 접근 방식이 있습니다. 첫 번째는 단순히 Reservations 테이블에서 모든 행을 읽고 기준과 일치하는 행만 가져오는 것입니다.

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

두 번째 접근 방식은 IX_ClientID 시트에서 데이터를 읽고 기준과 일치하는 항목에 대해 rowAddress 값을 기반으로 Reservation 테이블에서 행을 찾는 것과 관련됩니다.

 Get first row from IX_ClientID where ClientID = 12 While IX_ClientID.ClientID = 12 Fetch Reservations.* where rowAddress = IX_ClientID.rowAddress Write down Reservations.* Get next row from IX_ClientID

여기에서 Get first row from 표현식은 이전 기사에서 본 것과 유사한 루프로 구현됩니다.

 Repeat Fetch next row from IX_ClientID Until ClientID >= 12

행을 찾을 때까지 아래로 밀거나 rowAddress 열에 대한 필터를 사용하여 주어진 rowAddress가 있는 행을 찾을 수 있습니다.

반환할 예약이 소수만 있는 경우 인덱스를 사용하는 접근 방식이 더 좋습니다. 그러나 반환될 행이 수백 개(때로는 수십 개)에 불과하므로 Reservations 테이블을 직접 사용하는 것이 더 빠를 수 있습니다.

읽기 볼륨은 ClientID 값에 따라 다릅니다. 가장 큰 값의 경우 전체 인덱스를 읽어야 하고 가장 낮은 값의 경우 인덱스의 시작 부분에 있습니다. 평균값은 행 수의 절반입니다.

나중에 그 부분으로 돌아가 효율적인 솔루션을 제시하겠습니다. 지금은 기준과 일치하는 첫 번째 행을 찾은 후 해당 부분에 집중하겠습니다.

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

작업은 새로운 인덱스 디자인을 사용하여 2020년 8월 16일의 체크인 수를 계산하는 것입니다.

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

계산 에 적절한 인덱스를 사용하는 접근 방식은 관련된 행 수에 관계없이 테이블 스캔보다 우수합니다. 그 이유는 Reservations 테이블에 전혀 액세스할 필요가 없기 때문입니다. 인덱스 자체에 필요한 모든 정보가 있습니다.

 Count := 0 Get first row from IX_DateFrom where DateFrom >= '2020-08-16' While found and DateFrom < '2020-08-17' Count := Count + 1 Get next row from IX_DateFrom Write down Count

알고리즘은 기본적으로 정렬된 테이블을 사용하는 알고리즘과 동일합니다. 그러나 인덱스 행은 테이블 행보다 훨씬 짧기 때문에 RDBMS는 디스크에서 더 적은 수의 데이터 블록을 읽어야 합니다.

연습 3: 범죄 수사(호텔 및 날짜 범위가 주어진 게스트 목록)

2020년 8월 13일과 14일 호텔3에 도착한 손님 명단을 준비해보자.

 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;

Reservations 테이블에서 모든 행을 읽거나 사용 가능한 인덱스 중 하나를 사용할 수 있습니다. 특정 기준에 따라 정렬된 테이블로 동일한 연습을 수행한 후 IX_HotelID_DateFrom 인덱스가 가장 효율적이라는 것을 알았습니다.

 Get first row from IX_HotelID_DateFrom where HotelID = 3 and DateFrom between '2020-08-13' and '2020-08-14' While found and DateFrom < '2020-08-15' and IX_HotelID_DateFrom.HotelID = 3 Fetch Reservations.* where rowAddress = IX_HotelID_DateFrom.rowAddress Write down Reservations.ClientID Get next row from IX_HotelID_DateFrom

훨씬 더 효율적인 인덱스를 설계할 수 있습니까?

보고하는 게스트 목록에 필요한 유일한 정보인 ClientID 값 때문에 테이블에 액세스합니다. SQL 인덱스에 해당 값을 포함하면 테이블에 전혀 액세스할 필요가 없습니다. IX_HotelID_DateFrom_ClientID 와 같은 인덱스에서만 읽을 수 있는 목록을 준비하십시오.

 Get first row from IX_HotelID_DateFrom_ClientID where HotelID = 3 and DateFrom between '2020-08-13' and '2020-08-14' While found and HotelID = 3 and DateFrom < '2020-08-15' Write down ClientID Get next row from IX_HotelID_DateFrom_ClientID

인덱스에 쿼리 실행에 필요한 모든 정보가 포함되어 있으면 인덱스가 쿼리를 포함 한다고 합니다.

연습 4: ID 대신 게스트 이름 목록

게스트 ID 목록은 범죄를 수사하는 경찰관에게 쓸모가 없습니다. 이름을 제공해야 합니다.

 SELECT c.ClientName FROM Reservations r JOIN Clients c ON r.ClientID = c.ClientID WHERE r.DateFrom BETWEEN ( TO_DATE('2020-08-13', 'YYYY-MM-DD') AND TO_DATE('2020-08-14', 'YYYY-MM-DD') ) AND r.HotelID = 3;

목록을 제공하려면 Reservations 테이블의 데이터 외에 게스트 정보가 포함된 Clients 테이블도 필요합니다. 이 테이블은 이 Google 시트에서 찾을 수 있습니다.

이 연습은 이전 연습과 유사하며 접근 방식도 마찬가지입니다.

 Get first row from IX_HotelID_DateFrom_ClientID where HotelID = 3 and DateFrom between '2020-08-13' and '2020-08-14' While found and HotelID = 3 and DateFrom < '2020-08-15' Fetch Clients.* where ClientID = IX_HotelID_DateFrom_ClientID.ClientID Write down Clients.ClientName Get next row from IX_HotelID_DateFrom_ClientID

Fetch Clients.* where ClientID = IX_HotelID_DateFrom_ClientID.ClientID 는 테이블 스캔 또는 인덱스를 사용하여 구현할 수 있습니다. 테이블 스캔을 사용하는 경우 While 루프의 각 ClientID 에 대해 평균적으로 Clients 테이블에서 행의 절반을 읽어야 합니다.

 -- Get row from Clients using table scan Repeat Fetch next row from Clients Until ClientID = IX_HotelID_DateFrom_ClientID.ClientID or not found If found Write down ClientName

우리가 지금까지 고려한 인덱스 구현("평평한" 인덱스 구현이라고 함)은 그다지 도움이 되지 않을 것입니다. 인덱스에서 같은 수의 행(더 작은 행이지만)을 읽은 다음 RowAddress 를 사용하여 Clients 의 행으로 이동해야 합니다.

 -- Get row from Clients using flat index Repeat Fetch next row from Clients_PK_Flat Until ClientID >= IX_HotelID_DateFrom_ClientID.ClientID If found Fetch Clients.* where rowAddress = Clients_PK_Flat.rowAddress Write down ClientName

참고: 여기에서 PK 는 이 시리즈의 뒷부분에서 살펴볼 용어인 "기본 키"를 나타냅니다.

너무 많은 행을 읽지 않고도 이것을 수행할 수 있는 방법이 있습니까? 예, 이것이 바로 B-트리 인덱스가 필요한 것입니다.

균형 트리(B-트리) 인덱스

Clients_PK_Flat 의 행을 4행 블록으로 나누고 블록의 마지막 ClientID 값과 블록 시작 주소(열 IndexRowAddress )를 포함하는 목록을 생성해 보겠습니다. 결과 데이터베이스 인덱스 데이터 구조 - Clients_PK_2Levels 시트에서 찾을 수 있습니다. 새 구조가 ClientID 가 28인 클라이언트를 찾는 데 어떻게 도움이 되는지 시도해 보십시오. 알고리즘은 다음과 같아야 합니다.

 Fetch Level2.* Loop Leaf_address := Level3Address Exit when ClientID >= 28 Fetch next row from Level2 Fetch Level3.* where Level3Address = Leaf_address -- 3-21 Loop Client_address := RowAddress Exit when ClientID >= 28 Fetch next row from Level 3 Fetch Clients.* where rowAddress = Client_address -- 42 Write down Clients.*

당신은 아마 우리가 다른 레벨을 추가할 수 있다는 것을 알아차렸을 것입니다. 레벨 1은 IX_Clients_PK 탭에서 볼 수 있듯이 4개의 행으로 구성됩니다. ClientID가 28인 게스트의 이름을 찾으려면 기본 키 구조에서 레벨당 하나씩 3개의 데이터 블록(노드)을 읽고 마지막으로 주소가 42인 Clients 행으로 이동해야 합니다.

이 SQL 인덱스의 구조를 균형 트리라고 합니다. 루트 노드에서 각 리프 수준 노드까지의 경로가 동일한 길이(소위 B-트리 깊이)를 가질 때 트리가 균형을 이룹니다. 우리의 경우 깊이는 3입니다.

이제부터는 스프레드시트에 리프 수준 항목만 포함되어 있어도 각 인덱스가 B-트리 구조를 갖는 것으로 간주합니다. B-트리에 대해 알아야 할 가장 중요한 사실은 다음과 같습니다.

• B-트리 인덱스 구조는 시장의 모든 주요 RDBMS에서 가장 일반적으로 사용되는 인덱스입니다.
• 균형 트리의 모든 수준은 키 열 값으로 정렬됩니다.
• 데이터는 디스크에서 블록 단위로 읽습니다.
• 하나의 B-트리 노드는 하나 이상의 블록을 포함합니다.
• 쿼리 성능에 영향을 미치는 가장 중요한 요소는 디스크에서 읽은 블록의 수입니다.
• 루트에서 시작하여 리프 수준에서 끝나는 각 새 B-트리 수준의 항목 수는 기하급수적으로 증가합니다.

연습 5: 범죄 수사, 파트 II

이제 경찰 조사관은 A 도시의 모든 호텔에서 해당 손님의 이름, 도착 날짜 및 호텔 이름 목록을 찾고 있습니다.

 SELECT h.HotelName, r.DateFrom as CheckInDate, c.ClientName FROM Reservations r JOIN Clients c ON r.ClientID = c.ClientID JOIN Hotels h ON r.HotelID = h.HotelID WHERE r.DateFrom BETWEEN ( TO_DATE('2020-08-13', 'YYYY-MM-DD') AND TO_DATE('2020-08-14', 'YYYY-MM-DD') ) AND h.City = 'A';

접근법 1

IX_DateFrom_HotelID_ClientID 인덱스를 사용하는 경우 날짜 범위의 각 행에 대해 Hotels 테이블에 액세스하고 호텔이 A 도시인지 확인해야 합니다. 그렇다면 Client 테이블에도 액세스하여 다음을 수행해야 합니다. 클라이언트의 이름을 읽습니다.

 For each row from IX_DateFrom_HotelID_ClientID where DateFrom between '2020-08-13' and '2020-08-14' For each row from Hotels where HotelID = IX_DateFrom_HotelID_ClientID.HotelID If Hotels.City = 'A' then Fetch Clients.* where ClientID = IX_HotelID_DateFrom_ClientID.ClientID Write down Hotels.HotelName, IX_HotelID_DateFrom_ClientID.DateFrom, Clients.ClientName

접근법 2

IX_HotelID_DateFrom_ClientID 를 사용하면 보다 효율적인 실행 계획을 얻을 수 있습니다.

 For each row from Hotels where City = 'A' For each row from IX_HotelID_DateFrom_ClientID where HotelID = Hotels.HotelID and DateFrom between '2020-08-13' and '2020-08-14' Fetch Clients.* where ClientID = IX_HotelID_DateFrom_ClientID.ClientID Write down Hotels.HotelName, IX_HotelID_DateFrom_ClientID.DateFrom, Clients.ClientName

Hotels 테이블에서 A 도시의 모든 호텔을 찾습니다. 이러한 호텔의 ID를 알고 IX_HotelID_DateFrom_ClientID 인덱스에서 후속 항목을 읽을 수 있습니다. 이렇게 하면 각 호텔 및 날짜에 대한 B-트리 리프 수준에서 첫 번째 행을 찾은 후 도시 A 외부의 호텔 예약을 읽지 않습니다.

여기에서 목표에 적합한 데이터베이스 인덱스가 있을 때 추가 조인이 실제로 쿼리를 더 빠르게 만들 수 있음을 알 수 있습니다.

B-tree 구조와 행이 삽입, 업데이트 또는 삭제될 때마다 어떻게 업데이트되는지는 파티셔닝의 동기와 그 영향을 설명할 때 더 자세히 다룰 것입니다. 요점은 인덱스를 사용할 때마다 이 작업을 빠르게 고려할 수 있다는 것입니다.

SQL의 인덱스 쿼리: 세부 사항이 모든 차이를 만듭니다

인덱스 및 데이터베이스와 관련하여 SQL 언어 수준에서 작업하면 구현 세부 정보가 어느 정도 숨겨집니다. 이 연습은 다른 SQL 인덱스를 사용할 때 실행 계획이 어떻게 작동하는지 이해하는 데 도움이 됩니다. 기사를 읽은 후 가능한 한 빠르고 효율적으로 쿼리를 만들 수 있도록 사용 가능한 인덱스와 디자인 인덱스가 주어지면 최상의 실행 계획을 추측할 수 있기를 바랍니다.

이 시리즈의 다음 부분에서는 새로 습득한 기술을 사용하고 확장하여 SQL에서 인덱스를 사용할 때 가장 일반적인 모범 사례와 안티 패턴을 조사하고 이해합니다 . 다음 부분에서 다루고 싶은 우수 사례 및 모범 사례 목록이 있지만 다음 기사를 귀하의 필요와 경험과 더 관련성 있게 만들기 위해 답변을 보고 싶은 질문을 자유롭게 게시하십시오 .

SQL 인덱스 설명 의 마지막 부분에서는 테이블 및 인덱스 파티셔닝, 사용에 대한 올바른 동기와 잘못된 동기, 쿼리 성능 및 데이터베이스 유지 관리에 미치는 영향에 대해서도 배웁니다.