random mysql

¿Cómo funciona MySQL''s ORDER BY RAND()?

Question

He estado investigando y probando cómo hacer una selección aleatoria rápida en MySQL. En el proceso, he enfrentado algunos resultados inesperados y ahora no estoy completamente seguro de saber cómo funciona ORDER BY RAND ().

Siempre pensé que cuando haces ORDER BY RAND () sobre la mesa, MySQL agrega una nueva columna a la tabla que está llena de valores aleatorios, luego ordena los datos por esa columna y luego, por ejemplo, tomas el valor anterior que llegó aleatoriamente . He hecho muchas búsquedas en Google y finalmente encontré que la consulta que Jay ofrece en su blog es la solución más rápida:

SELECT * FROM Table T JOIN (SELECT CEIL(MAX(ID)*RAND()) AS ID FROM Table) AS x ON T.ID >= x.ID LIMIT 1;

Mientras que ORDER BY RAND () común toma 30-40 segundos en mi tabla de prueba, su consulta hace el trabajo en 0.1 segundos. Él explica cómo funciona esto en el blog, así que me saltearé esto y finalmente me moveré a lo extraño.

Mi tabla es una tabla común con una id PRIMARY KEY y otras cosas no indexadas como username , age , etc. Esto es lo que estoy luchando para explicar

SELECT * FROM table ORDER BY RAND() LIMIT 1; /*30-40 seconds*/
SELECT id FROM table ORDER BY RAND() LIMIT 1; /*0.25 seconds*/
SELECT id, username FROM table ORDER BY RAND() LIMIT 1; /*90 seconds*/

Esperaba ver aproximadamente el mismo tiempo para las tres consultas, ya que siempre selecciono una sola columna. Pero por alguna razón esto no sucedió. Por favor, hágame saber si tiene alguna idea sobre esto. Tengo un proyecto donde necesito hacerlo rápido ORDER BY RAND () y personalmente prefiero usar

SELECT id FROM table ORDER BY RAND() LIMIT 1;
SELECT * FROM table WHERE id=ID_FROM_PREVIOUS_QUERY LIMIT 1;

que, sí, es más lento que el método de Jay, sin embargo es más pequeño y más fácil de entender. Mis consultas son bastante grandes con varias JOIN y con la cláusula WHERE y, aunque el método de Jay todavía funciona, la consulta se vuelve realmente grande y compleja porque necesito usar todas las JOIN y WHERE en la sub solicitud JOINed (llamada x en su consulta).

¡Gracias por tu tiempo!

Answer 1

Puede tener que ver con la indexación. id está indexado y es de acceso rápido, mientras que agregar nombre de usuario al resultado significa que necesita leerlo de cada fila y ponerlo en la tabla de memoria. Con * también tiene que leer todo en la memoria, pero no necesita saltar el archivo de datos, lo que significa que no hay tiempo perdido buscando. Esto hace una diferencia solo si hay columnas de longitud variable, lo que significa que tiene que verificar la longitud, luego omita esa longitud, en lugar de saltarse una longitud establecida (o 0) entre cada fila

¡Practica es mejor que todas las teorías! ¿Por qué no solo para verificar los planes? :)

mysql> explain select name from avatar order by RAND() limit 1;
+----+-------------+--------+-------+---------------+-----------------+---------+------+-------+----------------------------------------------+
| id | select_type | table  | type  | possible_keys | key             | key_len | ref  | rows  | Extra                                        |
+----+-------------+--------+-------+---------------+-----------------+---------+------+-------+----------------------------------------------+
|  1 | SIMPLE      | avatar | index | NULL          | IDX_AVATAR_NAME | 302     | NULL | 30062 | Using index; Using temporary; Using filesort |
+----+-------------+--------+-------+---------------+-----------------+---------+------+-------+----------------------------------------------+
1 row in set (0.00 sec)

