背景

在中，当咱们为表创立了一个或多个索引后，通常须要在索引定义成功后，依据详细的数据状况口头EXPLN命令，才干观察到数据库实践经常使用哪个索引、能否经常使用索引。这使得咱们在参与新索引之前，不可提早预知数据库能否能经常使用希冀的索引。更为蹩脚的是，有时甚至在参与新的索引后，数据库在某些查问中会经常使用它，而在其余查问中则不会经常使用，这种状况下，咱们不可确定索引能否施展了预期的作用，让人感到十分苦恼。这种状况基本上象征着MySQL并没有为咱们选择最优的索引，而咱们不得不在茫茫数据中探索，试图找到疑问的症结所在。咱们或许会尝试调整索引，甚至删除索引，而后从新参与，宿愿MySQL能从中找到最优的索引选择。但是，这样的环节既耗时又费劲，而且往往收效甚微。

假设在参与索引之前，咱们能够预知索引的经常使用状况，那么关于表设计将大有裨益。咱们可以在设计表结构时，愈加明白地知道应该选择哪些索引，如何优化索引，以提高查问效率。咱们不再须要依赖自觉尝试和猜想，而是可以基于实践的数据和查问状况，做出愈加理智的决策。因此，关于MySQL用户来说，能够预知索引走势的需求十分迫切。咱们宿愿能有一种方法，能够让咱们在参与索引之前，就清楚地了解MySQL将如何经常使用索引，以便咱们能够更好地优化表结构，提高查问效率。这将极大地减轻咱们的上班累赘，提高咱们的上班效率，让咱们能够愈加专一于业务逻辑的处置，而不是在索引的陆地中挣扎。

为了处置这个疑问，咱们可以深化钻研MySQL的索引选择机制。实践上，这个机制的外围就是代价模型，它经过一个公式来选择索引的选择战略。相关于MySQL其余复杂的概念，代价模型成功起来要便捷得多。相熟代价模型之后，咱们可以预先了解MySQL在口头查问时会如何选择索引，从而更有效地启动索引优化。在接上去的文章中，我将结合近期启动索引优化的详细案例，来详细解释如何运用代价模型来优化索引。

MySQL代价模型浅析

MySQL数据库关键由4层组成：

1.衔接层：客户端和衔接服务，关键成功一些相似于衔接处置、授权治理、以及相关的安保方案。

2.服务层：关键成功大少数的外围服务性能，如SQL接口，并成功缓存的查问，SQL的剖析和优化以及外部函数的口头。

3.引擎层：担任MySQL中数据的存储和提取，主机经过AP1与存储引擎启动通讯。

4.存储层：将数据存储文件系统上，并成功与存储引擎的交互。

索引战略选择在SQL优化器启动的

SQL优化器会剖析一切或许的口头方案，选择老本最低的口头，这种优化器称之为：CBO（Cost-basedOptimizer，基于老本的优化器）。

Cost=ServerCost+EngineCost=CPUCost+IOCost

其中，CPUCost示意计算的开支，比如索引键值的比拟、记载值的比拟、结果集的排序......这些操作都在Server层成功；

IOCost示意引擎层IO的开支，MySQL可以经过区分一张表的数据能否在内存中，区分计算读取内存IO开支以及读取磁盘IO的开支。

源码简读

MySQL的数据源代码驳回了5.7.22版本，后续的代价计算公式将基于此版本启动参考。

opt_costconstants.cc【代价模型——计算所需代价计算系数】

在Server_cost_constants类中定义为静态常质变量的老本常量的值。假设主机治理员没有在server_cost表中参与新值，则将经常使用这些自动老本常数值。

5.7版本开局可用从数据库加载常量值，该版本前经常使用代码中写的常量值

//计算合乎条件的⾏的代价，⾏数越多，此项代价越⼤

constdoubleServer_cost_constants::ROW_EVALUATE_COST=0.2;

//键⽐较的代价，例如排序

constdoubleServer_cost_constants::KEY_COMPARE_COST=0.1;

内存暂时表的创立代价

经过基准测试，创立Memory暂时表的老本与向表中写入10行的老本一样高。

constdoubleServer_cost_constants::MEMORY_TEMPTABLE_CREATE_COST=2.0;

