首页 文章

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

提问于
浏览 次
0

我们有三个表，需要从三个表中选择记录，即有162161条记录的帐户Account_Types 6条记录有10761247条记录的交易

下面是表格结构

交易

id bigint（20）unsigned NO PRI auto_increment transfer_number bigint（20）unsigned YES MUL
debit_credit varchar（255）没有MUL
fund_type varchar（255）NO MUL
fund_id bigint（20）unsigned YES MUL
货币varchar（255）没有
金额十进制（20,2）NO MUL 0
说明文字NO
other_type varchar（255）NO
other_id bigint（20）unsigned YES
transaction_type varchar（255）NO
accounts_receivable int（1）unsigned NO 0
transaction_status varchar（255）NO MUL
creation_date datetime NO 0000-00-00 00:00:00 execution_date datetime NO MUL 0000-00-00 00:00:00 api int（1）YES
claim_id char（8）是的

账户

id bigint（20）unsigned NO PRI auto_increment user_id bigint（20）unsigned YES MUL
account_number varchar（255）NO UNI
type_id bigint（20）unsigned YES MUL
description varchar（255）NO
commission_acc_id bigint（20）unsigned YES MUL
allow_debit varchar（255）NO
allow_credit varchar（255）NO
account_status varchar（255）NO MUL
creation_date datetime NO 0000-00-00 00:00:00

ACCOUNT_TYPES

id bigint（20）unsigned NO PRI auto_increment description varchar（255）NO MUL
currency varchar（255）NO MUL
monthly_fee_amount double NO 0
monthly_fee_description varchar（255）NO
annual_fee_amount double NO 0
annual_fee_description varchar（255）NO
generate_interests varchar（255）NO
interest_rate double NO 0
interest_payout_period varchar（255）NO
interest_payout_day char（2）NO
interest_payout_month char（2）NO
interest_payout_hour char（2）NO
interest_based_on varchar（255）NO
interest_based_on_period varchar（255）NO
interest_minimum_balance double NO 0
generate_commissions varchar（255）NO
commission_rate double 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）NO
commission_based_on_period varchar（255）NO
commission_minimum_balance double 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分钟甚至更长时间才能获得输出 .

从管理层预先评估 - 由于它是一个交易表，我们无法存档表，我们需要记录来计算总和而不是允许的硬件更改 . table 上还有索引也存在 . 在描述中表示为MUL .

mysql query-optimization

1 回答

  • 2

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

    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';

    这些可能是最简单和最有成效的变化 . 试试看，看看情况是否有足够的改善 . 如果没有，则可以应用其他优化 .

    回复于

相关问题