EXPLAIN 与运维
→ 返回 MySQL
EXPLAIN
EXPLAIN FORMAT=TREE SELECT ...; -- 8.0.16+
EXPLAIN ANALYZE SELECT ...; -- 8.0.18+ 真实统计| 字段 | 关注 |
|---|---|
| type | 避免 ALL |
| key | 预期索引 |
| rows | 扫描行数 |
| Extra | Using index 好；filesort / temporary 需优化 |
慢日志、pt-query-digest：SQL 优化。
深分页
-- ❌
SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;
-- ✅ 游标
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 20;日志与复制
| 日志 | 作用 |
|---|---|
| redo log | 崩溃恢复 |
| undo log | 回滚、MVCC |
| binlog | 主从、CDC |
两阶段提交：redo prepare → binlog → redo commit。
分区表
CREATE TABLE orders (
id BIGINT, created_at DATE
)
PARTITION BY RANGE (YEAR(created_at)) (
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION p2024 VALUES LESS THAN (2025),
PARTITION p_max VALUES LESS THAN MAXVALUE
);WHERE 需能分区裁剪。