通過示例提高查詢性能

我試圖想出一種改進查詢的方法，使用的模式是這樣的：

CREATE TABLE `orders` (
  `id` int PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `store_id` INTEGER NOT NULL,
  `billing_profile_id` INTEGER NOT NULL,
  `billing_address_id` INTEGER NULL,
  `total` DECIMAL(8, 2) NOT NULL
);

CREATE TABLE `billing_profiles` (
  `id` int PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `name` TEXT NOT NULL
);

CREATE TABLE `billing_addresses` (
  `id` int PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `address` TEXT NOT NULL
);

CREATE TABLE `stores` (
  `id` int PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `name` TEXT NOT NULL
);

我正在執行的查詢：

SELECT bp.name,
       ba.address,
       s.name,
       Sum(o.total) AS total
FROM   billing_profiles bp,
       stores s,
       orders o
       LEFT JOIN billing_addresses ba
              ON o.billing_address_id = ba.id
WHERE  o.billing_profile_id = bp.id
       AND s.id = o.store_id
GROUP  BY bp.name,
          ba.address,
          s.name;

這是EXPLAIN：

 ---- ------------- ------- ------------ -------- --------------- --------- --------- ------------------------------ ------- ---------- -------------------------------------------- 
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                          | rows  | filtered | Extra                                      |
 ---- ------------- ------- ------------ -------- --------------- --------- --------- ------------------------------ ------- ---------- -------------------------------------------- 
|  1 | SIMPLE      | bp    | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL                         |155000 |   100.00 | Using temporary                            |
|  1 | SIMPLE      | o     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                         |220000 |    33.33 | Using where; Using join buffer (hash join) |
|  1 | SIMPLE      | ba    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | factory.o.billing_address_id |    1  |   100.00 | NULL                                       |
|  1 | SIMPLE      | s     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | factory.o.store_id           |    1  |   100.00 | NULL                                       |
 ---- ------------- ------- ------------ -------- --------------- --------- --------- ------------------------------ ------ ---------- -------------------------------------------- 

我面臨的問題是這個查詢需要 30 秒才能執行，我們有超過 200000 個訂單和 150000 billing_profiles/billing_addresses。

關于索引/約束我應該怎么做才能使這個查詢更快地執行？

編輯：在評論中提出一些建議后，我將查詢編輯為：

SELECT bp.name,
       ba.address,
       s.name,
       Sum(o.total) AS total
FROM   orders o
       INNER JOIN billing_profiles bp
               ON o.billing_profile_id = bp.id
       INNER JOIN stores s
               ON s.id = o.store_id
       LEFT JOIN billing_addresses ba
              ON o.billing_address_id = ba.id
GROUP  BY bp.name,
          ba.address,
          s.name; 

但仍然需要太多時間。

uj5u.com熱心網友回復：

我過去使用過并且在許多情況下對 MySQL 有幫助的一件事是使用該STRAIGHT_JOIN子句告訴引擎按照列出的順序進行查詢。

我已將您的查詢清理為正確的 JOIN 背景關系。由于 ORDER 表是資料的主要基礎，另外 3 個是對各自 ID 的查找參考，所以我將 ORDER 表放在首位。

SELECT STRAIGHT_JOIN
        bp.name,
        ba.address,
        s.name,
        Sum(o.total) AS total
    FROM
        orders o
            JOIN stores s
                ON o.store_id = s.id
            JOIN billing_profiles bp
                on o.billing_profile_id = bp.id
            LEFT JOIN billing_addresses ba
                ON o.billing_address_id = ba.id
    GROUP BY 
        bp.name,
        ba.address,
        s.name

現在，您的資料表看起來沒有那么大，但是如果您要按訂單表中的 3 列進行分組，我將在它們的基礎上建立一個索引，即鏈接到其他表。添加總數以幫助覆寫索引/聚合查詢，我將索引

( store_id, billing_profile_id, billing_address_id, total )

我敢肯定，實際上，您還有許多其他列與訂單相關聯，并且僅顯示此查詢的背景關系。然后，我將更改為預查詢，以便通過它們的 ID 鍵為訂單表完成一次聚合，然后將結果連接到查找表，您只需為最終輸出應用 ORDER BY 子句。就像是..

SELECT
        bp.name,
        ba.address,
        s.name,
        o.total
    FROM
        ( select
                store_id,
                billing_profile_id,
                billing_address_id,
                sum( total ) total 
            from
                orders 
            group by
                store_id,
                billing_profile_id,
                billing_address_id ) o
    JOIN stores s
        ON o.store_id = s.id
    JOIN billing_profiles bp
        on o.billing_profile_id = bp.id
    LEFT JOIN billing_addresses ba
        ON o.billing_address_id = ba.id
    ORDER BY 
        bp.name,
        ba.address,
        s.name

uj5u.com熱心網友回復：

將此索引添加到o，確保從 開始billing_profile_id：

INDEX(billing_profile_id, store_id, billing_address_id, total)

解釋的討論：

• 優化器發現它需要對某個表進行全面掃描。
• bp小于o，因此它被bp選為“第一”表。
• 然后它反復伸入下一張桌子。
• 它沒有看到合適的索引（以 開頭billing_profile_id）并決定執行“使用連接緩沖區（哈希連接）”，這涉及將整個表加載到 RAM 中的哈希中。
• “使用臨時”雖然在“第一”表中提到，但直到GROUP BY. （GROUP BY參考多個表，所以沒有辦法優化它。）

潛在錯誤 請檢查Sum(o.total) AS total. 它是在所有的之后JOINing和之前執行的GROUP BY，所以它可能會膨脹。注意 DRapp 的公式是如何在 JOINSUM 之前進行的。

標籤：

上一篇：如何僅在NaN值中將列的值替換為其他列？

下一篇：從[日期]定義上周