淺析MySQL代價模型：告別盲目使用EXPLAIN，提前預知索引優化策略
背景
在 MySQL 中，當我們為表建立了一個或多個索引後，通常需要在索引定義完成後，根據具體的資料情況執行 EXPLAIN 命令，才能觀察到資料庫實際使用哪個索引、是否使用索引。這使得我們在新增新索引之前，無法提前預知資料庫是否能使用期望的索引。更為糟糕的是，有時甚至在新增新的索引後，資料庫在某些查詢中會使用它，而在其他查詢中則不會使用，這種情況下，我們無法確定索引是否發揮了預期的作用，讓人感到非常苦惱。這種情況基本上意味著 MySQL 並沒有為我們選擇最優的索引，而我們不得不在茫茫資料中摸索，試圖找到問題的癥結所在。我們可能會嘗試調整索引，甚至刪除索引，然後重新新增，希望 MySQL 能從中找到最優的索引選擇。然而，這樣的過程既耗時又費力，而且往往收效甚微。
如果在新增索引之前，我們能夠預知索引的使用情況，那麼對於表設計將大有裨益。我們可以在設計表結構時，更加明確地知道應該選擇哪些索引，如何優化索引，以提高查詢效率。我們不再需要依賴盲目嘗試和猜測，而是可以基於實際的資料和查詢情況，做出更加明智的決策。因此，對於 MySQL 使用者來說，能夠預知索引走勢的需求非常迫切。我們希望能有一種方法，能夠讓我們在新增索引之前，就清楚地瞭解 MySQL 將如何使用索引，以便我們能夠更好地優化表結構，提高查詢效率。這將極大地減輕我們的工作負擔，提高我們的工作效率，讓我們能夠更加專注於業務邏輯的處理，而不是在索引的海洋中掙扎。
為了解決這個問題，我們可以深入研究 MySQL 的索引選擇機制。實際上，這個機制的核心就是代價模型，它通過一個公式來決定索引的選擇策略。相對於 MySQL 其他複雜的概念，代價模型實現起來要簡單得多。熟悉代價模型之後，我們可以預先了解 MySQL 在執行查詢時會如何選擇索引，從而更有效地進行索引優化。在接下來的文章中，我將結合近期進行索引優化的具體案例，來詳細解釋如何運用代價模型來優化索引。
MySQL代價模型淺析
MySQL資料庫主要由4層組成：
-
連線層：使用者端和連線服務，主要完成一些類似於連線處理、授權管理、以及相關的安全方案。
-
服務層：主要完成大多數的核心服務功能，如SQL介面，並完成快取的查詢，SQL的分析和優化以及內部函數的執行。
-
引擎層：負責MySQL中資料的儲存和提取，伺服器通過AP1與儲存引擎進行通訊。
-
儲存層：將資料儲存檔案系統上，並完成與儲存引擎的互動。
索引策略選擇在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
作者：京東零售 王多友
來源：京東雲開發者社群 轉載請註明來源