Se você ler o manual do MySQL você pode ter visto o ORDER BY RAND()para randomizar as linhas e usando o LIMIT 1 para pegar apenas uma das linhas.
SELECT name FROM random ORDER BY RAND() LIMIT 1;
Este exemplo funciona bem e é rápido se você só tiver 1000 linhas. Assim que você tiver 10000 linhas, o overhead para ordenar as linhas torna-se importante. Não se esqueça: nós só ordenamos para jogar quase todas as linhas fora.
Eu nunca gostei disso. E há formas melhores de o fazer. Sem ordenação. Desde que tenhamos uma chave primária numérica.
Para os primeiros exemplos assumimos que o ID começa em 1 e temos noholes entre 1 e o valor máximo do ID.
Primeira ideia: Podemos simplificar todo o trabalho se calcularmos o ID antes na aplicação.
SELECT MAX(id) FROM random;## generate random id in applicationSELECT name FROM random WHERE id = <random-id>
Como MAX(id) == COUNT(id) apenas geramos um número aleatório entre1 e MAX(id) e o passamos para o banco de dados para recuperar a linha aleatória.
O primeiro SELECT é um NO-OP e é otimizado para longe. O segundo é um eq_refagainst um valor constante e também muito rápido.
move o trabalho para a base de dados
Mas é realmente necessário fazê-lo na aplicação ? Não o podemos fazer na base de dados ?
Ok, agora sabemos como gerar o ID aleatório, mas como obter a linha ?
NO, NO, NO. Não vás por aqui. Esta é a forma mais óbvia, mas também a mais errada de fazê-lo. A razão: o SELECT na cláusula WHERE é executado para cada linha que o SELECT externo está a buscar. Isto leva a 0 a 4091 linhas, dependendo da sua sorte.
Nós precisamos de uma forma de ter a certeza de que o ID aleatório é gerado apenas uma vez:
O SELECT interno está a gerar uma tabela TEMPORÁRIA constante e o JOIN está a seleccionar apenas em singlerow. Perfect.
No Sorting, No Application, Most parts of the query optimisized away.
adding holes to the numbers
Para generalizar a última solução adicionamos a possibilidade de furos, como quando você DELETE rows.
O JOIN agora adiciona todos os IDs que são maiores ou iguais ao nosso valor aleatório e seleciona apenas o neighboorif directo não é possível uma correspondência directa. MAS assim que uma linha é encontrada, nós paramos (o LIMIT 1). E nós lemos as linhas de acordo com o índice (ORDER BY id ASC). Como estamos usando >= ao invés de um = podemos nos livrar de um CEIL e obter o mesmo resultado com um pouco menos de trabalho.
Equalquer Distribuição
As soon as the distribution of the IDs is not more equal anymore our selection of rows is not really random either.
The RAND function is generating IDs like 9 to 15 which all lead to the id 16 to be selected as the next higher number.
Não há solução real para este problema, mas seus dados são na maioria das vezes constantes você pode adicionar uma tabela de mapeamento que mapeia o número de linha ao id:
O row_id agora está novamente livre de buracos e podemos executar nossa consulta aleatória novamente:
A partir de 1000 fetches vemos novamente uma distribuição igual:
Manter os buracos
Vamos pegar as tabelas como antes:
Quando mudamos algo em r2 queremos que r2_equi_dist seja atualizado também.
INSERT é bastante simples, no DELETE temos de actualizar o equi-dist-id para manter a configuração sem buracos:
UPDATE é novamente simples. Só temos de manter a restrição Foreign Key:
Multiple Rows at once
Se você quiser ter mais de uma linha devolvida, você pode:
- executar a Consulta várias vezes
- escrever um procedimento armazenado que está executando a consulta e armazena o resultado em uma tabela temporária
- fazer uma UNION
um procedimento armazenado
Procedimentos armazenados fornecem-lhe as estruturas que você conhece da sua linguagem de programação favorita:
- loops
- estruturas de controlo
- procedimentos
- …
Para esta tarefa só precisamos de um LOOP:
Deixo ao leitor a tarefa de corrigir os problemas:
- utilizar SQL dinâmico e passar no nome da tabela temporária
- utilizar um índice UNIQUE na tabela e pegar a violação da chave UNIQUE para remover as possíveis duplicatas no conjunto de resultados
Desempenho
Agora vamos ver o que acontece com o nosso desempenho. Temos 3 consultas diferentes para resolver nossos problemas.
- Q1. ORDEM POR RAND()
- Q2. RAND() * MAX(ID)
- Q3. RAND() * MAX(ID) + ORDEM POR ID
Q1 é esperado que o custo de N * log2(N), Q2 e Q3 são quase constantes.
A obtenção de valores reais nós preenchemos a tabela com N linhas (mil a um milhão) e executamos cada consulta 1000 vezes.