大家好，願神的平安、憐憫、祝福臨到你們

SQL（結構化查詢語言）是管理和操作關係資料庫的重要工具。雖然基本的 SQL 技能可以幫助您入門，但高級 SQL 技術可以大大增強您處理複雜查詢和優化資料庫效能的能力。本文深入探討高階 SQL 主題，重點在於複雜的查詢最佳化策略、高階聯結類型以及SELECT語句的複雜性。

進階查詢最佳化技術

最佳化 SQL 查詢是資料庫管理員和開發人員的關鍵技能。進階查詢最佳化超越了基本索引和查詢重構，還包括一系列複雜的技術。

1. 查詢執行計劃

了解查詢的執行計劃對於最佳化至關重要。執行計劃顯示 SQL 引擎如何執行查詢，揭示潛在的瓶頸。

• EXPLAIN ： EXPLAIN語句提供對查詢執行方式的深入了解，使您能夠辨識效率低下的情況。

    EXPLAIN SELECT column1, column2 FROM table_name WHERE condition;

• ANALYZE ： ANALYZE語句與EXPLAIN結合使用，執行查詢並提供執行時統計訊息，從而更深入地了解查詢性能。

    EXPLAIN ANALYZE SELECT column1, column2 FROM table_name WHERE condition;

2. 子查詢最佳化

子查詢有時可以替換為更有效率的聯結或WITH子句（通用表表達式）。

• 用連接替換子查詢：

    -- Subquery
    SELECT * FROM table1 WHERE column1 IN (SELECT column1 FROM table2);

    -- Equivalent Join
    SELECT table1.* FROM table1 INNER JOIN table2 ON table1.column1 = table2.column1;

• 使用通用表格表達式 (CTE) ：

    WITH CTE AS (
        SELECT column1, column2 FROM table_name WHERE condition
    )
    SELECT * FROM CTE WHERE another_condition;

3. 索引策略

進階索引策略包括使用複合索引和覆蓋索引。

• 複合索引：包含多個欄位的索引可以加快對這些欄位進行篩選的查詢速度。

    CREATE INDEX idx_composite ON table_name (column1, column2);

• 覆蓋索引：包含查詢檢索到的所有列的索引可以顯著提高效能。

    CREATE INDEX idx_covering ON table_name (column1, column2, column3);

4. 分區

將大表劃分為更小、更易於管理的部分可以透過限制掃描的資料量來提高查詢效能。

• 範圍劃分：

    CREATE TABLE orders (
        order_id INT,
        order_date DATE,
        ...
    ) PARTITION BY RANGE (order_date) (
        PARTITION p0 VALUES LESS THAN ('2024-01-01'),
        PARTITION p1 VALUES LESS THAN ('2025-01-01'),
        ...
    );

• 哈希分區：根據雜湊函數將資料分佈在指定數量的分區上，提供均勻分佈。

    CREATE TABLE users (
        user_id INT,
        username VARCHAR(255),
        ...
    ) PARTITION BY HASH(user_id) PARTITIONS 4;

• 清單分區：根據值清單將資料劃分為多個分區。

    CREATE TABLE sales (
        sale_id INT,
        region VARCHAR(255),
        ...
    ) PARTITION BY LIST (region) (
        PARTITION p0 VALUES IN ('North', 'South'),
        PARTITION p1 VALUES IN ('East', 'West')
    );

5. 物化視圖

物化視圖實體儲存查詢結果，並且可以定期刷新，從而提高頻繁執行的複雜查詢的效能。

• 建立物化視圖：

    CREATE MATERIALIZED VIEW sales_summary AS
    SELECT region, SUM(sales_amount) AS total_sales
    FROM sales
    GROUP BY region;

• 刷新物化視圖：

    REFRESH MATERIALIZED VIEW sales_summary;

筆記：

在 MySQL 中，存在視圖，但物化視圖本身並不存在。 MySQL支援標準視圖，這些視圖是儲存查詢定義並在查詢時動態產生結果集的虛擬表。但是，它沒有對物化視圖的內建支持，物化視圖物理儲存結果集。

MySQL 中的視圖

建立視圖

您可以使用CREATE VIEW語句在 MySQL 中建立視圖。這是一個例子：

CREATE VIEW ActiveCustomers AS
SELECT CustomerID, CustomerName, ContactName, Country
FROM Customers
WHERE Status = 'Active';

這將建立一個名為ActiveCustomers的視圖，其中僅包含Customers表中的活動客戶。查詢此視圖如下所示：

SELECT * FROM ActiveCustomers;

