정렬인덱스를 정리하던 중에 흥미로은 블로그의 글을 보고 한번더 확인해보기로 했다.
http://www.gurubee.net/lecture/2260
--> 내용을 요약하자면 복합인덱스를 선언하면 order by을 쓰지 않더라도 ASC정렬이 가능하다는 것이다.
일단 복합인덱스 (A,B,C)의 정렬 인덱스를 사용할 수 있는 경우의 수를 알아본다.
--> 기본적으로 인덱스는 equal 조건만 사용가능하고 like 검색시 value% 은 가능하지만 %value은 불가하다.
1) 인덱스 사용가능한 경우
#1-1) 정렬만 사용한경우
order by a,b
order by a,b,c
--> Using index condition
#1-2) where + 정렬 사용한 경우
where a = 1
order by b,c
--> Using index condition
where a = 1
and b = 2
order by c
--> Using index condition
# Using index condition란?
WHERE 절의 인덱스를 이용한 조건에서 체크 조건이 있을 경우 체크 조건 처리를 스토리지 엔진이 하도록 전달하는 것이다.
2) 인덱스 사용 불가능한 경우
#2-1) 정렬만 사용한경우
#a를 뺀 경우
order by b,c
-->복합인덱스는 첫번째 컬럼이 선언되지 않으면 사용x
#순서대로 아닐때
order by a,c --> Using where; Using temporary; Using filesort 정렬인덱스 사용x
order by a,b,c,d --> Using where; Using temporary; Using filesort 정렬인덱스 사용x
# desc 정렬사용할 때
order by a,b,c desc --> Using where; Using temporary; Using filesort 정렬인덱스 사용x
#2-2) where + 정렬 사용한 경우
where a = 1
order by a,c
--> a가 포함되어있으면 Using index condition; Using filesort
where절에 a만 인덱스 쓰는 거고 order by는 인덱스를 사용하지 못하고 재정렬하기 위해 filesort를 쓴다.
a가 없으면 Using where; Using temporary; Using filesort 정렬인덱스 사용x
#테스트
select
T1.SEQ_NO
,T2.APLY_DV_CD
,T1.STD_NO
,T1.APLY_DATE
,T1.REG_DATE
from TESTDB.TB_TSET_20200626 T1
inner join TESTDB.TB_TSET_20200626_TEL T2
ON T1.SEQ_NO =T2.SEQ_NO
where T1.STTS_CD ='93'
order by T1.STTS_CD,T1.APLY_DATE,T1.REG_DATE
;
#인덱스 생성전
| id |
select_type |
table |
type |
possible_keys |
key |
key_len |
ref |
rows |
filtered |
Extra |
| 1 |
SIMPLE |
T2 |
ALL |
SEQ_NO |
|
|
|
110865 |
100 |
Using where; Using temporary; Using filesort |
| 1 |
SIMPLE |
T1 |
eq_ref |
PRIMARY |
PRIMARY |
92 |
TESTDB.T2.SEQ_NO |
1 |
10 |
Using where |
#정렬을 위해 인덱스를 추가한다.
alter table TESTDB.TB_TSET_20200626
add index IDX_TSET_20200626_12 (STTS_CD,APLY_DATE,REG_DATE);
#인덱스 생성후
| id |
select_type |
table |
type |
possible_keys |
key |
key_len |
ref |
rows |
filtered |
Extra |
| 1 |
SIMPLE |
T1 |
ref |
PRIMARY,IDX_TEST_20200626_12 |
IDX_TEST_20200626_12 |
8 |
const |
7668 |
100 |
Using index condition |
| 1 |
SIMPLE |
T2 |
ref |
SEQ_NO |
SEQ_NO |
93 |
TESTDB.T1.SEQ_NO |
1 |
100 |
|
#정렬조건 변경
order by T1.STTS_CD,T1.APLY_DATE,T1.REG_DATE -->order by T1.APLY_DATE,T1.REG_DATE
where 절에 STTS_CD쓰고 order by 절에서 APLY_DATE, REG_DATE 만 선언해도 정렬이 가능한지 플랜과 실제데이터를 확인한다.
| id |
select_type |
table |
type |
possible_keys |
key |
key_len |
ref |
rows |
filtered |
Extra |
| 1 |
SIMPLE |
T1 |
ref |
PRIMARY,IDX_TEST_20200626_12 |
IDX_TEST_20200626_12 |
8 |
const |
7668 |
100 |
Using index condition |
| 1 |
SIMPLE |
T2 |
ref |
SEQ_NO |
SEQ_NO |
93 |
TESTDB.T1.SEQ_NO |
1 |
100 |
|
플랜 같고, 데이터 정렬도 같다.
#이제 블로그에서 얘기한대로 order by 자체를 빼고 STTS_CD만 where절에 걸었을 때도 정렬이 되는지 확인해본다.
select
T1.SEQ_NO
,T2.APLY_DV_CD
,T1.STD_NO
,T1.APLY_DATE
,T1.REG_DATE
from TESTDB.TB_TSET_20200626 T1
inner join TESTDB.TB_TSET_20200626_TEL T2
ON T1.SEQ_NO =T2.SEQ_NO
where T1.STTS_CD ='93'
#order by T1.APLY_DATE,T1.REG_DATE
;
| id |
select_type |
table |
type |
possible_keys |
key |
key_len |
ref |
rows |
filtered |
Extra |
| 1 |
SIMPLE |
T1 |
ref |
PRIMARY,IDX_TEST_20200626_12 |
IDX_TEST_20200626_12 |
8 |
const |
7668 |
100 |
|
| 1 |
SIMPLE |
T2 |
ref |
SEQ_NO |
SEQ_NO |
93 |
TESTDB.T1.SEQ_NO |
1 |
100 |
|
일단 using index condition 없다. 하지만 IDX_TEST_20200626_12 인덱스를 썼다고는 한다. 데이터를 확인해본다.
흥미롭다. 실제로 order by를 쓰지 않았지만 데이터 정렬은 order by를 사용한 것과 동일하다.
이것이 가능한 이유는 IDX_TEST_20200626_12 인덱스를 생성할 때 STTS_CD,APLY_DATE,REG_DATE 순서대로 ASC 정렬을 해서 인덱스를 가지고 있고
STTS_CD만 사용하더라도 정렬된 인덱스의 값을 가져오기 때문에 나머지 컬럼은 선언하지 않더라도 정렬된 데이터를 확인할 수 있었다.
order by 정렬을 뺄 수 있다니..
정렬 인덱스는 뺄 수 있는 것을 확인했다. 이번엔 좀 다른 것을 테스트 해본다.
select
T1.SEQ_NO
,T2.APLY_DV_CD
,T1.STD_NO
,T1.APLY_DATE
,T1.REG_DATE
from TESTDB.TB_TSET_20200626 T1
inner join TESTDB.TB_TSET_20200626_TEL T2
ON T1.SEQ_NO =T2.SEQ_NO
where T1.STTS_CD ='4'
order by T1.APLY_DATE,T1.REG_DATE
;
# 정렬조건을 바꿔 T1.STTS_CD ='4'로 테스트한다.
| id |
select_type |
table |
type |
possible_keys |
key |
key_len |
ref |
rows |
filtered |
Extra |
| 1 |
SIMPLE |
T2 |
ALL |
SEQ_NO |
|
|
|
110865 |
100 |
Using where; Using temporary; Using filesort |
| 1 |
SIMPLE |
T1 |
eq_ref |
PRIMARY,IDX_TEST_20200626_12 |
PRIMARY |
92 |
TESTDB.T2.SEQ_NO |
1 |
50 |
Using where |
인덱스 있는데.. 안탄다. temporary에도 모자라서 filesort까지 쓴다. 수행시간도 2.9초나 걸린다.
옵티마이져가 멍청해졌나? 강제로 인덱스를 태워본다.
select
T1.SEQ_NO
,T2.APLY_DV_CD
,T1.STD_NO
,T1.APLY_DATE
,T1.REG_DATE
from TESTDB.TB_TSET_20200626 T1 force index (IDX_TEST_20200626_12)
inner join TESTDB.TB_TSET_20200626_TEL T2
ON T1.SEQ_NO =T2.SEQ_NO
where T1.STTS_CD ='4'
order by T1.APLY_DATE,T1.REG_DATE
;
| id |
select_type |
table |
type |
possible_keys |
key |
key_len |
ref |
rows |
filtered |
Extra |
| 1 |
SIMPLE |
T1 |
ref |
IDX_TEST_20200626_12 |
IDX_TEST_20200626_12 |
8 |
const |
213603 |
100 |
Using index condition |
| 1 |
SIMPLE |
T2 |
ref |
SEQ_NO |
SEQ_NO |
93 |
TESTDB.T1.SEQ_NO |
1 |
100 |
|
인덱스는 타지만 row수를 보니 더 최악이다... 수행시간도 5초나 걸리고 옵티마이져는 나보다 똑똑했다.;;
#왜이런 결과가 나올까?
select
count(*)
from TESTDB.TB_TSET_20200626
where T1.STTS_CD ='4';
// 전체 row 439,947 건중에 418,328 가 STTS_CD = 4 이다.
// 분포도 95%
// 이러면 옵티마이져가 풀스캔이 유리하다고 판단하고 인덱스를 당연히 안쓴다.
#앞선 조건은 왜 인덱스스캔이 가능했을까?
select
count(*)
from TESTDB.TB_TSET_20200626
where T1.STTS_CD ='93';
//확인해보니 전체 row수 439,947건중에 STTS_CD = 93 은 4262건이다.
//분포도 0.97%
인덱스를 설정할 때 데이터 분포도의 중요성이 여기서 다시 한번 확인된다.
복합인덱스를 선언하더라도 첫번째 컬럼의 분포도가 낮다면 order by 정렬인덱스를 제대로 쓰지 못할 수 있고
인덱스 무효화 혹은 강제로 인덱스를 태우면 풀스캔보다 더 느려지는 현상이 발생한다는 것을 확인했다.
끝~
