MySQL 自訂函式與儲存程式企業級程式化開發完整實戰指南

-- ============================================
-- 函式名稱: f_get_state_population
-- 功能說明: 根據縣市名稱查詢該縣市的人口總數
-- 輸入參數: state_param VARCHAR(100) - 縣市名稱
-- 回傳值: INT - 該縣市的人口數,若縣市不存在則回傳 NULL
-- 建立日期: 2024-01-15
-- 最後修改: 2024-11-28
-- 作者: 開發團隊
-- ============================================

-- 設定使用的資料庫
-- 確保在正確的資料庫結構描述下建立函式
USE population;

-- 修改 SQL 陳述式的分隔符號
-- MySQL 預設使用分號(;)作為陳述式結束符號
-- 但函式定義內部也會使用分號,會造成解析混淆
-- 因此暫時將分隔符號改為 // ,讓 MySQL 能夠完整解析函式定義
DELIMITER //

-- 建立自訂函式
-- CREATE FUNCTION: 函式建立的關鍵字
-- f_get_state_population: 函式名稱,建議使用 f_ 前綴標示這是 Function
-- 命名規範有助於在大型專案中快速識別資料庫物件的類型
CREATE FUNCTION f_get_state_population(
    state_param VARCHAR(100)     -- 輸入參數:縣市名稱
                                 -- VARCHAR(100) 提供足夠長度儲存台灣縣市名稱
                                 -- 參數命名使用 _param 後綴以區分變數作用域
)
RETURNS INT                      -- 宣告函式回傳值型別為整數
                                 -- 人口數使用 INT 型別足以涵蓋台灣縣市規模
DETERMINISTIC                    -- 確定性宣告:相同輸入必定產生相同輸出
                                 -- 此宣告讓 MySQL 可以快取函式結果
                                 -- 顯著提升重複查詢的效能
READS SQL DATA                   -- SQL 資料存取宣告:此函式只讀取資料
                                 -- 不執行任何 INSERT/UPDATE/DELETE 操作
                                 -- 讓 MySQL 能夠在複製環境中安全執行
COMMENT '查詢指定縣市的人口總數,用於統計分析與報表系統'
                                 -- 函式說明註解,便於系統維護與文件產生
BEGIN
    -- 宣告函式內部使用的區域變數
    -- DECLARE 關鍵字建立函式作用域內的臨時變數
    -- 變數命名使用 _var 後綴以明確標示其為變數
    DECLARE population_var INT DEFAULT NULL;
                                 -- 初始化為 NULL,若查詢無結果則保持此值
                                 -- DEFAULT 子句確保變數有明確的初始狀態

    -- 執行查詢並將結果存入變數
    -- SELECT ... INTO 語法是 MySQL 中將查詢結果賦值給變數的標準方式
    -- 這種寫法預期且要求查詢只回傳單一筆資料
    -- 若查詢結果為空,變數保持 NULL 值
    -- 若查詢回傳多筆資料,會產生錯誤 "Subquery returns more than 1 row"
    SELECT population INTO population_var
    FROM state_population               -- 人口統計資料表
    WHERE state = state_param;          -- 使用輸入參數進行精確比對
                                        -- 建議在 state 欄位建立索引以提升查詢效能

    -- 回傳查詢結果
    -- RETURN 關鍵字結束函式執行並回傳指定值
    -- 函式執行到 RETURN 陳述式時立即結束,不會繼續執行後續程式碼
    RETURN population_var;
    
    -- 注意:如果縣市不存在,此函式會回傳 NULL
    -- 呼叫端需要處理 NULL 值的情況,或使用 IFNULL/COALESCE 函式提供預設值
END //

-- 恢復預設的分隔符號
-- 函式建立完成後,將分隔符改回標準的分號
-- 這樣後續的 SQL 陳述式可以正常使用分號作為結束符號
DELIMITER ;

-- ============================================
-- 函式使用範例與說明
-- ============================================

-- 範例 1: 直接呼叫函式取得特定縣市的人口數
-- 函式可以在 SELECT 清單中直接使用,如同內建函式一樣
SELECT f_get_state_population('台北市') AS taipei_population;
-- 結果: taipei_population
--       2700000

-- 範例 2: 在 WHERE 子句中使用函式進行條件篩選
-- 這個查詢會找出所有人口數超過台北市的縣市
-- MySQL 會先執行 f_get_state_population('台北市') 取得基準值
-- 然後用這個值與資料表中的 population 欄位進行比較
SELECT
    state AS 縣市名稱,
    population AS 人口數,
    -- 計算與台北市的人口比例
    ROUND(population / f_get_state_population('台北市') * 100, 2) AS 相對台北市人口比例
