Stories

Detail Return Return

分佈式任務調度內的 MySQL 分頁查詢優化 - Stories Detail

作者：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
mysql優化 , 分佈式任務 , 主鍵
user avatar journey_64224c9377fd5 Avatar edonsoft Avatar zyx178 Avatar zzd41 Avatar candy_68fb0dfb0afd0 Avatar ccVue Avatar nqbefgvs Avatar DingyLand Avatar q_bit Avatar writers Avatar kubesphere Avatar matrixorigin Avatar
Favorites 37 users favorite the story!
Favorites

Add a new Comments

Some HTML is okay.