Início / dba / Perguntas / 52043
Accepted
Abdul Manaf
Asked: 2013-10-24 03:45:37 +0800 CST

Otimize a consulta MySQL Self JOIN

  • 772

Eu tenho uma consulta que está sendo executada em mais de 15 segundos

SELECT 
        t1.`ST_StockCode`, t2.`SM_StockCode`, t2.`ST_ItemSize`
    FROM
        `stocks` AS t1,
        `stocks` AS t2
    WHERE
        t1.`ST_StockCode` = t2.`SM_StockCode`
    GROUP BY t1.`ST_StockCode`
    ORDER BY t1.`id` ASC

Como posso reescrever/otimizar a consulta para acelerar o tempo de execução.

Estrutura da tabela

CREATE TABLE `stocks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ST_StockCode` int(11) NOT NULL,
  `SM_StockCode` int(11) NOT NULL,
  `ST_ItemSize` decimal(18,2) DEFAULT '0.00',
  PRIMARY KEY (`id`),
  KEY `stockcode` (`ST_StockCode`),
  KEY `sm_stockcode` (`SM_StockCode`)
) ENGINE=InnoDB

EXPLICAR O PLANO

+----+-------------+-------+------+--------------------+-----------+---------+--------------------+---------+---------------------------------+
| id | select_type | table | type | possible_keys      | key       | key_len | ref                | rows    | Extra                           |
+----+-------------+-------+------+--------------------+-----------+---------+--------------------+---------+---------------------------------+
|  1 | SIMPLE      | t2    | ALL  | sm_stockcode       | NULL      | NULL    | NULL               | 1000545 | Using temporary; Using filesort |
|  1 | SIMPLE      | t1    | ref  | stockcode,idx_test | stockcode | 4       | lc.t2.SM_StockCode |       4 | Using index                     |
+----+-------------+-------+------+--------------------+-----------+---------+--------------------+---------+---------------------------------+

ATUALIZE ALGUMAS LINHAS

SELECT * FROM stocks LIMIT 10;
+----+--------------+--------------+-------------+
| id | ST_StockCode | SM_StockCode | ST_ItemSize |
+----+--------------+--------------+-------------+
|  1 |       679783 |       678649 |        7.00 |
|  2 |       679789 |       688622 |        7.00 |
|  3 |       679792 |       679793 |        8.00 |
|  4 |       679792 |       686376 |        8.00 |
|  5 |       679793 |       679792 |        7.00 |
|  6 |       679793 |       686376 |        8.00 |
|  7 |       679795 |       679796 |        8.00 |
|  8 |       679796 |       679795 |        7.00 |
|  9 |       679797 |       617114 |        7.00 |
| 10 |       679797 |       627339 |        7.00 |
+----+--------------+--------------+-------------+

PARA ypercubo

 SELECT * FROM similar_stocks WHERE ST_StockCode = 679792 OR SM_StockCode = 679792 ;
+-------+--------------+--------------+-------------+
| id    | ST_StockCode | SM_StockCode | ST_ItemSize |
+-------+--------------+--------------+-------------+
|     3 |       679792 |       679793 |        8.00 |
|     4 |       679792 |       686376 |        8.00 |
|     5 |       679793 |       679792 |        7.00 |
|  4774 |       686376 |       679792 |        7.00 |
| 50028 |       679792 |       679793 |        8.00 |
| 50029 |       679792 |       686376 |        8.00 |
| 50030 |       679793 |       679792 |        7.00 |
| 52798 |       686376 |       679792 |        7.00 |
+-------+--------------+--------------+-------------+
mysql performance

1 respostas

  1. Best Answer

    Vamos começar dissecando a consulta original, com base nos dados de exemplo fornecidos:

    SQL Fiddle

    Configuração do esquema do MySQL 5.5.32 :

    CREATE TABLE Stocks
    (`id` int, `ST_StockCode` int, `SM_StockCode` int, `ST_ItemSize` int)
;

INSERT INTO Stocks
    (`id`, `ST_StockCode`, `SM_StockCode`, `ST_ItemSize`)
VALUES
    (1, 679783, 678649, 7.00),
    (2, 679789, 688622, 7.00),
    (3, 679792, 679793, 8.00),
    (4, 679792, 686376, 8.00),
    (5, 679793, 679792, 7.00),
    (6, 679793, 686376, 8.00),
    (7, 679795, 679796, 8.00),
    (8, 679796, 679795, 7.00),
    (9, 679797, 617114, 7.00),
    (10, 679797, 627339, 7.00)
