테스트를 진행하게 된 계기
MySQL을 사용하며 다중 컬럼 인덱스를 사용했다. 다중 컬럼 인덱스란 여러 개의 컬럼을 묶어 하나의 인덱스로 활용하는 방식이다. 해당 테이블에서 날짜, 시차, 장소번호의 조합에서 중복이 없어야 했기 때문에 아래의 쿼리로 다중 컬럼 유니크 인덱스를 만들었다.
alter table <table_name> add unique index <index name>(date, time_diff, location_num);
아무 생각없이 사용하던 중, MySQL의 Index를 공부하다 explain이라는 명령어를 알게됐다. MySQL 서버가 쿼리를 어떤 방식으로 수행할 것인가, 실행 계획을 알고 싶을 때 사용하는 명령어다. 내가 쓰는 테이블에도 explain을 통해 다양한 query의 실행계획을 알아보는 도중에, 인덱스를 타겠지라고 생각한 query가 full scan을 수행하는 경우를 발견했다. 뭐지 싶어서 당혹스러운 마음에 테스트용 테이블을 만들고 테스트를 진행했다.
첫 번째 테스트 조건 및 결론
조건
- index 여부만 다른 3개의 테이블
- 인덱스가 아예 없는 테이블 (no_idx)
- 기존에 사용하던 것 처럼 다중 컬럼 인덱스 하나(date, time_diff, location_num)를 가진 테이블 (total_uniq_idx)
- date, time_diff, location_num을 각각의 인덱스로 만든 테이블 (each_idx)
결과
- 다중 컬럼 인덱스는 생성할 때 제일 먼저 선정된 컬럼(Seq_in_index에서 가장 우선시되는 컬럼)이 빠지면 full-scan (query type: all)이다.
- 이걸 보다 확실히 하기 위해 두 번째 테스트를 진행했다.
- 다중 컬럼 인덱스에 포함된 컬럼들을 모두 where에 넣고 쿼리하면 성능이 매우 우수하다.
두 번째 테스트
4개의 컬럼(date, time_diff, location_num, success)로 다중 컬럼 인덱스를 만들고 Seq_in_index이 1인 컬럼(다중 컬럼 인덱스 만들 때 제일 먼저 온 컬럼)이 빠지면 full-scan인가를 알아보려고 진행했다.
결과
위의 가정이 맞다. 다중 컬럼 인덱스를 만든 컬럼을 모두 활용할 필요는 없고, Seq_in_index가 1인 컬럼, 혹은 1인 컬럼과 인덱스에 포함된 다른 컬럼을 조합하면 다중 컬럼 인덱스를 활용할 수 있다.
최종 결론
alter table <table_name> add unique index <index name>(date, time_diff, location_num);
나는 위의 명령어로 다중 컬럼 인덱스를 만들었다. 내가 select query에서 인덱스의 혜택을 보려면 where절에 반드시 date 컬럼은 들어가야 했다. 하지만 그렇게 select한 경우는 거의 없고 time_diff와 location_num을 조합해서 쿼리했다. (full scan만 주구장창 했다) 나의 상황에는 맞지 않는 인덱스였다..... 좀 더 효율적인 인덱싱 방식을 고려해야 겠다.
첫 번째 테스트 내용
table
MariaDB [sm_test]> show index from no_idx;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| no_idx | 0 | PRIMARY | 1 | id | A | 388597 | NULL | NULL | | BTREE | | |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
MariaDB [development]> show index from total_uniq_idx;
+----------------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| total_uniq_idx | 0 | PRIMARY | 1 | id | A | 392723 | NULL | NULL | | BTREE | | |
| total_uniq_idx | 0 | uniq_idx | 1 | date | A | 4412 | NULL | NULL | YES | BTREE | | |
| total_uniq_idx | 0 | uniq_idx | 2 | time_diff | A | 196361 | NULL | NULL | YES | BTREE | | |
| total_uniq_idx | 0 | uniq_idx | 3 | location_num | A | 392723 | NULL | NULL | YES | BTREE | | |
+----------------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.001 sec)
MariaDB [development]> show index from each_idx;
+----------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| each_idx | 0 | PRIMARY | 1 | id | A | 388405 | NULL | NULL | | BTREE | | |
| each_idx | 1 | idx_date | 1 | date | A | 4736 | NULL | NULL | YES | BTREE | | |
| each_idx | 1 | idx_diff | 1 | time_diff | A | 92 | NULL | NULL | YES | BTREE | | |
| each_idx | 1 | idx_loc | 1 | location_num | A | 12 | NULL | NULL | YES | BTREE | | |
+----------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
column A in where clause
MariaDB [development]> explain select * from no_idx where date="2022-02-22 10:00:00";
+------+-------------+--------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+--------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | no_idx | ALL | NULL | NULL | NULL | NULL | 388597 | Using where |
+------+-------------+--------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.001 sec)
MariaDB [development]> explain select * from total_uniq_idx where date="2022-02-22 10:00:00";
+------+-------------+----------------+------+---------------+----------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------------+------+---------------+----------+---------+-------+------+-------+
| 1 | SIMPLE | total_uniq_idx | ref | uniq_idx | uniq_idx | 6 | const | 294 | |
+------+-------------+----------------+------+---------------+----------+---------+-------+------+-------+
1 row in set (0.001 sec)
MariaDB [development]> explain select * from each_idx where date="2022-02-22 10:00:00";
+------+-------------+----------+------+---------------+----------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+------+---------------+----------+---------+-------+------+-------+
| 1 | SIMPLE | each_idx | ref | idx_date | idx_date | 6 | const | 294 | |
+------+-------------+----------+------+---------------+----------+---------+-------+------+-------+
MariaDB [development]> show profiles;
+----------+------------+-----------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-----------------------------------------------------------------------+
| 14 | 0.24012016 | select * from no_idx where date="2022-02-22 10:00:00" |
| 15 | 0.00451393 | select * from total_uniq_idx where date="2022-02-22 10:00:00" |
| 16 | 0.00421323 | select * from each_idx where date="2022-02-22 10:00:00" |
+----------+------------+-----------------------------------------------------------------------+
column B in where clause
MariaDB [development]> explain select * from no_idx where time_diff=15;
+------+-------------+--------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+--------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | no_idx | ALL | NULL | NULL | NULL | NULL | 388597 | Using where |
+------+-------------+--------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.001 sec)
MariaDB [development]> explain select * from total_uniq_idx where time_diff=15;
+------+-------------+----------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | total_uniq_idx | ALL | NULL | NULL | NULL | NULL | 392723 | Using where |
+------+-------------+----------------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.001 sec)
MariaDB [development]> explain select * from each_idx where time_diff=15;
+------+-------------+----------+------+---------------+----------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+------+---------------+----------+---------+-------+------+-------+
| 1 | SIMPLE | each_idx | ref | idx_diff | idx_diff | 9 | const | 8107 | |
+------+-------------+----------+------+---------------+----------+---------+-------+------+-------+
1 row in set (0.001 sec)
MariaDB [development]> show profiles;
+----------+------------+-----------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-----------------------------------------------------------------------+
| 17 | 0.19771821 | select * from no_idx where time_diff=15 |
| 19 | 0.23809865 | select * from total_uniq_idx where time_diff=15 |
| 20 | 0.05481219 | select * from each_idx where time_diff=15 |
+----------+------------+-----------------------------------------------------------------------+
column C in where clause
MariaDB [development]> explain select * from no_idx where location_num=0;
+------+-------------+--------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+--------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | no_idx | ALL | NULL | NULL | NULL | NULL | 388597 | Using where |
+------+-------------+--------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.001 sec)
MariaDB [development]> explain select * from total_uniq_idx where location_num=0;
+------+-------------+----------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | total_uniq_idx | ALL | NULL | NULL | NULL | NULL | 392723 | Using where |
+------+-------------+----------------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.001 sec)
MariaDB [development]> explain select * from each_idx where location_num=0;
+------+-------------+----------+------+---------------+---------+---------+-------+--------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+------+---------------+---------+---------+-------+--------+-------+
| 1 | SIMPLE | each_idx | ref | idx_loc | idx_loc | 9 | const | 160054 | |
+------+-------------+----------+------+---------------+---------+---------+-------+--------+-------+
1 row in set (0.001 sec)
MariaDB [development]> show profiles;
+----------+------------+-----------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-----------------------------------------------------------------------+
| 21 | 0.40787868 | select * from no_idx where location_num=0 |
| 22 | 0.39556710 | select * from total_uniq_idx where location_num=0 |
| 23 | 0.42857027 | select * from each_idx where location_num=0 |
+----------+------------+-----------------------------------------------------------------------+
column A, B in where clause
MariaDB [development]> explain select * from no_idx where date="2022-02-22 10:00:00" and time_diff=15;
+------+-------------+--------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+--------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | no_idx | ALL | NULL | NULL | NULL | NULL | 388597 | Using where |
+------+-------------+--------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.001 sec)
MariaDB [development]> explain select * from total_uniq_idx where date="2022-02-22 10:00:00" and time_diff=15;
+------+-------------+----------------+------+---------------+----------+---------+-------------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------------+------+---------------+----------+---------+-------------+------+-------+
| 1 | SIMPLE | total_uniq_idx | ref | uniq_idx | uniq_idx | 15 | const,const | 7 | |
+------+-------------+----------------+------+---------------+----------+---------+-------------+------+-------+
1 row in set (0.001 sec)
MariaDB [development]> explain select * from each_idx where date="2022-02-22 10:00:00" and time_diff=15;
+------+-------------+----------+-------------+-------------------+-------------------+---------+------+------+-------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+-------------+-------------------+-------------------+---------+------+------+-------------------------------------------------+
| 1 | SIMPLE | each_idx | index_merge | idx_date,idx_diff | idx_date,idx_diff | 6,9 | NULL | 6 | Using intersect(idx_date,idx_diff); Using where |
+------+-------------+----------+-------------+-------------------+-------------------+---------+------+------+-------------------------------------------------+
1 row in set (0.002 sec)
MariaDB [development]> show profiles;
+----------+------------+---------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+---------------------------------------------------------------------------------------------+
| 29 | 0.21803050 | select * from no_idx where date="2022-02-22 10:00:00" and time_diff=15 |
| 30 | 0.00135049 | select * from total_uniq_idx where date="2022-02-22 10:00:00" and time_diff=15 |
| 31 | 0.00896138 | select * from each_idx where date="2022-02-22 10:00:00" and time_diff=15 |
+----------+------------+---------------------------------------------------------------------------------------------+
column A, C in where clause
MariaDB [development]> explain select * from no_idx where date="2022-02-22 10:00:00" and location_num=0;
+------+-------------+--------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+--------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | no_idx | ALL | NULL | NULL | NULL | NULL | 388597 | Using where |
+------+-------------+--------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.001 sec)
MariaDB [development]> explain select * from total_uniq_idx where date="2022-02-22 10:00:00" and location_num=0;
+------+-------------+----------------+------+---------------+----------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------------+------+---------------+----------+---------+-------+------+-----------------------+
| 1 | SIMPLE | total_uniq_idx | ref | uniq_idx | uniq_idx | 6 | const | 294 | Using index condition |
+------+-------------+----------------+------+---------------+----------+---------+-------+------+-----------------------+
1 row in set (0.001 sec)
MariaDB [development]> explain select * from each_idx where date="2022-02-22 10:00:00" and location_num=0;
+------+-------------+----------+------+------------------+----------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+------+------------------+----------+---------+-------+------+-------------+
| 1 | SIMPLE | each_idx | ref | idx_date,idx_loc | idx_date | 6 | const | 294 | Using where |
+------+-------------+----------+------+------------------+----------+---------+-------+------+-------------+
1 row in set (0.002 sec)
+----------+------------+-------------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-------------------------------------------------------------------------------------------------+
| 35 | 0.21336171 | select * from no_idx where date="2022-02-22 10:00:00" and location_num=0 |
| 36 | 0.00235441 | select * from total_uniq_idx where date="2022-02-22 10:00:00" and location_num=0 |
| 37 | 0.00265221 | select * from each_idx where date="2022-02-22 10:00:00" and location_num=0 |
+----------+------------+-------------------------------------------------------------------------------------------------+
column B, C in where clause
MariaDB [development]> explain select * from no_idx where time_diff=15 and location_num=0;
+------+-------------+--------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+--------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | no_idx | ALL | NULL | NULL | NULL | NULL | 388597 | Using where |
+------+-------------+--------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.001 sec)
MariaDB [development]> explain select * from total_uniq_idx where time_diff=15 and location_num=0;
+------+-------------+----------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | total_uniq_idx | ALL | NULL | NULL | NULL | NULL | 392723 | Using where |
+------+-------------+----------------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.001 sec)
MariaDB [development]> explain select * from each_idx where time_diff=15 and location_num=0;
+------+-------------+----------+-------------+------------------+------------------+---------+------+------+------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+-------------+------------------+------------------+---------+------+------+------------------------------------------------+
| 1 | SIMPLE | each_idx | index_merge | idx_diff,idx_loc | idx_diff,idx_loc | 9,9 | NULL | 3340 | Using intersect(idx_diff,idx_loc); Using where |
+------+-------------+----------+-------------+------------------+------------------+---------+------+------+------------------------------------------------+
1 row in set (0.001 sec)
MariaDB [development]> show profiles;
+----------+------------+------------------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+------------------------------------------------------------------------------------------------------+
| 42 | 0.19190005 | select * from no_idx where time_diff=15 and location_num=0 |
| 43 | 0.19909273 | select * from total_uniq_idx where time_diff=15 and location_num=0 |
| 44 | 0.05068349 | select * from each_idx where time_diff=15 and location_num=0 |
+----------+------------+------------------------------------------------------------------------------------------------------+
column A, B, C in where clause
MariaDB [sm_test]> explain select * from no_idx where date="2022-02-22 10:00:00" and time_diff=15 and location_num=0;
+------+-------------+--------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+--------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | no_idx | ALL | NULL | NULL | NULL | NULL | 388597 | Using where |
+------+-------------+--------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.001 sec)
MariaDB [sm_test]> explain select * from total_uniq_idx where date="2022-02-22 10:00:00" and time_diff=15 and location_num=0;
+------+-------------+----------------+-------+---------------+----------+---------+-------------------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------------+-------+---------------+----------+---------+-------------------+------+-------+
| 1 | SIMPLE | total_uniq_idx | const | uniq_idx | uniq_idx | 24 | const,const,const | 1 | |
+------+-------------+----------------+-------+---------------+----------+---------+-------------------+------+-------+
1 row in set (0.002 sec)
MariaDB [sm_test]> explain select * from each_idx where date="2022-02-22 10:00:00" and time_diff=15 and location_num=0;
+------+-------------+----------+-------------+---------------------------+-------------------+---------+------+------+-------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+-------------+---------------------------+-------------------+---------+------+------+-------------------------------------------------+
| 1 | SIMPLE | each_idx | index_merge | idx_date,idx_diff,idx_loc | idx_date,idx_diff | 6,9 | NULL | 6 | Using intersect(idx_date,idx_diff); Using where |
+------+-------------+----------+-------------+---------------------------+-------------------+---------+------+------+-------------------------------------------------+
1 row in set (0.002 sec)
+----------+------------+---------------------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+---------------------------------------------------------------------------------------------------------+
| 66 | 0.20232242 | select * from no_idx where date="2022-02-22 10:00:00" and time_diff=15 and location_num=0 |
| 67 | 0.00119556 | select * from total_uniq_idx where date="2022-02-22 10:00:00" and time_diff=15 and location_num=0 |
| 68 | 0.00496843 | select * from each_idx where date="2022-02-22 10:00:00" and time_diff=15 and location_num=0 |
+----------+------------+---------------------------------------------------------------------------------------------------------+
두 번째 테스트 내용
column B, C, D in where clause
MariaDB [development]> create table four_idx select * from no_idx;
Query OK, 390296 rows affected (2.253 sec)
Records: 390296 Duplicates: 0 Warnings: 0
MariaDB [development]> alter table four_idx add index uniq_idx(date, time_diff, location_num, success);
Query OK, 0 rows affected (0.905 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [development]> show index from four_idx;
+----------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| four_idx | 1 | uniq_idx | 1 | date | A | 4703 | NULL | NULL | YES | BTREE | | |
| four_idx | 1 | uniq_idx | 2 | time_diff | A | 192851 | NULL | NULL | YES | BTREE | | |
| four_idx | 1 | uniq_idx | 3 | location_num | A | 385702 | NULL | NULL | YES | BTREE | | |
| four_idx | 1 | uniq_idx | 4 | success | A | 385702 | NULL | NULL | YES | BTREE | | |
+----------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.002 sec)
MariaDB [development]> explain select * from four_idx where time_diff=15 and location_num=0 and success=1;
+------+-------------+----------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | four_idx | ALL | NULL | NULL | NULL | NULL | 385702 | Using where |
+------+-------------+----------+------+---------------+------+---------+------+--------+-------------+
column A,B / A,C / A,D in where clause
MariaDB [development]> explain select * from four_idx where date="2022-02-22 10:00:00" and time_diff=15;
+------+-------------+----------+------+---------------+----------+---------+-------------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+------+---------------+----------+---------+-------------+------+-------+
| 1 | SIMPLE | four_idx | ref | uniq_idx | uniq_idx | 15 | const,const | 7 | |
+------+-------------+----------+------+---------------+----------+---------+-------------+------+-------+
1 row in set (0.001 sec)
MariaDB [development]> explain select * from four_idx where date="2022-02-22 10:00:00" and location_num=0;
+------+-------------+----------+------+---------------+----------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+------+---------------+----------+---------+-------+------+-----------------------+
| 1 | SIMPLE | four_idx | ref | uniq_idx | uniq_idx | 6 | const | 294 | Using index condition |
+------+-------------+----------+------+---------------+----------+---------+-------+------+-----------------------+
1 row in set (0.001 sec)
MariaDB [development]> explain select * from four_idx where date="2022-02-22 10:00:00" and success=1;
+------+-------------+----------+------+---------------+----------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+------+---------------+----------+---------+-------+------+-----------------------+
| 1 | SIMPLE | four_idx | ref | uniq_idx | uniq_idx | 6 | const | 294 | Using index condition |
+------+-------------+----------+------+---------------+----------+---------+-------+------+-----------------------+
leftmost prefix
- multiple column index(=composite index)는 leftmost prefix의 영향을 받는다.
- 가장 왼쪽에 있는 컬럼을 기준으로 sort를 진행하고 인덱스를 생성하기 때문이다. (B tree 구조의 index를 생각하면 당연한 일)
- 컬럼 a, b, c, d로 multiple column index를 만들고, where절에 a, b, d를 사용해 쿼리하면 실질적으로 인덱싱에 활용하는 컬럼은 a,b이고 d는 탐색할 row를 줄이는 데에는 활용하지 않는다. (아래의 포스트에서는 이것을 Gaps in the Prefix이라고 함)
- 바로 위에서 테스트했던 A,B / A,C / A,D 의 테스트도 동일하다.
- A,B는 leftmost prefix rule이 적용되어 A,B 까지는 인덱스를 이용해 스캔해야 할 rows를 줄였음
- A,C와 A,D는 A만 활용해서 스캔할 row를 줄였음. 그래서 rows 수가 똑같음
https://orangematter.solarwinds.com/2019/02/05/the-left-prefix-index-rule/
'Database' 카테고리의 다른 글
| 헛된 다중컬럼 인덱스와 진짜 다중컬럼 인덱스 (0) | 2022.05.26 |
|---|---|
| ES cluster 구축 과정 기록 (0) | 2022.05.26 |