分散式任務排程內的 MySQL 分頁查詢最佳化

vivo互联网技术發表於2024-05-24
分散式MySql

作者：vivo 網際網路資料庫團隊- Qiu Xinbo

本文主要透過圖示介紹了用主鍵進行分片查詢的過程，介紹了主鍵分頁查詢存在SQL效能問題，如何去建立高效的索引去最佳化主鍵分頁查詢的SQL效能問題。對於資料分佈不均如何發現，提供了一些SQL查詢案例來進行參考，對MySQL Index Condition Pushdown最佳化演算法做了一些簡單介紹。

一、背景介紹

最近線上上環境發現了一條執行較慢的分頁查詢，高併發執行，產生了大量的慢查詢日誌，CPU使用率逐步升高。

透過觀察它的執行時間，發現該SQL查詢時快時慢，執行時間並不穩定，以至於在高併發執行場景時，資料庫來不及響應，資料庫服務變慢。

圖片

圖片

二、分析定位

2.1 定位 SQL 執行變慢的原因

透過資料庫管理平臺檢視SQL執行資訊發現，SQL解析行數（掃描行數）和SQL執行時間都很不穩定，執行時長和解析行數（掃描行數）是成正比的。

這個也能解釋的通為什麼SQL執行時長變了，因為掃描行數變多了，SQL執行時間成比例增長。

-- SQL全文
select
    id,
    uuid,
    name,
    user_type,
    is_deleted,
    modify_date
from
    test_user
where
    is_deleted=0    
    and user_type=0    
    and id > 10000    
    and id % 10 = 9
order by
    id  limit 500;

圖片

2.2 瞭解 SQL 的業務背景

透過與研發溝通發現，該SQL原來是序列執行，單個執行緒在跑，後來覺得比較慢，改為分散式任務並行執行，透過id取模0-9，排程10個執行緒，每個執行緒處理1個分割槽，這樣就有10個併發相當於把資料做了切片，併發查詢併發處理，由此帶來資料庫端的併發升高。從技術角度上看，提高資料處理速度，給資料做切片，改單執行緒為併發處理，並沒有任何問題，反而是一種比較好的最佳化方案，但是高併發執行的SQL都是要有一個前提，SQL執行效率要特別高，否則會導致資料庫端物理機資源耗盡，資料庫服務來不及響應。

圖片

2.3 定位 SQL 掃描行數變化的原因

2.3.1 慢 SQL 及表結構資訊

-- 為了方便理解和說明，新建一個test_user表，造了一些模擬資料，將SQL做了一些簡化，不影響整體的分析效果
 
-- SQL全文
select
    id,
    uuid,
    name,
    user_type,
    is_deleted,
    modify_date
from
    test_user 
where
    is_deleted=0     
    and user_type=0     
    and id > 10000     
    and id % 10 = 9 
order by
    id  limit 500;
 
 
