当前位置:首页 > 数码 > EXPLAIN-代价模型-MySQL-浅析-提早预知索引优化战略-优化查问性能-告别自觉经常使用 (explain)

EXPLAIN-代价模型-MySQL-浅析-提早预知索引优化战略-优化查问性能-告别自觉经常使用 (explain)

admin2个月前 (04-15)数码37

背景

在中,当咱们为表创立了一个或多个索引后,通常须要在索引定义成功后,依据详细的数据状况口头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

2.2.1 硬件三件套

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

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

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

2.2.2 优化数据库参数

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

2.2.3 分库分表

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

2.2.4 缓存集群

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

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

免责声明:本文转载或采集自网络,版权归原作者所有。本网站刊发此文旨在传递更多信息,并不代表本网赞同其观点和对其真实性负责。如涉及版权、内容等问题,请联系本网,我们将在第一时间删除。同时,本网站不对所刊发内容的准确性、真实性、完整性、及时性、原创性等进行保证,请读者仅作参考,并请自行核实相关内容。对于因使用或依赖本文内容所产生的任何直接或间接损失,本网站不承担任何责任。

标签: MySQL

“EXPLAIN-代价模型-MySQL-浅析-提早预知索引优化战略-优化查问性能-告别自觉经常使用 (explain)” 的相关文章

实用性极强的-MySQL-查询优化策略 (实用性极强的app)

实用性极强的-MySQL-查询优化策略 (实用性极强的app)

在程序上线运行一段时间后,一旦数据量上去了,或多或少会感觉到系统出现延迟、卡顿等现象,出现这种问题,就需要程序员或架构师进行系统调优工作了。其中,大量的实践经验表明,调优的手段尽管有很多,但涉及到...

Java开发者的MySQL数据库版本管理策略-从规划到部署的全面指南 (java开发工程师)

Java开发者的MySQL数据库版本管理策略-从规划到部署的全面指南 (java开发工程师)

数据库是软件开发中常用的关系型数据库之一。版本管理是保障数据库稳定性和可靠性的重要方面。本文将介绍针对 Java 开发者的 MySQL 数据库版本管理策略,包括版本控制工具选择、数据库脚本管理、变...

MySQL-一探究竟-核心模块揭秘 (mysql-bin文件可以删除吗)

MySQL-一探究竟-核心模块揭秘 (mysql-bin文件可以删除吗)

Undo Segment Caching To improve the efficiency of undo segmentallocation, InnoDB caches some un...

实战-MySQL-数据库压力测试与性能评估方法-Java (实战篮球鞋排名)

实战-MySQL-数据库压力测试与性能评估方法-Java (实战篮球鞋排名)

压力测试的目的和重要性 压力测试是模拟真实环境中并发用户访问数据库的场景,通过增加负载来测试数据库系统的性能表现。压力测试的目的是发现数据库在高负载下的性能瓶颈、资源利用情况和响应时间等指...

主从复制原理简介-MySQL (主从复制原理mysql)

主从复制原理简介-MySQL (主从复制原理mysql)

主从复制(Master-SlaveReplication)是一种数据复制技术,用于在多个数据库主机之间的数据同步。在主从复制架构中,一个主机被设置为主主机(Master),充任数据源,其余主机被设...

全面指南-如何解决-MySQL-主从延时问题 (全面指导)

全面指南-如何解决-MySQL-主从延时问题 (全面指导)

一、什么是主从延时? 主从延时,是指从数据库从主数据库复制数据时产生的时间差。它会导致从库中的数据与主库不一致。 二、为什么会主从延时? 1. 主从复制原理 MySQL的...

如何在MySQL中成功数据的版本治理和回滚操作 (如何在mysql数据库中添加数据)

如何在MySQL中成功数据的版本治理和回滚操作 (如何在mysql数据库中添加数据)

成功数据的版本治理和回滚操作在中可以经过以下几种模式成功,包含经常使用事务、备份恢复、日志和版本控制工具等。上方将详细引见这些方法。 1.经常使用事务: MySQL允许事务操作,可以经...

使用-数据库并自动发送备份文件到指定邮箱-K8s-定期备份-MySQL (使用数据库的命令)

使用-数据库并自动发送备份文件到指定邮箱-K8s-定期备份-MySQL (使用数据库的命令)

简介 本文档描述了一个使用脚本来监控服务器高占用率进程并通过电子邮件发送警报的项目。本文还探讨了使用相同机制备份数据库的可能性。 技术 Python psuti...