Onde posso encontrar o log lento do 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

Subconsulta lenta na junção selecionada

772

Eu tenho uma subconsulta lenta como abaixo (levando 2 minutos e 35,03 segundos)

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)

Abaixo está a saída de explicação.

+----+-------------+------------+--------+--------------------------------+--------------------+---------+------------------------------------+-------+-------------------------+
| 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             |
+----+-------------+------------+--------+--------------------------------+--------------------+---------+------------------------------------+-------+-------------------------+

Só quero saber como funciona a subconsulta e junção e como isso afeta o desempenho da consulta. Além disso, é por causa da junção que ele precisa procurar a linha 4.713 x 28.965 = 136.512.045 linhas?

Eu reescrevi a consulta e ela está mais rápida agora (0,12 s), apenas me pergunto se é uma subconsulta também, mas como essa subconsulta funciona mais rápido.

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)

Explique

+----+--------------------+-------+--------+--------------------------------+--------------------+---------+------------------------------------+------+-------------+
| 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 |
+----+--------------------+-------+--------+--------------------------------+--------------------+---------+------------------------------------+------+-------------+

Atualizar

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 respostas

Voted

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

Você não precisa tê-los como subconsultas (tabelas derivadas). A primeira por exemplo:

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

pode ser reescrita como:

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

e o segundo em conformidade. Isso evitaria qualquer possível materialização das subconsultas e produziria planos semelhantes ao seu NOT EXISTSmétodo (que também é um bom método para escrever anti-junções).

Notas:

Um índice (allocation, reference)melhoraria a eficiência - de ambas as consultas/métodos.
Supondo que (match_invoice_id)seja uma coluna não anulável e considerando a NOT EXISTSconsulta, você pode substituir os dois AND mi.match_invoice_id IS NULLpor AND mi.reference IS NULL.

Portanto, toda a consulta se torna:

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 ;

Notas Adicionais:

Por que é LIKEusado e não i.gl_item_account_no = '3001/A01'?
O b.gl_period <= '2016012'torna o LEFT JOINtrabalho erp_gl_batch bum an INNER JOIN.

Subconsulta lenta na junção selecionada

Atualizar

conectar ao servidor PostgreSQL: FATAL: nenhuma entrada pg_hba.conf para o host

Como fazer a saída do sqlplus aparecer em uma linha?

Selecione qual tem data máxima ou data mais recente

Como faço para listar todos os esquemas no PostgreSQL?

Listar todas as colunas de uma tabela especificada

Como usar o sqlplus para se conectar a um banco de dados Oracle localizado em outro host sem modificar meu próprio tnsnames.ora

Como você mysqldump tabela (s) específica (s)?

Listar os privilégios do banco de dados usando o psql

Como inserir valores em uma tabela de uma consulta de seleção no PostgreSQL?

Como faço para listar todos os bancos de dados e tabelas usando o psql?

Subconsulta lenta na junção selecionada

Atualizar

1 respostas

relate perguntas