-- 表資訊
 CREATE TABLE `test_user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `uuid` varchar(64) NOT NULL COMMENT '使用者ID',
  `name` varchar(20) DEFAULT '' COMMENT '使用者名稱',
  `user_type` tinyint(4) NOT NULL DEFAULT '0',
  `is_deleted` tinyint(4) NOT NULL DEFAULT '0',
  `modify_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改時間',
  `create_date` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_uuid` (`uuid`),
  KEY `idx_modifydate` (`modify_date`)
) ENGINE=InnoDB AUTO_INCREMENT=7986024 DEFAULT CHARSET=utf8mb4

2.3.2 檢視 SQL 執行計劃

透過檢視SQL執行計劃，發現執行計劃走主鍵索引掃描，以下是SQL執行計劃的關鍵資訊解讀：

  • type=range 範圍掃描

  • key = primary 使用主鍵索引

  • rows = 877w 預估的掃描行數

  • filter = 1.00 百分比，滿足過濾條件返回的行數 = rows * filter 

mysql> explain  select
    ->     id,
    ->     uuid,
    ->     name,
    ->     user_type,
    ->     is_deleted,
    ->     modify_date
    -> from
    ->     test_user 
    -> where
    ->     is_deleted=0     
    ->     and user_type=9     
    ->     and id > 10000     
    ->     and id % 10 = 9 
    -> order by
    ->     id  limit 500; 
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | test_user | NULL       | range | PRIMARY       | PRIMARY | 8       | NULL | 8775507 |     1.00 | Using where |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

2.3.3 圖示 SQL 執行過程

透過簡單的圖示，描述下SQL掃描過程，由於是透過主鍵索引遍歷，避免了額外的排序行為，從最小id開始取到最大id。

mysql> select min(id),max(id) from test_user;
+---------+----------+
| min(id) | max(id)  |
+---------+----------+
|       3 | 17889149 |
+---------+----------+
1 row in set (0.00 sec)

圖片

2.3.4 計算資料分佈

從SQL過濾條件看只有is_deleted、user_type、id這三個，能預估到is_deleted和user_type區分度不高，透過SQL檢視下資料的分佈。

mysql> select is_deleted,user_type,count(*) from test_user group by is_deleted,user_type order by count(*) desc limit 1,10;
+------------+-----------+----------+
| is_deleted | user_type | count(*) |
+------------+-----------+----------+
|          1 |         1 |  4473019 |
|          1 |         0 |  4471648 |
|          0 |         0 |  4470140 |
|          0 |         2 |      999 |
+------------+-----------+----------+
4 rows in set (4.81 sec)
-- 從資料分佈來看user_type等於2的資料較少，只有999條，其他相對比較均勻

資料分佈驗證測試

將上述4種結果（is_deleted和user_type）分別透過SQL檢視最近1000條滿足條件的資料的id區間，驗證資料的分佈。

  • is_deleted=1、user_type=1

  • is_deleted=1、user_type=0

  • is_deleted=0、user_type=0

-- 最近1000條is_deleted=1、user_type=1的資料記錄分佈在id 6-3876,大約掃描3871條資料,能返回500條滿足條件的值,資料分佈均勻.
mysql> select max(id),min(id) from( select id from test_user where  is_deleted=1 and user_type=1 order by id  limit 1000) a;
+---------+---------+
| max(id) | min(id) |
+---------+---------+
|    3876 |       6 |
+---------+---------+
1 row in set (0.00 sec)
 
-- 最近1000條is_deleted=1、user_type=0的資料記錄分佈在id 3-4019,大約掃描4016條資料,能返回500條滿足條件的值,資料分佈均勻.
mysql> select max(id),min(id) from( select id from test_user where  is_deleted=1 and user_type=0 order by id  limit 1000) a;
+---------+---------+
| max(id) | min(id) |
+---------+---------+
|    4019 |       3 |
+---------+---------+
1 row in set (0.00 sec)
 
-- 最近1000條is_deleted=0、user_type=0的資料記錄分佈在id 5-4020,大約掃描4015條資料,能返回500條滿足條件的值,資料分佈均勻.
mysql> select max(id),min(id) from( select id from test_user where  is_deleted=0 and user_type=0 order by id  limit 1000) a;
+---------+---------+
| max(id) | min(id) |
+---------+---------+
|    4025 |       5 |
+---------+---------+
1 row in set (0.00 sec)

圖片

is_deleted=0、user_type=2

-- 最近1000條is_deleted=0、user_type=2的資料記錄分佈在id 17890648-17891147，是比較緊湊的，但是由於id比較大，整體排在較後的位置。
-- 如果按照主鍵遍歷，需要遍歷完前面的1700w條不符合條件資料，才能遍歷到滿足條件的資料。
mysql> select max(id),min(id) from( select id from test_user where  is_deleted=0 and user_type=2 order by id  limit 1000) a;
+----------+----------+
| max(id)  | min(id)  |
+----------+----------+
| 17891147 | 17890149 |
+----------+----------+
1 row in set (0.00 sec)

圖片

2.3.5 實際執行測試

重要欄位資訊說明：

  • Query_time：SQL執行時間

  • Rows_examined：SQL掃描行數

  • Rows_sent：SQL返回行數

# Query_time: 0.012232  Lock_time: 0.000076 Rows_sent: 500  Rows_examined: 19507

SET timestamp=1695711685;

select id,uuid,name,user_type,is_deleted,modify_date from test_user where is_deleted=1 and user_type=1 and id > 0 and id % 10 = 9 order by id  limit 500;
# Query_time: 0.009549  Lock_time: 0.000074 Rows_sent: 500  Rows_examined: 20537

SET timestamp=1695711745;

select id,uuid,name,user_type,is_deleted,modify_date from test_user where is_deleted=1 and user_type=0 and id > 0 and id % 10 = 9 order by id limit 500;
# Query_time: 0.009835  Lock_time: 0.000081 Rows_sent: 500  Rows_examined: 21037

SET timestamp=1695711779;

select id,uuid,name,user_type,is_deleted,modify_date from test_user where is_deleted=0 and user_type=0 and id > 0 and id % 10 = 9 order by id limit 500;

（這邊大家可能會有疑惑，為什麼掃描行數要比預估的多一些，其實也正常，我們在做預估時並沒有把取模的過濾條件加上，所以必然會多掃描）

# Query_time: 6.981938  Lock_time: 0.000076 Rows_sent: 100  Rows_examined: 17890145

SET timestamp=1695711818;

select id,uuid,name,user_type,is_deleted,modify_date from test_user where is_deleted=0 and user_type=2 and id > 0 and id % 10 = 9 order by id limit 500;

2.3.6 自此能得到結論

因為is_deleted和user_type資料分佈不均勻並且資料區分度不高，執行計劃走主鍵順序掃描， 在查詢is_deleted=0 and user_type=2 特定場景的時，因為走主鍵索引順序遍歷，滿足user_type=2 的id比較靠後，需要先掃描完成前面1700w條資料後，才能找到滿足user_type=2的資料，SQL掃描行數變多， SQL執行時間變長。

三、最佳化方案

3.1 最佳化方案確定

當前SQL執行計劃以主鍵進行順序遍歷，是一個範圍掃描，有點像在一片很大的居民區按照序號挨家挨戶尋找一些特定的人一樣，比較簡單也比較低效。

既然查詢是以is_deleted和user_type為主要的過濾條件，查詢特定的人群資訊，可以考慮直接在這兩列上新增索引，記錄特定人群資訊的位置，根據位置直接去定向尋找。

雖然is_deleted和user_type欄位區分度很低，但是成為有序結構，能避免這條SQL大量的讀取不符合條件的資料的行為，新增索引的收益遠大於索引帶來負面影響。

最終的新增的索引：

alter table test_user add index idx_isdeleted_usertype_id(is_deleted,user_type,id)；

新增該索引的考慮：遵循ESR原則（等值在前，排序在中間，範圍在最後），既能高效掃描到對應的資料,還能避免id的排序，extra內顯示使用了Using index condition。

mysql>  explain select id,uuid,name,user_type,is_deleted,modify_date from test_user where is_deleted=0 and user_type=2 and id > 0 and id % 10 = 9 order by id limit 500;
+----+-------------+-----------+------------+-------+-----------------------------------+---------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys                     | key                       | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+-----------------------------------+---------------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | test_user | NULL       | range | PRIMARY,idx_isdeleted_usertype_id | idx_isdeleted_usertype_id | 10      | NULL |  999 |   100.00 | Using index condition |
+----+-------------+-----------+------------+-------+-----------------------------------+---------------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

3.2 最佳化效果對比

最佳化前：

# Query_time: 6.981938  Lock_time: 0.000076 Rows_sent: 100  Rows_examined: 17890145
SET timestamp=1695711818;
select id,uuid,name,user_type,is_deleted,modify_date from test_user where is_deleted=0 and user_type=2 and id > 0 and id % 10 = 9 order by id limit 500;

最佳化後：

# Query_time: 0.000884  Lock_time: 0.000091 Rows_sent: 100  Rows_examined: 100
SET timestamp=1695714485;
select id,uuid,name,user_type,is_deleted,modify_date from test_user where is_deleted=0 and user_type=2 and id > 0 and id % 10 = 9 order by id limit 500;

最佳化提升：

掃描行數從1700w條降低為100條，查詢時間從6.98s 降低為 0.8ms。

3.3 圖示的最佳化後的SQL執行過程

  1. 透過idx_isdeleted_usertype_id索引的有序性，進行二分查詢，快速定位到滿足is_deleted和user_type、id條件主鍵資訊。

  2. 透過主鍵資訊回表讀取完整的資料。

  3. 返回資料給客戶端服務。

圖片

3.4 ICP特性(Index Condition Pushdown)

補充下執行計劃內extra列體現Using index condition最佳化。

  • 索引條件下推 (ICP) 是針對 MySQL 使用索引從表中檢索行的情況的最佳化。

  • 如果沒有 ICP，儲存引擎會遍歷索引以定位基表中的行，並將它們返回給 MySQL server，由 MySQL server評估行的 WHERE 條件。

  • 在啟用 ICP 的情況下，如果 WHERE 條件的一部分可以透過僅使用索引中的列來評估，MySQL server會將這部分 WHERE 條件下推到儲存引擎。

  • 然後儲存引擎透過使用索引條目來評估推送的索引條件，並且只有在滿足這一條件時才從表中讀取行。

  • ICP可以減少儲存引擎必須訪問基表的次數和MySQL server必須訪問儲存引擎的次數。

圖片

ICP最佳化的使用和侷限性路：

ICP最佳化在資料庫最佳化器內預設是開啟的，ICP最佳化適用性取決於以下條件：

  • icp 對於使用rang、ref、eq_ref 和ref_or_null訪問模式去檢索全表資料行時候。

  • icp 只適用於innodb、myisam引擎的表，包括分割槽的InnoDB和MyISAM表。

  • icp只會使用二級索引，減少完整行記錄的讀取和減少I/O操作 對於聚集索引，完整行記錄已經被讀入innodb buffer中，using icp不能減少I/O操作。

  • icp不支援使用建立在虛擬列上的二級索引，innodb引擎支援在虛擬列上建立二級索引。

  • 引用子查詢的條件無法下推。

  • 引用儲存函式的條件無法下推。儲存引擎無法呼叫儲存的函式。

  • Triggered conditions cannot be pushed down。

-- 測試下相同的SQL執行在開啟ICP最佳化和關閉ICP最佳化,執行時間和掃描行數的對比.
 
-- 關閉ICP,SQL執行掃描行數是5043行,執行時間為8.03ms.
SET optimizer_switch='index_condition_pushdown=off';
# Query_time: 0.008031  Lock_time: 0.000085 Rows_sent: 500  Rows_examined: 5043
select id,uuid,name,user_type,is_deleted,modify_date from test_user where is_deleted=0 and user_type=0  and id > 10000 and id % 10 = 9  order by id limit 500;
 
-- 開啟ICP,SQL執行掃描行數僅為500行,執行時間為2.72ms.
SET optimizer_switch='index_condition_pushdown=on';
# Query_time: 0.002724  Lock_time: 0.000082 Rows_sent: 500  Rows_examined: 500
select id,uuid, name, user_type,is_deleted,modify_date from test_user where is_deleted=0 and user_type=0 and id > 10000 and id % 10 = 9 order by id limit 500;

結論：本次測試，開啟ICP最佳化，SQL執行時掃描的行數僅為未開啟時的1/10，執行時間提升約2-3倍。

四、總結

  1. 將SQL查詢從序列改為高併發執行，需要評估下SQL查詢效率是否足夠高，評估的標準：SQL掃描行數/SQL返回行數 結果越大說明存在很多低效的資料掃描，執行效率不高。

  2. 分頁查詢透過主鍵遍歷是順序遍歷，從最小id到最大id，當存在其它過濾條件時，需要再次判斷資料是否滿足這些過濾條件，掃描的行數會隨著增長。

  3. 區分度較低的欄位並非不適合建立索引，仔細評估查詢的場景，建立特定的組合索引，觸發MySQL icp最佳化，對查詢效能會有很大提升。

參考文章

Index Condition Pushdown介紹：

  • Index Condition Pushdown

  • Index Condition Pushdown Optimization

相關文章