[Infra] 9. MariaDB 인덱스 설정 및 쿼리 성능 개선 - Voda Project

 

해당 프로젝트에서는 소셜 로그인 (카카오)를 기준으로 로그인이 진행된다. 우리 서비스에서는 소셜 로그인을 통해 받아온 email 정보를 가지고 유저 정보를 확인한다. 즉, PK 값이 아닌 컬럼 (email)은 pk 값과 동일한 역할을 한다. 그러므로 email 컬럼에 인덱스를 적용하여 쿼리 성능을 측정해보려고 한다.

 

먼저 서버 환경에서 MariaDB 컨테이너 내부로 접속한다. 

 

 

MariaDB [voda]> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
1 row in set (0.000 sec)

MariaDB [voda]> set profiling = 1;
Query OK, 0 rows affected (0.003 sec)

MariaDB [voda]> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           1 |
+-------------+
1 row in set (0.000 sec)

 

 

| 기존 User DB

 

 

| 프로시저 생성

MariaDB [(none)]> delimiter //
MariaDB [(none)]> create procedure kakao()
    -> BEGIN
    -> DECLARE i INT DEFAULT 1;
    -> WHILE (i <= 10000) DO
    -> SET @kakaId = CONCAT('kakaoemail', i);
    -> insert into user (email)
    -> values  (@kakaoId);
    -> SET i = i + 1;
    -> END WHILE;
    -> END
    -> //
    
MariaDB [(none)]> delimiter ; 다시 복원

 

 

| call kakao(); 호출

|
|    11000 | 2023-03-31 03:03:53.814587 | 2023-03-31 03:03:53.814587 |           | kakaoemail9995         | NULL     | NULL            |
|    11001 | 2023-03-31 03:03:53.815401 | 2023-03-31 03:03:53.815401 |           | kakaoemail9996         | NULL     | NULL            |
|    11002 | 2023-03-31 03:03:53.816264 | 2023-03-31 03:03:53.816264 |           | kakaoemail9997         | NULL     | NULL            |
|    11003 | 2023-03-31 03:03:53.817264 | 2023-03-31 03:03:53.817264 |           | kakaoemail9998         | NULL     | NULL            |
|    11004 | 2023-03-31 03:03:53.818176 | 2023-03-31 03:03:53.818176 |           | kakaoemail9999         | NULL     | NULL            |
|    11005 | 2023-03-31 03:03:53.819302 | 2023-03-31 03:03:53.819302 |           | kakaoemail10000        | NULL     | NULL            |
+----------+----------------------------+----------------------------+-----------+------------------------+----------+-----------------+
10006 rows in set (0.008 sec)

 

데이터 생성하는데 시간이 조금 걸린다. 이후 "select * from user;" 쿼리문을 날렸을 때, 10000 개의 유저 정보가 추가 된 것을 확인할 수 있다.

 

 

 

| DB 인덱스 조회

