На примерах ошибок, встречающихся у пользователей хостинга, рассмотрим, как можно оптимизировать базу данных.
Пример таблицы, которая состоит из более чем 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
.
с 2-00 и до 7-00