2.7.10. Оптимизация базы данных

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

Пример таблицы, которая состоит из более чем 40000 записей и к которой отправляются запросы, создающие немалую нагрузку на сервер, так как их поступает очень много:

CREATE TABLE `links` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `source` INT(11) UNSIGNED NOT NULL,
  `category` INT(11) UNSIGNED NOT NULL,
  `title` text NOT NULL,
  `description` text NOT NULL,
  `text` text NOT NULL,
  `link` text NOT NULL,
  `publication` datetime NOT NULL,
  `scan` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique` (`id`) USING BTREE,
  KEY `normal` (`source`,`category`,`title`(100),`publication`,`description`(100),`text`(100),`scan`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=40000 DEFAULT CHARSET=utf8

Пример поступающих запросов:

SELECT COUNT(*)
FROM links
WHERE link = 'http://www.......com.ua/31-08-2014/trolleybook-podgotovil-prazdnichnuyu-programmu-k-limba-noastr-/n96349/'
AND UNIX_TIMESTAMP(publication) = 1409472019

Выполнив запрос с приставкой EXPLAIN мы видим, что для получения результатов сканируется вся таблица — около 40 тысяч строк. Для того, чтобы оптимизировать работу таблицы:

1. Добавляем индекс по полю publication.
2. Меняем часть запроса UNIX_TIMESTAMP(publication) = 1409472019 на publication=FROM_UNIXTIME(1409472019). Только при таком запросе MySQL будет использовать индекс. Так как в первом случае СУБД придется пройтись по каждой строке в таблице и применить к ней функцию UNIX_TIMESTAMP, после чего сравнить полученный результат с числом 1409472019, а во втором — один раз меняется константа FROM_UNIXTIME(1409472019), после чего производится поиск по индексу.

Этих двух операций достаточно для того, чтобы всё начало работать быстро и без нагрузки на процессор сервера MySQL.

В качестве дополнения рекомендуем:

1. Изменить тип полей title, description, link с text на varchar нужной длинны.
2. Убрать индекс UNIQ по полю id. В нём нет нужды, поскольку эту функцию уже выполняет индекс PRIMARY по этому же полю.
3. Индекс с названием normal ещё больше вызывает сомнения, так как для его создания серверу приходится немало потрудиться. Он просто огромен, а крайне маловероятно, что поиск происходит по всем полям, которые в нём указаны. Скорее всего это «мёртвый» индекс, который больше мешает, чем помогает. Разработчикам стоит знать, что индекс будет использоваться только в том случае, если в запросе будут содержаться поля, которые в нём находятся слева-направо. Так, например, этот индекс будет использоваться, если в условии WHERE будут указаны поля category, title, source. Если же поле source указано в запросе не будет, то и индекс использоваться не будет.

Ну и напоследок есть вопрос относительно необходимости использования самого запроса COUNT(*). Очень похоже, что его автор хочет таким образом получить информацию о наличии записи в таблице, а не суммарное количество записей в таблице с указанным URL за указанную дату. В таком случае правильно использовать запрос без COUNT(*) с условием LIMIT 1:

SELECT id
FROM links
WHERE
link =  'http://www.......com.ua/31-08-2014/trolleybook-podgotovil-prazdnichnuyu-programmu-k-limba-noastr-/n96349/'
AND UNIX_TIMESTAMP(publication) = 1409472019
LIMIT 1

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

P.S. Несмотря на то, что в SQL-запросе идёт поиск по двум столбцам publication и link, в индекс не добавлялось поле link. Это связано с тем, что для данной таблицы поле publication с датой является довольно-таки уникальным и максимум будет содержать несколько строк с разным link. Поэтому накладные расходы на формирование индекса для поля link значительно превышают расходы на сканирование нескольких строк по одному индексу publicate.