Заметки об HTML, CSS, AJAX, DOM, PHP, MySQL и многом другом

Иногда возникает ситуация, когда какой-либо тяжелый, но трудно отслеживамый MySQL-запрос замедляет работу всего сайта . К сожалению, MySQL не содержит средств ограничения времени выполнения запроса, как PHP, но есть другая весьма полезная (в случае SELECT-запросов) опция: max_join_size

Эта опция позволяет задать максимальное количество строк (или их комбинаций или обращений к диску), которое может быть просмотрено за один запрос. Если количество строк превысит заданное, выполнение запроса прерывается с ошибкой.

Данную опцию следует прописывать либо в my.cnf (секция mysqld), либо задавать динамически с помощью запроса SET @@max_join_size = значение (требуются права суперпользователя).

Несколько советов по оптимизации MySQL-запросов.

1. Для изучения того, как выполняется запрос, можно использовать ключевое слово EXPLAIN (добавляется перед текстом запроса). EXPLAIN показывает, в каком порядке таблицы связываются, какие ключи при этом используются и сколько строк выбирается из каждой таблицы.
2. Если количество строк, которое надо выбрать, заранее известно точно (например, нужна всего одна строка), в конец запроса имеет смысл добавить LIMIT 1. В этом случае при связывании каждой таблицы будет искаться только первая запись, соответствующая критерию. (Исключением являются случаи, когда все таблицы связываются по первичному/уникальному ключу, в этом случае LIMIT 1 не даст каких-либо выгод.)
3. Если таблица содержит большие текстовые или бинарные поля, а при выводе требуется ее сортировка по какому-то другому полю (например, дате или номеру) или выборка по сложному критерию, не затрагивающему эти текстовые поля, то имеет смысл разбить ее на две таблицы, в одной из которых будет поле для сортировки, а в другой — текстовое. В этом случае сортировка/выборка значительно ускорится.
4. При использовании LEFT JOIN таблицы связываются в том порядке, в котором они перечислены. Этим следует пользоваться для того, чтобы вынести в конец запроса “тяжелые” таблицы, которые присоединяются для получения информации и не участвуют в выборке данных по сложным критериям (т.е. привязываются к предыдущим по первичному ключу). Примером такой ситуации является таблица с текстыми/бинарными данными из п.3. В начало запроса следует выносить те таблицы, выборка по которомы значительно сокращает количество строк данных, выбираемых в следующих таблицах (это можно узнать с помощью EXPLAIN).
5. Для выборки максимума можно воспользоваться сортировкой по искомому столбцу и LIMIT 1.
6. При создании индексов нужно не забывать, что если для первого столбца индекса совпадает более 30% значений, то этот индекс не используется при выборке. Поэтому в ситуациях, когда таблица индексируется по двум столбцам, в одном из которых значение меняется редко, этот столбец должен обязательно идти последним. Пример: один столбец col1 — двоичный признак, в который пишутся значения 0 и 1, причем записей с 1 значительно больше, чем с 0, а второй col2 — дата, и выборка производится всех сообщений с признаком 1 в определенном диапазоне дат. Если индекс будет определен как (col1,col2), он будет проигнорирован, так как записей с 1 более 30%, и для выборки по дате придется просматривать всю таблицу. Если же определить индекс как (col2,col1), то он сработает нормально.
7. В некоторых ситуациях, когда требуется сделать выборку по сложному условию из нескольких таблиц и часть этого условия не совпадает ни с одним индексом, может оказаться выгоднее вынести эту часть условия в HAVING, а связывание таблиц делать исключительно по индексам. Но это утверждение верно только в случаях, когда количество записей с дополнительным условием не сильно отличается от количества записей, извлеченных только по ключам.
8. В некоторых слуачаях вместо операции UPDATE оказыается более целесообразным делать DELETE/INSERT и периодически выполнять оптимизацию по cron, чтобы уменьшить время блокировок.

Организовать простую защиту от DoS-атак для сайтов, использующих PHP + MySQL, можно следующим образом. В MySQL создается таблица (будем называть ее ip_check) с полями lasttime, count, ip (все поля — типа INTEGER, причем ip — первичный ключ).

