第4章：查询优化与执行计划

SELECT * FROM orders WHERE user_id = 10 AND create_time > '2025-10-01';

SELECT * FROM users u 
WHERE u.id IN (
    SELECT user_id FROM orders 
    WHERE amount > 1000
);

SELECT DISTINCT u.* FROM users u 
INNER JOIN orders o ON u.id = o.user_id 
WHERE o.amount > 1000;

SELECT * FROM users u 
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.user_id = u.id AND o.status = 'paid'
);

SELECT u.* FROM users u 
INNER JOIN orders o ON u.id = o.user_id 
WHERE o.status = 'paid';

SELECT * FROM users 
WHERE age > 25 
ORDER BY name;

-- 查看当前优化器开关
SHOW VARIABLES LIKE 'optimizer_switch';

-- 关键开关说明
-- index_merge: 索引合并优化
-- index_condition_pushdown: 索引下推
-- materialization: 物化优化
-- subquery_materialization_cost_based: 基于成本的子查询物化
-- use_index_extensions: 使用索引扩展
-- condition_fanout_filter: 条件扇出过滤
-- derived_merge: 派生表合并
-- use_invisible_indexes: 使用不可见索引
-- skip_scan: 跳跃扫描
-- hash_join: 哈希连接
-- subquery_to_derived: 子查询转派生表

-- 原始查询
SELECT * FROM users 
WHERE age > 25 AND city = 'Beijing';

-- 如果有 (age) 和 (city) 两个索引
-- 优化器可能使用索引合并
EXPLAIN SELECT * FROM users 
WHERE age > 25 AND city = 'Beijing';
-- 可能显示：type=index_merge, Extra=Using intersect(age,city)

-- 大表连接查询
SELECT u.name, o.amount 
FROM users u 
JOIN orders o ON u.id = o.user_id;

-- 优化器可能选择哈希连接
EXPLAIN FORMAT=JSON SELECT u.name, o.amount 
FROM users u 
JOIN orders o ON u.id = o.user_id;
-- 查看 execution_plan 中的 join_type: "hash"

-- 原始查询
SELECT * FROM (
    SELECT user_id, COUNT(*) as order_count 
    FROM orders 
    GROUP BY user_id
) t 
WHERE order_count > 5;

-- 优化器可能合并为
SELECT user_id, COUNT(*) as order_count 
FROM orders 
GROUP BY user_id 
HAVING COUNT(*) > 5;

-- 查看成本模型参数
SHOW VARIABLES LIKE 'optimizer_cost%';

-- 关键参数说明
-- optimizer_cost_model: 成本模型类型
-- optimizer_switch: 优化策略开关
-- optimizer_trace: 优化器跟踪开关

-- 查看优化器选择的连接顺序
EXPLAIN SELECT * FROM a, b, c WHERE a.id = b.aid AND b.id = c.bid;

-- 强制指定连接顺序
EXPLAIN SELECT STRAIGHT_JOIN * FROM a, b, c WHERE a.id = b.aid AND b.id = c.bid;

-- 对比两种方式的成本差异
SHOW SESSION STATUS LIKE 'Last_query_cost';

EXPLAIN SELECT * FROM orders WHERE user_id=10 AND create_time > '2025-10-01';

-- 检查是否使用索引排序
EXPLAIN SELECT * FROM user ORDER BY name, age;

-- 若显示 Extra: Using index 说明使用索引排序，无需额外排序

-- 推荐写法
SELECT name, COUNT(*) FROM user GROUP BY name;

-- 若有索引 (name) 则不需临时表

-- 创建测试表
USE mysql_learning;

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    product_id INT NOT NULL,
    amount DECIMAL(10,2),
    status ENUM('pending', 'paid', 'shipped', 'delivered') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB;

-- 插入测试数据
INSERT INTO orders (user_id, product_id, amount, status) VALUES 
(1, 101, 99.99, 'paid'),
(1, 102, 199.99, 'shipped'),
(2, 101, 299.99, 'pending'),
(2, 103, 399.99, 'delivered'),
(3, 102, 499.99, 'paid');

-- 1. const 类型（主键查询）
EXPLAIN SELECT * FROM orders WHERE id = 1;

-- 2. ref 类型（普通索引等值查询）
EXPLAIN SELECT * FROM orders WHERE user_id = 1;

-- 3. range 类型（范围查询）
EXPLAIN SELECT * FROM orders WHERE created_at > '2024-01-01';

-- 4. ALL 类型（全表扫描）
EXPLAIN SELECT * FROM orders WHERE amount > 100;

-- 创建用户表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    email VARCHAR(100)
) ENGINE=InnoDB;

