Mysql Index 확인

* 디비조회시 쿼리결과가 엄청 오래걸리는 경우  쿼리 플랜을 이용하여 쿼리 튜닝을 해야 한다.

1. 기존쿼리 분석.

* 주문정보 log 가  280만 row 디비 데이터 가 존재시 해당 데이터를 결과 조회시 4초가량 걸림.

select product_name, shop_name, sum(product_cnt)as productCnt, sum(product_price) as productPrice , shop_id
from tb_order_detail_stack
where timestamp BETWEEN timestamp(curdate()) AND timestamp(curdate()+1)
  and  pay_flag = 0
  and daily_code not in (select daily_code from tb_order_detail_stack where pay_flag = 1 and timestamp BETWEEN timestamp(curdate()) AND timestamp(curdate()+1))
group by product_name, shop_name, shop_id
order by sum(product_price) DESC;

[ query plan 결과 - index scan 을 하지 못함 ]

* DB Row가 많아 짐에 따라 쿼리 조회시 많은 시간이 소요될수 있기에 Index 를 설정하여 index scan이 동작할 수 있도록 한다.

 

2. index 설정.

* Index 고려

- Index 설정시  설정할 컬럼은 카디널리티(Cardinality)가 가장 높은 것을 잡아야 한다.

( 많은 그룹으로 나뉠수 있는 경우가 높은것.)

==> 남,여  데이터 처럼 두개로 나뉘는것은 의미가 없다.  주소정보나 시간값같은 분포도가 넓은 데이터가 유리하다.

( 현실에서의 아주 두꺼운 책에서 맨뒤의 인덱스 찾기 페이지에서  책속의 어느페이지에 내가 원하는 정보가 있는지 찾는것을 고려 하면 적당할 것이다. )

- 컬럼이 여러개 일경우 카디널리티가 높은순에서 낮은순으로 구성하는게 더 성능이 뛰어납니다. 

==> no , 날짜, pay_flag  처럼 분포도가 넓은 순에서 낮은 순으로 ~~.

 

* index 지정할 컬럼을 선정한 이후에 설정한다.

 

생성 쿼리 )   create index 인덱스명 on 테이블명 (컬럼); 

- create index tb_order_detail_stack_timestamp_index on tb_order_detail_stack (timestamp); 

[ query plan 결과 - index scan 을 실행함 ]

 

인덱스 설정 이후 쿼리 결과가 43ms 걸림. 

 

3. index 추가 사항

-  동일한 데이터를 가진 colum을 indexing 하여 플랜을 실행해 보면  Full index scan 이라고 나오며 느린 결과를 보인다.  ( 카디널리티가 높은 컬럼을 index 설정 해야함. )

- 컬럼 선정하여 indexing 을 했음에도  쿼리에  따라 index scan을 하지 않을수 있다. 

  예) DATE_ADD(NOW(), INTERVAL 1 MINUTE);