php - 从慢速查询日志提高 MySQL 查询性能

Question

我在 MySQL 配置中打开了慢查询监视器。

查询及时间如下:

Time: 160330 20:54:11 User@Host: user[user] @ [xx.xx.xxx.xxx] Query_time: 8.794170 Lock_time: 0.000141 Rows_sent: 3942 Rows_examined: 4742825 SET timestamp=1459371251;

SELECT (SELECT (CASE WHEN ce_type = 'IN' then SUM(payment_amount)
                                END) as debit
                        FROM customer_payment_options cpo
                        WHERE wallet_id=cw.id
                        AND (cpo.real_account_type='HQ')
                        AND cpo.source_country_id='40'
                        GROUP BY cpo.wallet_id)
                    as debit,
                    (SELECT SUM(payment_amount)
                                 as credit
                        FROM customer_payment_options cpo
                        WHERE wallet_id=cw.id
                        AND (cpo.real_account_type='HQ')
                        AND cpo.tran_id IS NOT NULL
                        AND cpo.source_country_id='40'
                        GROUP BY cpo.wallet_id)
                    as credit

                    FROM customer_wallet cw
                    WHERE cw.company_id='1'
                    AND cw.currency='40'
                    AND cw.is_approved = '1'
                    AND DATE(cw.date_added) < '2016-03-30';

customer_payment_options 上的索引:

company_id
tran_id
ce_id
wallet_id

我应该怎么做才能提高它的性能？

解释:

http://i.stack.imgur.com/iH8rt.png

架构

