一个相同查询在不同 RDS for MySQL 实例上性能差异的案例分析-阿里云开发者社区

开发者社区> 田杰> 正文

一个相同查询在不同 RDS for MySQL 实例上性能差异的案例分析

简介: 一个相同查询在不同 RDS for MySQL 实例上性能差异的案例分析 1. 问题出现 2. 问题原因 3. 问题解决 4. 问题结论 相同查询在数据量相近的情况下在不同 RDS for MySQL 实例上有不同的性能表现,容易引发用户对 RDS for MySQL 实例的性能差异性的疑虑,本文分享下近期碰到的一个原因比较隐蔽但很常见的案例。
+关注继续查看

一个相同查询在不同 RDS for MySQL 实例上性能差异的案例分析

1. 问题出现

2. 问题原因

3. 问题解决

4. 问题结论



相同查询在数据量相近的情况下在不同 RDS for MySQL 实例上有不同的性能表现,容易引发用户对 RDS for MySQL 实例的性能差异性的疑虑,本文分享下近期碰到的一个原因比较隐蔽但很常见的案例。

1. 问题出现

一个用户的下述查询在 RDS for MySQL 实例 A 上执行需要 30+ 毫秒,而在 RDS for MySQL 实例 B 执行需要 12+ 秒 

SELECT A.PayId, A.sourceType, 
A.txnTime, A.txnEndTime, A.invid, A.payStatus, 
A.invstatus makeinvoicestatus, A.createTime, B.invstatus invstatus, F.returncode returncode
FROM Pay A
LEFT JOIN 
(
  SELECT  M.invstatus invstatus,  M.PayId PayId,   M.invid invid
  FROM  inv_msg M
  WHERE M.sourcetype != '03'
) B ON A.PayId = B.PayId
LEFT JOIN 
(
  SELECT C.invid invoiceids,  C.returncode, C.creatime
  FROM inv_detail C,
    (
      SELECT D.invid invoiceids, max(D.creatime) creatime
      FROM inv_detail D
      GROUP BY  D.invid
    ) E
  WHERE C.invid = E.invoiceids
  AND C.creatime = E.creatime
) F ON B.invid = F.invoiceids
WHERE A.deleteStatus = 0
AND A.payStatus IN ( '904', '905', '906', '907','908','909' )
AND A.sourceType IN ('01', '02')
ORDER BY txnTime DESC
LIMIT 0,10;​


2. 问题原因

排查 SQL 在 RDS for MySQL 实例 A 和 B 上的执行计划,发现不一致。
执行时间长 - A

id	select_type	table		type	possible_keys			key			key_len		ref				rows	Extra
1	PRIMARY	    A	    	ALL		payStatus,sourceType	NULL		NULL		NULL			26427	Using where; Using temporary; Using filesort
1	PRIMARY	    <derived2>	ALL		NULL					NULL		NULL		NULL			8737	Using where; Using join buffer (Block Nested Loop)
1	PRIMARY	    <derived3>	ref		<auto_key0>				<auto_key0>	8			B.invid			10		NULL
3	DERIVED	    <derived4>	ALL		NULL					NULL		NULL		NULL			10694	NULL
3	DERIVED	    C			ref		invid					invid		8			F.invoiceids	1		Using where
4	DERIVED	    D			index	invid					invid		8			NULL			10694	NULL
2	DERIVED	    M			ALL		NULL					NULL		NULL		NULL			8737	Using where


执行时间短 - B

id	select_type	table		type	possible_keys			key			key_len		ref							rows	Extra
1	PRIMARY		A			index	payStatus,sourceType	txnTime		6			NULL						1		Using where
1	PRIMARY		<derived2>	ref		<auto_key1>				<auto_key1>	8			pc_vqgc_0000.A.unionPayId	15		NULL
1	PRIMARY		<derived3>	ref		<auto_key0>				<auto_key0>	8			B.invid						10		NULL
3	DERIVED		<derived4>	ALL		NULL					NULL		NULL		NULL						10506	NULL
3	DERIVED		C			ref		invid					invid		8			F.invoiceids				1		Using where
4	DERIVED		D			index	invid					invid		8			NULL						10506	NULL
2	DERIVED		M			ALL		sourcetype				NULL		NULL		NULL						8928	Using where


 
从执行计划对比看问题出现在 表 A 和 中间表 B 关联这步
执行计划 A 的 Extra 信息显示 Using join buffer (Block Nested Loop),说明如果选择单纯的 Nested Loop Join 成本会很高(在内层循环无法使用索引的场景下,成本是 O(Rn x Sn))。
优化器为了提高效率,因此选择了 Block Nested Loop。
对比执行计划 B,内层使用的索引是 MySQL 自动创建的(auto_key1),检查优化器开关配置是否有区别,以防万一。
 
// 检查优化器开关配置
show global variables like 'optimizer_switch' \G

*************************** 1. row ***************************
Variable_name: optimizer_switch
        Value: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on

// 输出格式化后
index_merge=on
index_merge_union=on
index_merge_sort_union=on
index_merge_intersection=on
engine_condition_pushdown=on
index_condition_pushdown=on
mrr=on
mrr_cost_based=on
block_nested_loop=on
batched_key_access=off
materialization=on
semijoin=on
loosescan=on
firstmatch=on
subquery_materialization_cost_based=on
use_index_extensions=on
 
对比两个实例优化器开关配置相同,且 materialization 和 subquery_materialization_cost_based 都已经打开, 加之执行计划 A 中有物化表的使用,因此排除掉优化器开关配置问题。
 
此时问题就比较明朗了,应该是关联的两个字段类型不匹配,导致无法通过索引物化临时表的关联字段来使用 Nested Loop Join
 
