jisangs
03eea2098e
- 01~07 작업 문서를 운영 측정값 기준으로 정리 (문서의 DB 비밀번호 삭제) - app_highest_record 중복 8,795행 점수 기준 정리, 인덱스 5개 + UNIQUE 키 2개 추가 - 02·04 공통 측정 스크립트와 측정 결과, comparison.txt 추가 - 서버 처리 시간 18~292배 단축, 결과 행 수 동일 - 운영 데이터 백업과 권한(비밀번호 해시) 파일은 커밋에서 제외 Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
309 lines
9.5 KiB
Plaintext
309 lines
9.5 KiB
Plaintext
=== q1_hour_func ===
|
|
{
|
|
"query_optimization": {
|
|
"r_total_time_ms": 0.303570412
|
|
},
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"r_loops": 1,
|
|
"r_total_time_ms": 2.069071756,
|
|
"nested_loop": [
|
|
{
|
|
"read_sorted_file": {
|
|
"r_rows": 22,
|
|
"filesort": {
|
|
"sort_key": "best_record.RecordDateTime, best_record.PlayerID",
|
|
"r_loops": 1,
|
|
"r_total_time_ms": 2.050037968,
|
|
"r_used_priority_queue": false,
|
|
"r_output_rows": 22,
|
|
"r_buffer_size": "2047Kb",
|
|
"r_sort_mode": "sort_key,addon_fields",
|
|
"table": {
|
|
"table_name": "best_record",
|
|
"access_type": "ref",
|
|
"possible_keys": [
|
|
"MaestroID",
|
|
"AppID",
|
|
"idx_maestro_app_dt",
|
|
"idx_maestro_player_app_dt"
|
|
],
|
|
"key": "idx_maestro_app_dt",
|
|
"key_length": "8",
|
|
"used_key_parts": ["MaestroID", "AppID"],
|
|
"ref": ["const", "const"],
|
|
"r_loops": 1,
|
|
"rows": 5851,
|
|
"r_rows": 22,
|
|
"r_table_time_ms": 2.024712928,
|
|
"r_other_time_ms": 0.015343053,
|
|
"r_engine_stats": {
|
|
"pages_accessed": 77
|
|
},
|
|
"filtered": 100,
|
|
"r_filtered": 100,
|
|
"index_condition": "cast(best_record.RecordDateTime as date) = @`day` and hour(best_record.RecordDateTime) = 9",
|
|
"attached_condition": "best_record.MaestroID <=> @maestro and best_record.AppID <=> @app"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
]
|
|
}
|
|
}
|
|
=== q2_hour_range ===
|
|
{
|
|
"query_optimization": {
|
|
"r_total_time_ms": 0.23481673
|
|
},
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"r_loops": 1,
|
|
"r_total_time_ms": 0.259231588,
|
|
"nested_loop": [
|
|
{
|
|
"read_sorted_file": {
|
|
"r_rows": 22,
|
|
"filesort": {
|
|
"sort_key": "best_record.RecordDateTime, best_record.PlayerID",
|
|
"r_loops": 1,
|
|
"r_total_time_ms": 0.241578075,
|
|
"r_used_priority_queue": false,
|
|
"r_output_rows": 22,
|
|
"r_buffer_size": "2047Kb",
|
|
"r_sort_mode": "sort_key,addon_fields",
|
|
"table": {
|
|
"table_name": "best_record",
|
|
"access_type": "range",
|
|
"possible_keys": [
|
|
"MaestroID",
|
|
"AppID",
|
|
"idx_maestro_app_dt",
|
|
"idx_maestro_player_app_dt"
|
|
],
|
|
"key": "idx_maestro_app_dt",
|
|
"key_length": "13",
|
|
"used_key_parts": ["MaestroID", "AppID", "RecordDateTime"],
|
|
"r_loops": 1,
|
|
"rows": 22,
|
|
"r_rows": 22,
|
|
"r_table_time_ms": 0.143388535,
|
|
"r_other_time_ms": 0.086477209,
|
|
"r_engine_stats": {
|
|
"pages_accessed": 69
|
|
},
|
|
"filtered": 100,
|
|
"r_filtered": 100,
|
|
"index_condition": "best_record.MaestroID = @maestro and best_record.AppID = @app and best_record.RecordDateTime >= '2026-08-26 09:00:00' and best_record.RecordDateTime < '2026-08-26 10:00:00'"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
]
|
|
}
|
|
}
|
|
=== q3_day_func ===
|
|
{
|
|
"query_optimization": {
|
|
"r_total_time_ms": 0.260151772
|
|
},
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"r_loops": 1,
|
|
"r_total_time_ms": 2.440415655,
|
|
"filesort": {
|
|
"sort_key": "max(best_record.BestRecord) desc, best_record.PlayerID",
|
|
"r_loops": 1,
|
|
"r_total_time_ms": 0.019813943,
|
|
"r_used_priority_queue": false,
|
|
"r_output_rows": 61,
|
|
"r_buffer_size": "1Kb",
|
|
"r_sort_mode": "sort_key,rowid",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "best_record",
|
|
"access_type": "ref",
|
|
"possible_keys": [
|
|
"MaestroID",
|
|
"AppID",
|
|
"idx_maestro_app_dt",
|
|
"idx_maestro_player_app_dt"
|
|
],
|
|
"key": "idx_maestro_app_dt",
|
|
"key_length": "8",
|
|
"used_key_parts": ["MaestroID", "AppID"],
|
|
"ref": ["const", "const"],
|
|
"r_loops": 1,
|
|
"rows": 5851,
|
|
"r_rows": 99,
|
|
"r_table_time_ms": 2.331223925,
|
|
"r_other_time_ms": 0.071694268,
|
|
"r_engine_stats": {
|
|
"pages_accessed": 308
|
|
},
|
|
"filtered": 100,
|
|
"r_filtered": 100,
|
|
"index_condition": "year(best_record.RecordDateTime) = 2026 and month(best_record.RecordDateTime) = 8 and dayofmonth(best_record.RecordDateTime) = 26",
|
|
"attached_condition": "best_record.MaestroID <=> @maestro and best_record.AppID <=> @app"
|
|
}
|
|
}
|
|
]
|
|
}
|
|
}
|
|
}
|
|
}
|
|
=== q4_day_range ===
|
|
{
|
|
"query_optimization": {
|
|
"r_total_time_ms": 0.335646795
|
|
},
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"r_loops": 1,
|
|
"r_total_time_ms": 0.467062948,
|
|
"filesort": {
|
|
"sort_key": "max(best_record.BestRecord) desc, best_record.PlayerID",
|
|
"r_loops": 1,
|
|
"r_total_time_ms": 0.017993581,
|
|
"r_used_priority_queue": false,
|
|
"r_output_rows": 61,
|
|
"r_buffer_size": "1Kb",
|
|
"r_sort_mode": "sort_key,rowid",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "best_record",
|
|
"access_type": "range",
|
|
"possible_keys": [
|
|
"MaestroID",
|
|
"AppID",
|
|
"idx_maestro_app_dt",
|
|
"idx_maestro_player_app_dt"
|
|
],
|
|
"key": "idx_maestro_app_dt",
|
|
"key_length": "13",
|
|
"used_key_parts": ["MaestroID", "AppID", "RecordDateTime"],
|
|
"r_loops": 1,
|
|
"rows": 99,
|
|
"r_rows": 99,
|
|
"r_table_time_ms": 0.318203324,
|
|
"r_other_time_ms": 0.112702428,
|
|
"r_engine_stats": {
|
|
"pages_accessed": 300
|
|
},
|
|
"filtered": 100,
|
|
"r_filtered": 100,
|
|
"index_condition": "best_record.MaestroID = @maestro and best_record.AppID = @app and best_record.RecordDateTime >= '2026-08-26 00:00:00' and best_record.RecordDateTime < '2026-08-27 00:00:00'"
|
|
}
|
|
}
|
|
]
|
|
}
|
|
}
|
|
}
|
|
}
|
|
=== q5_month_func ===
|
|
{
|
|
"query_optimization": {
|
|
"r_total_time_ms": 0.248649482
|
|
},
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"r_loops": 1,
|
|
"r_total_time_ms": 4.299725667,
|
|
"filesort": {
|
|
"sort_key": "max(best_record.BestRecord) desc, best_record.PlayerID",
|
|
"r_loops": 1,
|
|
"r_total_time_ms": 0.068003533,
|
|
"r_used_priority_queue": false,
|
|
"r_output_rows": 262,
|
|
"r_buffer_size": "6Kb",
|
|
"r_sort_mode": "sort_key,rowid",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "best_record",
|
|
"access_type": "ref",
|
|
"possible_keys": [
|
|
"MaestroID",
|
|
"AppID",
|
|
"idx_maestro_app_dt",
|
|
"idx_maestro_player_app_dt"
|
|
],
|
|
"key": "idx_maestro_app_dt",
|
|
"key_length": "8",
|
|
"used_key_parts": ["MaestroID", "AppID"],
|
|
"ref": ["const", "const"],
|
|
"r_loops": 1,
|
|
"rows": 5851,
|
|
"r_rows": 709,
|
|
"r_table_time_ms": 3.78789381,
|
|
"r_other_time_ms": 0.408851363,
|
|
"r_engine_stats": {
|
|
"pages_accessed": 2138
|
|
},
|
|
"filtered": 100,
|
|
"r_filtered": 100,
|
|
"index_condition": "year(best_record.RecordDateTime) = 2026 and month(best_record.RecordDateTime) = 8",
|
|
"attached_condition": "best_record.MaestroID <=> @maestro and best_record.AppID <=> @app"
|
|
}
|
|
}
|
|
]
|
|
}
|
|
}
|
|
}
|
|
}
|
|
=== q6_month_range ===
|
|
{
|
|
"query_optimization": {
|
|
"r_total_time_ms": 0.306621019
|
|
},
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"r_loops": 1,
|
|
"r_total_time_ms": 2.496056728,
|
|
"filesort": {
|
|
"sort_key": "max(best_record.BestRecord) desc, best_record.PlayerID",
|
|
"r_loops": 1,
|
|
"r_total_time_ms": 0.065773089,
|
|
"r_used_priority_queue": false,
|
|
"r_output_rows": 262,
|
|
"r_buffer_size": "6Kb",
|
|
"r_sort_mode": "sort_key,rowid",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "best_record",
|
|
"access_type": "range",
|
|
"possible_keys": [
|
|
"MaestroID",
|
|
"AppID",
|
|
"idx_maestro_app_dt",
|
|
"idx_maestro_player_app_dt"
|
|
],
|
|
"key": "idx_maestro_app_dt",
|
|
"key_length": "13",
|
|
"used_key_parts": ["MaestroID", "AppID", "RecordDateTime"],
|
|
"r_loops": 1,
|
|
"rows": 709,
|
|
"r_rows": 709,
|
|
"r_table_time_ms": 2.033224622,
|
|
"r_other_time_ms": 0.360211684,
|
|
"r_engine_stats": {
|
|
"pages_accessed": 2131
|
|
},
|
|
"filtered": 0.057728633,
|
|
"r_filtered": 100,
|
|
"index_condition": "best_record.MaestroID = @maestro and best_record.AppID = @app and best_record.RecordDateTime >= '2026-08-01 00:00:00' and best_record.RecordDateTime < '2026-09-01 00:00:00'"
|
|
}
|
|
}
|
|
]
|
|
}
|
|
}
|
|
}
|
|
}
|