INSERT INTO users (name, email) VALUES 
('张三', 'zhangsan@example.com'),
('李四', 'lisi@example.com'),
('王五', 'wangwu@example.com');

-- 分析 JOIN 查询
EXPLAIN SELECT u.name, o.amount, o.status 
FROM users u 
JOIN orders o ON u.id = o.user_id 
WHERE o.status = 'paid';

-- 查看详细的 JOIN 信息
EXPLAIN FORMAT=JSON SELECT u.name, o.amount, o.status 
FROM users u 
JOIN orders o ON u.id = o.user_id 
WHERE o.status = 'paid';

-- 创建索引
CREATE INDEX idx_user_created ON orders(user_id, created_at);

-- 测试索引排序
EXPLAIN SELECT * FROM orders WHERE user_id = 1 ORDER BY created_at;

-- 测试非索引排序
EXPLAIN SELECT * FROM orders ORDER BY amount;

-- 测试混合排序
EXPLAIN SELECT * FROM orders WHERE user_id = 1 ORDER BY amount;

-- 测试分组查询
EXPLAIN SELECT user_id, COUNT(*), AVG(amount) 
FROM orders 
GROUP BY user_id;

-- 创建覆盖索引
CREATE INDEX idx_user_amount ON orders(user_id, amount);

-- 再次测试分组查询
EXPLAIN SELECT user_id, COUNT(*), AVG(amount) 
FROM orders 
GROUP BY user_id;

-- 查看表的统计信息
SHOW TABLE STATUS LIKE 'orders';

-- 查看索引统计信息
SHOW INDEX FROM orders;

-- 更新统计信息
ANALYZE TABLE orders;

-- 查看优化器开关
SHOW VARIABLES LIKE 'optimizer_switch';

-- 查看成本模型参数
SHOW VARIABLES LIKE 'optimizer_cost%';

-- 开启性能分析
SET profiling = 1;

-- 执行查询
SELECT u.name, o.amount, o.status 
FROM users u 
JOIN orders o ON u.id = o.user_id 
WHERE o.status = 'paid';

-- 查看性能分析结果
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;

-- 关闭性能分析
SET profiling = 0;

-- 使用 USE INDEX 提示
EXPLAIN SELECT * FROM orders USE INDEX (idx_user_id) WHERE user_id = 1;

-- 使用 FORCE INDEX 强制使用索引
EXPLAIN SELECT * FROM orders FORCE INDEX (idx_user_id) WHERE user_id = 1;

-- 使用 IGNORE INDEX 忽略索引
EXPLAIN SELECT * FROM orders IGNORE INDEX (idx_user_id) WHERE user_id = 1;

-- 使用 STRAIGHT_JOIN 强制连接顺序
EXPLAIN SELECT u.name, o.amount 
FROM users u 
STRAIGHT_JOIN orders o ON u.id = o.user_id;

SELECT 
    u.name,
    COUNT(o.id) as order_count,
    SUM(o.amount) as total_amount,
    AVG(o.amount) as avg_amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY u.id, u.name
ORDER BY total_amount DESC
LIMIT 10;

EXPLAIN SELECT 
    u.name,
    COUNT(o.id) as order_count,
    SUM(o.amount) as total_amount,
    AVG(o.amount) as avg_amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY u.id, u.name
ORDER BY total_amount DESC
LIMIT 10;

-- 1. 创建复合索引
CREATE INDEX idx_user_created_amount ON orders(user_id, created_at, amount);

-- 2. 优化后的查询
EXPLAIN SELECT 
    u.name,
    COUNT(o.id) as order_count,
    SUM(o.amount) as total_amount,
    AVG(o.amount) as avg_amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY u.id, u.name
ORDER BY total_amount DESC
LIMIT 10;

-- 1. 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';

-- 2. 执行问题查询
SELECT * FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'pending'
AND o.created_at BETWEEN '2024-01-01' AND '2024-12-31'
ORDER BY o.created_at DESC;

-- 3. 分析执行计划
EXPLAIN SELECT * FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'pending'
AND o.created_at BETWEEN '2024-01-01' AND '2024-12-31'
ORDER BY o.created_at DESC;

-- 4. 查看慢查询日志
SHOW VARIABLES LIKE 'slow_query_log_file';

-- 创建覆盖索引
CREATE INDEX idx_status_created_user ON orders(status, created_at, user_id);

-- 验证优化效果
EXPLAIN SELECT o.id, o.user_id, o.amount, o.status, o.created_at, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'pending'
AND o.created_at BETWEEN '2024-01-01' AND '2024-12-31'
ORDER BY o.created_at DESC;