php - outer - MySQL Query IN() Clause Slow on Indexed Column

laravel select max

Question

Tengo una consulta MySQL que está siendo generada por un script PHP, la consulta se verá algo como esto:

SELECT * FROM Recipe_Data WHERE 404_Without_200 = 0 AND Failures_Without_Success = 0 AND RHD_No IN (10, 24, 34, 41, 43, 51, 57, 59, 61, 67, 84, 90, 272, 324, 402, 405, 414, 498, 500, 501, 510, 559, 562, 595, 632, 634, 640, 643, 647, 651, 703, 714, 719, 762, 765, 776, 796, 812, 814, 815, 822, 848, 853, 855, 858, 866, 891, 920, 947, 956, 962, 968, 1049, 1054, 1064, 1065, 1070, 1100, 1113, 1119, 1130, 1262, 1287, 1292, 1313, 1320, 1327, 1332, 1333, 1335, 1340, 1343, 1344, 1346, 1349, 1352, 1358, 1362, 1365, 1482, 1495, 1532, 1533, 1537, 1549, 1550, 1569, 1571, 1573, 1574, 1596, 1628, 1691, 1714, 1720, 1735, 1755, 1759, 1829, 1837, 1844, 1881, 1919, 2005, 2022, 2034, 2035, 2039, 2054, 2076, 2079, 2087, 2088, 2089, 2090, 2091, 2092, 2154, 2155, 2156, 2157, 2160, 2162, 2164, 2166, 2169, 2171, 2174, 2176, 2178, 2179, 2183, 2185, 2186, 2187, 2201, 2234, 2236, 2244, 2245, 2250, 2255, 2260, 2272, 2280, 2281, 2282, 2291, 2329, 2357, 2375, 2444, 2451, 2452, 2453, 2454, 2456, 2457, 2460, 2462, 2464, 2465, 2467, 2468, 2469, 2470, 2473, 2474, 2481, 2485, 2487, 2510, 2516, 2519, 2525, 2540, 2545, 2547, 2553, 2571, 2579, 2580, 2587, 2589, 2597, 2602, 2611, 2629, 2660, 2662, 2700, 2756, 2825, 2833, 2835, 2858, 2958, 2963, 2964, 3009, 3090, 3117, 3118, 3120, 3121, 3122, 3123, 3126, 3127, 3129, 3130, 3133, 3135, 3137, 3138, 3139, 3141, 3142, 3145, 3146, 3147, 3151, 3152, 3155, 3193, 3201, 3204, 3219, 3221, 3222, 3223, 3224, 3225, 3226, 3227, 3228, 3229, 3231, 3232, 3233, 3234, 3235, 3237, 3239, 3246, 3250, 3253, 3259, 3261, 3291, 3315, 3328, 3377, 3381, 3383, 3384, 3385, 3387, 3388, 3389, 3390, 3396, 3436, 3463, 3465, 3467, 3470, 3471, 3484, 3507, 3515, 3554, 3572, 3641, 3672, 3683, 3689, 3690, 3692, 3693, 3694, 3697, 3698, 3705, 3711, 3713, 3715, 3716, 3717, 3719, 3720, 3722, 3726, 3727, 3732, 3737, 3763, 3767, 3770, 3771, 3772, 3773, 3803, 3810, 3812, 3816, 3846, 3847, 3848, 3851, 3874, 3882, 3902, 3903, 3906, 3908, 3916, 3924, 3967, 3987, 4006, 4030, 4043, 4045, 4047, 4058, 4067, 4107, 4108, 4114, 4115, 4131, 4132, 4133, 4137, 4138, 4139, 4140, 4141, 4142, 4146, 4150, 4151, 4152, 4153, 4157, 4158, 4160, 4163, 4166, 4167, 4171, 4179, 4183, 4221, 4225, 4242, 4257, 4435, 4437, 4438, 4443, 4446, 4449, 4450, 4451, 4452, 4454, 4460, 4550, 4557, 4618, 4731, 4775, 4804, 4972, 5025, 5026, 5039, 5042, 5294, 5578, 5580, 5599, 5602, 5649, 5726, 5779, 5783, 5931, 5934, 5936, 5939, 5940, 5941, 5978, 6044, 6056, 6113, 6116, 6118, 6122, 6123, 6125, 6127, 6128, 6129, 6130, 6131, 6135, 6141, 6145, 6147, 6150, 6152, 6153, 6154, 6160, 6166, 6169);

La columna RHD_No es la clave principal para esta base de datos, y hay alrededor de 400,000 filas en total. El problema es que la consulta es extremadamente lenta, a menudo es de alrededor de 2 segundos, pero la he visto llegar hasta 10.

Cuando trato de explicar la consulta, todo parece que debería estar bien:

