MySQL index - 인덱스 컬럼의 사이즈에 따른 조회 속도 성능 체크 삽질기

인덱스 컬럼 사이즈와 SELECT 속도에 대해서 테스트를 진행해보았습니다.

 

인덱스 컬럼 사이즈가 큰 경우에는 Page에 저장되는 인덱스정보개수가 적어지기 때문에, 페이지테이블에 대한 접근이 증가하기 때문에, 성능상 좋지 않다고 말씀드렸었습니다.

 

실제로 인덱스로 사용하는 컬럼의 사이즈가 큰 경우에 SELECT 조회에 어떤 영향을 주는지 확인해보겠습니다.

 

테스트를 진행하는 테이블의 스키마입니다.

mysql> desc employees;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| email      | varchar(50)  | NO   |     | NULL    |                |
| first_name | varchar(14)  | NO   |     | NULL    |                |
| last_name  | varchar(16)  | NO   |     | NULL    |                |
| birth_date | date         | NO   |     | NULL    |                |
| mobile     | varchar(30)  | NO   | MUL | NULL    |                |
| gender     | varchar(10)  | NO   | MUL | NULL    |                |
| zipcode    | varchar(15)  | NO   |     | NULL    |                |
| address    | varchar(200) | NO   |     | NULL    |                |
| hire_date  | date         | NO   |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+
10 rows in set (0.20 sec)

 

varchar 데이터타입을 많이 사용하고 있습니다.

먼저, varchar 데이터타입을 사용하는 컬럼의 데이터 저장사이즈를 한번 체크해보겠습니다.

mysql> SELECT AVG(LENGTH(email)),AVG(LENGTH(first_name)), AVG(LENGTH(last_name)), AVG(LENGTH(mobile)), AVG(LENGTH(gender)), AVG(LENGTH(zipcode)), AVG(LENGTH(address))  from employees;
+--------------------+-------------------------+------------------------+---------------------+---------------------+----------------------+----------------------+
| AVG(LENGTH(email)) | AVG(LENGTH(first_name)) | AVG(LENGTH(last_name)) | AVG(LENGTH(mobile)) | AVG(LENGTH(gender)) | AVG(LENGTH(zipcode)) | AVG(LENGTH(address)) |
+--------------------+-------------------------+------------------------+---------------------+---------------------+----------------------+----------------------+
|            22.9256 |                  5.9710 |                 6.5479 |             16.5999 |              1.0000 |               7.5003 |              11.5273 |
+--------------------+-------------------------+------------------------+---------------------+---------------------+----------------------+----------------------+
1 row in set (1 min 20.44 sec)

 

 

 

 

먼저 데이터 사이즈가 큰 컬럼들을 이용해서 인덱스를 생성해보겠습니다.

CREATE INDEX IDX_LARGE_COLUMNS ON employees (email, mobile, address);

인덱스로 사용한 총 컬럼의 사이즈 총합은 row당 51.03(22.92 + 16.59 + 11.52)바이트입니다.

 

 

 

데이터 사이즈가 작은 컬럼들을 이용해서 인덱스를 생성해보겠습니다.

CREATE INDEX IDX_SMALL_COLUMNS ON employees (last_name, zipcode, hire_date);

인덱스로 사용한 총 컬럼의 사이즈 총합은 row당 17.04(6.54 + 7.50 + 3)바이트입니다.

(* hire_date는 date타입인데, 3바이트를 사용합니다)

 

 

위에서 생성한 2개의 인덱스를 이용해서 쿼리를 20회 실행하였습니다.

select SQL_NO_CACHE * from employees use index (IDX_LARGE_COLUMNS) where email in ('iuhahhahiadphioa@gmail.com', 'iJHGFBSDLhioa@gmail.com', 'iuIUHFGD8923phioa@gmail.com', 'iFIULKB2dphioa@gmail.com', 'iuGFSOUNPa@gmail.com') 
AND mobile in ('02314090923', '098431525', '09419015', '023140', '094531525')
AND address in ('Seoul', 'Newyork', 'Pusan', 'Beijing', 'Singapore' );

select SQL_NO_CACHE * from employees use index (IDX_SMALL_COLUMNS) where 
last_name in ('Steve', 'Tom', 'Zeus', 'Marie', 'Steve')
AND zipcode in ('90345-23', '9483626', '09523901', '124113', '34215-23')
AND hire_date in ( DATE_FORMAT('20210310', 'YYYYMMDD'), DATE_FORMAT('20110310', 'YYYYMMDD'), DATE_FORMAT('20190110', 'YYYYMMDD'), DATE_FORMAT('20180210', 'YYYYMMDD'), DATE_FORMAT('20150110', 'YYYYMMDD'));

 

20회 조회한 결과입니다.

	IDX_LARGE_COLUMNS	IDX_SMALL_COLUMNS
1회	187ms	203ms
2회	203ms	188ms
3회	203ms	187ms
4회	203ms	188ms
5회	203ms	187ms
6회	188ms	203ms
7회	187ms	188ms
8회	188ms	188ms
9회	188ms	203ms
10회	203ms	187ms
11회	203ms	188ms
12회	203ms	203ms
13회	203ms	203ms
14회	204ms	187ms
15회	203ms	203ms
16회	188ms	203ms
17회	188ms	203ms
18회	203ms	204ms
19회	187ms	204ms
20회	203ms	203ms
평균	196.9ms	196.15ms

 

