背景

由于業務變遷，合規要求，我們需要刪除大量非本公司的數據，涉及到上百張表，幾個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