我在哪里可以找到mysql慢日志？

Question

cww

Asked: 2016-12-31 02:20:29 +0800 CST2016-12-31 02:20:29 +0800 CST 2016-12-31 02:20:29 +0800 CST

选择连接中的慢速子查询

772

我有一个如下所示的慢速子查询（需要 2 分 35.03 秒）

SELECT *
FROM erp_gl_batch_item i
LEFT JOIN erp_gl_batch b
  ON i.gl_batch_id = b.gl_batch_id
LEFT JOIN erp_customer c
  ON i.gl_item_account_no = c.customer_code
LEFT JOIN (SELECT *
    FROM erp_acc_match_invoice mi
    WHERE mi.allocation = 'full') mi
      ON i.gl_reference = mi.reference
LEFT JOIN (SELECT *
    FROM erp_acc_match_invoice mip
    WHERE mip.allocation = 'partial') mip
      ON i.gl_reference = mip.reference
WHERE i.gl_item_account_no LIKE '3001/A01'
AND mip.match_invoice_id IS NULL
AND b.gl_period <= '2016012'
AND i.gl_item_debit <> '0.00'
AND mi.match_invoice_id IS NULL;
89 rows in set (2 min 35.03 sec)

下面是解释输出。

+----+-------------+------------+--------+--------------------------------+--------------------+---------+------------------------------------+-------+-------------------------+
| id | select_type | table      | type   | possible_keys                  | key                | key_len | ref                                | rows  | Extra                   |
+----+-------------+------------+--------+--------------------------------+--------------------+---------+------------------------------------+-------+-------------------------+
|  1 | PRIMARY     | i          | range  | gl_item_account_no,gl_batch_id | gl_item_account_no | 767     | NULL                               |  4713 | Using where             |
|  1 | PRIMARY     | b          | eq_ref | PRIMARY                        | PRIMARY            | 4       | c1xxxtemp_erp.i.gl_batch_id        |     1 | Using where             |
|  1 | PRIMARY     | c          | ref    | customer_code                  | customer_code      | 767     | c1xxxtemp_erp.i.gl_item_account_no |     1 |                         |
|  1 | PRIMARY     | <derived2> | ALL    | NULL                           | NULL               | NULL    | NULL                               | 28965 | Using where; Not exists |
|  1 | PRIMARY     | <derived3> | ALL    | NULL                           | NULL               | NULL    | NULL                               |   149 | Using where; Not exists |
|  3 | DERIVED     | mip        | ref    | allocation                     | allocation         | 33      |                                    |   149 | Using where             |
|  2 | DERIVED     | mi         | ref    | allocation                     | allocation         | 33      |                                    | 15306 | Using where             |
+----+-------------+------------+--------+--------------------------------+--------------------+---------+------------------------------------+-------+-------------------------+

只是想知道子查询和连接是如何工作的，以及它如何影响查询的性能。另外，是否因为连接需要查找第 4,713 x 28,965 = 136,512,045 行？

我已经重写了查询，现在它更快了（0.12 秒），只是想知道它也是子查询，但是这个子查询如何更快地工作。

SELECT *
FROM erp_gl_batch_item i
LEFT JOIN erp_gl_batch b
  ON i.gl_batch_id = b.gl_batch_id
LEFT JOIN erp_customer c
  ON i.gl_item_account_no = c.customer_code
WHERE i.gl_item_account_no LIKE '3001/A01'
AND b.gl_period <= '2016012'
AND i.gl_item_debit <> '0.00'
AND NOT EXISTS (SELECT *
    FROM erp_acc_match_invoice mip
    WHERE mip.allocation = 'partial'
    AND i.gl_reference = mip.reference)
AND NOT EXISTS (SELECT *
    FROM erp_acc_match_invoice mi
    WHERE mi.allocation = 'full'
    AND i.gl_reference = mi.reference)

解释

+----+--------------------+-------+--------+--------------------------------+--------------------+---------+------------------------------------+------+-------------+
| id | select_type        | table | type   | possible_keys                  | key                | key_len | ref                                | rows | Extra       |
+----+--------------------+-------+--------+--------------------------------+--------------------+---------+------------------------------------+------+-------------+
|  1 | PRIMARY            | i     | range  | gl_item_account_no,gl_batch_id | gl_item_account_no | 767     | NULL                               | 4713 | Using where |
|  1 | PRIMARY            | b     | eq_ref | PRIMARY                        | PRIMARY            | 4       | c1xxxtemp_erp.i.gl_batch_id        |    1 | Using where |
|  1 | PRIMARY            | c     | ref    | customer_code                  | customer_code      | 767     | c1xxxtemp_erp.i.gl_item_account_no |    1 |             |
|  3 | DEPENDENT SUBQUERY | mi    | ref    | reference,allocation           | reference          | 767     | c1xxxtemp_erp.i.gl_reference       |    1 | Using where |
|  2 | DEPENDENT SUBQUERY | mip   | ref    | reference,allocation           | reference          | 767     | c1xxxtemp_erp.i.gl_reference       |    1 | Using where |
+----+--------------------+-------+--------+--------------------------------+--------------------+---------+------------------------------------+------+-------------+

