Оптимізація запитів MySQL з використанням змінних користувачів -стаття на

Введение.У світі існує велика кількість завдань, у межах яких доводиться обробляти великі масиви однотипних даних. Яскравими прикладами є системи аналізу біржових котирувань, погодних умов, статистики мережного трафіку. Багато хто з цих систем використовують різні реляційні бази даних, у таблицях яких містяться такі обсяги даних, що правильне складання та оптимізація запитів до цих таблиць стає просто необхідним для нормального функціонування системи. У цій статті описані методи вирішення (і порівняльні часові характеристики методів, що використовуються) кількох завдань з отримання даних з таблиць СУБДMySQL, що містять статистику про проходить через маршрутизатори одного з великих українських мережевих провайдерів мережевому трафіку. Інтенсивність потоку даних, що надходить з головного маршрутизатора така, що щодобово в таблиці бази даних системи моніторингу мережевого трафіку надходить в середньому від 400 мільйонів до мільярда записів, що містять інформацію про транзакціїTCP/IP(розглянутий маршрутизатор експортує дані по протоколуnetflow). Як СУБД для системи моніторингу використовуєтьсяMySQL.

Робота з індексами. Таблиця, в який немає індексів, є безладним набір рядків, і для пошуку потрібного запису, припустимо по id, необхідно перевірити всі її рядки на збіг з шуканим значенням, для цього потрібно просканувати всю таблицю від початку до кінця. Це може зайняти багато часу і буде виключно неефективно, особливо якщо таблиця містить лише кілька записів, які відповідають умовам пошуку. Як приклад розглянемо таблицю,містить підвідомчі організації, що розглядається інтернет-провайдеру. У даному контексті в таблиці є два поля – id організації (org_id) і назва організації (org_name).

Допустимо, ми додали індекс на поліorg_id(див. рис. 1). Індекс містить запис про кожний рядок з таблиці, і запис індексу відсортовані за значеннямorg_id. Тепер замість сканування всіх записів у таблиці ми можемо скористатися індексом. Припустимо, що потрібно знайти рядок, що містить запис про організацію (Інститут автоматики та електрометрії СО РАН), у якої унікальний ідентифікатор (org_id) дорівнює 2. Сканування за індексом повертає один рядок. Значення в індексі відсортовані за зростанням, тому досягненні наступного значення (зorg_idрівним3) можна завершувати сканування: після3ми не знайдемо потрібних значень. У випадку, якщо значені значення знаходяться десь посередині таблиці, за допомогою спеціальних алгоритмів (наприклад, методом бінарного пошуку) можна перейти до потрібного рядка без тривалого сканування таблиці.

Недоліки використання індексів.По-перше, індекси прискорюють пошук даних, але уповільнюють операції додавання, видалення та модифікації даних в стовпцях, що індексуються, оскільки при кожній зміні даних в такій таблиці доводиться перебудовувати індекс. Взаємозв'язок тут простий: що більше індексів має таблиця, то більше вповільнення операцій над записами.

По-друге, індексний файл займає певне місце на диску (нерідкі випадки, коли індекси займають більше місця, ніж самі дані). При створенні великої кількості індексів розмір такого індексного файлу може швидко досягти максимального розміру. Це може стати причиною досягнення граничногорозмір таблиці швидше, ніж це було б без використання індексів.

  • Для таблиць типу MyISAM надмірне індексування таблиць може призвести до досягнення індексним файлом його максимального розміру швидше, ніж файлом даних (проблему можна вирішити за допомогою PARTITIONING).
  • Таблиці типу BDB зберігають дані та індекси в одному файлі. Це, безумовно, є причиною швидшого досягнення максимального розміру табличним файлом.
  • Таблиці типу InnoDB розміщуються у єдиному табличному просторі. При додаванні індексів дисковий простір, відведений під табличний простір, буде вичерпано швидше.