CREATE TABLE `customer_payment_options` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`company_id` int(11) NOT NULL,
`local_branch_id` int(11) NOT NULL,
`tran_id` bigint(11) DEFAULT NULL,
`ce_id` int(11) DEFAULT NULL,
`wallet_id` int(11) DEFAULT NULL,
`reward_credit_id` int(11) DEFAULT NULL,
`ce_invoice_id` varchar(32) DEFAULT NULL,
`ce_type` enum('IN','OUT') DEFAULT NULL,
`payment_type` enum('CASH','DEBIT','CREDIT','CHEQUE','DRAFT','BANK_DEPOSIT','EWIRE','WALLET','LOAN','REWARD_CREDIT') NOT NULL,
`payment_amount` varchar(20) NOT NULL,
`payment_type_number` varchar(100) DEFAULT NULL,
`source_country_id` int(11) NOT NULL,
`real_account_id` int(11) DEFAULT NULL,
`real_account_type` enum('LOCAL','HQ') DEFAULT NULL,
`date_added` datetime NOT NULL,
`event_type` enum('MONEY_TRANSFER','CURRENCY_EXCHANGE','WALLET') DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `company_id` (`company_id`),
KEY `real_account_type` (`real_account_type`),
KEY `tran_id` (`tran_id`),
KEY `ce_id` (`ce_id`),
KEY `wallet_id` (`wallet_id`),
CONSTRAINT `customer_payment_options_ibfk_4` FOREIGN KEY (`wallet_id`) REFERENCES `customer_wallet` (`id`),
CONSTRAINT `customer_payment_options_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `company` (`id`),
CONSTRAINT `customer_payment_options_ibfk_2` FOREIGN KEY (`tran_id`) REFERENCES `transaction` (`id`),
CONSTRAINT `customer_payment_options_ibfk_3` FOREIGN KEY (`ce_id`) REFERENCES `currency_exchange` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=412 DEFAULT CHARSET=utf8

CREATE TABLE `customer_wallet` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`wallet_unique_id` varchar(100) DEFAULT NULL,
`company_id` int(11) NOT NULL,
`branch_admin_id` int(11) DEFAULT NULL,
`emp_id` int(11) DEFAULT NULL,
`emp_type` enum('SUPER_ADMIN','ADMIN','AGENT_ADMIN','AGENT','OVER_AGENT_ADMIN','OVER_AGENT') DEFAULT NULL,
`cus_id` bigint(11) NOT NULL,
`tran_id` bigint(11) DEFAULT NULL,
`beehive_id` int(11) DEFAULT NULL,
`type` enum('DEPOSIT','WITHDRAW','TRANSACTION') NOT NULL,
`sub_type` enum('MONEY_TRANSFER','BEEHIVE_DEPOSIT') DEFAULT NULL,
`credit_in` varchar(20) DEFAULT NULL,
`credit_out` varchar(20) DEFAULT NULL,
`currency` varchar(20) NOT NULL,
`date_added` datetime NOT NULL,
`note` varchar(255) DEFAULT NULL,
`location` enum('DIRECT') DEFAULT NULL,
`is_approved` enum('0','1') NOT NULL DEFAULT '1',
`idebit_issconf` varchar(50) DEFAULT NULL,
`idebit_issname` varchar(50) DEFAULT NULL,
`idebit_isstrack2` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `cus_id` (`cus_id`),
KEY `company_id` (`company_id`),
KEY `branch_admin_id` (`branch_admin_id`),
KEY `emp_id` (`emp_id`),
KEY `tran_id` (`tran_id`),
KEY `beehive_id` (`beehive_id`),
CONSTRAINT `customer_wallet_ibfk_1` FOREIGN KEY (`cus_id`) REFERENCES `customers` (`id`),
CONSTRAINT `customer_wallet_ibfk_2` FOREIGN KEY (`company_id`) REFERENCES `company` (`id`),
CONSTRAINT `customer_wallet_ibfk_3` FOREIGN KEY (`tran_id`) REFERENCES `transaction` (`id`),
CONSTRAINT `customer_wallet_ibfk_4` FOREIGN KEY (`emp_id`) REFERENCES `employees` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=152 DEFAULT CHARSET=utf8

Answer 1

您正在对每个钱包 ID 进行相关查询，以获取相应的借方和贷方。看来您每个钱包 ID 都获得了一条记录。这很忙。根据您的常见标准加入客户付款表(包括每个钱包 ID 的加入)。然后，将 CASE 简化为 SUM( case/when ) 作为相应的借方/贷方。

我不知道你的表列的基本标准，但我什至会(并且确实)包括 NOT CE_TYPE = 'IN' 因为它出现在借方基础上，你不想错误地算作一部分也有功劳。同样，不知道字段、trans_id、类型的相关性。

现在，如前所述，在各个字段上拥有单独的索引无助于优化此查询。我会建议以下指标。

表格索引customer_wallet ( company_id, is_approved, currency, id, date_added )customer_payment_options (wallet_id, account_type, country_id)

SELECT
      cw.wallet_id,
      SUM( case when cpo.ce_type = 'IN'
                then cpo.payment_amount
                ELSE 0 end ) as Debit,
      SUM( case when NOT cpo.ce_type = 'IN' 
                     AND cpo.tran_id IS NOT NULL
                then cpo.payment_amount
                ELSE 0 end ) as Credit
   FROM 
      customer_wallet cw
         JOIN customer_payment_options cpo
            ON cw.id = cpo.wallet_id
           AND cpo.real_account_type = 'HQ'
           AND cpo.source_country_id = '40'
   WHERE 
          cw.company_id = '1'
      AND cw.currency = '40'
      AND cw.is_approved = '1'
      AND cw.date_added < '2016-03-30'
   GROUP BY
      cw.id

一个额外的评论。如果您的 ID 列、货币标志、国家 ID、批准实际上是表结构中的数值，请删除引号并直接在数值上进行比较。另外，对于您添加的日期。您拥有基于 DATE( date_added ) 的那个。在列上做一个函数不能充分利用索引。由于 date() 剥离了日期/时间戳列的任何时间部分，并且您要求添加的所有时间都小于 3 月 30 日，因此添加的日期为 3 月 29 日晚上 11:59:59 仍然小于 3 月 30 日 12 点: 00:00am，因此不需要日期转换。

正如 Ivan(下文)所评论的那样，如果您想要所有钱包 ID 而不管是否有任何付款(借记或贷记)，则将连接更改为 LEFT JOIN。