更新視圖

可以使用CREATE OR REPLACE VIEW語句更新檢視：

CREATE OR REPLACE VIEW ActiveCustomers AS
SELECT CustomerID, CustomerName, ContactName, Country
FROM Customers
WHERE Status = 'Active' AND Country = 'USA';

這會將ActiveCustomers檢視修改為僅包含來自美國的活躍客戶。

刪除視圖

您可以使用DROP VIEW語句刪除檢視：

DROP VIEW ActiveCustomers;

MySQL 中的物化視圖

MySQL 本身不支援物化視圖，但有一些變通方法可以實現類似的功能。這裡有幾種方法：

1. 使用表格和計劃更新

一種常見的方法是建立一個表來儲存查詢結果並使用計劃事件（cron 作業）或觸發器定期更新它。

建立表

首先，建立一個表格來儲存結果：

CREATE TABLE MaterializedActiveCustomers AS
SELECT CustomerID, CustomerName, ContactName, Country
FROM Customers
WHERE Status = 'Active';

更新表格

使用計劃事件定期更新表。此範例使用 MySQL 事件每小時更新一次表格：

CREATE EVENT UpdateMaterializedActiveCustomers
ON SCHEDULE EVERY 1 HOUR
DO
BEGIN
    DELETE FROM MaterializedActiveCustomers;
    INSERT INTO MaterializedActiveCustomers
    SELECT CustomerID, CustomerName, ContactName, Country
    FROM Customers
    WHERE Status = 'Active';
END;

此事件每小時都會清除MaterializedActiveCustomers表並使用最新的活躍客戶重新填充。

2. 使用觸發器

另一種方法是使用觸發器使表與基底表保持同步。然而，這可能會變得複雜，對於大型資料集可能效率不高。

使用觸發器的範例

建立表

首先，建立表：

CREATE TABLE MaterializedActiveCustomers AS
SELECT CustomerID, CustomerName, ContactName, Country
FROM Customers
WHERE Status = 'Active';

建立觸發器

建立觸發器以保持物化表更新：

DELIMITER //

CREATE TRIGGER after_customer_insert
AFTER INSERT ON Customers
FOR EACH ROW
BEGIN
    IF NEW.Status = 'Active' THEN
        INSERT INTO MaterializedActiveCustomers (CustomerID, CustomerName, ContactName, Country)
        VALUES (NEW.CustomerID, NEW.CustomerName, NEW.ContactName, NEW.Country);
    END IF;
END //

CREATE TRIGGER after_customer_update
AFTER UPDATE ON Customers
FOR EACH ROW
BEGIN
    IF OLD.Status = 'Active' AND NEW.Status != 'Active' THEN
        DELETE FROM MaterializedActiveCustomers WHERE CustomerID = OLD.CustomerID;
    ELSEIF NEW.Status = 'Active' THEN
        REPLACE INTO MaterializedActiveCustomers (CustomerID, CustomerName, ContactName, Country)
        VALUES (NEW.CustomerID, NEW.CustomerName, NEW.ContactName, NEW.Country);
    END IF;
END //

CREATE TRIGGER after_customer_delete
AFTER DELETE ON Customers
FOR EACH ROW
BEGIN
    DELETE FROM MaterializedActiveCustomers WHERE CustomerID = OLD.CustomerID;
END //

DELIMITER ;

這些觸發器將確保MaterializedActiveCustomers表隨著Customers表的變更而保持更新。

結論

雖然 MySQL 支援視圖，但它本身不支援物化視圖。但是，您可以使用具有計劃更新或觸發器的表來實現類似的功能。透過使用這些解決方法，您可以維護可以快速查詢的預先計算的結果，類似於其他資料庫系統中的物化視圖。

高級連接類型和技術

連接是 SQL 的基礎，它允許您組合多個表中的資料。除了基本連接之外，高級連接技術還可以處理更複雜的需求。

1. 自加入

自連接是一種常規連接，但表與自身連接。它對於比較同一表中的行很有用。

SELECT a.employee_id, a.name, b.name AS manager_name
FROM employees a
INNER JOIN employees b ON a.manager_id = b.employee_id;

2. 橫向連接

LATERAL連線允許子查詢在FROM子句中引用前面表格中的欄位。這對於更複雜的查詢很有用。

SELECT a.*, b.*
FROM table1 a
LEFT JOIN LATERAL (
    SELECT *
    FROM table2 b
    WHERE b.column1 = a.column1
    ORDER BY b.column2 DESC
    LIMIT 1
) b ON TRUE;