FROM state_population
WHERE population > f_get_state_population('台北市')
      -- 注意:在 WHERE 子句中對非索引欄位使用函式不影響效能
      -- 但如果對 population 欄位使用函式(如 WHERE f_something(population) > xxx)
      -- 則可能導致索引失效
ORDER BY population DESC;

-- 範例 3: 在聚合查詢中使用函式
-- 計算所有縣市人口數與台北市的平均比例
SELECT
    COUNT(*) AS 統計縣市數,
    ROUND(AVG(population / f_get_state_population('台北市')), 2) AS 平均人口比例,
    ROUND(MIN(population / f_get_state_population('台北市')), 2) AS 最小人口比例,
    ROUND(MAX(population / f_get_state_population('台北市')), 2) AS 最大人口比例
FROM state_population
WHERE population > 100000;           -- 只統計人口超過 10 萬的縣市

-- 範例 4: 使用 IFNULL 處理函式可能回傳的 NULL 值
-- 如果查詢的縣市不存在,函式會回傳 NULL
-- 使用 IFNULL 可以提供預設值,避免後續計算出現問題
SELECT
    '高雄市' AS 查詢縣市,
    IFNULL(f_get_state_population('高雄市'), 0) AS 人口數,
    CASE
        WHEN f_get_state_population('高雄市') IS NULL THEN '資料不存在'
        WHEN f_get_state_population('高雄市') > 2000000 THEN '特大城市'
        WHEN f_get_state_population('高雄市') > 1000000 THEN '大城市'
        ELSE '中小城市'
    END AS 城市規模分類;

-- ============================================
-- 程式名稱: p_update_state_population
-- 功能說明: 更新指定縣市的人口數,並記錄操作日誌
-- 輸入參數: 
--   state_param VARCHAR(100) - 縣市名稱
--   new_population INT - 新的人口數值
-- 輸出參數:
--   operation_result VARCHAR(200) - 操作執行結果訊息
-- 建立日期: 2024-01-15
-- 最後修改: 2024-11-28
-- 作者: 開發團隊
-- 重要提示: 此程式使用交易控制,確保資料一致性
-- ============================================

-- 修改陳述式分隔符號
-- 儲存程式通常包含更複雜的邏輯與更多的陳述式
-- 需要使用非標準分隔符號來避免解析衝突
DELIMITER //

-- 建立儲存程式
-- CREATE PROCEDURE: 程式建立的關鍵字
-- p_update_state_population: 程式名稱,建議使用 p_ 前綴標示這是 Procedure
-- 這種命名慣例在大型專案中有助於快速識別資料庫物件類型
CREATE PROCEDURE p_update_state_population(
    IN state_param VARCHAR(100),      -- IN: 輸入參數,只能讀取不能修改
                                      -- 用於接收外部傳入的縣市名稱
    IN new_population INT,             -- IN: 新的人口數值
                                      -- 必須為正整數,程式內部會進行驗證
    OUT operation_result VARCHAR(200)  -- OUT: 輸出參數,用於回傳執行結果
                                      -- 呼叫端可以透過使用者變數接收這個值
)
MODIFIES SQL DATA                      -- SQL 資料存取宣告:此程式會修改資料
                                      -- 相較於 READS SQL DATA,這表示會執行
                                      -- INSERT/UPDATE/DELETE 等修改操作
