由一次 UPDATE 過慢 SQL 優化而總結出的經驗

乾貨滿滿張雜湊發表於2021-12-20
SQL優化

image

最近，線上的 ETL 資料歸檔 SQL 發生了點問題，有一個 UPDATE SQL 跑了兩天還沒跑出來：

 update t_order_record set archive_id = '420a7fe7-4767-45e8-a5f5-72280c192faa', update_time = update_time  where order_id in (select order_id from t_retailer_order_record force index (idx_archive_id) where archive_id = '420a7fe7-4767-45e8-a5f5-72280c192faa')

這個 SQL 其實就是將 t_retailer_order_record 中 archive_id 為 420a7fe7-4767-45e8-a5f5-72280c192faa 的所有記錄的訂單 id order_id，對應的訂單表中的記錄的 archive_id 也更新為 420a7fe7-4767-45e8-a5f5-72280c192faa 並且更新時間保持不變（因為表上有 update_time 按當前時間更新的觸發器）。

對於 SQL 的優化，我們可以使用下面三個工具進行分析：

  1. EXPLAIN：這個是比較淺顯的分析，並不會真正執行 SQL，分析出來的可能不夠準確詳細。但是能發現一些關鍵問題。
  2. PROFILING： 通過 set profiling = 1 開啟的 SQL 執行取樣。可以分析 SQL 執行分為哪些階段，並且每階段的耗時如何。需要執行並且執行成功 SQL，並且分析出來的階段不夠詳細，一般只能通過某些階段是否存在如何避免這些階段的出現進行優化（例如避免記憶體排序的出現等等）。
  3. OPTIMIZER TRACE：詳細展示優化器的每一步，需要執行並且執行成功 SQL。MySQL 的優化器由於考慮的因素太多，迭代太多，配置相當複雜，預設的配置在大部分情況沒問題，但是在某些特殊情況會有問題，需要我們進行人為干預。

首先，我們針對這個 SQL 進行 EXPLAIN：

+----+--------------------+-------------------------+------------+-------+----------------+----------------+---------+-------+-----------+----------+-------------+
| id | select_type        | table                   | partitions | type  | possible_keys  | key            | key_len | ref   | rows      | filtered | Extra       |
+----+--------------------+-------------------------+------------+-------+----------------+----------------+---------+-------+-----------+----------+-------------+
|  1 | UPDATE             | t_order_record          | NULL       | index | NULL           | PRIMARY        | 8       | NULL  | 668618156 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | t_retailer_order_record | NULL       | ref   | idx_archive_id | idx_archive_id | 195     | const |         1 |    10.00 | Using where |
+----+--------------------+-------------------------+------------+-------+----------------+----------------+---------+-------+-----------+----------+-------------+

發現 t_order_record 的索引使用有問題，這很奇怪：

  1. t_order_record 在 order_id 上面是有索引的，但是這裡走的是主鍵全掃描（主鍵不是 order_id 而是 id）
  2. 子查詢中其實只命中了 3 萬多條資料。

一般出現這種情況，肯定又是 SQL 優化器作妖了。

這也不能完全怪 SQL 優化器

我們在日常開發與設計表的時候，很難避免會有一些不合理的使用情況，會有很多索引，可能還會出現 large row。這種千奇百怪的情況中，SQL 優化器需要找到最優的方案確實很難。舉一個簡單的例子：假設我們有一張表，包含主鍵 id，有 id = 1 的一條記錄，一年後，有了 id = 1000000 的一條記錄。然後這時我們同時更新了 id = 1 和 id = 1000000 的記錄，那麼某個通過其他索引但是命中只有 id = 1 和 id = 1000000 的資料很可能不走索引而是主鍵搜尋。因為最近的更新導致這兩條資料跑到了同一頁上並且在記憶體中。

SQL 優化器考慮了很多這種複雜的情況，能在大部分情況下優化 SQL 為更適應當前情況的，但是由於邏輯過於複雜導致某些簡單情況下優化的反而很差，這就需要我們根據 OPTIMIZER TRACE 的結果進行手動優化。

使用測試資料庫進行 OPTIMIZER TRACE，先分析索引分析前的步驟是否有問題

