포럼

XE DB 튜닝하기

2014.02.02 02:28

라르게덴

글에 들어가기전에

XE가 전반적으로 타 프로그램보다 느리다는 대표적인 이유가 대용량 사용시 성능이 떨어진다는점 인데요. 최근 대용량 설계를 해보면서 느낀점인데 XE도 조금만 소스를 개선하면 빨라 질 수 있는데 왜 그렇게 하질 않는건가에 대해 이야기를 개진해보려고 합니다. 아래의 개선을 통해 XE의 성능이 어떻게 바뀌어가는지를 설명해보겠습니다.


목차

  1. 문제제기
  2. XE 성능시험
  3. 튜닝
  4. 마무리



1. 문제제기

XE의 DB 구조의 특징을 크게 두가지로 말하면 sequence와, 같은 종류는 둘 이상의 테이블로 나누지 않는다는 점에 있다. 이런상황에서 문제되는점은 한 테이블에 많은 양의 자료가 있을 경우 검색, 등록 등 일련의 처리가 느리진다는 점이다.

2. XE 성능시험

그렇다면 현재의 XE가 얼마나 느린지를 시험해보겠다. 아래는 이번 성능시험을 하는 스펙이다.(일본어는 읽지않기)


spec.png

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
        )

)


예상했던 데로 어마어마한 결과다. 결과 수치는 시스템 성능에 좌우되는 부분이니만큼 30초나 걸렸다는 표현은 쓰지 않겠다. 최초 페이지와 최후 페이지에서 많은 성능의 차이가 발생한다는 점에 주목하길 바란다.


그렇다면 이 요청을 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 테이블의 인덱스가 어떻게 되어있는지 한번 보자.
index.png


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


별다른 차이가 없고 인덱스도 idx_module_status을 쓰지 않았다. 왜 사용안한것일까? 검색을 통해 알아본 결과 인덱스를 걸어둔 키도 유일성이 작은 키는 잘 사용되지 않고, 컬럼의 전체 검색 대상값 중 3분의1 이상의 값이 존재한다면 다른 키를 사용하거나 풀스캔을 한다고 한다. 그외에 인덱스가 동작하지 않는 경우는 아래를 통해 들을 수 있다.



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


status 컬럼에는 PUBLIC(일반글), SECRET(비밀글), TEMP(임시저장글) 3종류가 있다. XE는 게시물 관련 출력에서 항상 TEMP를 제외한 PUBLIC, SECRET를 검색하고 있는것이다.


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


서브쿼리안에 정렬을 역순으로해서 제일 끝을 상위로 올리고 검색을 시도하면 첫페이지를 보는것과 같은 속도를 보여준다. 이게 무슨소리인가하면 게시물 리스트업에 발생하는 딜레이를 아까보다 최대 2배가량 빠르게 할 수 있다는 소리다. 100만건의 게시물에서 50만건째가 최대 딜레이가 발생되는 지점이 되는 셈이다.

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를 일부 제거했습니다.


태그 연관 글
  1. [2017/08/08] 묻고답하기 시놀로지 XE 설치시 DB 연결 실패 by outsider *4
  2. [2016/03/24] 묻고답하기 폴더안 txt 문서를 php explode 하여 mysql 에 넣는 과정이 안됩니다ㅠㅠ by theaud****
  3. [2015/10/29] 묻고답하기 [DB] xe_document_histories 라는 곳에 있는 것들을 지워도 되나요? by Paul *2
  4. [2014/08/29] 묻고답하기 게시글이동 쿼리질문입니다 by poltwo *2
  5. [2014/05/11] 묻고답하기 도와주세요ㅠㅠ갑자기 관리자페이지랑 홈페이지가 안들어가지네요ㅠㅠ by young3mom