這樣delete居然不走索引

jtea發表於2023-12-26
delete索引

背景

由於業務變遷，合規要求，我們需要刪除大量非本公司的資料，涉及到上百張表，幾個T的資料清洗。我們的做法是先從基礎資料出發，將要刪除的資料id收集到一張表，然後再由上往下刪除子表，多執行緒併發處理。
我們使用的是阿里的polardb，完全相容mysql協議，5.7版本，RC隔離級別。刪除過程一直很順利，突然有一天報了大量：“Lock wait timeout exceeded; try restarting transaction”。從日誌上看是獲取鎖失敗了，馬上想到出現死鎖了，但我們使用RC，這個隔離級別下會出現不可重複讀和幻讀，但沒有間隙鎖等，併發效率比較高，在我們實際應用過程中，也很少遇到加鎖失敗的問題。

單從日誌看我們確實先入為主了，以為是死鎖問題，但sql比較簡單，表資料量在千萬級別，其中task_id和uid均有索引，如下：

delete from t_table_1 where task_id in (select id from t_table_2 where uid = #{uid})

拿到報錯的引數，查詢要刪除的資料也不多，聯絡dba同學確認沒有死鎖日誌，但出現大量慢sql，那為什麼這條sql會是慢sql呢？

問題復現

表結構簡化如下：

CREATE TABLE `t_table_1` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `task_id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_task_id` (`task_id`)
) ENGINE=InnoDB;

