소셜 네트워킹 서비스를 개발하면서 SNS의 뉴스피드 기능을 만들게 되었다. 그런데 사람이 점점 늘어나면서 뉴스피드를 로딩하는 시간이 점점 길어지더니 한 페이지 로딩하는데 5초 정도 걸리기 시작했다. 데이터베이스 쿼리로 인한 속도 저하는 처음 겪어보는 일이라서 어떻게 해야할지 막막했지만 가만히 손 놓고 있을 수는 없었기 때문에 처음으로 쿼리 튜닝이라는 것을 해봤다.
먼저 디비 구조는 다음과 같다. 필요 없는 컬럼은 생략했다.
User
+----------------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
+----------------------------+--------------+------+-----+---------+----------------+
Index: id
Follow
+-------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| follower_id | int(11) | YES | | NULL | |
| followed_id | int(11) | YES | | NULL | |
+-------------+----------+------+-----+---------+----------------+
Index: id
Post
+----------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| user_id | int(11) | YES | MUL | NULL | |
| content | longtext | YES | | NULL | |
| created_at | datetime | NO | | NULL | |
+----------------+--------------+------+-----+---------+----------------+
Index: id, user_id
그리고 기존의 뉴스피드 포스트 목록을 불러오기 위한 쿼리는 다음과 같았다.
SELECT `posts`.*
FROM `posts`
WHERE
`posts`.`user_id` IN (
SELECT `users`.`id`
FROM `users`
INNER JOIN `follows`
ON `users`.`id` = `follows`.`followed_id`
WHERE `follows`.`follower_id` = 1
)
OR
`posts`.`user_id` = 1
이 쿼리는 초반에 잘 동작했지만 포스트와 팔로우의 레코드가 각각 1000단위로 쌓이자 점점 느려지기 시작했다. 그리로 현재 프로덕션 환경의 디비에는 포스트, 팔로우 각각 7000개씩 저장되어 있다.
서비스에 영향을 주면 안되기 때문에 테스트는 동일한 환경에 포스트 6000, 팔로우 4000개로 셋팅한 다른 데이터베이스에서 진행했다. 그리고 쿼리의 분석과 소요 시간을 확인하기 위해 MySQL 내장 기능인 EXPLAIN과 SHOW PROFILES를 사용했다.
테스트 환경에서 위의 쿼리는 3.7초가 소요되는 것을 SHOW PROFILES 명령어를 통해 볼 수 있다.
+----------+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | 3.78938800 | SELECT `posts`.* FROM `posts` WHERE (`posts`.`user_id` IN (SELECT `users`.`id` FROM `users` INNER JOIN `follows` ON `users`.`id` = `follows`.`followed_id` WHERE `follows`.`follower_id` = 1) OR `posts`.`user_id` = 1) |
+----------+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
다음엔 EXPLAIN으로 쿼리를 분석 해서 포스트와 팔로우 테이블을 풀스캔하는 것을 발견할 수 있었다.
+----+--------------------+---------+--------+------------------------+---------+---------+------+------+--------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+---------+--------+------------------------+---------+---------+------+------+--------------------------------+
| 1 | PRIMARY | posts | ALL | index_posts_on_user_id | NULL | NULL | NULL | 6301 | Using where |
| 2 | DEPENDENT SUBQUERY | users | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | Using index |
| 2 | DEPENDENT SUBQUERY | follows | ALL | NULL | NULL | NULL | NULL | 4801 | Using where; Using join buffer |
+----+--------------------+---------+--------+------------------------+---------+---------+------+------+--------------------------------+
먼저 팔로우의 풀스캔부터 없애보기로 했다. 팔로우 테이블이 조인되는 과정에서 발생하는 풀스캔을 없애기 위해 follows.follower_id와 follows.followed_id컬럼에 인덱스를 추가해봤다.
+----+--------------------+---------+--------+-----------------------------------------------------------+------------------------------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+---------+--------+-----------------------------------------------------------+------------------------------+---------+-------+------+--------------------------+
| 1 | PRIMARY | posts | ALL | index_posts_on_user_id | NULL | NULL | NULL | 6301 | Using where |
| 2 | DEPENDENT SUBQUERY | follows | ref | index_follows_on_follower_id,index_follows_on_followed_id | index_follows_on_follower_id | 5 | const | 1 | Using where |
| 2 | DEPENDENT SUBQUERY | users | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | Using where; Using index |
+----+--------------------+---------+--------+-----------------------------------------------------------+------------------------------+---------+-------+------+--------------------------+
“ALL” 타입에서 “ref”타입으로 변경된 것을 볼 수 있다. 이것은 정상적으로 인덱스를 탄다는 것을 뜻한다. 이렇게 2개의 풀스캔 중 1개를 없애니 실행 속도가 3.7초에서 0.03초로 엄청나게 줄어들었다.
+----------+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | 0.02776975 | SELECT `posts`.* FROM `posts` WHERE (`posts`.`user_id` IN (SELECT `users`.`id` FROM `users` INNER JOIN `follows` ON `users`.`id` = `follows`.`followed_id` WHERE `follows`.`follower_id` = 1) OR `posts`.`user_id` = 1) |
+----------+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
다음으로 포스트의 풀스캔을 없애기 위해 포스트와 팔로우를 직접 JOIN하도록 했다.
이 과정에서 애를 좀 먹었는데 처음에 구상한 쿼리는 다음과 같았다.
SELECT posts.*
FROM posts
INNER JOIN follows
ON posts.user_id = follows.followed_id
WHERE follows.follower_id = 1 OR posts.user_id = 1
쿼리 안에서 유저 테이블에 대한 참조를 없애고 포스트와 팔로우 테이블로만 표현했다. 이것으로 인해 포스트 테이블도 “ref” 타입으로 변경되었고, 팔로우 테이블은 “index_merge” 타입으로 변경되었다.
+----+-------------+---------+-------------+-----------------------------------------------------------+-----------------------------------------------------------+---------+---------------------------+------+-------------------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------------+-----------------------------------------------------------+-----------------------------------------------------------+---------+---------------------------+------+-------------------------------------------------------------------------------------+
| 1 | SIMPLE | follows | index_merge | index_follows_on_follower_id,index_follows_on_followed_id | index_follows_on_follower_id,index_follows_on_followed_id | 5,5 | NULL | 2 | Using union(index_follows_on_follower_id,index_follows_on_followed_id); Using where |
| 1 | SIMPLE | posts | ref | index_posts_on_user_id | index_posts_on_user_id | 5 | vinup.follows.followed_id | 50 | Using where |
+----+-------------+---------+-------------+-----------------------------------------------------------+-----------------------------------------------------------+---------+---------------------------+------+-------------------------------------------------------------------------------------+
하지만 위의 쿼리에는 한 가지 문제가 있었다.
뉴스피드에서는 팔로우한 유저들의 포스트 뿐만 아니라 자신의 포스트도 보여줘야 하기 때문에 WHERE 절에 posts.user_id = 1을 추가했지만, INNER JOIN의 ON 조건에 의해 자신의 포스트가 제외되어 버렸다.
그리고 이 문제는 UNION을 사용해서 WHERE 절을 2개의 쿼리로 나누는 것으로 해결했다.
SELECT posts.*
FROM posts
INNER JOIN follows
ON posts.user_id = follows.followed_id
WHERE follows.follower_id = 1
UNION
SELECT posts.*
FROM posts
WHERE posts.user_id = 1;
UNION으로 작성하니 포스트까지 “ref” 타입으로 변경되었고 실행 시간은 0.0006초로 더욱 더 빨라졌다.
EXPLAIN
+----+--------------+------------+------+-----------------------------------------------------------+------------------------------+---------+---------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+------------+------+-----------------------------------------------------------+------------------------------+---------+---------------------------+------+-------------+
| 1 | PRIMARY | follows | ref | index_follows_on_follower_id,index_follows_on_followed_id | index_follows_on_follower_id | 5 | const | 1 | Using where |
| 1 | PRIMARY | posts | ref | index_posts_on_user_id | index_posts_on_user_id | 5 | vinup.follows.followed_id | 61 | Using where |
| 2 | UNION | posts | ref | index_posts_on_user_id | index_posts_on_user_id | 5 | const | 5 | Using where |
| NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | |
+----+--------------+------------+------+-----------------------------------------------------------+------------------------------+---------+---------------------------+------+-------------+
Profile
+----------+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | 0.00066025 | SELECT posts.* FROM posts INNER JOIN follows ON posts.user_id = follows.followed_id WHERE follows.follower_id = 1 UNION SELECT posts.* FROM posts WHERE posts.user_id = 1 |
+----------+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
5초 => 0.0006초, 아주 만족스러운 작업이었다 ^ㅇ^
참고 링크