An Engineering Manager's Perspective: The Dilemma

As the technical lead of a small team, I've been through this scenario far too many times: the business side reports "the system is getting slow," engineers spend half a day investigating, and finally trace it back to a few slow queries dragging down the database. Everyone knows the solution—add indexes. But the real problem isn't the phrase "add indexes"—it's the entire workflow behind it:

Slow query logs often contain hundreds or thousands of entries—who's going to filter them? Who analyzes the filtered SQL to determine what indexes to create? Will creating indexes lock tables or impact writes? Who verifies the results after deployment? Who takes responsibility if performance regresses?

In a team without a dedicated DBA, the answer to these questions is usually "whoever's free." As a result, index optimization becomes a high-risk, low-priority task nobody wants to touch—until one day the database CPU spikes to 90%, and everyone is forced into firefighting mode.

What worried me even more was risk control: letting junior engineers run CREATE INDEX directly on the production database means that if indexes are created incorrectly or excessively, not only does it fail to solve the problem, it also slows down writes and wastes storage. Index governance is essentially an action that requires "review," but small teams often lack both the manpower and the process to conduct reviews.

That's why I brought AI into this workflow: not to replace human decision-making, but to hand the repetitive work of "analysis, recommendation, and initial review" to AI, leaving humans to do only the final gatekeeping.

What AI Can Do in This Process

Let me first clarify the boundaries. What AI doesn't do: it doesn't connect directly to production databases, doesn't execute any DDL, and doesn't make final decisions. What AI does:

1. Log cleaning and aggregation: Deduplicate and categorize messy slow query logs, identifying the Top N high-frequency, high-cost SQL statements.
2. SQL analysis: Parse WHERE, JOIN, and ORDER BY conditions to determine whether existing indexes are being hit.
3. Index recommendations: Suggest column ordering for composite indexes, with explanations of the reasoning.
4. Preliminary risk assessment: Flag which tables have frequent writes, whether the suggested number of indexes exceeds limits, and whether online index creation can be used.
5. Generate review documentation: Automatically produce a change description that all team members can understand, for use in deployment reviews.

The entire process becomes: export logs → AI analysis → human review → execute during off-peak hours → AI-assisted verification of results. Humans are clearly positioned at the two highest-risk points.

The Actual Workflow

Our team's specific approach consists of five steps:

Step 1: Sanitized export. Export slow query logs from the database (for MySQL, this is slow.log, or query via performance_schema), then sanitize them first—replace specific parameter values with placeholders and remove fields containing user information. This step must be done by a human; it's the first gate of data security.

Step 2: Feed to AI for analysis. Provide the sanitized logs together with the table structures (the output of SHOW CREATE TABLE) to the AI. Note: providing table structures is critical—otherwise the AI can only guess at indexes and cannot determine whether existing reusable indexes are already in place.

Step 3: Get structured recommendations. I require the AI to output in a fixed format: each recommendation includes the original SQL, problem diagnosis, suggested index DDL, expected benefits, and risk warnings. The benefit of a unified format is that team members don't need to adapt to different modes of expression during review.

Step 4: Human review meeting. Walk through the AI's recommendation list in a 15-minute standup, where engineers tag each item as "adopt / shelve / reject." Experience shows that the adoption rate of AI recommendations is around 70%—the rejected 30% is usually due to the AI lacking business context (for example, a table that's about to be deprecated), which is exactly where human gatekeeping adds value.

Step 5: Gradual execution and verification. Execute items one by one during off-peak hours, capture query latency before and after each execution for comparison, then have the AI help generate a verification report.

A Reusable Prompt Template

This is the template our team has refined—it works with any large model that supports long contexts:

你是一位数据库性能优化顾问。请根据我提供的慢查询日志和表结构,给出索引优化建议。

【输入材料】
1. 慢查询日志(已脱敏):
<在此粘贴慢查询日志>

2. 相关表结构(SHOW CREATE TABLE 输出):
<在此粘贴建表语句>

3. 数据库类型与版本:
<如 MySQL 8.0>

【输出要求】
请按以下格式逐条输出,不要省略任何一栏:

## 建议 N
- 原始SQL:(粘贴日志中的代表性SQL)
- 出现频次与平均耗时:(从日志统计)
- 问题诊断:(说明为何现有索引未命中,引用WHERE/JOIN/ORDER BY条件)
- 建议索引DDL:(给出完整CREATE INDEX语句,注明复合索引的列顺序及理由)
- 预期收益:(预估该查询的扫描行数变化)
- 风险提示:(写入放大、索引冗余、是否建议改用 ONLINE 方式、是否建议合并到已有索引)
- 验证方法:(执行后应观察什么指标)

【约束】
1. 不确定的业务背景请明确标注"需人工确认",不要臆测
2. 单表建议索引总数超过5个时,请给出取舍建议
3. 如发现SQL本身可改写(如SELECT *、隐式类型转换),请单独列出
4. 最后输出一份汇总表格,按"预期收益/风险等级"排序,便于评审

Before and After: From "Firefighting" to "Routine"

The changes before and after the transformation, described using our team's real experience:

On cost: this process only consumes model token fees, which are practically a rounding error compared to the engineer-hours invested. For specific pricing, refer to the official pricing page—costs vary significantly between models. I recommend first running the process with a mid-cost model, then evaluating whether a more powerful model is needed.

A Few Reminders for Managers

First, the sanitization step cannot be left to AI's discretion. Whether logs contain sensitive data must be confirmed by a human before submission. Second, treat AI recommendations as "review materials" rather than "execution scripts"—that final human confirmation cannot be skipped. Third, codify the prompt template into team documentation to avoid everyone writing their own and producing format drift.

Index optimization is just the beginning. The same "AI initial review + human final review" pattern can be replicated to scenarios like slow SQL rewriting, capacity alerting, and inspection reports. For teams that want to try it hands-on, you can register an API account and get started directly: https://api.thistoken.ai/register

---

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