背景
在 MySQL 中，當我們為表創建了一個或多個索引后，通常需要在索引定義完成后，根據具體的數據情況執行 EXPLAIN 命令，才能觀察到數據庫實際使用哪個索引、是否使用索引。這使得我們在添加新索引之前，無法提前預知數據庫是否能使用期望的索引。更為糟糕的是，有時甚至在添加新的索引后，數據庫在某些查詢中會使用它，而在其他查詢中則不會使用，這種情況下，我們無法確定索引是否發揮了預期的作用，讓人感到非常苦惱。這種情況基本上意味著 MySQL 并沒有為我們選擇最優的索引，而我們不得不在茫茫數據中摸索，試圖找到問題的癥結所在。我們可能會嘗試調整索引，甚至刪除索引，然后重新添加，希望 MySQL 能從中找到最優的索引選擇。然而，這樣的過程既耗時又費力，而且往往收效甚微。
如果在添加索引之前，我們能夠預知索引的使用情況，那么對于表設計將大有裨益。我們可以在設計表結構時，更加明確地知道應該選擇哪些索引，如何優化索引，以提高查詢效率。我們不再需要依賴盲目嘗試和猜測，而是可以基于實際的數據和查詢情況，做出更加明智的決策。因此，對于 MySQL 用戶來說，能夠預知索引走勢的需求非常迫切。我們希望能有一種方法，能夠讓我們在添加索引之前，就清楚地了解 MySQL 將如何使用索引，以便我們能夠更好地優化表結構，提高查詢效率。這將極大地減輕我們的工作負擔，提高我們的工作效率，讓我們能夠更加專注于業務邏輯的處理，而不是在索引的海洋中掙扎。
為了解決這個問題，我們可以深入研究 MySQL 的索引選擇機制。實際上，這個機制的核心就是代價模型，它通過一個公式來決定索引的選擇策略。相對于 MySQL 其他復雜的概念，代價模型實現起來要簡單得多。熟悉代價模型之后，我們可以預先了解 MySQL 在執行查詢時會如何選擇索引，從而更有效地進行索引優化。在接下來的文章中，我將結合近期進行索引優化的具體案例，來詳細解釋如何運用代價模型來優化索引。
MySQL 代價模型淺析
?MySQL 數據庫主要由 4 層組成：
1. 連接層：客戶端和連接服務，主要完成一些類似于連接處理、授權管理、以及相關的安全方案。
2. 服務層：主要完成大多數的核心服務功能，如 SQL 接口，并完成緩存的查詢，SQL 的分析和優化以及內部函數的執行。
3. 引擎層：負責 MySQL 中數據的存儲和提取，服務器通過 AP1 與存儲引擎進行通信。
4. 存儲層：將數據存儲文件系統上，并完成與存儲引擎的交互。
索引策略選擇在 SQL 優化器進行的
SQL 優化器會分析所有可能的執行計劃，選擇成本最低的執行，這種優化器稱之為：CBO（Cost-based Optimizer，基于成本的優化器）。
Cost = Server Cost + Engine Cost = CPU Cost + IO Cost
其中，CPU Cost 表示計算的開銷，比如索引鍵值的比較、記錄值的比較、結果集的排序 ...... 這些操作都在 Server 層完成；
IO Cost 表示引擎層 IO 的開銷，MySQL 可以通過區分一張表的數據是否在內存中，分別計算讀取內存 IO 開銷以及讀取磁盤 IO 的開銷。
源碼簡讀
MySQL 的數據源代碼采用了 5.7.22 版本，后續的代價計算公式將基于此版本進行參考。
opt_costconstants.cc【代價模型 —— 計算所需代價計算系數】
/*
在Server_cost_constants類中定義為靜態常量變量的成本常量的值。如果服務器管理員沒有在server_cost表中添加新值，則將使用這些默認成本常數值。
5.7版本開始可用從數據庫加載常量值，該版本前使用代碼中寫的常量值
*/
// 計算符合條件的?的代價，?數越多，此項代價越?
const double Server_cost_constants::ROW_EVALUATE_COST= 0.2;
// 鍵?較的代價，例如排序
const double Server_cost_constants::KEY_COMPARE_COST= 0.1;
/*
內存臨時表的創建代價
通過基準測試，創建Memory臨時表的成本與向表中寫入10行的成本一樣高。
*/
const double Server_cost_constants::MEMORY_TEMPTABLE_CREATE_COST= 2.0;
// 內存臨時表的?代價
const double Server_cost_constants::MEMORY_TEMPTABLE_ROW_COST= 0.2;
/*
內部myisam或innodb臨時表的創建代價
創建MyISAM表的速度是創建Memory表的20倍。
*/
const double Server_cost_constants::DISK_TEMPTABLE_CREATE_COST= 40.0;
/*
內部myisam或innodb臨時表的?代價
當行數大于1000時，按順序生成MyISAM行比生成Memory行慢2倍。然而，沒有非常大的表的基準，因此保守地將此系數設置為慢5倍（即成本為1.0）。
*/
const double Server_cost_constants::DISK_TEMPTABLE_ROW_COST= 1.0;
/*
在SE_cost_constants類中定義為靜態常量變量的成本常量的值。如果服務器管理員沒有在engine_cost表中添加新值，則將使用這些默認成本常數值。
*/
// 從主內存緩沖池讀取塊的成本
const double SE_cost_constants::MEMORY_BLOCK_READ_COST= 1.0;
// 從IO設備（磁盤）讀取塊的成本
const double SE_cost_constants::IO_BLOCK_READ_COST= 1.0;
?opt_costmodel.cc【代價模型 —— 部分涉及方法】
double Cost_model_table::page_read_cost(double pages) const
{
DBUG_ASSERT(m_initialized);
DBUG_ASSERT(pages >= 0.0);
// 估算聚集索引內存中頁面數占其所有頁面數的比率
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【代價模型 —— 部分涉及方法】
// 聚集索引掃描IO代價計算公式
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;
}
// 表全量掃描代價相關計算（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;
}
// 覆蓋索引掃描代價相關計算
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索引進行覆蓋掃描（不需要回表），掃描 records條記錄，需要讀取的索引頁面數
@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 訪問類型索引費用計算】
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 訪問類型索引費用計算】
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;
}
驗證公式
創建驗證需要的表
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 '門店編號',
`org_code` bigint(20) NOT NULL COMMENT '商家編號',
`extend_field` text COMMENT '擴展字段',
`version` int(11) DEFAULT '0' COMMENT '版本號',
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間',
`create_pin` varchar(50) DEFAULT '' COMMENT '創建人',
`update_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '更新時間',
`update_pin` varchar(50) DEFAULT '' COMMENT '更新人',
`yn` tinyint(4) DEFAULT '0' COMMENT '刪除標示 0:正常 1：刪除',
`ts` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '時間戳',
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 ='門店商品關系表';
通過存儲過程初始化測試數據
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;
執行存儲過程生成數據
CALL callback();
?1. 全表掃描計算代價公式
計算過程：
// 不同引擎計算方式有所區別
// innodb引擎實現handler.h
// 預估記錄數：ha_innobase::info_low
// 頁數量：ha_innobase::scan_time【數據總大小(字節) / 頁大小】
// 查詢全表數據大小（7880704）
SHOW TABLE STATUS LIKE 'store_goods_center';
// 查詢數據庫頁大小（默認：16384）
SHOW VARIABLES LIKE 'innodb_page_size';
// 全表掃描計算代價
// 頁數量
page = 數據總大小(字節) / 頁大小 = 7880704 / 16384 = 481;
// 預估范圍行數（總數據條數：10萬，預估數據條數：99827，有一定誤差）
records = 99827;
// 計算總代價
// 481 * 1 中的系數1 代表從主內存緩沖池讀取塊的成本（SE_cost_constants::IO_BLOCK_READ_COST= 1.0）
// 99827 * 0.2 中的系數0.2 代表計算符合條件的?的代價（ROW_EVALUATE_COST= 0.2）
cost = IO-cost + CPU-cost = (481 * 1) + (99827 * 0.2) = 481 + 19965.4 = 20446.4
?驗證結果：
explain format = json
select * from store_goods_center;
"cost_info": {"query_cost": "20446.40"}
?總結公式：
全表掃描代價 = 數據總大小 / 16384 + 預估范圍行數 * 0.2
2. 覆蓋索引掃描計算代價公式
計算過程：
// 查詢全表數據大小（7880704）
SHOW TABLE STATUS LIKE 'store_goods_center';
// 查詢數據庫頁大小（默認：16384）
SHOW VARIABLES LIKE 'innodb_page_size';
// 預估范圍行數（總數據條數：1999，預估數據條數：1999，有一定誤差） 1999;
records = 1999
// keys_per_block計算
// block_size是文件的block大小，mysql默認為16K；
// key_len是索引的鍵長度；
// ref_len是主鍵索引的長度；
keys_per_block = (stats.block_size / 2 / (table_share->key_info[keynr].key_length + ref_length) + 1);
// table_share->key_info[keynr].key_length 為聯合索引，分別是station_no和sku_id
// station_no 為varchar(20)且為utf8mb4，長度 = 20 * 4 + 2 (可變長度需要加2) = 82
// sku_id bigint類型，長度為8
// 主鍵索引為bigint類型，長度為8
keys_per_block = 16384 / 2 / (82 + 8 + 8) + 1 ≈ 84
// 計算總代價
read_time = ((double) (records + keys_per_block - 1) / (double) keys_per_block);
read_time = (1999 + 84 - 1) / 84 = 24.78;
// 計算總代價
// 24.78 * 1 中的系數1 代表從主內存緩沖池讀取塊的成本（SE_cost_constants::IO_BLOCK_READ_COST= 1.0）
// 1999 * 0.2 中的系數0.2 代表計算符合條件的?的代價（ROW_EVALUATE_COST= 0.2）
cost = IO-cost + CPU-cost = (24.78 * 1) + (1999 * 0.2) = 24.78 + 399.8 = 424.58
驗證結果：
explain format = json
select station_no from store_goods_center where station_no = '53';
"cost_info": {"query_cost": "424.58"}
總結公式：
keys_per_block = 8192 / 索引長度 + 1
覆蓋索引掃描代價 = (records + keys_per_block - 1) / keys_per_block + 預估范圍行數 * 0.2
公式簡化（去除影響較小的復雜計算）
覆蓋索引掃描代價 = (records * 涉及索引長度) / 8192 + 預估范圍行數 * 0.2
3.ref 索引掃描計算代價公式
計算過程：
// cardinality = 49（基數，即有多少個不同key統計。）
SHOW TABLE STATUS LIKE 'store_goods_center';
// 頁數量
page = 數據總大小(字節) / 頁大小 = 7880704 / 16384 = 481;
// 計算代價最低索引(sql_planner.cc 中find_best_ref函數)
// IO COST最壞不會超過全表掃描IO消耗的3倍(或者總記錄數除以10)
// 其中s->found_records表示表上的記錄數，s->read_time在innodb層表示page數
// 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);
// 預估范圍行數（總數據條數：10萬，預估數據條數：99827，有一定誤差）
total_records = 99827;
// 預估范圍行數（總數據條數：1999，預估數據條數：1999，有一定誤差） 1999;
records = 1999
// 計算總代價
// 1999 * 0.2 中的系數0.2 代表計算符合條件的?的代價（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
驗證結果：
explain format = json
select * from store_goods_center where station_no = '53';
"cost_info": {"query_cost": "1842.80"}
總結公式：
下面3個公式，取值最低的
1.(數據總大小 / 16384) * 3 + 預估范圍行數 * 0.2
2.總記錄數 / 10 + 預估范圍行數 * 0.2
3.掃描出記錄數 + 預估范圍行數 * 0.2
4.range 索引掃描計算代價公式
// 預估范圍行數（總數據條數：1299，預估數據條數：1299，有一定誤差） 1299;
records = 1299
// 計算代價最低索引(handler.cc 中 multi_range_read_info_const 函數)
// 計算總代價
// 1299 * 0.2 計算公式：cost_model->row_evaluate_cost(static_cast<double>(total_rows))
// + 0.01 計算公式：cost->add_cpu(cost_model->row_evaluate_cost(static_cast<double>(total_rows)) + 0.01);
// 1299 + 1 中的 +1 ：單個掃描區間（ id > 35018 ）
// 1299 + 1 計算公式：*cost= read_cost(keyno, static_cast<double>(n_ranges), static_cast<double>(total_rows));
// (1299 * 0.2 + 0.01 + 1299) * 1 中的系數1 代表從主內存緩沖池讀取塊的成本（SE_cost_constants::IO_BLOCK_READ_COST= 1.0）
// 1299 * 0.2 中的系數0.2 代表計算符合條件的?的代價（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
驗證結果：
explain format = json
select * from store_goods_center where station_no = '53' and id > 35018;
"cost_info": {"query_cost": "1819.61"}
總結公式：
range掃描代價 = 預估范圍行數 * 1.4 + 0.01 + 范圍數
公式簡化（去除影響較小的復雜計算）
range掃描代價 = 預估范圍行數 * 1.4
索引沖突案例
門店商品系統中主要存儲門店與商品的關聯信息，并為 B 端提供根據門店 ID 查詢關聯商品的功能。由于門店關聯的商品數據量較大，需要分頁查詢關聯商品數據。為避免深分頁問題，我們選擇基于上次最新主鍵進行查詢（核心思想：通過主鍵索引，每次定位到 ID 所在位置，然后往后遍歷 N 個數據。這樣，無論數據量多少，查詢性能都能保持穩定。我們將所有數據根據主鍵 ID 進行排序，然后分批次取出，將當前批次的最大 ID 作為下次查詢的篩選條件）。
select 字段1，字段2 ... from store_goods_center where station_no = ‘門店id’ and id > 上次查詢最大id order by id asc
為了確保門店與商品組合的唯一性，我們在 MySQL 表中為門店 ID 和商品 ID 添加了組合唯一索引【UNIQUE KEY uniq_storegoods (station_no, sku_id) USING BTREE】。由于該索引包含門店 ID 并且在聯合索引的第一個位置，查詢會使用該索引。但是，當分頁查詢命中該索引后，由于排序字段無法使用索引，產生了【Using filesort】，導致門店商品系統出現了一些慢查詢。為了解決這個問題，我們對慢查詢進行了優化，優化思路是創建一個新的索引，使該 SQL 可以使用索引的排序來規避【Using filesort】的負面影響，新添加的索引為【KEY idx_station_no_and_id (station_no, id)】。添加該索引后，效果立竿見影。
然而，我們發現仍然有慢查詢產生，并且這些慢查詢仍然使用 uniq_storegoods 索引，而不是 idx_station_no_and_id 索引。我們開始思考，為什么 MySQL 沒有為我們的系統推薦使用最優的索引？是 MySQL 索引推薦有問題，還是我們創建索引有問題？如何做才能讓 MySQL 幫我們推薦我們認為最優的索引？
當然，我們也可以使用 FORCE INDEX 強行讓 MySQL 走我們提前預設的索引，但是這種方式局限太大，后期索引維護成本變得很高，甚至可能使用該 SQL 的其他業務性能變低。為了突破整體優化的卡點狀態，我們需要了解一下 MySQL 索引推薦底層邏輯，即 MySQL 代價模型。了解相應規則后，現階段的問題將迎刃而解。
案例分析及優化
在回顧剛才的問題時，我們發現問題源于原始索引產生了【Using filesort】，從而導致了慢查詢的出現。為了解決這個問題，我們新增了一個索引，即【KEY idx_station_no_and_id (station_no, id)】，以替代原有的索引【UNIQUE KEY uniq_storegoods (station_no, sku_id)】。然而，盡管新增索引后大部分慢查詢得到了解決，但仍有部分慢查詢未能消除。進一步分析發現，這些慢查詢是由于 SQL 沒有使用我們期望的索引，而是使用了老索引，從而引發了【Using filesort】問題。在通過 explain 進行分析后，我們暫時還沒有找到合適的解決方案。
問題：盡管我們新增了索引，并且大部分 SQL 已經能夠使用新索引進行優化，但仍存在一些 SQL 沒有使用新索引。
// 通過代價模型進行分析
// 使用上面的測試數據進行分析
// 新增索引后都沒有走新索引
// 老索引，掃描行數：1999，代價計算值：1842.80，ref類型索引
// 新索引，掃描行數：1999，代價計算值：1850.46，range類型索引
select 字段1，字段2 ... from store_goods_center where station_no = ‘門店id’ and id > -1 order by id asc;
// 新增索引后走新索引
// 老索引，掃描行數：1999，代價計算值：1842.80，ref類型索引
// 新索引，掃描行數：1299，代價計算值：1819.61，range類型索引
select 字段1，字段2 ... from store_goods_center where station_no = ‘門店id’ and id > 35018 order by id asc;
經過分析 MySQL 的代價模型，我們發現 MySQL 在選擇使用哪個索引時，主要取決于掃描出的數據條數。具體來說，掃描出的數據條數越少，MySQL 就越傾向于選擇該索引（由于 MySQL 的索引數據訪問類型各異，計算公式也會有所不同。因此，在多個索引的掃描行數相近的情況下，所選索引可能與我們期望的索引有所不同）。順著這個思路排查，我們發現當 id > -1 時，無論是使用 storeId + skuId 還是 storeId + id 索引進行查詢，掃描出的數據條數是相同的。這是因為這兩種查詢方式都是根據門店查詢商品數據，且 id 值肯定大于 1。因此，對于 MySQL 來說，由于這兩種索引掃描出的數據條數相同，所以使用哪種索引效果相差不多。這就是為什么一部分查詢走新索引，而另一部分查詢走老索引的原因。然而，當查詢條件為 id > n 時，storeId + id 索引的優勢便得以顯現。因為它能夠直接從索引中掃描并跳過 id <= n 的數據，而 storeId + skuId 索引卻無法直接跳過這部分數據，因此真正掃描的數據條數 storeId + skuId 要大于 storeId + id。因此，在查詢條件為 id> n 時，MySQL 更傾向于使用新索引。（需要注意的是，示例給出的數據索引數據訪問類型不同，一個是 range 索引類型，一個是 ref 索引類型。由于算法不同，即使某個索引的檢索數據率略高于另一個索引，也可能導致系統將其推薦為最優索引）
問題已經分析清楚，主要原因是存在多個索引，且根據索引代價計算公式的代價相近，導致難以抉擇。因此，解決這個問題的方法不應該是同時定義兩個會讓 MySQL"糾結" 的索引選擇。相反，應該將兩個索引融合為一個索引。具體的解決方案是根據門店查詢，將原來的主鍵 id 作為上次查詢的最大 id 替換為 skuId。在算法切換完成后，刪除新的門店 + 主鍵 id 索引。然而，這種方式可能會引發另一個問題。由于底層排序算法發生了變化（由原來的主鍵 id 改為 skuId），可能導致無法直接從底層服務切換。此時，應考慮從下游使用此接口服務的應用進行切換。需要注意的是，如果下游系統是單機分頁迭代查詢門店數據，那么下游系統可以直接進行切換。但如果這種分頁查詢動作同時交給多臺應用服務器執行，切換過程將變得相當復雜，他們的切換成本與底層切換成本相同。但是，這個系統的對外服務屬于這種情況，下游調用系統會有多臺應用服務器協作分頁迭代查詢數據，為這次優化帶來很大影響。
最終，讓底層獨立完成切換方式最為合適。在切換過程中，關鍵在于正確區分新老算法。老算法在迭代過程中不應切換至新算法。原系統對外服務提供的下次迭代用的 id 可用來進行區分。新算法在返回下次迭代用的 id 基礎上增加一個常量值，例如 10 億（加完后不能與原數據沖突，也可以將迭代 id 由整數轉換成負數以區分新老算法）。因此，如果是第一次訪問，直接使用新算法；如果不是第一次訪問，需要根據下次迭代用的 id 具體規則來判斷是否切換新老算法。
總結與后續規劃
使用 Explan 執行計劃存在無法提前預知索引選擇的局限性。然而，只要熟悉 MySQL 底層代價模型的計算公式，我們就能預知索引的走向。借助代價模型，我們不僅可以分析索引沖突的原因，還可以在發生沖突之前進行預警。甚至在添加索引之前，我們也可以根據代價模型公式來排查潛在問題。此外，根據數據業務密度，我們還可以預估當前索引的合理性，以及是否可能出現全表掃描等情況。因此，深入研究 MySQL 代價模型對于優化索引管理具有關鍵意義。
未來我們的系統應用將結合 MySQL 代價模型進行集成，實現自動分析數據庫和表的信息，以發現當前索引存在的問題，例如索引沖突或未使用索引導致的全表掃描。此外，該工具還可以針對尚未添加索引的表，根據數據情況提供合適的索引推薦。同時，該工具還能夠預測當數據達到某種密度時，可能出現全表掃描的問題，從而幫助提前做好優化準備。
為了實現這些功能，我們將首先對 MySQL 代價模型進行深入研究，全面了解其計算公式和原理。這將有助于我們編寫相應的算法，自動分析數據庫和表的信息，找出潛在的索引問題。此外，我們還關注易用性和實用性，確保用戶能夠輕松地輸入相關數據庫和表的信息，并獲取有關優化建議。
該工具的開發將有助于提高數據庫性能，減少全表掃描的發生，降低系統資源消耗。同時，它還可以為數據庫管理員和開發人員提供便利，使他們能夠更加專注于其他核心業務。通過結合 MySQL 代價模型，我們相信這個工具將在優化索引管理方面發揮重要作用，為企業帶來更高的效益。?
參考資料
https://Github.com/mysql/mysql-server
作者：京東零售 王多友
來源：京東云開發者社區 轉載請注明來源
