[MySQL튜닝]넌클러스터드에서 클러스터드로

 

이번에 설명할 주제는 '넌클러스터드에서 클러스터드로'란 주제입니다.

MySQL의 인덱스 동작 관련해서 가장 중요한 부분이라 생각됩니다.

 

넌클러스터드 인덱스와 클러스터드 인데스의 차이점을 다시 한 번 짚어보면 아래 그림과 같습니다.

 

넌클러스터드 인덱스와 클러스터드 인덱스 간의 가장 큰 차이점은, 리프 노드의 구성입니다.

• 넌클러스터드 인덱스: 리프 노드에는 인덱스 컬럼의 값과, 해다 테이블의 클러스터드 인덱스의 컬럼 값이 존재
• 클러스터드 인덱스: 리프 노드에는 실제 데이터가 존재

이 부분을 정확히 알고 있어야 합니다.

 

자 그러면, 아래 SQL을 살펴봅니다. WHERE 절에서는 ORD_DT에 같다(=) 조건이 사용되었고, SELECT 절에서는 ORD_DT 컬럼만 사용하고 있습니다.

SELECT  T1.ORD_DT
FROM    MYTUNDB.T_ORD_BIG T1
WHERE   T1.ORD_DT = STR_TO_DATE('20170104','%Y%m%d');

EXPLAIN                                                                                                      
------------------------------------------------------------------------------------------------------------ 
-> Index lookup on T1 using T_ORD_BIG_X1 (ORD_DT=str_to_date('20170104','%Y%m%d'))  (cost=92.52 rows=900) (actual time=0.087..0.554 rows=900 loops=1)

 

위 SQL의 실행계획을 보면, T_ORD_BIG_X1 인덱스를 사용해 처리가 된 것을 알 수 있습니다. actual time은 0.087, cost는 92.52라고 측정되어 있습니다. 위 과정을 그림으로 그려보면, 아래와 같습니다. 앞에서도 계속 설명했던 내용입니다.

아래 그림에서 1번 과정은 수직적 탐색, 2번 과정은 수평적 탐색입니다.

1번, 수직적 탐색은 WHERE 절 조건에 해당하는 값이 어디서부터 시작인지, 위치를 찾기 위한 과정입니다.

2번, 수평적 탐색은 WHERE 절 조건을 만족하지 않을때까지 리프 노드를 차례대로 검색하는 과정입니다.

 

이번에는 아래 SQL을 살펴보도록 합니다. 방금 살펴본 위의 SQL과 거의 같습니다. 다른 점은 SELECT 절에 ORD_DT 외에도 ORD_SEQ 컬럼이 있다는 점입니다. cost는 위의 SQL과  같고, actual time이 아주 약간 늘어난 것을 확인 할 수 있지만, 거의 차이가 없다고 보셔도 됩니다.

아래 SQL의 실행 계획 역시 위의 그림과 동일합니다. T_ORD_BIG_X1 인덱스만 사용해 원하는 결과를 처리합니다. 넌클러스터드 인덱스의 리프 노드에는 클러스터드 인덱스의 컬럼인 ORD_SEQ의 값도 존재하므로, ORD_SEQ 값을 가져오기 위한 추가적인 인덱스 접근이나 액션이 필요하지 않습니다.

SELECT  T1.ORD_DT ,T1.ORD_SEQ
FROM    MYTUNDB.T_ORD_BIG T1
WHERE   T1.ORD_DT = STR_TO_DATE('20170104','%Y%m%d');

EXPLAIN                                                                                                      
------------------------------------------------------------------------------------------------------------ 
-> Index lookup on T1 using T_ORD_BIG_X1 (ORD_DT=str_to_date('20170104','%Y%m%d'))  (cost=92.52 rows=900) (actual time=0.133..1.150 rows=900 loops=1)

 

 

이번에는 아래 SQL과 같이 SELECT 절에 T1.*를 사용해 모든 컬럼을 조회하도록 합니다. 실행계획을 살펴보면, 이전 SQL들과 마찬가지로 T_ORD_BIG_X1 인덱스를 사용하고 있습니다. 그런데 뒤쪽의 cost를 보면, 987.60으로 10배 가까이 증가한 것을 알 수 있습니다. actual time 역시 0.475로 증가했습니다.

SELECT  T1.*
FROM    MYTUNDB.T_ORD_BIG T1
WHERE   T1.ORD_DT = STR_TO_DATE('20170104','%Y%m%d');

EXPLAIN                                                                                                      
------------------------------------------------------------------------------------------------------------- 
-> Index lookup on T1 using T_ORD_BIG_X1 (ORD_DT=str_to_date('20170104','%Y%m%d'))  (cost=987.60 rows=900) (actual time=0.475..3.152 rows=900 loops=1)

위 SQL의 경우, 실행계획에는 T_ORD_BIG_X1만 사용한 것으로 표시되어 있습니다. 하지만 실제로는, T_ORD_BIG 테이블의 클러스터드 인덱스도 사용하고 있습니다.

위 SQL의 결과를 만들기 위해서는 ORD_DT가 2017년 1월 4일인 로우들의 모든 값이 필요합니다.

이 값들은 클러스터드 인덱스에 저장되어 있습니다. 그러므로 내부적으로 클러스터드 인덱스를 경유해야만 위 SQL의 결과를 처리할 수 있습니다. 안타깝게도, MySQL의 실행계획에서는 해당 작업을 별도로 표시해 주지는 않습니다.

 

위 SQL을 처리한 과정을 그려보면 아래와 같습니다.

1번, 2번은 넌 클러스터드 인덱스로 WHERE 조건에 해당하는 값의 클러스터드 인덱스의 키 값(ORD_SEQ)을 찾는 과정입니다.

3번은 2번에서 찾은 ORD_SEQ를 사용해, 클러스터드 인덱스를 검색하러 가는 과정입니다.

4번, 5번은 3번의 ORD_SEQ를 사용해 실제 데이터를 찾아오는 과정입니다.

논리적으로 3번, 4번, 5번 작업은 2번에서 찾은 건수만큼 반복 실행됩니다. 내부 매커니즘을 통해 반복 3, 4, 5번의 반복 횟수를 줄일 것이라 예상되지만, 논리적으로는 반복 실행된다고 생각하면 됩니다.

 

 

오늘 살펴볼 내용은 여기까지입니다.

자 그러면, 우리는 SELECT 절에 무턱대고 '*'를 사용해서는 안되겠죠. '*'를 처리하기 위해서는 실제 데이터의  접근이 발생할 수 밖에 없으니까요.

또한 테이블의 접근(클러스터드 인덱스 접근)을 피하기 위해 무턱대고 넌클러스터드 인덱스의 컬럼을 늘릴 수는 없겠죠. 이는 CUD 작업의 성능 저하로 이어지니까요. 그래서 인데스 설계가 어렵습니다.

그런데, MySQL은 좀 더 어렵습니다.  이와 같은 넌클러스터드와 클러스터드 구조를 반드시 머리에 넣고 성능을 고려한 인덱스를 설계를 해야 하니까요.