요약

핵심 아이디어 한 줄 요약
“이 노트는 왜 중요한가?” → 맥락 설명

본문

pt-query-digest는 슬로우 쿼리 분석을 자동으로 해주는 툴로 Percona Toolkit안에 포함된 프로그램 중 하나다.
비슷한 쿼리를 뽑아서 아래의 통계 정보에 대해 정리해준다.
- 총 실행시간
- 전체 소요 시간 합계
- 평균 시간
- p95/p99 실행시간
- rows examined
- rows sent
- 쿼리 예시문

pt-query-digest slow.log > report.txt
설치 방법

brew install percona-toolkit

실행

pt-query-digest mysql-slowquery.log.2025-11-19.2 > report.txt

결과 예시


# 360ms user time, 60ms system time, 38.61M rss, 392.26G vsz
# Current date: Wed Nov 19 12:08:24 2025
# Hostname: bagjunhaui-MacBookPro.local
# Files: mysql-slowquery.log.2025-11-19.2
# Overall: 1.12k total, 23 unique, 0.31 QPS, 0.78x concurrency ___________
# Time range: 2025-11-19T01:00:02 to 2025-11-19T01:59:59
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time          2808s      1s     20s      3s      5s      2s      2s
# Lock time            3ms     1us    28us     2us     4us     1us     2us
# Rows sent        263.23k       0   6.54k  240.02   2.06k  604.40       0
# Rows examine     741.37M       0   2.71M 676.01k   2.62M   1.11M  42.34k
# Query size         3.38M     249  80.42k   3.08k  17.59k   7.79k   1.14k

# Profile
# Rank Query ID                            Response time   Calls R/Call V/
# ==== =================================== =============== ===== ====== ==
#    1 0xA7B83C2E4DEE831432610BAE46D363CA  1267.2396 45.1%   422 3.0029  0.28 SELECT account_user account_country account_useronboardingstatus
#    2 0xCFD833D98D9A7956A09B2ED1F68661ED   649.2635 23.1%   215 3.0198  1.59 SELECT swipe_swipelikehistory swipe_swipechoice account_user
#    3 0x5CC87B9F20387C63FB4174E11A6FD86A   304.0404 10.8%   191 1.5918  0.05 SELECT account_user account_useronboardingstatus report_usercontrol report_usercontroltype swipe_swipescore account_user
#    4 0x739F6212C74528C1F979C77174F117AC   252.1903  9.0%   112 2.2517  1.84 SELECT swipe_swipechoice account_user
#    5 0x0C9E313F2258E7F9939DD305CDC66A52    78.7649  2.8%    31 2.5408  1.76 SELECT swipe_swipelikehistory swipe_swipechoice account_user
#    6 0xD9C316AA9E19D196B6C25050FB8EA22A    57.7913  2.1%    28 2.0640  0.49 SELECT monitoring_matchinglog account_user account_country account_user account_country
#    7 0x14C16328F5978BFCF66156117DFB9745    28.9717  1.0%    24 1.2072  0.05 SELECT account_user account_useronboardingstatus report_usercontrol report_usercontroltype
#    8 0x247A59B45D532EA9989A2AE06A3539EF    28.0928  1.0%    22 1.2769  0.07 SELECT account_user account_useronboardingstatus report_usercontrol report_usercontroltype swipe_swipescore account_user
#    9 0x4763EBBBBB9F101A5DDF7038151FEDF6    27.3412  1.0%    15 1.8227  0.36 SELECT swipe_swipechoice
#   10 0x4F71BF848445A6F59F91DA11519C8194    24.1943  0.9%    20 1.2097  0.04 SELECT account_user account_useronboardingstatus report_usercontrol report_usercontroltype swipe_swipescore account_user swipe_swipescore account_user swipe_swipescore account_user
#   12 0xCE4AF66A6BFF8AC7B7A32ED2C69F3D53    18.8127  0.7%    12 1.5677  0.28 SELECT account_user account_useronboardingstatus report_usercontrol report_usercontroltype swipe_swipescore account_user
# MISC 0xMISC                                70.8119  2.5%    31 2.2843   0.0 <12 ITEMS>

