Было как то раз у меня постоянно проблема — очень долго исполнялись SELECT с JOIN запросы. Грешил я на долгое исполнение JOIN операций, хотя с индексами у меня был полный порядок. Что я только не делал — и настройки крутил MySQL, и код местами переписывал, чтобы оптимальнее работал. Но проблема оставалась. Причем это что-то грузило сервак конкретно, несмотря на оченбь мощное железо. Но вот нашел в инете одну статейку и она меня натолкнула на мысль — изменить priority операции INSERT/DELETE. Правки для двух строк кода убрали проблему совсем! И очень мощный сервак стал работать очень быстро… Я даже не ожидал такого! А теперь немного поподробнее…
В MySQL есть такая особенность для таблиц типа MyISAM — операции изменения таблицы (INSERT/UPDATE/DELETE) имеют более высокий приоритет перед SELECT. Представим себе такую картину: к MySQL поступает сразу несколько запросов в таком порядке: SELECT (1) -> UPDATE (2) -> SELECT (3) -> SELECT (4). Все эти запросы — от нескольких клиентов (сессий) и все — на работу с одной и той же таблицей (для простоты понимания). Первый SELECT, предположим, занимает 2 сек времени на выполнение. Если бы не было UPDATE, то остальные селекты могли бы исполняться в параллель — таблица ведь не меняется, данные тоже, почему бы не дать другим клиентам тоже выполнить запросы… Но, поскольку, есть UPDATE (то же было бы для случая DELETE/INSERT), MySQL заблокирует таблицу полностью при первом же случае (в нашем примере сразу же после отработки первого SELECT, который поступил раньше UPDATE), т.е. он ждет выполнения первого селекта (2 сек) для блокировки. За эти две секунды никто не может получить доступ к таблице, остальные SELECT будут ждать выполнения UPDATE (вень у него более высокий приоритет пол умолчанию), а UPDATE ждет первого SELECT. Все… Это пипец… Если у вас не загруженный сервак, то вас это беспокоить не будет — никто не будет наступать друг другу на пятки. А если сервак загружен, эту ситуацию можно сравнить с давкой в торговом центре, где ажиотаж, но при этом security решили пропустить без очереди VIP персону…
Какой выход? Оказывается, довольно простой. Один из вариантов — изменить по умолчанию приоритет операций для изменения таблиц (т.е. для UPDATE/INSERT/DELETE) — опция --low-priority-updates при запуске mysqld. Но это слишком глобальный способ, и вряд ли хороший. Я не вдумывался в его смысл, но думаю, раз придумали такой приоритет, то значит это кому то нужно.
Есть способ второй — найти такие узкие места в программе, где есть вместе долгоисполняемые SELECT (например большие JOIN), а также где рядом исполняется INSERT/DELETE/UPDATE на те же таблицы. И вот для тех INSERT/DELETE/UPDATE добавить слово LOW_PRIORITY в запросе. И все! На 99% вы устраните это узкое место и будете удивлены, насколько все пропрет 🙂 Представим, что будет после. Если встретился долгий SELECT, а поступил также, например UPDATE, но уже с LOW_PRIORITY, а за ним в очереди другие SELECT, то другие SELECT будут исполняться сразу же в параллель вместе с первым длинным по времени, даже пока он не закончился. А уже после будет работать UPDATE и никому не мешать. Вообще, есть больше рекомендаций от самой MySQL.ORG.