У меня есть запрос MySQL, который генерируется скриптом PHP, запрос будет выглядеть примерно так:
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 секунды.
Вы получаете доступ к 420 строкам с помощью первичного ключа, который, вероятно, приведет к пути доступа к индексу. Это может иметь доступ к двум страницам индекса и одной странице данных на ключ. Если они находятся в кеше, запрос должен выполняться быстро. Если нет, каждый доступ к странице, который идет на диск, будет зависеть от обычной задержки на диске. Если мы предположим, что задержка на 5 мс и 80% кеш-хитов, мы получаем 420 * 3 * 0,2 * 5 мс = 1,2 секунды, что находится на порядок того, что вы видите.
Проблема в том, что IN в основном рассматривается как группа OR s (например,
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
Вы должны преобразовать предложения 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, если можете, потому что они очень трудоемки.
Я собираюсь играть в азартные игры здесь и предлагаю, чтобы выполнить следующий запрос только один раз, чтобы создать индекс, подходящий для вашего запроса, должен сократить время запроса, по крайней мере, на секунду …
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 не соответствует ожидаемому значению.