2.6.1.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. Этой операции уже будет достаточно для того, чтобы увеличилась скорость работы и снизилась нагрузка на процессор MySQL-сервера.

Добавление индекса

Индекс можно добавить любым из способов:
  • Через интерфейс phpMyAdmin
  • С помощью SQL-запроса:
    ALTER TABLE `table_name` ADD INDEX(`column_name`);

    В запросе: table_name — название таблицы, column_name — название поля, для которого нужно добавить индекс.

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

  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.

Содержание
    (3)