2.7.9. Оптимизация базы данных
На примерах ошибок, встречающихся у пользователей хостинга, рассмотрим, как можно оптимизировать базу данных.
Пример таблицы, которая состоит из более чем 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 тысяч строк. Для того, чтобы оптимизировать работу таблицы:
- Добавляем индекс по полю
publication
. - Меняем часть запроса
UNIX_TIMESTAMP(publication) = 1409472019
наpublication=FROM_UNIXTIME(1409472019)
. Только при таком запросе MySQL будет использовать индекс. Так как в первом случае СУБД придется пройтись по каждой строке в таблице и применить к ней функциюUNIX_TIMESTAMP
, после чего сравнить полученный результат с числом1409472019
, а во втором — один раз меняется константаFROM_UNIXTIME(1409472019)
, после чего производится поиск по индексу.
Этих двух операций достаточно для того, чтобы всё начало работать быстро и без нагрузки на процессор сервера MySQL.
В качестве дополнения рекомендуем:
- Изменить тип полей
title
,description
,link
сtext
наvarchar
нужной длинны. - Убрать индекс
UNIQ
по полюid
. В нём нет нужды, поскольку эту функцию уже выполняет индексPRIMARY
по этому же полю. - Индекс с названием
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
.