# Query 1: 0.14 QPS, 0.43x concurrency, ID 0xA7B83C2E4DEE831432610BAE46D363CA at byte 352967
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.28
# Time range: 2025-11-19T01:11:15 to 2025-11-19T01:59:59
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         37     422
# Exec time     45   1267s      2s     20s      3s      4s   910ms      3s
# Lock time     31     1ms     1us    10us     2us     2us       0     1us
# Rows sent      0     149       0      30    0.35       0    2.83       0
# Rows examine  96 713.71M  22.37k   2.64M   1.69M   2.62M   1.24M   2.62M
# Query size    14 498.42k   1.17k   1.20k   1.18k   1.14k       0   1.14k
# String:
# Databases    connecting
# Hosts        172.31.4.167 (73/17%), 172.31.9.199 (72/17%)... 6 more
# Users        django
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s  ################################################################
#  10s+  #
# Tables
#    SHOW TABLE STATUS FROM `connecting` LIKE 'account_user'\G
#    SHOW CREATE TABLE `connecting`.`account_user`\G
#    SHOW TABLE STATUS FROM `connecting` LIKE 'account_country'\G
#    SHOW CREATE TABLE `connecting`.`account_country`\G
#    SHOW TABLE STATUS FROM `connecting` LIKE 'account_useronboardingstatus'\G
#    SHOW CREATE TABLE `connecting`.`account_useronboardingstatus`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT `account_user`.`id`, `account_user`.`email`, `account_user`.`nickname`, `account_user`.`sex`, `account_user`.`birthday`, `account_user`.`representative_image_id`, `account_user`.`cash`, `account_user`.`reentrance`, `account_user`.`approval`, `account_user`.`profile_level`, `account_user`.`date_joined`, `account_user`.`uuid`, `account_user`.`country_id`, `account_user`.`account_type`, `account_country`.`id`, `account_country`.`eng_short_name`, `account_country`.`country_name`, `account_country`.`alpha_code`, `account_country`.`calling_code`, `account_country`.`language_code`, `account_country`.`country_group_id` FROM `account_user` INNER JOIN `account_country` ON (`account_user`.`country_id` = `account_country`.`id`) INNER JOIN `account_useronboardingstatus` ON (`account_user`.`id` = `account_useronboardingstatus`.`user_id`) WHERE (`account_user`.`country_id` IN (25, 24, 26, 31, 28, 32, 27, 23) AND `account_useronboardingstatus`.`status` IN ('ONBOARDING_CALL', 'COMPLETED') AND NOT (`account_user`.`account_type` = 5) AND `account_user`.`approval` = 0 AND `account_user`.`is_delete` = 0 AND `account_user`.`black_type_id` IS NULL AND `account_user`.`black_server` = 0) ORDER BY `account_user`.`id` ASC LIMIT 30\G

# Query 2: 0.06 QPS, 0.18x concurrency, ID 0xCFD833D98D9A7956A09B2ED1F68661ED at byte 417579
# This item is included in the report because it matches --limit.
# Scores: V/M = 1.59
# Time range: 2025-11-19T01:00:02 to 2025-11-19T01:59:59
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         19     215
# Exec time     23    649s      1s     14s      3s      7s      2s      2s
# Lock time     17   589us     2us    13us     2us     3us       0     2us
# Rows sent      0     215       1       1       1       1       0       1
# Rows examine   0   3.22M   3.10k  54.08k  15.35k  33.17k   9.60k  11.91k
# Query size     3 125.96k     598     600  599.90  592.07    0.00  592.07
# String:
# Databases    connecting
# Hosts        172.31.11.56 (45/20%), 172.31.14.244 (38/17%)... 7 more
# Users        django
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s  ################################################################
#  10s+  #
# Tables
#    SHOW TABLE STATUS FROM `connecting` LIKE 'swipe_swipelikehistory'\G
#    SHOW CREATE TABLE `connecting`.`swipe_swipelikehistory`\G
#    SHOW TABLE STATUS FROM `connecting` LIKE 'swipe_swipechoice'\G
#    SHOW CREATE TABLE `connecting`.`swipe_swipechoice`\G
#    SHOW TABLE STATUS FROM `connecting` LIKE 'account_user'\G
#    SHOW CREATE TABLE `connecting`.`account_user`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT COUNT(*) AS `__count` FROM `swipe_swipelikehistory` INNER JOIN `swipe_swipechoice` ON (`swipe_swipelikehistory`.`swipe_choice_id` = `swipe_swipechoice`.`id`) INNER JOIN `account_user` ON (`swipe_swipechoice`.`user_id` = `account_user`.`id`) WHERE (`swipe_swipelikehistory`.`is_visible` = 1 AND `account_user`.`is_delete` = 0 AND `account_user`.`black_server` = 0 AND `swipe_swipelikehistory`.`created_at` >= '2025-11-12 01:15:56.335336' AND `swipe_swipechoice`.`created_at` >= '2025-11-12 01:15:56.335336' AND `swipe_swipechoice`.`choice` = 1 AND `swipe_swipechoice`.`targetUser_id` = 2382292)\G

