Хостинг и регистрация доменов

Чат онлайн
→  Хостинг →  Регистрация доменов →  VPS / VDS →  Выделенные сервера →  FAQ →  Форум →  Контакты →  Техподдержка

Оптимизация базы данных. Часть 1

Хостинг УкраинаFAQХостинг Оптимизация базы данных. Часть 1

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

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

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 будет сканровать всю таблицу. 

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


Добавить комментарий
Имя:
E-mail:

Обновить картинку
Ваш комментарий будет добавлен после проверки администратором.
Внимание! Чтобы ответить на существующий комментарий, пожалуйста, нажмите на кнопку Ответить, которая находится под соответствующим комментарием.

Другие полезные статьи:

Горячая линия
(044) 392 74 33 другие города
Copyright © 2006—2017 ООО "Хостинг «Украина»"

Все материалы данного сайта являются объектами авторского права.
Запрещается копирование, распространение или любое иное использование информации и объектов без письменного согласия правообладателя.
Нашли опечатку на странице - выделите ее и нажмите Ctrl+Enter
Идёт обновление информации, подождите...