MYSQL实战：深入理解内存临时表优化

-- 用户表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_username (username),
    INDEX idx_created (created_at)
);

-- 订单表
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    status ENUM('pending', 'completed', 'cancelled') NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id),
    INDEX idx_created_status (created_at, status)
);

-- 插入10万用户
DELIMITER $$
CREATE PROCEDURE InsertUsers()
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= 100000 DO
        INSERT INTO users (username, email, created_at) 
        VALUES (CONCAT('user', i), CONCAT('user', i, '@example.com'), 
                DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 365) DAY));
        SET i = i + 1;
    END WHILE;
END$$
DELIMITER ;

CALL InsertUsers();
DROP PROCEDURE InsertUsers;

-- 插入100万订单数据
DELIMITER $$
CREATE PROCEDURE InsertOrders()
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE user_count INT;
    SELECT COUNT(*) INTO user_count FROM users;
    
    WHILE i <= 1000000 DO
        INSERT INTO orders (user_id, amount, status, created_at) 
        VALUES (FLOOR(1 + RAND() * user_count), 
                ROUND(RAND() * 1000, 2),
                ELT(FLOOR(1 + RAND() * 3), 'pending', 'completed', 'cancelled'),
                DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 365) DAY));
        SET i = i + 1;
    END WHILE;
END$$
DELIMITER ;

CALL InsertOrders();
DROP PROCEDURE InsertOrders;

SELECT 
    u.username,
    SUM(o.amount) as total_amount,
    COUNT(o.id) as order_count
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
    AND o.status = 'completed'
GROUP BY u.id, u.username
ORDER BY total_amount DESC
LIMIT 1000;

EXPLAIN 
SELECT 
    u.username,
    SUM(o.amount) as total_amount,
    COUNT(o.id) as order_count
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
    AND o.status = 'completed'
GROUP BY u.id, u.username
ORDER BY total_amount DESC
LIMIT 100;

-- 查看临时表配置
SHOW VARIABLES LIKE 'tmp_table_size';
SHOW VARIABLES LIKE 'max_heap_table_size';

-- 在另一个会话中监控临时表创建
SHOW STATUS LIKE 'Created_tmp%';

-- 建议删除可能存在的低效索引
DROP INDEX idx_created_status ON orders;

-- 创建更适合的复合索引
CREATE INDEX idx_orders_covering ON orders (status, created_at, user_id, amount);

-- 原查询（性能差）
SELECT 
    u.username,
    SUM(o.amount) as total_amount,
    COUNT(o.id) as order_count
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
    AND o.status = 'completed'
GROUP BY u.id, u.username
ORDER BY total_amount DESC
LIMIT 100;

-- 优化后的查询：先聚合再连接
SELECT 
    u.username,
    ot.total_amount,
    ot.order_count
FROM users u
INNER JOIN (
    SELECT 
        user_id,
        SUM(amount) as total_amount,
        COUNT(id) as order_count
    FROM orders 
    WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
        AND status = 'completed'
    GROUP BY user_id
) ot ON u.id = ot.user_id
ORDER BY ot.total_amount DESC
LIMIT 100;

-- 增大内存临时表的大小限制（当前会话有效）
SET SESSION tmp_table_size = 256 * 1024 * 1024;  -- 256MB
SET SESSION max_heap_table_size = 256 * 1024 * 1024;