[텍스트큐브] MySQL select_full_join 값을 줄이기

  MySQL에서 select_full_join 값은 테이블의 열 목록을 담은 찾아보기(인덱스)를 참조하지 못하고 테이블을 샅샅이 뒤져 이어붙이기(full-join)한 잦기를 나타낸다. 이는 데이터베이스 서버의 처리 성능을 많이 떨어뜨릴 수 있는 작업 유형이므로, 이어붙이기(join)가 들어가는 SQL 결과문을 만들 때에 찾아보기를 참조하거나 조건절을 붙여서 테이블의 테이블을 필요한 데만 좁혀 찾게 하면 좋다. 그래서 찾아보기를 더 넣거나 SQL 물음글(쿼리)를 잘 고쳐서라도 select_full_join 값을 0 또는 0에 가까운 값에 머무르게 하는 것을 권고하곤 한다.

  텍스트큐브 최근 판(1.10.10)을 돌리며 살피니, 블로그 글과 관리 화면의 글 목록을 열 때마다 select_full_join 값이 꾸준히 늘어나고 있었다. 요즈음에 쓰이는 워드프레스, XE, phpBB 같은 웹 도구들은 이 문제가 나타나지 않는다. 그래서 찾아보기를 쓰지 못하는 이어붙이기를 막고자 아래처럼 텍스트큐브의 MySQL 테이블들에 찾아보기를 더 넣고 이어붙이기가 낀 SQL 명령문을 고쳤다.

  아래의 찾아보기를 더 넣는 SQL 예문에서는 데이터베이스 이름을 db_name이라고 가정했고, 새로 넣는 찾아보기는 나중에 넣었음을 알아보기 좋게 하려고 이름 앞에 idx_를 붙였다.

1. select_full_join 줄이기

(1) tc_Comments

• tc_Comments 테이블에 id 열 목록을 담는 찾아보기(idx_id)를 새로 넣는다.

ALTER TABLE `db_name`.`tc_Comments` ADD INDEX `idx_id` (`id`);

(2) tc_TagRelations

• tc_TagRelations 테이블에 tag 열 목록과 entry 열 목록을 담는 찾아보기(idx_tag, idx_entry)를 새로 넣는다.

ALTER TABLE `db_name`.`tc_TagRelations` ADD INDEX `idx_tag` (`tag`);
ALTER TABLE `db_name`.`tc_TagRelations` ADD INDEX `idx_entry` (`entry`);

(3) tc_Users

• tc_Users 테이블에 userid 열 목록을 담는 찾아보기(idx_userid)를 넣는다.

ALTER TABLE `db_name`.`tc_Users` ADD INDEX `idx_userid` (`userid`);

• interface/owner/entry/index.php 134째 줄의 내용(위)을 아래와 같이 바꾼다.

$teamblog_users = POD::queryAll("SELECT u.*, p.acl FROM {$database['prefix']}Users AS u INNER JOIN {$database['prefix']}Privileges AS p ON u.userid = p.userid AND p.blogid = $blogid ORDER BY p.acl DESC, name ASC");

$teamblog_users = POD::queryAll("SELECT u.userid, u.name, p.acl FROM {$database['prefix']}Users AS u INNER JOIN {$database['prefix']}Privileges AS p ON u.userid = p.userid AND p.blogid = $blogid ORDER BY p.acl DESC, name ASC");

2. 찾아보기 더 넣기

  다음 찾아보기들은 select_full_join을 줄이는 구실은 하지 못하지만, 테이블을 뒤지는 영역을 줄이는 구실을 할 수 있다.

(1) tc_Privileges

• tc_Privileges 테이블에 userid 열 목록을 담는 찾아보기를 넣는다.^주1

ALTER TABLE `db_name`.`tc_Privileges` ADD INDEX `idx_userid` (`userid`);

(2) tc_Attachments

• tc_Attachments 테이블에 blogid와 parent 열 목록을 담는 찾아보기를 넣는다.

ALTER TABLE `db_name`.`tc_Attachments` ADD INDEX `idx_blogid_parent` (`blogid`, `parent`);

3. 효과

  위와 같이 고친 다음에는 블로그 글과 자주 쓰이는 관리 화면을 열 때에 select_full_join 값이 늘지 않는 것을 확인했다. 찾아보기를 쓰지 못하고 샅샅이 뒤지는 이어붙이기(full-join)가 사라진 덕분인지 MySQL 서버 엔진이 차지하는 램 용량이 아주 조금 줄었다. 방문객이 드물면 차이를 느끼기 어려울 수도 있다.

  물음글 시렁(쿼리 캐시)을 쓸 때에는 위와 같이 찾아보기를 더 넣지 않아도 select_full_join 값이 크게 늘지 않을 수 있다. 이미 실행한 물음글의 결과문을 시렁에서 그대로 가져올 수 있기 때문이다.