由於 Optimizer_trace 需要 SQL 真正執行，但是這個 SQL 執行不出來了。Optimizer_trace 可以分析優化器的全步驟，我們可以先在一個資料量很少的測試環境，看看在進入統計資料分析前（例如分析索引的離散型資料來決定走哪個索引，這個用測試環境模擬不出來，因為資料和線上肯定有差異，即使複製線上的資料也不行，因為資料在哪些頁，索引經過怎樣的更新，檔案結構和線上不同，統計器的資訊肯定不會完全一樣），SQL 改寫轉換是否有問題。

執行：

mysql> set session optimizer_trace="enabled=on";
Query OK, 0 rows affected (0.20 sec)

mysql>  update t_order_record set archive_id = '420a7fe7-4767-45e8-a5f5-72280c192faa', update_time = update_time  where order_id in (select order_id from t_retailer_order_record force index (idx_archive_id) where archive_id = '420a7fe7-4767-45e8-a5f5-72280c192faa');
Query OK, 0 rows affected (2.95 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> SELECT trace FROM information_schema.OPTIMIZER_TRACE;

steps": [
    {
      "join_preparation": {
        "select#": 2,
        "steps": [
          {
            "expanded_query": "/* select#2 */ select `main`.`t_retailer_order_record`.`order_id` from `main`.`t_retailer_order_record` FORCE INDEX (`idx_archive_id`) where (`main`.`t_retailer_order_record`.`archive_id` = '420a7fe7-4767-45e8-a5f5-72280c192faa')"
          },
          {
            "transformation": {
              "select#": 2,
              "from": "IN (SELECT)",
              "to": "semijoin",
              "chosen": false
            }
          },
          {
            "transformation": {
              "select#": 2,
              "from": "IN (SELECT)",
              "to": "EXISTS (CORRELATED SELECT)",
              "chosen": true,
              "evaluating_constant_where_conditions": [
              ]
            }
          }
        ]
      }
    },
    {
      "substitute_generated_columns": {
      }
    },
    {
      "condition_processing": {
        "condition": "WHERE", 
        ## 以下省略

通過 Optimizer_trace 我們發現，優化有問題！將 IN 優化成了 EXISTS。這樣導致本來我們想的是使用子查詢的每一條記錄，去匹配外層訂單表的記錄，變成了遍歷外層訂單表的每一條記錄，去看是否存在於子查詢中，這也解釋了為啥 explain 的結果是通過主鍵遍歷訂單表的每一條記錄進行查詢。

這個要改的話，只能改變寫法來適應，沒法通過關閉優化器選項來實現

於是，我們改寫並優化 SQL (使用 JOIN，JOIN 是最接近最容易被優化器理解的編寫 SQL 的方式)，並且加上了時間條件（我們本身就想只操作 179 天前的資料，這個 archive_id 對應的資料都是 179 天前的），由於訂單 id 中本身就帶時間（以時間開頭，例如 211211094621ord123421 代表 2021 年 12 月 11 日 9 點 46 分 21 秒的一個訂單），所以用訂單 id 限制時間：

UPDATE t_order_record
JOIN t_retailer_order_record ON t_order_record.order_id = t_retailer_order_record.order_id 
SET t_order_record.archive_id = '420a7fe7-4767-45e8-a5f5-72280c192faa',
t_order_record.update_time = t_order_record.update_time 
WHERE
	t_order_record.order_id < DATE_FORMAT( now() - INTERVAL 179 DAY, '%y%m%d' ) 
	AND t_retailer_order_record.order_id < DATE_FORMAT( now() - INTERVAL 179 DAY, '%y%m%d' ) 
	AND t_retailer_order_record.archive_id = '420a7fe7-4767-45e8-a5f5-72280c192faa'

後續優化經驗

如果再遇到這種執行很慢但是實際上更新命中很少資料並且該有的索引都有的情況，可以先在一個資料量很少的測試環境，看看在進入統計資料分析前（例如分析索引的離散型資料來決定走哪個索引，這個用測試環境模擬不出來，因為資料和線上肯定有差異，即使複製線上的資料也不行，因為資料在哪些頁，索引經過怎樣的更新，檔案結構和線上不同，統計器的資訊肯定不會完全一樣），SQL 改寫轉換是否有問題。

如果有問題，考慮人為干預手動優化。手動優化的方式包括：

  1. force index 強制用某個索引
  2. 關閉當前會話的 MySQL 優化器的某些選項
  3. 改寫 SQL 讓優化器更易懂（JOIN 是最容易被 SQL 優化器理解的）

微信搜尋“我的程式設計喵”關注公眾號，每日一刷，輕鬆提升技術，斬獲各種offer：

相關文章