目的
基于现有业务需求，复杂查询严重影响了数据库服务器的性能。在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 |
appver
market |
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 |
appve
rmarket |
ALL |
NULL |
NULL |
NULL |
NULL |
73634 |
Using where;
Start materialize |
|
1 |
PRIMARY |
pkg |
ref |
U_PKGID,
idx_apptype,
idx_apptypescore |
U_PKGID |
4 |
test.
appver
market.
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查询还会进一步进行。此外，从查询计划来看，查询优化器也增加了提高性能的方式，有待进一步通过研究源码进行深入的分析和探索。
|