//内存暂时表的⾏代价

constdoubleServer_cost_constants::MEMORY_TEMPTABLE_ROW_COST=0.2;

外部myisam或innodb暂时表的创立代价

创立MyISAM表的速度是创立Memory表的20倍。

constdoubleServer_cost_constants::DISK_TEMPTABLE_CREATE_COST=40.0;

外部myisam或innodb暂时表的⾏代价

当行数大于1000时，按顺序生成MyISAM行比生成Memory行慢2倍。但是，没有十分大的表的基准，因此激进地将此系数设置为慢5倍（即老本为1.0）。

constdoubleServer_cost_constants::DISK_TEMPTABLE_ROW_COST=1.0;

在SE_cost_constants类中定义为静态常质变量的老本常量的值。假设主机治理员没有在engine_cost表中参与新值，则将经常使用这些自动老本常数值。

//从主内存缓冲池读取块的老本

constdoubleSE_cost_constants::MEMORY_BLOCK_READ_COST=1.0;

//从IO设施（磁盘）读取块的老本

constdoubleSE_cost_constants::IO_BLOCK_READ_COST=1.0;

opt_costmodel.cc【代价模型——局部触及方法】

doubleCost_model_table::page_read_cost(doublepages)const

DBUG_ASSERT(m_initialized);

DBUG_ASSERT(pages>=0.0);

//预算汇集索引内存中页面数占其一切页面数的比率

constdoublein_mem=m_table->file->table_in_memory_estimate();

constdoublepages_in_mem=pages*in_mem;

constdoublepages_on_disk=pages-pages_in_mem;

DBUG_ASSERT(pages_on_disk>=0.0);

constdoublecost=buffer_block_read_cost(pages_in_mem)+

io_block_read_cost(pages_on_disk);

returncost;

doubleCost_model_table::page_read_cost_index(uintindex,doublepages)const

DBUG_ASSERT(m_initialized);

DBUG_ASSERT(pages>=0.0);

doublein_mem=m_table->file->index_in_memory_estimate(index);

constdoublepages_in_mem=pages*in_mem;

constdoublepages_on_disk=pages-pages_in_mem;

constdoublecost=buffer_block_read_cost(pages_in_mem)+

io_block_read_cost(pages_on_disk);

returncost;

handler.cc【代价模型——局部触及方法】

//汇集索引扫描IO代价计算公式

Cost_estimatehandler::read_cost(uintindex,doubleranges,doublerows)

DBUG_ASSERT(ranges>=0.0);

DBUG_ASSERT(rows>=0.0);

constdoubleio_cost=read_time(index,static_cast<uint>(ranges),

static_cast<ha_rows>(rows))*

table->cost_model()->page_read_cost(1.0);

Cost_estimatecost;

cost.add_io(io_cost);

returncost;

//表全量扫描代价相关计算（IO-cost）

Cost_estimatehandler::table_scan_cost()

constdoubleio_cost=scan_time()*table->cost_model()->page_read_cost(1.0);

Cost_estimatecost;

cost.add_io(io_cost);

returncost;

//笼罩索引扫描代价相关计算

Cost_estimatehandler::index_scan_cost(uintindex,doubleranges,doublerows)

DBUG_ASSERT(ranges>=0.0);

DBUG_ASSERT(rows>=0.0);

constdoubleio_cost=index_only_read_time(index,rows)*

table->cost_model()->page_read_cost_index(index,1.0);

Cost_estimatecost;

cost.add_io(io_cost);

returncost;

预算在指定keynr索引启动笼罩扫描（不须要回表），扫描records条记载，须要读取的索引页面数

@paramkeynrIndexnumber

@paramrecordsEstimatednumberofrecordstoberetrieved

Estimatedcostof'indexonly'scan

doublehandler::index_only_read_time(uintkeynr,doublerecords)

doubleread_time;

uintkeys_per_block=(stats.block_size/2/

(table_share->key_info[keynr].key_length+ref_length)+

read_time=((double)(records+keys_per_block-1)/

(double)keys_per_block);

returnread_time;

sql_planner.cc【用于ref访问类型索引费用计算】

doubletmp_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];

