使用explain优化mysql查询

Question

我正在为客户端优化一个查询；在深入到这个结构之前，我将尝试在高级描述我要做的事情。客户端数据库有一个事务表(当前为352 k记录)。他们有一个黑名单，他们可以输入电子邮件地址，客户帐号，信用卡BIN号码等，这将阻止任何人完成交易，如果他们使用该信息。最近，他们提交了一份“交叉参考”报告的功能请求:对于每个被列入黑名单的电子邮件地址：

1. 找到买方或收件人的主要或次要电子邮件地址出现在黑名单中的任何交易(例如，通过使用后来被列入黑名单的电子邮件地址的交易)，
2. 从这些交易中收集任何客户帐号和ip地址，以及
3. 使用这些收集的帐号和ip地址显示任何其他事务。
4. 对黑名单上的帐号(收集电子邮件和IP )和黑名单上的IP地址(收集电子邮件和帐号)也这样做。

我做这项工作的方法是创建一个存储的简单的INSERT INTO语句；例如，第一个看起来有点像这样(当然是匿名的)：

insert into `blacklist_xref_filters`
select distinct 
    t.email as `filter_criteria`, 
    "accountNum" as `blacklist_source`, 
    blacklistAccountNum.account_number as `blacklist_entry`, 
    unix_timestamp(blacklistAccountNum.created_at) as `blacklist_stamp` 
from TransactionRecords t
inner join blacklistAccountNum on 
    t.purchaserAccountNumber = blacklistAccountNum.account_number 
    or t.recipientAccountNumber = blacklistAccountNum.account_number
where (not t.email = '') and (blacklistAccountNum.deleted_at is null);

除了使用t.emailSecondary、t.recipientEmail、t.recipientEmailSecondary和t.ip_address代替t.email之外，这个查询几乎重复了三次。下一个查询几乎是一样的，除了这次输入与黑名单上的电子邮件地址相关的事务：

insert into `blacklist_xref_filters`
select distinct 
    t.purchaserAccountNumber as `filter_criteria`, 
    "email" as `blacklist_source`, 
    blacklistEmail.email as `blacklist_entry`, 
    unix_timestamp(blacklistEmail.created_at) as `blacklist_stamp` 
from TransactionRecords t
inner join blacklistEmail on 
    t.email = blacklistEmail.email 
    or t.emailSecondary = blacklistEmail.email
    or t.recipientEmail = blacklistEmail.email
    or t.recipientEmailSecondary = blacklistEmail.email
where (not t.purchaserAccountNumber = '') and (blacklistEmail.deleted_at is null);

最后发生的事情是，我将它加入到TransactionRecords表中，以返回与黑名单项目相关联的记录列表：

select distinct <fieldlist> from TransactionRecords
inner join `blacklist_xref_filters` on 
    `TransactionRecords`.`purchaserAccountNumber` = `blacklist_xref_filters`.`filter_criteria` or 
    `TransactionRecords`.`recipientAccountNumber` = `blacklist_xref_filters`.`filter_criteria` or 
    `TransactionRecords`.`email` = `blacklist_xref_filters`.`filter_criteria` or 
    `TransactionRecords`.`emailSecondary` = `blacklist_xref_filters`.`filter_criteria` or 
    `TransactionRecords`.`recipientEmail` = `blacklist_xref_filters`.`filter_criteria` or 
    `TransactionRecords`.`recipientEmailSecondary` = `blacklist_xref_filters`.`filter_criteria` or 
    `TransactionRecords`.`ipAddress` = `blacklist_xref_filters`.`filter_criteria`;

我遇到的问题是，其中一些单独的SELECT查询可能需要10到15秒才能运行；而且由于我正在运行14个单独的INSERT查询，呈现视图的总时间至少需要45秒，有时更长。

我通过EXPLAIN运行了第一个源查询，我不知道还能做什么来进一步优化它：

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: blacklistAccountNum
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 147
     filtered: 100.00
        Extra: Using where; Using temporary
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
         type: ALL
possible_keys: transactionrecords_purchaseraccountnumber_index
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 350910
     filtered: 100.00
        Extra: Using where; Using join buffer

show warnings的输出表明第一个查询是以这种方式重写的：

select distinct 
    `t`.`email` AS `filter_criteria`,
    'accountNum' AS `blacklist_source`,
    `blacklistAccountNum`.`account_number` AS `blacklist_entry`,
    unix_timestamp(`blacklistAccountNum`.`created_at`) AS `blacklist_stamp` 
from `TransactionRecords` `t` 
join `blacklistAccountNum` 
where 
    (
        (`t`.`email` <> '') and 
        isnull(`blacklistAccountNum`.`deleted_at`) and 
        (
            (`t`.`purchaserAccountNumber` = `blacklistAccountNum`.`account_number`) or 
            (`t`.`recipientAccountNumber` = `blacklistAccountNum`.`account_number`)
        )
    )

就索引而言，我在以下方面有单独的索引：

• blacklistAccountNum.deleted_at
• TransactionRecords.purchaserAccountNumber
• TransactionRecords.recipientAccountNumber

不过，似乎没有在purchaserAccountNumber上使用该索引。我以前从未使用过EXPLAIN，所以我需要一些lost...what步骤来保持这个特定的查询不需要花12秒的时间运行吗？

Answer 1

查询的一个问题是OR is ON --这意味着它必须检查整个第二个表才能找到匹配的行，因为通常不能对这些行高效地使用索引(有一些索引合并的可能性，但那些索引通常在“基本”表上工作，而不是连接表)。

您可以根据不同的条件拆分这些插入，您将有更多的插入，但每个插入都可以使用不同的索引。然后你必须给它那些索引- purchaserAccountNumber，recipientAccountNumber，每个电子邮件字段。

此外，您还应该检查列类型与blacklistAccountNum.account_number是否匹配，因为如果不匹配，则mysql必须在两者之间执行某种类型杂耍，但又不能有效地使用索引。你加入的电子邮件也是如此。