影刀RPA 数据库索引优化：慢查询自动分析与建议

影刀RPA 数据库索引优化慢查询自动分析与建议作者林焱 | 分类影刀RPA新手教程 | 难度★★★什么情况用数据库慢查询越来越多DBA每周要手动跑慢查询日志分析看哪些SQL需要加索引。但DBA不一定知道每个SQL对应哪个业务功能加错了索引反而拖慢写入性能。用影刀RPA定时从MySQL慢查询日志中提取TOP N慢SQL分析执行计划自动生成索引优化建议推送给对应的开发负责人确认。怎么做第一步连接数据库获取慢查询拼多多店群自动化报活动上架# 影刀Python节点从MySQL获取慢查询importpymysql DB_CONFIG{host:192.168.1.100,user:dba_readonly,password:***,database:mysql,# 慢查询存在mysql库的slow_log表}connpymysql.connect(**DB_CONFIG)defget_slow_queries(conn,limit20):获取最近TOP N慢查询sql SELECT start_time, user_host, query_time, lock_time, rows_sent, rows_examined, db, sql_text FROM slow_log ORDER BY query_time DESC LIMIT %s withconn.cursor(pymysql.cursors.DictCursor)ascursor:cursor.execute(sql,(limit,))returncursor.fetchall()slow_queriesget_slow_queries(conn,20)forqinslow_queries:print(f⌛{q[query_time]}| 扫描{q[rows_examined]}行 | 返回{q[rows_sent]}行)print(f{q[sql_text][:120]}...)第二步分析执行计划# 影刀Python节点EXPLAIN分析defanalyze_explain(conn,sql_text):对SQL执行EXPLAIN分析是否用了索引try:# 去掉开头的特殊字符慢查询日志可能包含时间戳前缀clean_sqlsql_text.split(;)[0].strip()withconn.cursor(pymysql.cursors.DictCursor)ascursor:cursor.execute(fEXPLAIN{clean_sql})explain_resultcursor.fetchall()issues[]forrowinexplain_result:# typeALL 表示全表扫描严重ifrow.get(type)ALL:issues.append({table:row.get(table),problem:全表扫描(ALL),severity:严重,})# Extra包含Using filesort需要优化elifUsing filesortinstr(row.get(Extra,)):issues.append({table:row.get(table),problem:文件排序(Using filesort),severity:中等,})# Extra包含Using temporary需要优化elifUsing temporaryinstr(row.get(Extra,)):issues.append({table:row.get(table),problem:使用临时表(Using temporary),severity:中等,})returnissuesexceptExceptionase:return[{problem:fEXPLAIN执行失败{e},severity:未知}]第三步生成索引建议# 影刀Python节点智能索引建议importredefsuggest_indexes(sql_text,explain_issues):根据SQL和EXPLAIN结果给出索引建议suggestions[]# 提取WHERE条件中的字段where_clausere.search(rWHERE\s(.?)(?:ORDER BY|GROUP BY|LIMIT|$),sql_text,re.IGNORECASE|re.DOTALL)ifwhere_clause:conditionswhere_clause.group(1)# 提取字段名简化版实际需要SQL解析器fieldsre.findall(r(\w\.?\w*)\s*(?:|||||LIKE|IN|BETWEEN),conditions,re.IGNORECASE)forissueinexplain_issues:tableissue.get(table,unknown)iffields:suggestionf建议ALTER TABLE {table} ADD INDEX idx_optimize ({, .join(fields[:3])});suggestions.append(suggestion)# 检查是否有ORDER BYorder_byre.search(rORDER BY\s(.?)(?:LIMIT|$),sql_text,re.IGNORECASE|re.DOTALL)iforder_by:order_fields[f.strip().split()[0]forfinorder_by.group(1).split(,)]iforder_fields:# 联合索引WHERE字段 ORDER BY字段combinedfields[:2]order_fields[:1]suggestions.append(f进阶建议ALTER TABLE {table} ADD INDEX idx_cover ({, .join(combined)});覆盖索引消除filesort)returnsuggestionsforqinslow_queries:issuesanalyze_explain(conn,q[sql_text])ifissues:print(f\n{q[sql_text][:80]})forissueinissues:print(f{issue[severity]}:{issue[problem]})suggestionssuggest_indexes(q[sql_text],issues)forsinsuggestions:print(f {s})第四步影响评估# 影刀Python节点评估索引对写入的影响defassess_write_impact(table_name):评估目标表的写入频率写入频繁的表慎加索引sqlf SELECT TABLE_ROWS, round(DATA_LENGTH/1024/1024, 1) as data_mb, round(INDEX_LENGTH/1024/1024, 1) as index_mb FROM information_schema.TABLES WHERE TABLE_SCHEMA DATABASE() AND TABLE_NAME %s withconn.cursor(pymysql.cursors.DictCursor)ascursor:cursor.execute(sql,(table_name,))infocursor.fetchone()ifinfo:current_indexesinfo[index_mb]total_sizeinfo[data_mb]ifcurrent_indexes/(total_size1)0.5:return{warning:True,msg:f已有索引占总空间的{current_indexes/(total_size1)*100:.0f}%加索引需谨慎}else:return{warning:False,msg:可以安全添加索引}return{warning:False}第五步生成优化周报将分析结果汇总为优化周报内容包括本周新增慢查询TOP 10及执行计划建议添加的索引及DDL语句预计优化效果扫描行数→索引扫描行数风险评估写入负载、索引大小影刀【企微推送】给开发负责人、【邮件】附带详细报告。有什么坑坑1EXPLAIN结果不等于实际执行计划TEMU店群矩阵自动化运营核价报活动在测试环境或分析工具中EXPLAIN的结果和MySQL优化器实际选择的执行计划可能不同特别是数据量差异大的时候。建议在生产环境只读账号下执行EXPLAIN。坑2自动生成索引建议可能不准确正则表达式无法完全解析复杂SQL子查询、JOIN、UNION等。自动建议只能作为参考最终是否加索引需要DBA判断。RPA的角色是筛选可疑SQL不是替代DBA。坑3索引不是越多越好每多一个索引INSERT/UPDATE/DELETE都要多维护一个BTree结构。写入密集型表加索引后写入性能可能下降30-50%。需要在报告中强调这一点。坑4慢查询日志需要开启MySQL默认不开启慢查询日志。需要在my.cnf中配置slow_query_log1和long_query_time11秒以上的查询记录。坑5慢查询可能来自只跑了一次的临时查询开发人员的临时数据分析查询可能扫了全表跑了几分钟但它不是高频查询加索引意义不大。建议分析时结合慢查询的出现频率只关注重复出现的慢SQL。总结索引优化的关键是ROI评估——花费索引维护代价能换来多少查询性能提升。RPA帮你找出候选SQL并生成建议DBA评估风险后决定是否执行。人和机器的分工配合才能达到最佳效果。