+----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | Recipe_Data | range | PRIMARY | PRIMARY | 4 | NULL | 420 | Using where |
+----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+

Cuando perfilo la consulta, obtengo:

mysql> show profile;
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000015 |
| checking query cache for query | 0.000266 |
| Opening tables | 0.000009 |
| System lock | 0.000004 |
| Table lock | 0.000006 |
| init | 0.000115 |
| optimizing | 0.000038 |
| statistics | 0.000797 |
| preparing | 0.000047 |
| executing | 0.000002 |
| Sending data | 2.675270 |
| end | 0.000007 |
| query end | 0.000003 |
| freeing items | 0.000071 |
| logging slow query | 0.000002 |
| logging slow query | 0.000058 |
| cleaning up | 0.000005 |
+--------------------------------+----------+

He estado trabajando en este problema durante mucho tiempo y no he podido encontrar una solución. ¿Hay algo abiertamente erróneo con esta consulta? No veo cómo mirar 420 filas debería tomar más de 2 segundos.

Answer 1

El problema es que IN se trata básicamente como un grupo de OR (por ejemplo,

col IN (1,2,3)

es

col = 1 OR col = 2 OR col = 3

Esto es MUCHO más lento que una unión.

Lo que debe hacer es generar el código SQL que crea la tabla temporal, la rellena con los valores de la cláusula "IN" y luego se une a esa tabla temporal

CREATE TEMPORARY TABLE numbers (n INT)

Luego, en un bucle, agregue

INSERT numbers VALUES ($next_number)

Entonces al final

SELECT * FROM numbers, Recipe_Data
WHERE numbers.n = RHD_No

Answer 2

Voy a apostar aquí y sugerir que ejecutar la siguiente consulta solo una vez para crear un índice adecuado para su consulta debería reducir el tiempo de consulta en al menos un segundo ...

CREATE INDEX returnstatus ON Recipe_Data(404_Without_200,Failures_Without_Success)

Consulte: http://dev.mysql.com/doc/refman/5.0/en/create-index.html para crear índices, y http://dev.mysql.com/doc/refman/5.0/en/mysql- indexes.html para ver cómo se usan los índices en las consultas.

En su defecto, vea todos los procesos en ejecución en mysql para ver si una consulta actualmente en ejecución de cualquier fuente simplemente se niega a morir mientras consume todo el tiempo del servidor y lo mata. Ver: http://dev.mysql.com/doc/refman/5.0/en/kill.html

Si eso falla, determine qué más puede tener cada registro en común para evitar tener que hacer referencia a cada uno individualmente por número de identificación en su declaración IN . Si es necesario, agregue otra columna de tabla para rastrear esa comunidad. Luego, agregue columna (s) que tengan esa característica común al índice anterior y filtren por eso en su cláusula WHERE lugar de usar la instrucción IN . Por ejemplo, si solo desea imprimir esos números de ID en la página, tenga una columna visible como tipo: tinyint con valor 0 para excluir, y valor 1 para incluir en los resultados de búsqueda, luego agregue visible columna visible a sus índices y la cláusula WHERE para acelerar la consulta. No necesitarías esa declaración IN en absoluto.

Tal vez su declaración in se construye dinámicamente usando una consulta previa. Si ese es el caso, intente extraer todas las filas con Recipe_Data WHERE 404_Without_200 = 0 AND Failures_Without_Success = 0 . Luego, en su script PHP, simplemente descarte un registro en su ciclo de búsqueda si el RHD_No no coincide con un valor esperado.

Answer 3

Debe transformar las cláusulas IN en cláusulas INNER JOIN.

Puede transformar una consulta como esta:

SELECT foo
FROM bar
WHERE bar.stuff IN
(SELECT stuff FROM asdf)

En una consulta como esta otra:

SELECT b.foo
FROM (
SELECT DISTINCT stuff
FROM asdf ) a
JOIN bar b
ON b.stuff = a.stuff

Ganarás mucho rendimiento.

A medida que php genera la consulta, pruebe algún tipo de truco como una tabla temporal para los elementos dentro de la cláusula IN. Siempre trate de evitar las cláusulas IN si puede, ya que consumen mucho tiempo.

Answer 4

Está accediendo a 420 filas por clave principal, lo que probablemente dará lugar a una ruta de acceso de índice. Esto podría acceder a 2 páginas de índice y una página de datos por clave. Si están en caché, la consulta debería ejecutarse rápidamente. De lo contrario, cada acceso a la página que vaya al disco incurrirá en la latencia habitual del disco. Si suponemos una latencia de disco de 5 ms y un 80% de aciertos de caché, llegamos a 420 * 3 * 0.2 * 5ms = 1.2 segundos, que es del orden de lo que está viendo.