# Query 3: 0.05 QPS, 0.08x concurrency, ID 0x5CC87B9F20387C63FB4174E11A6FD86A at byte 1290056
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.05
# Time range: 2025-11-19T01:00:05 to 2025-11-19T01:59:48
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         17     191
# Exec time     10    304s      1s      3s      2s      2s   292ms      1s
# Lock time     22   768us     3us    23us     4us     4us     1us     3us
# Rows sent     75 199.85k     138   2.08k   1.05k   2.06k  739.54  537.02
# Rows examine   2  14.89M  56.59k  92.66k  79.85k  92.42k  10.28k  72.41k
# Query size     8 288.97k   1.50k   1.52k   1.51k   1.46k       0   1.46k
# String:
# Databases    connecting
# Hosts        172.31.14.244 (37/19%), 172.31.9.199 (33/17%)... 7 more
# Users        django
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s  ################################################################
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `connecting` LIKE 'account_user'\G
#    SHOW CREATE TABLE `connecting`.`account_user`\G
#    SHOW TABLE STATUS FROM `connecting` LIKE 'account_useronboardingstatus'\G
#    SHOW CREATE TABLE `connecting`.`account_useronboardingstatus`\G
#    SHOW TABLE STATUS FROM `connecting` LIKE 'report_usercontrol'\G
#    SHOW CREATE TABLE `connecting`.`report_usercontrol`\G
#    SHOW TABLE STATUS FROM `connecting` LIKE 'report_usercontroltype'\G
#    SHOW CREATE TABLE `connecting`.`report_usercontroltype`\G
#    SHOW TABLE STATUS FROM `connecting` LIKE 'swipe_swipescore'\G
#    SHOW CREATE TABLE `connecting`.`swipe_swipescore`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT `account_user`.`id`, `account_user`.`actived_at` FROM `account_user` INNER JOIN `account_useronboardingstatus` ON (`account_user`.`id` = `account_useronboardingstatus`.`user_id`) WHERE (`account_user`.`approval` IN (0, 1, 3, 4) AND `account_user`.`black_server` = 0 AND NOT EXISTS(SELECT 1 AS `a` FROM `report_usercontrol` U0 INNER JOIN `report_usercontroltype` U1 ON (U0.`control_type_id` = U1.`id`) WHERE (U1.`type` IN ('PAUSE', 'BANNED') AND U0.`is_active` = 1 AND U0.`user_id` = (`account_user`.`id`)) LIMIT 1) AND `account_user`.`is_delete` = 0 AND `account_useronboardingstatus`.`status` = 'COMPLETED' AND NOT (`account_user`.`account_type` = 5) AND `account_user`.`birthday` <= '2008-11-19' AND `account_user`.`birthday` >= '1925-11-19' AND `account_user`.`sex` IN ('M') AND `account_user`.`actived_at` >= '2025-11-04 01:29:55.502356' AND `account_user`.`country_id` IN (24, 1, 2, 23, 26, 31, 29) AND `account_user`.`global_mode_type` IN (2, 3, 4) AND NOT (`account_user`.`ab_test_flag_1` IN (1, 11) AND `account_user`.`country_id` = 1 AND `account_user`.`country_id` IS NOT NULL AND `account_user`.`global_mode_type` = 2 AND `account_user`.`sex` = 'F') AND (SELECT U0.`score` FROM `swipe_swipescore` U0 INNER JOIN `account_user` U1 ON (U0.`user_id` = U1.`id`) WHERE (U0.`accum_count` >= 10 AND U0.`height_level` = 1 AND U0.`title` = 20 AND U0.`user_id` = (`account_user`.`id`) AND U0.`score` >= (COALESCE(CASE WHEN (U1.`sex` = 'M') THEN 0.3e0 WHEN (U1.`sex` = 'F') THEN 0.35e0 ELSE 0.3e0 END, 0.3e0, 0.35e0))) LIMIT 1) IS NOT NULL)\G

