A Manager's Dilemma

Our team is small—about a dozen people—with no dedicated DBA. When database performance issues arise, they're usually handled on the side by business developers. The slow query log grows every week, but very little of it actually gets digested.

As the technical lead, my problem isn't that "nobody knows how to optimize SQL." It's three more hidden management problems:

First, slow query handling depends on individual availability. If Developer A is in a good mood and has time, they'll carefully review a few log entries; when things get busy, the work piles up. All the knowledge lives in people's heads—if someone goes on vacation, the optimization work stalls.

Second, the quality of recommendations is inconsistent. Optimization proposals from different people vary widely in depth. Some just say "add an index," while others analyze the execution plan. During review, I have no way to quickly judge which recommendations are reliable.

Third, risk is uncontrollable. My biggest fear is someone directly executing a "looks fine" index change on the production database and locking the table. What I need is for suggestions to be structurally reviewed first, not improvised on the fly.

Later I realized AI could fill exactly this gap: it doesn't take vacations, its output format is consistent, and it always "suggests" first rather than "acting" first. So I turned slow query optimization into a semi-automated pipeline.

The Four Stages of the Pipeline

The revamped process works like this:

Stage 1: Log collection and sanitization (manual, 5 minutes).

Every Monday, export last week's slow query log from the database and run it through a simple sanitization script—table names keep their structure, and business data is replaced with placeholders. This step must be manually controlled because it involves data security; I won't let raw data flow directly to any external service.

Stage 2: AI batch translation (automated).

Feed the sanitized logs to the AI and require it to output analysis reports in a unified format. This is the core of the entire pipeline—more on this below.

Stage 3: Human review (by a senior engineer, 30 minutes).

The AI report first goes to a senior developer for review, who marks items as "approve / questionable / reject." This step cannot be skipped—AI suggestions are the starting point, not the end point.

Stage 4: Scheduling and execution (existing process).

Approved recommendations enter the normal change management process, with review and gradual rollout. AI changed the "production efficiency" of recommendations, but not the "deployment discipline."

The Core Prompt Template

The prompt used in Stage 2 stabilized after several iterations—here it is for reference:

你是一位资深数据库性能优化顾问。以下是脱敏后的MySQL慢查询日志片段。
请对每条慢查询输出以下结构化分析：

## 查询 N
1. 【问题摘要】一句话说明这条SQL慢的可能原因
2. 【原因分析】基于SQL结构推断，涉及哪些字段、可能的扫描方式
3. 【优化建议】按优先级列出，每条注明：
   - 预期收益（高/中/低）
   - 执行风险（是否锁表、是否影响写入）
   - 验证方法（执行前后如何对比）
4. 【需要人工确认的信息】列出AI无法从日志判断、
   必须由DBA或开发确认的前置条件（如表行数、现有索引）

约束：
- 不要给出直接执行的DDL语句，只描述变更意图
- 信息不足时明确说"信息不足"，不要猜测
- 按影响程度排序输出

慢查询日志：
{粘贴脱敏后的日志}

Two design decisions are worth explaining. First, the AI is forbidden from producing DDL directly—this prevents anyone from copy-pasting something straight into production. Second, a "requires human confirmation" checklist is mandatory output—this gives the review stage concrete items to check, rather than a directionless skim.

Before and After

A typical week before the change:

Slow query logs pile up, handled by whoever has time. In a month, fewer than ten queries actually get addressed, and nothing is documented along the way. New team members have to understand the context of every historical SQL from scratch. On one occasion, a developer added an index based on intuition without assessing the table-locking impact, and production wobbled for ten minutes during peak hours.

A typical week after the change:

On Monday morning, we sanitize and export the logs; within half an hour, the AI produces a structured report covering all slow queries. During the senior developer's review, most low-risk recommendations (such as adding missing composite indexes) are approved directly, while a few high-risk items (such as changes to large tables) get flagged for dedicated discussion. Weekly throughput rises from fewer than ten queries to thirty or forty, and every recommendation comes with a complete analysis record—newcomers can understand the context just by reading the report.

Even more important are the management-level changes:

• Knowledge accumulates. AI reports plus human annotations are themselves a searchable optimization archive.
• Risk moves upstream. The "execution risk" and "requires human confirmation" fields mean dangerous operations get caught at the review stage, not discovered after execution.
• Collaboration becomes clear. Developers no longer decide whether to optimize based on their personal state; the reviewer has clearly defined responsibilities; and as a manager, I can see the week's digestion rate and rejection rate just from the summary data.

A Few Lessons Learned

1. AI will confidently recommend wrong indexes. Our review stage has rejected several composite index suggestions that looked reasonable but had incorrect column ordering. AI reports are maybe 70–80% reliable; the remaining 20–30% is exactly where human value lies.
2. Don't skimp on sanitization. Even for internal tools, I recommend sanitizing by default before sending anything out.
3. Control input size per run. Logs that are too long degrade analysis quality in the latter sections—I recommend batching to within a hundred entries at a time.

Final Thoughts

This kind of "log translation" scenario actually doesn't demand much from the model—any ordinary commercial model will do, and costs mainly come from call volume; check the official pricing page for specifics. If your team is also running similar analysis tasks via API, or wants to centrally manage calls and spending across multiple models, take a look at https://api.thistoken.ai/register —an aggregation portal for managing multiple model providers, quite friendly to independent developers and small teams.

Slow query logs themselves produce no value; it's the slow queries that get digested that produce value. Making "digestion" stable, reviewable, and low-risk is AI's most tangible contribution to this pipeline.

---

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