search

nyancat3 / private-isu-practice

MIT License
1 stars 0 forks source link

Solve N+1 queries with caching #13

Closed nyancat3 closed 17 hours ago

nyancat3 commented 19 hours ago

Initial score

{"pass":true,"score":80175,"success":76174,"fail":0,"messages":[]}
nyancat3 commented 18 hours ago

After applying the PR:

Some cases failed due to cache TTL

{"pass":true,"score":94550,"success":89968,"fail":2,"messages":["response code should be 200, got 500 (GET /)","response code should be 200, got 500 (GET /posts/1350)"]}

slow query log

Details

```bash # mysqldumpslow -s t /var/log/mysql/mysql-slow.log Reading mysql slow query log from /var/log/mysql/mysql-slow.log Count: 396 Time=0.03s (10s) Lock=0.00s (0s) Rows=9.9 (3914), isuconp[isuconp]@localhost SELECT `posts`.`id`, `posts`.`user_id`, `posts`.`body`, `posts`.`mime`, `posts`.`created_at`, `users`.`account_name` FROM `posts` JOIN `users` ON `posts`.`user_id` = `users`.`id` WHERE `user_id` = N AND `users`.`del_flg` = N ORDER BY `created_at` DESC LIMIT N Count: 101936 Time=0.00s (4s) Lock=0.00s (0s) Rows=7.2 (735416), 2users@localhost # Count: 24207 Time=0.00s (3s) Lock=0.00s (0s) Rows=1.0 (24207), isuconp[isuconp]@localhost SELECT * FROM `users` WHERE `id` = N Count: 8246 Time=0.00s (3s) Lock=0.00s (0s) Rows=1.0 (8246), isuconp[isuconp]@localhost SELECT * FROM `posts` WHERE `id` = N Count: 306 Time=0.01s (1s) Lock=0.00s (0s) Rows=0.0 (0), isuconp[isuconp]@localhost INSERT INTO `users` (`account_name`, `passhash`) VALUES ('S','S') Count: 256 Time=0.01s (1s) Lock=0.00s (0s) Rows=0.0 (0), isuconp[isuconp]@localhost INSERT INTO `comments` (`post_id`, `user_id`, `comment`) VALUES ('S',N,'S') Count: 396 Time=0.00s (1s) Lock=0.00s (0s) Rows=9.9 (3915), isuconp[isuconp]@localhost SELECT `id` FROM `posts` WHERE `user_id` = N Count: 4037 Time=0.00s (1s) Lock=0.00s (0s) Rows=20.0 (80740), isuconp[isuconp]@localhost SELECT `posts`.`id`, `posts`.`user_id`, `posts`.`body`, `posts`.`created_at`, `posts`.`mime`, `users`.`account_name` FROM `posts` JOIN `users` ON `posts`.`user_id` = `users`.`id` WHERE `users`.`del_flg` = N ORDER BY `created_at` DESC LIMIT N Count: 227 Time=0.01s (1s) Lock=0.00s (0s) Rows=0.0 (0), isuconp[isuconp]@localhost INSERT INTO `posts` (`user_id`, `mime`, `imgdata`, `body`) VALUES (N,'S','S','S') Count: 2839 Time=0.00s (0s) Lock=0.00s (0s) Rows=2.7 (7766), isuconp[isuconp]@localhost SELECT * FROM `comments` WHERE `post_id` = N ORDER BY `created_at` DESC LIMIT N Count: 1839 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (1839), isuconp[isuconp]@localhost SELECT `posts`.`id`, `posts`.`user_id`, `posts`.`mime`, `posts`.`imgdata`, `posts`.`body`, `posts`.`created_at`, `users`.`account_name` FROM `posts` JOIN `users` ON `posts`.`user_id` = `users`.`id` WHERE `posts`.`id` = 'S' LIMIT N Count: 2846 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (2846), isuconp[isuconp]@localhost SELECT COUNT(*) AS `count` FROM `comments` WHERE `post_id` = N Count: 1504 Time=0.00s (0s) Lock=0.00s (0s) Rows=8.6 (12961), isuconp[isuconp]@localhost SELECT * FROM `comments` WHERE `post_id` = N ORDER BY `created_at` DESC Count: 2225 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.8 (1822), isuconp[isuconp]@localhost SELECT * FROM users WHERE account_name = 'S' AND del_flg = N Count: 240 Time=0.00s (0s) Lock=0.00s (0s) Rows=20.0 (4800), isuconp[isuconp]@localhost SELECT `posts`.`id`, `posts`.`user_id`, `posts`.`body`, `posts`.`mime`, `posts`.`created_at`, `users`.`account_name` FROM `posts` JOIN `users` ON `posts`.`user_id` = `users`.`id` WHERE `posts`.`created_at` <= 'S' AND `users`.`del_flg` = N ORDER BY `created_at` DESC LIMIT N Count: 396 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (396), isuconp[isuconp]@localhost SELECT COUNT(*) AS count FROM `comments` WHERE `user_id` = N Count: 396 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (396), isuconp[isuconp]@localhost SELECT * FROM `users` WHERE `account_name` = 'S' AND `del_flg` = N Count: 64 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (64), isuconp[isuconp]@localhost SELECT COUNT(*) AS count FROM `comments` WHERE `post_id` IN (N,N,N,N,N,N,N,N,N,N,N) Count: 39 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (39), isuconp[isuconp]@localhost SELECT COUNT(*) AS count FROM `comments` WHERE `post_id` IN (N,N,N,N,N,N,N,N,N,N,N,N) Count: 53 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (53), isuconp[isuconp]@localhost SELECT COUNT(*) AS count FROM `comments` WHERE `post_id` IN (N,N,N,N,N,N,N,N,N,N) Count: 306 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), isuconp[isuconp]@localhost SELECT N FROM users WHERE `account_name` = 'S' Count: 32 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (32), isuconp[isuconp]@localhost SELECT COUNT(*) AS count FROM `comments` WHERE `post_id` IN (N,N,N,N,N,N,N,N,N,N,N,N,N) Count: 36 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (36), isuconp[isuconp]@localhost SELECT COUNT(*) AS count FROM `comments` WHERE `post_id` IN (N,N,N,N,N,N,N) Count: 42 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (42), isuconp[isuconp]@localhost SELECT COUNT(*) AS count FROM `comments` WHERE `post_id` IN (N,N,N,N,N,N,N,N,N) Count: 39 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (39), isuconp[isuconp]@localhost SELECT COUNT(*) AS count FROM `comments` WHERE `post_id` IN (N,N,N,N,N,N,N,N) Count: 1 Time=0.02s (0s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost FLUSH LOGS Count: 1 Time=0.02s (0s) Lock=0.00s (0s) Rows=0.0 (0), isuconp[isuconp]@localhost UPDATE users SET del_flg = N WHERE id % N = N Count: 28 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (28), isuconp[isuconp]@localhost SELECT COUNT(*) AS count FROM `comments` WHERE `post_id` IN (N,N,N,N,N,N) Count: 1 Time=0.01s (0s) Lock=0.00s (0s) Rows=0.0 (0), isuconp[isuconp]@localhost UPDATE users SET del_flg = N Count: 14 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (14), isuconp[isuconp]@localhost SELECT COUNT(*) AS count FROM `comments` WHERE `post_id` IN (N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N) Count: 1 Time=0.01s (0s) Lock=0.00s (0s) Rows=0.0 (0), isuconp[isuconp]@localhost DELETE FROM users WHERE id > N Count: 1 Time=0.01s (0s) Lock=0.00s (0s) Rows=0.0 (0), isuconp[isuconp]@localhost DELETE FROM comments WHERE id > N Count: 1 Time=0.01s (0s) Lock=0.00s (0s) Rows=0.0 (0), isuconp[isuconp]@localhost DELETE FROM posts WHERE id > N Count: 11 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (11), isuconp[isuconp]@localhost SELECT COUNT(*) AS count FROM `comments` WHERE `post_id` IN (N,N,N,N,N,N,N,N,N,N,N,N,N,N) Count: 13 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (13), isuconp[isuconp]@localhost SELECT COUNT(*) AS count FROM `comments` WHERE `post_id` IN (N,N,N,N) Count: 6 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (6), isuconp[isuconp]@localhost SELECT COUNT(*) AS count FROM `comments` WHERE `post_id` IN (N,N,N,N,N,N,N,N,N,N,N,N,N,N,N) Count: 9 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (9), isuconp[isuconp]@localhost SELECT COUNT(*) AS count FROM `comments` WHERE `post_id` IN (N,N,N,N,N) Count: 3 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (3), isuconp[isuconp]@localhost SELECT COUNT(*) AS count FROM `comments` WHERE `post_id` IN (N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N) Count: 4 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (4), isuconp[isuconp]@localhost SELECT COUNT(*) AS count FROM `comments` WHERE `post_id` IN (N,N,N) Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (1), isuconp[isuconp]@localhost SELECT COUNT(*) AS count FROM `comments` WHERE `post_id` IN (N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N) Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (1), isuconp[isuconp]@localhost SELECT COUNT(*) AS count FROM `comments` WHERE `post_id` IN (N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N) Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (1), isuconp[isuconp]@localhost SELECT COUNT(*) AS count FROM `comments` WHERE `post_id` IN (N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N) Count: 51063 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), 0users@0hosts administrator command: Prepare Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), 0users@0hosts administrator command: Quit Count: 50872 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), 0users@0hosts administrator command: Close stmt ```

