一个相同查询在不同 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 审核工作。

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

分享:

分享阿里云支持与服务团队最佳实践、经典案例与故障排查。

官方博客
文档