Оптимизация запросов MySQL

от
PHP/MySQL    mysql, запросы, оптимизация, sql
Вчера я решил заняться оптимизацией запросов на сайте. После перехода с устаревшего модуля mysql на PDO скорость работы снизилась, а после обновления MySQL с версии 5.5 до 5.7 производительности не прибавилось. Это явно говорило о том, что с нашими таблицами и с запросами что-то не так.

Прощай, MyISAM
Первым делом я решил посмотреть на саму структуру базы данных. Оказалось, половина таблиц была на движке MyISAM, а половина на InnoDB.

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

После перехода на PDO все операции с базой данных проходят в специальном классе. Добавив туда простой логгер запросов с выводом [время - запрос], я мог видеть, какие таблицы обновлялись при открытии той или иной страницы.

Такой таблицей оказалась таблица пользователей. При каждом открытии страницы обновляется счётчик переходов и местоположение (главная, форум, статьи и т.д.). MyISAM здесь не место.

Сразу отмечу, что напрямую менять движок и вообще какие-то критические данные, тем более такой таблицы как users, никак нельзя. На локалке можно. А в продакшене лучше сделать копию:
  1. CREATE TABLE `users_innodb` LIKE `users`;
  2. INSERT `users_innodb` SELECT * FROM `users`;
  3. ALTER TABLE `users_innodb` ENGINE=INNODB;
  4. RENAME TABLE `users` TO `users_myisam`, `users_innodb` TO `users`;

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

  1. ALTER TABLE `users` ENGINE=INNODB;

Следующими кандидатами были таблицы учёта прочтений в модулях. При каждом открытии модуля производится очистка старых данных, а это достаточно часто.

  1. ALTER TABLE `ablogs_rdm` ENGINE=INNODB;
  2. ALTER TABLE `ablogs_com_rdm` ENGINE=INNODB;
  3. -- ...
  4. ALTER TABLE `writers_rdm` ENGINE=INNODB;
  5. ALTER TABLE `writers_com_rdm` ENGINE=INNODB;


MyISAM и FULLTEXT INDEX
В MySQL 5.5 полнотекстовый индекс был доступен только для MyISAM. По этой причине мы и держали форум на этом движке. Изредка случались ситуации, когда после написания сообщения в теме просто не показывались какие-то свежие посты. Это вина MyISAM.

В MySQL 5.6 FULLTEXT появился и для InnoDB, так что:
  1. ALTER TABLE `forum` ENGINE=INNODB;


Типы столбцов
Следующее, на что я обратил внимание — типы столбцов. В статьях, альбомах, уголке писателя и кодах раздел, запись и комментарий классифицируются по значению столбца type. al - раздел, cm - комментарий, ph - запись (фотография). Больше никаких значений нет, только эти три.

Столбец type имел тип VARCHAR(2). Конечно, лучшим решением было бы перепроектировать БД, чтобы у разделов, комментариев и записей были свои таблицы, но у нас legacy проект, поэтому можно просто сменить VARCHAR на ENUM:
  1. ALTER TABLE `albums` CHANGE `type` `type` ENUM('al','cm','ph') NOT NULL;

Производительности это особо не добавит, зато можно быть уверенным, что другое значение кроме перечисленных в таблицу не попадёт.

Самое интересное было с дневниками — прародителем всех остальных модулей. Там у столбца был тип TEXT. TEXT, КАРЛ!!! Для трёх различных трёхбуквенных значений (com, txt, dir)!


Индексы
У некоторых таблиц индексы либо отсутствовали, либо не включали в себя нужные столбцы.

Например, не было индексов для столбцов type, которые я на прошлом этапе переделывал. А ведь очень много запросов именно на тип и смотрят.


Пример
Возьмём запрос на получение комментариев некоторого пользователя в уголке писателя.
  1. EXPLAIN SELECT * FROM `writers`
  2. WHERE `type` = 'cm'  AND `userid` = 1720
  3. ORDER BY `time` DESC;

При отсутствующих индексах результат будет таков:
1_before_add_index.png

Добавляем индекс для типа:
  1. ALTER TABLE `writers` ADD INDEX `type` (`type`);

После добавления индекса для типа

Теперь вместо всей таблицы из 864 строк, было просмотрено 686 строк по индексу type.

Добавляем индекс для пользователя:
  1. ALTER TABLE `writers` ADD INDEX `userid` (`userid`);

После добавления индекса для id пользователя

Теперь было просмотрено 38 записей по индексу userid.


