[refactor] 데이터베이스 인덱스를 적용하여 이메일 컬럼 조회 성능 개선

[msung99]

🕹️ 구현할 기능

기존 데이터베이스내의 인덱스 정보

mysql> SHOW INDEXES FROM member;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| member |          0 | PRIMARY  |            1 | id          | A         |          49 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

기존 실행 계획

mysql> EXPLAIN SELECT * FROM member WHERE email = 'devhaon@kakao.com';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | member | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   55 |    10.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+

email 인덱스 추가

mysql> SHOW INDEXES FROM member;
+---------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name          | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| member |          0 | PRIMARY           |            1 | id          | A         |          49 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| member |          1 | idx_member_email |            1 | email       | A         |          55 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+---------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.00 sec)

실행 계획 확인

mysql> EXPLAIN SELECT * FROM member WHERE email = 'devhaon@kakao.com';
+----+-------------+---------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys     | key               | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | member | NULL       | ref  | idx_member_email | idx_member_email | 1022    | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+

🛠️ 상세작업내용

이상 생략

⏰ 예상소요시간

생략

📄 참고자료

No response