èæ¯
å¨ 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
ä½è ï¼äº¬ä¸é¶å® çå¤å
æ¥æºï¼äº¬ä¸äºå¼åè 社åº