Mysql5.6新特性介绍

《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-