建站
咨詢(xún)
售后
建站咨詢(xún)
新聞中心
本文作者 ｜ 粟含，美團(tuán)基礎(chǔ)研發(fā)平臺(tái)/基礎(chǔ)技術(shù)部/數(shù)據(jù)庫(kù)平臺(tái)研發(fā)組工程師。
對(duì)于數(shù)據(jù)庫(kù)來(lái)說(shuō)，慢查詢(xún)往往意味著風(fēng)險(xiǎn)。SQL執(zhí)行得越慢，消耗的CPU資源或IO資源也會(huì)越大。大量的慢查詢(xún)可直接引發(fā)業(yè)務(wù)故障，關(guān)注慢查詢(xún)即是關(guān)注故障本身。本文主要介紹了美團(tuán)如何利用數(shù)據(jù)庫(kù)的代價(jià)優(yōu)化器來(lái)優(yōu)化慢查詢(xún)，并給出索引建議，評(píng)估跟蹤建議質(zhì)量，運(yùn)營(yíng)治理慢查詢(xún)。
1 背景
2 基于代價(jià)的優(yōu)化器介紹
- 2.1 SQL執(zhí)行與優(yōu)化器
- 2.2 代價(jià)模型介紹
- 2.3 基于代價(jià)的索引選擇
- 2.4 基于代價(jià)的索引推薦思路
3 索引推薦實(shí)現(xiàn)
- 3.1 前置校驗(yàn)
- 3.2 提取關(guān)鍵列名
- 3.3 生成候選索引
- 3.4 數(shù)據(jù)采集
- 3.5 統(tǒng)計(jì)數(shù)據(jù)計(jì)算
- 3.6 候選索引代價(jià)評(píng)估
4 推薦質(zhì)量保證
- 4.1 有效性驗(yàn)證
- 4.2 效果追蹤
- 4.3 仿真環(huán)境
- 4.4 測(cè)試案例庫(kù)
5 慢查詢(xún)治理運(yùn)營(yíng)
- 5.1 過(guò)去-歷史慢查詢(xún)
- 5.2 現(xiàn)在-新增慢查詢(xún)
- 5.3 未來(lái)-潛在慢查詢(xún)
6 項(xiàng)目運(yùn)行情況
7 未來(lái)規(guī)劃
1 背景
慢查詢(xún)是指數(shù)據(jù)庫(kù)中查詢(xún)時(shí)間超過(guò)指定閾值(美團(tuán)設(shè)置為100ms)的SQL，它是數(shù)據(jù)庫(kù)的性能殺手，也是業(yè)務(wù)優(yōu)化數(shù)據(jù)庫(kù)訪問(wèn)的重要抓手。隨著美團(tuán)業(yè)務(wù)的高速增長(zhǎng)，日均慢查詢(xún)量已經(jīng)過(guò)億條，此前因慢查詢(xún)導(dǎo)致的故障約占數(shù)據(jù)庫(kù)故障總數(shù)的10%以上，而且高級(jí)別的故障呈日益增長(zhǎng)趨勢(shì)。因此，對(duì)慢查詢(xún)的優(yōu)化已經(jīng)變得刻不容緩。
那么如何優(yōu)化慢查詢(xún)呢?最直接有效的方法就是選用一個(gè)查詢(xún)效率高的索引。關(guān)于高效率的索引推薦，主要有基于經(jīng)驗(yàn)規(guī)則和代價(jià)的兩種算法。在日常工作中，基于經(jīng)驗(yàn)規(guī)則的推薦隨處可見(jiàn)，對(duì)于簡(jiǎn)單的SQL，如select * from sync_test1 where name like 'Bobby%'，直接添加索引IX(name) 就可以取得不錯(cuò)的效果;但對(duì)于稍微復(fù)雜點(diǎn)的SQL，如select * from sync_test1 where name like 'Bobby%' and dt > '2021-07-06'，到底選擇IX(name)、IX(dt)、IX(dt,name) 還是IX(name,dt)，該方法也無(wú)法給出準(zhǔn)確的回答。更別說(shuō)像多表Join、子查詢(xún)這樣復(fù)雜的場(chǎng)景了。所以采用基于代價(jià)的推薦來(lái)解決該問(wèn)題會(huì)更加普適，因?yàn)榛诖鷥r(jià)的方法使用了和數(shù)據(jù)庫(kù)優(yōu)化器相同的方式，去量化評(píng)估所有的可能性，選出的是執(zhí)行SQL耗費(fèi)代價(jià)最小的索引。
2 基于代價(jià)的優(yōu)化器介紹
2.1 SQL執(zhí)行與優(yōu)化器
一條SQL在MySQL服務(wù)器中執(zhí)行流程主要包含：SQL解析、基于語(yǔ)法樹(shù)的準(zhǔn)備工作、優(yōu)化器的邏輯變化、優(yōu)化器的代價(jià)準(zhǔn)備工作、基于代價(jià)模型的優(yōu)化、進(jìn)行額外的優(yōu)化和運(yùn)行執(zhí)行計(jì)劃等部分。具體如下圖所示：
SQL執(zhí)行與優(yōu)化器
2.2 代價(jià)模型介紹
而對(duì)于優(yōu)化器來(lái)說(shuō)，執(zhí)行一條SQL有各種各樣的方案可供選擇，如表是否用索引、選擇哪個(gè)索引、是否使用范圍掃描、多表Join的連接順序和子查詢(xún)的執(zhí)行方式等。如何從這些可選方案中選出耗時(shí)最短的方案呢?這就需要定義一個(gè)量化數(shù)值指標(biāo)，這個(gè)指標(biāo)就是代價(jià)(Cost)，我們分別計(jì)算出可選方案的操作耗時(shí)，從中選出最小值。
代價(jià)模型將操作分為Server層和Engine(存儲(chǔ)引擎)層兩類(lèi)，Server層主要是CPU代價(jià)，Engine層主要是IO代價(jià)，比如MySQL從磁盤(pán)讀取一個(gè)數(shù)據(jù)頁(yè)的代價(jià)io_block_read_cost為1，計(jì)算符合條件的行代價(jià)為row_evaluate_cost為0.2。除此之外還有：
- memory_temptable_create_cost (default 2.0) 內(nèi)存臨時(shí)表的創(chuàng)建代價(jià)。
- memory_temptable_row_cost (default 0.2) 內(nèi)存臨時(shí)表的行代價(jià)。
- key_compare_cost (default 0.1) 鍵比較的代價(jià)，例如排序。
- disk_temptable_create_cost (default 40.0) 內(nèi)部myisam或innodb臨時(shí)表的創(chuàng)建代價(jià)。
- disk_temptable_row_cost (default 1.0) 內(nèi)部myisam或innodb臨時(shí)表的行代價(jià)。
在MySQL 5.7中，這些操作代價(jià)的默認(rèn)值都可以進(jìn)行配置。為了計(jì)算出方案的總代價(jià)，還需要參考一些統(tǒng)計(jì)數(shù)據(jù)，如表數(shù)據(jù)量大小、元數(shù)據(jù)和索引信息等。MySQL的代價(jià)優(yōu)化器模型整體如下圖所示：
代價(jià)模型
2.3 基于代價(jià)的索引選擇
還是繼續(xù)拿上述的SQL select * from sync_test1 where name like 'Bobby%' and dt > '2021-07-06'為例，我們看看MySQL優(yōu)化器是如何根據(jù)代價(jià)模型選擇索引的。首先，我們直接在建表時(shí)加入四個(gè)候選索引。
Create Table: CREATE TABLE `sync_test1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`cid` int(11) NOT NULL,
`phone` int(11) NOT NULL,
`name` varchar(10) NOT NULL,
`address` varchar(255) DEFAULT NULL,
`dt` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `IX_name` (`name`),
KEY `IX_dt` (`dt`),
KEY `IX_dt_name` (`dt`,`name`),
KEY `IX_name_dt` (`name`,`dt`)
) ENGINE=InnoDB
通過(guò)執(zhí)行explain看出MySQL最終選擇了IX_name索引。
mysql> explain select * from sync_test1 where name like 'Bobby%' and dt > '2021-07-06';
+----+-------------+------------+------------+-------+-------------------------------------+---------+---------+------+------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+-------------------------------------+---------+---------+------+------+----------+------------------------------------+
| 1 | SIMPLE | sync_test1 | NULL | range | IX_name,IX_dt,IX_dt_name,IX_name_dt | IX_name | 12 | NULL | 572 | 36.83 | Using index condition; Using where |
+----+-------------+------------+------------+-------+-------------------------------------+---------+---------+------+------+----------+------------------------------------+
然后再打開(kāi)MySQL追蹤優(yōu)化器Trace功能。可以看出，沒(méi)有選擇其他三個(gè)索引的原因均是因?yàn)樵谄渌齻€(gè)索引上使用range scan的代價(jià)均>= IX_name。
mysql> select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE\G;
*************************** 1. row ***************************
TRACE: {
...
"rows_estimation": [
{
"table": "`sync_test1`",
"range_analysis": {
"table_scan": {
"rows": 105084,
"cost": 21628
},
...
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "IX_name",
"ranges": [
"Bobby\u0000\u0000\u0000\u0000\u0000 <= name <= Bobby?????"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 572,
"cost": 687.41,
"chosen": true
},
{
"index": "IX_dt",
"ranges": [
"0x99aa0c0000 < dt"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 38698,
"cost": 46439,
"chosen": false,
"cause": "cost"
},
{
"index": "IX_dt_name",
"ranges": [
"0x99aa0c0000 < dt"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 38292,
"cost": 45951,
"chosen": false,
"cause": "cost"
},
{
"index": "IX_name_dt",
"ranges": [
"Bobby\u0000\u0000\u0000\u0000\u0000 <= name <= Bobby?????"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 572,
"cost": 687.41,
"chosen": false,
"cause": "cost"
}
],
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
}
},
"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan",
"index": "IX_name",
"rows": 572,
"ranges": [
"Bobby\u0000\u0000\u0000\u0000\u0000 <= name <= Bobby?????"
]
},
"rows_for_plan": 572,
"cost_for_plan": 687.41,
"chosen": true
}
...
}
下面我們根據(jù)代價(jià)模型來(lái)推演一下代價(jià)的計(jì)算過(guò)程：
- 走全表掃描的代價(jià)：io_cost + cpu_cost = (數(shù)據(jù)頁(yè)個(gè)數(shù) * io_block_read_cost)+ (數(shù)據(jù)行數(shù) * row_evaluate_cost + 1.1) = (data_length / block_size + 1)+ (rows * 0.2 + 1.1) = (9977856 / 16384 + 1) + (105084 * 0.2 + 1.1) = 21627.9。
- 走二級(jí)索引IX_name的代價(jià)：io_cost + cpu_cost = (預(yù)估范圍行數(shù) * io_block_read_cost + 1) + (數(shù)據(jù)行數(shù) * row_evaluate_cost + 0.01) = (572 * 1 + 1) + (572*0.2 + 0.01) = 687.41。
- 走二級(jí)索引IX_dt的代價(jià)：io_cost + cpu_cost = (預(yù)估范圍行數(shù) * io_block_read_cost + 1) + (數(shù)據(jù)行數(shù) * row_evaluate_cost + 0.01) = (38698 * 1 + 1) + (38698*0.2 + 0.01) = 46438.61。
- 走二級(jí)索引IX_dt_name的代價(jià): io_cost + cpu_cost = (預(yù)估范圍行數(shù) * io_block_read_cost + 1) + (數(shù)據(jù)行數(shù) * row_evaluate_cost + 0.01) = (38292 * 1 + 1) + (38292 * 0.2 + 0.01) = 45951.41。
- 走二級(jí)索引IX_name_dt的代價(jià)：io_cost + cpu_cost = (預(yù)估范圍行數(shù) * io_block_read_cost + 1) + (數(shù)據(jù)行數(shù) * row_evaluate_cost + 0.01) = (572 * 1 + 1) + (572*0.2 + 0.01) = 687.41。
補(bǔ)充說(shuō)明
- 計(jì)算結(jié)果在小數(shù)上有偏差，因?yàn)镸ySQL使用%g打印浮點(diǎn)數(shù)，小數(shù)會(huì)以最短的方式輸出。
- 除“+1.1 +1”這種調(diào)節(jié)值外，Cost計(jì)算還會(huì)出現(xiàn)+0.01, 它是為了避免index scan和range scan出現(xiàn)Cost的競(jìng)爭(zhēng)。
- Cost計(jì)算是基于MySQL的默認(rèn)參數(shù)配置，如果Cost Model參數(shù)改變，optimizer_switch的選項(xiàng)不同，數(shù)據(jù)分布不同都會(huì)導(dǎo)致最終Cost的計(jì)算結(jié)果不同。
- data_length可查詢(xún)information_schema.tables，block_size默認(rèn)16K。
2.4 基于代價(jià)的索引推薦思路
如果想借助MySQL優(yōu)化器給慢查詢(xún)計(jì)算出最佳索引，那么需要真實(shí)地在業(yè)務(wù)表上添加所有候選索引。對(duì)于線上業(yè)務(wù)來(lái)說(shuō)，直接添加索引的時(shí)間空間成本太高，是不可接受的。MySQL優(yōu)化器選最佳索引用到的數(shù)據(jù)是索引元數(shù)據(jù)和統(tǒng)計(jì)數(shù)據(jù)，所以我們想是否可以通過(guò)給它提供候選索引的這些數(shù)據(jù)，而非真實(shí)添加索引的這種方式來(lái)實(shí)現(xiàn)。
通過(guò)深入調(diào)研MySQL的代碼結(jié)構(gòu)和優(yōu)化器流程，我們發(fā)現(xiàn)是可行的：一部分存在于Server層的frm文件中，比如索引定義;另一部分存在于Engine層中，或者通過(guò)調(diào)用Engine層的接口函數(shù)來(lái)獲取，比如索引中某個(gè)列的不同值個(gè)數(shù)、索引占據(jù)的頁(yè)面大小等。索引相關(guān)的信息，如下圖所示：
基于代價(jià)的索引推薦思路
因?yàn)镸ySQL本身就支持自定義存儲(chǔ)引擎，所以索引推薦思路是構(gòu)建一個(gè)支持虛假索引的存儲(chǔ)引擎，在它上面建立包含候選索引的空表，再采集樣本數(shù)據(jù)，計(jì)算出統(tǒng)計(jì)數(shù)據(jù)提供給優(yōu)化器，讓優(yōu)化器選出最優(yōu)索引，整個(gè)調(diào)用關(guān)系如下圖所示：
基于代價(jià)的索引推薦思路
3 索引推薦實(shí)現(xiàn)
因?yàn)榇鎯?chǔ)引擎本身并不具備對(duì)外提供服務(wù)的能力，直接在MySQL Server層修改也難以維護(hù)，所以我們將整個(gè)索引推薦系統(tǒng)拆分成支持虛假索引的Fakeindex存儲(chǔ)引擎和對(duì)外提供服務(wù)的Go-Server兩部分，整體架構(gòu)圖如下：
架構(gòu)圖
首先簡(jiǎn)要介紹一下Fakeindex存儲(chǔ)引擎，這是一個(gè)輕量級(jí)的存儲(chǔ)引擎，負(fù)責(zé)將索引的相關(guān)接口透?jìng)鞯紾o-Server部分。因?yàn)樗仨毑捎肅++實(shí)現(xiàn)，與Go-Server間存在跨語(yǔ)言調(diào)用的問(wèn)題，我們使用了Go原生的輕量級(jí)RPC技術(shù)+cgo來(lái)避免引入重量級(jí)的RPC框架，也不必引入第三方依賴(lài)包。函數(shù)調(diào)用鏈路如下所示，MySQL優(yōu)化器調(diào)用Fakeindex的C++函數(shù)，參數(shù)轉(zhuǎn)換成C語(yǔ)言，然后通過(guò)cgo調(diào)用到Go語(yǔ)言的方法，再通過(guò)Go自帶的RPC客戶(hù)端向服務(wù)端發(fā)起調(diào)用。
調(diào)用鏈路
下面將重點(diǎn)闡述核心邏輯Go-Server部分，主要流程步驟如下。
3.1 前置校驗(yàn)
首先根據(jù)經(jīng)驗(yàn)規(guī)則，排除一些不支持通過(guò)添加索引來(lái)提高查詢(xún)效率的場(chǎng)景，如查系統(tǒng)庫(kù)的SQL，非select、update、delete SQL等。
3.2 提取關(guān)鍵列名
這一步提取SQL可用來(lái)添加索引的候選列名，除了選擇給出現(xiàn)在where中的列添加索引，MySQL對(duì)排序、聚合、表連接、聚合函數(shù)(如max)也支持使用索引來(lái)提高查詢(xún)效率。我們對(duì)SQL進(jìn)行語(yǔ)法樹(shù)解析，在樹(shù)節(jié)點(diǎn)的where、join、order by、group by、聚合函數(shù)中提取列名，作為索引的候選列。值得注意的是，對(duì)于某些SQL，還需結(jié)合表結(jié)構(gòu)才能準(zhǔn)確地提取，比如：
- select * from tb1, tb2 where a = 1，列a歸屬tb1還是tb2取決于誰(shuí)唯一包含列a。
- select * from tb1 natural join tb2 where tb1.a = 1，在自然連接中，tb1和tb2默認(rèn)使用了相同列名進(jìn)行連接，但SQL中并沒(méi)有暴露出這些可用于添加索引的列。
3.3 生成候選索引
將提取出的關(guān)鍵列名進(jìn)行全排列即包含所有的索引組合，如列A、B、C的所有索引組合是['A', 'B', 'C', 'AB', 'AC', 'BA', 'BC', 'CA', 'CB', 'ABC', 'ACB', 'BAC', 'BCA', 'CAB', 'CBA']，但還需排除一些索引才能得到所有的候選索引，比如：
- 已經(jīng)存在的索引，如存在AB，需排除AB、A，因?yàn)镸ySQL支持使用前綴索引。
- 超過(guò)最大索引長(zhǎng)度3072字節(jié)限制的索引。
- 一些暫時(shí)不支持的索引，如帶地理數(shù)據(jù)類(lèi)型列的空間索引。
3.4 數(shù)據(jù)采集
直接從業(yè)務(wù)數(shù)據(jù)庫(kù)采集，數(shù)據(jù)分成元數(shù)據(jù)、統(tǒng)計(jì)數(shù)據(jù)、樣本數(shù)據(jù)三部分：
元數(shù)據(jù)：即表的定義數(shù)據(jù)，包括列定義、索引定義，可通過(guò)show create table獲取。
統(tǒng)計(jì)數(shù)據(jù)：如表的行數(shù)、表數(shù)據(jù)大小、索引大小，可以通過(guò)查詢(xún)infromation_schema.tables獲取;已存在索引的cardinality(關(guān)鍵值：即索引列的不同值個(gè)數(shù)，值越大，索引優(yōu)化效果越明顯)，可以通過(guò)查詢(xún)mysql.innodb_index_stats表獲取。
樣本數(shù)據(jù)：候選索引為假索引，采集的統(tǒng)計(jì)數(shù)據(jù)并不包含假索引的數(shù)據(jù)，這里我們通過(guò)采集原表的樣本數(shù)據(jù)來(lái)計(jì)算出假索引的統(tǒng)計(jì)數(shù)據(jù)。
數(shù)據(jù)采集
下面介紹樣本數(shù)據(jù)的采樣算法，好的采樣算法應(yīng)該盡最大可能采集到符合原表數(shù)據(jù)分布的樣本。比如基于均勻隨機(jī)采樣的方式select * from table where rand() < rate，然而它會(huì)給線上數(shù)據(jù)庫(kù)造成大量I/O的問(wèn)題，嚴(yán)重時(shí)可引發(fā)數(shù)據(jù)庫(kù)故障。所以我們采用了基于塊的采樣方式：它參考了MySQL 8.0的直方圖采樣算法，如對(duì)于一張100萬(wàn)的表，采集10萬(wàn)行數(shù)，根據(jù)主鍵的最小值最大值將表數(shù)據(jù)均分成100個(gè)區(qū)間，每個(gè)區(qū)間取一塊1000行數(shù)據(jù)，采集數(shù)據(jù)的SQL，最后將采集到的數(shù)據(jù)塞入采樣表中。代碼如下：
select A,B,C,id from table where id >= 1000 and id <= 10000 limit 1000;
select A,B,C,id from table where id >= 10000 and id <= 20000 limit 1000;
...
3.5 統(tǒng)計(jì)數(shù)據(jù)計(jì)算
下面舉例說(shuō)明兩個(gè)核心統(tǒng)計(jì)數(shù)據(jù)的計(jì)算方式。首先是records_in_range，優(yōu)化器在處理范圍查詢(xún)時(shí)，如果可以用索引，就會(huì)調(diào)用該函數(shù)估算走該索引可過(guò)濾出的行數(shù)，以此決定最終選用的索引。
比如，對(duì)于SQLselect * from table1 where A > 100 and B < 1000，候選索引A、B來(lái)說(shuō)，優(yōu)化器會(huì)調(diào)用此函數(shù)在索引頁(yè)A上估算A > 100有多少行數(shù)，在索引頁(yè)B上估計(jì)B<1000的行數(shù)，例如滿足條件的A有200行，B有50行，那么優(yōu)化器會(huì)優(yōu)先選擇使用索引B。對(duì)于假索引來(lái)說(shuō)，我們按照該公式：樣本滿足條件的范圍行數(shù) * (原表行數(shù) / 樣本表行數(shù))，直接樣本數(shù)據(jù)中查找，然后按照采樣比例放大即可估算出原表中滿足條件的范圍行數(shù)。
其次是用于計(jì)算索引區(qū)分度的cardinality。如果直接套用上述公式：樣本列上不同值個(gè)數(shù) * (原表行數(shù) / 樣本表行數(shù))， 如上述的候選索引A，根據(jù)樣本統(tǒng)計(jì)出共有100個(gè)不同值，那么在原表中，該列有多少不同值?一般以為是10,000 =100 *(1,000,000/100,000)。但這樣計(jì)算不適用某些場(chǎng)景，比如狀態(tài)碼字段，可能最多100個(gè)不同值。針對(duì)該問(wèn)題，我們引入斜率和兩趟計(jì)算來(lái)規(guī)避，流程如下：
第一趟計(jì)算：取樣本數(shù)據(jù)一半來(lái)統(tǒng)計(jì)A的不同值個(gè)數(shù)R1，區(qū)間[min_id, min_id+(max_id - min_id) / 2]。
第二趟計(jì)算：取所有樣本據(jù)統(tǒng)計(jì)A的不同值個(gè)數(shù)R2，區(qū)間[min_id, max_id] 計(jì)算斜率：R2/R1。
判斷斜率：如果斜率小于1.1，為固定值100，否則根據(jù)采樣比例放大，為10,000。
統(tǒng)計(jì)數(shù)據(jù)計(jì)算
3.6 候選索引代價(jià)評(píng)估
這一步讓優(yōu)化器幫助我們從候選索引中選出最佳索引，主要步驟如下：
- 建包含候選索引的表：將候選索引塞入原表定義，并把存儲(chǔ)引擎改為Fakeindex，在推薦引擎的mysqld上創(chuàng)建表。
- 通過(guò)在推薦引擎mysqld上explain format=json SQL，獲取優(yōu)化器選擇的索引。
值得注意的是，MySQL表最多建64個(gè)索引(二級(jí)索引)，計(jì)算所有候選索引的可能時(shí)，使用的是增幅比指數(shù)還恐怖的全排列算法。如下圖所示，隨著列數(shù)的增加，候選索引數(shù)量急劇上升，在5個(gè)候選列時(shí)的索引組合數(shù)量就超過(guò)了MySQL最大值，顯然不能滿足一些復(fù)雜SQL的需求。統(tǒng)計(jì)美團(tuán)線上索引列數(shù)分布后，我們發(fā)現(xiàn)，95%以上的索引列數(shù)都<=3個(gè)。同時(shí)基于經(jīng)驗(yàn)考慮，3列索引也可滿足絕大部分場(chǎng)景，剩余場(chǎng)景會(huì)通過(guò)其他方式，如庫(kù)表拆分來(lái)提高查詢(xún)性能，而不是增加索引列個(gè)數(shù)。
候選索引代價(jià)評(píng)估
但即便最多推薦3列索引，在5個(gè)候選列時(shí)其排列數(shù)量85=也遠(yuǎn)超64。這里我們采用歸并思路。如下圖所示，將所有候選索引拆分到多個(gè)表中，采用兩次計(jì)算，先讓MySQL優(yōu)化器選出批次一的最佳索引，可采用并行計(jì)算保證時(shí)效性，再M(fèi)ySQL選出批次一所有最佳索引的最佳索引，該方案可以最多支持4096個(gè)候選索引，結(jié)合最大索引3列限制，可以支持計(jì)算出17個(gè)候選列的最佳索引。
候選索引代價(jià)評(píng)估
4 推薦質(zhì)量保證
為了得到索引推薦質(zhì)量大致的整體數(shù)據(jù)，我們使用美團(tuán)數(shù)據(jù)庫(kù)最近一周的線下慢查詢(xún)數(shù)據(jù)，共246G、約3萬(wàn)個(gè)SQL模板用例做了一個(gè)初步測(cè)試。
建議質(zhì)量保證
從結(jié)果可以看出，系統(tǒng)基本能覆蓋到大部分的慢查詢(xún)。但還是會(huì)出現(xiàn)無(wú)效的推薦，大致原因如下：
- 索引推薦計(jì)算出的Cost嚴(yán)重依賴(lài)樣本數(shù)據(jù)的質(zhì)量，在當(dāng)表數(shù)據(jù)分布不均或數(shù)據(jù)傾斜時(shí)會(huì)導(dǎo)致統(tǒng)計(jì)數(shù)據(jù)出現(xiàn)誤差，導(dǎo)致推薦出錯(cuò)誤索引。
- 索引推薦系統(tǒng)本身存在缺陷，從而導(dǎo)致推薦出錯(cuò)誤索引。
- MySQL優(yōu)化器自身存在的缺陷，導(dǎo)致推薦出錯(cuò)誤索引。
因此，我們?cè)跇I(yè)務(wù)添加索引前后增加了索引的有效性驗(yàn)證和效果追蹤兩個(gè)步驟，整個(gè)流程如下所示：
全鏈路
4.1 有效性驗(yàn)證
因?yàn)槟壳斑€不具備大規(guī)模數(shù)據(jù)庫(kù)備份快速還原的能力，所以無(wú)法使用完整的備份數(shù)據(jù)做驗(yàn)證。我們近似地認(rèn)為，如果推薦索引在業(yè)務(wù)庫(kù)上取得較好的效果，那么在樣本庫(kù)也會(huì)取得不錯(cuò)效果。通過(guò)真正地在樣本庫(kù)上真實(shí)執(zhí)行SQL，并添加索引來(lái)驗(yàn)證其有效性，驗(yàn)證結(jié)果展示如下：
有效性驗(yàn)證
4.2 效果追蹤
考慮到使用采樣數(shù)據(jù)驗(yàn)證的局限性，所以當(dāng)在生產(chǎn)環(huán)境索引添加完畢之后，會(huì)立即對(duì)添加的索引進(jìn)行效果追蹤。一方面通過(guò)explain驗(yàn)證索引是否被真正用到，以及Cost是否減小;另一方面用Flink實(shí)時(shí)跟蹤該數(shù)據(jù)庫(kù)的全量SQL訪問(wèn)數(shù)據(jù)，通過(guò)對(duì)比索引添加前后，該SQL的真實(shí)執(zhí)行時(shí)間來(lái)判斷索引是否有效。如果發(fā)現(xiàn)有性能方面的回退，則立即發(fā)出告警，周知到DBA和研發(fā)人員。生成的報(bào)告如下：
效果追蹤
4.3 仿真環(huán)境
當(dāng)推薦鏈路出現(xiàn)問(wèn)題時(shí)，直接在線上排查驗(yàn)證問(wèn)題的話，很容易給業(yè)務(wù)帶來(lái)安全隱患，同時(shí)也降低了系統(tǒng)的穩(wěn)定性。對(duì)此我們搭建了離線仿真環(huán)境，利用數(shù)據(jù)庫(kù)備份構(gòu)建了和生產(chǎn)環(huán)境一樣的數(shù)據(jù)源，并完整復(fù)刻了線上推薦鏈路的各個(gè)步驟，在仿真環(huán)境回放異常案例，復(fù)現(xiàn)問(wèn)題、排查根因，反復(fù)驗(yàn)證改進(jìn)方案后再上線到生產(chǎn)系統(tǒng)，進(jìn)而不斷優(yōu)化現(xiàn)有系統(tǒng)，提升推薦質(zhì)量。
仿真環(huán)境
4.4 測(cè)試案例庫(kù)
在上線過(guò)程中，往往會(huì)出現(xiàn)改進(jìn)方案修復(fù)了一個(gè)Bug，帶來(lái)了更多Bug的情況。能否做好索引推薦能力的回歸測(cè)試，直接決定了推薦質(zhì)量的穩(wěn)定性。于是，我們參考了阿里云的技術(shù)方案，計(jì)劃構(gòu)建一個(gè)盡可能完備的測(cè)試案例庫(kù)用于衡量索引推薦服務(wù)能力強(qiáng)弱。但考慮影響MySQL索引選擇的因素眾多，各因素間的組合，SQL的復(fù)雜性，如果人為去設(shè)計(jì)測(cè)試用例是是不切實(shí)際的，我們通過(guò)下列方法自動(dòng)化收集測(cè)試用例：
- 利用美團(tuán)線上的豐富數(shù)據(jù)，以影響MySQL索引選擇的因素特征為抓手，直接從全量SQL和慢SQL中抽取最真實(shí)的案例，不斷更新現(xiàn)有測(cè)試案例庫(kù)。
- 在生產(chǎn)的推薦系統(tǒng)鏈路上埋點(diǎn)，自動(dòng)收集異常案例，回流到現(xiàn)有的測(cè)試案例庫(kù)。
- 對(duì)于現(xiàn)有數(shù)據(jù)沒(méi)有覆蓋到的極端場(chǎng)景，采用人為構(gòu)造的方案，補(bǔ)充測(cè)試用例。
測(cè)試案例庫(kù)
5 慢查詢(xún)治理運(yùn)營(yíng)
我們主要從時(shí)間維度的三個(gè)方向?qū)⒙樵?xún)接入索引推薦，推廣治理：
慢查詢(xún)治理運(yùn)營(yíng)
5.1 過(guò)去-歷史慢查詢(xún)
這類(lèi)慢查詢(xún)屬于過(guò)去產(chǎn)生的，并且一直存在，數(shù)量較多，治理推動(dòng)力不足，可通過(guò)收集歷史慢查詢(xún)?nèi)罩景l(fā)現(xiàn)，分成兩類(lèi)接入：
核心數(shù)據(jù)庫(kù)：該類(lèi)慢查詢(xún)通常會(huì)被周期性地關(guān)注，如慢查詢(xún)周報(bào)、月報(bào)，可直接將優(yōu)化建議提前生成出來(lái)，接入它們，一并運(yùn)營(yíng)治理。
普通數(shù)據(jù)庫(kù)：可將優(yōu)化建議直接接入數(shù)據(jù)庫(kù)平臺(tái)的慢查詢(xún)模塊，讓研發(fā)自助地選擇治理哪些慢查詢(xún)。
5.2 現(xiàn)在-新增慢查詢(xún)
這類(lèi)慢查詢(xún)屬于當(dāng)前產(chǎn)生的，數(shù)量較少，屬于治理的重點(diǎn)，也可通過(guò)實(shí)時(shí)收集慢查詢(xún)?nèi)罩景l(fā)現(xiàn)，分成兩類(lèi)接入：
影響程度一般的慢查詢(xún)：可通過(guò)實(shí)時(shí)分析慢查詢(xún)?nèi)罩?，?duì)比歷史慢查詢(xún)，識(shí)別出新增慢查詢(xún)，并生成優(yōu)化建議，為用戶(hù)創(chuàng)建數(shù)據(jù)庫(kù)風(fēng)險(xiǎn)項(xiàng)，跟進(jìn)治理。
影響程度較大的慢查詢(xún)：該類(lèi)通常會(huì)引發(fā)數(shù)據(jù)庫(kù)告警，如慢查詢(xún)導(dǎo)致數(shù)據(jù)庫(kù)Load過(guò)高，可通過(guò)故障診斷根因系統(tǒng)，識(shí)別出具體的慢查詢(xún)SQL，并生成優(yōu)化建議，及時(shí)推送到故障處理群，降低故障處理時(shí)長(zhǎng)。
5.3 未來(lái)-潛在慢查詢(xún)
這類(lèi)查詢(xún)屬于當(dāng)前還沒(méi)被定義成慢查詢(xún)，隨著時(shí)間推進(jìn)可能變成演變成慢查詢(xún)，對(duì)于一些核心業(yè)務(wù)來(lái)說(shuō)，往往會(huì)引發(fā)故障，屬于他們治理的重點(diǎn)，分成兩類(lèi)接入：
未上線的準(zhǔn)慢查詢(xún)：項(xiàng)目準(zhǔn)備上線而引入的新的準(zhǔn)慢查詢(xún)，可接入發(fā)布前的集成測(cè)試流水線，Java項(xiàng)目可通過(guò) agentmain的代理方式攔截被測(cè)試用例覆蓋到的SQL，再通過(guò)經(jīng)驗(yàn)+explain識(shí)別出慢查詢(xún)，并生成優(yōu)化建議，給用戶(hù)在需求管理系統(tǒng)上創(chuàng)建缺陷任務(wù)，解決后才能發(fā)布上線。
已上線的準(zhǔn)慢查詢(xún)：該類(lèi)屬于當(dāng)前執(zhí)行時(shí)間較快的SQL，隨著表數(shù)據(jù)量的增加，會(huì)演變成慢查詢(xún)，最常見(jiàn)的就是全表掃描，這類(lèi)可通過(guò)增加慢查詢(xún)配置參數(shù)log_queries_not_using_indexes記錄到慢日志，并生成優(yōu)化建議，為用戶(hù)創(chuàng)建數(shù)據(jù)庫(kù)風(fēng)險(xiǎn)項(xiàng)，跟進(jìn)治理。
6 項(xiàng)目運(yùn)行情況
當(dāng)前，主要以新增慢查詢(xún)?yōu)橥黄泣c(diǎn)，重點(diǎn)為全表掃描推薦優(yōu)化建議。目前我們已經(jīng)灰度接入了一小部分業(yè)務(wù)，共分析了六千多條慢查詢(xún)，推薦了一千多條高效索引建議。另外，美團(tuán)內(nèi)部的研發(fā)同學(xué)也可通過(guò)數(shù)據(jù)庫(kù)平臺(tái)自助發(fā)起SQL優(yōu)化建議工單，如下圖所示：
RDS平臺(tái)發(fā)起
另外在美團(tuán)內(nèi)部，我們已經(jīng)和數(shù)據(jù)庫(kù)告警打通，實(shí)現(xiàn)了故障發(fā)現(xiàn)、根因分析、解決方案的自動(dòng)化處理，極大地提高了故障處理效率。下面是一個(gè)展示案例，當(dāng)數(shù)據(jù)庫(kù)集群發(fā)生告警，我們會(huì)拉一個(gè)故障群，先通過(guò)根因定位系統(tǒng)，如果識(shí)別出慢查詢(xún)?cè)斐傻?，?huì)馬上調(diào)用SQL優(yōu)化建議系統(tǒng)，推薦出索引，整個(gè)處理流程是分鐘級(jí)別，都會(huì)在群里面推送最新消息。如下圖所示：
告警診斷
7 未來(lái)規(guī)劃
考慮到美團(tuán)日均產(chǎn)生近億級(jí)別的慢查詢(xún)數(shù)據(jù)，為了實(shí)現(xiàn)對(duì)它們的診斷分析，我們還需要提高系統(tǒng)大規(guī)模的數(shù)據(jù)并發(fā)處理的能力。另外，當(dāng)前該系統(tǒng)還是針對(duì)單SQL的優(yōu)化，沒(méi)有考慮維護(hù)新索引帶來(lái)的代價(jià)，如占用額外的磁盤(pán)空間，使寫(xiě)操作變慢，也沒(méi)有考慮到MySQL選錯(cuò)索引引發(fā)其他SQL的性能回退。對(duì)于業(yè)務(wù)或者DBA來(lái)說(shuō)，我們更多關(guān)心的是整個(gè)數(shù)據(jù)庫(kù)或者集群層面的優(yōu)化。
業(yè)界如阿里云的DAS則是站在全局的角度考量，綜合考慮各個(gè)因素，輸出需要?jiǎng)?chuàng)建的新索引、需要改寫(xiě)的索引、需要?jiǎng)h除的索引，實(shí)現(xiàn)數(shù)據(jù)庫(kù)性能最大化提升，同時(shí)最大化降低磁盤(pán)空間消耗。未來(lái)我們也將不斷優(yōu)化和改進(jìn)，實(shí)現(xiàn)類(lèi)似基于Workload的全局優(yōu)化。
當(dāng)前名稱(chēng)：基于代價(jià)的慢查詢(xún)優(yōu)化建議
文章源于：http://www.5511xx.com/article/cosghsp.html