mysql> explain select * from avatar order by RAND() limit 1;
+----+-------------+--------+------+---------------+------+---------+------+-------+---------------------------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows  | Extra                           |
+----+-------------+--------+------+---------------+------+---------+------+-------+---------------------------------+
|  1 | SIMPLE      | avatar | ALL  | NULL          | NULL | NULL    | NULL | 30062 | Using temporary; Using filesort |
+----+-------------+--------+------+---------------+------+---------+------+-------+---------------------------------+
1 row in set (0.00 sec)

 mysql> explain select name, experience from avatar order by RAND() limit 1;
+----+-------------+--------+------+--------------+------+---------+------+-------+---------------------------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows  | Extra                           |
+----+-------------+--------+------+---------------+------+---------+------+-------+---------------------------------+
|  1 | SIMPLE      | avatar | ALL  | NULL          | NULL | NULL    | NULL | 30064 | Using temporary; Using filesort |
+----+-------------+--------+------+---------------+------+---------+------+-------+---------------------------------+

Answer 2

¿Por qué no agrega una id, username índice id, username de id, username en la tabla para ver si eso fuerza a mysql a usar el índice en lugar de solo una clasificación de archivos y una tabla temporal?

Answer 3

Puedo decirte por qué el SELECT id FROM ... es mucho más lento que los otros dos, pero no estoy seguro, por qué SELECT id, username es 2-3 veces más rápido que SELECT * .

Cuando tiene un índice (la clave principal en su caso) y el resultado incluye solo las columnas del índice, el optimizador de MySQL solo puede usar los datos del índice, ni siquiera examina la tabla en sí. Cuanto más caro sea cada fila, más efecto observará, ya que sustituye las operaciones IO del sistema de archivos con operaciones puras en memoria. Si tendrá un índice adicional en (id, nombre de usuario), también tendrá un rendimiento similar en el tercer caso.

Answer 4

Si bien no existe una "orden rápida por rand ()", existe una solución para su tarea específica.

Para obtener una sola fila al azar , puedes hacer lo mismo que este blogger alemán: http://www.roberthartung.de/mysql-order-by-rand-a-case-study-of-alternatives/ (No pude ver una URL de enlace directo. Si alguien ve una, siéntase libre de editar el enlace).

El texto está en alemán, pero el código SQL está un poco abajo de la página y en grandes cuadros blancos, por lo que no es difícil de ver.

Básicamente lo que hace es hacer un procedimiento que hace el trabajo de obtener una fila válida. Eso genera un número aleatorio entre 0 y max_id, intenta buscar una fila y, si no existe, sigue hasta que llegues a una que sí lo haga. Permite buscar x número de filas aleatorias almacenándolas en una tabla temporal, por lo que probablemente pueda volver a escribir el procedimiento para que sea un poco más rápido recuperar solo una fila.

La desventaja de esto es que si elimina MUCHAS filas, y hay enormes brechas, es probable que se pierda toneladas de veces, por lo que es ineficaz.

Actualización: diferentes tiempos de ejecución

SELECCIONAR * FROM table ORDER BY RAND () LIMIT 1; / 30-40 segundos /

SELECCIONE la ID FROM tabla ORDER BY RAND () LIMIT 1; / 0.25 segundos /

SELECCIONE id, nombre de usuario FROM tabla ORDER BY RAND () LIMIT 1; / 90 segundos /

Esperaba ver aproximadamente el mismo tiempo para las tres consultas, ya que siempre selecciono una sola columna. Pero por alguna razón esto no sucedió. Por favor, hágame saber si tiene alguna idea sobre esto.

Puede tener que ver con la indexación. id está indexado y es de acceso rápido, mientras que agregar username de username al resultado significa que necesita leerlo de cada fila y ponerlo en la tabla de memoria. Con * también tiene que leer todo en la memoria, pero no necesita saltar el archivo de datos, lo que significa que no hay tiempo perdido buscando.

Esto hace una diferencia solo si hay columnas de longitud variable (varchar / text), lo que significa que tiene que verificar la longitud, luego omita esa longitud, en lugar de saltarse una longitud establecida (o 0) entre cada fila.