3. 使用 COALESCE 進行完全外部連接

處理需要完整外連接但希望避免結果中出現NULL值的情況。

SELECT COALESCE(a.column1, b.column1) AS column1, a.column2, b.column2
FROM table1 a
FULL OUTER JOIN table2 b ON a.column1 = b.column1;

4. 進階連接過濾器

在連接中應用複雜的條件以更精確地過濾結果。

SELECT a.column1, b.column2
FROM table1 a
INNER JOIN table2 b ON a.column1 = b.column1 AND a.date_column BETWEEN '2023-01-01' AND '2023-12-31';

5. 反連接和半連接

這些連接分別對於排除和包含查詢很有用。

• 反連接：從左表中檢索右表中沒有匹配行的行。

    SELECT a.*
    FROM table1 a
    LEFT JOIN table2 b ON a.column1 = b.column1
    WHERE b.column1 IS NULL;

• 半連接：從左表中檢索右表中存在一個或多個匹配項的行。

    SELECT a.*
    FROM table1 a
    WHERE EXISTS (SELECT 1 FROM table2 b WHERE a.column1 = b.column1);

高級SELECT語句

SELECT語句可以透過進階功能進行擴展，以滿足複雜的資料檢索要求。

1. 視窗函數

視窗函數對與目前行相關的一組表行執行計算，提供強大的分析功能。

• 行號：

    SELECT column1, column2, ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY column2) AS row_num
    FROM table_name;

• 執行總計：

    SELECT column1, column2, SUM(column2) OVER (ORDER BY column1) AS running_total
    FROM table_name;

• 排行：

    SELECT column1, column2, RANK() OVER (PARTITION BY column1 ORDER BY column2) AS rank
    FROM table_name;

• 移動平均線：

    SELECT column1, column2, AVG(column2) OVER (PARTITION BY column1 ORDER BY column2 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_avg
    FROM table_name;

2. 遞迴 CTE

遞歸 CTE 可讓您執行遞歸查詢，這對於分層資料很有用。

WITH RECURSIVE cte AS (
    SELECT column1, column2
    FROM table_name
    WHERE condition
    UNION ALL
    SELECT t.column1, t.column2
    FROM table_name t
    INNER JOIN cte ON t.column1 = cte.column1
)
SELECT * FROM cte;

3.JSON函數

現代 SQL 資料庫通常包含處理 JSON 資料的函數，可讓您儲存和查詢 JSON 文件。

• 提取 JSON 值：

    SELECT json_column->>'key' AS value
    FROM table_name;

• 聚合成 JSON ：

    SELECT json_agg(row_to_json(t))
    FROM (SELECT column1, column2 FROM table_name) t;

• 更新 JSON 資料：

    UPDATE table_name
    SET json_column = jsonb_set(json_column, '{key}', '"new_value"', true)
    WHERE condition;

4. 資料透視

透視將行轉換為列，提供了一種重新組織和匯總資料以用於報告目的的方法。

• 使用 CASE 語句進行透視：

    SELECT
        category,
        SUM(CASE WHEN year = 2021 THEN sales ELSE 0 END) AS sales_2021,
        SUM(CASE WHEN year = 2022 THEN sales ELSE 0 END) AS sales_2022
    FROM sales_data
    GROUP BY category;

5.動態SQL

動態 SQL 允許在執行時間建立和執行 SQL 語句，為需要動態產生的複雜查詢提供靈活性。

• 執行動態SQL ：

    EXECUTE 'SELECT * FROM ' || table_name || ' WHERE ' || condition;

• 使用準備好的語句：

    PREPARE stmt AS SELECT * FROM table_name WHERE column1 = $1;
    EXECUTE stmt('value');

結論

掌握進階 SQL 技術可以讓您最佳化資料庫效能並輕鬆處理複雜查詢。了解執行計劃、利用高階聯結、利用複雜的SELECT語句、實作進階索引策略是精通 SQL 的關鍵。透過將這些技術整合到您的工作流程中，您可以顯著提高資料庫驅動應用程式的效率和可擴展性。

進階 SQL 技能可讓您處理複雜的資料操作和檢索任務，確保您的應用程式能夠有效率且有效地處理大量資料。無論您是資料庫管理員、開發人員還是資料分析師，這些進階 SQL 技術都將使您能夠充分利用關聯式資料庫，從而獲得更好的效能、更深入的見解和更強大的應用程式。

原文出處：https://dev.to/bilelsalemdev/advanced-sql-mastering-query-optimization-and-complex-joins-4gph