Query Tuning 예제

 안녕하세요 mysql에서 해당 sql 실행 결과가 너무 늦게 나와서 문의를 드립니다. 

sql문과 explain을 보시고 결과가 늦게 나오는 이유에 대해 조언 부탁드립니다. 

감사합니다. 

sql문 : 

select c.yyyy      

           , c.quota seq

          , substring(FN_GET_COMPANY_AVG_2(c.YYYY,c.quota,c.company),1,2) total_users_avg

from quota_date c use index (idx_yyyy, idx_company, idx_quota)

     inner join UHC_MEASURE_PHYSICAL a on a.yyyy = c.yyyy

     inner join UHC_USER_INFO b on a.USER_ID=b.USER_ID

where c.company = 'T'

GROUP BY c.YYYY,c.quota

 

Explain : 

 

id	select type	table	type	possible_keys	key	key_len	ref	rows	extra
1	SIMPLE	c	ref	idx_yyyy, idx_company	idx_company	6	const	25	Using where; Using temporary; Using filesort
1	SIMPLE	a	index	PRIMARY	PRIMARY	78		1992	Using where; Using index
1	SIMPLE	b	eq_ref	PRIMARY	PRIMARY	62	uhealthcare_demo.a.USER_ID	1	Using index

 

---

답안

데이터 량과 관계가 정확하지 않아 튜닝이 될지 모르겠지만

plan상으로 봤을때는 c를 먼저 group처리하고

나머지 테이블은 체크 조건으로 처리 하면 될 것 같습니다.

 

select t.*
from (
 select c.yyyy      
      , c.quota seq
      , substring(FN_GET_COMPANY_AVG_2(c.YYYY,c.quota,c.company),1,2) total_users_avg
 from quota_date c 
 where c.company = 'T'
 GROUP BY c.YYYY,c.quota
) t
WHERE EISTS (SELECT 1 
             FROM UHC_MEASURE_PHYSICAL a
      inner join UHC_USER_INFO b on a.USER_ID=b.USER_ID

      WHERE a.yyyy = t.yyyy)

---

예제

안녕하세요. index관련해서 테스트를 하던중에 궁금한점이 있어서 질문드립니다.

현재 사용하는 Mysql 버전은 5.1.41-3 을 사용하고 있고요.\

 

상황 : index가 걸려있는 컬럼을 select 할 경우 index를 타는데, index가 걸려있는 않은 컬럼도 select 를 할 경우 index를 타지 않습니다.(테이블은 MyISAM 입니다.)

 

ex) time컬럼이 index인 경우,

 

 select time from TB_TEST where time between 1329814800 and 1329832800;

=> ( index 사용 )

 

 select time, count from TB_TEST where time between 1329814800 and 1329832800;

=> ( index 사용안됨 )

 

 mysql> explain select time from TB_TEST where time between 1329829200 and 1329832800;
+----+-------------+-------------------------------+-------+---------------+------------+---------+------+-------+--------------------------+
| id | select_type | table                         | type  | possible_keys | key        | key_len | ref  | rows  | Extra                    |
+----+-------------+-------------------------------+-------+---------------+------------+---------+------+-------+--------------------------+
|  1 | SIMPLE      | TB_TEST | range | index_time    | index_time | 4       | NULL | 47782 | Using where; Using index |
+----+-------------+-------------------------------+-------+---------------+------------+---------+------+-------+--------------------------+
1 row in set (0.00 sec)

 

mysql> explain select time, idx from TB_TEST where time between 1329829200 and 1329832800;
+----+-------------+-------------------------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table                         | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------------------------------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | TB_TEST | ALL  | index_time    | NULL | NULL    | NULL | 103442 | Using where |
+----+-------------+-------------------------------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)
 

왜 select에서 구해오는 값에 index가 걸려있는 컬럼만 있을경우 index를 타는데, index가 걸려있지 않은 컬럼까지 포함하면 index를 타지 않는 걸까요.... between 때문인건가요?ㅠㅠ;

---

답안

이 글에 대한 댓글이 총 4건 있습니다.

참고로 between말고 부등호로 'time <= 시간 and time >= 시간' 으로 사용해도 마찬가지로 나오네요...

지종현(whdgus15)님이 2012-02-22 21:28:49에 작성한 댓글입니다.

 1줄 요점: 찾아봐야 되는 데이터의 범위가 너무 넓어서요.

 

time만 골라오는 경우에는 index만 뒤져서 처리가 가능합니다. 이를 일컬어 covered query라고 부르고 이때 사용되는 index를 covering index라고 부릅니다.

그런데 count까지도 가져오기 위해서는 index에 count 정보가 없기 때문에 index말고 table까지도 접근해서 data를 퍼 올려야 하는 상황에 처하게 됩니다.

전체 십만여건의 data를 대상으로 주어진 범위에 해당하는 데이타 건수를 4만7천여개가 해당할 것으로 통계를 근거로 예측을 하였습니다.

그렇다면 47천번 "index보고 table 쫒아가고"를 반복해서 수행하느니(index와 table을 보는 횟수를 모두 더하면 94천여번)

걍 인덱스를 포기하고 테이블을 다 퍼올려서 where조건에 해당하는 놈들만 filtering하지 머.. 그래봐야 십만건 차례대로 쳐다보면 되는데 94천번 왔다갔다 하는 것보단 효율이 좋겠군....이라고 옵티마이저가 판단했을 겁니다.

 

between으로 조회하는 범위를 전체 데이터의 반절에 해당하는 현재의 수준에서 확 줄인 범위를 where 조건으로 주면 '에이.. 그렇다면 index와 table을 왔다갔다 하면서 쳐다봐도 전체를 다 쳐다보는 것보다는 낫겠구먼'이라고 판단하고 index를 사용하게 될겁니다.

 

 

참고로 between 이나 부등호나 사람이 눈으로 보이는 것의 차이일 뿐 실제 처리는 100% 동일합니다.

우욱님이 2012-02-22 22:28:19에 작성한 댓글입니다. Edit Ｘ

---