-- 在 SELECT 前加 EXPLAIN
EXPLAIN SELECT * FROM orders WHERE user_id = 100;

-- EXPLAIN ANALYZE（8.0.18+，显示实际执行时间）
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 100;

-- 格式化输出
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE user_id = 100;
EXPLAIN FORMAT=TREE SELECT * FROM orders WHERE user_id = 100;

-- const：主键等值查询
EXPLAIN SELECT * FROM users WHERE id = 100;
-- type: const, key: PRIMARY

-- eq_ref：JOIN 时主键匹配
EXPLAIN SELECT * FROM orders o JOIN users u ON o.user_id = u.id;
-- users 表的 type: eq_ref

-- ref：普通索引等值
EXPLAIN SELECT * FROM orders WHERE user_id = 100;
-- type: ref, key: idx_user_id

-- range：范围扫描
EXPLAIN SELECT * FROM orders WHERE created_at >= '2026-01-01';
-- type: range

-- index：全索引扫描
EXPLAIN SELECT user_id FROM orders;
-- type: index（只扫描索引树，不读数据）

-- ALL：全表扫描
EXPLAIN SELECT * FROM orders WHERE YEAR(created_at) = 2026;
-- type: ALL（索引失效）

-- Using index（覆盖索引）
EXPLAIN SELECT email, status FROM users WHERE email = '[email protected]';
-- Extra: Using index

-- Using filesort（需要优化）
EXPLAIN SELECT * FROM users ORDER BY created_at DESC;
-- 如果 created_at 没有索引 → Extra: Using filesort

-- Using temporary（需要优化）
EXPLAIN SELECT DISTINCT username FROM users;
-- 可能使用临时表去重

-- Using index condition（索引下推）
EXPLAIN SELECT * FROM orders WHERE user_id > 100 AND status = 1;
-- Extra: Using index condition（MySQL 5.6+ 的 ICP 优化）

-- 查看当前状态
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;          -- 超过 1 秒记录
SET GLOBAL log_queries_not_using_indexes = ON;  -- 记录未使用索引的查询
SET GLOBAL min_examined_row_limit = 100;        -- 至少扫描 100 行才记录

-- 永久生效需写入 my.cnf
-- [mysqld]
-- slow_query_log = ON
-- slow_query_log_file = /var/log/mysql/slow.log
-- long_query_time = 1
-- log_queries_not_using_indexes = ON

# 使用 mysqldumpslow 工具
# 按查询时间排序，显示前 10 条
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# 按出现次数排序
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

# 按锁时间排序
mysqldumpslow -s l -t 10 /var/log/mysql/slow.log

# 使用 pt-query-digest（Percona Toolkit，更强大）
pt-query-digest /var/log/mysql/slow.log > slow_report.txt

# 只分析最近 1 小时的慢查询
pt-query-digest --since '1h' /var/log/mysql/slow.log

-- 查看最耗时的 SQL（按总执行时间排序）
SELECT 
    DIGEST_TEXT AS query,
    COUNT_STAR AS exec_count,
    ROUND(SUM_TIMER_WAIT / 1e12, 2) AS total_time_s,
    ROUND(AVG_TIMER_WAIT / 1e12, 4) AS avg_time_s,
    SUM_ROWS_EXAMINED AS rows_examined,
    SUM_ROWS_SENT AS rows_sent
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = 'myapp'
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

-- 查看全表扫描的查询
SELECT 
    DIGEST_TEXT,
    COUNT_STAR,
    SUM_ROWS_EXAMINED,
    SUM_ROWS_SENT
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = 'myapp'
  AND SUM_ROWS_EXAMINED > SUM_ROWS_SENT * 10  -- 扫描行远大于返回行
ORDER BY SUM_ROWS_EXAMINED DESC
LIMIT 10;

-- 重置统计
TRUNCATE TABLE performance_schema.events_statements_summary_by_digest;

-- ❌ 问题：OFFSET 很大时性能极差
SELECT * FROM orders ORDER BY id LIMIT 10 OFFSET 1000000;
-- 需要扫描 1000010 行，丢弃前 1000000 行

-- ✅ 方案 1：基于游标（Keyset Pagination）
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 10;

-- ✅ 方案 2：延迟关联（Deferred Join）
SELECT o.* FROM orders o
INNER JOIN (
    SELECT id FROM orders ORDER BY id LIMIT 10 OFFSET 1000000
) t ON o.id = t.id;
-- 子查询走覆盖索引，只回表 10 行

