EXPLAIN-代价模型-MySQL-浅析-提早预知索引优化战略-优化查问性能-告别自觉经常使用 (explain)
背景
在中,当咱们为表创立了一个或多个索引后,通常须要在索引定义成功后,依据详细的数据状况口头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 硬优化
![explain](https://tyye.cn/zb_users/theme/tpure/style/images/lazyload.png)
2.2.1 硬件三件套
1.配置多核心和频率高的cpu,多核心可以执行多个线程.
2.配置大内存,提高内存,即可提高缓存区容量,因此能减少磁盘I/O时间,从而提高响应速度.
3.配置高速磁盘或合理分布磁盘:高速磁盘提高I/O,分布磁盘能提高并行操作的能力.
2.2.2 优化数据库参数
优化数据库参数可以提高资源利用率,从而提高MySQL服务器性能服务的配置参数都在或,下面列出性能影响较大的几个参数.
2.2.3 分库分表
因为数据库压力过大,首先一个问题就是高峰期系统性能可能会降低,因为数据库负载过高对性能会有影响。另外一个,压力过大把你的数据库给搞挂了怎么办?所以此时你必须得对系统做分库分表 + 读写分离,也就是把一个库拆分为多个库,部署在多个数据库服务上,这时作为主库承载写入请求。然后每个主库都挂载至少一个从库,由从库来承载读请求。
2.2.4 缓存集群
如果用户量越来越大,此时你可以不停的加机器,比如说系统层面不停加机器,就可以承载更高的并发请求。然后数据库层面如果写入并发越来越高,就扩容加数据库服务器,通过分库分表是可以支持扩容机器的,如果数据库层面的读并发越来越高,就扩容加更多的从库。但是这里有一个很大的问题:数据库其实本身不是用来承载高并发请求的,所以通常来说,数据库单机每秒承载的并发就在几千的数量级,而且数据库使用的机器都是比较高配置,比较昂贵的机器,成本很高。如果你就是简单的不停的加机器,其实是不对的。所以在高并发架构里通常都有缓存这个环节,缓存系统的设计就是为了承载高并发而生。所以单机承载的并发量都在每秒几万,甚至每秒数十万,对高并发的承载能力比数据库系统要高出一到两个数量级。所以你完全可以根据系统的业务特性,对那种写少读多的请求,引入缓存集群。具体来说,就是在写数据库的时候同时写一份数据到缓存集群里,然后用缓存集群来承载大部分的读请求。这样的话,通过缓存集群,就可以用更少的机器资源承载更高的并发。
一个完整而复杂的高并发系统架构中,一定会包含:各种复杂的自研基础架构系统。各种精妙的架构设计.因此一篇小文顶多具有抛砖引玉的效果,但是数据库优化的思想差不多就这些了.
免责声明:本文转载或采集自网络,版权归原作者所有。本网站刊发此文旨在传递更多信息,并不代表本网赞同其观点和对其真实性负责。如涉及版权、内容等问题,请联系本网,我们将在第一时间删除。同时,本网站不对所刊发内容的准确性、真实性、完整性、及时性、原创性等进行保证,请读者仅作参考,并请自行核实相关内容。对于因使用或依赖本文内容所产生的任何直接或间接损失,本网站不承担任何责任。