IDX_LARGE_COLUMNS가 IDX_SMALL_COLUMNS보다 

인덱스 컬럼의 사이즈 총합이 3배 가까이 되지만, 실제 검색쿼리의 시간에 큰 차이는 없었습니다.

 

SELECT조회시에 페이지테이블에 접근하는 횟수가 많아지고, 그로 인해 SELECT 조회 속도에 차이가 생길 것으로 예상했습니다만, 결과는 예상 밖이었습니다.

 

 

캐시를 사용하지 않게 하기 위해서 SQL_NO_CACHE 힌트를 주었으나, 결과가 예상밖이어서

이번에는 query_cache_type을 0으로 설정후에 다시 테스트를 진행해 보았습니다.

	IDX_LARGE_COLUMNS	IDX_SMALL_COLUMNS
1회	188	188
2회	188	203
3회	188	188
4회	203	187
5회	187	204
6회	188	187
7회	203	203
8회	187	188
9회	188	203
10회	187	203
11회	188	187
12회	188	203
13회	188	203
14회	188	188
15회	203	188
16회	187	188
17회	203	204
18회	188	188
19회	187	187
20회	188	187
평균	190.75	193.85

 

 

my.cnf의 query_cache_type 설정을 변경해도 비슷한 결과가 나왔습니다.

 

EPXLAIN결과를 다시 살펴 보았습니다.

mysql> explain select SQL_NO_CACHE * from employees use index (IDX_LARGE_COLUMNS) where email in ('iuhahhahiadphioa@gmail.com', 'iJHGFBSDLhioa@gmail.com', 'iuIUHFGD8923phioa@gmail.com', 'iFIULKB2dphioa@gmail.com', 'iuGFSOUNPa@gmail.com')
    -> AND mobile in ('02314090923', '098431525', '09419015', '023140', '094531525')
    -> AND address in ('Seoul', 'Newyork', 'Pusan', 'Beijing', 'Singapore' );
+----+-------------+-----------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys     | key               | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | employees | NULL       | range | IDX_LARGE_COLUMNS | IDX_LARGE_COLUMNS | 846     | NULL |  125 |   100.00 | Using index condition |
+----+-------------+-----------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+
1 row in set, 2 warnings (0.19 sec)


mysql> explain select SQL_NO_CACHE * from employees use index (IDX_SMALL_COLUMNS) where
    -> last_name in ('Steve', 'Tom', 'Zeus', 'Marie', 'Steve')
    -> AND zipcode in ('90345-23', '9483626', '09523901', '124113', '34215-23')
    -> AND hire_date in ( DATE_FORMAT('20210310', 'YYYYMMDD'), DATE_FORMAT('20110310', 'YYYYMMDD'), DATE_FORMAT('20190110', 'YYYYMMDD'), DATE_FORMAT('20180210', 'YYYYMMDD'), DATE_FORMAT('20150110', 'YYYYMMDD'));
+----+-------------+-----------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys     | key               | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | employees | NULL       | range | IDX_SMALL_COLUMNS | IDX_SMALL_COLUMNS | 97      | NULL |   20 |    50.00 | Using index condition |
+----+-------------+-----------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+

 

 

IDX_LARGE_COLUMNS를 이용하는 경우의 profile정보를 확인하였습니다.

show profile;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000080 |
| checking permissions | 0.000005 |
| Opening tables       | 0.000049 |
| init                 | 0.000033 |
| System lock          | 0.000004 |
| optimizing           | 0.000008 |
| statistics           | 0.000899 |
| preparing            | 0.000015 |
| executing            | 0.000002 |
| Sending data         | 0.000387 |
| end                  | 0.000003 |
| query end            | 0.000009 |
| closing tables       | 0.000005 |
| freeing items        | 0.000047 |
| cleaning up          | 0.000002 |
+----------------------+----------+
15 rows in set, 1 warning (0.19 sec)

 

 

IDX_SMALL_COLUMNS를 이용하는 경우의 profile정보를 확인하였습니다.

mysql> show profile;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000091 |
| checking permissions | 0.000005 |
| Opening tables       | 0.000015 |
| init                 | 0.000043 |
| System lock          | 0.000005 |
| optimizing           | 0.000009 |
| statistics           | 0.000193 |
| preparing            | 0.000015 |
| executing            | 0.000002 |
| Sending data         | 0.000074 |
| end                  | 0.000002 |
| query end            | 0.000005 |
| closing tables       | 0.000005 |
| freeing items        | 0.000051 |
| cleaning up          | 0.000002 |
+----------------------+----------+
15 rows in set, 1 warning (0.19 sec)

 

 

 

 

실행속도를 확인 하는 방법을 MySQL의 workbench를 이용해서 아래 정보를 이용해서 측정하였는데, 아무래도 측정방법이 잘못 된거 같습니다. 더 정확한 측정방법에 대해서 다음에 다시 정리해보겠습니다.