order by에 의하지 않는 인덱싱을 해 봅시다....
 
        [ 작성자 : hatelove | 등록일 : 2001년 06월 29일 | 조회 : 1141 ]
        [ 이메일 : love at zeezon.net ]
 
안녕하세요...
오늘은 인덱스 테이블을 만들어서 실제로 정렬을 해보겠습니다...
물론 where절 인덱스를 아시는 분들은 오늘도 굿바이 입니다....
 

먼저 다음과 같은 특징을 이해하고 넘어갑시다...
우선 단일 인덱스만 생각하세요...
 
MYSQL 3.22버전까지는 인덱스로 생성한 컬럼을 order by 를 이용한다구 해도 인덱싱하지 않습니다..
explain select * from test order by 인덱스생성컬럼 ==> type이 ALL이라구 나옵니다...
하지만 MYSQL 3.23버전부터는 order by를 이용하면 type ==> index라고 나오면서 인덱싱 됩니다..
 
오히려 range보다도 빠르죠.... index와 range에 무슨 차이가 있는지? 왜 index가 빠른지는 숙제입니다...
다음시간에 order by에 의한 인덱스를 공부하게 되면 이해가 되죠... 그때 짚고 넘어가겠습니다...
 
그럼 오늘 시간에는 order by에 의한 인덱싱이 되지 않는 DB를 사용할때 어떻게 인덱스를 옵티마이저에게...
사용하도록 명령하는가를 공부해 보겠습니다.....
 
일단 모든 DB의 공통점을 알고 넘어갑시다...

1. 인덱스 생성이 가능하다...
2. ASC로 정렬시 인덱스를 사용하게 만들 수 있다...
3. DECS보다는 ASC가 유리하다 (DESC 인덱스를 모두 지원하는 것은 아닙니다.)
4. 옵티마이저에게 where절 쿼리를 이용하여 인덱싱하도록 할 수 있다....
 
여기서 우리는 4번에 주의해야 합니다... order by에 의한것이 아니라 순수 쿼리에 의해서....
order by를 명시하지 않아도 원하는 인덱싱효과를 거두도록 할 수 있다는 겁니다...
이건 비단 MYSQL뿐만이 아니라 모든 대부분의 DB에서도 적용되기 때문에...
대단히 유용한 방법입니다... 필히 익혀 두시기 바랍니다....
일단 테이블을 생성합니다... 여기서는 3.22를 기준으로 하죠... 왜냐면..
3.22는 order by에 의한 인덱싱을 못하므로....
 
 
create table test3 (
id char(20) not null,
KEY (id)
);

아주 간단한 테이블이 생성되었습니다.....
우리는 데이터를 입력합니다....

insert into test3 values ('abc');
insert into test3 values ('ddd');
insert into test2 values ('fff');
insert into test3 values ('sfd');
insert into test3 values ('a');
insert into test3 values ('abg');
insert into test3 values ('dfasf');

######################### 참고 : 올림차순, 내림차순 ##############################
ascending : 오름차순 ==> 아시죠 1,2,3,4,5 또는 a,b,c,d 순 order by ASC
descending : 내림차순 ==> 역순정렬 5,4,3,2,1 또는 d,c,b,a 순 order by DESC
##################################################################################

그럼 테이블에는 (3.22에서는 test3.ISD 가 되겠죠)
abc
ddd
fff
sfd
a
abg
dfasf
처럼 순서가 뒤죽 박죽 들어가 있습니다...
select * from test order by id (3.22 버전에서는 order by에 의한 인덱싱이 안됩니다.. 오늘기준입니다..)
처럼 하면 인덱스 테이블을 쓰지를 않기 때문에 전체 스캔후 정렬하고 출력합니다...
물론 7개 밖에 안되기 때문에.. 문제가 안되지만 수만개나 된다면 문제가 되겠죠?

인덱스 테이블에는 (3.22에서는 test3.ISM) 당연히...
a
abc
abg
ddd
dfasf
fff
sfd
순으로 순서를 기억하겠죠... 물리적으로 하드에 순서대로 기억된다는 소리가 아닙니다...
 

자 옵티마이저에게 인덱스를 쓰라고 해 봅시다...
하지만 아쉽게도 방법이 없습니다...
3.22에서 where을 이용해서 인덱스하도록 명령해야 하는데 그럴만한 방법이 없습니다....
 
 
그럼 일단 제가 먼저 해보겠습니다... 아까의 쿼리에 앞에 임으로 동일문자 a를 추가하겠습니다...

일단 따라해 보시고 이유는 나중에 알아봅시다...
insert into test3 values ('aabc');
insert into test3 values ('addd');
insert into test3 values ('afff');
insert into test3 values ('asfd');
insert into test3 values ('aa');
insert into test3 values ('aabg');
insert into test3 values ('adfasf');
 
그리고 다음과 같이 쿼리합시다...
역시서 실제로 앞에 붙은 a는 임으로 붙인 문자이므로 실제 데이터가 아니므로
쿼리시 a는 빼고 가져옵시다...
여러가지 방법이 있는데 그건 각자 공부해 보시고...
다음처럼 해 보겠습니다...

select right(id,length(id)-1) from test3;
어때요? 우리가 임으로 넣은 a를 제외한 결과를 가져옵니다.....
 
