[MySQL/MariaDB] 대용량 페이징 처리 활용 방안(Pagination, 커버링 인덱스)

대용량 테이블에서 페이징 처리 시 맨 뒤쪽으로 갈수록 느려지는 현상을 보실 수 있습니다.  그러한 이유는 어느 순간까지는 인덱스를 활용해서 결과를 출력 가능 하지만, MySQL/MariaDB에서는 filesort가 발생하면서 성능 저하가 발생합니다.

옵티마이저는 정렬을 위해 인덱스 사용이 가능한지 확인하고, 가능하다면 'Filesort' 과정 없이 인덱스 순으로 결과를 반환하게 됩니다. 인덱스를 사용할 수 없다면 WHERE 조건에 일치하는 레코드를 검색해 정렬 버퍼에 저장하면서 정렬 처리(Filesort)를 합니다.  Order by, Group By를 처리할 때 인덱스를 처리하지 못할 경우에는 filesort 알고리즘을 통해 정렬하게 됩니다. 

Data -> Sort Buffer -> Temp File -> Result File -> Read Random Buffer


1. 테스트 환경

 

OS : CentOS 7.5(CPU : Intel(R) Xeon(R) CPU E3-1220 v3 @ 3.10GHz(4 core), Memory : 32GB)
DB : MariaDB 10.3.8
Parameter : 
innodb_buffer_pool_size =15G
sort_buffer_size = 1048576
innodb_sort_buffer_size = 1048576
max_length_for_sort_data = 1024
read_rnd_buffer_size = 8388608

Table 및 인덱스 정보 : 
EST_USER_CONN_LOGIN 전체 건수 : 2000546

EST_USER_CONN_LOGIN Primary Key
  - USER_CONN_HIST_SEQ_NO, type=int(9), auto_increment
EST_USER_CONN_LOGIN_IX02 인덱스 (결합 인덱스)
  - USER_LOGIN_DTTM, type=datetime
  - USER_LOGIN_RST_TP_CODE, type=varchar

 

2. 일반적인 페이징 쿼리

약 200만 건 테이블에서 뒤쪽에 있는 데이터를 추출하려고 할 때 상당히 오래 걸리는 것을 보실 수 있습니다. 그 이유는 인덱스를 제대로 사용하지 못하고, 데이터 블록을 정렬해서 가져오기 때문입니다.

 

SELECT    A.USER_LOGIN_DTTM, A.USER_ID, A.USER_NM
FROM      EST_USER_CONN_LOGIN A
ORDER BY  A.USER_LOGIN_DTTM
LIMIT     0, 10    /* 앞쪽 데이터를 엑세스 할 경우 속도 영향 없습니다. */
;

수행 시간 : 2ms

SELECT    A.USER_LOGIN_DTTM, A.USER_ID, A.USER_NM
FROM      EST_USER_CONN_LOGIN A
ORDER BY  A.USER_LOGIN_DTTM
LIMIT     2000536, 10 /* 맨 뒤쪽 데이터를 엑세스 할 경우 속도 지연 현상이 발생 합니다.*/
;

수행 시간 : 1m 44s

ANALYZE   FORMAT=JSON 결과
{
  "query_block": {
    "select_id": 1,
    "r_loops": 1,
    "r_total_time_ms": 3347.9,
    "read_sorted_file": {
      "r_rows": 2e6,
      "filesort": {                       ->> Filesort 발생!!
        "sort_key": "A.USER_LOGIN_DTTM",
        "r_loops": 1,
        "r_total_time_ms": 2968.6,
        "r_limit": 2000546,
        "r_used_priority_queue": false,
        "r_output_rows": 2000546,
        "r_sort_passes": 84,
        "r_buffer_size": "1023Kb",
        "table": {
          "table_name": "A",
          "access_type": "ALL",
          "r_loops": 1,
          "rows": 1950976,
          "r_rows": 2e6,
          "r_total_time_ms": 693.89,
          "filtered": 100,
          "r_filtered": 100
        }
      }
    }
  }
}

 

 

3. 튜닝한 페이징 쿼리(Covering Index) 

Covering Index(커버링 인덱스)를 활용해서 대용량 데이터를 처리하는 방법으로 성능을 높일 수 있습니다.
커버링 인덱스는 데이터를 인덱스에서만 추출할 수 있는 인덱스를 의미하며, B-Tree 스캔만으로 원하는 데이터를 가져올 수 있습니다.(데이터 블록 사용 안 함)

 

SELECT    B.USER_LOGIN_DTTM, B.USER_ID, B.USER_NM
FROM      (
          SELECT    A.USER_CONN_HIST_SEQ_NO
          FROM      EST_USER_CONN_LOGIN A
          ORDER BY  A.USER_LOGIN_DTTM
          LIMIT     2000536, 10
          ) A
          STRAIGHT_JOIN EST_USER_CONN_LOGIN B ON (A.USER_CONN_HIST_SEQ_NO = B.USER_CONN_HIST_SEQ_NO)
          ;

수행 시간 : 389ms

