-- 查看 SQL 执行计划
EXPLAIN SELECT * FROM employee WHERE id = 1;
-- 或
EXPLAIN FORMAT=JSON SELECT * FROM employee WHERE id = 1;  -- 更详细

-- MySQL 8.0.16+ 支持 EXPLAIN ANALYZE（实际执行并给出耗时）
EXPLAIN ANALYZE SELECT * FROM employee e
JOIN department d ON e.dept_id = d.id
WHERE e.salary > 10000;

-- 示例输出
EXPLAIN SELECT e.name, d.dept_name
FROM employee e
JOIN department d ON e.dept_id = d.id
WHERE e.salary > 10000\G

-- system：表只有一行（系统表）
EXPLAIN SELECT * FROM mysql.proxies_priv;

-- const：主键或唯一索引等值查询
EXPLAIN SELECT * FROM employee WHERE id = 1;
-- type: const

-- eq_ref：JOIN 时使用主键/唯一索引关联
EXPLAIN SELECT * FROM employee e
JOIN department d ON e.dept_id = d.id;
-- 如果 d.id 是主键，type 为 eq_ref

-- ref：普通索引等值查询
EXPLAIN SELECT * FROM employee WHERE dept_id = 1;
-- type: ref（dept_id 上有索引）

-- range：索引范围查询
EXPLAIN SELECT * FROM employee WHERE salary BETWEEN 10000 AND 20000;
-- type: range（salary 上有索引）

-- index：扫描整个索引树
EXPLAIN SELECT COUNT(*) FROM employee;
-- type: index（比全表扫描稍好）

-- ALL：全表扫描（最差）
EXPLAIN SELECT * FROM employee WHERE name = '张三';
-- type: ALL（name 无索引）

-- MySQL 命令行设置（当前生效）
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;            -- 超过 1 秒
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL log_queries_not_using_indexes = ON;  -- 记录无索引查询

-- 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

-- 查看配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 查看慢查询日志数量
SHOW GLOBAL STATUS LIKE 'Slow_queries';

-- 使用 mysqldumpslow 分析工具
mysqldumpslow -t 10 /var/log/mysql/slow.log       -- 最慢的 10 条
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log   -- 统计次数最多的 10 条

-- 使用 pt-query-digest 工具（Percona Toolkit）
pt-query-digest /var/log/mysql/slow.log

-- 慢查询日志示例内容
# Time: 2024-06-15T10:30:00.123456Z
# Query_time: 2.345678  Lock_time: 0.001234 Rows_sent: 100 Rows_examined: 500000
SET timestamp = 1718443800;
SELECT * FROM orders WHERE YEAR(create_time) = 2024;

-- 1. 避免 SELECT *
-- 差：查询所有字段，可能用不到覆盖索引
SELECT * FROM employee WHERE dept_id = 1;

-- 好：只查需要的字段
SELECT id, name FROM employee WHERE dept_id = 1;

-- 2. 避免在 WHERE 中用函数
-- 差：DATE() 函数导致索引失效
SELECT * FROM orders WHERE DATE(create_time) = '2024-01-01';

-- 好：范围查询
SELECT * FROM orders
WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02';

-- 3. 用 UNION ALL 替代 UNION（不需要去重时）
SELECT id FROM orders_2023
UNION ALL
SELECT id FROM orders_2024;  -- 避免去重排序

-- 1. 用 IN 替代 OR（IN 会优化，OR 可能索引失效）
-- 差：
SELECT * FROM employee WHERE dept_id = 1 OR dept_id = 2;

-- 好：
SELECT * FROM employee WHERE dept_id IN (1, 2);

-- 2. 用 EXISTS 替代 IN（子表大时）
-- 查询有订单的用户
-- 差（orders 大时）：
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);

-- 好：
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- 3. 用 JOIN 替代子查询
-- 差：
SELECT * FROM employee
WHERE dept_id IN (SELECT id FROM department WHERE dept_name LIKE '技术%');

-- 好：
SELECT e.* FROM employee e
JOIN department d ON e.dept_id = d.id
WHERE d.dept_name LIKE '技术%';

-- 1. 小表驱动大表
-- 如果 department 数据少，employee 数据多
-- MySQL 会自动选择小表作为驱动表
SELECT e.*, d.dept_name
FROM employee e            -- 大表
JOIN department d ON e.dept_id = d.id;  -- 小表

-- 2. JOIN 字段要有索引
-- e.dept_id 和 d.id 都要有索引

-- 3. 注意 JOIN 缓冲
-- 如果 JOIN 时 type 为 ALL 且 Extra 有 Using join buffer
-- 说明关联字段缺少索引
EXPLAIN SELECT * FROM employee e
LEFT JOIN department d ON e.dept_id = d.id;
-- 检查 type 和 Extra

-- 传统分页（越往后越慢）
SELECT * FROM orders ORDER BY id LIMIT 100000, 20;

-- 优化1：延迟关联（子查询优化）
SELECT o.* FROM orders o
INNER JOIN (
    SELECT id FROM orders ORDER BY id LIMIT 100000, 20
) tmp ON o.id = tmp.id;

-- 优化2：范围查询（适用于连续翻页）
SELECT * FROM orders
WHERE id > 100000
ORDER BY id
LIMIT 20;

-- 优化3：记录上次位置（游标分页，推荐）
-- 第一页：
SELECT * FROM orders ORDER BY id LIMIT 20;
-- 后续页：传入上一页最后一条的 id
SELECT * FROM orders
WHERE id > 100020
ORDER BY id
LIMIT 20;

