H2 數據庫索引優化與存儲過程相關關鍵字

在 H2 數據庫的進階使用中，索引優化關鍵字是提升查詢性能的核心，存儲過程相關關鍵字則能封裝複雜業務邏輯，兩者結合能讓數據庫應對高併發、複雜計算等場景時更遊刃有餘。這些關鍵字看似抽象，實則實用性極強，掌握它們能讓數據庫操作從 “能用” 升級到 “好用”。

一、索引優化進階關鍵字

基礎的CREATE INDEX之外，H2 還提供了更靈活的索引相關關鍵字，適配不同查詢場景的性能需求。

CREATE UNIQUE INDEX是UNIQUE約束的進階形式，既能保證字段唯一性，又能提升查詢速度，尤其適合需要頻繁查詢的唯一字段。比如給用户表的手機號字段創建唯一索引：

-- 給users表的phone字段創建唯一索引，兼顧唯一性約束和查詢性能
CREATE UNIQUE INDEX idx_users_phone ON users(phone);

這樣既避免了手機號重複，又能讓WHERE phone = '13800138000'這類查詢直接通過索引定位數據，效率大幅提升。

DROP INDEX用於刪除無用索引，當業務變更導致某些索引不再被使用時，及時刪除能減少寫入操作的開銷。示例如下：

-- 刪除不再使用的訂單號索引
DROP INDEX idx_orders_no ON orders;

刪除索引前建議通過EXPLAIN確認索引是否被查詢使用，避免誤刪有用索引。

CREATE INDEX ... INCLUDE是 H2 支持的覆蓋索引關鍵字，能將查詢常用的非索引字段包含到索引中，實現 “索引覆蓋查詢”，無需回表就能獲取所有需要的數據。比如查詢訂單時經常需要獲取訂單號和支付金額，可創建包含這兩個字段的索引：

-- 創建覆蓋索引，包含order_no和pay_amount字段
CREATE INDEX idx_orders_time_include ON orders(order_time) INCLUDE (order_no, pay_amount);

之後執行SELECT order_no, pay_amount FROM orders WHERE order_time > '2024-01-01'時，數據庫直接通過索引就能返回結果，無需訪問表數據，查詢速度更快。

二、存儲過程核心關鍵字

存儲過程能將多條 SQL 語句封裝成一個可調用的單元，CREATE PROCEDURE、BEGIN、END、OUT等關鍵字是構建存儲過程的基礎，讓複雜邏輯的複用變得簡單。

1. 基礎存儲過程創建與調用

用CREATE PROCEDURE定義存儲過程，BEGIN和END包裹執行邏輯，示例：創建一個批量插入訂單數據的存儲過程：

-- 創建批量插入訂單的存儲過程，接收用户ID和訂單數量參數
CREATE PROCEDURE batch_insert_orders(IN uid INT, IN order_count INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    -- 循環插入指定數量的訂單
    WHILE i <= order_count DO
        INSERT INTO orders(user_id, order_no, order_time)
        VALUES(uid, CONCAT('ORD_', DATE_FORMAT(NOW(), '%Y%m%d'), '_', i), NOW());
        SET i = i + 1;
    END WHILE;
END;

調用存儲過程仍使用CALL關鍵字，比如給用户 ID 為 1 的用户插入 5 條訂單：

CALL batch_insert_orders(1, 5);

2. 帶輸出參數的存儲過程

OUT關鍵字用於定義輸出參數，能讓存儲過程返回計算結果。比如創建一個統計用户訂單總金額的存儲過程：

-- 創建統計用户訂單總金額的存儲過程，接收用户ID，輸出總金額
CREATE PROCEDURE calc_user_order_total(IN uid INT, OUT total DECIMAL(10,2))
BEGIN
    SELECT COALESCE(SUM(pay_amount), 0) INTO total
    FROM orders
    WHERE user_id = uid;
END;

調用時需要先聲明變量接收輸出結果（H2 命令行或客户端中）：

-- 聲明變量接收總金額
DECLARE total_amount DECIMAL(10,2);
-- 調用存儲過程
CALL calc_user_order_total(1, total_amount);
-- 查看結果
SELECT total_amount;

其中COALESCE函數用於處理無訂單時的空值，確保返回 0 而非 NULL。

3. 存儲過程中的條件判斷

IF、ELSE關鍵字讓存儲過程支持條件邏輯，比如創建一個根據訂單金額發放優惠券的存儲過程：

CREATE PROCEDURE give_coupon_by_order(IN order_id INT)
BEGIN
    DECLARE amount DECIMAL(10,2);
    DECLARE uid INT;
    -- 獲取訂單金額和用户ID
    SELECT pay_amount, user_id INTO amount, uid FROM orders WHERE order_id = order_id;
    -- 根據金額發放不同面額優惠券
    IF amount >= 1000 THEN
        INSERT INTO user_coupons(user_id, coupon_amount, expire_time)
        VALUES(uid, 100, DATE_ADD(NOW(), INTERVAL 30 DAY));
    ELSEIF amount >= 500 THEN
        INSERT INTO user_coupons(user_id, coupon_amount, expire_time)
        VALUES(uid, 50, DATE_ADD(NOW(), INTERVAL 30 DAY));
    ELSE
        INSERT INTO user_coupons(user_id, coupon_amount, expire_time)
        VALUES(uid, 20, DATE_ADD(NOW(), INTERVAL 15 DAY));
    END IF;
END;

調用後會根據訂單金額自動給用户發放對應優惠券，無需手動判斷執行。

這些索引優化和存儲過程相關的關鍵字，前者聚焦性能提升，後者側重邏輯封裝。在實際開發中，合理運用CREATE UNIQUE INDEX、INCLUDE等索引關鍵字優化查詢速度，用CREATE PROCEDURE、OUT、IF等關鍵字封裝重複複雜的業務邏輯，能顯著提升開發效率和系統性能，讓 H2 數據庫更好地支撐業務發展。