AI 在数据库优化上的应用——慢查询分析和索引建议

适读人群：Java工程师、DBA入门、关注性能的开发者 | 阅读时长：约14分钟 | 核心价值：AI分析MySQL慢查询的真实流程，含实际SQL优化案例

接了一个新项目之后，第一个月就在处理性能问题。

那个系统有一张订单表，1200 万条记录，几个核心查询接口的响应时间在 3-8 秒，早高峰的时候直接超时。

我没有 DBA 背景，以前碰到数据库性能问题，基本就是靠直觉加经验，加一个索引试试、改一下查询结构试试，摸着石头过河。

这次我把 AI 拉进来，用了一套比较系统的分析方法。过程比我想象的顺，但也发现了 AI 在这个领域的明确边界。

---

工具准备

分析慢查询需要：

1. MySQL 慢查询日志（slow_query_log 开启，long_query_time 设置合理，我用的 1 秒）
2. EXPLAIN 输出（分析单条查询的执行计划）
3. SHOW INDEX FROM table（看现有索引）
4. information_schema 里的统计信息（选做）

有了这些数据，AI 才有分析的基础。

---

案例一：典型的全表扫描

从慢查询日志里捞出来的第一条：

# Time: 2024-03-15T09:23:41
# Query_time: 4.328  Lock_time: 0.000  Rows_sent: 47  Rows_examined: 12847392
# SET timestamp=1710494621;
SELECT o.*, u.name, u.phone
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
WHERE o.status = 'PENDING_PAYMENT'
  AND o.created_at > DATE_SUB(NOW(), INTERVAL 24 HOUR)
ORDER BY o.created_at DESC
LIMIT 50;

关键数据：查询了 1284 万行，返回了 47 行。比例 1284万:47，这是非常典型的全表扫描。

把这条慢查询日志记录 + EXPLAIN 输出 + 表结构给 AI 分析：

-- EXPLAIN 输出
+----+-------------+-------+------------+------+---------------+------+---------+------+-----------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows      | filtered | Extra                       |
+----+-------------+-------+------------+------+---------------+------+---------+------+-----------+----------+-----------------------------+
|  1 | SIMPLE      | o     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 12847392  |    0.33  | Using where; Using filesort |
|  1 | SIMPLE      | u     | NULL       | eq_ref| PRIMARY      | PRIMARY | 8   | o.user_id | 1     |   100.00 | NULL                        |
+----+-------------+-------+------------+------+---------------+------+---------+------+-----------+----------+-----------------------------+

-- 表结构
CREATE TABLE orders (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT NOT NULL,
  status VARCHAR(20) NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  -- ... 其他字段
);

-- 现有索引
SHOW INDEX FROM orders:
只有 PRIMARY KEY (id)

AI 的分析和建议：

问题分析：
1. orders 表走了全表扫描（type=ALL，rows=12847392），没有可用索引
2. Using filesort 说明 ORDER BY 也无法利用索引，需要额外排序
3. filtered=0.33% 说明只有 0.33% 的行符合 WHERE 条件，但扫描了全部行

根因：没有覆盖 WHERE 和 ORDER BY 条件的复合索引

索引建议：

方案1（推荐）：复合索引 (status, created_at)
  CREATE INDEX idx_status_created ON orders(status, created_at);
  
  优点：能利用等值查询（status='PENDING_PAYMENT'）+ 范围查询（created_at >...）
  的组合，MySQL 可以用索引直接定位，然后在有限行范围内排序
  
  预期效果：扫描行数从 1284 万降到 status='PENDING_PAYMENT' 且在24小时内的
  行数，可能是几百到几千行，性能提升会非常显著

方案2：覆盖索引 (status, created_at, user_id, amount)
  包含查询需要的列，避免回表
  但这个查询还 JOIN 了 users 表，o.* 也包含所有字段，覆盖索引收益有限

建议先加 idx_status_created，验证效果后再决定是否需要覆盖索引。

额外注意：
- 如果 status 的区分度很低（比如只有5种状态），这个索引效果取决于各状态的数据分布
- 建议先用 SELECT COUNT(*) FROM orders WHERE status='PENDING_PAYMENT' 
  查一下这个状态的数据量，如果占总量 30%+，索引效果会打折扣

我去查了一下 PENDING_PAYMENT 状态的数据量：大约 48 万条，占总量约 3.7%。

加索引之后，EXPLAIN 的 type 从 ALL 变成了 range，rows 从 1284 万降到约 5200，查询时间从 4.3 秒降到 0.08 秒。

---

案例二：AI 发现了我没想到的问题

第二条慢查询：

# Query_time: 2.847  Rows_sent: 1  Rows_examined: 12847392
SELECT COUNT(*) FROM orders
WHERE user_id = 10086
  AND status NOT IN ('CANCELLED', 'REFUNDED');

我最初的直觉是：给 user_id 加个索引。

AI 的分析：