Индекс для двух столбцов
Однако самым эффективным будет индекс для двух столбцов: type + userid. Удаляем предыдущие индексы и добавляем новый.
  1. ALTER TABLE `writers`
  2.     DROP INDEX `type`,
  3.     DROP INDEX `userid`,
  4.     ADD INDEX `type_user` (`type`, `userid`);

После добавления индекса для type и userid


Уникальный индекс
Столбец с логином в JohnCMS уникален. Не может быть двух пользователей с одинаковыми логинами, поэтому для этого столбца лучше подойдёт индекс UNIQUE. Это поможет и быстрее найти пользователя, и не даст записать в таблицу повторяющееся значение. Что было до:

Перед добавлением уникального индекса

А вот, что стало после смены индекса на уникальный:
  1. ALTER TABLE `users`
  2.     DROP INDEX `name_lat`,
  3.     ADD UNIQUE INDEX `name_lat` (`name_lat`);

После добавлением уникального индекса

Теперь запись нашлась без дополнительных проверок.

В итоге, добавив к остальным таблицам пару нужных индексов, удалось заметно ускорить открытие страниц. Однако на главной дневников страница по-прежнему открывалась более полутора-двух секунд, а на локалке вообще за 3.


Переписываем запросы
Дневники
Счётчик запросов показал, что на главной дневников было около 30 запросов. И это всего лишь для вывода 5 дневников на страницу с количеством комментариев и самой последней записью.

Сперва выбирались пять дневников, то есть записей с type = 'dir'. Затем для каждого такого дневника, то есть в цикле, выполнялись ещё запросы:
  1. Получение информации о пользователе.
  2. Количество записей в дневнике.
  3. Последняя запись.
  4. Количество комментариев у последней записи.
  5. Количество непрочитанных комментариев.

Итого, один запрос на получение списка дневников, 25 запросов в цикле для каждого дневника и несколько для проверок и очистки старых непрочитанных.

Список дневников:
  1. SELECT * FROM `diaries`
  2. WHERE `type` = 'dir'
  3. ORDER BY `time` DESC
  4. LIMIT :start, 5

Последняя запись в дневнике:
  1. SELECT * FROM `diaries`
  2. WHERE `type` = 'txt'
  3.   AND `userid` = :userid
  4. ORDER BY `time` DESC
  5. LIMIT 1

Количество записей в дневнике пользователя:
  1. SELECT COUNT(*) FROM `diaries`
  2. WHERE `type` = 'txt'
  3.   AND `userid` = :userid

В дневнике (type = 'dir') хранится только id пользователя и время создания, которое всегда совпадает с временем первой размещённой записи. Нам эта информация не только не нужна, но ещё и мешает. Если пользователь создаст дневник, а потом удалит все записи, то дневник всё ещё будет отображаться в списке. Поэтому уберём этот тип и будем идентифицировать дневники по наличию записей.

Сначала идёт запрос на получение количества дневников, чтобы правильно отобразить постраничную навигацию.

Было:
  1. SELECT COUNT(*) FROM `diaries`
  2. WHERE `type` = 'dir'

Стало:
  1. SELECT COUNT(DISTINCT `userid`) FROM `diaries`
  2. WHERE `type` = 'txt'

Теперь это количество уникальных пользователей, у которых есть записи, что, в принципе, одно и то же.

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

  1. SELECT * FROM `diaries` d
  2. INNER JOIN (
  3.      SELECT
  4.          MAX(`time`) as `record_date`,
  5.          COUNT(*) as `records_count`
  6.      FROM `diaries`
  7.      WHERE `type` = 'txt'
  8.      GROUP BY `userid`
  9. ) r
  10. WHERE d.type = 'txt' AND d.`time` = r.record_date
  11. ORDER BY d.`time` DESC
  12. LIMIT :start, 5

Во внутреннем подзапросе получаем количество и время последней записи, чтобы по нему выбрать саму последнюю запись. Это сэкономило 10 запросов на страницу.

Сюда же можно добавить и информацию о пользователе.
  1. SELECT * FROM `diaries` d
  2. INNER JOIN (
  3.      SELECT
  4.          MAX(`time`) as `record_date`,
  5.          COUNT(*) as `records_count`
  6.      FROM `diaries`
  7.      WHERE `type` = 'txt'
  8.      GROUP BY `userid`
  9. ) r
  10. LEFT JOIN `users` u ON u.id = d.userid
  11. WHERE d.type = 'txt' AND d.`time` = r.record_date
  12. ORDER BY d.`time` DESC
  13. LIMIT :start, 5

