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-сервера.

Додавання індексу

Індекс можна додати будь-яким із способів:
  • За допомогою 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.

Зміст