-- 未優(yōu)化：可能觸發(fā)全表掃描
SELECT * FROM orders WHERE order_date > '2023-01-01';

-- 優(yōu)化：為 order_date 添加索引
CREATE INDEX idx_order_date ON orders (order_date);

-- 未優(yōu)化：子查詢
SELECT * FROM employees 
WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'Beijing');

-- 優(yōu)化：JOIN
SELECT e.* FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE d.location = 'Beijing';

-- 未優(yōu)化
SELECT * FROM products;

-- 優(yōu)化
SELECT product_id, name, price FROM products;

-- 為多條件查詢創(chuàng)建復(fù)合索引
CREATE INDEX idx_customer_order ON orders (customer_id, order_date DESC);

-- 未優(yōu)化：循環(huán)逐條更新
WHILE condition DO
    UPDATE products SET stock = stock - 1 WHERE product_id = id;
END WHILE;

-- 優(yōu)化：批量更新
UPDATE products SET stock = stock - 1 WHERE product_id IN (1, 2, 3, ...);

-- 未優(yōu)化：重復(fù)計算
IF (SELECT COUNT(*) FROM orders WHERE customer_id = 100) > 10 THEN
    -- 再次查詢相同條件
    SELECT SUM(amount) FROM orders WHERE customer_id = 100;
END IF;

-- 優(yōu)化：使用臨時變量
DECLARE order_count INT;
SELECT COUNT(*) INTO order_count FROM orders WHERE customer_id = 100;

IF order_count > 10 THEN
    SELECT SUM(amount) FROM orders WHERE customer_id = 100;
END IF;

DELIMITER $$

CREATE PROCEDURE GetSalesReport()
BEGIN
    -- 創(chuàng)建臨時表存儲中間結(jié)果
    CREATE TEMPORARY TABLE temp_sales (
        product_id INT,
        total_sales DECIMAL(10,2)
    );
    
    -- 插入中間結(jié)果
    INSERT INTO temp_sales
    SELECT product_id, SUM(amount) FROM orders GROUP BY product_id;
    
    -- 使用臨時表進行最終查詢
    SELECT p.name, t.total_sales 
    FROM products p
    JOIN temp_sales t ON p.product_id = t.product_id;
    
    -- 刪除臨時表
    DROP TEMPORARY TABLE IF EXISTS temp_sales;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE TransferFunds(IN from_account INT, IN to_account INT, IN amount DECIMAL(10,2))
BEGIN
    START TRANSACTION;
    
    -- 快速執(zhí)行更新操作
    UPDATE accounts SET balance = balance - amount WHERE account_id = from_account;
    UPDATE accounts SET balance = balance + amount WHERE account_id = to_account;
    
    COMMIT;
END$$

DELIMITER ;

EXPLAIN SELECT * FROM orders WHERE customer_id = 100;

SET profiling = 1;
CALL CalculateTotal(1001);
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;  -- 查詢 ID 可從 SHOW PROFILES 結(jié)果中獲取

DELIMITER $$

CREATE PROCEDURE ProcessLargeData()
BEGIN
    DECLARE offset INT DEFAULT 0;
    DECLARE batch_size INT DEFAULT 1000;
    DECLARE total_rows INT;
    
    -- 獲取總記錄數(shù)
    SELECT COUNT(*) INTO total_rows FROM large_table;
    
    WHILE offset < total_rows DO
        -- 分批處理
        UPDATE large_table 
        SET status = 'processed' 
        WHERE id BETWEEN offset AND offset + batch_size;
        
        SET offset = offset + batch_size;
    END WHILE;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE GetOrderTotal(IN customerId INT)
BEGIN
    -- 未優(yōu)化：全表掃描 + 子查詢
    SELECT 
        customer_id,
        (SELECT COUNT(*) FROM orders WHERE customer_id = c.customer_id) AS order_count,
        (SELECT SUM(amount) FROM orders WHERE customer_id = c.customer_id) AS total_amount
    FROM customers c
    WHERE c.customer_id = customerId;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE GetOrderTotal(IN customerId INT)
BEGIN
    -- 優(yōu)化：JOIN + 索引 + 聚合函數(shù)
    SELECT 
        c.customer_id,
        COUNT(o.order_id) AS order_count,
        SUM(o.amount) AS total_amount
    FROM customers c
    LEFT JOIN orders o ON c.customer_id = o.customer_id
    WHERE c.customer_id = customerId
    GROUP BY c.customer_id;
END$$

DELIMITER ;