MariaDB [voda]> show index from user;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| user  |          0 | PRIMARY  |            1 | user_seq    | A         |        9925 |     NULL | NULL   |      | BTREE      |         |               | NO      |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
1 row in set (0.001 sec

 

현재 인덱스 하나 존재. PK 값을 인덱스로 가지고있다.이 상태로 쿼리문 하나를 날려보자. 

 

MariaDB [voda]> select * from user u where u.email = 'kakaoemail9992';
+----------+----------------------------+----------------------------+-----------+----------------+----------+----------+
| user_seq | mod_dtm                    | reg_dtm                    | delete_yn | email          | model_id | nickname |
+----------+----------------------------+----------------------------+-----------+----------------+----------+----------+
|    10997 | 2023-03-31 03:03:53.810187 | 2023-03-31 03:03:53.810187 |           | kakaoemail9992 | NULL     | NULL     |
+----------+----------------------------+----------------------------+-----------+----------------+----------+----------+
1 row in set (0.001 sec)

MariaDB [voda]> show profiles;
|       14 | 0.00418660 | select * from user u where u.email = 'kakaoemail9992'    |
+----------+------------+----------------------------------------------------------+
14 rows in set (0.000 sec)

 

현재 하나의 행을 뽑기 위한 쿼리 속도는 0.00418660 이다. 

 

 

| email 인덱스 설정

MariaDB [voda]> create index kakao on user(email);
Query OK, 0 rows affected (0.030 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [voda]> show index from user;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+--------                                                              ----+---------+---------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_t                                                              ype | Comment | Index_comment | Ignored |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+--------                                                              ----+---------+---------------+---------+
| user  |          0 | PRIMARY  |            1 | user_seq    | A         |        9925 |     NULL | NULL   |      | BTREE                                                                    |         |               | NO      |
| user  |          1 | kakao    |            1 | email       | A         |        9925 |     NULL | NULL   |      | BTREE                                                                    |         |               | NO      |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+--------                                                              ----+---------+---------------+---------+
2 rows in set (0.001 sec)

 

인덱스 설정 2개가 되어 있다. 이제 다시 쿼리 성능 측정 해본다.

 

 

MariaDB [voda]> show profiles;

|       15 | 0.00046156 | select * from user u where u.email = 'kakaoemail9992' |
+----------+------------+-------------------------------------------------------+
15 rows in set (0.000 sec)

 

현재 하나의 행을 뽑기 위한 쿼리 속도는 0.00046156이다. 10배 빨라졌다.

 

 

이게 운이 아닌가? 생각할 수 있으니, 인덱스 설정 안할 때 10번, 인덱스 설정 후 10번을 쿼리문 날려본다.

일단 인덱스 다시 지운다.

 

Database changed
MariaDB [voda]> alter table user drop index kakao;
Query OK, 0 rows affected (0.015 sec)
Records: 0  Duplicates: 0  Warnings: 0

 

 

| 인덱스 없이 10번 쿼리 날리고 성능 측정 결과

MariaDB [voda]> show profiles;
+----------+------------+-------------------------------------------------------+
| Query_ID | Duration   | Query                                                 |
+----------+------------+-------------------------------------------------------+
|        1 | 0.00396043 | select * from user u where u.email = 'kakaoemail9992' |
|        2 | 0.00400779 | select * from user u where u.email = 'kakaoemail9992' |
|        3 | 0.00399760 | select * from user u where u.email = 'kakaoemail9992' |
|        4 | 0.00397338 | select * from user u where u.email = 'kakaoemail9992' |
|        5 | 0.00399458 | select * from user u where u.email = 'kakaoemail9992' |
|        6 | 0.00398010 | select * from user u where u.email = 'kakaoemail9992' |
|        7 | 0.00403243 | select * from user u where u.email = 'kakaoemail9992' |
|        8 | 0.00396560 | select * from user u where u.email = 'kakaoemail9992' |
|        9 | 0.00413708 | select * from user u where u.email = 'kakaoemail9992' |
|       10 | 0.00396227 | select * from user u where u.email = 'kakaoemail9992' |
+----------+------------+-------------------------------------------------------+
10 rows in set (0.000 sec)

 

 

| 인덱스 설정하고 10번 쿼리 날리고 성능 측정 결과

MariaDB [voda]> show profiles;
+----------+------------+-------------------------------------------------------+
| Query_ID | Duration   | Query                                                 |
+----------+------------+-------------------------------------------------------+
|        1 | 0.00084957 | select * from user u where u.email = 'kakaoemail9992' |
|        2 | 0.00050354 | select * from user u where u.email = 'kakaoemail9992' |
|        3 | 0.00049764 | select * from user u where u.email = 'kakaoemail9992' |
|        4 | 0.00047359 | select * from user u where u.email = 'kakaoemail9992' |
|        5 | 0.00045662 | select * from user u where u.email = 'kakaoemail9992' |
|        6 | 0.00048603 | select * from user u where u.email = 'kakaoemail9992' |
|        7 | 0.00049534 | select * from user u where u.email = 'kakaoemail9992' |
|        8 | 0.00046354 | select * from user u where u.email = 'kakaoemail9992' |
|        9 | 0.00047090 | select * from user u where u.email = 'kakaoemail9992' |
|       10 | 0.00051084 | select * from user u where u.email = 'kakaoemail9992' |
+----------+------------+-------------------------------------------------------+
10 rows in set (0.000 sec)

 

사용자 검색을 위해 Select 하는 쿼리문의 속도가 10배 이상 빨라진 것을 확인할 수 있다. 

 

 

MariaDB [voda]> DELETE FROM user WHERE user_seq BETWEEN 1006 AND 11006; // 추가된 데이터 삭제

 

 

| 현재 DB 인덱스 조회. 

MariaDB [voda]> show index from user;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| user  |          0 | PRIMARY  |            1 | user_seq    | A         |           6 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| user  |          1 | kakao    |            1 | email       | A         |           6 |     NULL | NULL   |      | BTREE      |         |               | NO      |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
2 rows in set (0.001 sec)

 

 

 

결론 : email 컬럼을 인덱스로 설정하였다.