-- ✅ 方案 3：书签法（记住上次查询的最大 ID）
-- 第一页
SELECT * FROM orders ORDER BY id LIMIT 10;
-- 假设最后一条 id = 100
-- 第二页
SELECT * FROM orders WHERE id > 100 ORDER BY id LIMIT 10;

-- COUNT(*) vs COUNT(1) vs COUNT(col)
SELECT COUNT(*) FROM orders;         -- 统计所有行（包括 NULL）
SELECT COUNT(1) FROM orders;         -- 等价于 COUNT(*)
SELECT COUNT(user_id) FROM orders;   -- 统计 user_id 不为 NULL 的行

-- 性能上 InnoDB 中 COUNT(*) 和 COUNT(1) 没有区别
-- InnoDB 会选最小的索引来遍历计数

-- 优化方案：
-- 1. 使用缓存存储计数（Redis）
-- 2. 使用近似值
SELECT TABLE_ROWS FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'myapp' AND TABLE_NAME = 'orders';
-- 注意：这个值可能不精确（InnoDB 的估算值）

-- 3. 额外计数表
CREATE TABLE table_counts (
    table_name VARCHAR(50) PRIMARY KEY,
    row_count BIGINT NOT NULL DEFAULT 0
);

-- ❌ Using filesort（全表排序）
EXPLAIN SELECT * FROM orders WHERE user_id = 100 ORDER BY created_at DESC;
-- 如果没有 (user_id, created_at) 联合索引 → filesort

-- ✅ 使用联合索引避免排序
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;
-- Extra: 无 Using filesort

-- ❌ ORDER BY 多列方向不一致
SELECT * FROM orders ORDER BY user_id ASC, created_at DESC;
-- MySQL 8.0 之前不能使用索引排序

-- ✅ MySQL 8.0+ 降序索引
ALTER TABLE orders ADD INDEX idx_user_asc_created_desc (user_id, created_at DESC);

-- GROUP BY 也会产生 filesort 或 temporary
EXPLAIN SELECT user_id, COUNT(*) FROM orders GROUP BY user_id;

-- 优化：确保 GROUP BY 列有索引
ALTER TABLE orders ADD INDEX idx_user_id (user_id);

-- GROUP BY + ORDER BY 优化
SELECT user_id, COUNT(*) AS cnt 
FROM orders 
GROUP BY user_id 
ORDER BY cnt DESC;
-- 如果 GROUP BY 和 ORDER BY 字段不同，通常无法避免 filesort
-- 可以用 LIMIT 限制返回行数减少排序开销

-- ❌ 被驱动表无索引
EXPLAIN SELECT * FROM orders o 
JOIN users u ON o.user_name = u.username;
-- 如果 users.username 没有索引 → ALL 扫描

-- ✅ 为 JOIN 列创建索引
ALTER TABLE users ADD INDEX idx_username (username);

-- 小表驱动大表
-- MySQL 优化器通常会自动选择，但可以通过 STRAIGHT_JOIN 强制
SELECT STRAIGHT_JOIN * FROM small_table s
JOIN big_table b ON s.id = b.small_id;
-- small_table 作为驱动表

-- 减少 JOIN 返回的列
-- ❌ 返回不需要的列
SELECT * FROM orders o JOIN users u ON o.user_id = u.id;

-- ✅ 只返回需要的列
SELECT o.order_no, u.username, o.total_amount 
FROM orders o JOIN users u ON o.user_id = u.id;

-- ❌ 子查询（可能效率低）
SELECT * FROM users WHERE id IN (
    SELECT user_id FROM orders WHERE status = 1
);

-- ✅ 改写为 JOIN
SELECT DISTINCT u.* FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.status = 1;

-- ✅ 或使用 EXISTS
SELECT * FROM users u WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.status = 1
);

-- 联合索引：INDEX idx_user_status (user_id, status)
-- 查询：WHERE user_id > 100 AND status = 1

-- 没有 ICP 时：
-- 1. 通过索引找到 user_id > 100 的所有记录
-- 2. 回表获取完整行
-- 3. Server 层过滤 status = 1

-- 有 ICP 时：
-- 1. 通过索引找到 user_id > 100 的记录
-- 2. 在存储引擎层就过滤 status = 1（索引中包含 status）
-- 3. 只对满足条件的记录回表
-- → 减少回表次数

EXPLAIN SELECT * FROM orders WHERE user_id > 100 AND status = 1;
-- Extra: Using index condition ← ICP 生效