A Manager's Real Dilemma

As the leader of a small five-person team, I've been through this scenario far too many times:

One Tuesday afternoon, someone from operations says in the group chat, "The backend is lagging again." The developers investigate and trace it to a slow SQL query. Then the problem emerges—this SQL was written three years ago by a colleague who has since left the company, running on an order table with over four million rows, with no index hints, joining three tables, plus a subquery.

The bigger problem is the team's situation: only one person truly understands SQL optimization, and he's half a DBA anyway. Others can read the fields in the slow query log, but they lack confidence on questions like "why does this query take eight seconds" or "will changing the index affect writes." The result is that all slow queries pile up on that one person, and he becomes the bottleneck. The week he took vacation, optimization work came to a complete standstill.

From a management perspective, there are actually three problems here:

1. Concentrated capability: Optimization knowledge lives in individuals' heads instead of being consolidated into team processes.
2. Slow response: From discovering a slow query to producing an optimization plan, it takes an average of one to two days.
3. Uncontrolled risk: Occasionally someone would change SQL or add indexes based on gut feeling, which once caused an incident with degraded write performance in production—after that, everyone became even more afraid to touch anything.

What AI Can Do in This Pipeline

Our approach: don't let AI directly modify the database. Instead, let it handle the "translation" work from slow query logs to structured optimization suggestions. AI outputs recommendations and risk warnings; decisions and execution remain with humans.

The redesigned workflow looks like this:

Step 1: Scheduled collection of slow query logs. Use a script to periodically pull slow query logs, sorted by execution frequency and time consumed, and filter out the Top N entries.

Step 2: AI batch translation. Feed each slow query along with the table structure (DDL) to a large language model, and have it output an analysis report in a standardized format: what the problem is, why it's slow, proposed solutions, risk points, and verification methods.

Step 3: Human review. Hold a 15-minute review meeting once a week where the team goes through the AI's suggestions together. People who understand SQL act as gatekeepers, while others participate in the discussion. This step unexpectedly became an excellent teaching scenario—junior members quickly built optimization intuition by reviewing AI suggestions.

Step 4: Gradual rollout and regression. Adopted suggestions go through the change management process: first verify the execution plan on a replica or in a test environment, then roll out gradually.

AI's role here is clear: it's that junior DBA who never takes vacation, never complains, and responds in seconds. It takes on all the grunt work (reading logs, analyzing execution logic, drafting initial plans) while leaving judgment to the team.

A Ready-to-Use Prompt Template

This is the template we actually use in our pipeline. With API calls, you can take it and adapt it directly:

你是一位资深的MySQL性能优化工程师。请分析以下慢查询，并按固定格式输出报告。

【慢查询信息】
- 执行耗时：{{query_time}} 秒
- 扫描行数：{{rows_examined}}
- 返回行数：{{rows_sent}}
- 执行频率：每天约 {{frequency}} 次
- SQL语句：
{{sql}}

【相关表结构】
{{table_ddl}}

【请严格按以下格式输出】
1. 问题诊断：这条SQL慢的根本原因（逐条列出，说明判断依据）
2. 优化建议：给出2-3个方案，按推荐度排序，说明每个方案的预期收益
3. 风险提示：每个方案可能带来的副作用（如对写入性能、锁、其他查询的影响）
4. 验证方法：如何确认优化有效（如EXPLAIN应观察哪些指标）
5. 不确定项：明确指出基于现有信息无法判断、需要人工补充的内容

要求：
- 不要臆测不存在的索引或字段
- 涉及加索引时，评估组合索引的列顺序
- 如果建议改写SQL，给出改写前后对照

The last item, "uncertainties," is crucial—it forces the AI to acknowledge its boundaries, and these uncertainty items conveniently form the agenda for the human review meeting.

Before and After: Not Just Faster

The benefit that surprised me most wasn't speed, but that risks became discussable. Before, "should we add this index" was a black-box judgment call. Now, AI will write things like "this index may increase write latency; recommend creating it during off-peak hours" in the report, and the review meeting discusses concrete risks instead of second-guessing each other.

On cost, mid-tier models are more than capable for this kind of analysis task. The cost per query analysis is negligible—check the official pricing page for specifics. For independent developers, even without a team, one person can run this entire pipeline.

A Few Management Reminders

1. AI never touches the production database. Draw a hard boundary for the pipeline: AI only reads logs and DDL and produces documents. Any changes go through human approval.
2. Prompt templates are team assets. We keep the template in a shared repository. Every time a review reveals a flaw in AI output, we iterate on the template—this is the highest-ROI "training investment."
3. Watch out for AI's overconfidence. Occasionally it produces solutions that seem reasonable but aren't supported by the execution plan, so the EXPLAIN verification step can't be skipped.
4. Start with the Top 3, don't overreach. First run the full workflow on the three most frequent slow queries, then scale up once the pattern is validated.

Turning slow query logs into input that AI can translate is, in essence, converting an implicit disease that depends on individual experience into a routine, well-paced, documented, and reviewed process. For independent developers and small teams, this kind of "AI drafts, humans decide" process transformation often delivers far better ROI than grand AI narratives.

If you want to implement a similar AI pipeline in your team, you can start by registering for a stable model API service to experiment: https://api.thistoken.ai/register —starting with the translation of a single slow query is much easier than you'd think.

---

Every example in this post runs with a single API key — get yours at https://api.thistoken.ai/register and start in minutes.