# Query 4: 0.03 QPS, 0.07x concurrency, ID 0x739F6212C74528C1F979C77174F117AC at byte 41136
# This item is included in the report because it matches --limit.
# Scores: V/M = 1.84
# Time range: 2025-11-19T01:00:38 to 2025-11-19T01:59:53
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          9     112
# Exec time      8    252s      1s     10s      2s      5s      2s      1s
# Lock time      7   260us     2us     4us     2us     2us       0     1us
# Rows sent      0      27       0       3    0.24    0.99    0.56       0
# Rows examine   0   1.21M   2.82k  44.81k  11.10k  23.58k   8.79k   7.31k
# Query size    10 362.79k   3.24k   3.24k   3.24k   3.19k       0   3.19k
# String:
# Databases    connecting
# Hosts        172.31.14.244 (27/24%), 172.31.4.167 (24/21%)... 7 more
# Users        django
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s  ################################################################
#  10s+  #
# Tables
#    SHOW TABLE STATUS FROM `connecting` LIKE 'swipe_swipechoice'\G
#    SHOW CREATE TABLE `connecting`.`swipe_swipechoice`\G
#    SHOW TABLE STATUS FROM `connecting` LIKE 'account_user'\G
#    SHOW CREATE TABLE `connecting`.`account_user`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT `swipe_swipechoice`.`id`, `swipe_swipechoice`.`created_at`, `swipe_swipechoice`.`updated_at`, `swipe_swipechoice`.`user_id`, `swipe_swipechoice`.`targetUser_id`, `swipe_swipechoice`.`choice`, `swipe_swipechoice`.`undoed_at`, `swipe_swipechoice`.`olap`, T3.`id`, T3.`password`, T3.`last_login`, T3.`is_superuser`, T3.`email`, T3.`sendbird_access_token`, T3.`nickname`, T3.`login_type`, T3.`sex`, T3.`birthday`, T3.`region_id`, T3.`phone_id`, T3.`representative_image_id`, T3.`latest_attendance`, T3.`actived_at`, T3.`attendance_num`, T3.`cash`, T3.`free_ticket`, T3.`tester`, T3.`black_type_id`, T3.`inapproval_type_id`, T3.`reentrance`, T3.`black_server`, T3.`banned_at`, T3.`purchase_date_time`, T3.`purchase_accumulate`, T3.`get_like_accumulate`, T3.`fcm_token`, T3.`approval`, T3.`profile_level`, T3.`profile_rank`, T3.`is_delete`, T3.`is_sendbird_user`, T3.`is_sendbird_user_at`, T3.`is_active_search`, T3.`is_active`, T3.`is_internal_developer`, T3.`is_staff`, T3.`date_joined`, T3.`salt`, T3.`referrer_code`, T3.`os_info`, T3.`approved_time`, T3.`uuid`, T3.`country_id`, T3.`advertising_id`, T3.`is_manager`, T3.`user_test_group_id`, T3.`account_type`, T3.`global_mode_type`, T3.`ab_test_flag_1`, T3.`ab_test_flag_2`, T3.`deleted_at`, T3.`user_group_id`, `account_user`.`id`, `account_user`.`password`, `account_user`.`last_login`, `account_user`.`is_superuser`, `account_user`.`email`, `account_user`.`sendbird_access_token`, `account_user`.`nickname`, `account_user`.`login_type`, `account_user`.`sex`, `account_user`.`birthday`, `account_user`.`region_id`, `account_user`.`phone_id`, `account_user`.`representative_image_id`, `account_user`.`latest_attendance`, `account_user`.`actived_at`, `account_user`.`attendance_num`, `account_user`.`cash`, `account_user`.`free_ticket`, `account_user`.`tester`, `account_user`.`black_type_id`, `account_user`.`inapproval_type_id`, `account_user`.`reentrance`, `account_user`.`black_server`, `account_user`.`banned_at`, `account_user`.`purchase_date_time`, `account_user`.`purchase_accumulate`, `account_user`.`get_like_accumulate`, `account_user`.`fcm_token`, `account_user`.`approval`, `account_user`.`profile_level`, `account_user`.`profile_rank`, `account_user`.`is_delete`, `account_user`.`is_sendbird_user`, `account_user`.`is_sendbird_user_at`, `account_user`.`is_active_search`, `account_user`.`is_active`, `account_user`.`is_internal_developer`, `account_user`.`is_staff`, `account_user`.`date_joined`, `account_user`.`salt`, `account_user`.`referrer_code`, `account_user`.`os_info`, `account_user`.`approved_time`, `account_user`.`uuid`, `account_user`.`country_id`, `account_user`.`advertising_id`, `account_user`.`is_manager`, `account_user`.`user_test_group_id`, `account_user`.`account_type`, `account_user`.`global_mode_type`, `account_user`.`ab_test_flag_1`, `account_user`.`ab_test_flag_2`, `account_user`.`deleted_at`, `account_user`.`user_group_id` FROM `swipe_swipechoice` INNER JOIN `account_user` ON (`swipe_swipechoice`.`targetUser_id` = `account_user`.`id`) LEFT OUTER JOIN `account_user` T3 ON (`swipe_swipechoice`.`user_id` = T3.`id`) WHERE (`swipe_swipechoice`.`created_at` >= '2025-11-12 01:02:14.263066' AND `swipe_swipechoice`.`choice` = 2 AND `swipe_swipechoice`.`targetUser_id` IN (2300673)) ORDER BY `swipe_swipechoice`.`id` DESC\G

# Query 5: 0.01 QPS, 0.02x concurrency, ID 0x0C9E313F2258E7F9939DD305CDC66A52 at byte 33012
# This item is included in the report because it matches --limit.
# Scores: V/M = 1.76
# Time range: 2025-11-19T01:00:38 to 2025-11-19T01:59:53
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          2      31
# Exec time      2     79s      1s     10s      3s      5s      2s      2s
# Lock time      2    77us     2us     4us     2us     2us       0     1us
# Rows sent      0   2.32k       0     642   76.52  271.23  133.37    7.70
# Rows examine   0 392.94k   3.42k  45.94k  12.68k  24.75k   9.19k   8.46k
# Query size     0  26.49k     873     875  874.87  874.75       1  874.75
# String:
# Databases    connecting
# Hosts        172.31.11.14 (5/16%), 172.31.1.128 (4/12%)... 7 more
# Users        django
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s  ################################################################
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `connecting` LIKE 'swipe_swipelikehistory'\G
#    SHOW CREATE TABLE `connecting`.`swipe_swipelikehistory`\G
#    SHOW TABLE STATUS FROM `connecting` LIKE 'swipe_swipechoice'\G
#    SHOW CREATE TABLE `connecting`.`swipe_swipechoice`\G
#    SHOW TABLE STATUS FROM `connecting` LIKE 'account_user'\G
#    SHOW CREATE TABLE `connecting`.`account_user`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT `swipe_swipelikehistory`.`id`, `swipe_swipelikehistory`.`created_at`, `swipe_swipelikehistory`.`updated_at`, `swipe_swipelikehistory`.`swipe_choice_id`, `swipe_swipelikehistory`.`is_visible`, `swipe_swipelikehistory`.`is_visible_updated_at`, `swipe_swipelikehistory`.`targetUser_viewed_at` FROM `swipe_swipelikehistory` INNER JOIN `swipe_swipechoice` ON (`swipe_swipelikehistory`.`swipe_choice_id` = `swipe_swipechoice`.`id`) INNER JOIN `account_user` ON (`swipe_swipechoice`.`user_id` = `account_user`.`id`) WHERE (`swipe_swipelikehistory`.`is_visible` = 1 AND `account_user`.`is_delete` = 0 AND `account_user`.`black_server` = 0 AND `swipe_swipelikehistory`.`created_at` >= '2025-11-12 01:02:14.519654' AND `swipe_swipechoice`.`created_at` >= '2025-11-12 01:02:14.519654' AND `swipe_swipechoice`.`targetUser_id` = 2300673) ORDER BY `swipe_swipelikehistory`.`id` DESC\G

