Mysql+Innodb+查询优化实现分析专业版.docx
《Mysql+Innodb+查询优化实现分析专业版.docx》由会员分享,可在线阅读,更多相关《Mysql+Innodb+查询优化实现分析专业版.docx(24页珍藏版)》请在文库网上搜索。
1、Mysql Innodb 查询优化实现分析 以下文字由808影视收集提供于影视网 何登成目录1目的22测试准备23单表查询33.1单表range查询33.1.1records_in_range函数分析53.1.2best_access_path函数(单表)63.1.3单表range查询总结83.2单表unique查询93.2.1单表Unique查询总结94多表查询104.1多表简单join104.2best_access_path函数分析124.2.1总流程分析124.2.2代价估计分析124.2.3best_access_path函数流程134.3optimizer_search_depth
2、参数154.4多表join查询总结155统计信息165.1统计信息收集165.2统计信息更新175.3统计信息收集总结186查询优化总结187参考文献18附录一19附录二20附录三21附录四221 目的分析mysql+innodb如何实现查询优化?实现查询优化,存储引擎需要做哪些方面的配合?2 测试准备mysqlselect version();5.1.49-debug-loginnodb-表定义-+-+-| Table | Create Table+-+-| nkeys | CREATE TABLE nkeys ( c1 int(11) NOT NULL, c2 int(11) DEFAUL
3、T NULL, c3 int(11) DEFAULT NULL, c4 int(11) DEFAULT NULL, c5 int(11) DEFAULT NULL, PRIMARY KEY (c1), UNIQUE KEY c2 (c2), UNIQUE KEY c3 (c3), UNIQUE KEY c4 (c4), KEY nkey1 (c3,c5) ENGINE=InnoDB DEFAULT CHARSET=gbk |+-+-数据-insert into nkeys values (1,1,1,1,1);insert into nkeys values (2,2,2,2,2);inser
4、t into nkeys values (3,3,3,3,3);insert into nkeys values (4,4,4,4,4);insert into nkeys values (5,5,5,5,5);3 单表查询3.1 单表range查询1) select * from nkeys where c3 3;不能进行索引覆盖扫描index range scan2) select c3 from nkeys where c3 3; 可以进行索引覆盖扫描index only range scan调用流程:msyql_select - JOIN:optimize - make_join_st
5、atistics -0. 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_selectOpt_range.cc:check_quick_keys -ha_innobase:records_in_range- get_index_only_read_time - ha_innobase:read_time -get_best_ror_intersect -get_best_
6、covering_ror_intersect -a) ha_innobase:scan_time函数,给出全表扫描read_timei. scan_time = (double) records / TIME_FOR_COMPARE + 1; 1. mysql层面,返回一个record需要的时间(CPU时间)2. TIME_FOR_COMPARE = 5ii. return (double) (prebuilt-table-stat_clustered_index_size(聚簇索引叶页面数);1. innodb层面,全表扫描时间,用读取的page数计算(IO时间)2. 由于innodb是索引
7、组织表,用不到page的预读,因此一次读取一个pageiii. table_read_time = ha_innobase:scan_time() + scan_time + 1;1. 全表扫描总时间 = innodb读取数据块时间 + mysql比较记录时间 + 12. 测试中:table_read_time = 4.3000000000000007b) check_quick_select函数,判断索引扫描的代价c) ha_innobase:records_in_range函数,判断给定range的索引扫描,将返回多少记录i. 给定range的min_key,max_key,根据min_k
8、ey,max_key构造查询条件,分别进行btr_cur_search_to_nth_levelii. 传入的level是0,search到叶页面iii. 根据返回的两个页面的关系,计算range中的数据量iv. 详细的records_in_range函数实现,请见3.1.1章节。d) get_index_only_read_time函数,当前scan为index only scan,调用此函数计算read_timei. cpu_cost = (double) found_records / TIME_FOR_COMPARE;1. range中的记录数,除以比较时间(CPU时间)ii. get
9、_index_only_read_time,mysql上层提供函数,用于计算index only scan的代价1. keys_per_block = (table-file-stats.block_size/2)(a) / (table-key_infokeynr.key_length + table-file-ref_length + 1)(b)a) (a) 估计索引页面的利用率为1/2b) (b) 索引中,每个索引占用的空间;keynr为索引的编号,哪个索引?c) 测试中:keys_per_block = 9112. io_time = (double) (records + keys_
10、per_block - 1) / keys_per_block;a) 需要进行多少次index叶页面的IO (index only scan,不需要回表)b) 测试中:io_time = 1.0021953896816684 (records = 3)3. index_only_read_time = cpu_cost + io_time + 0.01 = 1.6121953896816683a) index_only_read_time table_read_timeb) 测试中:index only scan要好于table scan,针对第二条语句c) 对于语句(2),mysql选择索引
11、覆盖扫描i. 索引c1,而非nkey1,虽然nkey1索引也可以做覆盖性扫描,但是nkey1的key_length要大于c1,导致io_time略微大于c1。e) ha_innobase:read_time函数,非index only scan时,调用此函数计算read_timei. cpu_cost = (double) found_records / TIME_FOR_COMPARE; 1. range中的记录数,除以比较时间(CPU时间)ii. ha_innobase:read_time Innodb层面读取的页面,IO时间1. 聚簇索引a) rows a) 针对join查询b)2. c
12、hoose_plan -a) 执行计划选择主函数,读取分析用户定义属性3. greedy_search -a) 从join_tables中逐个选取最优的表,加入当前已选择的pplancode procedure greedy_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 (
13、remaining_tables != ) return pplan;4. best_extension_by_limited_search -a) 从join_tables的remain_tables中选择一个table加入pplan,目标使得整体pplan的开销最小5. best_access_path -a) 若为单表,计算单表的全表扫描代价。b) 若为多表,计算当前选择表的扫描代价。6. make_join_readinfo - pick_table_access_method - tab-index = find_shortest_key(table, & table-coverin
14、g_keys) - tab-read_first_record = join_read_first - tab-type = JT_NEXT -a) 索引覆盖扫描路径优化。若当前为全表扫描,同时存在一个或多个可以进行索引覆盖扫描的查询,那么优先选择索引覆盖扫描。i. 原理:针对Innodb引擎,索引覆盖扫描一定要优于全表扫描ii.b) 对于单表扫描,步骤0确定是否可以选择索引。步骤5返回全表扫描开销。步骤6主要处理index coverage scan的部分优化。c) 在函数find_shortest_key中,选择合适的索引,for index coverage scan。i. 索引必须包含
15、scan键值?ii. 索引列的key_length最小?iii.7.3.1.1 records_in_range函数分析records_in_range -btr_estimage_n_rows_in_range -tuple1 = min value in range scan,range scan的范围起始值btr_cur_search_to_nth_level(index, tuple1, &cursor) -tuple2 = max value in range scan,range scan的范围终止值btr_cur_search_to_nth_level(index, tuple2
16、, &cursor) -根据起始值与终止值,做两次search path,确定index path,存储在cursor中我们有了起始值与终止值的两个path,起始值与终止值所对应的索引叶节点如何根据两个叶节点计算叶节点范围内的数据量(records in range),想法如下:1. 计算出两个叶节点间,包含多少个索引页,记为n (n leaf pages in range)2. 计算索引页平均包含多少个索引项,记为r (records per leaf page)3. 那么,records in range = n * rInnodb采用相同的计算方法,innodb计算n,r的算法如下:计算
17、n,采用自顶向下的方式计算。(1) 根页面只有一个,可以计算出根页面内,两个range间相差多少索引项n1(2) 第二层页面的n2 = n1 * r2(第二层页面平均记录数)(3) 索引叶节点一层,nn = records in range = nn-1 * rn(叶节点评价记录数)计算r,采用水平采样的方式计算。r的算法相对简单,但是却会产生IO,其核心思想是,对于索引的每一层,从tuple1确定的路径页面开始,沿着页面中的水平链表,向后遍历页面,遍历结束的条件是:1) 遇到了tuple2确定的页面; 2) 或者读取了N_PAGES_READ_LIMIT个页面(10个)。假设读取了N_PAG
18、ES_READ_LIMIT个页面,一共有M个索引记录,那么 r = M / N_PAGES_READ_LIMIT.records_in_range函数总结分析:l 总的来说,records_in_range函数是一个较为费时的函数,两次search path开销,索引层数次的计算索引每一层的records in range开销。如果表数据量较大,索引层数较多,同时查询的range也较大,那么records_in_range函数会产生多次IO,甚至是物理IO,这个是难以接受的。当然,如果查询的range较小,两次search path得到相同的路径,那么records in range pere
19、very level的开销可以忽略不计。l 解决records_in_range函数开销的办法之一,就是在Innodb内部收集更为详尽的统计信息,包括unique key counts,以及histogram(柱状图)。通过统计信息的计算来近似估计records_in_range,从而避免两次search path以及records in range per every level的开销。3.1.2 best_access_path函数(单表)在前面,我们分析到,best_access_path函数针对单表,仅仅计算全表扫描的开销。其实这个说法不是完全正确。在部分情况下,best_access
20、_path函数针对单表range查询,也会计算索引开销。也就是说函数中,s-keyuse != NULL。那么s-keyuse参数是何时设置的呢?通过跟踪两个不同的查询,可以找出设置规律。sql 1:select c3 from nkeys where c3 ID) AND (UserId = 129329421) AND (DeleteState = 0) ORDER BY ID DESC LIMIT 100;通过测试发现,执行sql 1时,s-keyuse = NULL;而执行sql2时,s-keyuse != NULL。s-keyuse设置流程:sql_select.cc:make_jo
21、in_statistics -update_ref_and_keys -sql_select.cc:add_key_fields -重点在于函数sql_select.cc:add_key_fields:此函数会被递归调用,遍历当前sql中的所有查询条件,为每一个查询条件,寻找合适的索引。简单来说,也就是设置每个表的s-keyuse,在best_access_path函数中可以估算这些索引的代价。那么哪些查询条件,才会使用索引呢?sql 1的查询条件是 = =结果是sql 2的s-keyuse被设置。就我测试跟踪的代码看来,若是等值条件,则设置s-keyuse,否则不设置。代码如下:针对sql
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Mysql Innodb 查询 优化 实现 分析 专业版