更新

CREATE TABLE `erp_gl_batch_item` (
  `gl_batch_item_id` int(11) NOT NULL AUTO_INCREMENT,
  `gl_batch_id` int(11) NOT NULL,
  `gl_line_item` int(11) NOT NULL,
  `gl_item_date` date NOT NULL,
  `gl_item_account_id` int(11) DEFAULT NULL,
  `gl_item_account_no` varchar(255) NOT NULL,
  `gl_reference` varchar(255) NOT NULL,
  `gl_reference2` varchar(255) DEFAULT NULL,
  `gl_tax_code` int(11) DEFAULT NULL,
  `gl_desc` varchar(255) NOT NULL,
  `gl_foreign_currency` varchar(10) DEFAULT NULL,
  `currency_id` int(11) DEFAULT NULL,
  `gl_rate` varchar(10) DEFAULT NULL,
  `gl_item_foreign_amount_debit` decimal(10,2) DEFAULT '0.00',
  `gl_item_foreign_amount_credit` decimal(10,2) NOT NULL DEFAULT '0.00',
  `gl_item_debit` decimal(10,2) DEFAULT '0.00',
  `gl_item_credit` decimal(10,2) DEFAULT '0.00',
  `knockoff` varchar(40) DEFAULT NULL,
  `marked` varchar(10) DEFAULT NULL,
  `gst_log_id` int(11) NOT NULL DEFAULT '0',
  `tax_group_key` int(11) NOT NULL DEFAULT '0',
  `user_created` int(11) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  `user_modified` int(11) DEFAULT NULL,
  `date_modified` datetime DEFAULT NULL,
  PRIMARY KEY (`gl_batch_item_id`),
  KEY `gl_item_account_id` (`gl_item_account_id`),
  KEY `gl_reference` (`gl_reference`),
  KEY `knockoff` (`knockoff`),
  KEY `marked` (`marked`),
  KEY `gl_item_account_no` (`gl_item_account_no`),
  KEY `gl_batch_id` (`gl_batch_id`)
) ENGINE=InnoDB AUTO_INCREMENT=208712 DEFAULT CHARSET=utf8 


CREATE TABLE `erp_gl_batch` (
  `gl_batch_id` int(11) NOT NULL AUTO_INCREMENT,
  `gl_batch_no` int(11) NOT NULL,
  `gl_batch_name` varchar(255) NOT NULL,
  `gl_period` int(11) DEFAULT NULL,
  `gl_bill_type` int(11) NOT NULL,
  `gl_date_start` date NOT NULL,
  `gl_date_end` date NOT NULL,
  `user_created` int(11) DEFAULT NULL,
  `user_modified` int(11) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  `date_modified` datetime DEFAULT NULL,
  PRIMARY KEY (`gl_batch_id`)
) ENGINE=InnoDB AUTO_INCREMENT=282 DEFAULT CHARSET=utf8

CREATE TABLE `erp_customer` (
  `customer_id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_code` varchar(255) NOT NULL,
  `customer_name` varchar(255) NOT NULL,
  `company_reg_no` varchar(255) DEFAULT NULL,
  `billing_address` text,
  `shipping_address` text,
  `contact_name` varchar(255) DEFAULT NULL,
  `contact_phone` varchar(255) DEFAULT NULL,
  `contact_email` varchar(255) DEFAULT NULL,
  `contact_fax` varchar(255) DEFAULT NULL,
  `contact_website` varchar(255) DEFAULT NULL,
  `currency_id` int(11) DEFAULT NULL,
  `credit_term_id` int(11) DEFAULT NULL,
  `credit_limit` decimal(16,4) DEFAULT NULL,
  `gst_no` varchar(255) DEFAULT NULL,
  `tax_code_id` int(11) DEFAULT NULL,
  `po_line_required` tinyint(1) DEFAULT NULL,
  `freeze_account` int(11) DEFAULT NULL,
  `status` varchar(255) DEFAULT NULL,
  `status_id` int(11) DEFAULT NULL,
  `remarks` text,
  `user_created` int(11) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  `user_modified` int(11) DEFAULT NULL,
  `date_modified` datetime DEFAULT NULL
  PRIMARY KEY (`customer_id`),
  KEY `customer_code` (`customer_code`)
) ENGINE=InnoDB AUTO_INCREMENT=1072 DEFAULT CHARSET=utf8

