Como otimizar o operador ORDER BY RAND()?

Otimizar ORDER BY RAND() é difícil, pois requer uma seleção completa de dados para gerar um número aleatório para cada linha. As abordagens típicas:

1. Seleção de um identificador aleatório:

   • Obter o número de linhas na tabela.
   • Gerar um número aleatório entre 0 e N-1 (ou 1 e N).
   • Selecionar a linha com o deslocamento correspondente ou OFFSET.

   SELECT COUNT(*) FROM your_table;
   -- Assume-se que o total de linhas = N
   
   SELECT * FROM your_table LIMIT 1 OFFSET floor(random() * N);
   

   Este método é adequado para selecionar uma ou algumas linhas aleatórias. Não é eficiente para um grande número de linhas.

2. Seleção aleatória por intervalo de id:

   • Encontrar o id mínimo e máximo.
   • Gerar um número aleatório nesse intervalo.
   • Selecionar a linha com id >= número_aleatório, usando LIMIT.

   SELECT MIN(id), MAX(id) FROM your_table;
   -- Assume-se que min_id, max_id foram obtidos
   
   -- Na aplicação, gerar um id aleatório no intervalo [min_id, max_id]
   -- Por exemplo: id_aleatório = min_id + floor(random() * (max_id - min_id + 1))
   
   SELECT * FROM your_table WHERE id >= id_aleatório LIMIT 1;
   

   Pode omitir linhas se houver lacunas em id.

3. Criação de uma tabela temporária ou uso de subconsulta com ordenação por número aleatório:

   • Selecionar um subconjunto de dados ou apenas id em uma subconsulta.
   • Aplicar ORDER BY RAND() a esse subconjunto.

   SELECT *
   FROM your_table AS t1 JOIN (SELECT id FROM your_table ORDER BY RAND() LIMIT 100) AS t2
   ON t1.id = t2.id;
   

   Reduz a carga se LIMIT for aplicado à subconsulta.

4. Uso de um campo adicional com número aleatório:

   • Adicionar um campo na tabela para armazenar um número aleatório.
   • Atualizar periodicamente esse campo para todas as linhas.
   • Ordenar por esse campo.

   -- Adicionar campo
   ALTER TABLE your_table ADD random_sort_key FLOAT;
   
   -- Atualização periódica
   UPDATE your_table SET random_sort_key = RAND();
   
   -- Seleção
   SELECT * FROM your_table ORDER BY random_sort_key LIMIT 10;
   

   Requer alterações no esquema e no processo de atualização em segundo plano.

5. Algoritmo de "embaralhamento" (Fisher-Yates) fora do banco de dados:

   • Selecionar todos os id (ou todos os dados, se forem pequenos).
   • Embaralhar a lista na aplicação.
   • Selecionar os primeiros N elementos.

   // Assume-se que ids é []int
   // ids = ... // Obter ids do banco de dados
   
   rand.Shuffle(len(ids), func(i, j int) {
       ids[i], ids[j] = ids[j], ids[i]
   })
   
   // Agora os primeiros N elementos de ids são aleatórios
   // Podem ser usados para selecionar linhas completas
   // SELECT * FROM your_table WHERE id IN (...)
   

   Adequado apenas para tabelas com poucas linhas.

A escolha do método ótimo depende do tamanho da tabela, do número de linhas aleatórias necessárias e do sistema de gerenciamento de banco de dados específico. Para a maioria dos casos, quando é necessário selecionar um pequeno número de linhas aleatórias de uma tabela grande, a combinação de obter os id com um OFFSET ou WHERE id IN (...) será mais eficiente do que ordenar por RAND() diretamente.