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
'MySQL' 카테고리의 다른 글
| [MySQL] Binary log 정리 (0) | 2016.10.18 |
|---|---|
| [MySQL] Log 정리 (0) | 2016.10.05 |
| [펌][MySQL] 대용량 테이블의 경우 성능 개선을 위한 10 가지 방안 (0) | 2016.08.03 |
| [MySQL] Stored Procedure 장단점 (2) | 2016.07.19 |
| [펌][MySQL] Warning: Using a password on the command line interface can be insecure. (0) | 2016.07.18 |