;

    Consulta 1 :

    SELECT 
        t1.`ST_StockCode`, t2.`SM_StockCode`, t2.`ST_ItemSize`
    FROM
        `stocks` AS t1,
        `stocks` AS t2
    WHERE
        t1.`ST_StockCode` = t2.`SM_StockCode`
    GROUP BY t1.`ST_StockCode`
    ORDER BY t1.`id` ASC

    Resultados :

    | ST_STOCKCODE | SM_STOCKCODE | ST_ITEMSIZE |
|--------------|--------------|-------------|
|       679792 |       679792 |           7 |
|       679793 |       679793 |           8 |
|       679795 |       679795 |           7 |
|       679796 |       679796 |           8 |

    A primeira coisa a observar é que, sendo uma junção interna, os dois valores ST_StockCode e SM_StockCode são sempre idênticos. Nos exemplos a seguir, menciono apenas um deles, pois é fácil adicionar o que falta.

    Em segundo lugar, esta consulta está usando uma extensão não SQL Standard para a cláusula GROUP BY. A consulta é agrupada por ST_StockCode e, em seguida, faz referência a duas outras colunas sem uma agregação. O MySQL retornará para essas colunas o primeiro valor que encontrar. Ele não verifica se os valores são distintos e você pode obter resultados diferentes a cada execução. Se assumirmos que o criador da consulta original estava ciente desse comportamento, podemos fazer isso:

    Consulta 2 :

    SELECT SM_StockCode, MIN(ST_ItemSize) ST_ItemSize
  FROM stocks AS t1
 GROUP BY SM_StockCode;

    Resultados :

    | SM_STOCKCODE | ST_ITEMSIZE |
|--------------|-------------|
|       617114 |           7 |
|       627339 |           7 |
|       678649 |           7 |
|       679792 |           7 |
|       679793 |           8 |
|       679795 |           7 |
|       679796 |           8 |
|       686376 |           8 |
|       688622 |           7 |

    Isso retorna para cada SM_StockCode o ST_ItemSize mínimo (em vez de aleatório). No entanto, ele retorna potencialmente mais linhas do que a consulta original porque pode haver SM_StockCodes que não possuem um ST_StockCode correspondente (como nos dados de exemplo fornecidos). No entanto, isso é facilmente corrigido:

    Consulta 3 :

    SELECT SM_StockCode, MIN(ST_ItemSize) ST_ItemSize
  FROM stocks AS t1
 WHERE EXISTS(SELECT 1 FROM stocks AS t2 WHERE t2.ST_StockCode = t1.SM_StockCode)
 GROUP BY SM_StockCode;

    Resultados :

    | SM_STOCKCODE | ST_ITEMSIZE |
|--------------|-------------|
|       679792 |           7 |
|       679793 |           8 |
|       679795 |           7 |
|       679796 |           8 |

    Agora, apenas SM_StockCodes que possuem um ST_StockCodes correspondente são retornados. (Você precisa duplicar a coluna SM_StockCode se realmente precisar desse valor duas vezes.)

    Com a consulta escrita desta forma, a estratégia de indexação torna-se bastante óbvia:

    Crie um índice em ST_StockCode para suportar a pesquisa EXISTS e crie um segundo índice em SM_StockCode, ST_ItemSize para suportar GROUP BY.

    Se você tem certeza de que cada SM_StockCode tem um ST_StockCode correspondente (por exemplo, porque uma chave estrangeira foi declarada entre os dois ou porque dois dos analistas juram que esse é sempre o caso), você pode encurtar ainda mais a consulta:

    Consulta 4 :

    SELECT DISTINCT SM_StockCode, ST_ItemSize
  FROM stocks;

    Resultados :

    | SM_STOCKCODE | ST_ITEMSIZE |
|--------------|-------------|
|       678649 |           7 |
|       688622 |           7 |
|       679793 |           8 |
|       686376 |           8 |
|       679792 |           7 |
|       679796 |           8 |
|       679795 |           7 |
|       617114 |           7 |
|       627339 |           7 |

    No entanto, como no exemplo de trecho de dados de 10 linhas aleatórias, essa condição não é fornecida, o resultado acima não corresponde a esse caso.

    • 3

relate perguntas