COMMENT '更新縣市人口數並記錄操作日誌,支援交易回滾與錯誤處理'
BEGIN
    -- 宣告錯誤處理器
    -- MySQL 的錯誤處理機制類似其他程式語言的 try-catch
    -- DECLARE HANDLER 定義當特定條件發生時的處理邏輯
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        -- 當任何 SQL 錯誤發生時,這個處理器會被自動觸發
        -- EXIT 表示處理完錯誤後會退出程式
        -- 相對地,CONTINUE 表示處理完錯誤後會繼續執行
        
        -- 回滾所有尚未提交的變更
        -- 確保資料庫維持一致性狀態
        ROLLBACK;
        
        -- 設定錯誤訊息給輸出參數
        -- 呼叫端可以透過這個訊息了解錯誤原因
        SET operation_result = CONCAT(
            '更新失敗:系統錯誤 - ',
            state_param,
            ' - 請聯繫系統管理員'
        );
    END;
    
    -- 宣告區域變數用於存取中間結果
    DECLARE old_population INT DEFAULT NULL;
    DECLARE current_user_name VARCHAR(100);
    
    -- 取得當前資料庫使用者名稱
    -- 用於記錄是誰執行了這個操作
    SET current_user_name = CURRENT_USER();

    -- 開始交易
    -- START TRANSACTION 標記交易的起點
    -- 從這裡開始的所有資料修改操作都會被納入交易範圍
    -- 直到執行 COMMIT(提交)或 ROLLBACK(回滾)才會結束交易
    START TRANSACTION;
    
    -- 輸入參數驗證
    -- 在執行實際的資料操作前,先驗證輸入是否合法
    IF state_param IS NULL OR LENGTH(TRIM(state_param)) = 0 THEN
        -- 縣市名稱為空的錯誤處理
        ROLLBACK;                     -- 回滾交易
        SET operation_result = '錯誤:縣市名稱不能為空';
        -- 使用 LEAVE 語句可以提前退出程式
        -- 但這裡我們使用條件判斷的結構來控制流程
    ELSEIF new_population IS NULL OR new_population <= 0 THEN
        -- 人口數不合法的錯誤處理
        ROLLBACK;
        SET operation_result = '錯誤:人口數必須為正整數';
    ELSE
        -- 輸入驗證通過,執行實際的資料操作
        
        -- 檢查縣市是否存在於資料庫中
        -- 使用 EXISTS 子查詢進行存在性檢查,效能優於 COUNT(*)
        IF EXISTS (
            SELECT 1 FROM state_population 
            WHERE state = state_param
        ) THEN
            -- 縣市存在,執行更新操作
            
            -- 先查詢並保存舊的人口數
            -- 這個資訊將用於記錄操作日誌
            SELECT population INTO old_population
            FROM state_population
            WHERE state = state_param;
            
            -- 執行人口數更新
            -- UPDATE 陳述式修改現有記錄
            UPDATE state_population
            SET 
                population = new_population,    -- 更新為新的人口數
                last_updated = NOW()            -- 記錄更新時間戳記
            WHERE state = state_param;
            
            -- 新增操作日誌記錄
            -- 記錄誰在什麼時間對哪個縣市的人口數做了什麼變更
            -- 這對於資料稽核與問題追蹤非常重要
            INSERT INTO population_update_log (
                state,                          -- 縣市名稱
                old_population,                 -- 更新前的人口數
                new_population,                 -- 更新後的人口數
                update_time,                    -- 更新時間
                operator,                       -- 操作人員
                change_amount                   -- 變化量
            ) VALUES (
                state_param,
                old_population,
                new_population,
                NOW(),
                current_user_name,
                new_population - old_population  -- 計算變化量
            );
            
            -- 所有操作都成功完成,提交交易
            -- COMMIT 將所有變更永久寫入資料庫
            COMMIT;
            
            -- 設定成功訊息
            SET operation_result = CONCAT(
                '成功更新 ',
                state_param,
                ' 的人口數從 ',
                old_population,
                ' 變更為 ',
                new_population,
                ' (變化: ',
                (new_population - old_population),
                ')'
            );
            
        ELSE
            -- 縣市不存在的錯誤處理
            -- 回滾交易並設定錯誤訊息
            ROLLBACK;
            SET operation_result = CONCAT(
                '錯誤:縣市「',
                state_param,
                '」不存在於資料庫中,請先新增該縣市資料'
            );
        END IF;
    END IF;
    
    -- 程式執行到此處自動結束
    -- 注意:所有的路徑都已經明確處理了 COMMIT 或 ROLLBACK
    -- 確保交易不會處於未決狀態
END //

-- 恢復預設分隔符號
DELIMITER ;

-- ============================================
-- 儲存程式使用範例與說明
-- ============================================

-- 範例 1: 基本的單一縣市人口更新
-- 宣告使用者變數用於接收輸出參數
-- MySQL 使用者變數以 @ 符號開頭,可以在多個陳述式之間保持值
SET @result_message = '';

-- 呼叫儲存程式
-- CALL 關鍵字用於執行儲存程式
-- 參數依序傳入:縣市名稱、新人口數、輸出變數
CALL p_update_state_population('台北市', 2700000, @result_message);

-- 查詢執行結果
-- 使用者變數會保留程式執行後的輸出值
SELECT @result_message AS 執行結果;
-- 預期結果: 成功更新 台北市 的人口數從 2650000 變更為 2700000 (變化: 50000)

