헛된 다중컬럼 인덱스와 진짜 다중컬럼 인덱스

개요

이전에 explain 기능을 써보다가 다중컬럼 인덱스를 걸어놓은 테이블에서 full scan을 하는 상황을 발견했다. 이후에 테이블을 수정해서 사용하고 있다. 수정한 것이라고는 다중컬럼 인덱스를 만들 때 순서를 수정한 것이다. where 절에 항상 넣는 location_num을 가장 먼저 넣어 unique index를 생성했다. 원래 테이블 unique index의 Seq_in_index의 1번이 date인데 반해 새로운 테이블 unique index의 Seq_in_index의 1번이 location_num인 것이 차이점이다.

ALTER TABLE new_index_table ADD UNIQUE INDEX u_idx(location_num, date, time_diff);

이전의 테이블과 현재 수정하고 쓰고있는 테이블의 성능차이를 확인하고자 한다. 내가 자주 활용하는 쿼리를 해보고 탐색하는 행 수, 쿼리 소요시간을 비교한다.

2022.03.03 - [Database] - MySQL 다중 컬럼 인덱스 사용 방법에 대해

MySQL 다중 컬럼 인덱스 사용 방법에 대해

Table

MariaDB [development]> show index from original_table;
+----------------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table          | Non_unique | Key_name | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| original_table |          0 | PRIMARY  |            1 | id           | A         |      443576 |     NULL | NULL   |      | BTREE      |         |               |
| original_table |          0 | u_idx    |            1 | date         | A         |        4620 |     NULL | NULL   | YES  | BTREE      |         |               |
| original_table |          0 | u_idx    |            2 | time_diff    | A         |      221788 |     NULL | NULL   | YES  | BTREE      |         |               |
| original_table |          0 | u_idx    |            3 | location_num | A         |      443576 |     NULL | NULL   | YES  | BTREE      |         |               |
+----------------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

MariaDB [development]> show index from new_index_table; 
+-----------------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table           | Non_unique | Key_name | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| new_index_table |          0 | u_idx    |            1 | location_num | A         |          12 |     NULL | NULL   | YES  | BTREE      |         |               |
| new_index_table |          0 | u_idx    |            2 | date         | A         |       25423 |     NULL | NULL   | YES  | BTREE      |         |               |
| new_index_table |          0 | u_idx    |            3 | time_diff    | A         |      457620 |     NULL | NULL   | YES  | BTREE      |         |               |
+-----------------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.001 sec)

Test 1

MariaDB [development]> SET profiling=1;

MariaDB [development]> explain select * from original_table where month(date)=5 and location_num=0;
+------+-------------+----------------+------+---------------+------+---------+------+--------+-------------+
| id   | select_type | table          | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+------+-------------+----------------+------+---------------+------+---------+------+--------+-------------+
|    1 | SIMPLE      | original_table | ALL  | NULL          | NULL | NULL    | NULL | 443576 | Using where |
+------+-------------+----------------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.001 sec)

MariaDB [development]> explain select * from new_index_table where month(date)=5 and location_num=0;
+------+-------------+-----------------+------+---------------+-------+---------+-------+--------+-----------------------+
| id   | select_type | table           | type | possible_keys | key   | key_len | ref   | rows   | Extra                 |
+------+-------------+-----------------+------+---------------+-------+---------+-------+--------+-----------------------+
|    1 | SIMPLE      | new_index_table | ref  | u_idx         | u_idx | 9       | const | 180438 | Using index condition |
+------+-------------+-----------------+------+---------------+-------+---------+-------+--------+-----------------------+
1 row in set (0.001 sec)

MariaDB [development]> show profiles;
+----------+------------+-----------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                             |
+----------+------------+-----------------------------------------------------------------------------------+
|        4 | 0.49608423 | select * from original_table where month(date)=5 and location_num=0               |
|        5 | 0.06381534 | select * from new_index_table where month(date)=5 and location_num=0              |
+----------+------------+-----------------------------------------------------------------------------------+

• 인덱싱이 잘못 되어있던 기존 테이블은 443,576 row를 인덱스를 수정한 테이블은 180,438 row를 탐색한다.
• 기존 테이블의 소요시간은 0.49608423초, 수정한 테이블은 0.06381534초가 걸린다. (7.77배 차이)

Test 2

MariaDB [development]> explain select * from original_table where year(date)=2021 and month(date) between 6 and 8 and location_num=0;
+------+-------------+----------------+------+---------------+------+---------+------+--------+-------------+
| id   | select_type | table          | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+------+-------------+----------------+------+---------------+------+---------+------+--------+-------------+
|    1 | SIMPLE      | original_table | ALL  | NULL          | NULL | NULL    | NULL | 443576 | Using where |
+------+-------------+----------------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.001 sec)

MariaDB [development]> explain select * from new_index_table where year(date)=2021 and month(date) between 6 and 8 and location_num=0;
+------+-------------+-----------------+------+---------------+-------+---------+-------+--------+-----------------------+
| id   | select_type | table           | type | possible_keys | key   | key_len | ref   | rows   | Extra                 |
+------+-------------+-----------------+------+---------------+-------+---------+-------+--------+-----------------------+
|    1 | SIMPLE      | new_index_table | ref  | u_idx         | u_idx | 9       | const | 180438 | Using index condition |
+------+-------------+-----------------+------+---------------+-------+---------+-------+--------+-----------------------+
1 row in set (0.001 sec)

MariaDB [development]> show profiles;
+----------+------------+--------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                                                          |
+----------+------------+--------------------------------------------------------------------------------------------------------------------------------+
|       10 | 0.20854691 | select * from original_table where year(date)=2021 and month(date) between 6 and 8 and location_num=0                          |
|       11 | 0.06477613 | select * from new_index_table where year(date)=2021 and month(date) between 6 and 8 and location_num=0                         |
+----------+------------+--------------------------------------------------------------------------------------------------------------------------------+

• 인덱싱이 잘못 되어있던 기존 테이블은 443,576 row를 인덱스를 수정한 테이블은 180,438 row를 탐색한다.
• 기존 테이블의 소요시간은0.20854691초, 수정한 테이블은0.06477613초가 걸린다. (3.21배 차이)

결과

이제서야 인덱스를 제대로 활용할 수 있게 되었다. 성능 개선을 위해 무언가 방법을 적용했다면 그 방법이 효과가 있는가, 있으면 얼마나 있는가 확인하는 습관을 들여야겠다.