CREATE TABLE `t_table_2` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `uid` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_uid` (`uid`)
) ENGINE=InnoDB;

開始我們拿sql到資料庫查詢平臺查庫執行計劃，無奈這個平臺有bug，delete語句無法檢視，所以我們改成select，“應該”是一樣。這個“應該”加了雙引號，導致我們走了一點彎路。

EXPLAIN SELECT * from t_table_1 where task_id in (select id from t_table_2 where uid = 1)

explain後可以看到是走了索引的

到這裡可以總結：
1.沒有死鎖，這點比較肯定，因為沒有日誌，也符合我們的理解。
2.有慢sql，這點比較奇怪，透過explain select語句是走索引的，但資料庫慢日誌記錄到，全表掃描，不會錯。

那是select和delete的執行計劃不同嗎？正常來說應該是一樣的，delete無非就是先查，加鎖，再刪。
拿到本地環境執行再次檢視執行計劃，發現確實不同，select的是一樣的，但delete的變成全表掃描了。

首先這就符合問題現象了，雖然沒有死鎖，但每個delete語句都全表掃描，相當於全表加鎖，後面的請求就只能等待釋放鎖，等到超時就出現“Lock wait timeout exceeded”。
那為什麼delete會不走索引呢，接下來我們分析一下。

分析

select * from t_table_1 where task_id in (select id from t_table_2 where uid = #{uid})

回到這條簡單sql，包含子查詢，按照我們的理解，mysql應該是先執行子查詢：select id from t_table_2 where uid = #{uid}，然後再執行外部查詢：select * from t_table_1 where task_id in()，但這不一定，例如我關了這個引數：

set optimizer_switch='semijoin=off';

這裡我們先不用管這個引數的作用，下面會說到。
關閉後上面的sql就變成先掃描外部的t_table_1，然後再逐行去匹配子查詢了，假設t_table_1的資料量非常大，那全表掃描時間就會很長，我們可以透過optimizer_trace證明一下。
optimizer_trace是mysql一個跟蹤功能，可以跟蹤最佳化器做的各種決策，包括sql改寫，成本計算，索引選擇詳細過程，並將跟蹤結果記錄到INFORMATION_SCHEMA.OPTIMIZER_TRACE表中。

set session optimizer_trace="enabled=on";
set OPTIMIZER_TRACE_MAX_MEM_SIZE=10000000; -- 防止內容過多被截斷   
SELECT * from t_table_1 where task_id in (select id from t_table_2 where uid = 1)
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

輸出結果比較長，這裡我只挑選主要資訊

"steps": [
    {
        "expanded_query": "/* select#2 */ select `t_table_2`.`id` from `t_table_2` where (`t_table_2`.`uid` = 1)"
    },
    {
        "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": [
            ]
        }
    }
]

"expanded_query": "/* select#1 */ select `t_table_1`.`id` AS `id`,`t_table_1`.`task_id` AS `task_id` from `t_table_1` where <in_optimizer>(`t_table_1`.`task_id`,<exists>(/* select#2 */ select `t_table_2`.`id` from `t_table_2` where ((`t_table_2`.`uid` = 1) and (<cache>(`t_table_1`.`task_id`) = `t_table_2`.`id`)))) limit 0,1000"

sql簡寫一下就是

select * from t_table_1 t1 where exists (select t2.id from t_table_2 t2 where t2.uid = 1 and t1.task_id = t2.id)

可以看到in可以改成semijoin或exists，最終最佳化器選擇了exists，因為我們關閉了semijoin開關。
按照這條sql邏輯查詢，將會遍歷t_table_1表的每一行，然後代入子查詢看是否匹配，當t_table_1表的行數很多時，耗時將會很長。
透過explain觀察執行計劃可以看到t_table_1進行了全表掃描。
備註：想檢視最佳化器改下後生成的sql，也可以透過show extended + show warnings：

explain extended SELECT * from t_table_1 where task_id in (select id from t_table_2 where uid = 1);
show warnings;

接著我們開啟上面的引數開關，再次optimizer_trace跟蹤一下

set optimizer_switch='semijoin=on';

得到如下：

"steps": [
    {
        "expanded_query": "/* select#2 */ select `t_table_2`.`id` from `t_table_2` where (`t_table_2`.`uid` = 1)"
    },
    {
        "transformation": {
            "select#": 2,
            "from": "IN (SELECT)",
            "to": "semijoin",
            "chosen": true
        }
    }
]

"expanded_query": "/* select#1 */ select `t_table_1`.`id` AS `id`,`t_table_1`.`task_id` AS `task_id` from `t_table_1` semi join (`t_table_2`) where (1 and (`t_table_2`.`uid` = 1) and (`t_table_1`.`task_id` = `t_table_2`.`id`)) limit 0,1000"

sql簡寫一下就是

select * from t_table_1 semi join t_table_2 where (`t_table_2`.`uid` = 1 and `t_table_1`.`task_id` = `t_table_2`.`id`)"

可以看到最佳化器這次選擇將in轉換成semijoin了，觀察執行計劃可以看到走了索引。

那如果換成delete呢？同樣保持開關開啟，跟蹤如下：

"steps": [
    {
        "expanded_query": "/* select#2 */ select `t_table_2`.`id` from `t_table_2` where (`t_table_2`.`uid` = 1)"
    },
    {
        "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": [
            ]
        }
    }
]

可以看到和關閉semijoin一樣，對於delete最佳化器也是選擇了exists，我們表是千萬級別，全表掃描加鎖，其它操作語句自然都會超時獲取不到鎖而失敗。

semijoin

semijoin翻譯過來是半連線，是mysql針對in/exists子查詢進行最佳化的一種技術，參見文件。
可以使用SHOW VARIABLES LIKE 'optimizer_switch';檢視semijoin是否開啟。
上面使用IN-TO-EXISTS改寫後，外層表變成驅動表，效率很差，那如果使用inner join呢，使用條件過濾後，用小表驅動大表，但join查詢結果是會重複的，和子查詢語義不一定相同。如：

SELECT class.class_num, class.class_name
    FROM class
    INNER JOIN roster
    WHERE class.class_num = roster.class_num;

這樣會查詢出多條相同class_num的記錄，如果子查詢，那麼查詢出來的class_num是不一樣的，也就是去重。當然也可以加上distinct，但這樣效率比較低。

SELECT class_num, class_name
    FROM class
    WHERE class_num IN
        (SELECT class_num FROM roster);

semijoin有以下幾種策略，以下是官方的解釋：

Duplicate Weedout: Run the semijoin as if it was a join and remove duplicate records using a temporary table.

FirstMatch: When scanning the inner tables for row combinations and there are multiple instances of a given value group, choose one rather than returning them all. This "shortcuts" scanning and eliminates production of unnecessary rows.

LooseScan: Scan a subquery table using an index that enables a single value to be chosen from each subquery's value group.

Materialize the subquery into an indexed temporary table that is used to perform a join, where the index is used to remove duplicates. The index might also be used later for lookups when joining the temporary table with the outer tables; if not, the table is scanned. For more information about materialization, see Section 8.2.2.2, “Optimizing Subqueries with Materialization”.

以Duplicate Weedout為例，mysql會先將roster的記錄以class_num為主鍵新增到一張臨時表，達到去重的目的。接著掃描臨時表，每行去匹配外層表，滿足條件則放到結果集，最終返回。
具體使用哪種策略是最佳化器根據具體情況分析得出的，可以從explain的extra欄位看到。

那麼為什麼delete沒有使用semijoin最佳化呢？
這其實是mysql的一個bug，bug地址，描述場景和我們的一樣。
文中還提到這個問題在mysql 8.0.21被修復，地址

大致就是解釋了一下之前版本沒有支援的原因，提到主要是因為單表沒有可以JOIN的物件，沒法進行一系列的最佳化，所以單表的UPDATE/DELETE是無法用semijoin最佳化的。
這個最佳化還有一些限制，例如不能使用order by和limit，我們還是應該儘量避免使用子查詢。
在我們的場景透過將子查詢改寫為join即可走索引，現在也明白為什麼老司機們都說盡量用join代替了子查詢了吧。

更多分享，歡迎關注我的github：https://github.com/jmilktea/jtea

相關文章