-- 範例 2: 批次更新多個縣市的人口數
-- 這個範例展示如何在應用程式層面迴圈呼叫儲存程式
-- 實務上這個邏輯通常會放在應用程式的循環結構中

-- 更新新北市
CALL p_update_state_population('新北市', 4030000, @result_message);
SELECT '新北市' AS 縣市, @result_message AS 更新結果;

-- 更新桃園市
CALL p_update_state_population('桃園市', 2280000, @result_message);
SELECT '桃園市' AS 縣市, @result_message AS 更新結果;

-- 更新台中市
CALL p_update_state_population('台中市', 2820000, @result_message);
SELECT '台中市' AS 縣市, @result_message AS 更新結果;

-- 範例 3: 錯誤處理測試
-- 測試縣市不存在的情況
CALL p_update_state_population('不存在的縣市', 1000000, @result_message);
SELECT @result_message AS 錯誤處理結果;
-- 預期結果: 錯誤:縣市「不存在的縣市」不存在於資料庫中,請先新增該縣市資料

-- 測試人口數無效的情況
CALL p_update_state_population('台北市', -1000, @result_message);
SELECT @result_message AS 輸入驗證結果;
-- 預期結果: 錯誤:人口數必須為正整數

-- 範例 4: 查詢操作日誌驗證交易完整性
-- 檢視今天所有的人口數更新記錄
SELECT
    state AS 縣市,
    old_population AS 更新前人口,
    new_population AS 更新後人口,
    change_amount AS 變化量,
    CASE
        WHEN change_amount > 0 THEN '增加'
        WHEN change_amount < 0 THEN '減少'
        ELSE '無變化'
    END AS 變化類型,
    update_time AS 更新時間,
    operator AS 操作人員
FROM population_update_log
WHERE DATE(update_time) = CURDATE()   -- 只顯示今天的記錄
ORDER BY update_time DESC;

-- ============================================
-- 確定性特性的正確使用範例
-- ============================================

-- 範例 1: 典型的確定性函式
-- 這個函式根據縣市名稱查詢人口數
-- 只要資料庫內容不變,相同的輸入必然產生相同的輸出
DELIMITER //

CREATE FUNCTION f_get_population_deterministic(
    city_name VARCHAR(100)
)
RETURNS INT
DETERMINISTIC                    -- 確定性宣告
READS SQL DATA                   -- 只讀取資料
COMMENT '確定性人口查詢函式,相同輸入產生相同輸出'
BEGIN
    DECLARE pop_count INT;
    
    -- 從靜態的參考資料表查詢
    -- 這種查詢結果在資料未更新時保持穩定
    SELECT population INTO pop_count
    FROM city_reference              -- 假設這是一個變動不頻繁的參考資料表
    WHERE city = city_name;
    
    -- 使用 IFNULL 避免回傳 NULL
    -- 提供預設值 0 確保回傳值的確定性
    RETURN IFNULL(pop_count, 0);
END //

DELIMITER ;

-- 範例 2: 必須使用 NOT DETERMINISTIC 的情況 - 時間相關
-- 這個函式的輸出取決於當前時間,即使參數相同也可能回傳不同結果
DELIMITER //

CREATE FUNCTION f_get_current_quarter_population(
    city_name VARCHAR(100)
)
RETURNS INT
NOT DETERMINISTIC                -- 非確定性宣告,因為輸出隨時間改變
READS SQL DATA
COMMENT '查詢當前季度的人口數,輸出隨季度變化'
BEGIN
    DECLARE pop_count INT;
    DECLARE current_quarter INT;
    DECLARE current_year INT;
    
    -- 計算當前季度與年份
    -- NOW() 函式的回傳值會隨時間改變
    SET current_quarter = QUARTER(NOW());
    SET current_year = YEAR(NOW());
    
    -- 根據當前季度查詢對應的人口數
    SELECT population INTO pop_count
    FROM quarterly_population
    WHERE city = city_name
      AND quarter = current_quarter
      AND year = current_year;
    
    RETURN IFNULL(pop_count, 0);
    
    -- 注意:即使 city_name 參數相同
    -- 在不同的季度呼叫這個函式會得到不同的結果
    -- 因此必須宣告為 NOT DETERMINISTIC
END //

DELIMITER ;

-- 範例 3: 必須使用 NOT DETERMINISTIC 的情況 - 包含隨機性
-- 這個函式使用隨機數產生器,每次呼叫都可能回傳不同結果
DELIMITER //

