MySQL 이용한 게시판 구성시 고질적인 문제 해결을 위한 방안
일반적인 경우 게시판은 최신글에 대한 리스트가 먼저 출력 됩니다.
tab_bbs 의 스키마가 (응답형 게시판 아님)
create tab_bbs (
no int auto_increment,
subject varchar(100),
.......
primary key (no)
);
라고 가정을 할때
첫글을 읽어 오는 쿼리는
select no, subject, ...... from tab_bbs order by no desc limit 10 형태로 질의를 할수 있을것 같습니다.
no 가 인덱스가 잡혀 있다고 하더라도.
역순 인덱스를 지원하지 않는 mysql 은 실제적으로 전체 범위처리를 해야 한다는 제약이 있을수 있습니다. (물론 인덱스를 가지고 역순으로 정렬 한다 하더라도..)
만일 100만개의 레코드가 존재하고 99십만번째 레코드를 읽어 오기 위해서는
select no,subject, .... from tab_bbs order by no desc limit 9900000,10
이런식으로 불러 와야 겠지요.
레코드가 작다면 모를까 , 레코드가 불어 난다면 갈수록 성능을 떨어 질수 밖에 없습니다.
그래서 다른 부분은 무시하고 일단 order by 를 사용하지 않고 인덱스만으로
역순으로 정렬 하는 방법에 대해서 고민해 봤습니다.
개념은 간단합니다.
인덱스는 정방향으로 (작은것에서 큰것)으로 생성 됩니다.
따라서 가장 최근에 등록된 레코드의 특정 필드 값이 가장 작다면
인덱스 순서로만 불러 온다고 하더라도 오라클에서와 같은 역순 인덱스형식으로
사용할수 있을것 같습니다.
만일 순차적으로 증가하는 no (auto_increment) 컬럼의 값에 -1을 곱할수 있다면
그 레코드의 no 값은 차례대로 작아 지는 형태가 됩니다.
no: 1, 2, 3, 4, 5, 6
minus_no: -1, -2, -3, -4, -5, -6
따라서 가장 최근에 insert 된 레코드가 가장 작은 -6을 가지게 되므로
minus_no 에 키를 생성하고
해당 쿼리가 그 키를 사용하게 해준다면.
order by 는 필요가 없어 지는거지요.
(레코드 출력은 해당 인덱스의 순서와 같아 지므로)
그런데 mysql에선 auto_increment는 있는데 auto_decrement(?)는 없습니다..ㅡㅡ;
그래서 한가지 방안으로
레코드 입력시
insert into tab_bbs (subject,minus_no) select "제목...",(MAX(NO)+1)*-1 from bbs_tab
이런식으로 해당 테이블에서 auto_increment 와 같은 방식으로 decrement를 해 나간다면
order by 를 없앨수 있지 않을까 합니다.
(물론 해당 퀴리는 minus_no 컬럼으로 만든 키를 참조하게 만들어야 겠지요)
문제는 insert into ..select 를 사용했을 경우
1. 동시에 같은 max(no) 값을 읽는 쿼리가 있을경우 중복 되는 minus_no 값이 발생할수 있고 minus_no 가 unique 제약을 가질경우 해당 레코드의 insert 가 실패 할수 있다는거지요.
2. 이를 해결하기 위해서 insert 시 해당 테이블에 락을 거는 방법이 있을수 있는데 insert/select 비율이 상당히 낮다면 문제가 되지 않을수도 있지만,
그렇다 하더라고 insert 시 select 에 많은 락이 발생한다면 그것도 별로 좋지 않은 방법 같습니다.
3. 해당 테이블에 락을 걸고
max(no)를 가지고 온후 insert, 그 뒤에 다시 락을 해제하는건
하나의 쿼리를 처리하기 위해서 디비와 두세번 커넥션을 맺어야 하기 때문에
그것도 별로 좋은 방법같지는 않군요.
어쨌든 제가 생각할때. auto_increment 를 음수화 시켜서 order by를 없애는
방안은 상당히 많은 성능 향상을 가지고 올수 있을것 같습니다.
다른 분들 생각은 어떠신지....리플 달아 주심 감샤...^^;
어째서 제로보드에서 headnum 값을 음수로 처리하나 했는데 그런 이유
어째서 제로보드에서 headnum 값을 음수로 처리하나 했는데 그런 이유가 있었군요...
... 보통 그렇게까지 크리티컬하게 동시작동을 어플리케이션도 아니고 DB레벨에서 고민할 필요는 없지 않을까요?
from bzImage
It's blue paper
[quote="dhunter"]... 보통 그렇게까지 크리티컬하게 동
어차피 디비에서 관리 되는것이고 디비내의 무결성이 문제 된다고 할때
그것을 프로그램 로직으로 풀어서는 안되는다는게 제 생각입니다.
데이타 베이스는 그 자체로 무결해야 한다....머 그런거랄까...ㅡㅡ;;;
또 한가지 큰 이유는 제가 개발자가 아니라 SE 라서리..
프로그램 로직보다는 데이타베이스 내부의 제약과 무결성에 더 무게를 두는
이유도 있겠지요...^^;
밤을 흐르며 몰래 스며드는 창가의 비처럼
어느새 젖어 버린 당신의 나처럼...
위의 방법만으로는 order by 를 아주 없앨 수는 없을겁니다. btr
위의 방법만으로는 order by 를 아주 없앨 수는 없을겁니다. btree 인덱스에 대한 이해가 필요한 부분입니다. btree인덱스가 생성되어 있고 쿼리플랜중에 이 인덱스를 사용해야만(where 또는 join..) 원하는 결과를 얻을 수 있습니다.
mysql에는 sequence가 없는데 sequence를 따로 지정할 수 있는 DBMS(예: pgsql)의 경우에는 시퀀스의 초기값을 최대값으로 주고 증가값을 -1 로 주어 역순 입력을 훨씬 간결화 할 수 있습니다. 단지 nextval() 만으로 원하는 동작을 할 수 있습니다.
인덱스를 역순으로 잡을 수 있는 DBMS도 있습니다...
MySQL 의 기본 인덱스 구조는 btree 로 알고 있습니다.그리고
MySQL 의 기본 인덱스 구조는 btree 로 알고 있습니다.
그리고 쿼리 플랜은 당연히 no 로 이루어 져야 겠지요..^^;
제가 응답형 게시판이 아니라고 했던 부분도
순전히 no 컬럼만을 고려하기 위함입니다.
검색이 아닌 일반 리스트 출력의 경우 게시물 번호로
소팅하지 않은 경우는 없을거라는 생각이 드는군요..
'그리고 역순 인덱스를 다른 디비에서 지원한다'라는것이 중요한게 아니라
MySQL에선 지원하지 않는다는 것이지욥...쩝~
oracle 에서도 지원을 하고 있고, sqlserver에서도 8.0 부터 역순 인덱스를
지원하더군요 . 그리고 시쿼스를 지원하는 것이 있다면 사용하는것도 좋겠지요.
제가 말씀 드린건 이미 MySQL을 이용하고 있기 때문에 Sequence 가 되는
다른 디비를 논하지 않는다는 가정 하에서 출발 한겁니다.
단지 MySQL 에서 편법으로 어떻게 역순 인덱스 같은 형태로 불러 올수 없겠냐 하는거에 대한 방안(?) 이었씀다.
밤을 흐르며 몰래 스며드는 창가의 비처럼
어느새 젖어 버린 당신의 나처럼...
생각해 보니까 제목을 너무 거창하게 단것 같기도 하네요.고질적 문제에
생각해 보니까 제목을 너무 거창하게 단것 같기도 하네요.
고질적 문제에 대한 방안...쩝...ㅡㅡ;;;;
밤을 흐르며 몰래 스며드는 창가의 비처럼
어느새 젖어 버린 당신의 나처럼...
MySQL 도 역순 인덱스 지원 하지 않나요?테이블 스키마 작성할
MySQL 도 역순 인덱스 지원 하지 않나요?
테이블 스키마 작성할때
예를 들면
create table pageinfo ( hash CHAR(32) NOT NULL, . . supercede TIMESTAMP(14) DEFAULT NULL, . . UNIQUE INDEX idx_hash(hash(12)), KEY idx_recently(supercede desc) <<-- ) type=MyISAM;CREATE INDEX 문에 ASC, DESC 구문을 붙이더라도 에러
CREATE INDEX 문에
ASC, DESC 구문을 붙이더라도 에러는 발생하지 않습니다.
미래를 위해서 예약된 것입니다.
MySQL 에서 아직 역순 인덱스는 지원하지 않습니다.
밤을 흐르며 몰래 스며드는 창가의 비처럼
어느새 젖어 버린 당신의 나처럼...
[quote="Advanced"]MySQL 도 역순 인덱스 지원 하지 않
문법적 오류만 안나고 지원은 안된다는.. ^^;;
목적을 찾아서... jiNoos
중요한것은 "인덱스를 사용하게 하는 쿼리" 인듯 합니다.explain
중요한것은 "인덱스를 사용하게 하는 쿼리" 인듯 합니다.
explain select * from table; 해보시면 아시겠지만 일반적으로 그냥 인덱스를 사용하지는 않습니다. 인덱스의 원론적인 의도에서 벗어난 쿼리라면 인덱스를 사용하지 않습니다. 쿼리 옵티마이저가 판단하기에 위와 같은 쿼리라면 인덱스를 사용하는 것보다 시퀀셜 스캔이 더 비용이 낮다고 판단하기 때문입니다.
적어도 select * from table where no > 0 처럼 의미없는 검색 연산자라도 넣어줘야만 인덱스를 사용하게 됩니다. 쿼리 옵티마이저라는 눔들도 "극진! 인텔리전트"하지는 않기 때문에 위와 같은 쿼리는 인덱스를 타게 됩니다.
제가 mysql이 깔려있지 않고 사용한지 오래되서 가물가물한데 이런식의 쿼리가 된다면 우려하시는 문제가 해결되지 않을까 싶습니다.
insert into tab_bbs (subject,minus_no) values('제목',no*-1);
물론 no 는 테이블에 auto_increment 가 걸린채로 존재해야 하구요.. 인덱스가 minus_no 에 싱글로 걸려있다면 select 할 때 minus_no < 0 이라고 주면 원하는 대로 정렬되지 않을까 합니다. 위와 같은 문법이 pgsql에서는 되는데 mysql에서는 해보지 않아서 모르겠습니다.
얼마전에 삽실했던 기억이 나서...MySQL 에서 아직 역순 인덱
얼마전에 삽실했던 기억이 나서...
MySQL 에서 아직 역순 인덱스는 지원하지 않아도..
id라는 필드에 인덱스를 잡아주면..
order by id asc 또는 order by id desc 쿼리는 날려보면
인덱스를 모두 탑니다..
문제는 결합인덱스에서는 이게 안통하더라구요...
id, depth라는 필드로 결합인덱스를 만들때..
create index id_depth on bbs (id asc, depth asc );
create index id_desc_depth on bbs (id desc, depth asc );
이두개가 만들때는 에러를 안내는데..
만들어진 인덱스는 동일한 인덱스라는 사실.
btree인덱스의 특성을 잘 살리면 order by 를 사용하지 않을 수
btree인덱스의 특성을 잘 살리면 order by 를 사용하지 않을 수 있습니다. 멀티컬럼 인덱스를 만들 경우
(id, depth) 처럼 만들면 id 필드에 검색이 일어나면 depth 는 자동 정렬됩니다.
order by 구문은 결과값에 대해 정렬을 한번 더 하게 됩니다. mysql은 btree밖에 없어서 어떤지 모르지만 인덱스 타입을 맘대로 바꿀 수 있는 DBMS의 경우에는 인덱스의 특성상 결과값이 정렬이 안되어 있을 수 있게 때문에 정렬을 하게 되는데 정렬에는 적잖은 비용이 들어갑니다. 막연하게 mysql도 예외는 아니라고 생각되네요? -.-;;
[quote="nonun"]btree인덱스의 특성을 잘 살리면 order
제가 알기론..
order by절로 정렬을 해도.. 정렬을 다시 한번 더 하는게 아니라..
order by절의 정렬 필드에 인덱스가 만들어져 있으면 옵티마이져가 알아서
인덱스로 정렬하는걸로 압니다. 플랜을 떠보면 그렇게 나옵니다.
이건 오라클이나 mysql이나 마찬가지인데요..
mysql은 오라클과 다르게..
create index id_desc_depth on bbs (id desc, depth asc );
이런식으로 id값을 desc으로 인덱스를 생성해도..
실제로는 id값이 asc로 만들어져 있다는거지요..
인덱스 생성시 에러는 내면 안되는구나 하는데..
에러를 안내더라구요..
그래서 생각해낸 방법이...
id값을 -1, -2 이런식으로 감소시키는... ^^;
인덱스의 종류가 btree 만이 아닙니다.. =_=hash, btre
인덱스의 종류가 btree 만이 아닙니다.. =_=
hash, btree, gist, rtree 등.. 조합형도 많구요
예를 들면 hash 인덱스가 정렬에 이용될 수는 없습니다. 벡터 인덱스로도 정렬을 할 수는 없겠지요. 엄연히 말하자면 btree는 순차적인 범위 검색용입니다. 하지만 mysql은 btree뿐이니.. -_-;
인덱스마다 최적화된 연산자와 용도가 있습니다. order by 했을때 btree인덱스가 걸려있다고 실제 쿼리 플랜에 order플랜이 안들어가지는 않을텐데요? 스킵이라면 모를까 order는 정렬을 하라는 명령이므로 결과가 정렬되어 있지 않다는 가정이 1%라도 있다면 플랜에 무조건 포함되고 동작되어야 합니다.
"정렬에 인덱스가 사용될 수 있다"와 "인덱스가 있으면 자동으로 정렬되서 플랜에 포함되지 않는다"는 다른 얘기라고 생각합니다.
btree인덱스의 특성을 이용해서 게시판 알고리즘의 성능을 향상 시키는 것은 사실 진부하지만 그만큼 좋은 방법입니다. mysql에서만 국한된다는 조건이라면 특별히 제가 더 할 말이 없을듯 합니다 ^^;
MySQL에서 안된다고 생각하시는 것 같아 한번 써봅니다.
:oops: KLDP에 올라온 글을 보고 테스트해 봄.
create table test
(
no int auto_increment,
subject varchar(100),
minus_no int,
primary key(no)
);
create index test_idx on test (minus_no); // alter table test add index test_idx (minus_no);
insert into test(subject,minus_no) values('a', -1 * (last_insert_id()+1));
insert into test(subject,minus_no) values('b', -1 * (last_insert_id()+1));
insert into test(subject,minus_no) values('c', -1 * (last_insert_id()+1));
insert into test(subject,minus_no) values('d', -1 * (last_insert_id()+1));
insert into test(subject,minus_no) values('e', -1 * (last_insert_id()+1));
insert into test(subject,minus_no) values('f', -1 * (last_insert_id()+1));
mysql> select no,subject,minus_no from test force index(test_idx) where minus_no < 0;
+----+---------+----------+
| no | subject | minus_no |
+----+---------+----------+
| 5 | e | -5 |
| 4 | d | -4 |
| 3 | c | -3 |
| 2 | b | -2 |
| 1 | a | -1 |
+----+---------+----------+
5 rows in set (0.00 sec)
mysql> explain select no,subject,minus_no from test force index(test_idx) where minus_no < 0;
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| 1 | SIMPLE | test | range | test_idx | test_idx | 5 | NULL | 4 | Using where |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> select * from test;
+----+---------+----------+
| no | subject | minus_no |
+----+---------+----------+
| 1 | a | -1 |
| 2 | b | -2 |
| 3 | c | -3 |
| 4 | d | -4 |
| 5 | e | -5 |
+----+---------+----------+
5 rows in set (0.00 sec)
where절로 minus_no를 지정하지 않았으므로 인덱스를 사용하라고 하더라도 안됨.
mysql> select * from test force index(test_idx);
+----+---------+----------+
| no | subject | minus_no |
+----+---------+----------+
| 1 | a | -1 |
| 2 | b | -2 |
| 3 | c | -3 |
| 4 | d | -4 |
| 5 | e | -5 |
+----+---------+----------+
5 rows in set (0.00 sec)
mysql> explain select * from test force index(test_idx);
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | test | ALL | NULL | NULL | NULL | NULL | 5 | |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)
mysql>
문제점: 아래와 같이 insert를 쓰면 a,b가 동일한 minus_no를 가진다.
insert into test(subject,minus_no) values('a', -1 * (last_insert_id()+1)),('b', -1 * (last_insert_id()+1));
보충하지요..^^
실제 테스트까지 해보셨다니 감사 합니다...^^;
MySQL에서 order by를 주더라도 인덱스를 탑니다.
물론 경우가 맞아야 겠지요.
위 게시판의 경우 최신글을 가져와야 합니다.
그래서 minus_no 값을 기준으로 order by를 항상 함께 실행해야 합니다.
아래와 같은 경우 Mysql은 인덱스를 사용합니다.
select * from tab1 order by key1 ;
select * from tab1 where key1 > 1 order by key1;
( mysql 메뉴얼: order by 절에서의 인덱스 사용)
..
..
따라서
select no,subject,minus_no order by minus_no limit 10;
이렇게 호출할경우 mysql 은 minus_no 에 있는 인덱스에서 상위 10개만을 리턴하고 바로 종료 합니다.
전체 게시물을 정렬하진 않지요.
(전체 범위가 아니라 부분범위라는거지요)
그럼 이만..^^
http://dev.mysql.com/doc/mysql/en/create
http://dev.mysql.com/doc/mysql/en/create-index.html
heap/memory 테이블에 한해서 hash 인덱스도 지원은 합니다 =3=33
오랫동안 꿈을 그리는 사람은 그 꿈을 닮아간다...
http://mytears.org ~(~_~)~
나 한줄기 바람처럼..
DROP TABLE
DROP TABLE `loginlog2`;
CREATE TABLE `loginlog2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`type` enum('VALID','INVALID') DEFAULT NULL,
`is_gm` enum('Y','N') DEFAULT 'N',
`login_time` datetime DEFAULT NULL,
`logout_time` datetime DEFAULT NULL,
`channel` tinyint(4) DEFAULT NULL,
`account_id` int(10) unsigned DEFAULT NULL,
`pid` int(10) unsigned DEFAULT NULL,
`playtime` time DEFAULT NULL,
`ip` int(12) unsigned NOT NULL DEFAULT '0',
`client_version` varchar(32) DEFAULT NULL,
PRIMARY KEY (`id` DESC),
KEY `aid_idx` (`account_id`),
KEY `pid_idx` (`pid`)
) ENGINE=InnoDB DEFAULT CHARSET=euckr;
# 로그인
INSERT INTO loginlog2(type, is_gm, login_time, channel, account_id, pid, ip, client_version)
VALUES('INVALID', 'N', NOW(), 1, 1234, 4321, 0, 'r299');
UPDATE loginlog2 SET id=-id WHERE id=LAST_INSERT_ID();
# 로그아웃
UPDATE loginlog2 SET type='VALID', logout_time=NOW(), playtime=TIMEDIFF(logout_time,login_time) WHERE pid=4321 LIMIT 1;
---
row가 엄청나게 많은 경우 ORDER BY DESC으로 인해 slow-query가 많이 발생하여 이 글을 보고 해결방법을 찾았습니다.
위 스킴과 쿼리들은 로그인 시 로그인 기록을 남기고 (2쿼리) 로그아웃 시 로그인 했을 때 남긴 row에 logout, playtime을 박는 상황 입니다.
보시다시피 ORDER BY없이 간단하게 구현됩니다. 처음에 minus_id를 만들어서 해봤는데 ORDER BY문이 없어질 수는 없길래 그냥 id에 과감하게 * -1을 했습니다. -_-;;;
스토리지 엔진이 InnoDB일 때만 작동하는 것으로 확인되었습니다.
Server version: MySQL 5.1.40-log FreeBSD port: mysql-server-5.1.40
질문하나만...
MySQL 보통 스토리지엔진 MyISAM 보다 InnoDB 쓰지 않나요?
BTree 얘기이면 MyISAM 이고, B+Tree 면 InnoDB 일텐데...
그리고, MySQL SQL parser 는 매우 루즈해서 믿지 않는게 정신건강에 좋습니다.
개인적으로는 질의, 스키마구조.. 등으로 성능개선도 도움이 되지만,
DB 셋팅, 디스크, 서버셋팅.. 등이 더 도움이 될것 같습니다.
실예로 RAID 컨트롤러만 바꿨는데 성능이 좋아졌다거나, I/O Hell 이 줄어들었다는 글들을 보네요.
댓글 달기