MySQL 查询慢又不想动业务：索引加法的三步微优化

CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  user_id BIGINT,
  status TINYINT,
  created_at DATETIME,
  KEY idx_user_status_created (user_id, status, created_at)
);

-- 慢查询（无索引或索引不佳）
SELECT * FROM orders
WHERE user_id = 123 AND status = 1
ORDER BY created_at DESC
LIMIT 20;

-- 优化方式：覆盖索引与顺序
-- 索引顺序与过滤列优先，排序列放末尾
ALTER TABLE orders
ADD INDEX idx_user_status_created (user_id, status, created_at);

-- 观察执行计划
EXPLAIN SELECT * FROM orders
WHERE user_id = 123 AND status = 1
ORDER BY created_at DESC
LIMIT 20;