Змінні користувача.MySQL підтримує змінні користувача (далі в текстіПП), починаючи з версії 3.23.6. Змінним можна надавати значення, і звертатися до них пізніше - це також можна використовувати, коли є потреба зберегти результати обчислень для використання в подальших запитах. Наприклад, щоб знайти вироби з мінімальною ціною, можна виконати такі дії: mysql> SELECT @min_price: = MIN (price) FROM shop; mysql> SELECT * FROM shop WHERE price = @ min_price;

Користувацькі змінні записуються як@var_name, і можуть приймати значення цілого (int), дробового (real) та рядкового (string) типу . Надання змінної значення здійснюється через оператор SET ( SET @var1= 'RUS' ), через операторSELECT( SELECT 'RUS' INTO @var1; ) або в ході виконання запиту через оператор “:=” (“= ” трактується як рівність) (@var1:=72).

запитів

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

Переваги використання ПП. Можливість зберігати проміжні результати в змінних і оперувати їм під час подальшого виконання запиту дозволяє значно прискорити виконання деяких запитів (за рахунок меншої кількості сканувань таблиці), а також дозволяє виконувати такі запити, які в стандартній реляційній моделі реалізуються дуже складно або зовсім не реалізуються.

Недоліки використання ПП.

  • Складна переносимість інші СУБД (механізм ПП служить певної надбудовою над реляційної моделлю баз даних у рамках СУБД MySQL). Втім, цей недолік не дуже критичний, оскільки на даний момент усі великі СУБД мають відхилення від стандарту ANSI SQL.
  • Поведінка змін користувача буде залежати від порядку виконання складного запиту, який може змінюватися оптимізатором MySQL (якщо не використовується операторSTRAIGHT_JOIN).

Приклад №1.Однією з важливих завдань у моніторингу мережного трафіку є фіксація піків завантаження каналів зв'язку, що надаються, від постачальників інтернету (див. Мал. 2).

mysql

Система моніторингу зберігає інформацію про вхідний та вихідний трафік цілком, і зокрема, по кожному з каналів в окремих таблицях. Тип таблиці –MyISAM. Спрощена форма таблиці такого типу:

Для пошуку максимального стрибка вхідного (так само як і вихідного, але в цьому випадку йде робота з полемsrc) трафіку необхідно просканувати всю таблицю, при цьому порівнюючи значення поляdst, відповідні tprev ( тимчасова мітка на попередньому кроці ) та tcurr (тимчасова мітка на поточному кроці). У цьому полягає основна складність: вВ рамках реляційної моделі неможливо запам'ятати попереднє значення в процесі сканування таблиці та використовувати його безпосередньо. Його можна обчислити за допомогою підзапиту SELECT dst FROM t t2 WHERE t2.t 2 ) (через те, що доводиться для кожної мітки часу обчислювати попередню шляхом сканування всієї таблиці), що сильно позначається на швидкості виконання запиту. У разі наявності в таблиці унікального індексу на поліtобчислення буде проводитися значно швидше, але це тоді, коли точно відомо, що в таблиці немає пропущених тимчасових міток (а така ідеальна ситуація практично не зустрічається), і попередня тимчасова мітка обчислюється просто відніманням потрібного інтервалу з поточної (за унікальним індексом вибірка проходить дуже швидко). У загальному випадку значення тимчасової мітки на попередньому кроці доводиться обчислювати підзапитом, заснованим не на суворій рівності, а на нестрогому, використовуючи сортування, і такий запит, природно, працює значно довше. У реляційному варіанті в підзапиті для швидшої вибірки даних потрібен індекс на поліt, а в зовнішньому запиті йде робота з полемdst, тому в даному випадку в таблиці створено складовий індексt_dstна поля (t,dst).

Реляційний варіант запиту: SELECT MAX ( t1.dst- t_dst); SELECT @max_value;

У таблиці 1 наведено тривалість виконання запитів (при різній кількості рядків у таблиці). Кількість рядків у таблиці визначається тривалістю інтервалу. У разі розглядаються інтервали тривалістю за одну хвилину.