AI 帮你优化 SQL：5 个真实慢查询的提速方案

SELECT * FROM products 
WHERE name LIKE '%耳机%' 
   OR description LIKE '%耳机%';

-- 方案 A：使用 PostgreSQL 全文搜索
SELECT * FROM products 
WHERE to_tsvector('simple', name || ' ' || description) 
      @@ to_tsquery('simple', '耳机');

-- 方案 B：如果数据量大，建一个搜索专用表
CREATE TABLE product_search (
    product_id INTEGER REFERENCES products(id),
    search_vector tsvector,
    PRIMARY KEY (product_id)
);

-- 插入数据
INSERT INTO product_search (product_id, search_vector)
SELECT id, to_tsvector('simple', name || ' ' || description)
FROM products;

-- 查询
SELECT p.* FROM products p
JOIN product_search ps ON p.id = ps.product_id
WHERE ps.search_vector @@ to_tsquery('简单', '耳机');

SELECT u.name, 
       (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) as order_count,
       (SELECT SUM(amount) FROM orders o WHERE o.user_id = u.id) as total_spent
FROM users u
WHERE u.created_at > '2025-01-01';

SELECT u.name, 
       COALESCE(s.order_count, 0) as order_count,
       COALESCE(s.total_spent, 0) as total_spent
FROM users u
LEFT JOIN (
    SELECT user_id, 
           COUNT(*) as order_count, 
           SUM(amount) as total_spent
    FROM orders 
    GROUP BY user_id
) s ON u.id = s.user_id
WHERE u.created_at > '2025-01-01';

SELECT * FROM logs ORDER BY created_at DESC LIMIT 10000 OFFSET 50000;

-- 方案 A：游标分页（推荐）
SELECT * FROM logs 
WHERE created_at < '上次最后一条的时间戳'
ORDER BY created_at DESC 
LIMIT 100;

-- 方案 B：延迟关联
SELECT l.* FROM logs l
INNER JOIN (
    SELECT id FROM logs 
    ORDER BY created_at DESC 
    LIMIT 100 OFFSET 50000
) AS sub ON l.id = sub.id;

SELECT o.id, o.total, c.name, p.title
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN products p ON o.product_id = p.id
WHERE o.status = 'completed'
  AND o.created_at BETWEEN '2025-01-01' AND '2025-12-31'
  AND c.country = 'CN';

-- orders 表复合索引
CREATE INDEX idx_orders_status_created ON orders(status, created_at) 
INCLUDE (customer_id, product_id, total);

-- customers 表索引
CREATE INDEX idx_customers_country ON customers(country) 
INCLUDE (name, id);

SELECT department, AVG(salary) as avg_salary
FROM employees
WHERE department IN ('技术部', '产品部', '市场部')
GROUP BY department
HAVING AVG(salary) > 15000;

WITH dept_stats AS (
    SELECT department, AVG(salary) as avg_salary
    FROM employees
    WHERE department IN ('技术部', '产品部', '市场部')
    GROUP BY department
)
SELECT department, avg_salary
FROM dept_stats
WHERE avg_salary > 15000;

-- 把慢查询发给 AI
-- Prompt: "这个查询很慢，请分析并给出索引建议"

SELECT * FROM users u
JOIN orders o ON u.id = o.user_id
JOIN items i ON o.id = i.order_id
WHERE u.city = '北京'
  AND o.amount > 100
  AND i.category = '电子产品'
ORDER BY o.created_at DESC;

CREATE INDEX idx_users_city ON users(city) INCLUDE (id);
CREATE INDEX idx_orders_user_amount ON orders(user_id, amount) 
  INCLUDE (id, created_at);
CREATE INDEX idx_items_order_category ON items(order_id, category) 
  INCLUDE (id);