ANALYZE   FORMAT=JSON 결과
{
  "query_block": {
    "select_id": 1,
    "r_loops": 1,
    "r_total_time_ms": 0.067,
    "table": {
      "table_name": "",
      "access_type": "ALL",
      "r_loops": 1,
      "rows": 10,
      "r_rows": 10,
      "r_total_time_ms": 0.0026,
      "filtered": 100,
      "r_filtered": 100,
      "materialized": {
        "query_block": {
          "select_id": 2,
          "r_loops": 1,
          "r_total_time_ms": 0.0261,
          "table": {
            "table_name": "A",
            "access_type": "index",
            "key": "EST_USER_CONN_LOGIN_IX02",
            "key_length": "10",
            "used_key_parts": ["USER_LOGIN_DTTM", "USER_LOGIN_RST_TP_CODE"],
            "r_loops": 1,
            "rows": 1950976,
            "r_rows": 10,
            "r_total_time_ms": 0.0157,
            "filtered": 100,
            "r_filtered": 100,
            "using_index": true
          }
        }
      }
    },
    "table": {
      "table_name": "B",
      "access_type": "eq_ref",
      "possible_keys": ["PRIMARY"],
      "key": "PRIMARY",
      "key_length": "4",
      "used_key_parts": ["USER_CONN_HIST_SEQ_NO"],
      "ref": ["A.USER_CONN_HIST_SEQ_NO"],
      "r_loops": 10,
      "rows": 1,
      "r_rows": 1,
      "r_total_time_ms": 0.0201,
      "filtered": 100,
      "r_filtered": 100
    }
  }
}

 

Covering Index를 사용하기 전에는 100000건부터 10건을 가져올 때 "Filesort"가 발생했지만, Covering Index를 활용하게 되면 "Using Index"를 확인할 수 있습니다. 

가장 마지막에 존재하는 데이터를 가져올 때 1m 44s에서 389ms로 단축된 것을 보실 수 있습니다. 결과적으로 Primary Key를 가져와서 인덱스 접근만으로 데이터를 가져올 수 있게 한 것이 큰 효과를 보게 되었습니다.

 

 

4. 데이터 범위 

 

아래 내용은 화면 기획자, 개발 담당자와 긴밀한 협업이 필요한 내용입니다. 데이터 모델링을 할 때 데이터의 범위를 미리 생각해서 인덱스를 활용하는 방법입니다. 전체를 대상으로 데이터를 가져오는 경우는 많이 적기 때문에 화면을 통해서 제어를 한다면 전체 데이터 200만 건을 모두 확인할 필요 없이 적절한 범위 내에서 데이터를 가져올 수 있습니다. 조회 조건에서 기간을 3개월만 조회 가능하도록 정의하면 아래처럼 WHERE 조건절에 범위를 줄일 수 있습니다.  커버링 인텍스를 활용한다고 해도 데이터 양이 늘어날수록 조금씩 느려지게 되는데 아래와 같이 범위를 정하게 되면 동일한 속도를 유지할 수 있습니다.

최종적으로는 55ms로 줄이게 되었습니다.

 

SELECT    B.USER_LOGIN_DTTM, B.USER_ID, B.USER_NM
FROM      (
          SELECT    A.USER_CONN_HIST_SEQ_NO
          FROM      EST_USER_CONN_LOGIN A
          WHERE     A.USER_LOGIN_DTTM BETWEEN STR_TO_DATE( CONCAT('20200201', '00:00'), '%Y%m%d%H:%i')   /* 조회 조건 활용 */
                                      AND STR_TO_DATE(CONCAT('20200430', '23:59'),'%Y%m%d%H:%i')
          ORDER BY  A.USER_LOGIN_DTTM
          LIMIT     200000, 10
          ) A
          STRAIGHT_JOIN EST_USER_CONN_LOGIN B ON (A.USER_CONN_HIST_SEQ_NO = B.USER_CONN_HIST_SEQ_NO)
          ;

수행 시간 : 55ms

 

5. ROW_COUNT( ), FOUND_ROWS( ) 적절한 활용

 

목록을 보여주는 화면의 경우 현재 건수/전체 건수와 같이 표현하는 경우가 많은데 그럴 때 전체 조회건수를 동작하기 위해서 같은 쿼리를 COUNT(*) 함수를 활용해서 실행하게 되면 비효율이 발생할 수 있습니다.
그러한 부분을 개선해주기 위해서 ROW_COUNT( ), FOUND_ROWS( ) 적절히 사용해도 좋습니다..

 

FOUND_ROWS() 함수는 SELECT 수행 후 결과를 리턴 받으며,
ROW_COUNT() 함수는 DML(Delete, Update, Insert) 문장을 수행 시 결과를 리턴합니다.

 

6. 결론 

MySQL/MariaDB에서 대용량 데이터를 페이징 할 때는 커버링 인덱스를 적극적으로 활용하고, 화면 기획 담당자, 개발 담당자 협업을 통해서 조건에 대한 범위를 줄이고자 노력한다면 데이터가 늘어나도 동일한 속도를 유지할 수 있습니다.  전체 건수를 활용할 때는 ROW_COUNT( ), FOUND_ROW( ) 활용해보는 것도 좋습니다.

 

 

7. 참고

• ktdsoss.tistory.com/423
• gywn.net/2012/04/mysql-covering-index/
• blog.lulab.net/database/optimize-pagination-sql-by-join-instead-of-limit/
• elky84.github.io/2018/10/05/mysql/