用 AI 优化 SQL 和数据库性能

Prompt：分析以下 SQL 的性能问题，给出优化建议：

SELECT u.*, o.*, p.*
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
LEFT JOIN products p ON o.product_id = p.id
WHERE u.status = 'active'
AND o.created_at > '2024-01-01'
ORDER BY o.created_at DESC
LIMIT 100;

问题分析：

1. SELECT * 问题
   - 查询了所有字段，浪费带宽和内存
   - 建议只查询需要的字段

2. 索引缺失
   - users 表需要索引：(status)
   - orders 表需要索引：(user_id, created_at)

3. JOIN 顺序
   - LEFT JOIN 可能导致全表扫描
   - 如果 orders 是主要查询表，考虑调整 JOIN 顺序

4. 子查询优化
   - 可以用子查询先过滤，再 JOIN

优化后的 SQL：
SELECT u.name, u.email, o.order_no, o.total, p.product_name
FROM orders o
INNER JOIN users u ON u.id = o.user_id AND u.status = 'active'
LEFT JOIN products p ON o.product_id = p.id
WHERE o.created_at > '2024-01-01'
ORDER BY o.created_at DESC
LIMIT 100;

索引建议：
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at);
CREATE INDEX idx_users_status ON users(status);

Prompt：根据以下查询模式，给出 MySQL 索引建议：

常用查询：
1. SELECT * FROM orders WHERE user_id = ?
2. SELECT * FROM orders WHERE status = ? AND created_at > ?
3. SELECT COUNT(*) FROM orders WHERE user_id = ? GROUP BY status
4. SELECT * FROM orders ORDER BY created_at DESC LIMIT 20

表结构：
CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id BIGINT,
    status VARCHAR(20),
    total DECIMAL(10,2),
    created_at TIMESTAMP
);

-- 查询每个部门工资最高的员工
SELECT * FROM employees e1
WHERE salary = (
    SELECT MAX(salary) FROM employees e2
    WHERE e2.department_id = e1.department_id
);

-- 使用窗口函数（更高效）
SELECT * FROM (
    SELECT *,
        RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as rn
    FROM employees
) ranked
WHERE rn = 1;

-- 或者使用 JOIN
SELECT e.* FROM employees e
INNER JOIN (
    SELECT department_id, MAX(salary) as max_salary
    FROM employees
    GROUP BY department_id
) d ON e.department_id = d.department_id AND e.salary = d.max_salary;

Prompt：设计一个电商系统的数据库表结构，要求：
- 支持用户、商品、订单、购物车
- 考虑性能和扩展性
- 使用 MySQL
- 给出建表 SQL 和索引建议

Prompt：分析以下 MySQL 执行计划，找出性能瓶颈：

EXPLAIN SELECT * FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'completed'
AND o.created_at > '2024-01-01';

执行计划结果：
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | o     | ALL  | NULL          | NULL | NULL    | NULL | 100000 | Using where |
|  1 | SIMPLE      | u     | eq_ref | PRIMARY     | PRIMARY | 8   | o.user_id | 1 |          |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+

Prompt：将以下 MySQL SQL 转换为 PostgreSQL：

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

SELECT * FROM users LIMIT 10 OFFSET 20;

Prompt：将以下 SQL 转换为 JPA Repository 方法：

SELECT * FROM users WHERE email = ? AND status = ?
ORDER BY created_at DESC LIMIT 10;

-- MySQL 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

-- 查看慢查询
SHOW VARIABLES LIKE 'slow_query_log%';

Prompt：分析以下慢查询，给出优化方案：
[粘贴 SQL 和执行计划]

-- 优化后再次执行
EXPLAIN [优化后的 SQL];

-- 反模式
SELECT * FROM users;

-- 优化
SELECT id, name, email FROM users;

-- 反模式：循环查询
for user in users:
    SELECT * FROM orders WHERE user_id = user.id;

-- 优化：批量查询
SELECT * FROM orders WHERE user_id IN (1, 2, 3, ...);

-- 反模式
SELECT * FROM users WHERE id IN (
    SELECT user_id FROM orders WHERE total > 1000
);

-- 优化：JOIN
SELECT DISTINCT u.* FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.total > 1000;

Prompt：生成数据库迁移脚本：
- 给 orders 表添加 discount 字段（DECIMAL(10,2)）
- 给 orders 表添加索引 idx_status_created(status, created_at)
- 兼容 MySQL 和 PostgreSQL