Mysql+Innodb+查询优化实现分析专业版文档格式.docx
- 文档编号:958237
- 上传时间:2023-04-29
- 格式:DOCX
- 页数:35
- 大小:74.69KB
Mysql+Innodb+查询优化实现分析专业版文档格式.docx
《Mysql+Innodb+查询优化实现分析专业版文档格式.docx》由会员分享,可在线阅读,更多相关《Mysql+Innodb+查询优化实现分析专业版文档格式.docx(35页珍藏版)》请在冰点文库上搜索。
分析mysql+innodb如何实现查询优化?
实现查询优化,存储引擎需要做哪些方面的配合?
2测试准备
mysql
selectversion();
5.1.49-debug-log
innodb
--------表定义---------
+-------+------------------------------
|Table|CreateTable
|nkeys|CREATETABLE`nkeys`(
`c1`int(11)NOTNULL,
`c2`int(11)DEFAULTNULL,
`c3`int(11)DEFAULTNULL,
`c4`int(11)DEFAULTNULL,
`c5`int(11)DEFAULTNULL,
PRIMARYKEY(`c1`),
UNIQUEKEY`c2`(`c2`),
UNIQUEKEY`c3`(`c3`),
UNIQUEKEY`c4`(`c4`),
KEY`nkey1`(`c3`,`c5`)
)ENGINE=InnoDBDEFAULTCHARSET=gbk|
----数据----
insertintonkeysvalues(1,1,1,1,1);
insertintonkeysvalues(2,2,2,2,2);
insertintonkeysvalues(3,3,3,3,3);
insertintonkeysvalues(4,4,4,4,4);
insertintonkeysvalues(5,5,5,5,5);
3单表查询
3.1单表range查询
1)select*fromnkeyswherec3>
3;
不能进行索引覆盖扫描indexrangescan
2)selectc3fromnkeyswherec3>
可以进行索引覆盖扫描indexonlyrangescan
调用流程:
msyql_select->
JOIN:
:
optimize->
make_join_statistics->
1.sql_select.cc:
get_quick_record_count->
opt_range.cc:
SQL_SELECT:
test_quick_select–>
ha_innobase:
scan_time->
get_key_scans_params->
check_quick_select–>
Opt_range.cc:
check_quick_keys->
records_in_range->
get_index_only_read_time->
ha_innobase:
read_time->
get_best_ror_intersect->
get_best_covering_ror_intersect->
a)ha_innobase:
scan_time函数,给出全表扫描read_time
i.scan_time=(double)records/TIME_FOR_COMPARE+1;
1.mysql层面,返回一个record需要的时间(CPU时间)
2.TIME_FOR_COMPARE=5
ii.return(double)(prebuilt->
table->
stat_clustered_index_size(聚簇索引叶页面数);
1.innodb层面,全表扫描时间,用读取的page数计算(IO时间)
2.由于innodb是索引组织表,用不到page的预读,因此一次读取一个page
iii.table_read_time=ha_innobase:
scan_time()+scan_time+1;
1.全表扫描总时间=innodb读取数据块时间+mysql比较记录时间+1
2.测试中:
table_read_time=4.3000000000000007
b)check_quick_select函数,判断索引扫描的代价
c)ha_innobase:
records_in_range函数,判断给定range的索引扫描,将返回多少记录
i.给定range的min_key,max_key,根据min_key,max_key构造查询条件,分别进行btr_cur_search_to_nth_level
ii.传入的level是0,search到叶页面
iii.根据返回的两个页面的关系,计算range中的数据量
iv.详细的records_in_range函数实现,请见3.1.1章节。
d)get_index_only_read_time函数,当前scan为indexonlyscan,调用此函数计算read_time
i.cpu_cost=(double)found_records/TIME_FOR_COMPARE;
1.range中的记录数,除以比较时间(CPU时间)
ii.get_index_only_read_time,mysql上层提供函数,用于计算indexonlyscan的代价
1.keys_per_block=(table->
file->
stats.block_size/2)(a)/(table->
key_info[keynr].key_length+table->
ref_length+1)(b)
a)(a)估计索引页面的利用率为1/2
b)(b)索引中,每个索引占用的空间;
keynr为索引的编号,哪个索引?
c)测试中:
keys_per_block=911
2.io_time=(double)(records+keys_per_block-1)/keys_per_block;
a)需要进行多少次index叶页面的IO(indexonlyscan,不需要回表)
b)测试中:
io_time=1.0021953896816684(records=3)
3.index_only_read_time=cpu_cost+io_time+0.01=1.6121953896816683
a)index_only_read_time<
table_read_time
indexonlyscan要好于tablescan,针对第二条语句
c)对于语句
(2),mysql选择索引覆盖扫描
i.索引c1,而非nkey1,虽然nkey1索引也可以做覆盖性扫描,但是nkey1的key_length要大于c1,导致io_time略微大于c1。
e)ha_innobase:
read_time函数,非indexonlyscan时,调用此函数计算read_time
ii.ha_innobase:
read_timeInnodb层面读取的页面,IO时间
1.聚簇索引
a)rows<
=2时returnrows
b)return(ranges+rows)/total_rows*全表扫描IO时间
2.二级索引
a)returnrows2double(ranges+rows)
b)IO时间为ranges个数+range中的行数=二级索引定位代价+回聚簇代价(由于无法进行indexonlyscan)
iii.index_read_time=cpu_cost+io_cost+0.01;
1.测试中:
index_read_time=4.6099999999999994,大于全表扫描时间
f)比较所有e步骤计算出来的index_read_time与a步骤计算出来的table_read_time之间的大小关系,确定当前scan是选择全表扫描,还是索引扫描
i.对于语句
(2),mysql选择索引覆盖扫描
ii.对于语句1),mysql最终选择全表扫描
g)get_best_ror_intersect函数,是一个优化路径。
i.ROR=RowidOrderedRetrievalkeyscan,索引扫描得到的记录,其rowid是排序的,极大降低了回表开销,在此重新评估此索引扫描的代价(ror类似于Oracle中的clusterindexfactor,索引聚簇因子,不过没有Oracle统计的那么详尽)
ii.
2.optimize_keyuse->
a)针对join查询
b)
3.choose_plan->
a)执行计划选择主函数,读取分析用户定义属性
4.greedy_search->
a)从join_tables中逐个选取最优的表,加入当前已选择的pplan
@code
proceduregreedy_search
input:
remaining_tables
output:
pplan;
{
pplan=<
>
;
do{
(t,a)=best_extension(pplan,remaining_tables);
pplan=concat(pplan,(t,a));
remaining_tables=remaining_tables-t;
}while(remaining_tables!
={})
returnpplan;
}
5.best_extension_by_limited_search->
a)从join_tables的remain_tables中选择一个table加入pplan,目标使得整体pplan的开销最小
6.best_access_path->
a)若为单表,计算单表的全表扫描代价。
b)若为多表,计算当前选择表的扫描代价。
7.make_join_readinfo->
pick_table_access_method->
tab->
index=find_shortest_key(table,&
table->
covering_keys)->
read_first_record=join_read_first->
type=JT_NEXT->
a)索引覆盖扫描路径优化。
若当前为全表扫描,同时存在一个或多个可以进行索引覆盖扫描的查询,那么优先选择索引覆盖扫描。
i.原理:
针对Innodb引擎,索引覆盖扫描一定要优于全表扫描
b)对于单表扫描,步骤0确定是否可以选择索引。
步骤5返回全表扫描开销。
步骤6主要处理indexcoveragescan的部分优化。
c)在函数find_shortest_key中,选择合适的索引,forindexcoveragescan。
i.索引必须包含scan键值?
ii.索引列的key_length最小?
iii.
8.
3.1.1records_in_range函数分析
records_in_range->
btr_estimage_n_rows_in_range->
tuple1=minvalueinrangescan,rangescan的范围起始值
btr_cur_search_to_nth_level(index,tuple1,&
cursor)->
tuple2=maxvalueinrangescan,rangescan的范围终止值
btr_cur_search_to_nth_level(index,tuple2,&
根据起始值与终止值,做两次searchpath,确定indexpath,存储在cursor中我们有了起始值与终止值的两个path,起始值与终止值所对应的索引叶节点如何根据两个叶节点计算叶节点范围内的数据量(recordsinrange),想法如下:
1.计算出两个叶节点间,包含多少个索引页,记为n(nleafpagesinrange)
2.计算索引页平均包含多少个索引项,记为r(recordsperleafpage)
3.那么,recordsinrange=n*r
Innodb采用相同的计算方法,innodb计算n,r的算法如下:
计算n,采用自顶向下的方式计算。
(1)根页面只有一个,可以计算出根页面内,两个range间相差多少索引项n1
(2)第二层页面的n2=n1*r2(第二层页面平均记录数)
(3)索引叶节点一层,nn=recordsinrange=nn-1*rn(叶节点评价记录数)
计算r,采用水平采样的方式计算。
r的算法相对简单,但是却会产生IO,其核心思想是,对于索引的每一层,从tuple1确定的路径页面开始,沿着页面中的水平链表,向后遍历页面,遍历结束的条件是:
1)遇到了tuple2确定的页面;
2)或者读取了N_PAGES_READ_LIMIT个页面(10个)。
假设读取了N_PAGES_READ_LIMIT个页面,一共有M个索引记录,那么r=M/N_PAGES_READ_LIMIT.
records_in_range函数总结分析:
●总的来说,records_in_range函数是一个较为费时的函数,两次searchpath开销,索引层数次的计算索引每一层的recordsinrange开销。
如果表数据量较大,索引层数较多,同时查询的range也较大,那么records_in_range函数会产生多次IO,甚至是物理IO,这个是难以接受的。
当然,如果查询的range较小,两次searchpath得到相同的路径,那么recordsinrangepereverylevel的开销可以忽略不计。
●解决records_in_range函数开销的办法之一,就是在Innodb内部收集更为详尽的统计信息,包括uniquekeycounts,以及histogram(柱状图)。
通过统计信息的计算来近似估计records_in_range,从而避免两次searchpath以及recordsinrangepereverylevel的开销。
3.1.2best_access_path函数(单表)
在前面,我们分析到,best_access_path函数针对单表,仅仅计算全表扫描的开销。
其实这个说法不是完全正确。
在部分情况下,best_access_path函数针对单表range查询,也会计算索引开销。
也就是说函数中,s->
keyuse!
=NULL。
那么s->
keyuse参数是何时设置的呢?
通过跟踪两个不同的查询,可以找出设置规律。
sql1:
selectc3fromnkeyswherec3<
=520;
sql2(参考文献[2]):
select*fromTB_IMGOUT_PictureWHERE(2601629>
ID)AND(UserId=129329421)AND(DeleteState=0)ORDERBYIDDESCLIMIT100;
通过测试发现,执行sql1时,s->
keyuse==NULL;
而执行sql2时,s->
s->
keyuse设置流程:
sql_select.cc:
make_join_statistics->
update_ref_and_keys->
add_key_fields->
重点在于函数sql_select.cc:
add_key_fields:
此函数会被递归调用,遍历当前sql中的所有查询条件,为每一个查询条件,寻找合适的索引。
简单来说,也就是设置每个表的s->
keyuse,在best_access_path函数中可以估算这些索引的代价。
那么哪些查询条件,才会使用索引呢?
sql1的查询条件是<
=
sql2的查询条件是>
==
结果是sql2的s->
keyuse被设置。
就我测试跟踪的代码看来,若是等值条件,则设置s->
keyuse,否则不设置。
代码如下:
针对sql1与sql2的第一个查询条件(2601629>
ID),函数add_key_fields判断如下:
caseItem_func:
OPTIMIZE_OP:
boolequal_func=(cond_func->
functype()==Item_func:
EQ_FUNC||
cond_func->
EQUAL_FUNC);
add_key_equal_fields(…,equal_func,…);
add_key_field(…,equal_func,…);
if(!
eq_func)
return;
cond_func->
functype()=LE_FUNC(GT_FUNC),因此equal_func=false,导致add_key_field直接返回,因此s->
keyuse不设置
针对sql2的第二个查询条件(UserId=129329421),函数add_key_fields判断如下:
OPTIMIZE_EQUAL:
add_key_field(…,TRUE,…);
//此处增加possible_keys
//根据field->
keys_in_use_for_query,与当前表上的index做一次比较,
//就得出possible_keys=6=2^1+2^2,1号与2号索引可以用,分别是
//IDX_UID_PHOTOID,IDX_UID_ID。
都是以UserId字段打头的索引。
possible_keys.intersect(field->
keys_in_use_for_query);
stat[0].keys.merge(possible_keys);
if(!
return;
(*key_fields)->
field=field;
eq_func=eq_func;
val=*value;
level=and_level;
}
equal_func直接设置为TRUE,因此UserId对应的索引将会被添加到s->
keyuse中。
为什么单表range查询,在rangequeryoptimization之后,还需要调用best_access_path函数进行二次执行计划的选择?
我的理解是,这是针对等值查询条件的一种二次优化。
mysqlrangequeryoptimization更倾向于选择聚簇索引扫描(二级索引回表代价过大),而对于等值查询,我们可以在best_access_path函数中再次计算使用二级索引的代价。
若此时计算的代价小于rangequeryoptimization选择的执行计划,则替换新的执行计划。
mysqlrangequeryoptimization选择的执行计划,如果不是全表扫描,那么一定要优于全表扫描的代价。
3.1.3单表range查询总结
Ø
每一条执行路径,其cost=CPUcost+IOcost
单表rangescan的执行计划选择,记住几个关键数据的计算即可
⏹tablereadtime,以上步骤0)下面的a)步骤
⏹indexonlyreadtime,以上步骤0)下面的d)步骤
⏹indexreadtime,以上步骤0)下面的e)步骤
为了计算以上关键数据,innodb存储引擎提供了ha_innobase:
records_in_keys方法,该方法根据scan的min_key,max_key进行两次索引上的searchpath(一直读取到叶页面),可能产生IO,然后根据两个叶页面,进行records_in_range的预估。
根据where查询条件,mysql首先会选择哪些索引可能适合做查询,这些潜在的索引,就是explain中的possible_keys一列中所列出来的索引。
而possible_keys的选择,就是由where条件中涉及到的列打头的所有索引。
附录一证实了这个估计。
由于mysql会对每一个possible_keys调用records_in_range进行预估,同时该函数需要两次searchpath,多次索引页面水平扫描,可能产生两次或以上的IO,因此我的建议是,同一个键值打头的索引,越少越好,在mysql中
explain不会真正执行scan,而是只会执行上面的流程,然后将预估出来的各值返回给用户
mysql的查询优化,用的是CBO,而非RBO。
mysql的查询优化,对于uniquescan,会特殊处理,不会这么复杂的路径,因此uniquescan的查询优化效率很高。
根据索引覆盖扫描与全表扫描的理论代价可得,索引覆盖扫描一定要优于全表扫描。
innodb的全表扫描也就是聚簇索引的覆盖扫描,由于聚簇索引包含记录的所有属性,因此其单行记录的长度一定大于二级索引,
⏹二级索引的页面数一
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Mysql Innodb 查询 优化 实现 分析 专业版