# Query 6: 0.01 QPS, 0.02x concurrency, ID 0xD9C316AA9E19D196B6C25050FB8EA22A at byte 31290
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.49
# Time range: 2025-11-19T01:02:12 to 2025-11-19T01:58:09
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          2      28
# Exec time      2     58s      1s      5s      2s      3s      1s      2s
# Lock time      2    87us     2us     5us     3us     3us       0     2us
# Rows sent      2   5.84k      27     764  213.43  537.02  199.25  158.58
# Rows examine   0  23.34k     108   2.98k  853.71   2.06k  779.12  621.67
# Query size     0  19.52k     709     715     714  685.39       0  685.39
# String:
# Databases    connecting
# Hosts        172.31.160.203 (17/60%), 172.31.159.177 (9/32%)... 1 more
# Users        matching
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s  ################################################################
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `connecting` LIKE 'monitoring_matchinglog'\G
#    SHOW CREATE TABLE `connecting`.`monitoring_matchinglog`\G
#    SHOW TABLE STATUS FROM `connecting` LIKE 'account_user'\G
#    SHOW CREATE TABLE `connecting`.`account_user`\G
#    SHOW TABLE STATUS FROM `connecting` LIKE 'account_country'\G
#    SHOW CREATE TABLE `connecting`.`account_country`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT user_email, targetUser_email, user_id, targetUser_id, created_at, conversation_started_at, user_disconnect_at, targetUser_disconnect_at, user_country.alpha_code AS user_country, target_user_country.alpha_code AS targetUser_country
      FROM monitoring_matchinglog mm
      JOIN account_user user_au ON mm.user_id = user_au.id
      LEFT JOIN account_country user_country ON user_au.country_id = user_country.id
      JOIN account_user target_user_au ON mm.targetUser_id = target_user_au.id
      LEFT JOIN account_country target_user_country ON target_user_au.country_id = target_user_country.id
      WHERE 
      (user_id = 1031 OR targetUser_id = 1031)
      AND created_at >= NOW()-INTERVAL 30 DAY\G

# Query 7: 0.01 QPS, 0.01x concurrency, ID 0x14C16328F5978BFCF66156117DFB9745 at byte 1526389
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.05
# Time range: 2025-11-19T01:04:07 to 2025-11-19T01:55:53
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          2      24
# Exec time      1     29s      1s      2s      1s      2s   243ms      1s
# Lock time      2    96us     2us     6us     4us     4us       0     3us
# Rows sent      0      13       0      11    0.54       0    2.18       0
# Rows examine   0     152       2      24    6.33    5.75    3.82    5.75
# Query size    24 848.42k  10.93k  79.31k  35.35k  76.03k  17.81k  30.09k
# String:
# Databases    connecting
# Hosts        172.31.8.145 (7/29%), 172.31.14.244 (5/20%)... 5 more
# Users        django
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s  ################################################################
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `connecting` LIKE 'account_user'\G
#    SHOW CREATE TABLE `connecting`.`account_user`\G
#    SHOW TABLE STATUS FROM `connecting` LIKE 'account_useronboardingstatus'\G
#    SHOW CREATE TABLE `connecting`.`account_useronboardingstatus`\G
#    SHOW TABLE STATUS FROM `connecting` LIKE 'report_usercontrol'\G
#    SHOW CREATE TABLE `connecting`.`report_usercontrol`\G
#    SHOW TABLE STATUS FROM `connecting` LIKE 'report_usercontroltype'\G
#    SHOW CREATE TABLE `connecting`.`report_usercontroltype`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT `account_user`.`id`, `account_user`.`actived_at` FROM `account_user` INNER JOIN `account_useronboardingstatus` ON (`account_user`.`id` = `account_useronboardingstatus`.`user_id`) WHERE (`account_user`.`approval` IN (0, 1, 3, 4) AND `account_user`.`black_server` = 0 AND NOT EXISTS(SELECT 1 AS `a` FROM `report_usercontrol` U0 INNER JOIN `report_usercontroltype` U1 ON (U0.`control_type_id` = U1.`id`) WHERE (U1.`type` IN ('PAUSE', 'BANNED') AND U0.`is_active` = 1 AND U0.`user_id` = (`account_user`.`id`)) LIMIT 1) AND `account_user`.`is_delete` = 0 AND `account_useronboardingstatus`.`status` = 'COMPLETED' AND NOT (`account_user`.`account_type` = 5) AND `account_user`.`birthday` <= '2005-11-19' AND `account_user`.`birthday` >= '1925-11-19' AND `account_user`.`sex` IN ('F') AND `account_user`.`actived_at` >= '2025-11-17 01:30:17.687817' AND `account_user`.`country_id` IN (25) AND `account_user`.`global_mode_type` IN (2, 3, 4) AND NOT (`account_user`.`ab_test_flag_1` IN (1, 11) AND `account_user`.`country_id` = 1 AND `account_user`.`country_id` IS NOT NULL AND `account_user`.`global_mode_type` = 2 AND `account_user`.`sex` = 'F') AND NOT (`account_user`.`id` IN (2736134, 65545, 2547730, 2752533, 2744342, 2744343, 2752538, 2744346, 2662429, 2621478, 2711592, 1425451, 2752555, 2744373, 466997, 2105402, 2752572, 2728001, 2457666, 2588739/*... omitted 3380 items ...*/)) AND `account_user`.`date_joined` > '2025-11-17 01:30:17.699722') ORDER BY `account_user`.`actived_at` DESC\G

