[MySQL] File Sort 알아보기 ( sort_buffer_size , filesort )

- 목차

 

들어가며.

MySQL 은 ORDER BY 키워드가 적용된 Select Query 를 수행할 때에 내부적으로 데이터 정렬을 수행합니다.

데이터의 사이즈가 적은 Query 의 경우에는 기본적으로 메모리 영역에서 정렬을 수행합니다.

하지만 정렬해야하는 데이터의 규모가 메모리에서 수행할 수 없는 큰 규모인 경우에는 Disk 와 File 의 도움을 받습니다.

이러한 정렬을 File Sort 라고 부릅니다.

 

Sort Buffer 란 ?

MySQL 에서 Client 와 mysqld 가 서로 Connection 을 맺게 되면, MySQL 내부에서 하나의 Thread 가 생성됩니다.

이는 Session, Connection, Process, Thread 등 여러가지 표현으로 불리는데, show processlist; 쿼리로 확인할 수 있는 그 대상들이 바로 Connection 입니다.

아래와 같이 show processlist 쿼리를 실행하게 되면 172.18.0.3 Host 에서 연결된 Connection 을 확인할 수 있습니다.

mysql> show processlist;
+-----+-----------------+------------------+------+---------+-------+------------------------+-------------------+
| Id  | User            | Host             | db   | Command | Time  | State                  | Info              |
+-----+-----------------+------------------+------+---------+-------+------------------------+-------------------+
|   5 | event_scheduler | localhost        | NULL | Daemon  | 71116 | Waiting on empty queue | NULL              |
| 509 | root            | localhost        | NULL | Query   |     0 | init                   | show processlist  |
| 510 | root            | 172.18.0.3:56116 | NULL | Query   |     2 | User sleep             | select sleep(123) |
+-----+-----------------+------------------+------+---------+-------+------------------------+-------------------+
3 rows in set (0.00 sec)

 

이 Connection 은 mysqld 내부적으로 하나의 Thread 로 동작합니다.

그리고 이러한 Thread 는 Session Thread 라고도 부릅니다.

 

하나의 Session Thread 가 생성되면, 이는 내부적으로 여러가지 버퍼들을 사용하게 됩니다.

그중 하나가 Sorting 을 수행하기 위한 Sort Buffer 입니다.

Session Thread 는 Disk 나 Buffer Pool 로부터 Data Page 들을 버퍼로 로드합니다.

그리고 버퍼에 로드된 데이터들을 필터링하거나 정렬하게 됩니다.

 

Sort Buffer 의 크기는 sort_buffer_size 에 의해서 결정됩니다.

만약에 sort_buffer_size 보다 정렬해야할 데이터의 규모가 작으면 Sort Buffer 내부에서 정렬을 수행하구요.

그 규모가 매우 크게되면 File Sort 가 수행됩니다.

 

sort_buffer_size.

sort_buffer_size 는 하나의 Session Thread 가 사용하는 Sort Buffer 의 크기를 설정하는 옵션입니다.

sort_buffer_size 는 아래의 결과와 같이 기본적으로 256KB 가 설정됩니다.

show variables like 'sort_buffer_size';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| sort_buffer_size | 262144 |
+------------------+--------+
1 row in set (0.06 sec)

 

그리고 정렬해야한 데이터의 규모가 256KB 이상이면 File Sort 가 수행됩니다.

간단한 테스트를 위해서 아래와 같은 Table 을 생성합니다.

CREATE TABLE `large_table` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `product_id` int NOT NULL,
  `quantity` int DEFAULT '1',
  `price` decimal(10,2) NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB

 

그리고 저의 경우에는 6만개 이상의 데이터를 추가한 상태입니다.

select count(*) from test.large_table;
+----------+
| count(*) |
+----------+
|  6364618 |
+----------+

 

이 상태에서 Select Query 의 실행 계획을 살펴봅니다.

Index 가 적용되지 않은 price Column 을 기준으로 정렬하게 되면, 아래와 같이 "Extra : Using filesort" 가 출력됩니다.

explain select * from test.large_table order by price\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: large_table
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 566553490
     filtered: 100.00
        Extra: Using filesort
1 row in set, 1 warning (0.00 sec)

 

반면, Index 가 적용된 Primary Key 를 기준으로 Sorting 을 수행하게 되면, 

디스크에서 데이터의 레코드 혹은 페이지를 조회하는 단계에서 이미 정렬된 데이터를 가져오게 됩니다.

따라서 이러한 경우에는 File Sort 가 발생하지 않습니다.