그럼 id순으로 정렬해 봅시다...
select right(id,length(id)-1) from test3 order by id; ==> type이 ALL로 나옵니다.
select right(id,length(id)-1) from test3 where id like 'a%' limit 6; ==> type = range 며 인덱싱 됩니다....
 
자 이제 알아봅시다... where 절에 의한 인덱싱은 대부분의 데이터베이스에서...
공통으로 사용하는 인덱싱 방법이며... order by를 명시할 필요가 없습니다...
 
where절에 의한 인덱싱을 옵티마이저에게 알리는 방법은...
1. 동일 문자로 시작하는 컬럼에서 like '동일문자(하나이상의동일문자)%'
즉 동일문자로 시작하는 것이라는 것을 명시할 경우... 인덱싱합니다...

2. 특정값이 인덱싱키로 쓰일때... 인덱스 = '특정값' 일때... (결합인덱스시 많이 쓰입니다.)
그럼 위에 것에 a를 추하가는 것은 불러올때 내부연산(동일문자를 빼야...)이 있다...
그리고 like보다는 = 는 효율적이므로 다음과 같이 변경해도 좋습니다..

create table test4 (
k char(1) not null,
id char(20) not null,
KEY (k,id)
);

insert into test4 values ('1','abc');
insert into test4 values ('1','ddd');
insert into test4 values ('1','fff');
insert into test4 values ('1','sfd');
insert into test4 values ('1','a');
insert into test4 values ('1','abg');
insert into test4 values ('1','dfasf');
select right(id,length(id)-1) from test3 where k = "1" limit 6;

에구구... 위에 2번은 결합인데스구나 정도만 이해하시면 될듯합니다...
 
지금부터는 MYSQL에 국한되는 where절 인덱스시 주의 할 점입니다...
위에서 자료는 7개인데 왜 limit 6이라구 명시했나를 의아해 하신 분은...
센스가 있으신 분이네요...
 
쿼리를 해석해서 최적화되도록 실제 쿼리하는 옵티마이저에게...
where절(like나... = 에 의한)에 의한 쿼리를 했다구 무조건 인덱스 하는건 아닙니다...
솔직히 일종의 버그같은데요... 이것때문에... where절 인덱스 주의 하실 점이 있습니다....
 
MYSQL에서 where절에 의한 인덱싱을 할때 옵티마이저는 다음과 같이 해석합니다..

위에서 처럼 7개의 자류가 있을경우 자료가 있을경우....
where id like 'a%' limit 6 ===> 옵티마이저는 인덱스를 사용합니다.
where id like 'a%' limit 0,6 ===> 옵티마이저는 인덱스를 사용합니다.
where id like 'a%' limit 1,3 ===> 옵티마이저는 인덱스를 사용합니다.
where id like 'a%' limit 1,2 ===> 옵티마이저는 인덱스를 사용합니다.
where id like 'a%' limit 3 ===> 옵티마이저는 인덱스를 사용합니다.

where id like 'a%' limit 7===> 옵티마이저는 인덱스를 사용하지 않습니다.
where id like 'a%' limit 0,7===> 옵티마이저는 인덱스를 사용하지 않습니다.
where id like 'a%' ===> 옵티마이저는 인덱스를 사용하지 않습니다.
where id like 'a%' limit 7===> 옵티마이저는 인덱스를 사용하지 않습니다.
where id like 'a%' limit 1,7===> 옵티마이저는 인덱스를 사용하지 않습니다.
where id like 'a%' limit 6,2===> 옵티마이저는 인덱스를 사용하지 않습니다.

자 눈치를 채셨을 겁니다...
자료가 a개가 있을때... a 또는 a+알파가 포함되도록 쿼리해서는 안됩니다...
총 자료가 a개가 있을때... a-1개 까지만 쿼리해야 제대로 인덱싱이 됩니다...
where id like 'a%' 같은경우 당연히 모든 자료를 쿼리하므로 인덱싱 되지 않습니다...
where id like 'a%' limit x,y; 에서 x+y=총자료-1 까지만 인덱싱 된다는 것입니다...
이건 일종의 버그라고 생각됩니다...
참... limit x,y 에서 x번째부터 y개라는 거 아시죠...
 
자 다음에는 order by에 의한 인덱싱과 order by에 의한 인덱싱의 type이 index인 이유와...
where 절에 의한 인덱싱인 range이며 왜 index가 조금 더 빠른지... 살펴봅시다...
그리고 인덱스 컬럼을 선정하는 요령도 한번 알아봅시다...
그럼 담에 봐요....
열분~~~~~ 싸랑해요~~~~~ 끼악~~~~~
 

기타 의견 있으시면 알려주세요! 공유합시다!
   [ 이름 : oddy | 작성일 : 2001-06-30 ]     
 
 
      흠.... range scan 은 어느정도 범위가 적당할까요?
아무리 range scan 이라도 범위가 넓어지면 계속 인덱스 효과는 감소합니다.
(JBBS 역시 인덱스라기 보다는 분할예술에 가깝겠죠?)
3.23대 버전에서 explain select ....order by ** desc limit x,y 해도
인덱스 사용한다고 나오죠 하지만 큰 테이블의 경우 무의미 합니다. explain은 추천이지 정답은 아닙니다.
혹시 인덱싱을 처음 경험하시는 분들이 착각하실까봐 한마디 써 봤습니다. 
   
 
 
 

출처 : Tong - 드렁크수달스님의 ■ PHP ■통