mysql order by 정렬 빼도 될까? 2

정렬인덱스를 정리하던 중에 흥미로은 블로그의 글을 보고 한번더 확인해보기로 했다.
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 정렬인덱스를 제대로 쓰지 못할 수 있고
인덱스 무효화 혹은 강제로 인덱스를 태우면 풀스캔보다 더 느려지는 현상이 발생한다는 것을 확인했다.
끝~