背景
在 MySQL 中，當(dāng)我們?yōu)楸韯?chuàng)建了一個(gè)或多個(gè)索引后，通常需要在索引定義完成后，根據(jù)具體的數(shù)據(jù)情況執(zhí)行 EXPLAIN 命令，才能觀察到數(shù)據(jù)庫(kù)實(shí)際使用哪個(gè)索引、是否使用索引。這使得我們?cè)谔砑有滤饕埃瑹o(wú)法提前預(yù)知數(shù)據(jù)庫(kù)是否能使用期望的索引。更為糟糕的是，有時(shí)甚至在添加新的索引后，數(shù)據(jù)庫(kù)在某些查詢中會(huì)使用它，而在其他查詢中則不會(huì)使用，這種情況下，我們無(wú)法確定索引是否發(fā)揮了預(yù)期的作用，讓人感到非常苦惱。這種情況基本上意味著 MySQL 并沒(méi)有為我們選擇最優(yōu)的索引，而我們不得不在茫茫數(shù)據(jù)中摸索，試圖找到問(wèn)題的癥結(jié)所在。我們可能會(huì)嘗試調(diào)整索引，甚至刪除索引，然后重新添加，希望 MySQL 能從中找到最優(yōu)的索引選擇。然而，這樣的過(guò)程既耗時(shí)又費(fèi)力，而且往往收效甚微。
如果在添加索引之前，我們能夠預(yù)知索引的使用情況，那么對(duì)于表設(shè)計(jì)將大有裨益。我們可以在設(shè)計(jì)表結(jié)構(gòu)時(shí)，更加明確地知道應(yīng)該選擇哪些索引，如何優(yōu)化索引，以提高查詢效率。我們不再需要依賴盲目嘗試和猜測(cè)，而是可以基于實(shí)際的數(shù)據(jù)和查詢情況，做出更加明智的決策。因此，對(duì)于 MySQL 用戶來(lái)說(shuō)，能夠預(yù)知索引走勢(shì)的需求非常迫切。我們希望能有一種方法，能夠讓我們?cè)谔砑铀饕埃颓宄亓私?MySQL 將如何使用索引，以便我們能夠更好地優(yōu)化表結(jié)構(gòu)，提高查詢效率。這將極大地減輕我們的工作負(fù)擔(dān)，提高我們的工作效率，讓我們能夠更加專注于業(yè)務(wù)邏輯的處理，而不是在索引的海洋中掙扎。
為了解決這個(gè)問(wèn)題，我們可以深入研究 MySQL 的索引選擇機(jī)制。實(shí)際上，這個(gè)機(jī)制的核心就是代價(jià)模型，它通過(guò)一個(gè)公式來(lái)決定索引的選擇策略。相對(duì)于 MySQL 其他復(fù)雜的概念，代價(jià)模型實(shí)現(xiàn)起來(lái)要簡(jiǎn)單得多。熟悉代價(jià)模型之后，我們可以預(yù)先了解 MySQL 在執(zhí)行查詢時(shí)會(huì)如何選擇索引，從而更有效地進(jìn)行索引優(yōu)化。在接下來(lái)的文章中，我將結(jié)合近期進(jìn)行索引優(yōu)化的具體案例，來(lái)詳細(xì)解釋如何運(yùn)用代價(jià)模型來(lái)優(yōu)化索引。
MySQL 代價(jià)模型淺析
?MySQL 數(shù)據(jù)庫(kù)主要由 4 層組成：
1. 連接層：客戶端和連接服務(wù)，主要完成一些類似于連接處理、授權(quán)管理、以及相關(guān)的安全方案。
2. 服務(wù)層：主要完成大多數(shù)的核心服務(wù)功能，如 SQL 接口，并完成緩存的查詢，SQL 的分析和優(yōu)化以及內(nèi)部函數(shù)的執(zhí)行。
3. 引擎層：負(fù)責(zé) MySQL 中數(shù)據(jù)的存儲(chǔ)和提取，服務(wù)器通過(guò) AP1 與存儲(chǔ)引擎進(jìn)行通信。
4. 存儲(chǔ)層：將數(shù)據(jù)存儲(chǔ)文件系統(tǒng)上，并完成與存儲(chǔ)引擎的交互。
索引策略選擇在 SQL 優(yōu)化器進(jìn)行的
SQL 優(yōu)化器會(huì)分析所有可能的執(zhí)行計(jì)劃，選擇成本最低的執(zhí)行，這種優(yōu)化器稱之為：CBO（Cost-based Optimizer，基于成本的優(yōu)化器）。
Cost = Server Cost + Engine Cost = CPU Cost + IO Cost
其中，CPU Cost 表示計(jì)算的開(kāi)銷，比如索引鍵值的比較、記錄值的比較、結(jié)果集的排序 ...... 這些操作都在 Server 層完成；
IO Cost 表示引擎層 IO 的開(kāi)銷，MySQL 可以通過(guò)區(qū)分一張表的數(shù)據(jù)是否在內(nèi)存中，分別計(jì)算讀取內(nèi)存 IO 開(kāi)銷以及讀取磁盤(pán) IO 的開(kāi)銷。
源碼簡(jiǎn)讀
MySQL 的數(shù)據(jù)源代碼采用了 5.7.22 版本，后續(xù)的代價(jià)計(jì)算公式將基于此版本進(jìn)行參考。
opt_costconstants.cc【代價(jià)模型 —— 計(jì)算所需代價(jià)計(jì)算系數(shù)】
/*
在Server_cost_constants類中定義為靜態(tài)常量變量的成本常量的值。如果服務(wù)器管理員沒(méi)有在server_cost表中添加新值，則將使用這些默認(rèn)成本常數(shù)值。
5.7版本開(kāi)始可用從數(shù)據(jù)庫(kù)加載常量值，該版本前使用代碼中寫(xiě)的常量值
*/
// 計(jì)算符合條件的?的代價(jià)，?數(shù)越多，此項(xiàng)代價(jià)越?
const double Server_cost_constants::ROW_EVALUATE_COST= 0.2;
// 鍵?較的代價(jià)，例如排序
const double Server_cost_constants::KEY_COMPARE_COST= 0.1;
/*
內(nèi)存臨時(shí)表的創(chuàng)建代價(jià)
通過(guò)基準(zhǔn)測(cè)試，創(chuàng)建Memory臨時(shí)表的成本與向表中寫(xiě)入10行的成本一樣高。
*/
const double Server_cost_constants::MEMORY_TEMPTABLE_CREATE_COST= 2.0;
// 內(nèi)存臨時(shí)表的?代價(jià)
const double Server_cost_constants::MEMORY_TEMPTABLE_ROW_COST= 0.2;
/*
內(nèi)部myisam或innodb臨時(shí)表的創(chuàng)建代價(jià)
創(chuàng)建MyISAM表的速度是創(chuàng)建Memory表的20倍。
*/
const double Server_cost_constants::DISK_TEMPTABLE_CREATE_COST= 40.0;
/*
內(nèi)部myisam或innodb臨時(shí)表的?代價(jià)
當(dāng)行數(shù)大于1000時(shí)，按順序生成MyISAM行比生成Memory行慢2倍。然而，沒(méi)有非常大的表的基準(zhǔn)，因此保守地將此系數(shù)設(shè)置為慢5倍（即成本為1.0）。
*/
const double Server_cost_constants::DISK_TEMPTABLE_ROW_COST= 1.0;
/*
在SE_cost_constants類中定義為靜態(tài)常量變量的成本常量的值。如果服務(wù)器管理員沒(méi)有在engine_cost表中添加新值，則將使用這些默認(rèn)成本常數(shù)值。
*/
// 從主內(nèi)存緩沖池讀取塊的成本
const double SE_cost_constants::MEMORY_BLOCK_READ_COST= 1.0;
// 從IO設(shè)備（磁盤(pán)）讀取塊的成本
const double SE_cost_constants::IO_BLOCK_READ_COST= 1.0;
?opt_costmodel.cc【代價(jià)模型 —— 部分涉及方法】
double Cost_model_table::page_read_cost(double pages) const
{
DBUG_ASSERT(m_initialized);
DBUG_ASSERT(pages >= 0.0);
// 估算聚集索引內(nèi)存中頁(yè)面數(shù)占其所有頁(yè)面數(shù)的比率
const double in_mem= m_table->file->table_in_memory_estimate();
const double pages_in_mem= pages * in_mem;
const double pages_on_disk= pages - pages_in_mem;
DBUG_ASSERT(pages_on_disk >= 0.0);
const double cost= buffer_block_read_cost(pages_in_mem) +
io_block_read_cost(pages_on_disk);
return cost;
}
double Cost_model_table::page_read_cost_index(uint index, double pages) const
{
DBUG_ASSERT(m_initialized);
DBUG_ASSERT(pages >= 0.0);
double in_mem= m_table->file->index_in_memory_estimate(index);
const double pages_in_mem= pages * in_mem;
const double pages_on_disk= pages - pages_in_mem;
const double cost= buffer_block_read_cost(pages_in_mem) +
io_block_read_cost(pages_on_disk);
return cost;
}
?handler.cc【代價(jià)模型 —— 部分涉及方法】
// 聚集索引掃描IO代價(jià)計(jì)算公式
Cost_estimate handler::read_cost(uint index, double ranges, double rows)
{
DBUG_ASSERT(ranges >= 0.0);
DBUG_ASSERT(rows >= 0.0);
const double io_cost= read_time(index, static_cast<uint>(ranges),
static_cast<ha_rows>(rows)) *
table->cost_model()->page_read_cost(1.0);
Cost_estimate cost;
cost.add_io(io_cost);
return cost;
}
// 表全量掃描代價(jià)相關(guān)計(jì)算（IO-cost）
Cost_estimate handler::table_scan_cost()
{
const double io_cost= scan_time() * table->cost_model()->page_read_cost(1.0);
Cost_estimate cost;
cost.add_io(io_cost);
return cost;
}
// 覆蓋索引掃描代價(jià)相關(guān)計(jì)算
Cost_estimate handler::index_scan_cost(uint index, double ranges, double rows)
{
DBUG_ASSERT(ranges >= 0.0);
DBUG_ASSERT(rows >= 0.0);
const double io_cost= index_only_read_time(index, rows) *
table->cost_model()->page_read_cost_index(index, 1.0);
Cost_estimate cost;
cost.add_io(io_cost);
return cost;
}
/**
估算在指定 keynr索引進(jìn)行覆蓋掃描（不需要回表），掃描 records條記錄，需要讀取的索引頁(yè)面數(shù)
@param keynr Index number
@param records Estimated number of records to be retrieved
@return
Estimated cost of 'index only' scan
*/
double handler::index_only_read_time(uint keynr, double records)
{
double read_time;
uint keys_per_block= (stats.block_size/2/
(table_share->key_info[keynr].key_length + ref_length) +
1);
read_time=((double) (records + keys_per_block-1) /
(double) keys_per_block);
return read_time;
}
?sql_planner.cc【用于 ref 訪問(wèn)類型索引費(fèi)用計(jì)算】
double tmp_fanout= 0.0;
if (table->quick_keys.is_set(key) && !table_deps && //(C1)
table->quick_key_parts[key] == cur_used_keyparts && //(C2)
table->quick_n_ranges[key] == 1+MY_TEST(ref_or_null_part)) //(C3)
{
tmp_fanout= cur_fanout= (double) table->quick_rows[key];
}
else
{
// Check if we have statistic about the distribution
if (keyinfo->has_records_per_key(cur_used_keyparts - 1))
{
cur_fanout= keyinfo->records_per_key(cur_used_keyparts - 1);
if (!table_deps && table->quick_keys.is_set(key) && // (1)
table->quick_key_parts[key] > cur_used_keyparts) // (2)
{
trace_access_idx.add("chosen", false)
.add_alnum("cause", "range_uses_more_keyparts");
is_dodgy= true;
continue;
}
tmp_fanout= cur_fanout;
}
else
{
rec_per_key_t rec_per_key;
if (keyinfo->has_records_per_key(
keyinfo->user_defined_key_parts - 1))
rec_per_key=
keyinfo->records_per_key(keyinfo->user_defined_key_parts - 1);
else
rec_per_key=
rec_per_key_t(tab->records()) / distinct_keys_est + 1;
if (tab->records() == 0)
tmp_fanout= 0.0;
else if (rec_per_key / tab->records() >= 0.01)
tmp_fanout= rec_per_key;
else
{
const double a= tab->records() * 0.01;
if (keyinfo->user_defined_key_parts > 1)
tmp_fanout=
(cur_used_keyparts * (rec_per_key - a) +
a * keyinfo->user_defined_key_parts - rec_per_key) /
(keyinfo->user_defined_key_parts - 1);
else
tmp_fanout= a;
set_if_bigger(tmp_fanout, 1.0);
}
cur_fanout= (ulong) tmp_fanout;
}
if (ref_or_null_part)
{
// We need to do two key searches to find key
tmp_fanout*= 2.0;
cur_fanout*= 2.0;
}
if (table->quick_keys.is_set(key) &&
table->quick_key_parts[key] <= cur_used_keyparts &&
const_part &
((key_part_map)1 << table->quick_key_parts[key]) &&
table->quick_n_ranges[key] == 1 + MY_TEST(ref_or_null_part &
const_part) &&
cur_fanout > (double) table->quick_rows[key])
{
tmp_fanout= cur_fanout= (double) table->quick_rows[key];
}
}
······
······
// Limit the number of matched rows
const double tmp_fanout=
min(cur_fanout, (double) thd->variables.max_seeks_for_key);
if (table->covering_keys.is_set(key)
(table->file->index_flags(key, 0, 0) & HA_CLUSTERED_INDEX))
{
// We can use only index tree
const Cost_estimate index_read_cost=
table->file->index_scan_cost(key, 1, tmp_fanout);
cur_read_cost= prefix_rowcount * index_read_cost.total_cost();
}
else if (key == table->s->primary_key &&
table->file->primary_key_is_clustered())
{
const Cost_estimate table_read_cost=
table->file->read_cost(key, 1, tmp_fanout);
cur_read_cost= prefix_rowcount * table_read_cost.total_cost();
}
else
cur_read_cost= prefix_rowcount *
min(table->cost_model()->page_read_cost(tmp_fanout),
tab->worst_seeks);
?handler.cc【用于 range 訪問(wèn)類型索引費(fèi)用計(jì)算】
handler::multi_range_read_info_const(uint keyno, RANGE_SEQ_IF *seq,
void *seq_init_param, uint n_ranges_arg,
uint *bufsz, uint *flags,
Cost_estimate *cost)
{
KEY_MULTI_RANGE range;
range_seq_t seq_it;
ha_rows rows, total_rows= 0;
uint n_ranges=0;
THD *thd= current_thd;
/* Default MRR implementation doesn't need buffer */
*bufsz= 0;
DBUG_EXECUTE_IF("bug13822652_2", thd->killed= THD::KILL_QUERY;);
seq_it= seq->init(seq_init_param, n_ranges, *flags);
while (!seq->next(seq_it, &range))
{
if (unlikely(thd->killed != 0))
return HA_POS_ERROR;
n_ranges++;
key_range *min_endp, *max_endp;
if (range.range_flag & GEOM_FLAG)
{
min_endp= &range.start_key;
max_endp= NULL;
}
else
{
min_endp= range.start_key.length? &range.start_key : NULL;
max_endp= range.end_key.length? &range.end_key : NULL;
}
int keyparts_used= 0;
if ((range.range_flag & UNIQUE_RANGE) && // 1)
!(range.range_flag & NULL_RANGE))
rows= 1; /* there can be at most one row */
else if ((range.range_flag & EQ_RANGE) && // 2a)
(range.range_flag & USE_INDEX_STATISTICS) && // 2b)
(keyparts_used= my_count_bits(range.start_key.keypart_map)) &&
table->
key_info[keyno].has_records_per_key(keyparts_used-1) && // 2c)
!(range.range_flag & NULL_RANGE))
{
rows= static_cast<ha_rows>(
table->key_info[keyno].records_per_key(keyparts_used - 1));
}
else
{
DBUG_EXECUTE_IF("crash_records_in_range", DBUG_SUICIDE(););
DBUG_ASSERT(min_endp || max_endp);
if (HA_POS_ERROR == (rows= this->records_in_range(keyno, min_endp,
max_endp)))
{
/* Can't scan one range => can't do MRR scan at all */
total_rows= HA_POS_ERROR;
break;
}
}
total_rows += rows;
}
if (total_rows != HA_POS_ERROR)
{
const Cost_model_table *const cost_model= table->cost_model();
/* The following calculation is the same as in multi_range_read_info(): */
*flags|= HA_MRR_USE_DEFAULT_IMPL;
*flags|= HA_MRR_SUPPORT_SORTED;
DBUG_ASSERT(cost->is_zero());
if (*flags & HA_MRR_INDEX_ONLY)
*cost= index_scan_cost(keyno, static_cast<double>(n_ranges),
static_cast<double>(total_rows));
else
*cost= read_cost(keyno, static_cast<double>(n_ranges),
static_cast<double>(total_rows));
cost->add_cpu(cost_model->row_evaluate_cost(
static_cast<double>(total_rows)) + 0.01);
}
return total_rows;
}
驗(yàn)證公式
創(chuàng)建驗(yàn)證需要的表
CREATE TABLE `store_goods_center`
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主鍵id',
`sku_id` bigint(20) NOT NULL COMMENT '商品skuid',
`station_no` varchar(20) NOT NULL COMMENT '門(mén)店編號(hào)',
`org_code` bigint(20) NOT NULL COMMENT '商家編號(hào)',
`extend_field` text COMMENT '擴(kuò)展字段',
`version` int(11) DEFAULT '0' COMMENT '版本號(hào)',
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時(shí)間',
`create_pin` varchar(50) DEFAULT '' COMMENT '創(chuàng)建人',
`update_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '更新時(shí)間',
`update_pin` varchar(50) DEFAULT '' COMMENT '更新人',
`yn` tinyint(4) DEFAULT '0' COMMENT '刪除標(biāo)示 0:正常 1：刪除',
`ts` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '時(shí)間戳',
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_storegoods` (`station_no`, `sku_id`) USING BTREE,
KEY `idx_storegoods_org` (`org_code`, `sku_id`, `station_no`),
KEY `idx_sku_id` (`sku_id`),
KEY `idx_station_no_and_id` (`station_no`, `id`)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4 COMMENT ='門(mén)店商品關(guān)系表';
通過(guò)存儲(chǔ)過(guò)程初始化測(cè)試數(shù)據(jù)
DELIMITER //
CREATE PROCEDURE callback()
BEGIN
DECLARE num INT;
SET num = 1;
WHILE
num <= 100000 DO
INSERT INTO store_goods_center(sku_id, station_no, org_code) VALUES (num + 10000000, floor(50+rand()*(100-50+1)), num);
SET num = num + 1;
END WHILE;
END;
執(zhí)行存儲(chǔ)過(guò)程生成數(shù)據(jù)
CALL callback();
?1. 全表掃描計(jì)算代價(jià)公式
計(jì)算過(guò)程：
// 不同引擎計(jì)算方式有所區(qū)別
// innodb引擎實(shí)現(xiàn)handler.h
// 預(yù)估記錄數(shù)：ha_innobase::info_low
// 頁(yè)數(shù)量：ha_innobase::scan_time【數(shù)據(jù)總大小(字節(jié)) / 頁(yè)大小】
// 查詢?nèi)頂?shù)據(jù)大小（7880704）
SHOW TABLE STATUS LIKE 'store_goods_center';
// 查詢數(shù)據(jù)庫(kù)頁(yè)大小（默認(rèn)：16384）
SHOW VARIABLES LIKE 'innodb_page_size';
// 全表掃描計(jì)算代價(jià)
// 頁(yè)數(shù)量
page = 數(shù)據(jù)總大小(字節(jié)) / 頁(yè)大小 = 7880704 / 16384 = 481;
// 預(yù)估范圍行數(shù)（總數(shù)據(jù)條數(shù)：10萬(wàn)，預(yù)估數(shù)據(jù)條數(shù)：99827，有一定誤差）
records = 99827;
// 計(jì)算總代價(jià)
// 481 * 1 中的系數(shù)1 代表從主內(nèi)存緩沖池讀取塊的成本（SE_cost_constants::IO_BLOCK_READ_COST= 1.0）
// 99827 * 0.2 中的系數(shù)0.2 代表計(jì)算符合條件的?的代價(jià)（ROW_EVALUATE_COST= 0.2）
cost = IO-cost + CPU-cost = (481 * 1) + (99827 * 0.2) = 481 + 19965.4 = 20446.4
?驗(yàn)證結(jié)果：
explain format = json
select * from store_goods_center;
"cost_info": {"query_cost": "20446.40"}
?總結(jié)公式：
全表掃描代價(jià) = 數(shù)據(jù)總大小 / 16384 + 預(yù)估范圍行數(shù) * 0.2
2. 覆蓋索引掃描計(jì)算代價(jià)公式
計(jì)算過(guò)程：
// 查詢?nèi)頂?shù)據(jù)大小（7880704）
SHOW TABLE STATUS LIKE 'store_goods_center';
// 查詢數(shù)據(jù)庫(kù)頁(yè)大小（默認(rèn)：16384）
SHOW VARIABLES LIKE 'innodb_page_size';
// 預(yù)估范圍行數(shù)（總數(shù)據(jù)條數(shù)：1999，預(yù)估數(shù)據(jù)條數(shù)：1999，有一定誤差） 1999;
records = 1999
// keys_per_block計(jì)算
// block_size是文件的block大小，mysql默認(rèn)為16K；
// key_len是索引的鍵長(zhǎng)度；
// ref_len是主鍵索引的長(zhǎng)度；
keys_per_block = (stats.block_size / 2 / (table_share->key_info[keynr].key_length + ref_length) + 1);
// table_share->key_info[keynr].key_length 為聯(lián)合索引，分別是station_no和sku_id
// station_no 為varchar(20)且為utf8mb4，長(zhǎng)度 = 20 * 4 + 2 (可變長(zhǎng)度需要加2) = 82
// sku_id bigint類型，長(zhǎng)度為8
// 主鍵索引為bigint類型，長(zhǎng)度為8
keys_per_block = 16384 / 2 / (82 + 8 + 8) + 1 ≈ 84
// 計(jì)算總代價(jià)
read_time = ((double) (records + keys_per_block - 1) / (double) keys_per_block);
read_time = (1999 + 84 - 1) / 84 = 24.78;
// 計(jì)算總代價(jià)
// 24.78 * 1 中的系數(shù)1 代表從主內(nèi)存緩沖池讀取塊的成本（SE_cost_constants::IO_BLOCK_READ_COST= 1.0）
// 1999 * 0.2 中的系數(shù)0.2 代表計(jì)算符合條件的?的代價(jià)（ROW_EVALUATE_COST= 0.2）
cost = IO-cost + CPU-cost = (24.78 * 1) + (1999 * 0.2) = 24.78 + 399.8 = 424.58
驗(yàn)證結(jié)果：
explain format = json
select station_no from store_goods_center where station_no = '53';
"cost_info": {"query_cost": "424.58"}
總結(jié)公式：
keys_per_block = 8192 / 索引長(zhǎng)度 + 1
覆蓋索引掃描代價(jià) = (records + keys_per_block - 1) / keys_per_block + 預(yù)估范圍行數(shù) * 0.2
公式簡(jiǎn)化（去除影響較小的復(fù)雜計(jì)算）
覆蓋索引掃描代價(jià) = (records * 涉及索引長(zhǎng)度) / 8192 + 預(yù)估范圍行數(shù) * 0.2
3.ref 索引掃描計(jì)算代價(jià)公式
計(jì)算過(guò)程：
// cardinality = 49（基數(shù)，即有多少個(gè)不同key統(tǒng)計(jì)。）
SHOW TABLE STATUS LIKE 'store_goods_center';
// 頁(yè)數(shù)量
page = 數(shù)據(jù)總大小(字節(jié)) / 頁(yè)大小 = 7880704 / 16384 = 481;
// 計(jì)算代價(jià)最低索引(sql_planner.cc 中find_best_ref函數(shù))
// IO COST最壞不會(huì)超過(guò)全表掃描IO消耗的3倍(或者總記錄數(shù)除以10)
// 其中s->found_records表示表上的記錄數(shù)，s->read_time在innodb層表示page數(shù)
// s-> worst_seeks = min((double) s -> found_records / 10, (double) s -> read_time * 3);
// cur_read_cost= prefix_rowcount * min(table->cost_model() -> page_read_cost(tmp_fanout), tab -> worst_seeks);
// 預(yù)估范圍行數(shù)（總數(shù)據(jù)條數(shù)：10萬(wàn)，預(yù)估數(shù)據(jù)條數(shù)：99827，有一定誤差）
total_records = 99827;
// 預(yù)估范圍行數(shù)（總數(shù)據(jù)條數(shù)：1999，預(yù)估數(shù)據(jù)條數(shù)：1999，有一定誤差） 1999;
records = 1999
// 計(jì)算總代價(jià)
// 1999 * 0.2 中的系數(shù)0.2 代表計(jì)算符合條件的?的代價(jià)（ROW_EVALUATE_COST= 0.2）
// s-> worst_seeks = min((double) s -> found_records / 10, (double) s -> read_time * 3) -> min(99827 / 10, 481 * 3) = 481 * 3
// min(table->cost_model() -> page_read_cost(tmp_fanout), tab -> worst_seeks) -> min(page_read_cost(1999), 481 * 3) = 481 * 3
cost = IO-cost + CPU-cost = 481 * 3 + (1999 * 0.2) = 1443 + 399.8 = 1842.80
驗(yàn)證結(jié)果：
explain format = json
select * from store_goods_center where station_no = '53';
"cost_info": {"query_cost": "1842.80"}
總結(jié)公式：
下面3個(gè)公式，取值最低的
1.(數(shù)據(jù)總大小 / 16384) * 3 + 預(yù)估范圍行數(shù) * 0.2
2.總記錄數(shù) / 10 + 預(yù)估范圍行數(shù) * 0.2
3.掃描出記錄數(shù) + 預(yù)估范圍行數(shù) * 0.2
4.range 索引掃描計(jì)算代價(jià)公式
// 預(yù)估范圍行數(shù)（總數(shù)據(jù)條數(shù)：1299，預(yù)估數(shù)據(jù)條數(shù)：1299，有一定誤差） 1299;
records = 1299
// 計(jì)算代價(jià)最低索引(handler.cc 中 multi_range_read_info_const 函數(shù))
// 計(jì)算總代價(jià)
// 1299 * 0.2 計(jì)算公式：cost_model->row_evaluate_cost(static_cast<double>(total_rows))
// + 0.01 計(jì)算公式：cost->add_cpu(cost_model->row_evaluate_cost(static_cast<double>(total_rows)) + 0.01);
// 1299 + 1 中的 +1 ：?jiǎn)蝹€(gè)掃描區(qū)間（ id > 35018 ）
// 1299 + 1 計(jì)算公式：*cost= read_cost(keyno, static_cast<double>(n_ranges), static_cast<double>(total_rows));
// (1299 * 0.2 + 0.01 + 1299) * 1 中的系數(shù)1 代表從主內(nèi)存緩沖池讀取塊的成本（SE_cost_constants::IO_BLOCK_READ_COST= 1.0）
// 1299 * 0.2 中的系數(shù)0.2 代表計(jì)算符合條件的?的代價(jià)（ROW_EVALUATE_COST= 0.2）
cost = IO-cost + CPU-cost = ((1299 * 0.2 + 0.01 + 1299 + 1) * 1) + (1299 * 0.2) = 1559.81 + 259.8 = 1819.61
驗(yàn)證結(jié)果：
explain format = json
select * from store_goods_center where station_no = '53' and id > 35018;
"cost_info": {"query_cost": "1819.61"}
總結(jié)公式：
range掃描代價(jià) = 預(yù)估范圍行數(shù) * 1.4 + 0.01 + 范圍數(shù)
公式簡(jiǎn)化（去除影響較小的復(fù)雜計(jì)算）
range掃描代價(jià) = 預(yù)估范圍行數(shù) * 1.4
索引沖突案例
門(mén)店商品系統(tǒng)中主要存儲(chǔ)門(mén)店與商品的關(guān)聯(lián)信息，并為 B 端提供根據(jù)門(mén)店 ID 查詢關(guān)聯(lián)商品的功能。由于門(mén)店關(guān)聯(lián)的商品數(shù)據(jù)量較大，需要分頁(yè)查詢關(guān)聯(lián)商品數(shù)據(jù)。為避免深分頁(yè)問(wèn)題，我們選擇基于上次最新主鍵進(jìn)行查詢（核心思想：通過(guò)主鍵索引，每次定位到 ID 所在位置，然后往后遍歷 N 個(gè)數(shù)據(jù)。這樣，無(wú)論數(shù)據(jù)量多少，查詢性能都能保持穩(wěn)定。我們將所有數(shù)據(jù)根據(jù)主鍵 ID 進(jìn)行排序，然后分批次取出，將當(dāng)前批次的最大 ID 作為下次查詢的篩選條件）。
select 字段1，字段2 ... from store_goods_center where station_no = ‘門(mén)店id’ and id > 上次查詢最大id order by id asc
為了確保門(mén)店與商品組合的唯一性，我們?cè)?MySQL 表中為門(mén)店 ID 和商品 ID 添加了組合唯一索引【UNIQUE KEY uniq_storegoods (station_no, sku_id) USING BTREE】。由于該索引包含門(mén)店 ID 并且在聯(lián)合索引的第一個(gè)位置，查詢會(huì)使用該索引。但是，當(dāng)分頁(yè)查詢命中該索引后，由于排序字段無(wú)法使用索引，產(chǎn)生了【Using filesort】，導(dǎo)致門(mén)店商品系統(tǒng)出現(xiàn)了一些慢查詢。為了解決這個(gè)問(wèn)題，我們對(duì)慢查詢進(jìn)行了優(yōu)化，優(yōu)化思路是創(chuàng)建一個(gè)新的索引，使該 SQL 可以使用索引的排序來(lái)規(guī)避【Using filesort】的負(fù)面影響，新添加的索引為【KEY idx_station_no_and_id (station_no, id)】。添加該索引后，效果立竿見(jiàn)影。
然而，我們發(fā)現(xiàn)仍然有慢查詢產(chǎn)生，并且這些慢查詢?nèi)匀皇褂?uniq_storegoods 索引，而不是 idx_station_no_and_id 索引。我們開(kāi)始思考，為什么 MySQL 沒(méi)有為我們的系統(tǒng)推薦使用最優(yōu)的索引？是 MySQL 索引推薦有問(wèn)題，還是我們創(chuàng)建索引有問(wèn)題？如何做才能讓 MySQL 幫我們推薦我們認(rèn)為最優(yōu)的索引？
當(dāng)然，我們也可以使用 FORCE INDEX 強(qiáng)行讓 MySQL 走我們提前預(yù)設(shè)的索引，但是這種方式局限太大，后期索引維護(hù)成本變得很高，甚至可能使用該 SQL 的其他業(yè)務(wù)性能變低。為了突破整體優(yōu)化的卡點(diǎn)狀態(tài)，我們需要了解一下 MySQL 索引推薦底層邏輯，即 MySQL 代價(jià)模型。了解相應(yīng)規(guī)則后，現(xiàn)階段的問(wèn)題將迎刃而解。
案例分析及優(yōu)化
在回顧剛才的問(wèn)題時(shí)，我們發(fā)現(xiàn)問(wèn)題源于原始索引產(chǎn)生了【Using filesort】，從而導(dǎo)致了慢查詢的出現(xiàn)。為了解決這個(gè)問(wèn)題，我們新增了一個(gè)索引，即【KEY idx_station_no_and_id (station_no, id)】，以替代原有的索引【UNIQUE KEY uniq_storegoods (station_no, sku_id)】。然而，盡管新增索引后大部分慢查詢得到了解決，但仍有部分慢查詢未能消除。進(jìn)一步分析發(fā)現(xiàn)，這些慢查詢是由于 SQL 沒(méi)有使用我們期望的索引，而是使用了老索引，從而引發(fā)了【Using filesort】問(wèn)題。在通過(guò) explain 進(jìn)行分析后，我們暫時(shí)還沒(méi)有找到合適的解決方案。
問(wèn)題：盡管我們新增了索引，并且大部分 SQL 已經(jīng)能夠使用新索引進(jìn)行優(yōu)化，但仍存在一些 SQL 沒(méi)有使用新索引。
// 通過(guò)代價(jià)模型進(jìn)行分析
// 使用上面的測(cè)試數(shù)據(jù)進(jìn)行分析
// 新增索引后都沒(méi)有走新索引
// 老索引，掃描行數(shù)：1999，代價(jià)計(jì)算值：1842.80，ref類型索引
// 新索引，掃描行數(shù)：1999，代價(jià)計(jì)算值：1850.46，range類型索引
select 字段1，字段2 ... from store_goods_center where station_no = ‘門(mén)店id’ and id > -1 order by id asc;
// 新增索引后走新索引
// 老索引，掃描行數(shù)：1999，代價(jià)計(jì)算值：1842.80，ref類型索引
// 新索引，掃描行數(shù)：1299，代價(jià)計(jì)算值：1819.61，range類型索引
select 字段1，字段2 ... from store_goods_center where station_no = ‘門(mén)店id’ and id > 35018 order by id asc;
經(jīng)過(guò)分析 MySQL 的代價(jià)模型，我們發(fā)現(xiàn) MySQL 在選擇使用哪個(gè)索引時(shí)，主要取決于掃描出的數(shù)據(jù)條數(shù)。具體來(lái)說(shuō)，掃描出的數(shù)據(jù)條數(shù)越少，MySQL 就越傾向于選擇該索引（由于 MySQL 的索引數(shù)據(jù)訪問(wèn)類型各異，計(jì)算公式也會(huì)有所不同。因此，在多個(gè)索引的掃描行數(shù)相近的情況下，所選索引可能與我們期望的索引有所不同）。順著這個(gè)思路排查，我們發(fā)現(xiàn)當(dāng) id > -1 時(shí)，無(wú)論是使用 storeId + skuId 還是 storeId + id 索引進(jìn)行查詢，掃描出的數(shù)據(jù)條數(shù)是相同的。這是因?yàn)檫@兩種查詢方式都是根據(jù)門(mén)店查詢商品數(shù)據(jù)，且 id 值肯定大于 1。因此，對(duì)于 MySQL 來(lái)說(shuō)，由于這兩種索引掃描出的數(shù)據(jù)條數(shù)相同，所以使用哪種索引效果相差不多。這就是為什么一部分查詢走新索引，而另一部分查詢走老索引的原因。然而，當(dāng)查詢條件為 id > n 時(shí)，storeId + id 索引的優(yōu)勢(shì)便得以顯現(xiàn)。因?yàn)樗軌蛑苯訌乃饕袙呙璨⑻^(guò) id <= n 的數(shù)據(jù)，而 storeId + skuId 索引卻無(wú)法直接跳過(guò)這部分?jǐn)?shù)據(jù)，因此真正掃描的數(shù)據(jù)條數(shù) storeId + skuId 要大于 storeId + id。因此，在查詢條件為 id> n 時(shí)，MySQL 更傾向于使用新索引。（需要注意的是，示例給出的數(shù)據(jù)索引數(shù)據(jù)訪問(wèn)類型不同，一個(gè)是 range 索引類型，一個(gè)是 ref 索引類型。由于算法不同，即使某個(gè)索引的檢索數(shù)據(jù)率略高于另一個(gè)索引，也可能導(dǎo)致系統(tǒng)將其推薦為最優(yōu)索引）
問(wèn)題已經(jīng)分析清楚，主要原因是存在多個(gè)索引，且根據(jù)索引代價(jià)計(jì)算公式的代價(jià)相近，導(dǎo)致難以抉擇。因此，解決這個(gè)問(wèn)題的方法不應(yīng)該是同時(shí)定義兩個(gè)會(huì)讓 MySQL"糾結(jié)" 的索引選擇。相反，應(yīng)該將兩個(gè)索引融合為一個(gè)索引。具體的解決方案是根據(jù)門(mén)店查詢，將原來(lái)的主鍵 id 作為上次查詢的最大 id 替換為 skuId。在算法切換完成后，刪除新的門(mén)店 + 主鍵 id 索引。然而，這種方式可能會(huì)引發(fā)另一個(gè)問(wèn)題。由于底層排序算法發(fā)生了變化（由原來(lái)的主鍵 id 改為 skuId），可能導(dǎo)致無(wú)法直接從底層服務(wù)切換。此時(shí)，應(yīng)考慮從下游使用此接口服務(wù)的應(yīng)用進(jìn)行切換。需要注意的是，如果下游系統(tǒng)是單機(jī)分頁(yè)迭代查詢門(mén)店數(shù)據(jù)，那么下游系統(tǒng)可以直接進(jìn)行切換。但如果這種分頁(yè)查詢動(dòng)作同時(shí)交給多臺(tái)應(yīng)用服務(wù)器執(zhí)行，切換過(guò)程將變得相當(dāng)復(fù)雜，他們的切換成本與底層切換成本相同。但是，這個(gè)系統(tǒng)的對(duì)外服務(wù)屬于這種情況，下游調(diào)用系統(tǒng)會(huì)有多臺(tái)應(yīng)用服務(wù)器協(xié)作分頁(yè)迭代查詢數(shù)據(jù)，為這次優(yōu)化帶來(lái)很大影響。
最終，讓底層獨(dú)立完成切換方式最為合適。在切換過(guò)程中，關(guān)鍵在于正確區(qū)分新老算法。老算法在迭代過(guò)程中不應(yīng)切換至新算法。原系統(tǒng)對(duì)外服務(wù)提供的下次迭代用的 id 可用來(lái)進(jìn)行區(qū)分。新算法在返回下次迭代用的 id 基礎(chǔ)上增加一個(gè)常量值，例如 10 億（加完后不能與原數(shù)據(jù)沖突，也可以將迭代 id 由整數(shù)轉(zhuǎn)換成負(fù)數(shù)以區(qū)分新老算法）。因此，如果是第一次訪問(wèn)，直接使用新算法；如果不是第一次訪問(wèn)，需要根據(jù)下次迭代用的 id 具體規(guī)則來(lái)判斷是否切換新老算法。
總結(jié)與后續(xù)規(guī)劃
使用 Explan 執(zhí)行計(jì)劃存在無(wú)法提前預(yù)知索引選擇的局限性。然而，只要熟悉 MySQL 底層代價(jià)模型的計(jì)算公式，我們就能預(yù)知索引的走向。借助代價(jià)模型，我們不僅可以分析索引沖突的原因，還可以在發(fā)生沖突之前進(jìn)行預(yù)警。甚至在添加索引之前，我們也可以根據(jù)代價(jià)模型公式來(lái)排查潛在問(wèn)題。此外，根據(jù)數(shù)據(jù)業(yè)務(wù)密度，我們還可以預(yù)估當(dāng)前索引的合理性，以及是否可能出現(xiàn)全表掃描等情況。因此，深入研究 MySQL 代價(jià)模型對(duì)于優(yōu)化索引管理具有關(guān)鍵意義。
未來(lái)我們的系統(tǒng)應(yīng)用將結(jié)合 MySQL 代價(jià)模型進(jìn)行集成，實(shí)現(xiàn)自動(dòng)分析數(shù)據(jù)庫(kù)和表的信息，以發(fā)現(xiàn)當(dāng)前索引存在的問(wèn)題，例如索引沖突或未使用索引導(dǎo)致的全表掃描。此外，該工具還可以針對(duì)尚未添加索引的表，根據(jù)數(shù)據(jù)情況提供合適的索引推薦。同時(shí)，該工具還能夠預(yù)測(cè)當(dāng)數(shù)據(jù)達(dá)到某種密度時(shí)，可能出現(xiàn)全表掃描的問(wèn)題，從而幫助提前做好優(yōu)化準(zhǔn)備。
為了實(shí)現(xiàn)這些功能，我們將首先對(duì) MySQL 代價(jià)模型進(jìn)行深入研究，全面了解其計(jì)算公式和原理。這將有助于我們編寫(xiě)相應(yīng)的算法，自動(dòng)分析數(shù)據(jù)庫(kù)和表的信息，找出潛在的索引問(wèn)題。此外，我們還關(guān)注易用性和實(shí)用性，確保用戶能夠輕松地輸入相關(guān)數(shù)據(jù)庫(kù)和表的信息，并獲取有關(guān)優(yōu)化建議。
該工具的開(kāi)發(fā)將有助于提高數(shù)據(jù)庫(kù)性能，減少全表掃描的發(fā)生，降低系統(tǒng)資源消耗。同時(shí)，它還可以為數(shù)據(jù)庫(kù)管理員和開(kāi)發(fā)人員提供便利，使他們能夠更加專注于其他核心業(yè)務(wù)。通過(guò)結(jié)合 MySQL 代價(jià)模型，我們相信這個(gè)工具將在優(yōu)化索引管理方面發(fā)揮重要作用，為企業(yè)帶來(lái)更高的效益。?
參考資料
https://Github.com/mysql/mysql-server
作者：京東零售 王多友
來(lái)源：京東云開(kāi)發(fā)者社區(qū) 轉(zhuǎn)載請(qǐng)注明來(lái)源