//Checkifwehavestatisticaboutthedistribution

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;

tmp_fanout=cur_fanout;

rec_per_key_trec_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);

rec_per_key=

rec_per_key_t(tab->records())/distinct_keys_est+1;

if(tab->records()==0)

tmp_fanout=0.0;

elseif(rec_per_key/tab->records()>=0.01)

tmp_fanout=rec_per_key;

constdoublea=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);

tmp_fanout=a;

set_if_bigger(tmp_fanout,1.0);

cur_fanout=(ulong)tmp_fanout;

if(ref_or_null_part)

//Weneedtodotwokeysearchestofindkey

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];

//Limitthenumberofmatchedrows

constdoubletmp_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))

//Wecanuseonlyindextree

constCost_estimateindex_read_cost=

table->file->index_scan_cost(key,1,tmp_fanout);

cur_read_cost=prefix_rowcount*index_read_cost.total_cost();

elseif(key==table->s->primary_key&&

table->file->primary_key_is_clustered())

constCost_estimatetable_read_cost=

table->file->read_cost(key,1,tmp_fanout);

cur_read_cost=prefix_rowcount*table_read_cost.total_cost();

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(uintkeyno,RANGE_SEQ_IF*seq,

void*seq_init_param,uintn_ranges_arg,

uint*bufsz,uint*flags,

Cost_estimate*cost)

KEY_MULTI_RANGErange;

range_seq_tseq_it;

ha_rowsrows,total_rows=0;

uintn_ranges=0;

THD*thd=current_thd;

/*DefaultMRRimplementationdoesn'tneedbuffer*/

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))

returnHA_POS_ERROR;

n_ranges++;

key_range*min_endp,*max_endp;

if(range.range_flag&GEOM_FLAG)

min_endp=&range.start_key;

max_endp=NULL;

min_endp=range.start_key.length?&range.start_key:NULL;

max_endp=range.end_key.length?&range.end_key:NULL;

intkeyparts_used=0;

if((range.range_flag&UNIQUE_RANGE)&&//1)

!(range.range_flag&NULL_RANGE))

rows=1;/*therecanbeatmostonerow*/

elseif((range.range_flag&EQ_RANGE)&&//2a)

(range.range_flag&USE_INDEX_STATISTICS)&&//2b)

(keyparts_used=my_count_bits(range.start_key.keypart_map))&&

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));

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'tscanonerange=>can'tdoMRRscanatall*/

total_rows=HA_POS_ERROR;

total_rows+=rows;

if(total_rows!=HA_POS_ERROR)

constCost_model_table*constcost_model=table->cost_model();