CREATE FUNCTION f_get_random_city()
RETURNS VARCHAR(100)
NOT DETERMINISTIC                -- 非確定性,因為包含隨機選擇
READS SQL DATA
COMMENT '隨機選取一個縣市,每次呼叫可能回傳不同結果'
BEGIN
    DECLARE city_name VARCHAR(100);
    
    -- 使用 RAND() 隨機選取一個縣市
    -- RAND() 是非確定性函式,每次呼叫產生不同的隨機數
    SELECT city INTO city_name
    FROM city_reference
    ORDER BY RAND()              -- 隨機排序
    LIMIT 1;
    
    RETURN city_name;
    
    -- 即使沒有參數,每次呼叫也可能得到不同結果
    -- 因此必須宣告為 NOT DETERMINISTIC
END //

DELIMITER ;

-- 範例 4: 錯誤設定確定性特性的危險
-- 這個範例展示錯誤設定特性可能導致的問題
DELIMITER //

-- 錯誤範例:將非確定性函式錯誤標記為 DETERMINISTIC
CREATE FUNCTION f_wrong_deterministic_example()
RETURNS DATETIME
DETERMINISTIC                    -- 錯誤!此函式實際上是非確定性的
NO SQL
COMMENT '錯誤範例:不應該將 NOW() 標記為確定性'
BEGIN
    -- NOW() 每次呼叫回傳不同時間
    -- 但函式被標記為 DETERMINISTIC
    -- 可能導致 MySQL 錯誤快取結果
    RETURN NOW();
END //

DELIMITER ;

-- 測試錯誤設定的影響
SELECT 
    f_wrong_deterministic_example() AS 第一次呼叫,
    SLEEP(2),                           -- 等待 2 秒
    f_wrong_deterministic_example() AS 第二次呼叫;
-- 如果 MySQL 快取了第一次的結果,兩次呼叫可能回傳相同時間
-- 這明顯不符合函式的預期行為

-- 正確的寫法應該是:
DROP FUNCTION IF EXISTS f_wrong_deterministic_example;

DELIMITER //

CREATE FUNCTION f_correct_nondeterministic_example()
RETURNS DATETIME
NOT DETERMINISTIC                -- 正確:明確宣告為非確定性
NO SQL
COMMENT '正確範例:NOW() 函式應標記為非確定性'
BEGIN
    RETURN NOW();
END //

DELIMITER ;

-- ============================================
-- SQL 資料存取特性的完整解析
-- ============================================

-- 特性 1: NO SQL - 純計算函式
-- 適用於不涉及任何資料庫操作的純計算邏輯
DELIMITER //

CREATE FUNCTION f_calculate_percentage_no_sql(
    part_value DECIMAL(10,2),
    total_value DECIMAL(10,2)
)
RETURNS DECIMAL(5,2)
DETERMINISTIC                    -- 純計算是確定性的
NO SQL                           -- 不包含任何 SQL 陳述式
COMMENT '計算百分比,純數學運算不涉及資料庫'
BEGIN
    -- 純粹的數學計算
    -- 不涉及任何資料庫表格的查詢或操作
    
    -- 處理除以零的情況
    IF total_value = 0 THEN
        RETURN 0;
    END IF;
    
    -- 計算百分比並四捨五入到小數點後兩位
    RETURN ROUND((part_value / total_value) * 100, 2);
    
    -- 因為完全不涉及資料庫操作
    -- MySQL 可以更積極地快取這個函式的結果
END //

DELIMITER ;

-- 特性 2: CONTAINS SQL - 包含 SQL 但不存取資料
-- 適用於包含 SQL 語法結構但不實際讀寫資料的邏輯
DELIMITER //

CREATE FUNCTION f_validate_city_name_contains_sql(
    city_name VARCHAR(100)
)
RETURNS BOOLEAN
DETERMINISTIC
CONTAINS SQL                     -- 包含 SQL 語法但不存取資料表
COMMENT '驗證縣市名稱格式,使用 SQL 語法但不查詢資料庫'
BEGIN
    -- 這個函式只進行邏輯判斷
    -- 雖然使用了 IF、CASE 等 SQL 控制結構
    -- 但不實際存取任何資料庫表格
    
    -- 檢查是否為 NULL 或空字串
    IF city_name IS NULL OR LENGTH(TRIM(city_name)) = 0 THEN
        RETURN FALSE;
    END IF;
    
    -- 檢查長度是否合理
    IF LENGTH(city_name) > 50 THEN
        RETURN FALSE;
    END IF;
    
    -- 檢查是否包含非法字元
    -- 這裡只是範例,實際驗證邏輯可能更複雜
    IF city_name REGEXP '[0-9]' THEN
        -- 縣市名稱不應包含數字
        RETURN FALSE;
    END IF;
    
    -- 通過所有驗證
    RETURN TRUE;
