Предложение MySQL Query IN() Медленное индексирование столбца

У меня есть запрос MySQL, который генерируется PHP script, запрос будет выглядеть примерно так:

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);

Столбец RHD_No является первичным ключом для этой базы данных и насчитывает около 400 000 строк. Проблема в том, что запрос очень медленный, он часто составляет около 2 секунд, но я видел, что он доходит до 10.

Когда я пытаюсь объяснить запрос, все кажется, что все должно быть хорошо:

+----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+
| 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 |
+----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+

Когда я просматриваю запрос, я получаю:

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 |
+--------------------------------+----------+

Я работаю над этой проблемой в течение долгого времени, и я не смог найти решение. Есть ли что-то прямое в этом вопросе? Я не вижу, как смотреть 420 строк на 2 секунды.

Ответ 1

Вы получаете доступ к 420 строкам с помощью первичного ключа, который, вероятно, приведет к пути доступа к индексу. Это может иметь доступ к двум страницам индекса и одной странице данных на ключ. Если они находятся в кеше, запрос должен выполняться быстро. Если нет, каждый доступ к странице, который идет на диск, будет зависеть от обычной задержки на диске. Если мы предположим, что задержка на 5 мс и 80% попаданий кэша, мы получаем 420 * 3 * 0,2 * 5 мс = 1,2 секунды, который находится на порядок того, что вы видите.

Ответ 2

Проблема заключается в том, что IN в основном рассматривается как связка OR (например,

col IN (1,2,3)

является

col = 1 OR col = 2 OR col = 3

Это LOT медленнее, чем соединение.

Что вам нужно сделать, так это сгенерировать код SQL, который создает временную таблицу, заполняет ее значениями в предложении "IN", а затем присоединяется к этой временной таблице

CREATE TEMPORARY TABLE numbers (n INT)

Затем в цикле добавьте

INSERT numbers  VALUES ($next_number)

Тогда в конце

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

Ответ 3

Вы должны преобразовать предложения IN в предложения INNER JOIN.

Вы можете преобразовать запрос, подобный этому:

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

В запросе, подобном этому другому:

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

Вы получите большую производительность.

Как php генерирует запрос, попробуйте какой-то трюк, как временная таблица для элементов внутри предложения IN. Всегда старайтесь избегать предложений IN, если это возможно, потому что они занимают много времени.

Ответ 4

Я собираюсь играть в азартные игры здесь и предлагаю, чтобы выполнить следующий запрос только один раз, чтобы создать индекс, подходящий для вашего запроса, должен сократить время запроса как минимум на секунду...

CREATE INDEX returnstatus ON Recipe_Data(404_Without_200,Failures_Without_Success)

Смотрите: http://dev.mysql.com/doc/refman/5.0/en/create-index.html для создания индексов и http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html о том, как индексы используются в запросах.

В противном случае просмотрите все запущенные процессы в mysql, чтобы увидеть, не работает ли текущий запрос из какого-либо источника из любого источника, потребляя все время сервера и убивая его. См.: http://dev.mysql.com/doc/refman/5.0/en/kill.html

В противном случае определите, что еще может иметь каждая запись, чтобы избежать необходимости ссылаться друг на друга по идентификационному номеру в вашем заявлении IN. Если необходимо, добавьте еще один столбец таблицы для отслеживания этой общности. Затем добавьте столбец (столбцы), имеющие эту общность, к указанному выше индексу и отфильтруйте это в своем предложении WHERE вместо использования оператора IN. Например, если вы хотите, чтобы только эти идентификационные номера были распечатаны на странице, введите столбец visible как тип: tinyint со значением 0 для исключения и значение 1 для включения в результаты поиска, затем добавьте visible для индексирования и WHERE для ускорения запроса. Вам вообще не понадобится инструкция IN.

Возможно, ваш оператор IN динамически построен с использованием предыдущего запроса. Если это произойдет, попробуйте потянуть все строки с помощью Recipe_Data WHERE 404_Without_200 = 0 AND Failures_Without_Success = 0. Затем в вашем PHP скрипт просто отбросьте запись в вашем цикле выборки, если RHD_No не соответствует ожидаемому значению.