观察：
1. 只返回 1 行（COUNT 结果），但扫描了 1284 万行，走了全表扫描
2. WHERE 条件包含 user_id 等值查询 + status NOT IN 范围查询

直觉方案（只给 user_id 加索引）的问题：
- user_id 索引确实能定位到这个用户的所有订单
- 但 status NOT IN 需要回表检查每条记录的 status 值
- 如果 user_id=10086 有大量订单，回表成本仍然很高

推荐方案：复合索引 (user_id, status)
  CREATE INDEX idx_user_status ON orders(user_id, status);
  
  MySQL 可以用 (user_id=10086 AND status NOT IN (...)) 在索引里直接过滤，
  减少甚至避免回表
  
  更好的方案（如果业务允许）：
  把 NOT IN 改成 IN 的正向筛选
  即：AND status IN ('PENDING_PAYMENT', 'PAID', 'SHIPPED', 'COMPLETED')
  
  IN 在索引里的利用率通常比 NOT IN 更好
  而且如果将来加了新的状态，NOT IN 的逻辑会自动包含新状态，
  可能是一个隐藏的业务逻辑风险

这个建议里最有价值的一点，不是索引建议，而是"NOT IN 的业务逻辑风险"。

我去确认了一下，结果确实存在这个问题：系统后来加了一个 DISPUTE（争议）状态，按照原来 NOT IN 的逻辑，争议中的订单会被统计进去，这是错误的业务逻辑，而且这个 bug 沉默地存在了几个月没被发现。

这是 AI 分析数据库查询时的一个额外价值：它会顺带检查业务逻辑，不只是看性能。

---

案例三：AI 建议我不要加索引

第三条慢查询：

# Query_time: 1.234  Rows_sent: 1  Rows_examined: 12847392
SELECT COUNT(*) FROM orders
WHERE DATE(created_at) = '2024-03-15';

AI 的分析：

问题：DATE(created_at) 是一个函数，作用在列上

根本原因：
对 created_at 加函数后，即便 created_at 有索引，MySQL 也无法直接用索引
定位，必须全表扫描对每行计算 DATE(created_at) 的值

有两个解决方向：

方向1（推荐）：改写查询，不用函数
  SELECT COUNT(*) FROM orders
  WHERE created_at >= '2024-03-15 00:00:00'
    AND created_at < '2024-03-16 00:00:00';
  
  然后给 created_at 加索引（如果没有），就能走 range 扫描
  不需要新加任何索引，只改查询语句

方向2：MySQL 8.0+ 支持函数索引
  CREATE INDEX idx_date_created ON orders((DATE(created_at)));
  但这个方案：
  - 需要 MySQL 8.0+（你的版本是 5.7）
  - 只对这一种用法有效，其他用 created_at 的查询不受益
  - 维护成本更高
  
建议：先试方向1，不需要改表结构，只改 SQL

我去找了这条 SQL 的调用方，是一个报表接口，改了查询语句，created_at 上已经有索引，查询时间从 1.2 秒降到 0.006 秒。改动只有一行 SQL，没有加任何索引。

---

AI 在数据库优化上的边界

几个月用下来，我对 AI 在这个场景里的能力有了比较清醒的认识：

AI 擅长的：

• 解读 EXPLAIN 输出（各字段的含义和问题诊断）
• 识别常见的查询反模式（函数作用在列上、SELECT *、IN 子查询等）
• 给出复合索引的设计建议
• 发现查询语句里的逻辑问题（像我上面案例二的 NOT IN 风险）

AI 不擅长的（或者说需要你补充信息的）：

• 不了解你的数据分布（索引的有效性高度依赖数据分布，你必须提供 COUNT 等统计信息）
• 不了解你的读写比例（高写入场景下加太多索引有明显的写性能代价）
• 不了解你的业务增长趋势（1200 万行现在够用的索引，一年后可能不够）
• 做不了真实的基准测试（最终的性能数字需要在你的环境里实测）

AI 给的是有根据的方向和建议，不是可以直接执行的操作手册。每条建议你都需要在测试环境验证，确认效果后才上生产。

---

我的分析流程模板

【慢查询分析包】
1. 慢查询日志记录（Query_time, Rows_examined, Rows_sent）
2. EXPLAIN SELECT ...（完整输出）
3. SHOW CREATE TABLE 表名（表结构和现有索引）
4. SELECT COUNT(*) 的关键过滤条件数据量（估算数据分布）
5. 表的总行数

【提问模板】
以上是一条生产慢查询的完整信息。请：
1. 解释 EXPLAIN 输出里的主要问题点
2. 分析根本原因
3. 给出2-3个优化方案，并说明各自的适用条件和预期效果
4. 有没有发现查询语句本身的逻辑问题（不只是性能问题）
5. 如果需要更多信息才能准确判断，告诉我需要什么

把这个流程固定下来，每次慢查询分析都能快速拿到有质量的分析结果。