END //

DELIMITER ;

-- 特性 3: READS SQL DATA - 只讀取資料
-- 這是最常用的特性,適用於查詢資料但不修改的函式
DELIMITER //

CREATE FUNCTION f_calculate_population_density_reads_sql(
    city_name VARCHAR(100)
)
RETURNS DECIMAL(10,2)
DETERMINISTIC
READS SQL DATA                   -- 只讀取資料,不進行修改
COMMENT '計算人口密度,需要查詢資料庫但不修改資料'
BEGIN
    DECLARE pop INT;
    DECLARE area DECIMAL(10,2);
    DECLARE density DECIMAL(10,2);
    
    -- 從資料庫讀取人口數與面積
    -- 這是典型的只讀操作
    SELECT population, area_km2
    INTO pop, area
    FROM city_statistics
    WHERE city = city_name;
    
    -- 進行計算但不寫回資料庫
    -- 計算結果只是函式的回傳值
    IF area > 0 THEN
        SET density = pop / area;
    ELSE
        SET density = 0;
    END IF;
    
    RETURN ROUND(density, 2);
    
    -- 注意:雖然這個函式讀取了資料庫
    -- 但它不會修改任何資料
    -- 因此適合標記為 READS SQL DATA
END //

DELIMITER ;

-- 特性 4: MODIFIES SQL DATA - 修改資料
-- 注意:在函式中修改資料通常不是最佳實踐
-- 建議改用儲存程式來處理需要修改資料的邏輯
DELIMITER //

-- 這個範例僅用於說明 MODIFIES SQL DATA 的使用
-- 實務上應該使用儲存程式而非函式來修改資料
CREATE FUNCTION f_increment_query_counter_modifies_sql(
    city_name VARCHAR(100)
)
RETURNS INT
NOT DETERMINISTIC                -- 修改資料的函式通常是非確定性的
MODIFIES SQL DATA                -- 會執行 INSERT/UPDATE/DELETE
COMMENT '增加查詢計數器 - 不建議在函式中修改資料'
BEGIN
    -- 更新查詢計數
    UPDATE city_query_log
    SET query_count = query_count + 1,
        last_query_time = NOW()
    WHERE city = city_name;
    
    -- 如果記錄不存在,插入新記錄
    IF ROW_COUNT() = 0 THEN
        INSERT INTO city_query_log (city, query_count, last_query_time)
        VALUES (city_name, 1, NOW());
    END IF;
    
    -- 回傳當前的查詢次數
    RETURN (SELECT query_count FROM city_query_log WHERE city = city_name);
    
    -- 重要提示:
    -- 1. 在函式中修改資料可能影響複製環境的一致性
    -- 2. 函式中的資料修改不易進行錯誤處理與交易控制
    -- 3. 建議改用儲存程式來處理資料修改邏輯
END //

DELIMITER ;

-- ============================================
-- 效能最佳化範例:減少函式呼叫次數
-- ============================================

-- 效能問題範例:在大型查詢中重複呼叫多個函式
-- 這個查詢的效能可能不佳,因為每筆資料都要呼叫三個函式
SELECT
    city,
    population,
    -- 呼叫函式 1:取得平均氣溫
    f_get_average_temperature(city) AS 平均氣溫,
    -- 呼叫函式 2:取得平均雨量
    f_get_average_rainfall(city) AS 平均雨量,
    -- 呼叫函式 3:計算舒適度指數
    -- 這個函式內部又呼叫了前兩個函式
    f_calculate_comfort_index(
        f_get_average_temperature(city),
        f_get_average_rainfall(city)
    ) AS 舒適度指數
FROM city_reference
WHERE population > 100000;

-- 問題分析:
-- 1. 如果 city_reference 有 100 筆記錄
-- 2. 每筆記錄都會呼叫 f_get_average_temperature 和 f_get_average_rainfall
-- 3. 共執行 200 次函式呼叫,每次都可能涉及資料庫查詢
-- 4. 如果函式未標記為 DETERMINISTIC,無法使用快取
-- 5. 總執行時間 = 100 × (呼叫時間1 + 呼叫時間2 + 呼叫時間3)

