欢迎来到天天文库
浏览记录
ID:41061063
大小:1.52 MB
页数:50页
时间:2019-08-15
《Mysql5.6新特性介绍》由会员上传分享,免费在线阅读,更多相关内容在教育资源-天天文库。
1、网易DBA王洪权mydbalife@gmail.com2012-06-06Mysql5.6新特性介绍主要内容mysql5.6查询优化InnoDBperformancemysqlreplicationIndexConditionPushdownMySQL5.6+,MariaDB5.3+Mysql5.6新特性--indexconditionpushdown为什么需要indexconditionpushdownAnon-indexonlyreadisatwo-stepprocess:1.读索引2.读记录3.检查where条件,
2、做过滤indexconditionpushdown原理1.读索引2.检查索引的条件3.读整行记录4.检查where条件,过滤数据Mysql5.6新特性--indexconditionpushdown注意:mysql在复合索引中,第一列是范围查询,第二列通常是无法利用索引的,建议第一列的查询为=,<=>,orISNULL#基础表#早期版本,5.6以下MariaDB5.3以下usingwhereVSindexconditionpushdown#MariaDB5.3+,MySQL5.6+usingwhereVSindexcon
3、ditionpushdown#MariaDB5.3+,MySQL5.6+#早期版本,5.6以下MariaDB5.3以下Mysql5.6新特性--indexconditionpushdownCounterNameMySQL5.5MySQL5.6Created_tmp_disk_tables00Handler_read_key491495Handler_read_next14667113Handler_read_rnd_next200761200693Innodb_buffer_pool_read_ahead19131912
4、Innodb_buffer_pool_read_requests10362637440Innodb_buffer_pool_reads139091265Innodb_data_read261410816(249M)54235136(51M)Innodb_data_reads158343189Innodb_pages_read158213176Innodb_rows_read214667200113Select_scan44Sort_scan00Innodb_buffer_pool_pages_data239.13M44.
5、8906M查询耗时1m13.334s0m9.907sMysql5.6新特性--indexconditionpushdownindexconditionpushdown性能上的提升一方面提升了查询性能,使的联合索引的范围查询速度得到很大提升令一方面,节省了BP中的内存空间。Mysql5.6新特性--Multi-Range-ReadMulti-Range-ReadMySQL5.6+,MariaDB5.3+Mysql5.6新特性--Multi-Range-Read随机读,接近转换成顺序读Mysql5.6新特性--Multi-R
6、ange-ReadMulti-Range-Read在IO密集型情况测试数据CounterNameMySQL5.5MySQL5.6MySQL5.5read_rnd_bufer_size=4MMySQL5.6read_rnd_bufer_size=4MCreated_tmp_disk_tables0000Handler_read_key283752340796283752340796Handler_read_next286093286093286093286093Handler_read_rnd_next3794437944
7、3794437944Innodb_buffer_pool_read_ahead017776017776Innodb_buffer_pool_read_requests1551628146196415513841461847Innodb_buffer_pool_reads77336403717734740370Innodb_data_read1269256192(1.2G)954863616(910M)1269436416(1.2G)954847232(910M)Innodb_data_reads7735058161773
8、6158160Innodb_pages_read77335581467734658145Innodb_rows_read398157455197398157455197Select_scan1111Sort_scan1111查询耗时10m57.584s2m18.757s6m31.233s2m43.835sMulti-
此文档下载收益归作者所有