MySQL-5.6与MySQL-5.5查询性能比较

目的

       基于现有业务需求，复杂查询严重影响了数据库服务器的性能。在MySQL-5.6中，着重对查询优化器进行了改造，提高了查询的性能。为了进一步验证查询性能的提高，对MySQL-5.6的查询性能进行测试。

设计

       为了全面了解MySQL-5.6的查询性能，分别对mysql-5.5.20、percona-server-5.5.20、mysql-5.6.6、percona-server-5.6.6四个版本的数据库在相同配置下，执行相同测试语句，使用sql_no_cache避免innodb_buffer_pool对查询的影响，测试相同次数（10），获取查询时间的总值、最大值、最小值、平均值。此外，输出查询计划，为进一步研究提供思路。

       为了使得测试更贴近实际线上环境，测试库选择实际的业务数据库，测试语句选择了一个开发目前性能较低的SQL。

       SQL查询语句如下所示：

 

SELECT count(1)

FROM app app, appver av

WHERE

  av.appid = app.appid AND

  av.stat in (0,1,2) AND

  av.pkgvercode in

    ( SELECT max(pkgvercode) maxpkgvercode FROM appver WHERE appver.appid = av.appid GROUP BY appid) AND

  av.pkgid in ( SELECT DISTINCT pkg.pkgid FROM pkginfo pkg, appvermarket WHERE pkg.apptype = 0 AND (pkg.apptypescore BETWEEN 1 and 100) AND

  (appvermarket.modifytime BETWEEN '1990-01-01' AND '2013-01-01') AND

  pkg.pkgid = appvermarket.pkgid )

ORDER BY av.creationtime DESC

 

测试

       测试查询语句的执行时间、以及执行总时间、执行最大时间、执行最小时间、平均执行时间。具体如下所示：

 

次数	mysql-5.5.20（sec）	percona-server-5.5.20（sec）	mysql-5.6.6（sec）	percona-server-5.6.6（sec）
1	22.74938375	21.67018175	4.841938	5.144301
2	20.3056995	18.897762	2.503185	2.44326125
3	20.219908	19.35700875	2.54380675	2.411751
4	20.02808875	18.7846835	2.5677525	2.4499795
5	19.058993	18.85679225	2.6188195	2.54481625
6	19.1560985	18.5317	2.53454075	2.4130805
7	19.199497	18.84013225	2.583974	2.417411
8	19.4677955	19.75344125	2.67006825	2.4147165
9	19.50529475	19.01621475	2.55766075	2.268934
10	19.30806825	19.030371	2.588415	2.38645325
Total	198.99882700	192.73828750	28.01016050	26.89470425
Max	22.74938375	21.67018175	4.84193800	5.14430100
Min	19.05899300	18.53170000	2.50318500	2.26893400
Avg	19.89988270	19.27382875	2.80101605	2.68947042

 

       MySQL查询执行时间的比较如下图所示，从图中可以明显发现，查询性能有较大的提高。

 

 

 

通过以上各个版本执行相同SQL查询10次，得到的结果可知：mysql-5.6和Percona-server-5.6在查询性能上，有较大的提高。以平均值来看，5.6版本的查询性能是5.5版本的查询性能的近7倍。此外，Percona-mysql的性能较mysql官方的性能有所提高，但不明显。具体如下所示。

 

Version		mysql-5.5.20（sec）	percona-server-5.5.20（sec）	mysql-5.6.6（sec）	percona-server-5.6.6（sec）
	AVG	19.8998827	19.27383	2.801016	2.68947042
mysql-5.5.20（sec）	19.89988	1	0.96854	0.140755	0.135150064
percona-server-5.5.20（sec）	19.27383	0.051883827	1	0.145327	0.139540018
mysql-5.6.6（sec）	2.801016	0.018523217	6.881013	1	0.960176726
percona-server-5.6.6（sec）	2.68947	0.00688731	7.166403	1.041475	1

 

       除了以上实际的输出结果有较大差异，相同的查询语句，MySQL-5.5与MySQL-5.6的执行计划也有较大差异。

       MySQL-5.5的查询计划如下所示：

 

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	av	ALL	appver_appid_i	NULL	NULL	NULL	65012	Using where
1	PRIMARY	app	eq_ref	PRIMARY	PRIMARY	4	test.av.appid	1	Using index
3	DEPENDENT SUBQUERY	pkg	ref	U_PKGID,idx_apptype,idx_apptypescore	U_PKGID	4	func	2	Using where; Using temporary
3	DEPENDENT SUBQUERY	appvermarket	ALL	NULL	NULL	NULL	NULL	70243	Using where; Using join buffer
2	DEPENDENT SUBQUERY	appver	ref	appver_appid_i	appver_appid_i	4	test.av.appid	1	Using where

 

MySQL-5.6的查询计划如下所示：

 

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	av	ALL	PRIMARY,appver_appid_i	NULL	NULL	NULL	64579	Using where
1	PRIMARY	appvermarket	ALL	NULL	NULL	NULL	NULL	73634	Using where; Start materialize
1	PRIMARY	pkg	ref	U_PKGID,idx_apptype,idx_apptypescore	U_PKGID	4	test.appvermarket.pkgid	3	Using where; End materialize
1	PRIMARY	app	eq_ref	PRIMARY	PRIMARY	4	test.av.appid	1	Using index
2	DEPENDENT SUBQUERY	appver	ref	appver_appid_i	appver_appid_i	4	test.av.appid	1	Using where

 

       从查询计划可以看出，在Extra中，增加了Start materialize和End materialize信息。具体查询优化器的处理，还需要通过分析源码进行深入的探索。

 

总结

       通过测试发现，5.6版本较5.5版本从SQL执行时间来看，在查询性能方面，有较大的提高。由于测试的SQL较少，没有对多种SQL查询进行测试，仍然存在一定的不完善之处。因此，后续测试的SQL查询还会进一步进行。此外，从查询计划来看，查询优化器也增加了提高性能的方式，有待进一步通过研究源码进行深入的分析和探索。