带着上面的怀疑检查下两个实例的表 Pay 和 inv_msg 的关联字段 PayId 的字段类型。
// ========= 执行快 ========= 
// 表 pay
CREATE TABLE `pay` (
  `PayId` bigint(20) NOT NULL AUTO_INCREMENT,
  `companyId` bigint(20) DEFAULT NULL,
  .......
  `txnEndTime` datetime DEFAULT NULL,
  `deleteStatus` varchar(255) DEFAULT '0',
  PRIMARY KEY (`unionPayId`),
  KEY `companyId` (`companyId`) USING BTREE,
  KEY `invid` (`invId`) USING BTREE,
  KEY `payStatus` (`payStatus`) USING BTREE,
  KEY `sourceType` (`sourceType`) USING BTREE,
  KEY `txnTime` (`txnTime`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=24906 DEFAULT CHARSET=utf8

// 表 inv_msg
CREATE TABLE `inv_msg ` (
`invid` bigint(20) NOT NULL AUTO_INCREMENT, `payid` bigint(20) NOT NULL, ...... `invoicestatus` varchar(2) NOT NULL DEFAULT '0', `sourcetype` varchar(200) NOT NULL', PRIMARY KEY (`invoiceid`), KEY `unionpayid` (`unionpayid`) USING BTREE, KEY `invoicestatus` (`invoicestatus`) USING BTREE, KEY `sourcetype` (`sourcetype`,`unionpayid`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=8897 DEFAULT CHARSET=utf8
// ========= 执行慢 ========= 
// 表 pay
CREATE TABLE `pay` (
  `PayId` bigint(20) NOT NULL AUTO_INCREMENT,
  `companyId` bigint(20) DEFAULT NULL,
  .......
  `txnEndTime` datetime DEFAULT NULL,
  `deleteStatus` varchar(255) DEFAULT '0',
  PRIMARY KEY (`unionPayId`),
  KEY `companyId` (`companyId`) USING BTREE,
  KEY `invid` (`invId`) USING BTREE,
  KEY `payStatus` (`payStatus`) USING BTREE,
  KEY `sourceType` (`sourceType`) USING BTREE,
  KEY `txnTime` (`txnTime`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=24906 DEFAULT CHARSET=utf8

// 表 inv_msg
CREATE TABLE `inv_msg ` (
`invid` bigint(20) NOT NULL AUTO_INCREMENT, `payid` varchar(20) NOT NULL, ...... `invoicestatus` varchar(2) NOT NULL DEFAULT '0', `sourcetype` varchar(200) NOT NULL', PRIMARY KEY (`invoiceid`), KEY `unionpayid` (`unionpayid`) USING BTREE, KEY `invoicestatus` (`invoicestatus`) USING BTREE, KEY `sourcetype` (`sourcetype`,`unionpayid`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=8897 DEFAULT CHARSET=utf8
 
可以看到 payId 字段在执行快场景下 2 个表都是 big int 类型;而在执行慢的场景下,2个表的字段类型分别为 big int 和 varchar,导致执行计划选择了对无法使用索引场景优化的 Block Netsted Loop。

3. 问题解决

理清问题的根源,就有了针对性的方法。
建议用户修改 表 inv_msg 的字段 payid 类型为 big int not null,重新收集统计信息后问题解决。
// 业务低峰期执行
alter table inv_msg algorithm=copy, lock=shared, modify payid bigint not null;  

// 重新收集统计信息
analyze table inv_msg;


4. 问题结论

  • 需要严格遵守规范进行开发工作。

  • 用户 DBA 应该进行 SQL 审核工作。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
不同版本的SQL Server之间数据导出导入的方法及性能比较
工作中有段时间常常涉及到不同版本的数据库间导出导入数据的问题,索性整理一下,并简单比较下性能,有所遗漏的方法也欢迎讨论、补充。 00.建立测试环境 01.使用SQL Server Import and Export Tool 02.
880 0
MySQL使用profile分析语句性能消耗
MySQL使用profile分析语句性能消耗 --查看profile是否开启mysql> show variables like '%profil%';+------------------------+-...
737 0
使用Spring AOP实现MySQL数据库读写分离案例分析
使用Spring AOP实现MySQL数据库读写分离案例分析 前言 分布式环境下数据库的读写分离策略是解决数据库读写性能瓶颈的一个关键解决方案,更是最大限度了提高了应用中读取 (Read)数据的速度和并发量。
1746 0
AliSQL开源功能特性
在2017在线技术峰会“阿里开源项目最佳实践”上,阿里云数据库内核专家赵建伟(冷香)为大家带来了“AliSQL开源功能特性”的演讲。本文先简要介绍了AliSQL以及其开源背景,重点说明了AliSQL已开源的功能,包括Sequence Engine、TokuDB引擎支持和秒杀优化等,最后对AliSQL用户ISSUE和典型问题作了解答。
8812 0
RDS MySQL 5.7 4核和8核共享规格性能压测
写在前面 这次压测只用了一台ECS,也没有调整各种压测比和压测参数,把实例压到最高;这里的压测只是为了说明不同的参数对性能影响,要想得出一个结论,必须要在可对比的情况下,比如:实例规格一样,从压测机到被压测机网络延迟一样,MySQL的配置文件一样等等,否则都是耍流氓。
1644 0
+关注
田杰
专注于数据库领域技术
21
文章
46
问答
来源圈子
更多
作为全球云计算的领先者,阿里云为全球230万企业提供着云计算服务,服务范围覆盖200多个国家和地区。我们致力于为企业、政府等组织机构提供安全可靠的云计算服务,给用户带来极速愉悦的服务体验。
+ 订阅
文章排行榜
最热
最新
相关电子书
更多
文娱运维技术
立即下载
《SaaS模式云原生数据仓库应用场景实践》
立即下载
《看见新力量:二》电子书
立即下载