SQL 查询优化通用思路：执行计划、索引与深翻页

-- 看实际执行耗时
SET profiling = 1;
SELECT ... ;
SHOW PROFILES;

-- 看执行计划
EXPLAIN SELECT ... ;

CREATE INDEX idx_status ON orders (status);
SELECT * FROM orders WHERE status = "paid";

SELECT * FROM orders WHERE created_at >= "2026-01-01";

-- 索引 (status, created_at)
SELECT status, created_at FROM orders WHERE status = "paid";
-- Extra 会显示 Using index，不需要回表

-- 查询：WHERE status = ? AND created_at >= ?
-- 正确：(status, created_at)
-- 错误：(created_at, status) —— 范围列在前，status 用不上

-- 失效
WHERE DATE(created_at) = "2026-09-23"

-- 有效：改成范围
WHERE created_at >= "2026-09-23 00:00:00"
  AND created_at <  "2026-09-24 00:00:00"

-- 假设 users 有 10 万行，orders 里某用户只有 20 行
-- 好的顺序：先用条件把 users 缩到 1 行，再去 orders 里查
SELECT u.name, o.amount
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.email = "a@b.com";

-- 第 1 页很快
SELECT * FROM article ORDER BY id LIMIT 10 OFFSET 0;

-- 第 10000 页很慢
SELECT * FROM article ORDER BY id LIMIT 10 OFFSET 100000;

SELECT * FROM article
WHERE id > 100000
ORDER BY id LIMIT 10;

SELECT a.* FROM article a
JOIN (SELECT id FROM article ORDER BY id LIMIT 10 OFFSET 100000) t
  ON a.id = t.id;