-- 最佳化方案 1:使用 JOIN 預先取得資料,減少函式呼叫
-- 將資料查詢從函式內部移到主查詢的 JOIN 中
SELECT
    cr.city,
    cr.population,
    cd.avg_temperature AS 平均氣溫,
    cd.avg_rainfall AS 平均雨量,
    -- 只呼叫一次計算函式,參數直接使用 JOIN 取得的資料
    f_calculate_comfort_index(
        cd.avg_temperature,
        cd.avg_rainfall
    ) AS 舒適度指數
FROM city_reference cr
JOIN climate_data cd ON cr.city = cd.city
WHERE cr.population > 100000;

-- 優勢分析:
-- 1. 氣溫和雨量資料透過 JOIN 一次性取得,不需要函式呼叫
-- 2. 只有舒適度計算需要呼叫函式,且參數不涉及資料庫查詢
-- 3. 如果 climate_data 表格有適當索引,JOIN 效能優異
-- 4. 總執行時間 = JOIN 時間 + 100 × 計算時間(純數學運算)

-- 最佳化方案 2:確保函式被標記為 DETERMINISTIC
-- 將純計算函式正確標記,啟用快取機制
DELIMITER //

CREATE FUNCTION f_calculate_comfort_index_optimized(
    temperature DECIMAL(5,2),
    rainfall DECIMAL(8,2)
)
RETURNS DECIMAL(5,2)
DETERMINISTIC                    -- 啟用結果快取
NO SQL                           -- 純計算,不存取資料庫
COMMENT '最佳化版本:純計算舒適度指數,支援快取'
BEGIN
    DECLARE comfort_value DECIMAL(5,2);
    
    -- 基於溫度和降雨量的舒適度計算公式
    -- 25°C 時舒適度最高,降雨越少舒適度越高
    SET comfort_value = (
        (25 - ABS(temperature - 25)) * 0.4 +  -- 溫度因子
        (100 - rainfall) * 0.01 * 0.6          -- 降雨因子
    );
    
    -- 確保回傳值在 0-100 範圍內
    IF comfort_value < 0 THEN
        RETURN 0;
    ELSEIF comfort_value > 100 THEN
        RETURN 100;
    ELSE
        RETURN ROUND(comfort_value, 2);
    END IF;
END //

DELIMITER ;

-- ============================================
-- 效能最佳化範例:避免在 WHERE 子句中對索引欄位使用函式
-- ============================================

-- 效能問題範例:對索引欄位使用函式導致索引失效
-- 即使 population 欄位有索引,這個查詢也可能執行全表掃描
SELECT *
FROM city_reference
WHERE f_calculate_population_density(city, population, area_km2) > 1000;

-- 問題分析:
-- 1. WHERE 子句中對 population 使用了函式
-- 2. MySQL 無法使用 population 欄位上的索引
-- 3. 必須掃描全表,對每筆記錄執行函式計算
-- 4. 如果資料表有百萬筆記錄,效能會非常差

-- 最佳化方案 1:將函式移到 SELECT 清單,WHERE 使用原始欄位
-- 讓 WHERE 子句可以使用索引進行快速過濾
SELECT
    city,
    population,
    area_km2,
    f_calculate_population_density(city, population, area_km2) AS population_density
FROM city_reference
WHERE population / area_km2 > 1000;  -- 直接計算,可使用索引
-- 注意:這裡假設 (population / area_km2) 的計算方式與函式內部相同

-- 優勢分析:
-- 1. WHERE 子句使用原始欄位的計算,可以利用索引
-- 2. 先透過索引快速過濾出符合條件的記錄
-- 3. 只對過濾後的記錄執行函式(在 SELECT 清單中)
-- 4. 大幅減少函式執行次數

-- 最佳化方案 2:建立計算欄位並建立索引
-- 這是最根本的解決方案,但需要修改資料表結構
ALTER TABLE city_reference
ADD COLUMN population_density DECIMAL(10,2)
GENERATED ALWAYS AS (population / area_km2) STORED;

-- 為計算欄位建立索引
CREATE INDEX idx_population_density
ON city_reference(population_density);

-- 現在查詢可以充分利用索引
SELECT city, population, area_km2, population_density
FROM city_reference
WHERE population_density > 1000;

-- 優勢分析:
-- 1. population_density 是預先計算並儲存的
-- 2. 有專門的索引支援快速查詢
-- 3. 不需要在查詢時重複計算
-- 4. 是效能最佳但需要額外儲存空間的方案

-- ============================================
-- 效能最佳化範例:使用 CTE 避免重複計算
-- ============================================

