Si has leído el manual de MySQL habrás visto el ORDER BY RAND()para aleatorizar las filas y usar el LIMIT 1 para tomar sólo una de las filas.
SELECT name FROM random ORDER BY RAND() LIMIT 1;
Este ejemplo funciona bien y es rápido si sólo tienes, digamos, 1000 filas. No lo olvides: sólo ordenamos para tirar casi todas las filas.
Nunca me ha gustado. Y hay mejores maneras de hacerlo. Sin una ordenación.Siempre que tengamos una clave primaria numérica.
Para los primeros ejemplos suponemos que el be ID está empezando en 1 y no tenemos huecos entre 1 y el valor máximo del ID.
Primera idea: Podemos simplificar todo el trabajo si calculamos el ID de antemano en la aplicación.
SELECT MAX(id) FROM random;## generate random id in applicationSELECT name FROM random WHERE id = <random-id>
Como MAX(id) == COUNT(id) simplemente generamos un número aleatorio entre1 y MAX(id) y lo pasamos a la base de datos para recuperar la fila aleatoria.
El primer SELECT es un NO-OP y se optimiza. El segundo es un eq_refcontra un valor constante y también muy rápido.
Pasa el trabajo a la base de datos
¿Pero es realmente necesario hacerlo en la aplicación? ¿No podemos hacerlo en la base de datos?
Ok, ahora sabemos como generar el ID aleatorio, pero ¿como obtener la fila?
NO, NO, NO. No vayas por este camino. La razón es que el SELECT de la cláusula WHERE se ejecuta por cada fila que obtiene el SELECT externo, lo que nos lleva a obtener de 0 a 4091 filas, dependiendo de la suerte que tengamos.
Necesitamos una forma de asegurarnos de que el ID aleatorio sólo se genera una vez:
El SELECT interno está generando una tabla TEMPORAL constante y el JOIN está seleccionando sólo una fila. Perfecto.
Sin ordenación, sin aplicación, la mayoría de las partes de la consulta se optimizan.
Añadir agujeros a los números
Para generalizar la última solución añadimos la posibilidad de agujeros, como cuando DELETE filas.
El JOIN ahora añade todos los IDs que son mayores o iguales que nuestro valor aleatorio y selecciona sólo los vecinos directos si no es posible una coincidencia directa. PERO en cuanto se encuentra una fila, nos detenemos (el LIMIT 1). Y leemos las filas según el índice (ORDER BY id ASC). Como estamos usando >= en lugar de un = podemos deshacernos de un el CEIL y obtener el mismo resultado con un poco menos de trabajo.
Distribución igual
En cuanto la distribución de los IDs ya no es igual nuestra selección de filas tampoco es realmente aleatoria.
La función RAND está generando IDs como el 9 al 15 que todos llevan al id 16 a ser seleccionado como el siguiente número más alto.
No hay una solución real para este problema, pero tus datos son en su mayoría constantes puedes añadir una tabla de mapeo que mapea el número de fila al id:
El row_id está ahora libre de agujeros de nuevo y podemos ejecutar nuestra consulta aleatoria de nuevo:
Después de 1000 búsquedas vemos de nuevo una distribución equitativa:
Manteniendo los agujeros
Tomemos las tablas como antes:
Cuando cambiemos algo en r2 queremos que r2_equi_dist se actualice también.
INSERTAR es bastante sencillo, en DELETE tenemos que actualizar el equi-dist-id para mantener la configuración sin agujeros:
UPDATE es sencillo de nuevo. Sólo tenemos que mantener la restricción de clave foránea:
Múltiples filas a la vez
Si quieres que te devuelvan más de una fila, puedes hacerlo:
- ejecutar la consulta varias veces
- escribir un procedimiento almacenado que ejecute la consulta y almacene el resultado en una tabla temporal
- hacer un UNION
un procedimiento almacenado
Los procedimientos almacenados le proporcionan las estructuras que conoce de su lenguaje de programación favorito:
- bucles
- estructuras de control
- procedimientos
- …
Para esta tarea sólo necesitamos un LOOP:
Dejo al lector que solucione los problemas:
- Usar SQL dinámico y pasar el nombre de la tabla temporal
- Usar un índice UNIQUE en la tabla y capturar la violación de la clave UNIQUE para eliminar los posibles duplicados en el conjunto de resultados
Rendimiento
Ahora vamos a ver qué pasa con nuestro rendimiento. Tenemos 3 consultas diferentes para resolver nuestros problemas.
- Q1. ORDER BY RAND()
- Q2. RAND() * MAX(ID)
- Q3. RAND() * MAX(ID) + ORDER BY ID
Se espera que Q1 cueste N * log2(N), Q2 y Q3 son casi constantes.
Para obtener valores reales llenamos la tabla con N filas ( de mil a un millón)y ejecutamos cada consulta 1000 veces.