포럼
XE DB 튜닝하기
2014.02.02 02:28
글에 들어가기전에
XE가 전반적으로 타 프로그램보다 느리다는 대표적인 이유가 대용량 사용시 성능이 떨어진다는점 인데요. 최근 대용량 설계를 해보면서 느낀점인데 XE도 조금만 소스를 개선하면 빨라 질 수 있는데 왜 그렇게 하질 않는건가에 대해 이야기를 개진해보려고 합니다. 아래의 개선을 통해 XE의 성능이 어떻게 바뀌어가는지를 설명해보겠습니다.
목차
- 문제제기
- XE 성능시험
- 튜닝
- 마무리
1. 문제제기
XE의 DB 구조의 특징을 크게 두가지로 말하면 sequence와, 같은 종류는 둘 이상의 테이블로 나누지 않는다는 점에 있다. 이런상황에서 문제되는점은 한 테이블에 많은 양의 자료가 있을 경우 검색, 등록 등 일련의 처리가 느리진다는 점이다.
2. XE 성능시험
그렇다면 현재의 XE가 얼마나 느린지를 시험해보겠다. 아래는 이번 성능시험을 하는 스펙이다.(일본어는 읽지않기)
SSD 250G(150G/100G)
xe_documents에 가상의 데이터 100만 건을 가진 게시판을 등록하여 성능의 추이를 관측해 보았다.
mysql> select count(document_srl) from xe_documents; +---------------------+ | count(document_srl) | +---------------------+ | 1019200 | +---------------------+ 1 row in set (0.24 sec) mysql> select count(document_srl) from xe_documents where module_srl = 48; +---------------------+ | count(document_srl) | +---------------------+ | 1019200 | +---------------------+ 1 row in set (0.30 sec)
아래는 게시판의 첫 페이지를 XE 디버깅을 통해 받아본 결과이다.
stdClass Object
(
[error] => 0
[message] => success
[variables] => Array
(
[_query] => SELECT `documents`.`title`, `documents`.`nick_name`, `documents`.`regdate`, `documents`.`readed_count`, `documents`.`user_id`, `documents`.`is_notice`, `documents`.`document_srl`, `documents`.`module_srl`, `documents`.`category_srl`, `documents`.`lang_code`, `documents`.`member_srl`, `documents`.`last_update`, `documents`.`comment_count`, `documents`.`trackback_count`, `documents`.`uploaded_count`, `documents`.`status`, `documents`.`title_bold`, `documents`.`title_color` FROM `xe_documents` as `documents` WHERE `module_srl` in (48) and `status` in ('SECRET','PUBLIC') ORDER BY `list_order` asc LIMIT 0, 20
[_elapsed_time] => 0.00058
)
)
_elapsed_time를 보면 크게 성능이 떨어지는 경향은 보이지 않는다. 하지만 제일 마지막으로 가보면 어떻게 될까?
stdClass Object
(
[error] => 0
[message] => success
[variables] => Array
(
[_query] => SELECT `documents`.`title`, `documents`.`nick_name`, `documents`.`regdate`, `documents`.`readed_count`, `documents`.`user_id`, `documents`.`is_notice`, `documents`.`document_srl`, `documents`.`module_srl`, `documents`.`category_srl`, `documents`.`lang_code`, `documents`.`member_srl`, `documents`.`last_update`, `documents`.`comment_count`, `documents`.`trackback_count`, `documents`.`uploaded_count`, `documents`.`status`, `documents`.`title_bold`, `documents`.`title_color` FROM `xe_documents` as `documents` WHERE `module_srl` in (48) and `status` in ('SECRET','PUBLIC') ORDER BY `list_order` asc LIMIT 815340, 20
[_elapsed_time] => 30.41654
)
)
그렇다면 이 요청을 DB는 어떻게 처리하고 있는걸까?
mysql> explain SELECT `documents`.`title`, `documents`.`nick_name`, `documents`.`regdate`, `documents`.`readed_count`, `documents`.`user_id`, `documents`.`is_notice`, `documents`.`document_srl`, `documents`.`module_srl`, `documents`.`category_srl`, `documents`.`lang_code`, `documents`.`member_srl`, `documents`.`last_update`, `documents`.`comment_count`, `documents`.`trackback_count`, `documents`.`uploaded_count`, `documents`.`status`, `documents`.`title_bold`, `documents`.`title_color` FROM `xe_documents` as `documents` WHERE `module_srl` in (48) and `status` in ('SECRET','PUBLIC') ORDER BY `list_order` asc LIMIT 815340, 20 \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: documents
type: ref
possible_keys: idx_module_srl,idx_module_list_order,idx_module_update_order,idx_module_readed_count,idx_module_voted_count,idx_module_notice,idx_module_document_srl,idx_module_blamed_count,idx_module_status
key: idx_module_list_order
key_len: 8
ref: const
rows: 505041
Extra: Using where
1 row in set (1.24 sec)
ERROR:
No query specified
중요하게 봐줄 내용은 key(idx_module_list_order)이다. 해당 인덱스를 이용해서 결과를 산출하고 있는 것이다. 헌데 실제 쿼리문에는 `status` in ('SECRET','PUBLIC')라는 조건을 달고 있다. 이쯤에서 xe_documents 테이블의 인덱스가 어떻게 되어있는지 한번 보자.
idx_module_status라는 키가 보인다. 하지만 실제 동작은 idx_module_list_order 키를 인덱스로 사용했다. 그렇다면 idx_module_status로 인덱스를 사용하면 빨라질까? list_order 정렬을 하지 말고 수행해보았다. (그런데 왜 null일까? 보통 인덱스에 사용할 컬럼은 not null이 유리하다고 하는데..)
mysql> SELECT `documents`.`title`, `documents`.`nick_name`, `documents`.`regdate`, `documents`.`readed_count`, `documents`.`user_id`, `documents`.`is_notice`, `documents`.`document_srl`, `documents`.`module_srl`, `documents`.`category_srl`, `documents`.`lang_code`, `documents`.`member_srl`, `documents`.`last_update`, `documents`.`comment_count`, `documents`.`trackback_count`, `documents`.`uploaded_count`, `documents`.`status`, `documents`.`title_bold`, `documents`.`title_color` FROM `xe_documents` as `documents` WHERE `module_srl` in (48) and `status` in ('SECRET','PUBLIC') LIMIT 815340, 20;
--... 중략 ...
20 rows in set (30.49 sec)
mysql> explain SELECT `documents`.`title`, `documents`.`nick_name`, `documents`.`regdate`, `documents`.`readed_count`, `documents`.`user_id`, `documents`.`is_notice`, `documents`.`document_srl`, `documents`.`module_srl`, `documents`.`category_srl`, `documents`.`lang_code`, `documents`.`member_srl`, `documents`.`last_update`, `documents`.`comment_count`, `documents`.`trackback_count`, `documents`.`uploaded_count`, `documents`.`status`, `documents`.`title_bold`, `documents`.`title_color` FROM `xe_documents` as `documents` WHERE `module_srl` in (48) and `status` in ('SECRET','PUBLIC') LIMIT 815340, 20 \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: documents
type: ref
possible_keys: idx_module_srl,idx_module_list_order,idx_module_update_order,idx_module_readed_count,idx_module_voted_count,idx_module_notice,idx_module_document_srl,idx_module_blamed_count,idx_module_status
key: idx_module_srl
key_len: 8
ref: const
rows: 512693
Extra: Using where
1 row in set (0.01 sec)
ERROR:
No query specified
INDEX가 동작하지 않는 경우
다음 연산자는 인덱스를 타지 않는다.
not,<> 는 인덱스 사용못함(= >= <= 는 사용가능)
like '%value' 와 like '%value%'는 인덱스 사용못함(like 'like%'는 사용가능)
조건 컬럼을 가공하거나 연산을 하면 인덱스를 사용 못합니다.
문자열 타입에 인덱스를 걸경우 150 바이트 이하까지만 인덱스가 적용됩니다.
그렇다면 상대적으로 그 수가 작은 TEMP를 검색하는 요청을 해보자. 정상적으로 idx_module_status를 사용했고 출력 또한 빠른 결과를 보였다.
mysql> explain SELECT `documents`.`title`, `documents`.`nick_name`, `documents`.`regdate`, `documents`.`readed_count`, `documents`.`user_id`, `documents`.`is_notice`, `documents`.`document_srl`, `documents`.`module_srl`, `documents`.`category_srl`, `documents`.`lang_code`, `documents`.`member_srl`, `documents`.`last_update`, `documents`.`comment_count`, `documents`.`trackback_count`, `documents`.`uploaded_count`, `documents`.`status`, `documents`.`title_bold`, `documents`.`title_color` FROM `xe_documents` as `documents` WHERE `status` in ('TEMP') order by list_order asc LIMIT 0, 20 \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: documents
type: ref
possible_keys: idx_module_srl,idx_module_list_order,idx_module_update_order,idx_module_readed_count,idx_module_voted_count,idx_module_notice,idx_module_document_srl,idx_module_blamed_count,idx_module_status
key: idx_module_status
key_len: 70
ref: const,const
rows: 387776
Extra: Using where; Using filesort
1 row in set (0.00 sec)
ERROR:
No query specified
3. 튜닝
이제부터 하나하나 성능을 바꿔보겠다. 속도가 변화되는 과정에 주목해 주길 바라며 무언가를 포기할 수 있다는 점도 알아주길 바란다.
단, 이 튜닝을 위해서는 전제조건이 필요하다. 아래의 이슈를 확인하고 이것도 개선해야한다.
https://github.com/xpressengine/xe-core/issues/393
3-1. 서브쿼리
XE는 XML Query를 통해서 서브쿼리를 지원하고 있다. 총 3가지 종류를 통한 처리를 지원하는데 그중 커버링 인덱스를 지원하는 아래의 방식을 사용해보자.
<query id="getDocumentList" action="select"> <tables> <table query="true" alias="subdocuments"> <tables> <table name="documents" alias="documents" /> </tables> <columns> <column name="list_order" /> </columns> <conditions> <condition operation="in" column="module_srl" var="module_srl" filter="number" /> <condition operation="notin" column="module_srl" var="exclude_module_srl" filter="number" pipe="and" /> <condition operation="in" column="category_srl" var="category_srl" pipe="and" /> <condition operation="equal" column="is_notice" var="s_is_notice" pipe="and" /> <condition operation="equal" column="member_srl" var="member_srl" filter="number" pipe="and" /> <condition operation="in" column="status" var="statusList" pipe="and" /> <group pipe="and"> <condition operation="more" column="list_order" var="division" pipe="and" /> <condition operation="below" column="list_order" var="last_division" pipe="and" /> </group> <group pipe="and"> <condition operation="like" column="title" var="s_title" /> <condition operation="like" column="content" var="s_content" pipe="or" /> <condition operation="like" column="user_name" var="s_user_name" pipe="or" /> <condition operation="like" column="user_id" var="s_user_id" pipe="or" /> <condition operation="like" column="nick_name" var="s_nick_name" pipe="or" /> <condition operation="like" column="email_address" var="s_email_address" pipe="or" /> <condition operation="like" column="homepage" var="s_homepage" pipe="or" /> <condition operation="like" column="tags" var="s_tags" pipe="or" /> <condition operation="equal" column="member_srl" var="s_member_srl" pipe="or" /> <condition operation="more" column="readed_count" var="s_readed_count" pipe="or" /> <condition operation="more" column="voted_count" var="s_voted_count" pipe="or" /> <condition operation="less" column="blamed_count" var="s_blamed_count" pipe="or" /> <condition operation="more" column="comment_count" var="s_comment_count" pipe="or" /> <condition operation="more" column="trackback_count" var="s_trackback_count" pipe="or" /> <condition operation="more" column="uploaded_count" var="s_uploaded_count" pipe="or" /> <condition operation="like_prefix" column="regdate" var="s_regdate" pipe="or" /> <condition operation="like_prefix" column="last_update" var="s_last_update" pipe="or" /> <condition operation="like_prefix" column="ipaddress" var="s_ipaddress" pipe="or" /> </group> <group pipe="and"> <condition operation="more" column="last_update" var="start_date" pipe="and" /> <condition operation="less" column="last_update" var="end_date" pipe="and" /> </group> </conditions> <navigation> <index var="sort_index" default="list_order" order="order_type" /> <list_count var="list_count" default="20" /> <page_count var="page_count" default="10" /> <page var="page" default="1" /> </navigation> </table> <table name="documents" alias="documents" type="left join"> <conditions> <condition operation="equal" column="documents.list_order" default="subdocuments.list_order" /> </conditions> </table> </tables> <columns> <column name="documents.*" /> </columns> <conditions> <condition operation="in" column="documents.module_srl" var="module_srl" filter="number" /> </conditions> <navigation> <index var="sort_index" default="documents.list_order" order="asc" /> <list_count var="list_count" default="20" /> <page_count var="page_count" default="10" /> <page var="page2" default="1" /> </navigation> </query>
SELECT `documents`.`title`, `documents`.`nick_name`, `documents`.`regdate`, `documents`.`readed_count`, `documents`.`user_id`, `documents`.`is_notice`, `documents`.`document_srl`, `documents`.`module_srl`, `documents`.`category_srl`, `documents`.`lang_code`, `documents`.`member_srl`, `documents`.`last_update`, `documents`.`comment_count`, `documents`.`trackback_count`, `documents`.`uploaded_count`, `documents`.`status`, `documents`.`title_bold`, `documents`.`title_color`, `documents`.`list_order`, `documents`.`update_order` FROM (SELECT `list_order` FROM `xe_documents` as `documents` WHERE `module_srl` in (48) ORDER BY `list_order` desc LIMIT 0 , 20) as `subdocuments` left join `xe_documents` as `documents` on `documents`.`list_order` = `subdocuments`.`list_order` WHERE `documents`.`module_srl` in (48) and `status` in ('SECRET','PUBLIC') ORDER BY `list_order` asc LIMIT 0, 20;
해당 서브쿼리를 사용해서 아까 딜레이가 발생했던 부분에 수행해보겠다.
mysql> SELECT `documents`.`title`, `documents`.`nick_name`, `documents`.`regdate`, `documents`.`readed_count`, `documents`.`is_notice`, `documents`.`document_srl`, `documents`.`module_srl`, `documents`.`category_srl`, `documents`.`lang_code`, `documents`.`member_srl`, `documents`.`last_update`, `documents`.`comment_count`, `documents`.`trackback_count`, `documents`.`uploaded_count`, `documents`.`status`, `documents`.`title_bold`, `documents`.`title_color`, `documents`.`list_order`, `documents`.`update_order` FROM (SELECT `list_order` FROM `xe_documents` as `documents` WHERE `module_srl` in (48) and `status` in ('SECRET','PUBLIC') order by list_order asc LIMIT 815340, 20) as `subdocuments` left join `xe_documents` as `documents` on `documents`.`list_order` = `subdocuments`.`list_order` WHERE `documents`.`module_srl` in (48) ORDER BY `list_order` asc LIMIT 0, 20;
--... 중략 ...
20 rows in set (31.09 sec)
특별히 달라진 점은 없다. 오히려 1초 더 늦어진거 같은데? 정말 이게 효과가 있는걸까? 여기서 status 조건검색을 제외한 쿼리문으로 수행해보자.
mysql> SELECT `documents`.`title`, `documents`.`nick_name`, `documents`.`regdate`, `documents`.`readed_count`, `documents`.`is_notice`, `documents`.`document_srl`, `documents`.`module_srl`, `documents`.`category_srl`, `documents`.`lang_code`, `documents`.`member_srl`, `documents`.`last_update`, `documents`.`comment_count`, `documents`.`trackback_count`, `documents`.`uploaded_count`, `documents`.`status`, `documents`.`title_bold`, `documents`.`title_color`, `documents`.`list_order`, `documents`.`update_order` FROM (SELECT `list_order` FROM `xe_documents` as `documents` WHERE `module_srl` in (48) order by list_order asc LIMIT 815340, 20) as `subdocuments` left join `xe_documents` as `documents` on `documents`.`list_order` = `subdocuments`.`list_order` WHERE `documents`.`module_srl` in (48) ORDER BY `list_order` asc LIMIT 0, 20;
-- ... 중략 ...
20 rows in set (0.34 sec)
mysql> SELECT `documents`.`title`, `documents`.`nick_name`, `documents`.`regdate`, `documents`.`readed_count`, `documents`.`is_notice`, `documents`.`document_srl`, `documents`.`module_srl`, `documents`.`category_srl`, `documents`.`lang_code`, `documents`.`member_srl`, `documents`.`last_update`, `documents`.`comment_count`, `documents`.`trackback_count`, `documents`.`uploaded_count`, `documents`.`status`, `documents`.`title_bold`, `documents`.`title_color`, `documents`.`list_order`, `documents`.`update_order` FROM `xe_documents` as `documents` WHERE `module_srl` in (48) order by list_order asc LIMIT 815340, 20;
--... 중략 ...
20 rows in set (27.65 sec)
이 차이를 보라. status를 제외한 것만으로 서브쿼리에서는 상상이상의 결과를 보여준다. 그럼 여기서 의문을 가질 수 있겠는데 과연 게시물 리스트를 보는데 status 조건을 달아야하는것일까? 임시저장글이 아닌글을 뽑기 위해?? 여기서 하나를 포기해야하는 문제가 생긴다.
3-2. 임시저장글을 개선하고 status 조건검색 없애기
임시저장글의 개선을 통해 status 조건을 안쓰도록 해봤다.
/**
* Document temporary save
* @return void|Object
*/
function procDocumentTempSave()
{
// Check login information
if(!Context::get('is_logged')) return new Object(-1, 'msg_not_logged');
$module_info = Context::get('module_info');
$logged_info = Context::get('logged_info');
// Get form information
$obj = Context::getRequestVars();
// Change the target module to log-in information
$obj->module_srl = $logged_info->member_srl;
$obj->status = $this->getConfigStatus('temp');
unset($obj->is_notice);
/* End of file document.controller.php */
/* Location: ./modules/document/document.controller.php */위 조치를 하면 더 이상 status 검색을 하지 않아도 될 것이다. (기존에 TEMP가 있다면 module_srl를 수동으로 교체해줘야한다.)
이렇게 해둘시에 발생되는 문제점은 임시저장글을 복원할때 글 작성 시 사용한 게시물 모듈과 관계없이 다른 모듈에서도 복원이 된다는 점이다. 이 부분의 문제를 해결하자면 xe_documents 테이블에 extra_vars컬럼을 활용하여 임시저장된 모듈번호를 기억해두는 정도가 되겠다.
3-3. 정렬을 통해 처리속도를 높이자.
커버링 인덱스를 이용하여 굉장한 수행 단축에 성공했다. 하지만 이걸로 만족하는가? 실은 더 빨리 처리 가능하다.
mysql> SELECT `documents`.`title`, `documents`.`nick_name`, `documents`.`regdate`, `documents`.`readed_count`, `documents`.`is_notice`, `documents`.`document_srl`, `documents`.`module_srl`, `documents`.`category_srl`, `documents`.`lang_code`, `documents`.`member_srl`, `documents`.`last_update`, `documents`.`comment_count`, `documents`.`trackback_count`, `documents`.`uploaded_count`, `documents`.`status`, `documents`.`title_bold`, `documents`.`title_color`, `documents`.`list_order`, `documents`.`update_order` FROM (SELECT `list_order` FROM `xe_documents` as `documents` WHERE `module_srl` in (48) order by list_order desc LIMIT 0, 20) as `subdocuments` left join `xe_documents` as `documents` on `documents`.`list_order` = `subdocuments`.`list_order` WHERE `documents`.`module_srl` in (48) ORDER BY `list_order` asc LIMIT 0, 20 \G;
*************************** 1. row ***************************
title: 설정마법사 XML 설정 만들기
nick_name: 라르게덴
regdate: 2013-11-02 11:
readed_count: 133
is_notice: N
document_srl: 22
module_srl: 48
category_srl: 0
lang_code: ko
member_srl: 3222926
last_update: 2013-08-20 01:
comment_count: 0
trackback_count: 0
uploaded_count: 0
status: PUBLIC
title_bold: N
title_color: N
list_order: -20
update_order: -20
*************************** 2. row ***************************
title: nmsXE 이용에 필요한 필수 기능 설치, 확인 방법
nick_name: 라르게덴
regdate: 2013-02-14 06:
readed_count: 183
is_notice: N
document_srl: 21
module_srl: 48
category_srl: 0
lang_code: ko
member_srl: 3222926
last_update: 2012-12-16 21:
comment_count: 0
trackback_count: 0
uploaded_count: 4
status: PUBLIC
title_bold: N
title_color: N
list_order: -19
update_order: -19
//.. 중략 ..
20 rows in set (0.01 sec)
ERROR:
No query specified
mysql> SELECT `documents`.`title`, `documents`.`nick_name`, `documents`.`regdate`, `documents`.`readed_count`, `documents`.`is_notice`, `documents`.`document_srl`, `documents`.`module_srl`, `documents`.`category_srl`, `documents`.`lang_code`, `documents`.`member_srl`, `documents`.`last_update`, `documents`.`comment_count`, `documents`.`trackback_count`, `documents`.`uploaded_count`, `documents`.`status`, `documents`.`title_bold`, `documents`.`title_color`, `documents`.`list_order`, `documents`.`update_order` FROM (SELECT `list_order` FROM `xe_documents` as `documents` WHERE `module_srl` in (48) order by list_order asc LIMIT 1019180, 20) as `subdocuments` left join `xe_documents` as `documents` on `documents`.`list_order` = `subdocuments`.`list_order` WHERE `documents`.`module_srl` in (48) ORDER BY `list_order` asc LIMIT 0, 20 \G;
*************************** 1. row ***************************
title: 설정마법사 XML 설정 만들기
nick_name: 라르게덴
regdate: 2013-11-02 11:
readed_count: 133
is_notice: N
document_srl: 22
module_srl: 48
category_srl: 0
lang_code: ko
member_srl: 3222926
last_update: 2013-08-20 01:
comment_count: 0
trackback_count: 0
uploaded_count: 0
status: PUBLIC
title_bold: N
title_color: N
list_order: -20
update_order: -20
*************************** 2. row ***************************
title: nmsXE 이용에 필요한 필수 기능 설치, 확인 방법
nick_name: 라르게덴
regdate: 2013-02-14 06:
readed_count: 183
is_notice: N
document_srl: 21
module_srl: 48
category_srl: 0
lang_code: ko
member_srl: 3222926
last_update: 2012-12-16 21:
comment_count: 0
trackback_count: 0
uploaded_count: 4
status: PUBLIC
title_bold: N
title_color: N
list_order: -19
update_order: -19
//.. 중략 ..
20 rows in set (0.37 sec)
ERROR:
No query specified
mysql> SELECT `documents`.`title`, `documents`.`nick_name`, `documents`.`regdate`, `documents`.`readed_count`, `documents`.`is_notice`, `documents`.`document_srl`, `documents`.`module_srl`, `documents`.`category_srl`, `documents`.`lang_code`, `documents`.`member_srl`, `documents`.`last_update`, `documents`.`comment_count`, `documents`.`trackback_count`, `documents`.`uploaded_count`, `documents`.`status`, `documents`.`title_bold`, `documents`.`title_color`, `documents`.`list_order`, `documents`.`update_order` FROM (SELECT `list_order` FROM `xe_documents` as `documents` WHERE `module_srl` in (48) order by list_order asc LIMIT 509580, 20) as `subdocuments` left join `xe_documents` as `documents` on `documents`.`list_order` = `subdocuments`.`list_order` WHERE `documents`.`module_srl` in (48) ORDER BY `list_order` asc LIMIT 0, 20;
--.. 중략 ..
20 rows in set (0.19 sec)
3-4. SOLUTION
실제 이 서브쿼리를 사용하려면 여러 곳을 수정해야 한다. 이것만으로 수행하게 되면 네비게이션 출력에 문제가 발생하게 되서 페이지 계산을 할 수 없게 된다. 그외 문제에 대한 자세한 설명은 생략하겠다.
아래처럼 코드를 개선하면 사용이 가능해진다.
① xe/modules/document/queries/getDocumentListCount.xml 을 생성
<query id="getDocumentListCount" action="select"> <tables> <table name="documents" /> </tables> <columns> <column name="count(document_srl)" alias="count" /> </columns> <conditions> <condition operation="in" column="module_srl" var="module_srl" filter="number" /> <condition operation="notin" column="module_srl" var="exclude_module_srl" filter="number" pipe="and" /> <condition operation="in" column="category_srl" var="category_srl" pipe="and" /> <condition operation="equal" column="is_notice" var="s_is_notice" pipe="and" /> <condition operation="equal" column="member_srl" var="member_srl" filter="number" pipe="and" /> <group pipe="and"> <condition operation="more" column="list_order" var="division" pipe="and" /> <condition operation="below" column="list_order" var="last_division" pipe="and" /> </group> <group pipe="and"> <condition operation="like" column="title" var="s_title" /> <condition operation="like" column="content" var="s_content" pipe="or" /> <condition operation="like" column="user_name" var="s_user_name" pipe="or" /> <condition operation="like" column="user_id" var="s_user_id" pipe="or" /> <condition operation="like" column="nick_name" var="s_nick_name" pipe="or" /> <condition operation="like" column="email_address" var="s_email_address" pipe="or" /> <condition operation="like" column="homepage" var="s_homepage" pipe="or" /> <condition operation="like" column="tags" var="s_tags" pipe="or" /> <condition operation="equal" column="member_srl" var="s_member_srl" pipe="or" /> <condition operation="more" column="readed_count" var="s_readed_count" pipe="or" /> <condition operation="more" column="voted_count" var="s_voted_count" pipe="or" /> <condition operation="less" column="blamed_count" var="s_blamed_count" pipe="or" /> <condition operation="more" column="comment_count" var="s_comment_count" pipe="or" /> <condition operation="more" column="trackback_count" var="s_trackback_count" pipe="or" /> <condition operation="more" column="uploaded_count" var="s_uploaded_count" pipe="or" /> <condition operation="like_prefix" column="regdate" var="s_regdate" pipe="or" /> <condition operation="like_prefix" column="last_update" var="s_last_update" pipe="or" /> <condition operation="like_prefix" column="ipaddress" var="s_ipaddress" pipe="or" /> </group> <group pipe="and"> <condition operation="more" column="last_update" var="start_date" pipe="and" /> <condition operation="less" column="last_update" var="end_date" pipe="and" /> </group> </conditions> <navigation> <index var="n_sort_index" default="document_srl" order="asc" /> <list_count var="list_count" default="20" /> <page_count var="page_count" default="10" /> <page var="page" default="1" /> </navigation> </query>
② document.model.php 의 getDocumentList() 수정
function getDocumentList(...)
{
//.. 중략 ..
else
{
$navi = executeQueryArray($query_id.'Count', $args);
if($args->page > ($navi->total_page/2))
{
if($args->order_type == 'asc') $args->order_type = 'desc';
elseif($args->order_type == 'desc') $args->order_type = 'asc';
$args->page = ($navi->total_page - $args->page)+1;
}
$output = executeQueryArray($query_id, $args, $columnList);
$output->total_count = $navi->total_count;
$output->total_page = $navi->total_page;
$output->page = $navi->page;
$output->page_navigation = $navi->page_navigation;
}
}
// Return if no result or an error occurs
if(!$output->toBool()||!count($output->data)) return $output;
$idx = 0;
$data = $output->data;
unset($output->data);
if(!isset($virtual_number))
{
$virtual_number = $navi->total_count - ($navi->page - 1) * $args->list_count;
}
//.. 중략 ..
}
/* End of file document.model.php */
/* Location: ./modules/document/document.model.php */
③ document.controller.php 의 procDocumentTempSave() 수정
function procDocumentTempSave()
{
// Check login information
if(!Context::get('is_logged')) return new Object(-1, 'msg_not_logged');
$module_info = Context::get('module_info');
$logged_info = Context::get('logged_info');
// Get form information
$obj = Context::getRequestVars();
// Change the target module to log-in information
$obj->module_srl = $logged_info->member_srl;
$obj->status = $this->getConfigStatus('temp');
unset($obj->is_notice);
//.. 중략 ..
}
/* End of file document.controller.php */
/* Location: ./modules/document/document.controller.php */
④ board.view.php 의 _makeListColumnList() 수정
function _makeListColumnList()
{
//.. 중략 ..
// default column list add
$defaultColumn = array('document_srl', 'module_srl', 'category_srl', 'lang_code', 'member_srl', 'last_update', 'comment_count', 'trackback_count', 'uploaded_count', 'status', 'regdate', 'title_bold', 'title_color', 'list_order', 'update_order');
//.. 중략 ..
}
/* End of file board.view.php */
/* Location: ./modules/b/document.controller.php */4. 마무리
이제와서야 인덱스 수정이나 DB구조를 바꾸는건 현실적으로 어렵겠지만 아직 무리하지 않는 선에서 충분히 개선의 여지가 있습니다. 앞으로 하나하나 문제를 찾아서 개선해 나갔으면 좋겠습니다.
5. 덤
5-1. regdate, last_update 정렬은 하지말자.
아무리 서브쿼리라도 얘는 안된다. https://github.com/xpressengine/xe-core/pull/384
mysql> SELECT `documents`.`title`, `documents`.`nick_name`, `documents`.`regdate`, `documents`.`readed_count`, `documents`.`is_notice`, `documents`.`document_srl`, `documents`.`module_srl`, `documents`.`category_srl`, `documents`.`lang_code`, `documents`.`member_srl`, `documents`.`last_update`, `documents`.`comment_count`, `documents`.`trackback_count`, `documents`.`uploaded_count`, `documents`.`status`, `documents`.`title_bold`, `documents`.`title_color`, `documents`.`list_order`, `documents`.`update_order` FROM (SELECT `list_order` FROM `xe_documents` as `documents` WHERE `module_srl` in (48) order by `regdate` asc LIMIT 1019180, 20) as `subdocuments` left join `xe_documents` as `documents` on `documents`.`list_order` = `subdocuments`.`list_order` WHERE `documents`.`module_srl` in (48) ORDER BY `list_order` asc LIMIT 0, 20;
// .. 중략 ..
20 rows in set (9.19 sec)
mysql> SELECT `documents`.`title`, `documents`.`nick_name`, `documents`.`regdate`, `documents`.`readed_count`, `documents`.`is_notice`, `documents`.`document_srl`, `documents`.`module_srl`, `documents`.`category_srl`, `documents`.`lang_code`, `documents`.`member_srl`, `documents`.`last_update`, `documents`.`comment_count`, `documents`.`trackback_count`, `documents`.`uploaded_count`, `documents`.`status`, `documents`.`title_bold`, `documents`.`title_color`, `documents`.`list_order`, `documents`.`update_order` FROM (SELECT `list_order` FROM `xe_documents` as `documents` WHERE `module_srl` in (48) order by `list_order` asc LIMIT 1019180, 20) as `subdocuments` left join `xe_documents` as `documents` on `documents`.`list_order` = `subdocuments`.`list_order` WHERE `documents`.`module_srl` in (48) ORDER BY `list_order` asc LIMIT 0, 20;
// .. 중략 ..
20 rows in set (0.51 sec)
5-2. 파티셔닝
환경이 된다면 파티셔닝을 써보자. 테이블도 나눌 수 있고 좋다.
mysql> explain partitions SELECT `list_order` FROM `xe_documents` as `documents` WHERE `module_srl` in (48) order by `list_order` asc LIMIT 0, 20 \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: documents
partitions: m48
type: ref
possible_keys: idx_module_srl,idx_module_list_order,idx_module_update_order,idx_module_readed_count,idx_module_voted_count,idx_module_notice,idx_module_document_srl,idx_module_blamed_count,idx_module_status
key: idx_module_list_order
key_len: 8
ref: const
rows: 4997
Extra: Using where; Using index
1 row in set (0.00 sec)
ERROR:
No query specified
※ Mysql 쿼리문이 보기 불편한거 같아서 syntax highlighter를 일부 제거했습니다.
- [2017/08/08] 묻고답하기 시놀로지 XE 설치시 DB 연결 실패 *4
- [2016/03/24] 묻고답하기 폴더안 txt 문서를 php explode 하여 mysql 에 넣는 과정이 안됩니다ㅠㅠ
- [2015/10/29] 묻고답하기 [DB] xe_document_histories 라는 곳에 있는 것들을 지워도 되나요? *2
- [2014/08/29] 묻고답하기 게시글이동 쿼리질문입니다 *2
- [2014/05/11] 묻고답하기 도와주세요ㅠㅠ갑자기 관리자페이지랑 홈페이지가 안들어가지네요ㅠㅠ
댓글 18
-
CTN
2014.02.02 06:53
감사합니다.
-
대단한 분석력이군요... 오 갓..
-
오 좋은정보입니다.
-
Goos
2014.02.02 17:48
아직 무리하지 않는 선에서 충분히 개선의 여지가 있다는 말은 정말 와닿습니다. :) 많은 게시물을 처리하는 사이트가 되었을때 필연적으로 느려지기 때문에 사용하지 않는 공통 DB쿼리에 튜닝이 가해져야 했고 그렇게 갈라져버린 경우 이후 버전업을 계속 따라갈 여유가 없어지더라구요. 저 개인적으로도 한 세번쯤 올리다가 결국 지쳐서 1.5에 머물러 있습니다. 여기저기 만진게 너무 많고 소스관리를 물려놓은게 아닌 하드코딩이라 ㅠㅠ 쿼리에 대한 개선이 있다면 1.7을 올려보고 싶네요. -
야옹이님
2014.02.02 19:10
Great!
-
socialskyo
2014.02.03 00:18
부디 우선 순위에 두고 좋은 선택 하길 기대 해 봅니다.
-
대단하다~~~ ㅡㅡb
-
퍼니엑스이
2014.02.03 12:22
오랜만에 글다운 글을 보는군요. 읽어보니 세세하게 분석해놓으셔서 감탄했습니다.
임시 저장글의 경우 status 칼럼에 TEMP가 들어가게 되는데, status 칼럼으로 임시 저장글인지를 판별하지 않는다면 (Content 위젯 등에서) 대상 모듈을 선택하지 않았을 때 임시 저장글이 노출될 수 있습니다. 이 부분을 고려하지 않으신 것 같아 태클(?)을 걸어봅니다. -
@퍼니엑스이
의견 고맙습니다. 말씀하신데로의 문제점이 있네요. 그렇다면 퍼니엑스이님께서 이 문제를 어떻게 풀어야할지 의견을 제시해주신다면 정말 댓글다운 댓글이 될거 같습니다.
말씀해주신 부분의 해결방법을 부탁드리겠습니다. :p -
misol
2014.02.03 13:36
@라르게덴 임시 저장글을 저장하는 테이블은 따로 만들면 어떨까요?
자동저장되는 임시글은 에디터 모듈에 별도 테이블이 있습니다. -
ToFinder
2014.02.03 12:50
궁금한것이 있는데 임시저장이라는 부분의 활용성을 없애어 버린다면 속도면에서
더 빠른 결과를 얻을 수 있다고 글을 이해를 하였습니다. ( 맞겠죠? ^^ )
그렇다면 아예 임시저장이라는 기능을 빼 버리고 해당 쿼리를 삭제를 해버린다면
현제 속도보다 더 빠른 결과를 얻을 수 있다는 이야기인가요?..
이 부분에 대한 수정을 많이 해야할까요? 라는 궁금증이 생기네요. ㅎ.
-
@퍼니엑스이 @misol @ToFinder
자, 하루 시간을 드리겠습니다. 빨리 문제를 풀어주세요 ㅎㅎ. -
銀童
2014.02.03 16:04
별도 테이블등으로 관리하는것은 확장변수등 여러가지 문제가 발생할수 있고, 저장공간이 두개 생겨서 논리적 문제가 생길수 있어서 가능하면 지양하는게 좋지 않을까요?
저는, 개인적으로 지금 임시저장글을 없애고, 클라이언트에 임시 저장을 구현하고있습니다. 이 방식도 나쁘지 않더라구요 :)
괜히 서버랑 쓸데없는 통신도 줄이고. 비밀글도 없애서 STATUS 부분의 검색 condition 을 하나 없애니 충분히 효과가 있더군요.
-
@퍼니엑스이
집에 와서 해당 부분을 확인해보았습니다. 그런데 위 부분을 처리한다고 해서 content위젯이 문제될건 없네요. 별도로 xml query를 사용하고 있고 조건도 사이트번호에 해당하는 모듈 전체와 public이 들어가있네요(status 검색은 성능에 문제가 있겠지만) 그리고 최근 20개 글 정도라면 제가 위에 적어놓은것 처럼 성능상 저하는 크게 발생하지 않습니다. 태클(?)을 거시기 전에 한번쯤 살펴봐주셨으면 어땠을까하는 아쉬움이 조금 있네요. ^^
그래도 누군가가 테이블 조회를 통으로 할 수 있으니 임시저장글을 아래처럼 처리하면 어떨까 합니다.
module_srl = $logged_info->member_srl 를 0으로 바꾸고 extra_vars에 module_srl을 백업합니다.(양은 적을테니 파일로 떨어뜨려도 무관하고요.) 테이블 조회는 documents.module_srl >= 1 로 조회한다면 문제는 없어보입니다.
@ToFinder
임시저장과 임시저장글은 내가 회사나 학교에서 글을 작성하다가 도중에 임시저장하고 집에와서 다른컴퓨터로 글을 이어갈 수 있는 좋은 기능입니다. 없애서는 안됩니다. ^^ 임시저장값을 검색하지 않기 위해 해당 기능을 없애는 이야기가 아니라 임시저장값 검색이전에 다른 검색으로 구분을 시키자는겁니다.
-
ToFinder
2014.02.03 20:58
아하~ 이렇게 이야기르 하시니 좋은 기능이었군요 ㅎㅎ.
대체적으로 좋은 기능이기는 하지만 사용빈도를 본다면 아시리라 봅니다.
트래팩이었나? 해당 기능의 중단 ( 다음버전에.. ) 이 대표적이라 보여지네요.
그래도 이야기를 하시니 이것참 답을 내리기가 힘이드네요 ㅎㅎ. 블로거형이면 아무래도
꼭 필요한 기능이긴 한데요 ㅎㅎ.
-
게시판에서 임시 저장 기능을 제거하고 애드온이나 모듈 식으로 임시 저장 기능을 분리 시킬 순 없을까요?
임시 저장 기능 때문에 속도가 저하된다면 임시 저장 기능을 원치 않는 사이트에서 사용하지 않으면 낫지 않을까 싶습니다.
물론 근본적인 문제를 수정하는게 더 좋긴 하겠지만요.
-
영흥도우럭
2014.02.10 22:33
이번 부분이 XE에 공식적용되면 좋겠네요~ 좀 따라해보고 싶은데 문제발생시 대응불가실력이라.. 아쉽습니다. ^^
모두 기립!
박수....