[MySQL] Query 관련 수정 (Index)

Login 통계 관련하여 설계를 진행 했다.

그 동안 Login / out 정보를 테이블로 각기 다르게 추가를 하였다.

그러다 보니 Login/out 정보를 제대로 관리가 되지 않았다.

그래서 아래와 같이 설계 하였다.

create table LoginResult ( PlayerID varchar(50), Name varchar(30), WorldNum int(2) unsigned, CurrentChannel int(2) unsigned, LoginIP varchar(15) NOT NULL, PayPlayCheck enum('0','1') NOT NULL default '0', LoginDatetime datetime NOT NULL default '0000-00-00 00:00:00', LogoutDatetime datetime NOT NULL default '0000-00-00 00:00:00', Status enum('0','1','2') NOT NULL default '0' ); create index idx_Login_PlayerID on LoginResult (PlayerID,Name); create index idx_Login_Status on LoginResult (Status desc,Name);

간단하다.

- Login 할 때는 Insert / out 할때는 Update를 진행 한다.

Status

0	Logout 상태일 때
1	Login 상태 일 때
2	Login 후 정상적으로 out 하지 않았을 때

이렇게 구성한 후 일일 평균 / 특정 구간 동안의 평균을 낸 쿼리를 다음과 같이 구성하였다.

SELECT 

WorldNum, 

D.Name, 

CurrentChannel, 

SUM(CASE WHEN STATUS='1' THEN 1 END) cntNow, 

SUM(CASE WHEN DATE_FORMAT(LoginDatetime, '%Y-%m-%d') = DATE_FORMAT(NOW(), '%Y-%m-%d') THEN 1 END) cntCon, 

SEC_TO_TIME

(AVG

(CASE WHEN 

STATUS IN ('0','1') 

AND DATE_FORMAT(LoginDatetime, '%Y-%m-%d') = DATE_FORMAT(NOW(), '%Y-%m-%d') 

THEN IF (DATE_FORMAT(LogoutDatetime, '%Y-%m-%d') = '0000-00-00', SEC_TO_TIME(UNIX_TIMESTAMP(NOW())- UNIX_TIMESTAMP(LoginDatetime)), 

SEC_TO_TIME(UNIX_TIMESTAMP(LogoutDatetime)- UNIX_TIMESTAMP(LoginDatetime))) 

END)

) AS t

FROM USERINFO.LoginResult,DARKEDEN.WorldInfo D

WHERE 

DATE_FORMAT(LoginDatetime, '%Y-%m-%d') >= SUBDATE(NOW(), INTERVAL 1 MONTH) 

AND WorldNum = D.ID

GROUP BY WorldNum, CurrentChannel

ORDER BY WorldNum, CurrentChannel

위와 같이 구성하면 다음과 같은 실행 계획이 나온다.

Possible_keys 가 null 인 것을 보면 Index 를 사용하지 않는 것을 확인 할 수 있다.

여기서 빨간색으로 하이라이트 한 것을 확인 가능하다.

Index를 사용하지 못하는 경우가 몇가지가 있다.(가장 기본으로 알고 있어야 하는 것 중 하나로 난 생각한다)

그 중 한가지가 조건절을 함수로 변경하는 경우 Index를 사용 못하게 된다.

다음과 같이 변경해 보자

SELECT 

WorldNum, 

D.Name, 

CurrentChannel ,

sum(case when Status='1' then 1 end) cntNow ,

 sum(case when date_format(LoginDatetime, '%Y-%m-%d') = date_format(now(), '%Y-%m-%d') then 1 end) cntCon ,

sec_to_time( avg( case when Status in ('0','1') and date_format(LoginDatetime, '%Y-%m-%d') = date_format(now(), '%Y-%m-%d') then if ( date_format(LogoutDatetime, '%Y-%m-%d') = '0000-00-00' ,SEC_TO_TIME(unix_timestamp(now())-unix_timestamp(LoginDatetime)) ,SEC_TO_TIME(unix_timestamp(LogoutDatetime)-unix_timestamp(LoginDatetime)) ) end ) )as t 

FROM USERINFO.LoginResult ,DARKEDEN.WorldInfo D 

WHERE LoginDatetime >= SUBDATE(now(), INTERVAL 1 month) 

AND WorldNum = D.ID 

GROUP BY WorldNum, CurrentChannel 

ORDER BY WorldNum, CurrentChannel 

함수를 빼버렸다.

사실 나도 왜 저렇게 DATE_FORMAT 사용 했는지 모르겠다....습관도 아닐텐데...특정 날짜의 구간을 구하다 보니 반자동으로 DATE_FORMAT 를 사용했는 듯 싶다.

다음과 같이 확인해 볼 수 있다.

하지만 rows 수가 늘어난 것을 확인 가능하다......이건 아니잖아....ㅠ

나의 튜닝 목표 중 하나가 row 수를 줄이는 것이다.(랜덤 I/O 도 줄이는 것이다)

옵티마이저가 선택하는 것이니 이유가 있을 것이다.......OTL