田杰
2018-05-03
2254浏览量
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;
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
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
// 检查优化器开关配置
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
// ========= 执行快 =========
// 表 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
// 业务低峰期执行
alter table inv_msg algorithm=copy, lock=shared, modify payid bigint not null;
// 重新收集统计信息
analyze table inv_msg;
需要严格遵守规范进行开发工作。
版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。
分享阿里云支持与服务团队最佳实践、经典案例与故障排查。