#5. 두 번째 수업 - 2 Lab: Indexing for WHERE

드디어 두 번째 수업(사실상 첫 번째 수업이다. 저번 영상은 prerequisite이었어서..)을 시작한다. 이번 수업은 Indexing for WHERE으로 상황에 맞게 인덱스를 설계하는 법을 배우는 수업인 것 같다.

 

제일 먼저 할 일은 StackOverflow 데이터베이스의 인덱스를 모두 drop 하는 것이다.

 

■ 첫 번째 문제

아래 쿼리를 support하는 알맞은 인덱스 구성하기.

 

 

먼저 이 쿼리가 얼만큼의 logical reads를 하는지부터 확인해보자.

 

 

이 쿼리는 7,778 페이지의 logical reads를 한다. 이제 인덱스를 어떻게 세울지를 봐야하는데, 쿼리를 보니까 WebsiteUrl과 Location으로 인덱스 컬럼을 구성하고 DisplayName과 Id를 INCLUDE하면 될 것 같은 느낌이 든다. 문제는 WebsiteUrl과 Location 중 어떤 컬럼을 선행 컬럼으로 할 것인지이다.

 

 

이렇게 건수를 비교해보니 WebsiteUrl이 Location 보다 더 selective해 보인다. 그러면 WebsiteUrl을 선행컬럼으로 두면 되나? 사실 어떤 순서로 인덱스를 만들든 logical reads 관점에서는 차이가 없다. 확인해보자.

 

 

• INCLUDE(DisplayName) : Id는 PK니까 INCLUDE에 굳이 포함시키지 않았다.
• WITH(MAXDOP = 0) : MAXDOP 은 하나 이상의 프로세서 또는 CPU가 있는 컴퓨터에서 각 쿼리에 대한 단일 문을 실행하는데 병렬 실행 계획에 사용할 수 있는 프로세서 수 이다. (출처: https://sungwookkang.com/387) MAXDOP을 0으로 설정하면 모든 프로세서를 다 사용하겠다는 의미이다. 쿼리 수준에서의 MAXDOP 힌트는 서버 자체의 max degree of parallelism을 우선한다. 그렇기 때문에 인덱스를 생성하거나 하는 쿼리(지금 당장 빨리 처리되어야 하는?)에 이 힌트를 사용하면 유용할 때가 많다.

그러면 만든 인덱스들을 사용했을 때의 성능을 확인해보자.

 

 

둘 다 logical reads가 7,778에서 3으로 줄어든 것을 확인할 수 있다. 실행계획도 확인해보자.

 

 

두 인덱스 모두 동일한 성능과 실행계획을 가짐을 확인할 수 있다.

■ 두 번째 문제

아래 쿼리를 support하는 알맞은 인덱스 구성하기.

 

 

먼저 AS_IS의 logical reads 확인

 

 

각 조건의 건수 비교해보기 (각 컬럼의 selectivity 확인)

 

 

위 결과를 보았을 때 DownVotes보다 UpVotes 컬럼이 더 selective 해 보인다. 양쪽 컬럼 모두 선행되는 인덱스를 각각 만들어서 비교해보자.

 

 

각 인덱스의 실행계획을 비교해보자.

 

 

둘 다 index seek를 수행한다. (어디서부터 시작해야할 지 알고 있다는 뜻) 실행계획을 조금 더 자세히 들여다보자.

 

1) 첫 번째 쿼리

 

실행계획을 보면 Number of Rows Read와 Actual Number of Rows가 동일하다. → fantastic

두 개의 값이 동일하다는 것은 딱 필요한 만큼 읽었다는 뜻이기에 불필요한 read가 없었다는 것을 의미한다.

 

2) 두 번째 쿼리

Number of Rows Read가 Actual Number of Rows보다 크다. 필요한 양보다 더 많이 읽었다는 것을 뜻한다. 

 

왜 더 selective 한 컬럼을 선행 컬럼으로 둔 인덱스를 사용했을 때 더 많이 읽었을까?

 

각 인덱스의 내부가 어떻게 생겼는지 한 번 확인해보자.

 

1) 첫 번째 인덱스

 

DownVotes_UpVotes 인덱스가 DownVotes = 0 AND UpVotes > 100 인 조건을 수행할 때 어떤 순서로 데이터를 찾는지 볼 것이다. 먼저 DownVotes = 0 조건에 맞는 데이터를 조회하면 아래와 같이 데이터가 나올 것이다.

 

 