-- 效能問題範例:相同的計算在查詢中重複執行
SELECT
    order_id,
    quantity,
    unit_price,
    tax_rate,
    -- 計算小計(第 1 次)
    quantity * unit_price AS subtotal,
    -- 計算稅金(第 2 次計算小計)
    quantity * unit_price * tax_rate AS tax,
    -- 計算總計(第 3 次計算小計)
    quantity * unit_price * (1 + tax_rate) AS total,
    -- 判斷客戶類型(第 4 次計算小計)
    CASE
        WHEN quantity * unit_price > 10000 THEN 'VIP'
        WHEN quantity * unit_price > 5000 THEN 'Premium'
        ELSE 'Regular'
    END AS customer_type
FROM orders
WHERE order_date >= '2024-01-01';

-- 問題分析:
-- 1. quantity * unit_price 被計算了 4 次
-- 2. 雖然是簡單運算,但在大量資料時累積成本顯著
-- 3. 程式碼可讀性差,維護困難

-- 最佳化方案:使用 CTE 一次計算,多次使用
WITH calculated_orders AS (
    -- 第一階段:計算基礎值
    SELECT
        order_id,
        quantity,
        unit_price,
        tax_rate,
        quantity * unit_price AS subtotal  -- 只計算一次
    FROM orders
    WHERE order_date >= '2024-01-01'
)
-- 第二階段:使用預先計算的值
SELECT
    order_id,
    quantity,
    unit_price,
    tax_rate,
    subtotal,
    subtotal * tax_rate AS tax,           -- 重複使用 subtotal
    subtotal * (1 + tax_rate) AS total,   -- 重複使用 subtotal
    CASE
        WHEN subtotal > 10000 THEN 'VIP'
        WHEN subtotal > 5000 THEN 'Premium'
        ELSE 'Regular'
    END AS customer_type
FROM calculated_orders;

-- 優勢分析:
-- 1. quantity * unit_price 只計算一次
-- 2. 後續所有使用都直接參考 subtotal
-- 3. 程式碼結構清晰,易於理解和維護
-- 4. 效能提升與可讀性提升兼得

-- ============================================
-- 效能監控與診斷
-- ============================================

-- 步驟 1:啟用 Performance Schema 的函式監控
-- 這個設定通常在 MySQL 設定檔中配置,或在執行階段啟用
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE 'statement/sp/%';

-- 步驟 2:查詢函式執行統計資訊
-- 找出執行最頻繁和最耗時的函式
SELECT
    OBJECT_SCHEMA AS 資料庫,
    OBJECT_NAME AS 函式名稱,
    OBJECT_TYPE AS 物件類型,
    COUNT_EXECUTE AS 執行次數,
    SUM_TIMER_WAIT / 1000000000000 AS 總執行時間秒,
    AVG_TIMER_WAIT / 1000000000000 AS 平均執行時間秒,
    MIN_TIMER_WAIT / 1000000000000 AS 最小執行時間秒,
    MAX_TIMER_WAIT / 1000000000000 AS 最大執行時間秒
FROM performance_schema.events_statements_summary_by_program
WHERE OBJECT_TYPE = 'FUNCTION'
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

-- 步驟 3:識別需要最佳化的函式
-- 找出執行次數多且平均耗時較長的函式
SELECT
    OBJECT_NAME AS 需要最佳化的函式,
    COUNT_EXECUTE AS 執行次數,
    ROUND(AVG_TIMER_WAIT / 1000000000000, 4) AS 平均執行時間秒,
    ROUND(SUM_TIMER_WAIT / 1000000000000, 2) AS 總耗時秒,
    -- 計算該函式佔總體執行時間的比例
    ROUND(
        (SUM_TIMER_WAIT * 100.0) / 
        (SELECT SUM(SUM_TIMER_WAIT) 
         FROM performance_schema.events_statements_summary_by_program 
         WHERE OBJECT_TYPE = 'FUNCTION'
        ), 2
    ) AS 佔總體時間百分比
FROM performance_schema.events_statements_summary_by_program
WHERE OBJECT_TYPE = 'FUNCTION'
  AND COUNT_EXECUTE > 1000              -- 執行次數超過 1000 次
  AND AVG_TIMER_WAIT > 10000000         -- 平均執行時間超過 0.01 秒
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

-- 步驟 4:清除統計資訊以進行新的測試
-- 在最佳化後,清除舊的統計資訊以評估改進效果
TRUNCATE TABLE performance_schema.events_statements_summary_by_program;