mysql - 如何优化这个SELECT？

Question

我有一对多表 Payment 和 PaymentFlows 来跟踪付款工作流程。

对于不同的管理者，他们只对某些工作流程感兴趣。因此，每当付款达到某个工作流程时，就会向他们提供一个列表。

例如，

 Payment 1 - A) Apply
             B) Checked
             C) Approved by Manager
             D) Approved by CFO
             E) Cheque issued

 Payment 2 - A) Apply
             B) Checked
             C) Approved by Manager

 Payment 3 - A) Apply
             B) Checked
             C) Approved by Manager

 Payment 4 - A) Apply
             B) Checked

要显示工作流程C中的所有付款，我所做的是:

class Payment < ActiveRecord::Base

  def self.search_by_workflow(flow_code)
    self.find_by_sql("SELECT * FROM payments P INNER JOIN (
        SELECT payment_id FROM (
          SELECT * FROM (
            SELECT * FROM payment_flows F
            ORDER BY F.payment_flow_id DESC
          ) latest GROUP BY payment_id
        ) flows WHERE flows.code = flow_code)
      ) IDs ON IDs.payment_id = P.payment_id ORDER BY P.payment_id DESC LIMIT 100;")
  end

end

所以:

@payments = Payment.search_by_workflow('Approved by Manager')

返回:付款 2 和 3

但是，性能不是很好(15,000 笔付款和 55,000 个工作流程需要 5 到 7 秒)。

如何提高性能？

更新(使用表结构):

CREATE TABLE `payments` (
  `payment_id` int(11) NOT NULL,
  `payment_type_code` varchar(50) default 'PETTY_CASH',
  `status` varchar(16) NOT NULL default '?',
  PRIMARY KEY  (`payment_id`),
  KEY `status` (`status`),
  KEY `payment_type_code` (`payment_type_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `payment_flows` (
  `payment_flow_id` int(11) NOT NULL,
  `payment_id` int(11) default NULL,
  `code` varchar(64) default NULL,
  `status` varchar(255) NOT NULL default 'new',
  PRIMARY KEY  (`payment_flow_id`),
  KEY `payment_id` (`payment_id`),
  KEY `code` (`code`),
  KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

更新(使用name_scope):

named_scope :by_workflows, lambda { |workflows| { :conditions =>  [ "EXISTS (
          SELECT 'FLOW'
          FROM payment_flows pf
          WHERE pf.payment_id = payments.payment_id
          AND pf.proc_code IN (:flows)
          AND NOT EXISTS (
              SELECT 'OTHER'
              FROM payment_flows pfother
              WHERE pfother.payment_id = pf.payment_id
              AND pfother.payment_flow_id > pf.payment_flow_id
          )
      )", { :flows => workflows } ]}
    }

为了方便，例如:

Payment.by_workflows(['Approved by Manager', 'Approved by CFO']).count

Answer 1

试试这个:

SELECT * FROM payment p
WHERE EXISTS(
    SELECT 'FLOW'
    FROM payment_flows pf
    WHERE pf.payment_id = p.payment_id
    AND pf.code = flow_code 
    AND NOT EXISTS(
        SELECT 'OTHER'
        FROM payment_flows pf2
        WHERE pf2.payment_id = pf.payment_id
        AND pf2.payment_flow_id > pf.payment_flow_id
    )
)

注意:查询中的flow_code是一个变量，其中包含您要搜索的代码

我添加了一个关于 flow_code 是否存在的主要 EXISTS 条件，以及一个关于 flow_code 接下来不存在相同付款的其他 id 的嵌套 NOT EXISTS 条件。

告诉我是否可以提高性能。