DownVotes = 0 인 데이터를 가져오는데 UpVotes는 이미 오름차순으로 정렬이 된 상태이다.

 

 

이렇게 DownVotes = 0 인 데이터를 찾아놓고, 그 다음에 UpVotes > 100 인 데이터를 찾고자 하면,

 

 

이미 UpVotes도 정렬이 되어 있으므로 바로 찾을 수 있다. UpVotes가 101인 지점을 알고 이 지점부터 시작하면 된다는 것을 알고 있다. 어디서부터 어디까지 읽어야할지 정확히 알고 있으므로 읽은 row와 결과 row 수가 동일할 수 있다. → 적절한 인덱스

실제로 인덱스 힌트를 주지 않았을 때 옵티마이저가 첫 번째 인덱스를 사용한다는 것을 확인할 수 있다.

 

 

2) 두 번째 인덱스

두 번째 인덱스를 사용하여 데이터를 검색하는 과정도 visualization 해보자.

 

 

결과 데이터의 첫 부분을 보면 우리가 원하는 UpVotes > 100 이상인 데이터가 나온다. 그 뒤의 DownVotes는 오름차순으로 정렬되어 있다. 그렇지만 조금 더 내려보면 UpVotes는 조건에 맞는데 DownVotes가 조건에 맞지 않는(0이 아닌) 데이터들이 등장한다.

 

 

UpVotes, DownVotes 순으로 정렬이 되어 있기 때문에 UpVotes 부터 순서대로 읽다보면 DownVotes가 0이 아닌 데이터를 중간에 맞닥뜨리게 된다.

 

그러면 여기서 이렇게 생각할 수 있다. UpVotes가 100보다 큰 데이터들 중에서 DownVotes가 0인 애들 쭉 읽다가 DownVotes가 1인 애들을 만나게 되면 나머지를 안 읽고 다음 UpVotes(UpVotes=102)로 넘어가면 되지 않을까?

→ SQL Server는 이런 거 못한다.

 

 

SQL Server는 여기서부터 읽을래, 라고 정하면 거기서부터 쭉 읽어야 한다.

 

 

뭐 어쩔 수 없다. 돈 많이 벌어서 오라클(세계 최고 비싼 DB) 쓰면 된다. (오라클은 skip scan이라는 것이 있다.)

 

 

아무튼 본론으로 돌아와서, SQL Server는 그렇게 필요한 것만 건너뛰기 못한다. 따라서 이런 경우 inequality 조건을 사용하는 컬럼을 인덱스의 선행 컬럼으로 두는 것은 좋지 못하다.

두 인덱스를 각각 사용했을 때의 logical reads를 확인할 수 있다.

■ 세 번째 문제

 

AS-IS의 logical reads 파악하기.

 

 

WHERE절 조건 : Location LIKE 검색, Reputation inequality 조건

 

각 조건에 해당하는 건수 파악하기.

 

 

Reputation이 더 selective해 보인다. → 나의 검색 영역을 좁혀주는 데 있어서 Reputation이 좀 더 도움이 될 것 같다.

그러면 인덱스 선행 컬럼에 Reputation을 넣긴 할건데, Location도 인덱스 컬럼에 들어가야 할까? INCLUDE로만 포함시키는 게 낫지 않겠나?

 

확인해보자.

 

 

이제 각 인덱스를 사용했을 때의 실행계획을 비교해보자.

 

 

위 두 실행계획을 보면 number of rows read와 actual number of rows가 동일하게 차이가 난다. 

 

 

 

두 값이 차이가 나는 건 어쩔 수 없다. Location LIKE '%Germany%' 이 조건을 찾으려면 어느 한 지점으로 seek할 수 없다. 다 까 봐야 한다.

 

결론은, 이런 경우 Location이 인덱스 키로 들어가든 INCLUDE로 들어가든 성능 상 차이는 없다는 것이다.

 

그런데 이 두 인덱스 간의 차이는 있다. sp_BlitzIndex를 실행해보자.

 

 

받아놓은 줄 알았는데 안 받았나 보다. 

 

 

이 sql 키트는 brent ozar 사이트에서 신청하면 무료로 받을 수 있다. (메일로 쏴준다.) 얘네들을 설치한 후 다시 이어서 써야지.