MySQL version 5.0.51을 사용하고있습니다.
문제가 되는 테이블의 스키마는 다음과 같습니다.
CREATE TABLE "userdata" (
"gid" int(11) NOT NULL auto_increment, //row_id
"uid" bigint(20) NOT NULL,//userid
"sex" char(8) default NULL,
"wtime" timestamp NULL default CURRENT_TIMESTAMP,//가입시간
"accesstime" timestamp NULL default NULL,//최근 접속시간(datetype)
"img" char(255) default NULL,//user profile picture
"rate" int(255) default '0',// 총점수
"ratecount" int(255) default '0',//점수 받은 횟수
"interested" char(8) default NULL,//
"vote" bigint(20) default '0',투표한 횟수
"showtype" char(8) default NULL,
"online" tinyint(4) default '0',
"access" char(16) default NULL,//최근 접속시간(number type)
"country" smallint(6) default NULL,
"Headline" text,
PRIMARY KEY ("gid"),
KEY "uid" ("uid"),
KEY "wtime" ("wtime"),
KEY "accesstime" ("accesstime")
);
이 테이블을 사용하는 프로그램을 간단히 설명드리면
최대20명의 유저의 데이터를 보여주고 그 페이지에서 해당 유저마다 점수(1~10점)를 주는 프로그램입니다.
그리고 성별에 따른 점수가 높은 ranking(1위~50위)를 보여주기도 합니다.
해당 테이블의 row수는 대략 30만건이고 하루에 20000만명 정도가 사용합니다.
sex,interedted 컬럼은 male,female,none3가지 타입
showtype 컬럼은 everyone,groups,friends,country4가지 타입
country 컬럼은 0~244 까지
online컬럼은 0,1두가지로 이루어져 있습니다.
검색시에는
1. SELECT uid,interested,img,rate,ratecount,gid,accesstime,online,access,headline,type FROM SEXYPEOPLE WHERE uid =111
2-1. SELECT uid,interested,img,rate,ratecount,gid,accesstime,online,access,headline,type FROM SEXYPEOPLE WHERE country = 111 ORDER BY rand() LIMIT 20
2-2. SELECT uid,interested,img,rate,ratecount,gid,accesstime,online,access,headline,type FROM SEXYPEOPLE WHERE online = 1 AND country = 111 ORDER BY rand() LIMIT 20
3-1. SELECT uid,interested,img,rate,ratecount,gid,accesstime,online,access,headline,type FROM SEXYPEOPLE WHERE sex = 'male' AND country = 111 ORDER BY rand() LIMIT 20
3-2. SELECT uid,interested,img,rate,ratecount,gid,accesstime,online,access,headline,type FROM SEXYPEOPLE WHERE sex = 'male' AND online = 1 AND country = 111 ORDER BY rand() LIMIT 20
4-1. SELECT uid,interested,img,rate,ratecount,gid,accesstime,online,access,headline,type FROM SEXYPEOPLE WHERE uid IN (친구 아이디 리스트) ORDER BY rand() LIMIT 20
4-2. SELECT uid,interested,img,rate,ratecount,gid,accesstime,online,access,headline,type FROM SEXYPEOPLE WHERE sex = 'male' AND uid IN (친구 아이디 리스트) ORDER BY rand() LIMIT 20
5-1. SELECT uid,interested,img,rate,ratecount,gid,accesstime,online,access,headline,type FROM SEXYPEOPLE WHERE gid >= 1111 ORDER BY gid LIMIT 20
5-2. SELECT uid,interested,img,rate,ratecount,gid,accesstime,online,access,headline,type FROM SEXYPEOPLE WHERE online = 1 ORDER BY rand() LIMIT 20
6-1. SELECT uid,interested,img,rate,ratecount,gid,accesstime,online,access,headline,type FROM SEXYPEOPLE WHERE sex = 'female' AND gid >= 1234 ORDER BY gid LIMIT 20
6-2. SELECT uid,interested,img,rate,ratecount,gid,accesstime,online,access,headline,type FROM SEXYPEOPLE WHERE online = 1 AND sex = 'male' ORDER BY rand() LIMIT 20
7. SELECT UID, img, rate/ratecount rating, ratecount,type,sex FROM SEXYPEOPLE WHERE sex = 'male' AND ratecount > 100 ORDER BY rating DESC,ratecount DESC limit 50
8.SELECT uid,interested,invite,showtype,country from SEXYPEOPLE where uid=222222
검색페이지가 열릴때마다 8번 쿼리를 이용해서 사용자의 정보를 받아와서 그 데이터들의 값에따라 위의2~6번 쿼리중에서 한가지를 선택하여 검색하고 사용자의 accesstime,access,online컬럼값을 업데이트 합니다.
검색할때마다 다른 유저들의 리스트를 받아오기위해 반드시 랜덤정렬을 해야하는 상황입니다.
이 테이블에 일어나는 insert와 update횟수는 insert는 하루 약 1만건, update(rate,ratecount,vote컬럼에만 해당)는 하루 100만건이상이 발생합니다.
너무 많은 insert와 update가 한개의 테이블에서 발생하는 상황이라 서버에서 mysqld process가 cpu를 50%이상 사용하고 있습니다.
이 결과 빈번하게 테이블이 깨지거나 lock되는 상황이 발생합니다.
이 문제를 다음과 같이 테이블을 나누어서 해결해보고자 하였습니다.
CREATE TABLE USERLIST (
UID bigint(20) unsigned NOT NULL,
`wtime` timestamp NULL default CURRENT_TIMESTAMP,
`showtype` char(8) default 'Friends',
`interested` char(8) default NULL,
`country` smallint(6) unsigned default NULL,
`vote` bigint(20) unsigned default '0',
`invite` smallint(6) unsigned default NULL,
PRIMARY KEY (`UID`)
);
CREATE TABLE USERINFO (
gid INT(11) unsigned NOT NULL auto_increment,
UID bigint(20) unsigned NOT NULL,
`sex` char(8) default 'none',
`online` tinyint(4) unsigned default '0',
`country` smallint(6) unsigned default NULL,
`accesstime` timestamp NULL default NULL,
`type` tinyint(4) unsigned NOT NULL default '0',
`access` char(16) default NULL,
`img` char(255) default NULL,
`Headline` text,
PRIMARY KEY (`UID`),
KEY `userinfoGid` (`gid`,`sex`)
);
CREATE TABLE USERRATE (
UID bigint(20) unsigned NOT NULL,
`sex` char(8) default 'none',
`rate` bigint unsigned default '0',
`ratecount` int(255) unsigned default '0',
PRIMARY KEY (`UID`),
KEY `userrateSex` (`sex`)
);
update가 자주 일어나는 컬럼들과 insert시에 꼭 필요한 컬럼들 그리고 select시에 꼭필요한 컬럼들 별로 따로 테이블을 나누어 사용해 보고자 하였으나
결국 select를 할때 최소2개의 테이블끼리의 join이 발생하기때문에 테스트 결과 오히려 쿼리의 속도는 더 느려지는 문제가 발생하였습니다.
이 문제를 어떤방법(해시테이블 등)으로 해결해야할지 어떤식으로 최적화 하여야하는지 많은 분들에게 의견을 여쭙고자 합니다.
