這個大表走索引欄位查詢的 SQL 怎麼就成全掃描了,我TM人傻了

乾貨滿滿張雜湊 發表於 2021-08-07
SQL

image

今天收到運營同學的一個 SQL,有點複雜,尤其是這個 SQL explain 都很長時間執行不出來,於是我們後臺團隊幫忙解決這個 SQL 問題,卻正好發現了一個隱藏很深的線上問題。

image

select 
a.share_code,
a.generated_time,
a.share_user_id,
b.user_count,
b.order_count,
a.share_order_id,
b.rewarded_amount
from t_risk_share_code a,
(select count(distinct r.user_id) user_count,
count(distinct r.order_id) order_count,
s.rewarded_amount,
r.share_code
from t_order s,t_order_rel r
where r.order_id = s.id and r.type = 1 and r.share_code = '我剛剛分享的訂單編碼'
group by r.share_code) b
where a.share_code = b.share_code and a.type = 1

首先,我們發現,直接 EXPLAIN 這個 SQL 也很慢,也就是可能某些子查詢被實際執行了導致。所以,第一步我們先將其中的子查詢拆解出來,逐步分析,即:

select count(distinct r.user_id) user_count,
count(distinct r.order_id) order_count,
max(s.rewarded_amount),
r.share_code
from t_order s,t_order_rel r
where r.order_id = s.id and r.type = 1 and r.share_code = '我剛剛分享的訂單編碼'
group by r.share_code

EXPLAIN 這個 SQL,執行很快,我們發現結果是:
image

奇了怪了,怎麼 t_order 這張表的掃描就成為全掃描了?這張表的索引是正常的呀,主鍵就是 id。

image

根據官方文件,可以知道有如下幾個原因

  1. 表太小了,走索引不值當的。但我們這裡這兩張表都非常大,都是千萬級別的資料。
  2. 對於 WHERE 或者 ON 的條件,沒有合適的索引,這也不是我們這裡的情況,兩張表都針對 WHERE 和 ON 條件有合適的索引(這裡查詢條件雖然都放到了 WHERE 裡面,但是後面的分析我們會知道這個 SQL 會被改成 JOIN ON + WHERE 去執行)。
  3. 使用索引列與常數值作比較, MYSQL 通過索引分析出這個覆蓋了表中大部分的值,其實就是分析出命中的行最後回表拉取資料的時候,表的檔案中大部分頁都要被載入到記憶體中進行讀取,這樣的話與其說先將索引載入到記憶體中獲取命中列,不如直接掃描整個表,反正最後也是差不多將表的檔案中大部分頁都載入到記憶體中。這種情況很顯然,不走索引反而會更快。我們這個 SQL 中,t_order_rel 表實際上根據 where 條件只會返回幾十條資料,t_order 與 t_order_rel 是 1 對多的關係,這裡不會命中太多資料的。
  4. 這一列值的離散度(Cardinality)太低,離散度就是是不同值的個數除以行數,最大為 1。但是這個值對於 innoDB 引擎來說,並不是實時計算的,可能不準確(尤其是在這一列的值發生更新導致行在頁中的位置發生變化的時候).但是對於 distinct 或者主鍵列是不用計算的,就是 1。如果離散度太低,那麼其實和第三種情況差不多,會命中過多的行數。這裡我們要優化的 SQL 使用的是主鍵,所以不屬於這種情況。

雖然以上都不是我們這裡要討論的情況,但是這裡還是提一些我們為了避免出現全掃描的優化:

  1. 為了讓 SQL 執行計劃分析器更準確,針對第四種情況,我們對於某些表可能需要在業務閒時定期執行 ANALYZE TABLE,來確保分析器的統計資料的準確性。
  2. 由於考慮分庫分表,以及有時候資料庫 SQL 執行計劃總是不完美還是會出現索引走錯的情況,我們一般儘量在 OLTP 查詢業務上加 force index 強制走一些索引。這在使用基於中介軟體的分庫分表(例如 sharding-jdbc)或者原生分散式資料庫(例如 TiDB)過程中,我們經常遇到的坑。
  3. 對於 MySQL,我們設定 --max-seeks-for-key = 10000(預設這個值非常大),這樣其實就是限制了每次 SQL 執行計劃分析器分析出來的走索引可能掃描的行數。其原理非常簡單,參考原始碼:

sql_planner.cc

double find_cost_for_ref(const THD *thd, TABLE *table, unsigned keyno,
                         double num_rows, double worst_seeks) {
  //將分析出會掃描的行數與 max_seeks_for_key 作對比,取其中小的那個
  //也就是 SQL 分析器得出的結論中,走索引掃描的行數不會超過 max_seeks_for_key
  num_rows = std::min(num_rows, double(thd->variables.max_seeks_for_key));
  if (table->covering_keys.is_set(keyno)) {
    // We can use only index tree
    const Cost_estimate index_read_cost =
        table->file->index_scan_cost(keyno, 1, num_rows);
    return index_read_cost.total_cost();
  } else if (keyno == table->s->primary_key &&
             table->file->primary_key_is_clustered()) {
    const Cost_estimate table_read_cost =
        table->file->read_cost(keyno, 1, num_rows);
    return table_read_cost.total_cost();
  } else
    return min(table->cost_model()->page_read_cost(num_rows), worst_seeks);
}

這個不能設定太小,否則會出現可以走多個索引但是走到實際掃描行數最多的索引

image

現在沒辦法了,EXPLAIN 已經不夠我們分析出問題了,只能進一步求助 optimizer_trace 了。不直接用 optimizer_trace 的原因是,optimizer_trace 必須完整的執行 SQL 之後,才能獲取到所有有用的資訊。

## 開啟 optimizer_trace
set session optimizer_trace="enabled=on";
## 執行 SQL
select .....
## 查詢 trace 結果
SELECT trace FROM information_schema.OPTIMIZER_TRACE;

通過 trace 結果我們發現,實際執行的 SQL 是:

SELECT
	各種欄位
FROM
	`t_order_rel` `r`
	JOIN `t_order` `s` 
WHERE
	(
	( `r`.`order_id` = CONVERT ( `s`.`id` USING utf8mb4 ) ) 
	AND ( `r`.`type` = 1 ) 
	AND ( `r`.`share_code` = 'B2MTB6C' ) 
	)

我去,原來兩個表的欄位的編碼是不一樣的!導致 JOIN ON 的時候,套了一層編碼轉換 CONVERT ( s.idUSING utf8mb4 ) ).我們知道,欄位外套一層函式這種條件匹配,是走不到索引的,例如:date(create_time) < "2021-8-1" 是不能走索引的,但是 create_time < "2021-8-1" 是可以的。不同型別之間列的比較,也走不到索引,因為 MySQL 會自動套上型別轉換函式。這也是 MySQL 的語法糖經常帶來的誤用

這個 t_order_rel 的預設編碼和其他表不一樣,由於某些欄位使用了 emoji 表情,所以建表的時候整個表預設編碼使用了 utf8mb4。而且這個表僅僅是記錄使用,沒有 OLTP 的業務,只有一些運營同學使用的 OLAP 場景。所以一直沒有發現這個問題。

修改欄位編碼後,SQL 終於不是全掃描了。同時以後要注意:

  1. 資料庫指定預設的編碼,表不再指定預設編碼,同時對於需要使用特殊編碼的欄位,針對欄位指定編碼
  2. join,where 的時候,注意 compare 兩邊的型別是否一致,是否會導致不走索引

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

image