explain select * from test.large_table order by id\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: large_table
   partitions: NULL
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 566553490
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.01 sec)

 

 

index 가 적용되지 않은 Column 의 정렬 (Full Table Scan and filesort) .

예를 들어서 아래와 같이 Indexing 이 적용되지 않은 price 칼럼에 Where 와 Order By 를 동시에 적용합니다.

explain select * from large_table 
where price < 0
order by price;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: large_table
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 566553490
     filtered: 33.33
        Extra: Using where; Using filesort
1 row in set, 1 warning (0.00 sec)

 

위 실행 계획과 같은 결과를 확인할 수 있습니다.

이를 분석해보면,

1. type: All --> 이는 Full Table Scan 을 수행함을 의미합니다.
2. Extra: Using where;
   --> 이것은 단순히 Where 쿼리를 수행한다는 의미인데, Full Table Scan 이나 file sort 가 수행될 때에 함께 표시되곤 합니다.
3. Extra: Using filesort;
   --> 정렬해야할 데이터의 수가 read_buffer_size 를 초과합니다. 그래서 filesort 가 수행됩니다.

여기서 중요한 점은 where price < 0; 조건에 해당하는 데이터는 사실 하나도 없습니다.

하지만 Indexing 이 적용되지 않은 price 기반의 조건은 모든 데이터들을 Full Table Scan 할 수 밖에 없습니다.

 

index 가 적용된 Column 의 정렬 (Full Table Scan) .

반면 아래와 같이 Indexing 되지 않은 price 칼럼으로 필터링을 적용하고, Primary Key 로 Sorting 을 적용하는 경우.

아래의 쿼리 실행은 Full Table Scan 이 동반됩니다.

실행 계획은 type: index & key: PRIMARY 로 보이지만, price 가 0 보다 작은 데이터를 필터링해야함으로 Full Table Scan 이 사용됩니다.

하지만 이 경우에는 Primary Key 를 기준으로 정렬된 데이터를 순서대로 조회하기 때문에 별도의 Sorting 작업이 필요하지 않습니다.

explain select * from large_table 
where price < 0
order by id;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: large_table
   partitions: NULL
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 6218236
     filtered: 33.33
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

 

 

데이터의 규모가 작으면 File Sort 가 적용되지 않는다.

아래와 같이 small_table 이라는 이름의 테이블을 생성하고 소량의 데이터를 추가합니다.

CREATE TABLE `small_table` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `product_id` int NOT NULL,
  `quantity` int DEFAULT '1',
  `price` decimal(10,2) NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO small_table (user_id, product_id, quantity, price) 
VALUES (FLOOR(RAND() * 1000000), FLOOR(RAND() * 10000), FLOOR(RAND() * 10) + 1, ROUND(RAND() * 500, 2));

 

그 후에 아래와 같이 실행 계획을 확인합니다.

총 6개의 데이터를 정렬하는데에 filesort 를 활용한다고 출력됩니다.

이는 뭔가 기대했던 결과는 아닌데요.

MySQL 은 내부적으로 Indexing 되지 않은 칼럼을 정렬할 때에 그 규모와 상관없이 무조건 Using filesort 라고 출력하는 것으로 보입니다.

 

explain select * from small_table order by price;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: small_table
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
     filtered: 100.00
        Extra: Using filesort

 

하지만 아래와 같이 File Sorting 의 횟수를 표시하는 status 를 통해서 내부적으로 File Sort 수행 결과를 확인할 수 있습니다.

SHOW GLOBAL STATUS LIKE 'Sort_merge_passes';

 

아래처럼 large_table 을 Unindexed Column 으로 정렬합니다.

그 결과 Sort_merge_passes 의 값은 1 -> 516 로 증가합니다.

select * from large_table order by price;

SHOW GLOBAL STATUS LIKE 'Sort_merge_passes';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Sort_merge_passes | 1   |
+-------------------+-------+

SHOW GLOBAL STATUS LIKE 'Sort_merge_passes';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Sort_merge_passes | 516   |
+-------------------+-------+

 

반면 Record 가 6개뿐인 small_table 의 경우에는 Unindexed Column 을 정렬하더라도 Sort_merge_passes 의 값이 증가하지 않습니다.

이는 filesort 가 실제로 수행되지 않음을 의미합니다.

select * from small_table order by price;

SHOW GLOBAL STATUS LIKE 'Sort_merge_passes';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Sort_merge_passes | 516   |
+-------------------+-------+