mysql - 查询性能问题 - 对于 select 语句，即使表上有超过 20 万亿条记录的索引

Question

我们有三个表，需要从三个表中选取记录，即拥有 162161 条记录的账户账户类型 6 条记录具有 10761247 条记录的交易

下面是表结构

交易

id bigint(20) 无符号 NO PRI 自动增量Transfer_number bigint(20) 无符号 YES MUL
debit_credit varchar(255) NO MUL
fund_type varchar(255) NO MUL
fund_id bigint(20) 未签名 YES MUL
货币 varchar(255) NO
金额小数(20,2) NO MUL 0
描述文字NO
other_type varchar(255) 否
other_id bigint(20) 无符号 是
transaction_type varchar(255) 否
account_receivable int(1) unsigned NO 0
transaction_status varchar(255) NO MUL
创建日期日期时间否 0000-00-00 00:00:00execution_date 日期时间 NO MUL 0000-00-00 00:00:00api int(1) 是
Claim_id char(8) 是

账户

id bigint(20) 无符号 NO PRI 自动增量user_id bigint(20) 无符号 YES MUL
account_number varchar(255) 无 UNI
type_id bigint(20) 无符号 YES MUL
描述 varchar(255) 否
Commission_acc_id bigint(20) 无符号 YES MUL
allow_debit varchar(255) 否
allow_credit varchar(255) 否
account_status varchar(255) NO MUL
创建日期日期时间NO 0000-00-00 00:00:00

ACCOUNT_TYPES

id bigint(20) 无符号 NO PRI 自动增量描述 varchar(255) NO MUL
货币 varchar(255) NO MUL
Monthly_fee_amount 双倍 NO 0
Monthly_fee_description varchar(255) NO
yearly_fee_amount 双倍 NO 0
yearly_fee_description varchar(255) NO
generate_interests varchar(255) 否
interest_rate double NO 0
interest_payout_period varchar(255) 否
interest_payout_day char(2) NO
interest_payout_month char(2) 否
interest_payout_hour char(2) NO
interest_based_on varchar(255) 否
interest_based_on_period varchar(255) NO
interest_minimum_balance 双 NO 0
generate_commissions varchar(255) 否
Commission_rate 双倍 NO 0
Commission_payout_period varchar(255) NO
Commission_payout_day char(2) NO
Commission_payout_month char(2) NO
Commission_payout_hour char(2) NO
Commission_based_on varchar(255) 否
Commission_based_on_period varchar(255) NO
Commission_minimum_balance 双 NO 0

下面是我们正在使用的查询:

SELECT SUM(t.amount) AS total_credit 
FROM    account_types at, 
        accounts a, 
        transactions t 
WHERE   at.currency          = var_currency 
AND     at.id                = a.type_id 
AND     (a.account_status    = 'active' 
        OR a.account_status  = 'blocked')
AND     a.id                 = t.fund_id 
AND     t.debit_credit       = var_debit_credit 
AND     t.fund_type          = 'account'
AND     t.transaction_status = 'executed';

需要 20 分钟或更长时间才能获得输出。

管理层的先决条件 - 由于它是一个事务表，我们无法存档该表，我们需要记录来计算总和，并且不允许进行硬件更改。表上也有索引。在描述中表示为 MUL。

Answer 1

首先，尝试在事务表中所需的所有列上添加索引。

ALTER TABLE transaction
ADD INDEX `DebCredFundTypeTransStatus`
(`fund_id`,`debit_credit`,`fund_type`,`transaction_status`,`amount`)

这将减少对该表的查找次数，仅查找该索引，而不必访问表本身来获取数据。重要的是，amount 是索引的最右侧部分。

因为 a.account_status 和 at.currency 是您各自表中唯一需要访问的列(主键除外，主键包含在所有索引的最右边部分)那么你应该可以接受这些。

下一个大瓶颈是 OR 子句。由于您没有对结果进行排序，因此我们可以轻松地使用 UNION ALL，然后针对 account_status 的每个所需值运行两次。

SELECT SUM(t.amount) AS total_credit 
FROM    account_types at, 
        accounts a, 
        transactions t 
WHERE   at.currency          = var_currency 
AND     at.id                = a.type_id 
AND     a.account_status    = 'active'
AND     a.id                 = t.fund_id 
AND     t.debit_credit       = var_debit_credit 
AND     t.fund_type          = 'account'
AND     t.transaction_status = 'executed'

UNION ALL

SELECT SUM(t.amount) AS total_credit 
FROM    account_types at, 
        accounts a, 
        transactions t 
WHERE   at.currency          = var_currency 
AND     at.id                = a.type_id 
AND     a.account_status  = 'blocked'
AND     a.id                 = t.fund_id 
AND     t.debit_credit       = var_debit_credit 
AND     t.fund_type          = 'account'
AND     t.transaction_status = 'executed';

这些可能是最简单、最有成效的改变。尝试一下，看看情况是否有足够的改善。如果没有，还可以应用其他优化。