/*Thefollowingcalculationisthesameasinmulti_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));

*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);

returntotal_rows;

验证公式

创立验证须要的表

CREATETABLE`store_goods_center`

`id`bigint(20)NOTNULLAUTO_INCREMENTCOMMENT'主键id',

`sku_id`bigint(20)NOTNULLCOMMENT'商品skuid',

`station_no`varchar(20)NOTNULLCOMMENT'门店编号',

`org_code`bigint(20)NOTNULLCOMMENT'商家编号',

`extend_field`textCOMMENT'裁减字段',

`version`int(11)DEFAULT'0'COMMENT'版本号',

`create_time`datetimeDEFAULTCURRENT_TIMESTAMPCOMMENT'创立期间',

`create_pin`varchar(50)DEFAULT''COMMENT'创立人',

`update_time`datetimeDEFAULTCURRENT_TIMESTAMPCOMMENT'降级期间',

`update_pin`varchar(50)DEFAULT''COMMENT'降级人',

`yn`tinyint(4)DEFAULT'0'COMMENT'删除标示0:反常1：删除',

`ts`timestampNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT'期间戳',

PRIMARYKEY(`id`),

UNIQUEKEY`uniq_storegoods`(`station_no`,`sku_id`)USINGBTREE,

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

DEFAULTCHARSET=utf8mb4COMMENT='门店商品相关表';

经过存储环节初始化测试数据

DELIMITER//

CREATEPROCEDUREcallback()

DECLAREnumINT;

num<=100000DO

INSERTINTOstore_goods_center(sku_id,station_no,org_code)VALUES(num+10000000,floor(50+rand()*(100-50+1)),num);

SETnum=num+1;

口头存储环节生成数据

CALLcallback();

1.全表扫描计算代价公式

计算环节：

//不同引擎计算模式有所区别

//innodb引擎成功handler.h

//预估记载数：ha_innobase::info_low

//页数量：ha_innobase::scan_time【数据总大小(字节)/页大小】

//查问全表数据大小（7880704）

SHOWTABLESTATUSLIKE'store_goods_center';

//查问数据库页大小（自动：16384）

SHOWVARIABLESLIKE'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

验证结果：

explainformat=json

select*fromstore_goods_center;

"cost_info":{"query_cost":"20446.40"}

总结公式：

全表扫描代价=数据总大小/16384+预估范畴行数*0.2

2.笼罩索引扫描计算代价公式

计算环节：

//查问全表数据大小（7880704）

SHOWTABLESTATUSLIKE'store_goods_center';

//查问数据库页大小（自动：16384）

SHOWVARIABLESLIKE'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_idbigint类型，长度为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

验证结果：

explainformat=json

"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统计。）

SHOWTABLESTATUSLIKE'store_goods_center';

//页数量

page=数据总大小(字节)/页大小=7880704/16384=481;

//计算代价最低索引(sql_planner.cc中find_best_ref函数)

//IOCOST最坏不会超越全表扫描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

验证结果：

explainformat=json

select*fromstore_goods_centerwherestation_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

验证结果：

explainformat=json

select*fromstore_goods_centerwherestation_no='53'andid>35018;

"cost_info":{"query_cost":"1819.61"}

总结公式：

range扫描代价=预估范畴行数*1.4+0.01+范畴数

公式简化（去除影响较小的复杂计算）

range扫描代价=预估范畴行数*1.4

索引抵触案例

门店商品系统中关键存储门店与商品的关联消息，并为B端提供依据门店ID查问关联商品的性能。由于门店关联的商品数据量较大，须要分页查问关联商品数据。为防止深分页疑问，咱们选择基于上次最新主键启动查问（外围现实：经过主键索引，每次定位到ID所在位置，而后往后遍历N个数据。这样，无论数据量多少，查问性能都能坚持稳固。咱们将一切数据依据主键ID启动排序，而后分批次取出，将以后批次的最大ID作为下次查问的挑选条件）。

select字段1，字段2...fromstore_goods_centerwherestation_no=‘门店id’andid>上次查问最大idorderbyidasc

为了确保门店与商品组合的惟一性，咱们在MySQL表中为门店ID和商品ID参与了组合惟一索引【UNIQUEKEYuniq_storegoods(station_no,sku_id)USINGBTREE】。由于该索引蕴含门店ID并且在联结索引的第一个位置，查问会经常使用该索引。但是，当分页查问命中该索引后，由于排序字段不可经常使用索引，发生了【Usingfilesort】，造成门店商品系统出现了一些慢查问。为了处置这个疑问，咱们对慢查问启动了优化，优化思绪是创立一个新的索引，使该SQL可以经常使用索引的排序来规避【Usingfilesort】的负面影响，新参与的索引为【KEYidx_station_no_and_id(station_no,id)】。参与该索引后，成果空谷传声。

但是，咱们发现依然有慢查问发生，并且这些慢查问依然经常使用uniq_storegoods索引，而不是idx_station_no_and_id索引。咱们开局思索，为什么MySQL没有为咱们的系统介绍经常使用最优的索引？是MySQL索引介绍有疑问，还是咱们创立索引有疑问？如何做才干让MySQL帮咱们介绍咱们以为最优的索引？

当然，咱们也可以经常使用FORCEINDEX强行让MySQL走咱们提早预设的索引，但是这种模式局限太大，前期索引保养老本变得很高，甚至或许经常使用该SQL的其余业务性能变低。为了打破全体优化的卡点形态，咱们须要了解一下MySQL索引介绍底层逻辑，即MySQL代价模型。了解相应规定后，现阶段的疑问将迎刃而解。

案例剖析及优化

在回忆刚才的疑问时，咱们发现疑问源于原始索引发生了【Usingfilesort】，从而造成了慢查问的出现。为了处置这个疑问，咱们新增了一个索引，即【KEYidx_station_no_and_id(station_no,id)】，以代替原有的索引【UNIQUEKEYuniq_storegoods(station_no,sku_id)】。但是，虽然新增索引后大局部慢查问失掉了处置，但仍有局部慢查问未能消弭。进一步剖析发现，这些慢查问是由于SQL没有经常使用咱们希冀的索引，而是经常使用了老索引，从而引发了【Usingfilesort】疑问。在经过explain启动剖析后，咱们暂时还没有找到适宜的处置方案。

疑问：虽然咱们新增了索引，并且大局部SQL曾经能够经常使用新索引启动优化，但仍存在一些SQL没有经常使用新索引。

//经过代价模型启动剖析

//经常使用上方的测试数据启动剖析

//新增索引后都没有走新索引

//老索引，扫描行数：1999，代价计算值：1842.80，ref类型索引

//新索引，扫描行数：1999，代价计算值：1850.46，range类型索引

select字段1，字段2...fromstore_goods_centerwherestation_no=‘门店id’andid>-1orderbyidasc;

//新增索引后走新索引

//老索引，扫描行数：1999，代价计算值：1842.80，ref类型索引

//新索引，扫描行数：1299，代价计算值：1819.61，range类型索引

select字段1，字段2...fromstore_goods_centerwherestation_no=‘门店id’andid>35018orderbyidasc;

经过剖析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代价模型，咱们置信这个工具将在优化索引治理方面施展关键作用，为企业带来更高的效益。

参考资料

---

什么是索引及MySQL索引原理和慢查询优化

索引目的索引的目的在于提高查询效率，可以类比字典，如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从下往下找到y字母，再找到剩下的sql。 如果没有索引，那么你可能需要把所有单词看一遍才能找到你想要的，如果我想找到m开头的单词呢？或者ze开头的单词呢？是不是觉得如果没有索引，这个事情根本无法完成？索引原理除了词典，生活中随处可见索引的例子，如火车站的车次表、图书的目录等。 它们的原理都是一样的，通过不断的缩小想要获得数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是我们总是通过同一种查找方式来锁定数据。 数据库也是一样，但显然要复杂许多，因为不仅面临着等值查询，还有范围查询(>、<、between、in)、模糊查询(like)、并集查询(or)等等。 数据库应该选择怎么样的方式来应对所有的问题呢？我们回想字典的例子，能不能把数据分成段，然后分段查询呢？最简单的如果1000条数据，1到100分成第一段，101到200分成第二段，201到300分成第三段……这样查第250条数据，只要找第三段就可以了，一下子去除了90%的无效数据。 但如果是1千万的记录呢，分成几段比较好？稍有算法基础的同学会想到搜索树，其平均复杂度是lgN，具有不错的查询性能。 但这里我们忽略了一个关键的问题，复杂度模型是基于每次相同的操作成本来考虑的，数据库实现比较复杂，数据保存在磁盘上，而为了提高性能，每次又可以把部分数据读入内存来计算，因为我们知道访问磁盘的成本大概是访问内存的十万倍左右，所以简单的搜索树难以满足复杂的应用场景。 索引的数据结构前面讲了生活中索引的例子，索引的基本原理，数据库的复杂性，又讲了操作系统的相关知识，目的就是让大家了解，任何一种数据结构都不是凭空产生的，一定会有它的背景和使用场景，我们现在总结一下，我们需要这种数据结构能够做些什么，其实很简单，那就是：每次查找数据时把磁盘IO次数控制在一个很小的数量级，最好是常数数量级。 那么我们就想到如果一个高度可控的多路搜索树是否能满足需求呢？就这样，b+树应运而生。

超详细MySQL数据库优化

数据库优化一方面是找出系统的瓶颈,提高MySQL数据库的整体性能,而另一方面需要合理的结构设计和参数调整,以提高用户的相应速度,同时还要尽可能的节约系统资源,以便让系统提供更大的负荷.

1. 优化一览图

2. 优化

笔者将优化分为了两大类,软优化和硬优化,软优化一般是操作数据库即可,而硬优化则是操作服务器硬件及参数设置.

2.1 软优化

2.1.1 查询语句优化

1.首先我们可以用EXPLAIN或DESCRIBE(简写:DESC)命令分析一条查询语句的执行信息.

2.例:

显示:

其中会显示索引和查询数据读取数据条数等信息.

2.1.2 优化子查询

在MySQL中,尽量使用JOIN来代替子查询.因为子查询需要嵌套查询,嵌套查询时会建立一张临时表,临时表的建立和删除都会有较大的系统开销,而连接查询不会创建临时表,因此效率比嵌套子查询高.

2.1.3 使用索引

索引是提高数据库查询速度最重要的方法之一,关于索引可以参高笔者<MySQL数据库索引>一文,介绍比较详细,此处记录使用索引的三大注意事项:

2.1.4 分解表

对于字段较多的表,如果某些字段使用频率较低,此时应当,将其分离出来从而形成新的表,

2.1.5 中间表

对于将大量连接查询的表可以创建中间表,从而减少在查询时造成的连接耗时.

2.1.6 增加冗余字段

类似于创建中间表,增加冗余也是为了减少连接查询.

2.1.7 分析表,,检查表,优化表

分析表主要是分析表中关键字的分布,检查表主要是检查表中是否存在错误,优化表主要是消除删除或更新造成的表空间浪费.

1. 分析表: 使用 ANALYZE 关键字,如ANALYZE TABLE user;

2. 检查表: 使用 CHECK关键字,如CHECK TABLE user [option]

option 只对MyISAM有效,共五个参数值:

3. 优化表:使用OPTIMIZE关键字,如OPTIMIZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE user;

LOCAL|NO_WRITE_TO_BINLOG都是表示不写入日志.,优化表只对VARCHAR,BLOB和TEXT有效,通过OPTIMIZE TABLE语句可以消除文件碎片,在执行过程中会加上只读锁.

2.2 硬优化

2.2.1 硬件三件套

1.配置多核心和频率高的cpu,多核心可以执行多个线程.

2.配置大内存,提高内存,即可提高缓存区容量,因此能减少磁盘I/O时间,从而提高响应速度.

3.配置高速磁盘或合理分布磁盘:高速磁盘提高I/O,分布磁盘能提高并行操作的能力.

2.2.2 优化数据库参数

优化数据库参数可以提高资源利用率,从而提高MySQL服务器性能服务的配置参数都在或,下面列出性能影响较大的几个参数.

2.2.3 分库分表

因为数据库压力过大，首先一个问题就是高峰期系统性能可能会降低，因为数据库负载过高对性能会有影响。另外一个，压力过大把你的数据库给搞挂了怎么办？所以此时你必须得对系统做分库分表 + 读写分离，也就是把一个库拆分为多个库，部署在多个数据库服务上，这时作为主库承载写入请求。然后每个主库都挂载至少一个从库，由从库来承载读请求。

2.2.4 缓存集群

如果用户量越来越大，此时你可以不停的加机器，比如说系统层面不停加机器，就可以承载更高的并发请求。然后数据库层面如果写入并发越来越高，就扩容加数据库服务器，通过分库分表是可以支持扩容机器的，如果数据库层面的读并发越来越高，就扩容加更多的从库。但是这里有一个很大的问题：数据库其实本身不是用来承载高并发请求的，所以通常来说，数据库单机每秒承载的并发就在几千的数量级，而且数据库使用的机器都是比较高配置，比较昂贵的机器，成本很高。如果你就是简单的不停的加机器，其实是不对的。所以在高并发架构里通常都有缓存这个环节，缓存系统的设计就是为了承载高并发而生。所以单机承载的并发量都在每秒几万，甚至每秒数十万，对高并发的承载能力比数据库系统要高出一到两个数量级。所以你完全可以根据系统的业务特性，对那种写少读多的请求，引入缓存集群。具体来说，就是在写数据库的时候同时写一份数据到缓存集群里，然后用缓存集群来承载大部分的读请求。这样的话，通过缓存集群，就可以用更少的机器资源承载更高的并发。

一个完整而复杂的高并发系统架构中，一定会包含：各种复杂的自研基础架构系统。各种精妙的架构设计.因此一篇小文顶多具有抛砖引玉的效果,但是数据库优化的思想差不多就这些了.