# Query 8: 0.01 QPS, 0.01x concurrency, ID 0x247A59B45D532EA9989A2AE06A3539EF at byte 1500363
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.07
# Time range: 2025-11-19T01:16:11 to 2025-11-19T01:51:22
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          1      22
# Exec time      1     28s      1s      2s      1s      2s   303ms      1s
# Lock time      3   107us     3us     7us     4us     5us       0     4us
# Rows sent      0      14       0       8    0.64    1.96    1.78       0
# Rows examine   0     369       2     128   16.77   56.92   33.86    1.96
# Query size    18 626.28k  11.30k  58.48k  28.47k  31.59k   9.41k  30.09k
# String:
# Databases    connecting
# Hosts        172.31.4.167 (5/22%), 172.31.14.244 (4/18%)... 5 more
# Users        django
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s  ################################################################
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `connecting` LIKE 'account_user'\G
#    SHOW CREATE TABLE `connecting`.`account_user`\G
#    SHOW TABLE STATUS FROM `connecting` LIKE 'account_useronboardingstatus'\G
#    SHOW CREATE TABLE `connecting`.`account_useronboardingstatus`\G
#    SHOW TABLE STATUS FROM `connecting` LIKE 'report_usercontrol'\G
#    SHOW CREATE TABLE `connecting`.`report_usercontrol`\G
#    SHOW TABLE STATUS FROM `connecting` LIKE 'report_usercontroltype'\G
#    SHOW CREATE TABLE `connecting`.`report_usercontroltype`\G
#    SHOW TABLE STATUS FROM `connecting` LIKE 'swipe_swipescore'\G
#    SHOW CREATE TABLE `connecting`.`swipe_swipescore`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT DISTINCT `account_user`.`id`, `account_user`.`actived_at` FROM `account_user` INNER JOIN `account_useronboardingstatus` ON (`account_user`.`id` = `account_useronboardingstatus`.`user_id`) WHERE (`account_user`.`approval` IN (0, 1, 3, 4) AND `account_user`.`black_server` = 0 AND NOT EXISTS(SELECT 1 AS `a` FROM `report_usercontrol` U0 INNER JOIN `report_usercontroltype` U1 ON (U0.`control_type_id` = U1.`id`) WHERE (U1.`type` IN ('PAUSE', 'BANNED') AND U0.`is_active` = 1 AND U0.`user_id` = (`account_user`.`id`)) LIMIT 1) AND `account_user`.`is_delete` = 0 AND `account_useronboardingstatus`.`status` = 'COMPLETED' AND NOT (`account_user`.`account_type` = 5) AND `account_user`.`birthday` <= '2008-11-19' AND `account_user`.`birthday` >= '2001-11-19' AND `account_user`.`sex` IN ('F') AND `account_user`.`actived_at` >= '2025-11-16 01:30:17.073358' AND `account_user`.`country_id` IN (24) AND `account_user`.`global_mode_type` IN (2, 3, 4) AND NOT (`account_user`.`ab_test_flag_1` IN (1, 11) AND `account_user`.`country_id` = 1 AND `account_user`.`country_id` IS NOT NULL AND `account_user`.`global_mode_type` = 2 AND `account_user`.`sex` = 'F') AND NOT (`account_user`.`id` IN (2736134, 2744327, 2547730, 2744342, 2744343, 2744355, 2736165, 2621478, 1949735, 2744373, 466997, 2375743, 2744387, 2588739, 2736209, 2482264, 2752601, 2752602, 2637917, 2703454/*... omitted 2517 items ...*/)) AND (`account_user`.`profile_level` = 'A' OR (SELECT U0.`score` FROM `swipe_swipescore` U0 INNER JOIN `account_user` U1 ON (U0.`user_id` = U1.`id`) WHERE (U0.`accum_count` >= 10 AND U0.`height_level` = 1 AND U0.`title` = 20 AND U0.`user_id` = (`account_user`.`id`) AND U0.`score` >= (COALESCE(CASE WHEN (U1.`sex` = 'M') THEN 0.07e0 WHEN (U1.`sex` = 'F') THEN 0.25e0 ELSE 0.07e0 END, 0.07e0, 0.25e0))) LIMIT 1) IS NOT NULL)) ORDER BY `account_user`.`actived_at` DESC LIMIT 100\G

