Início / dba / Perguntas / 13940
Accepted
Sebastian
Asked: 2012-02-27 06:10:17 +0800 CST

MySQL: ORDER BY inútil?

  • 772
CREATE TEMPORARY TABLE tt;
INSERT INTO tt; many times
SELECT FROM tt ORDER BY id LIMIT ?,1000;

A tabela temporária é criada no início do script com uma coluna "id" auto_increment (e outras). A parte 1 do script preenche a tabela usando vários SELECTs. A parte 2 deve processar todas as linhas selecionadas em blocos de 1.000, mas nunca mais gravará a tabela.

Eu suponho que esses dois comandos são os mesmos

SELECT FROM tt ORDER BY id LIMIT ?,1000;
SELECT FROM tt LIMIT ?,1000;

mas isso é verdade? Existe alguma chance de que o mySQL recupere as linhas em uma ordem diferente em vários SELECTs, mesmo que a tabela não esteja escrita entre eles?

Classificar a tabela temporária é a parte mais longa durante a leitura e ficaria feliz em me livrar da parte ORDER BY, mas não quero perder nenhum registro porque a ordem mudou entre SELECT LIMIT 0,1000 e SELECT LIMIT 1000, 1000.

mysql order-by

3 respostas

  1. Isso não está diretamente relacionado ao ORDER BY, mas tenha cuidado ao usar o padrão LIMIT N,M. Isso geralmente é usado para paginar resultados, por exemplo

    ... limit 0,10;
... limit 10,10;
... limit 20,10;

    Cada vez que o mysql irá "selecionar" as primeiras N linhas e apenas retornar as últimas M para o cliente. À medida que você se aprofunda, seleciona mais e mais linhas, apenas para serem descartadas.

    Resumindo, cada "página" demora mais para carregar. Se seus conjuntos de dados forem pequenos e as consultas estiverem usando índices bem, isso pode parecer irrelevante, mas o efeito ficará evidente à medida que você crescer. Eu sinto que geralmente é melhor colocar essas restrições na cláusula where. por exemplo

    ... where id between 0 and 10;
... where id between 11 and 20;
... where id between 21 and 30;

    Finalmente, se você está preocupado com a ordem para a limitação efetivamente cria as restrições de alcance corretas para você, então essa preocupação é completamente eliminada para começar.

    • 2
  2. Best Answer

    Eu acho que isso depende do mecanismo de armazenamento. Eu posso dizer para MyISAM, um select sem qualquer ordem retornará as linhas sequencialmente conforme elas ocorrem no arquivo (mesmo se mantidas na memória).

    Como você ordenou os dados durante a gravação, eles serão ordenados por id dentro do MyISAM-temptables. Portanto, você pode assumir com segurança que sempre retornará resultados corretos, desde que você não exclua de myISAM-temp.

    Mas a nova questão é - o tentable foi criado no formato myIsam? Presumo que o mecanismo de armazenamento padrão será usado. Isso costumava ser MyISAM, mas foi alterado para InnoDB após o MySQL 5.1.

    Não sei como o innodb lidaria com uma seleção não classificada.

    -- editar

    http://dev.mysql.com/doc/refman/5.1/en/internal-temporary-tables.html

    Em tabelas temporárias grandes (no disco), myISAM é usado. Não é um comportamento intencional que as linhas sejam servidas sequencialmente na ordem em que foram escritas, mas é por design (conforme documentado) confiável, desde que a tabela myISAM seja apenas anexada.

    Isso provavelmente não é verdade para qualquer "tabela com hash" como InnoDB, BDB e, eventualmente, MEMORY. Mas quando a tabela de memória é convertida em "tabela no disco", a ordem atual é "congelada no disco" e, portanto, confiável novamente.

    Mas acho que temos outro tipo de problema aqui:

    • O problema 'real' parece ser que o seu "ordenar por" parece muito lento e você gostaria de contornar isso. Você por acaso sabe o tamanho da sua tabela e sessões max_heap_table_size? Isso lhe dá uma dica se sua tabela foi convertida para myisam. Você também pode brincar com o método Index-Hash (BTREE, HASH) para procurar otimização.

    • Você está ciente de que LIMIT N,M ficará dolorosamente lento porque precisa selecionar N linhas, descartá-las e, em seguida, selecionar M linhas para retornar?

    • 1

  3. Em vez de sua consulta original

    SELECT FROM tt ORDER BY id LIMIT ?,1000;

    você pode querer refatorar a consulta para aplicar a ordem apenas nas chaves e juntar as chaves de volta à tabela original:

    SELECT tt.*
FROM
(
    SELECT id FROM tt
    ORDER BY id LIMIT ?,1000
) ttkeys
LEFT JOIN tt USING (id);

    Isso pode ser feito para forçar 1.000 chaves a se juntarem apenas à tabela original.

    Eu uso LEFT JOINem vez de INNER JOINporque LEFT JOINmantém a ordem das chaves após a conclusão da junção, enquanto INNER JOINtende a desfazer a ordem na subconsulta.

    Se fazer isso em uma tabela temporária interna tiver problemas, tente com uma tabela temporária externa da seguinte maneira:

    CREATE TEMPORARY TABLE tt;
INSERT INTO tt; many times
CREATE TEMPORARY TABLE ttkeys
    SELECT id FROM tt
    ORDER BY id LIMIT 1000
;
ALTER TABLE ttkeys ADD PRIMARY KEY (id);
SELECT tt.* FROM ttkeys
LEFT JOIN tt USING (id);
    • 1

relate perguntas