Repository navigation
Expand file tree
/
Copy pathperformance_manager.py
More file actions
1627 lines (1343 loc) · 63.4 KB
/
Copy pathperformance_manager.py
File metadata and controls
1627 lines (1343 loc) · 63.4 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
"""
MySQL性能管理器 - 企业级数据库性能优化方案
统一管理和优化MySQL数据库性能的综合性工具,集成了慢查询分析、索引优化、
查询性能剖析、系统监控和报告生成等全方位性能管理功能。
@fileoverview MySQL性能管理的统一解决方案
@author liyq
@version 1.0.0
@since 1.0.0
@updated 2025-09-25
@license MIT
"""
import asyncio
import re
from datetime import datetime, timedelta
from typing import Any, Dict, List, Optional
from dataclasses import dataclass
from type_utils import (
MySQLMCPError,
ErrorCategory,
ErrorSeverity
)
from logger import logger
from mysql_manager import MySQLManager
# =============================================================================
# 性能管理器相关类型定义
# =============================================================================
@dataclass
class IndexSuggestion:
"""索引建议信息"""
table: str
columns: List[str]
index_type: str # 'PRIMARY', 'UNIQUE', 'INDEX', 'FULLTEXT', 'SPATIAL'
expected_improvement: str
priority: str # 'HIGH', 'MEDIUM', 'LOW'
reason: str
@dataclass
class QueryProfileResult:
"""查询剖析结果"""
explain_result: List[Dict[str, Any]]
execution_stats: Dict[str, Any]
recommendations: List[str]
performance_score: float
@dataclass
class SlowQueryInfo:
"""慢查询信息"""
sql_text: str
execution_time: float
lock_time: float
rows_examined: int
rows_returned: int
start_time: datetime
user: str
database: str
ip_address: str
thread_id: int
uses_index: bool
@dataclass
class SlowQueryAnalysis:
"""慢查询分析结果"""
total_slow_queries: int
slowest_query: Optional[SlowQueryInfo]
average_execution_time: float
common_patterns: List[Dict[str, Any]]
index_suggestions: List[IndexSuggestion]
performance_issues: List[str]
recommendations: List[str]
@dataclass
class PerformanceReport:
"""性能报告结果"""
generated_at: datetime
summary: Dict[str, Any]
slow_query_analysis: SlowQueryAnalysis
system_status: Dict[str, Any]
recommendations: List[str]
@dataclass
class PerformanceAnalysisConfig:
"""性能分析配置选项"""
long_query_time: Optional[float] = 1.0
time_range: Optional[int] = 1
include_details: Optional[bool] = True
limit: Optional[int] = 100
min_examined_row_limit: Optional[int] = 1000
enable_performance_schema: Optional[bool] = True
log_queries_not_using_indexes: Optional[bool] = True
max_log_file_size: Optional[int] = 100
log_slow_admin_statements: Optional[bool] = True
slow_query_log_file: Optional[str] = None
@dataclass
class SlowQueryConfig:
"""慢查询配置选项"""
long_query_time: Optional[float] = 1.0
log_queries_not_using_indexes: Optional[bool] = True
min_examined_row_limit: Optional[int] = 1000
max_log_file_size: Optional[int] = 100
log_slow_admin_statements: Optional[bool] = True
slow_query_log_file: Optional[str] = None
enable_performance_schema: Optional[bool] = True
# =============================================================================
# 慢查询分析模块
# =============================================================================
class SlowQueryAnalysisModule:
"""
慢查询分析模块
专门用于分析MySQL慢查询日志和性能模式数据的模块,能够识别性能瓶颈、
分析查询模式并提供优化建议。通过分析performance_schema中的统计信息,
提供详细的慢查询分析报告。
"""
def __init__(self, mysql_manager: MySQLManager, config: PerformanceAnalysisConfig = None):
"""初始化慢查询分析模块"""
self.mysql_manager = mysql_manager
self.config = config or PerformanceAnalysisConfig()
async def enable_slow_query_log(self, config: SlowQueryConfig = None) -> bool:
"""
启用慢查询日志
配置MySQL服务器启用慢查询日志记录功能。
Args:
config: 慢查询配置参数
Returns:
bool: 配置是否成功
"""
effective_config = {**self.config.__dict__, **(config.__dict__ if config else {})}
try:
# 检查用户权限
await self._check_user_privileges()
# 设置慢查询相关参数
settings = [
"SET GLOBAL slow_query_log = 'ON'",
f"SET GLOBAL long_query_time = {effective_config.get('long_query_time', 1)}",
f"SET GLOBAL log_queries_not_using_indexes = '{effective_config.get('log_queries_not_using_indexes', True) and 'ON' or 'OFF'}'",
f"SET GLOBAL min_examined_row_limit = {effective_config.get('min_examined_row_limit', 1000)}",
f"SET GLOBAL log_slow_admin_statements = '{effective_config.get('log_slow_admin_statements', True) and 'ON' or 'OFF'}'"
]
if effective_config.get('slow_query_log_file'):
settings.append(f"SET GLOBAL slow_query_log_file = '{effective_config['slow_query_log_file']}'")
# 批量执行配置
for setting in settings:
await self.mysql_manager.execute_query(setting)
# 验证配置是否生效
verify_query = """
SELECT
@@slow_query_log as slow_query_log_enabled,
@@long_query_time as long_query_time,
@@log_queries_not_using_indexes as log_queries_not_using_indexes,
@@min_examined_row_limit as min_examined_row_limit
"""
verify_result = await self.mysql_manager.execute_query(verify_query)
settings_verified = verify_result[0] if verify_result else {}
if not settings_verified.get('slow_query_log_enabled'):
raise MySQLMCPError(
"慢查询日志启用失败",
ErrorCategory.SLOW_QUERY_LOG_ERROR,
ErrorSeverity.HIGH
)
# 更新配置
if config:
self.config = PerformanceAnalysisConfig(**{**self.config.__dict__, **config.__dict__})
logger.info("✅ 慢查询日志已成功启用")
return True
except Exception as e:
raise MySQLMCPError(
f"启用慢查询日志失败: {str(e)}",
ErrorCategory.SLOW_QUERY_CONFIGURATION_ERROR,
ErrorSeverity.HIGH
)
async def disable_slow_query_log(self) -> bool:
"""禁用慢查询日志"""
try:
await self.mysql_manager.execute_query("SET GLOBAL slow_query_log = 'OFF'")
logger.info("⏹️ 慢查询日志已禁用")
return True
except Exception as e:
raise MySQLMCPError(
f"禁用慢查询日志失败: {str(e)}",
ErrorCategory.SLOW_QUERY_CONFIGURATION_ERROR,
ErrorSeverity.MEDIUM
)
async def get_slow_query_log_config(self) -> SlowQueryConfig:
"""获取慢查询日志配置"""
try:
query = """
SELECT
@@slow_query_log as slowQueryLog,
@@long_query_time as longQueryTime,
@@log_queries_not_using_indexes as logQueriesNotUsingIndexes,
@@min_examined_row_limit as minExaminedRowLimit,
@@slow_query_log_file as slowQueryLogFile,
@@log_slow_admin_statements as logSlowAdminStatements
"""
result = await self.mysql_manager.execute_query(query)
return SlowQueryConfig(**result[0]) if result else SlowQueryConfig()
except Exception as e:
raise MySQLMCPError(
f"获取慢查询日志配置失败: {str(e)}",
ErrorCategory.DATA_ERROR,
ErrorSeverity.LOW
)
async def get_slow_query_log_status(self) -> Dict[str, Any]:
"""获取慢查询日志状态"""
try:
queries = [
'SELECT @@slow_query_log as enabled',
'SELECT @@slow_query_log_file as log_file',
'SELECT @@long_query_time as threshold_seconds',
'SELECT @@log_queries_not_using_indexes as log_no_index',
'SELECT @@min_examined_row_limit as min_rows',
'SELECT @@log_slow_admin_statements as log_admin'
]
results = {}
for i, query in enumerate(queries):
try:
result = await self.mysql_manager.execute_query(query)
if result:
results.update(result[0])
except Exception as e:
logger.warning(f"获取状态信息失败 ({i}): {str(e)}")
return results
except Exception as e:
raise MySQLMCPError(
f"获取慢查询日志状态失败: {str(e)}",
ErrorCategory.DATA_ERROR,
ErrorSeverity.LOW
)
async def analyze_slow_queries(self, limit: int = 100, time_range: str = '1 day') -> SlowQueryAnalysis:
"""
分析慢查询日志
通过查询performance_schema.events_statements_summary_by_digest表,
分析慢查询的执行统计信息,识别性能瓶颈和常见查询模式。
Args:
limit: 限制返回的慢查询数量,默认为100
time_range: 分析时间范围,默认为1天
Returns:
SlowQueryAnalysis: 慢查询分析结果
"""
try:
# 检查performance_schema是否启用
await self._check_performance_schema()
# 检查列可用性
column_availability = await self._check_column_availability()
# 构建时间范围条件
time_filter = self._build_time_filter(time_range)
# 基础查询列(这些在大部分版本中都有)
base_columns = [
"DIGEST_TEXT as sql_text",
"COUNT_STAR as execution_count",
"SUM_TIMER_WAIT / 1000000000 as total_time_sec",
"AVG_TIMER_WAIT / 1000000000 as avg_time_sec",
"MAX_TIMER_WAIT / 1000000000 as max_time_sec",
"FIRST_SEEN as first_seen",
"LAST_SEEN as last_seen",
"SCHEMA_NAME as database_name",
"DIGEST as query_digest"
]
# 根据列可用性添加额外列
optional_columns = []
if column_availability.get('SUM_ROWS_EXAMINED', False):
optional_columns.append("SUM_ROWS_EXAMINED as total_rows_examined")
else:
optional_columns.append("0 as total_rows_examined")
if column_availability.get('SUM_ROWS_SENT', False):
optional_columns.append("SUM_ROWS_SENT as total_rows_sent")
else:
optional_columns.append("0 as total_rows_sent")
if column_availability.get('SUM_NO_INDEX_USED', False) and column_availability.get('SUM_NO_GOOD_INDEX_USED', False):
optional_columns.append("SUM_NO_INDEX_USED + SUM_NO_GOOD_INDEX_USED as queries_without_index")
elif column_availability.get('SUM_NO_INDEX_USED', False):
optional_columns.append("SUM_NO_INDEX_USED as queries_without_index")
else:
optional_columns.append("0 as queries_without_index")
# 组合所有列
all_columns = base_columns + optional_columns
# 查询慢查询统计信息
query = f"""
SELECT
{', '.join(all_columns)}
FROM performance_schema.events_statements_summary_by_digest
WHERE
SCHEMA_NAME IS NOT NULL
AND DIGEST_TEXT IS NOT NULL
AND AVG_TIMER_WAIT > {(self.config.long_query_time or 1)} * 1000000000
{time_filter}
ORDER BY AVG_TIMER_WAIT DESC
LIMIT {limit}
"""
slow_queries = await self.mysql_manager.execute_query(query)
if not slow_queries:
return self._create_empty_analysis()
# 转换格式并分析
query_infos = []
for row in slow_queries:
query_info = SlowQueryInfo(
sql_text=row.get('sql_text', ''),
execution_time=row.get('avg_time_sec', 0),
lock_time=0,
rows_examined=self._safe_int_convert(row.get('total_rows_examined', 0) / max(row.get('execution_count', 1), 1)),
rows_returned=self._safe_int_convert(row.get('total_rows_sent', 0) / max(row.get('execution_count', 1), 1)),
start_time=datetime.now(),
user='N/A',
database=row.get('database_name', 'unknown'),
ip_address='N/A',
thread_id=0,
uses_index=row.get('queries_without_index', 0) == 0
)
query_infos.append(query_info)
# 生成完整分析结果
return self._generate_analysis_result(query_infos, slow_queries)
except Exception as e:
raise MySQLMCPError(
f"慢查询分析失败: {str(e)}",
ErrorCategory.SLOW_QUERY_ANALYSIS_ERROR,
ErrorSeverity.MEDIUM
)
async def get_active_slow_queries(self) -> List[SlowQueryInfo]:
"""
获取活跃的慢查询
查询information_schema.processlist表,获取当前正在执行的慢查询。
"""
try:
query = f"""
SELECT
info AS sql_text,
TIME AS execution_time,
COMMAND AS command_type,
STATE AS current_state,
DB AS database_name,
HOST AS client_host,
ID AS thread_id
FROM information_schema.processlist
WHERE TIME > {(self.config.long_query_time or 1)}
AND COMMAND != 'Sleep'
ORDER BY TIME DESC
LIMIT 50
"""
result = await self.mysql_manager.execute_query(query)
active_queries = []
for row in result:
query_info = SlowQueryInfo(
sql_text=row.get('sql_text', ''),
execution_time=row.get('execution_time', 0),
lock_time=0,
rows_examined=0,
rows_returned=0,
start_time=datetime.now() - timedelta(seconds=row.get('execution_time', 0)),
user=row.get('client_host', '').split('@')[0] if row.get('client_host') else '',
database=row.get('database_name', ''),
ip_address=row.get('client_host', '').split('@')[1] if row.get('client_host') else '',
thread_id=row.get('thread_id', 0),
uses_index=False
)
active_queries.append(query_info)
return active_queries
except Exception as e:
raise MySQLMCPError(
f"获取活跃慢查询失败: {str(e)}",
ErrorCategory.DATA_ERROR,
ErrorSeverity.LOW
)
async def _check_user_privileges(self) -> None:
"""检查用户权限"""
try:
await self.mysql_manager.execute_query("SELECT @@version")
except Exception as e:
if "Access denied" in str(e):
raise MySQLMCPError(
"用户没有足够的权限配置慢查询日志,需要SUPER权限或相应权限",
ErrorCategory.PRIVILEGE_ERROR,
ErrorSeverity.HIGH
)
raise
async def _check_performance_schema(self) -> None:
"""检查性能模式是否启用"""
try:
result = await self.mysql_manager.execute_query("SELECT @@performance_schema as enabled")
enabled = result[0].get('enabled', 0) if result else 0
if not enabled:
raise MySQLMCPError(
"performance_schema未启用,无法进行慢查询分析。需要启用performance_schema以获得详细的查询统计信息。",
ErrorCategory.CONFIGURATION_ERROR,
ErrorSeverity.MEDIUM
)
except Exception as e:
raise MySQLMCPError(
f"检查performance_schema状态失败: {str(e)}",
ErrorCategory.CONFIGURATION_ERROR,
ErrorSeverity.MEDIUM
)
async def _check_column_availability(self) -> Dict[str, bool]:
"""检查性能模式表中的列是否可用"""
try:
# 检查events_statements_summary_by_digest表中的列
result = await self.mysql_manager.execute_query("""
SELECT COLUMN_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'performance_schema'
AND TABLE_NAME = 'events_statements_summary_by_digest'
""")
available_columns = {row['COLUMN_NAME'] for row in result} if result else set()
return {
'SUM_ROWS_EXAMINED': 'SUM_ROWS_EXAMINED' in available_columns,
'SUM_ROWS_SENT': 'SUM_ROWS_SENT' in available_columns,
'SUM_NO_INDEX_USED': 'SUM_NO_INDEX_USED' in available_columns,
'SUM_NO_GOOD_INDEX_USED': 'SUM_NO_GOOD_INDEX_USED' in available_columns
}
except Exception as e:
# 如果检查失败,假设所有列都不可用(向后兼容)
return {
'SUM_ROWS_EXAMINED': False,
'SUM_ROWS_SENT': False,
'SUM_NO_INDEX_USED': False,
'SUM_NO_GOOD_INDEX_USED': False
}
def _build_time_filter(self, time_range: str) -> str:
"""构建时间范围过滤条件"""
return f"AND LAST_SEEN >= DATE_SUB(NOW(), INTERVAL {time_range})" if time_range else ""
def _create_empty_analysis(self) -> SlowQueryAnalysis:
"""创建空的慢查询分析结果"""
return SlowQueryAnalysis(
total_slow_queries=0,
slowest_query=None,
average_execution_time=0.0,
common_patterns=[],
index_suggestions=[],
performance_issues=[],
recommendations=["未发现慢查询记录"]
)
def _generate_analysis_result(self, query_infos: List[SlowQueryInfo], raw_queries: List[Dict[str, Any]]) -> SlowQueryAnalysis:
"""生成完整的分析结果"""
if not query_infos:
return self._create_empty_analysis()
total_time = sum(q.execution_time for q in query_infos)
avg_time = total_time / len(query_infos)
# 分析查询模式
pattern_count = {}
for query in query_infos:
pattern = self._extract_query_pattern(query.sql_text)
if pattern not in pattern_count:
pattern_count[pattern] = {"count": 0, "total_time": 0}
pattern_count[pattern]["count"] += 1
pattern_count[pattern]["total_time"] += query.execution_time
common_patterns = [
{
"pattern": pattern,
"count": stats["count"],
"avg_time": stats["total_time"] / stats["count"]
}
for pattern, stats in pattern_count.items()
]
common_patterns.sort(key=lambda x: x["count"], reverse=True)
common_patterns = common_patterns[:10]
# 生成索引建议
index_suggestions = self._generate_index_suggestions(query_infos)
# 识别性能问题
performance_issues = self._identify_performance_issues(query_infos, raw_queries)
# 生成优化建议
recommendations = self._generate_optimization_recommendations(
len(query_infos), avg_time, common_patterns
)
return SlowQueryAnalysis(
total_slow_queries=len(query_infos),
slowest_query=query_infos[0] if query_infos else None,
average_execution_time=avg_time,
common_patterns=common_patterns,
index_suggestions=index_suggestions,
performance_issues=performance_issues,
recommendations=recommendations
)
def _extract_query_pattern(self, sql_text: str) -> str:
"""提取查询模式字符串"""
return sql_text.replace(' ', '').replace('\n', '').replace('\t', '').upper()[:100]
def _generate_index_suggestions(self, query_infos: List[SlowQueryInfo]) -> List[IndexSuggestion]:
"""生成索引优化建议"""
suggestions = []
for query in query_infos:
if not query.uses_index and query.execution_time > 1:
upper_sql = query.sql_text.upper()
if 'WHERE' in upper_sql and '=' in upper_sql:
table_match = re.search(r'FROM\s+(\w+)', query.sql_text, re.IGNORECASE)
table = table_match.group(1) if table_match else 'unknown_table'
# 根据WHERE条件生成不同的索引建议
columns = []
priority = 'MEDIUM'
expected_improvement = '60-85%'
if 'WHERE ID =' in upper_sql or 'WHERE USER_ID =' in upper_sql:
columns = ['id']
priority = 'HIGH'
expected_improvement = '70-90%'
elif 'WHERE EMAIL =' in upper_sql:
columns = ['email']
priority = 'MEDIUM'
elif 'WHERE STATUS =' in upper_sql:
columns = ['status']
priority = 'MEDIUM'
if columns:
suggestions.append(IndexSuggestion(
table=table,
columns=columns,
index_type='INDEX',
expected_improvement=expected_improvement,
priority=priority,
reason=f"WHERE子句中频繁使用{','.join(columns)}字段进行查询"
))
# 复合索引建议
if 'WHERE' in upper_sql and (
upper_sql.count('AND') > 1 or
'ORDER BY' in upper_sql or
'GROUP BY' in upper_sql
):
composite_columns = self._extract_composite_columns(upper_sql)
if len(composite_columns) > 1:
suggestions.append(IndexSuggestion(
table=table,
columns=composite_columns,
index_type='INDEX',
expected_improvement='70-95%',
priority='HIGH',
reason='多条件查询,复合索引可显著提升性能'
))
return suggestions
def _extract_composite_columns(self, upper_sql: str) -> List[str]:
"""提取复合索引列"""
columns = []
where_clause = upper_sql.split('WHERE')[1].split('ORDER BY')[0].split('GROUP BY')[0] if 'WHERE' in upper_sql else ''
# 提取WHERE子句中的字段
field_pattern = r'(\w+)\s*[=!><]'
matches = re.findall(field_pattern, where_clause)
for field in matches:
if field.upper() not in ['AND', 'OR', 'NOT', 'IS', 'NULL', 'EXISTS', 'IN']:
if field not in columns:
columns.append(field.lower())
if len(columns) >= 3: # 最多3个字段
break
return columns
def _identify_performance_issues(self, query_infos: List[SlowQueryInfo], raw_queries: List[Dict[str, Any]]) -> List[str]:
"""识别性能问题"""
issues = []
no_index_queries = len([q for q in query_infos if not q.uses_index])
if no_index_queries > len(query_infos) * 0.5:
issues.append(f"大量查询未使用索引 ({no_index_queries}/{len(query_infos)})")
total_rows_examined = sum(q.rows_examined for q in query_infos)
avg_rows_examined = total_rows_examined / len(query_infos) if query_infos else 0
if avg_rows_examined > 10000:
issues.append(f"平均扫描行数过高 ({int(avg_rows_examined)}行)")
long_running_queries = len([q for q in query_infos if q.execution_time > 5])
if long_running_queries > 0:
issues.append(f"发现{long_running_queries}个执行时间超过5秒的查询")
high_lock_time_queries = len([q for q in query_infos if q.lock_time > 1])
if high_lock_time_queries > 0:
issues.append(f"发现{high_lock_time_queries}个锁等待时间较长的查询")
return issues
def _generate_optimization_recommendations(
self,
total_queries: int,
avg_time: float,
common_patterns: List[Dict[str, Any]]
) -> List[str]:
"""生成优化建议"""
recommendations = []
if avg_time > 2:
recommendations.append("⚡ 查询平均执行时间较长,建议检查数据库参数调优")
if len(common_patterns) > 3:
recommendations.append("🔄 发现重复查询模式,考虑使用查询缓存或存储过程")
if total_queries > 50:
recommendations.append("📊 慢查询数量较多,建议启用慢查询日志进行详细分析")
if total_queries > 10:
recommendations.append("🔍 发现多个慢查询,建议添加适当索引")
if not recommendations:
recommendations.append("✅ 查询性能相对良好,建议继续监控")
return recommendations
# =============================================================================
# 索引优化模块
# =============================================================================
class IndexOptimizationModule:
"""
索引优化模块
专门用于分析数据库索引使用情况并生成优化建议的模块。通过分析慢查询模式、
现有索引使用情况和表结构,提供针对性的索引创建建议,以提升查询性能。
"""
def __init__(self, mysql_manager: MySQLManager, config: PerformanceAnalysisConfig = None):
"""初始化索引优化模块"""
self.mysql_manager = mysql_manager
self.config = config or PerformanceAnalysisConfig()
async def generate_index_suggestions(self, limit: int = 50, time_range: str = '1 day') -> List[IndexSuggestion]:
"""
生成索引优化建议
通过分析慢查询模式和现有索引使用情况,生成针对性的索引创建建议。
Args:
limit: 限制返回的建议数量,默认为50
time_range: 分析时间范围,默认为1天
Returns:
List[IndexSuggestion]: 索引优化建议列表
"""
try:
# 初始化慢查询分析模块实例
slow_query_module = SlowQueryAnalysisModule(self.mysql_manager, self.config)
slow_query_analysis = await slow_query_module.analyze_slow_queries(limit, time_range)
if slow_query_analysis.total_slow_queries == 0:
return self._generate_general_index_recommendations()
# 基于慢查询数据生成针对性建议
suggestions = []
analyzed_tables = set()
if slow_query_analysis.common_patterns:
for pattern in slow_query_analysis.common_patterns:
pattern_suggestions = await self._analyze_query_pattern(pattern["pattern"])
for suggestion in pattern_suggestions:
if suggestion.table not in analyzed_tables:
suggestions.append(suggestion)
analyzed_tables.add(suggestion.table)
# 分析现有索引使用情况
existing_index_analysis = await self._analyze_existing_indexes()
suggestions.extend(existing_index_analysis)
return suggestions[:limit]
except Exception as e:
raise MySQLMCPError(
f"索引建议生成失败: {str(e)}",
ErrorCategory.SLOW_QUERY_INDEX_SUGGESTION_ERROR,
ErrorSeverity.MEDIUM
)
async def _analyze_query_pattern(self, pattern: str) -> List[IndexSuggestion]:
"""分析查询模式"""
suggestions = []
try:
if 'FROM' in pattern and 'WHERE' in pattern:
table_match = re.search(r'FROM\s+(\w+)', pattern, re.IGNORECASE)
if table_match:
table_name = table_match.group(1)
# 检查表是否存在
if await self._check_table_exists(table_name):
# 分析WHERE条件中的字段
field_suggestions = self._extract_field_suggestions(pattern, table_name)
suggestions.extend(field_suggestions)
except Exception:
# 忽略分析错误,继续下一个模式
pass
return suggestions
async def _check_table_exists(self, table_name: str) -> bool:
"""检查表是否存在"""
try:
query = f"SHOW TABLES LIKE '{table_name}'"
result = await self.mysql_manager.execute_query(query)
return bool(result)
except Exception:
return False
def _extract_field_suggestions(self, pattern: str, table_name: str) -> List[IndexSuggestion]:
"""提取字段建议"""
suggestions = []
upper_pattern = pattern.upper()
# 分析相等查询
equal_conditions = re.findall(r'(\w+)\s*[=!]\s*[?\w]+', upper_pattern)
for condition in equal_conditions[:5]: # 限制建议数量
field = condition
if field.upper() not in ['AND', 'OR', 'NOT', 'IS', 'NULL', 'EXISTS', 'IN']:
suggestions.append(IndexSuggestion(
table=table_name,
columns=[field.lower()],
index_type='INDEX',
expected_improvement='50-80%',
priority=self._get_priority(field),
reason=f"WHERE条件中频繁使用{field}字段进行等值查询"
))
# 分析范围查询
range_conditions = re.findall(r'(\w+)\s*[><]\s*=?\s*[?\w]+', upper_pattern)
for condition in range_conditions[:3]:
field = condition
suggestions.append(IndexSuggestion(
table=table_name,
columns=[field.lower()],
index_type='INDEX',
expected_improvement='40-70%',
priority='MEDIUM',
reason=f"{field}字段的范围查询可通过索引优化"
))
return suggestions
def _get_priority(self, field: str) -> str:
"""获取字段优先级"""
high_priority_fields = ['id', 'user_id', 'created_at', 'updated_at']
medium_priority_fields = ['email', 'status', 'category_id']
if field.lower() in high_priority_fields:
return 'HIGH'
elif field.lower() in medium_priority_fields:
return 'MEDIUM'
return 'MEDIUM'
async def _analyze_existing_indexes(self) -> List[IndexSuggestion]:
"""分析现有索引"""
suggestions = []
try:
# 获取所有表
tables = await self.mysql_manager.execute_query('SHOW TABLES')
for table_row in tables[:20]: # 限制分析的表数量
table_name = list(table_row.values())[0]
# 检查表的索引
index_info = await self._get_table_index_info(table_name)
table_suggestions = await self._analyze_table_index_health(table_name, index_info)
suggestions.extend(table_suggestions)
except Exception:
# 忽略索引分析错误
pass
return suggestions
async def _get_table_index_info(self, table_name: str) -> List[Dict[str, Any]]:
"""获取表索引信息"""
query = """
SELECT
INDEX_NAME,
COLUMN_NAME,
SEQ_IN_INDEX,
NON_UNIQUE,
INDEX_TYPE
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = %s
ORDER BY INDEX_NAME, SEQ_IN_INDEX
"""
return await self.mysql_manager.execute_query(query, [table_name])
async def _analyze_table_index_health(self, table_name: str, indexes: List[Dict[str, Any]]) -> List[IndexSuggestion]:
"""分析表索引健康状况"""
suggestions = []
# 检查是否缺少主键
has_primary_key = any(idx.get('INDEX_NAME') == 'PRIMARY' for idx in indexes)
if not has_primary_key:
suggestions.append(IndexSuggestion(
table=table_name,
columns=['id'], # 假设主键字段名为id
index_type='PRIMARY',
expected_improvement='80-95%',
priority='HIGH',
reason='表缺少主键索引,这是数据库设计的最佳实践'
))
# 检查是否有重复索引
index_map = {}
for idx in indexes:
index_name = idx.get('INDEX_NAME', '')
column_name = idx.get('COLUMN_NAME', '')
if index_name not in index_map:
index_map[index_name] = []
index_map[index_name].append(column_name)
for index_name, columns in index_map.items():
for other_index_name, other_columns in index_map.items():
if index_name != other_index_name and columns and other_columns:
if self._is_redundant_index(columns, other_columns):
suggestions.append(IndexSuggestion(
table=table_name,
columns=columns,
index_type='INDEX',
expected_improvement='5-10%',
priority='LOW',
reason=f"索引{index_name}与{other_index_name}存在重复,可考虑清理"
))
return suggestions
def _is_redundant_index(self, columns1: List[str], columns2: List[str]) -> bool:
"""检查索引是否冗余"""
if len(columns1) != len(columns2):
return False
return all(col in columns2 for col in columns1)
def _generate_general_index_recommendations(self) -> List[IndexSuggestion]:
"""生成通用索引建议"""
return [
IndexSuggestion(
table='general_recommendation',
columns=['标准建议'],
index_type='INDEX',
expected_improvement='N/A',
priority='MEDIUM',
reason='定期运行慢查询分析以获取针对性的索引优化建议'
),
IndexSuggestion(
table='primary_key_check',
columns=['主键检查'],
index_type='PRIMARY',
expected_improvement='高',
priority='HIGH',
reason='确保所有表都定义了合适的主键索引'
)
]
# =============================================================================
# 查询性能剖析模块
# =============================================================================
class QueryProfilingModule:
"""
查询性能剖析模块
专门用于分析单个SQL查询性能的模块,通过执行EXPLAIN命令分析查询执行计划,
提供详细的性能分析报告和优化建议。帮助识别查询中的性能瓶颈和改进机会。
"""
def __init__(self, mysql_manager: MySQLManager):
"""初始化查询性能剖析模块"""
self.mysql_manager = mysql_manager
async def profile_query(self, sql: str, params: Optional[List[Any]] = None) -> QueryProfileResult:
"""
对特定查询进行性能剖析
通过执行EXPLAIN命令分析查询执行计划,评估查询性能并提供优化建议。
Args:
sql: 要剖析的SQL查询语句
params: 查询参数数组
Returns:
QueryProfileResult: 查询性能剖析结果
"""
try:
# 验证查询
await self.mysql_manager.validate_input(sql, 'query')
# 执行EXPLAIN分析
explain_result = await self._get_explain_result(sql, params)
# 获取执行统计(如果可能)
execution_stats = await self._get_execution_stats(sql, params)
# 分析执行计划并生成建议
recommendations = self._analyze_explain_result(explain_result)
performance_score = self._calculate_performance_score(explain_result, execution_stats)
return QueryProfileResult(
explain_result=explain_result,
execution_stats=execution_stats,
recommendations=recommendations,
performance_score=performance_score
)
except Exception as e:
raise MySQLMCPError(
f"查询性能剖析失败: {str(e)}",
ErrorCategory.DATA_ERROR,
ErrorSeverity.MEDIUM
)
async def _get_explain_result(self, sql: str, params: Optional[List[Any]] = None) -> List[Dict[str, Any]]:
"""获取EXPLAIN结果"""
try:
# 尝试使用EXPLAIN FORMAT=JSON
explain_query = f"EXPLAIN FORMAT=JSON {sql}"
result = await self.mysql_manager.execute_query(explain_query, params)
return result if isinstance(result, list) else [result]
except Exception:
# 如果FORMAT=JSON不可用,使用传统格式
explain_query = f"EXPLAIN {sql}"
result = await self.mysql_manager.execute_query(explain_query, params)
return result if isinstance(result, list) else [result]
async def _get_execution_stats(self, sql: str, params: Optional[List[Any]] = None) -> Dict[str, Any]:
"""获取执行统计信息"""
try:
# 启用查询性能分析
await self.mysql_manager.execute_query('SET profiling = 1')
# 执行查询