# Query 9: 0.01 QPS, 0.01x concurrency, ID 0x4763EBBBBB9F101A5DDF7038151FEDF6 at byte 3132319
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.36
# Time range: 2025-11-19T01:07:14 to 2025-11-19T01:51:05
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          1      15
# Exec time      0     27s      1s      4s      2s      3s   809ms      2s
# Lock time      0    27us     1us     3us     1us     1us       0     1us
# Rows sent     15  41.34k   1.28k   6.54k   2.76k   3.69k   1.18k   2.62k
# Rows examine   0 476.81k  19.33k  86.00k  31.79k  40.32k  15.35k  28.66k
# Query size     0   4.50k     306     307  306.93  299.03       0  299.03
# String:
# Databases    connecting
# Hosts        172.31.11.56 (3/20%), 172.31.14.244 (3/20%)... 4 more
# Users        django
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s  ################################################################
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `connecting` LIKE 'swipe_swipechoice'\G
#    SHOW CREATE TABLE `connecting`.`swipe_swipechoice`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT `swipe_swipechoice`.`id`, `swipe_swipechoice`.`created_at`, `swipe_swipechoice`.`targetUser_id`, `swipe_swipechoice`.`choice`, `swipe_swipechoice`.`undoed_at` FROM `swipe_swipechoice` WHERE (`swipe_swipechoice`.`user_id` = 2347952 AND `swipe_swipechoice`.`created_at` >= '2025-10-20 01:42:55.183180')\G

# Query 10: 0.01 QPS, 0.02x concurrency, ID 0x4F71BF848445A6F59F91DA11519C8194 at byte 1253627
# This item is included in the report because it matches --outliers.
# Scores: V/M = 0.04
# Time range: 2025-11-19T01:25:23 to 2025-11-19T01:50:30
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          1      20
# Exec time      0     24s      1s      2s      1s      2s   230ms      1s
# Lock time      4   163us     5us    28us     8us    10us     4us     6us
# Rows sent      0     141       0      73    7.05   42.48   18.19       0
# Rows examine   0   1.39k      16     453   70.95  363.48  116.15   23.65
# Query size    17 592.76k  12.13k  80.42k  29.64k  31.59k  14.03k  31.59k
# String:
# Databases    connecting
# Hosts        172.31.4.167 (5/25%), 172.31.14.244 (4/20%)... 4 more
# Users        django
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s  ################################################################
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `connecting` LIKE 'account_user'\G
#    SHOW CREATE TABLE `connecting`.`account_user`\G
#    SHOW TABLE STATUS FROM `connecting` LIKE 'account_useronboardingstatus'\G
#    SHOW CREATE TABLE `connecting`.`account_useronboardingstatus`\G
#    SHOW TABLE STATUS FROM `connecting` LIKE 'report_usercontrol'\G
#    SHOW CREATE TABLE `connecting`.`report_usercontrol`\G
#    SHOW TABLE STATUS FROM `connecting` LIKE 'report_usercontroltype'\G
#    SHOW CREATE TABLE `connecting`.`report_usercontroltype`\G
#    SHOW TABLE STATUS FROM `connecting` LIKE 'swipe_swipescore'\G
#    SHOW CREATE TABLE `connecting`.`swipe_swipescore`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT `account_user`.`id`, `account_user`.`actived_at` FROM `account_user` INNER JOIN `account_useronboardingstatus` ON (`account_user`.`id` = `account_useronboardingstatus`.`user_id`) WHERE (`account_user`.`approval` IN (0, 1, 3, 4) AND `account_user`.`black_server` = 0 AND NOT EXISTS(SELECT 1 AS `a` FROM `report_usercontrol` U0 INNER JOIN `report_usercontroltype` U1 ON (U0.`control_type_id` = U1.`id`) WHERE (U1.`type` IN ('PAUSE', 'BANNED') AND U0.`is_active` = 1 AND U0.`user_id` = (`account_user`.`id`)) LIMIT 1) AND `account_user`.`is_delete` = 0 AND `account_useronboardingstatus`.`status` = 'COMPLETED' AND NOT (`account_user`.`account_type` = 5) AND `account_user`.`birthday` <= '2005-11-19' AND `account_user`.`birthday` >= '1925-11-19' AND `account_user`.`sex` IN ('F') AND `account_user`.`actived_at` >= '2025-11-14 01:29:55.470177' AND `account_user`.`country_id` IN (25) AND `account_user`.`global_mode_type` IN (2, 3, 4) AND NOT (`account_user`.`ab_test_flag_1` IN (1, 11) AND `account_user`.`country_id` = 1 AND `account_user`.`country_id` IS NOT NULL AND `account_user`.`global_mode_type` = 2 AND `account_user`.`sex` = 'F') AND NOT (`account_user`.`id` IN (2736134, 65545, 2547730, 2752533, 2744342, 2744343, 2752538, 2744346, 2662429, 2621478, 2711592, 1425451, 2752555, 2744373, 466997, 2105402, 2752572, 2728001, 2457666, 2588739/*... omitted 3356 items ...*/)) AND ((SELECT U0.`score` FROM `swipe_swipescore` U0 INNER JOIN `account_user` U1 ON (U0.`user_id` = U1.`id`) WHERE (U0.`accum_count` >= (COALESCE(CASE WHEN (U1.`sex` = 'M') THEN 100 WHEN (U1.`sex` = 'F') THEN 35 ELSE 100 END, 100, 35)) AND U0.`height_level` = 1 AND U0.`title` = 20 AND U0.`user_id` = (`account_user`.`id`)) LIMIT 1) IS NULL OR (SELECT U0.`score` FROM `swipe_swipescore` U0 WHERE (U0.`height_level` = 0 AND U0.`title` = 601 AND U0.`user_id` = (`account_user`.`id`)) LIMIT 1) IS NOT NULL OR CASE WHEN (`account_user`.`sex` = 'F') THEN (COALESCE((SELECT U0.`score` FROM `swipe_swipescore` U0 INNER JOIN `account_user` U1 ON (U0.`user_id` = U1.`id`) WHERE (U0.`accum_count` >= (COALESCE(CASE WHEN (U1.`sex` = 'M') THEN 100 WHEN (U1.`sex` = 'F') THEN 35 ELSE 100 END, 100, 35)) AND U0.`height_level` = 1 AND U0.`title` = 20 AND U0.`user_id` = (`account_user`.`id`)) LIMIT 1), 0.0e0) - 0.05001e0) ELSE (COALESCE((SELECT U0.`score` FROM `swipe_swipescore` U0 INNER JOIN `account_user` U1 ON (U0.`user_id` = U1.`id`) WHERE (U0.`accum_count` >= (COALESCE(CASE WHEN (U1.`sex` = 'M') THEN 100 WHEN (U1.`sex` = 'F') THEN 35 ELSE 100 END, 100, 35)) AND U0.`height_level` = 1 AND U0.`title` = 20 AND U0.`user_id` = (`account_user`.`id`)) LIMIT 1), 0.0e0) - 0.01001e0) END >= 0.0e0)) ORDER BY `account_user`.`actived_at` DESC LIMIT 100\G