-- 问题 SQL：
SELECT u.name, o.order_no, oi.product_name
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
WHERE o.create_time >= '2024-01-01'
ORDER BY o.create_time DESC
LIMIT 20;

-- EXPLAIN 发现问题：
-- type: ALL（全表扫描）
-- rows: 100000+
-- Extra: Using temporary; Using filesort

-- 优化1：添加索引
CREATE INDEX idx_user_id ON orders(user_id);
CREATE INDEX idx_order_id ON order_items(order_id);
CREATE INDEX idx_create_time ON orders(create_time);

-- 优化2：改写 SQL，先缩小数据范围
SELECT u.name, t.order_no, t.product_name
FROM users u
JOIN (
    SELECT o.id, o.user_id, o.order_no, oi.product_name
    FROM orders o
    JOIN order_items oi ON o.id = oi.order_id
    WHERE o.create_time >= '2024-01-01'
    ORDER BY o.create_time DESC
    LIMIT 20
) t ON u.id = t.user_id;

-- 问题 SQL：查询最近30天下单的用户及其最新订单
SELECT u.*,
    (SELECT order_no FROM orders
     WHERE user_id = u.id
     ORDER BY create_time DESC LIMIT 1) AS last_order_no,
    (SELECT amount FROM orders
     WHERE user_id = u.id
     ORDER BY create_time DESC LIMIT 1) AS last_amount
FROM users u
WHERE u.create_time >= DATE_SUB(NOW(), INTERVAL 30 DAY);

-- 优化：使用 JOIN + 派生表（避免相关子查询）
SELECT u.*, t.order_no AS last_order_no, t.amount AS last_amount
FROM users u
LEFT JOIN (
    SELECT user_id, order_no, amount,
           ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY create_time DESC) AS rn
    FROM orders
) t ON u.id = t.user_id AND t.rn = 1
WHERE u.create_time >= DATE_SUB(NOW(), INTERVAL 30 DAY);

-- 场景：后台订单列表翻到第 1000 页
-- 每页 20 条，LIMIT 19980, 20

-- 优化前：
EXPLAIN SELECT * FROM orders ORDER BY create_time DESC LIMIT 19980, 20;
-- type: ALL, rows: 500000, Extra: Using filesort

-- 优化方案1：覆盖索引 + 延迟关联
EXPLAIN
SELECT o.* FROM orders o
INNER JOIN (
    SELECT id FROM orders
    ORDER BY create_time DESC
    LIMIT 19980, 20
) tmp ON o.id = tmp.id;
-- 子查询走覆盖索引，回表只取 20 条

-- 优化方案2：游标分页（需前端配合）
SELECT * FROM orders
WHERE create_time < '2024-06-01 00:00:00'
ORDER BY create_time DESC
LIMIT 20;

-- 问题 SQL：统计各部门工资分布
SELECT d.dept_name,
       CASE
           WHEN e.salary < 8000 THEN '低'
           WHEN e.salary < 15000 THEN '中'
           ELSE '高'
       END AS salary_level,
       COUNT(*) AS emp_count
FROM employee e
JOIN department d ON e.dept_id = d.id
GROUP BY d.dept_name, salary_level;

-- Extra: Using temporary; Using filesort

-- 优化：给 GROUP BY 字段建索引
CREATE INDEX idx_dept_salary ON employee(dept_id, salary);

-- 进一步优化：使用虚拟列（MySQL 5.7+）
ALTER TABLE employee ADD COLUMN salary_level VARCHAR(5)
GENERATED ALWAYS AS (
    CASE
        WHEN salary < 8000 THEN '低'
        WHEN salary < 15000 THEN '中'
        ELSE '高'
    END
) STORED;

CREATE INDEX idx_dept_level ON employee(dept_id, salary_level);

-- 优化后查询
SELECT d.dept_name, e.salary_level, COUNT(*)
FROM employee e
JOIN department d ON e.dept_id = d.id
GROUP BY d.dept_name, e.salary_level;

-- 原始 SQL（来自慢查询日志，耗时 3.2 秒）
SELECT
    u.name,
    u.phone,
    o.order_no,
    o.amount,
    oi.product_name,
    oi.quantity
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
WHERE u.create_time >= '2024-01-01'
  AND u.status = 1
  AND o.status = 1
ORDER BY o.create_time DESC
LIMIT 20;

-- 步骤1：EXPLAIN 分析
EXPLAIN SELECT ...;
-- type: ALL, rows: 200000
-- Extra: Using where; Using temporary; Using filesort
-- JOIN 关联字段无索引

-- 步骤2：添加索引
CREATE INDEX idx_user_status ON users(status, create_time);
CREATE INDEX idx_user_id ON orders(user_id);
CREATE INDEX idx_order_status ON orders(status, create_time);
CREATE INDEX idx_order_id ON order_items(order_id);

-- 步骤3：优化 SQL 写法
-- 优化：先缩小核心数据集再 JOIN
SELECT u.name, u.phone, t.*
FROM (
    SELECT id, order_no, amount, create_time, user_id
    FROM orders
    WHERE status = 1
    ORDER BY create_time DESC
    LIMIT 20
) o
JOIN users u ON o.user_id = u.id AND u.status = 1 AND u.create_time >= '2024-01-01'
JOIN order_items oi ON o.id = oi.order_id;

-- 步骤4：验证效果
EXPLAIN SELECT ...;
-- type: ref/range, rows: 20
-- Extra: Using index condition

-- 最终耗时：3.2s → 15ms