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)