# Query 12: 0.01 QPS, 0.01x concurrency, ID 0xCE4AF66A6BFF8AC7B7A32ED2C69F3D53 at byte 1287241
# This item is included in the report because it matches --outliers.
# Scores: V/M = 0.28
# Time range: 2025-11-19T01:21:13 to 2025-11-19T01:59:37
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          1      12
# Exec time      0     19s      1s      3s      2s      3s   665ms      1s
# Lock time      1    48us     3us     5us     4us     4us       0     3us
# Rows sent      4  11.38k     203   1.59k  971.33   1.53k  579.19  964.41
# Rows examine   0 890.60k  57.30k  83.88k  74.22k  83.83k   9.56k  68.96k
# Query size     0  15.84k   1.31k   1.32k   1.32k   1.26k       0   1.26k
# String:
# Databases    connecting
# Hosts        172.31.14.244 (3/25%), 172.31.3.252 (2/16%)... 6 more
# Users        django
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s  ################################################################
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `connecting` LIKE 'account_user'\G
#    SHOW CREATE TABLE `connecting`.`account_user`\G
#    SHOW TABLE STATUS FROM `connecting` LIKE 'account_useronboardingstatus'\G
#    SHOW CREATE TABLE `connecting`.`account_useronboardingstatus`\G
#    SHOW TABLE STATUS FROM `connecting` LIKE 'report_usercontrol'\G
#    SHOW CREATE TABLE `connecting`.`report_usercontrol`\G
#    SHOW TABLE STATUS FROM `connecting` LIKE 'report_usercontroltype'\G
#    SHOW CREATE TABLE `connecting`.`report_usercontroltype`\G
#    SHOW TABLE STATUS FROM `connecting` LIKE 'swipe_swipescore'\G
#    SHOW CREATE TABLE `connecting`.`swipe_swipescore`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT `account_user`.`id`, `account_user`.`actived_at` FROM `account_user` INNER JOIN `account_useronboardingstatus` ON (`account_user`.`id` = `account_useronboardingstatus`.`user_id`) WHERE (`account_user`.`approval` IN (0, 1, 3, 4) AND `account_user`.`black_server` = 0 AND NOT EXISTS(SELECT 1 AS `a` FROM `report_usercontrol` U0 INNER JOIN `report_usercontroltype` U1 ON (U0.`control_type_id` = U1.`id`) WHERE (U1.`type` IN ('PAUSE', 'BANNED') AND U0.`is_active` = 1 AND U0.`user_id` = (`account_user`.`id`)) LIMIT 1) AND `account_user`.`is_delete` = 0 AND `account_useronboardingstatus`.`status` = 'COMPLETED' AND NOT (`account_user`.`account_type` = 5) AND `account_user`.`birthday` <= '2008-11-19' AND `account_user`.`birthday` >= '1925-11-19' AND `account_user`.`sex` IN ('F', 'M', 'O') AND `account_user`.`actived_at` >= '2025-11-04 01:29:54.739732' AND `account_user`.`country_id` IN (1, 2, 23, 25, 28, 24, 26, 31, 32) AND `account_user`.`global_mode_type` IN (3, 4) AND (SELECT U0.`score` FROM `swipe_swipescore` U0 INNER JOIN `account_user` U1 ON (U0.`user_id` = U1.`id`) WHERE (U0.`accum_count` >= 10 AND U0.`height_level` = 1 AND U0.`title` = 20 AND U0.`user_id` = (`account_user`.`id`) AND U0.`score` >= (COALESCE(CASE WHEN (U1.`sex` = 'M') THEN 0.3e0 WHEN (U1.`sex` = 'F') THEN 0.35e0 ELSE 0.3e0 END, 0.3e0, 0.35e0))) LIMIT 1) IS NOT NULL)\G

z - 작성중 - 더 자세하게 쿼리 프로파일링하기

24 데이터 베이스 인덱스