這個大表走索引欄位查詢的 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

相關文章