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
— назва поля, для якого потрібно додати індекс.
Як доповнення рекомендуємо:
- Змінити тип полів
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
.