А это ещё минус пять запросов. В итоге мы снизили количество запросов вдвое и получили весомый прирост скорости.


Форум
Следующим на очереди оптимизации был форум. Вывод темы занимал от одной секунды на обычных темах, до трёх-четырёх секунд на большой теме в 80000 постов.

  1. SELECT
  2.   `forum`.*,
  3.   `users`.*,
  4.   `cms_ban_users`.*
  5. FROM `forum`
  6. LEFT JOIN `users`
  7.    ON `forum`.`user_id` = `users`.`id`'
  8. LEFT JOIN `cms_ban_users`
  9.   ON `module` = "forum"
  10.   AND `cms_ban_users`.`mid` = `forum`.`id`'
  11. WHERE
  12.   `forum`.`type` = "m"
  13.    AND `forum`.`refid` = :refid
  14. LIMIT
  15.   :start, :total

Это запрос на получение списка сообщений в теме с информацией о пользователе и банах. Остаются ещё вложения, но они получаются для каждого сообщения в цикле.

Здесь уже было уменьшено количество запросов, но это лишь снизило скорость. Дело в том, что сообщений в теме может быть очень много и ко всем им придётся делать LEFT JOIN. В теме с 80000 сообщений это большая нагрузка, нужен другой способ оптимизации.

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

  1. SELECT * FROM `forum`
  2. WHERE `type` = "m" AND `refid` = :refid
  3. LIMIT :start, :total

Теперь у нас есть только 20 сообщений (количество постов на странице). Уж на этом промежутке выбирать остальные данные будет заметно проще.
Средствами php я получил массив с id сообщений или user_id полученной выборки и теперь могу делать остальные запросы.

Баны:
  1. SELECT * FROM `cms_ban_users`
  2. WHERE `module` = "forum" AND `mid` IN :ids

Информация о пользователях:
  1. SELECT * FROM `users`
  2. WHERE `id` IN :user_ids

И даже вложения удалось вынести из цикла:
  1. SELECT * FROM `forum_files`
  2. WHERE `post` IN :ids

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


Удаление записей в связанных таблицах
В движке во многих местах удаление происходит таким образом: выбираются сначала данные, зачастую id, а потом в цикле они берутся и выполняется запрос на удаление.

Например, удаление дневника.

  1. // Очистка комментариев
  2. $stmt = DB::query(
  3.         "SELECT * FROM `diaries`
  4.         WHERE `type` = 'txt'  AND `userid` = ?",
  5.         [$id]);
  6. while ($diary = $stmt->fetch()) {
  7.     DB::query("DELETE FROM `diaries`
  8.               WHERE `type` = 'com' AND `com_id` = ?",
  9.               [$diary['id']);
  10. }
  11. // Удаление записей
  12. DB::exec("DELETE FROM `diaries`
  13.          WHERE `type` = 'txt' AND `userid` = ?");

Можно сделать по-другому: получить сначала все id, а потом сразу одним запросом удалить при помощи id IN (...):
  1. // Очистка комментариев
  2. $diaryIds = DB::prepareAndExec(
  3.         "SELECT `id` FROM `diaries`
  4.         WHERE `type` = 'txt'  AND `userid` = ?",
  5.         [$id])->fetchAll(PDO::FETCH_COLUMN);
  6. $sql = implode(',', $diaryIds);
  7. DB::exec("DELETE FROM `diaries`
  8.          WHERE `type` = 'com' AND `com_id` IN ($sql)");
  9. // Удаление записей
  10. DB::exec("DELETE FROM `diaries`
  11.          WHERE `type` = 'txt' AND `userid` = ?",
  12.          [$id]);

А можно и одним запросом всё удалить, выполнив JOIN:
  1. DB::exec("DELETE d, com FROM `diaries` d
  2.          LEFT JOIN `diaries` com
  3.                 ON com.`com_id` = d.id
  4.                AND com.`type` = 'com'
  5.          WHERE d.`type` = 'txt'
  6.            AND d.userid = ?",
  7.          [$id]);

Но лучше, конечно же, настроить каскадное удаление.

Итоги
В итоге сайт стал открываться очень быстро. Прирост скорости на всех страницах приблизительно в 6 раз. На форуме и дневниках заметно больше. Да и PHP 7.1 стал намного шустрее, по сравнению с версией 5.4, которая была раньше.
  • +10
  • views 5688
    • PHP/MySQL
    Категории