Сразу после подключения к базе данных выполняется проверка, есть ли для данного IP-адреса запись в таблице. Если записи нет, она создается, при этом в count пишется 1, в lasttime — текущее время. Если запись уже есть, то проверяется, когда она была сделана (поле lasttime), и если прошло менее определенного количества секунд, то поле count увеличивается на 1, в противном случае приравнивается единице, и производится обновление записи в таблице (записывается новый count и lasttime). Далее происходит проверка величины count, и если она превысила некоторое пороговое значение, выдается статус 500 (или 403 или 503), сообщение об ошибке, и выполнение скрипта завершается.

Таким образом, для выполнения проверки требуется всего одной таблица с предельно простой структурой и 2 запроса SQL. Также можно добавить еще одно поле status, в которое ставить 1, если IP-адрес забанен навсегда по каким-то причинам, и совместить проверку на DoS-атаки с проверкой на забаненные IP-адреса без добавления дополнительных запросов. При необходимости можно еще одним запросом добавить учет суммарной нагрузки за все время или нагрузки по подсетям.

Кроме того, перед завершением скрипта можно добавить проверку, сколько времени заняло его выполнение, и если оно превышает какую-то пороговую величину (например, 10 секунд), начислять этому IP-адресу “штрафные очки” (т.е. дополнительно увеличивать поле count).

В виде кода это можно представить следующим образом:

// $link -- соединение с БД, $ip_text -- IP-адрес
define('ALERT_TIME',300);
define('ALERT_COUNT',150);  

function check_dos($ip_text, $link)  {
  $ip=ip2long($ip_text);
  $res=mysql_query($link,'SELECT lasttime, count, status FROM ip_check WHERE ip="'.$ip.'"');
  if (mysql_num_rows($res)==0) { // нет такого IP
  $count=1;
  mysql_query($link,'INSERT INTO ip_check (lasttime,count,ip,status) VALUES ('.time().',1,"'.$ip.'",0)');
  }
else {
  $data=mysql_fetch_row($res);
  $status=$data[2];
  if ($status!=2 && ($data[0]<time()-ALERT_TIME || $data[1]<ALERT_COUNT)))   $count=1;
  elseif ($status!=2) { $count=$data[1]+1; if ($count>=ALERT_COUNT) $status=1; }
  if ($status!=2) {
    mysql_query($link,'UPDATE ip_check SET count='.$count,', status='.$status.', lasttime='.time().' WHERE ip="'.$ip.'"';
  }
  if ($status) {
    header($_SERVER['HTTP_PROTOCOL'].' 503 Temporary Unavailable');
    trigger_error('Превышено число допустимых запросов!',E_USER_ERROR);
  }
}

Кроме того, периодически следует очищать базу от старых записей (например, по cron). Кроме того, если ожидается большая интенсивность атак, возможно, имеет смысл заменить UPDATE-запрос на последовательность DELETE/INSERT, но в этом случае необходимо будет регулярно проводить оптимизацию таблицы.

Бывают ситуации, когда желательно восстановить базу форума или CMS из резервной копии, но при этом не терять ту часть материала, которая была добавлена после снятия этой копии. Сделать это достаточно просто. При снятии дампа с помощью phpMyAdmin следует включить опции “create table if not exists” и “insert ignore”. При включении этих опций попытка создать таблицу будет выполняться только в том случае, если таблицы не существует, а вставка записей — только при отсутствии записей с таким же PRIMARY KEY (тогда как без этих опций при наличии таблиц/записей возникли бы ошибки SQL).

Также вместо insert ignore можно выбрать режим создания дапма REPLACE, это позволит не только восстановить уничтоженные записи, но и вернуть в состояние на момент снятия дампа все записи.

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

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

Но существует скрипт под названием Sypex Dumper, который позволяет решить эту проблему. Sypex Dumper в отличии от многих подобных скриптов не загружает бекап-файл целиком в память, благодаря чему, ему безразличен размер базы данных и он одинаково быстро работает, как с маленькими, так и с большими объемами данных. Sypex Dumper Lite распространяется по лицензии GNU GPL, т. е. является абсолютно бесплатным.

Основные преимущества Sypex Dumper:

• высокая скорость работы;
• работа с базами любого размера;
• простота использования;
• удобный интерфейс;
• многотомные бекапы;
• мультиязычность;
• компактность.

Скачать программу можно на ее официальном сайте: http://sypex.net