Mysql

選擇連接中的慢子查詢

  • December 26, 2019

我有一個如下的慢子查詢（需要 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

您不需要將這些作為子查詢（派生表）。例如第一個：

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 b``INNER JOIN

引用自：https://dba.stackexchange.com/questions/159559

相關問答

Mysql

如何對另一個查詢結果進行查詢操作

  • December 26, 2019
檢視問答
Mysql

為多個 WHERE EXISTS 子查詢創建索引

  • November 24, 2019
檢視問答
Mysql

在子查詢中分組

  • October 18, 2018
檢視問答
Mysql

從 MySQL 表中查找最接近的值

  • July 12, 2018
檢視問答
Mysql

用 JOIN 替換子查詢 - MYSQL

  • April 14, 2018
檢視問答
Mysql

通過最近的匹配連接兩個表

  • January 7, 2018
檢視問答