CREATE TABLE `erp_acc_match_invoice` (
  `match_invoice_id` int(11) NOT NULL AUTO_INCREMENT,
  `log_id` int(11) NOT NULL,
  `chartaccount_id` int(11) NOT NULL,
  `batch_item_id` int(11) NOT NULL,
  `inv_date` date NOT NULL DEFAULT '0000-00-00',
  `reference` varchar(255) NOT NULL,
  `pay_reference` varchar(255) DEFAULT NULL,
  `mi_currency` varchar(10) DEFAULT NULL,
  `mi_rate` varchar(10) DEFAULT NULL,
  `mi_amount` decimal(10,2) NOT NULL,
  `inv_amount` decimal(10,2) NOT NULL DEFAULT '0.00',
  `allocation` varchar(10) DEFAULT NULL,
  `user_created` int(11) NOT NULL,
  `date_created` datetime NOT NULL,
  `rvknockoff` varchar(40) DEFAULT NULL,
  `inknockoff` varchar(40) DEFAULT NULL,
  `table_location` varchar(10) NOT NULL,
  PRIMARY KEY (`match_invoice_id`),
  KEY `rvknockoff` (`rvknockoff`),
  KEY `inknockoff` (`inknockoff`),
  KEY `reference` (`reference`),
  KEY `pay_reference` (`pay_reference`),
  KEY `log_id` (`log_id`),
  KEY `allocation` (`allocation`)
) ENGINE=InnoDB AUTO_INCREMENT=37368 DEFAULT CHARSET=utf8

1 个回答

Voted

ypercubeᵀᴹ · Answer 1 · 2016-12-31T02:46:52+08:00

您不需要将它们作为子查询（派生表）。第一个例如：

LEFT JOIN (SELECT *
    FROM erp_acc_match_invoice mi
    WHERE mi.allocation = 'full') mi
      ON i.gl_reference = mi.reference

可以重写为：

LEFT JOIN erp_acc_match_invoice mi
    ON  mi.allocation = 'full'
    AND i.gl_reference = mi.reference

第二个相应。这将避免子查询的任何潜在具体化并产生与您的NOT EXISTS方法类似的计划（这也是编写反连接的好方法）。

笔记：

上的索引(allocation, reference)将提高效率 - 两种查询/方法。
假设这(match_invoice_id)是不可为空的列并考虑NOT EXISTS查询，您可以将两者替换AND mi.match_invoice_id IS NULL为AND mi.reference IS NULL.

所以整个查询变成：

SELECT *
FROM erp_gl_batch_item i
  LEFT JOIN erp_gl_batch b
    ON i.gl_batch_id = b.gl_batch_id
  LEFT JOIN erp_customer c
    ON i.gl_item_account_no = c.customer_code
  LEFT JOIN erp_acc_match_invoice mi
    ON  mi.allocation = 'full'
    AND i.gl_reference = mi.reference
  LEFT JOIN erp_acc_match_invoice mip
    ON  mip.allocation = 'partial'
    AND i.gl_reference = mip.reference
WHERE i.gl_item_account_no LIKE '3001/A01'
  AND mip.reference IS NULL
  AND b.gl_period <= '2016012'
  AND i.gl_item_debit <> '0.00'
  AND mi.reference IS NULL ;

补充说明：

为什么LIKE使用而不是i.gl_item_account_no = '3001/A01'？
这b.gl_period <= '2016012'使得工作成为LEFT JOIN可能。erp_gl_batch bINNER JOIN

选择连接中的慢速子查询

更新

连接到 PostgreSQL 服务器：致命：主机没有 pg_hba.conf 条目

如何让sqlplus的输出出现在一行中？

选择具有最大日期或最晚日期的日期

如何列出 PostgreSQL 中的所有模式？

列出指定表的所有列

如何在不修改我自己的 tnsnames.ora 的情况下使用 sqlplus 连接到位于另一台主机上的 Oracle 数据库

你如何mysqldump特定的表？

使用 psql 列出数据库权限

如何从 PostgreSQL 中的选择查询中将值插入表中？

如何使用 psql 列出所有数据库和表？

选择连接中的慢速子查询

更新

1 个回答

相关问题