Payment提现申请定时任务执行速度慢分析
根据线上反馈目前Payment提现申请定时任务(以下简称定时任务)运行时间较长。通过对定时任务所执行的SQL进行分析发现，定时任务执行速度慢是因为account_journal表和r_settlement_journal表的关联查询速度过慢造成。
两张表，每张表都有40W条数据，一张流水表(account_journal)；一张中间表(r_settlement_journal)，中间表记录着已经处理了的流水表数据。
流水表结构：CREATE TABLE `account_journal` (
`id` varchar(32) NOT NULL COMMENT '主键',
`account_id` varchar(32) NOT NULL,
`in_or_out` int(11) NOT NULL DEFAULT '0' COMMENT '出入账(0：入账 1：出账)',
`origin_amount` int(11) NOT NULL DEFAULT '0' COMMENT '原金额(单位：分)',
`service_charge` int(11) DEFAULT NULL COMMENT '服务费(单位：分)',
`actual_amount` int(11) NOT NULL DEFAULT '0' COMMENT '实际金额(单位：分)',
`account_amount` int(11) NOT NULL COMMENT '入账后，账户余额',
`create_time` datetime NOT NULL COMMENT '创建时间',
`info` varchar(256) DEFAULT NULL COMMENT '描述',
`opposite_type` int(2) NOT NULL COMMENT '交易对方类型:0.咕咕订单 1 商家结算',
`opposite_account_number` varchar(64) NOT NULL COMMENT '交易对方账号 当opposite_type=0时，为顾客手机号；当opposite_type=1时，为第三方支付账号',
`opposite_account_name` varchar(64) DEFAULT NULL COMMENT '交易对方账号姓名',
`opposite_account_type_name` varchar(64) NOT NULL COMMENT '交易对方账号类型：微信支付、支付宝、中国银行…',
`trade_id` varchar(32) NOT NULL COMMENT '交易ID当opposite_type=0时，为订单ID；当opposite_type=1时，为结算ID',
`business_id` varchar(32) DEFAULT NULL COMMENT '当opposite_type=0时，为支付id或者退款id;当当opposite_type=1时为空',
`create_time_ms` bigint(20) DEFAULT NULL COMMENT '流水创建时间按毫秒存储',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;
中间表结构：CREATE TABLE `r_settlement_journal` (
`id` varchar(32) NOT NULL COMMENT '主键',
`journal_id` varchar(32) NOT NULL COMMENT '流水id',
`settlement_id` varchar(32) NOT NULL COMMENT '审核id',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;
每次定时任务执行时需要查询出没有处理的流水表数据。
查询语句使用not exists进行关联查询-- EXPLAIN
SELECT
SUM(a.actual_amount) amount
FROM
account_journal a
WHERE
NOT EXISTS (
SELECT
b.journal_id
FROM
r_settlement_journal b
WHERE
b.journal_id = a.id
)
AND a.in_or_out = 0
AND a.opposite_type = 0
AND a.account_id = '58077112a5a911e5b45900215edb4bc4'
AND a.create_time < '2016-07-26 23:59:59'
40W条数据查询用时为26s,如果切换成left join用时28s。
查询语句分析结果：*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: a
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 421952
Extra: Using where
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: b
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 386560
Extra: Using where
两张表都使用了全表扫描。
优化步骤：
1. 中间表关联id(journal_id)加索引ALTER TABLE `r_settlement_journal`
ADD INDEX `idx_journal_id` (`journal_id`) USING BTREE ;
执行查询语句，查询时间由26s变为0.6s,查询效率提升明显。
2. 流水表account_id字段添加索引ALTER TABLE `account_journal`
ADD INDEX `idx_account_id` (`account_id`) USING BTREE ;
再次执行查询语句，查询时间由0.6s变为0.005s,查询效率再次提升。
3. 添加索引对插入效率的影响分析
3.1 表中已有40W数据时，有索引和无索引插入时间对比account_journal表有account_id索引和无account_id索引插入时间对比
|测试数据|无索引插入数据耗时 |有索引插入数据耗时 |
|——-|——————-|——————-|
|8线程同时插入1W条数据 ，共插入8W条数据| 77.19s| 78.413s|
|64个线程，每个线程插入10条数据，共插入640条数据|0.504s|0.536s|
r_settlement_journal表有journal_id索引和无journal_id索引插入时间对比
|测试数据|无索引插入数据耗时 |有索引插入数据耗时 |
|——-|——————-|——————-|
|8线程同时插入1W条数据 ，共插入8W条数据| 56.29s|57.3s|
|64个线程，每个线程插入10条数据，共插入640条数据|0.393s|0.458s|
3.2 表中无数据时，有索引和无索引插入时间对比account_journal表有account_id索引和无account_id索引插入时间对比
|测试数据|无索引插入数据耗时 |有索引插入数据耗时 |
|——-|——————-|——————-|
|8线程同时插入1W条数据 ，共插入8W条数据| 54.601s| 55.599s|
r_settlement_journal表有journal_id索引和无journal_id索引插入时间对比
|测试数据|无索引插入数据耗时 |有索引插入数据耗时 |
|——-|——————-|——————-|
|8线程同时插入1W条数据 ，共插入8W条数据| 51.833s|52.505s|
总结：有无索引对数据插入效率影响不大，但是能显著的提升关联查询的效率。
优化结果
提现申请定时任务执行一次所需时间由线3小时变为8分钟。
(测试数据：40W条数据，处理2016-07-25当天的流水数据)
添加索引：-- ALTER TABLE `r_settlement_journal` ADD INDEX `idx_journal_id` (`journal_id`) USING BTREE ;
-- ALTER TABLE `account_journal` ADD INDEX `idx_account_id` (`account_id`) USING BTREE ;
ALTER TABLE `r_settlement_journal` ADD INDEX `idx_journal_id` (`journal_id`) ;
ALTER TABLE `account_journal` ADD INDEX `idx_account_id` (`account_id`) ;
删除索引：DROP INDEX idx_journal_id ON r_settlement_journal;
DROP INDEX idx_account_id ON account_journal;