nyancat3 commented 17 hours ago

After removing TTL

{"pass":true,"score":93288,"success":88677,"fail":0,"messages":[]}

slow query log

Details

```bash # mysqldumpslow -s t /var/log/mysql/mysql-slow.log Reading mysql slow query log from /var/log/mysql/mysql-slow.log Count: 399 Time=0.03s (10s) Lock=0.00s (0s) Rows=10.2 (4074), isuconp[isuconp]@localhost SELECT `posts`.`id`, `posts`.`user_id`, `posts`.`body`, `posts`.`mime`, `posts`.`created_at`, `users`.`account_name` FROM `posts` JOIN `users` ON `posts`.`user_id` = `users`.`id` WHERE `user_id` = N AND `users`.`del_flg` = N ORDER BY `created_at` DESC LIMIT N Count: 83338 Time=0.00s (4s) Lock=0.00s (0s) Rows=7.4 (620169), 2users@localhost # Count: 8264 Time=0.00s (3s) Lock=0.00s (0s) Rows=1.0 (8264), isuconp[isuconp]@localhost SELECT * FROM `posts` WHERE `id` = N Count: 17361 Time=0.00s (2s) Lock=0.00s (0s) Rows=1.0 (17361), isuconp[isuconp]@localhost SELECT * FROM `users` WHERE `id` = N Count: 302 Time=0.01s (1s) Lock=0.00s (0s) Rows=0.0 (0), isuconp[isuconp]@localhost INSERT INTO `users` (`account_name`, `passhash`) VALUES ('S','S') Count: 265 Time=0.01s (1s) Lock=0.00s (0s) Rows=0.0 (0), isuconp[isuconp]@localhost INSERT INTO `comments` (`post_id`, `user_id`, `comment`) VALUES ('S',N,'S') Count: 399 Time=0.00s (1s) Lock=0.00s (0s) Rows=10.2 (4076), isuconp[isuconp]@localhost SELECT `id` FROM `posts` WHERE `user_id` = N Count: 3982 Time=0.00s (1s) Lock=0.00s (0s) Rows=20.0 (79640), isuconp[isuconp]@localhost SELECT `posts`.`id`, `posts`.`user_id`, `posts`.`body`, `posts`.`created_at`, `posts`.`mime`, `users`.`account_name` FROM `posts` JOIN `users` ON `posts`.`user_id` = `users`.`id` WHERE `users`.`del_flg` = N ORDER BY `created_at` DESC LIMIT N Count: 228 Time=0.01s (1s) Lock=0.00s (0s) Rows=0.0 (0), isuconp[isuconp]@localhost INSERT INTO `posts` (`user_id`, `mime`, `imgdata`, `body`) VALUES (N,'S','S','S') Count: 1873 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (1873), isuconp[isuconp]@localhost SELECT `posts`.`id`, `posts`.`user_id`, `posts`.`mime`, `posts`.`imgdata`, `posts`.`body`, `posts`.`created_at`, `users`.`account_name` FROM `posts` JOIN `users` ON `posts`.`user_id` = `users`.`id` WHERE `posts`.`id` = 'S' LIMIT N Count: 1724 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (1724), isuconp[isuconp]@localhost SELECT COUNT(*) AS `count` FROM `comments` WHERE `post_id` = N Count: 2220 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.8 (1821), isuconp[isuconp]@localhost SELECT * FROM users WHERE account_name = 'S' AND del_flg = N Count: 1720 Time=0.00s (0s) Lock=0.00s (0s) Rows=2.6 (4401), isuconp[isuconp]@localhost SELECT * FROM `comments` WHERE `post_id` = N ORDER BY `created_at` DESC LIMIT N Count: 1171 Time=0.00s (0s) Lock=0.00s (0s) Rows=8.1 (9455), isuconp[isuconp]@localhost SELECT * FROM `comments` WHERE `post_id` = N ORDER BY `created_at` DESC Count: 240 Time=0.00s (0s) Lock=0.00s (0s) Rows=20.0 (4800), isuconp[isuconp]@localhost SELECT `posts`.`id`, `posts`.`user_id`, `posts`.`body`, `posts`.`mime`, `posts`.`created_at`, `users`.`account_name` FROM `posts` JOIN `users` ON `posts`.`user_id` = `users`.`id` WHERE `posts`.`created_at` <= 'S' AND `users`.`del_flg` = N ORDER BY `created_at` DESC LIMIT N Count: 399 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (399), isuconp[isuconp]@localhost SELECT COUNT(*) AS count FROM `comments` WHERE `user_id` = N Count: 399 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (399), isuconp[isuconp]@localhost SELECT * FROM `users` WHERE `account_name` = 'S' AND `del_flg` = N Count: 59 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (59), isuconp[isuconp]@localhost SELECT COUNT(*) AS count FROM `comments` WHERE `post_id` IN (N,N,N,N,N,N,N,N,N,N) Count: 49 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (49), isuconp[isuconp]@localhost SELECT COUNT(*) AS count FROM `comments` WHERE `post_id` IN (N,N,N,N,N,N,N,N,N,N,N) Count: 302 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), isuconp[isuconp]@localhost SELECT N FROM users WHERE `account_name` = 'S' Count: 48 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (48), isuconp[isuconp]@localhost SELECT COUNT(*) AS count FROM `comments` WHERE `post_id` IN (N,N,N,N,N,N,N,N,N) Count: 41 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (41), isuconp[isuconp]@localhost SELECT COUNT(*) AS count FROM `comments` WHERE `post_id` IN (N,N,N,N,N,N,N,N,N,N,N,N) Count: 38 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (38), isuconp[isuconp]@localhost SELECT COUNT(*) AS count FROM `comments` WHERE `post_id` IN (N,N,N,N,N,N,N,N,N,N,N,N,N) Count: 47 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (47), isuconp[isuconp]@localhost SELECT COUNT(*) AS count FROM `comments` WHERE `post_id` IN (N,N,N,N,N,N,N,N) Count: 1 Time=0.03s (0s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost FLUSH LOGS Count: 37 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (37), isuconp[isuconp]@localhost SELECT COUNT(*) AS count FROM `comments` WHERE `post_id` IN (N,N,N,N,N,N,N) Count: 20 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (20), isuconp[isuconp]@localhost SELECT COUNT(*) AS count FROM `comments` WHERE `post_id` IN (N,N,N,N,N,N,N,N,N,N,N,N,N,N) Count: 10 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (10), isuconp[isuconp]@localhost SELECT COUNT(*) AS count FROM `comments` WHERE `post_id` IN (N,N,N,N,N,N,N,N,N,N,N,N,N,N,N) Count: 1 Time=0.01s (0s) Lock=0.00s (0s) Rows=0.0 (0), isuconp[isuconp]@localhost DELETE FROM users WHERE id > N Count: 1 Time=0.01s (0s) Lock=0.00s (0s) Rows=0.0 (0), isuconp[isuconp]@localhost DELETE FROM comments WHERE id > N Count: 1 Time=0.01s (0s) Lock=0.00s (0s) Rows=0.0 (0), isuconp[isuconp]@localhost UPDATE users SET del_flg = N WHERE id % N = N Count: 1 Time=0.01s (0s) Lock=0.00s (0s) Rows=0.0 (0), isuconp[isuconp]@localhost DELETE FROM posts WHERE id > N Count: 11 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (11), isuconp[isuconp]@localhost SELECT COUNT(*) AS count FROM `comments` WHERE `post_id` IN (N,N,N,N,N,N) Count: 1 Time=0.01s (0s) Lock=0.00s (0s) Rows=0.0 (0), isuconp[isuconp]@localhost UPDATE users SET del_flg = N Count: 13 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (13), isuconp[isuconp]@localhost SELECT COUNT(*) AS count FROM `comments` WHERE `post_id` IN (N,N,N,N,N) Count: 6 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (6), isuconp[isuconp]@localhost SELECT COUNT(*) AS count FROM `comments` WHERE `post_id` IN (N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N) Count: 2 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (2), isuconp[isuconp]@localhost SELECT COUNT(*) AS count FROM `comments` WHERE `post_id` IN (N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N) Count: 6 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (6), isuconp[isuconp]@localhost SELECT COUNT(*) AS count FROM `comments` WHERE `post_id` IN (N,N,N,N) Count: 5 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (5), isuconp[isuconp]@localhost SELECT COUNT(*) AS count FROM `comments` WHERE `post_id` IN (N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N) Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (1), isuconp[isuconp]@localhost SELECT COUNT(*) AS count FROM `comments` WHERE `post_id` IN (N,N,N) Count: 2 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (2), isuconp[isuconp]@localhost SELECT COUNT(*) AS count FROM `comments` WHERE `post_id` IN (N,N) Count: 2 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (2), isuconp[isuconp]@localhost SELECT COUNT(*) AS count FROM `comments` WHERE `post_id` IN (N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N) Count: 2 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (2), isuconp[isuconp]@localhost SELECT COUNT(*) AS count FROM `comments` WHERE `post_id` IN (N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N) Count: 41652 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), 0users@0hosts administrator command: Prepare Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), 0users@0hosts administrator command: Quit Count: 41685 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), 0users@0hosts administrator command: Close stmt ```