示例
示例
ALTER TABLE orders ADD INDEX idx_user_created (user_id, created_at);
-- 可避免排序：等值列加排序列与索引一致
EXPLAIN SELECT * FROM orders
WHERE user_id = 100
ORDER BY created_at DESC
LIMIT 20;
-- 会引入排序：排序键与索引顺序不一致
EXPLAIN SELECT * FROM orders
WHERE user_id = 100
ORDER BY total DESC
LIMIT 20; -- Using filesort / Sort 节点| 匹配要点 | WHERE 用索引前导列、ORDER BY 接续剩余列且方向一致 |
|---|---|
| 小 LIMIT | 排序无法避免时，尽量缩小参与排序的行数 |
| 表达式排序 | ORDER BY 表达式无法用普通索引，需要函数索引或改写 |