Скачков Д.А.

Кременчуцький національний університет імені Михайла Остроградського, Україна

Реалізація системи аналізу SQL-запитів

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

На продуктивність веб-додатків, в кінцевому рахунку, впливає величезна кількість різних факторів, починаючи від початкових архітектурних рішень, здібностей і можливостей використовуваних СУБД і апаратного забезпечення, закінчуючи станом власне інформаційних ресурсів.

Індексування даних, робота з індексами - одна з фундаментальних основ роботи будь-якої бази даних. Правильне застосування індексів може підвищити продуктивність інформаційної системи або програми в рази, а то і в десятки разів.

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

У більшості випадків можна оцінювати продуктивність шляхом підрахунку дискових операцій. Для маленьких таблиць можна зазвичай приймати 1 рядок за 1 операцію дискового пошуку (оскільки індекс, швидше за все, в кеші). Для великих таблиць можна вважати, що (при використанні індексів типу B ++ дерев) для знаходження рядка потрібно log (кількість_рядків) / log (довжина_індексного_блока / 3 * 2 / (довжина_індекса + довжина_вказівник_на_данні)) + 1

У загальному випадку для того, щоб змусити повільний SELECT ... WHERE працювати швидше, насамперед потрібно з'ясувати, чи можна додати індекс. Для всіх посилань між різними таблицями повинні, як правило, застосовуватися індекси. Щоб визначити, які індекси використовуються для вибірки SELECT, можна використовувати закладену в СУБД можливість аналізу (EXPLAIN).

Методи аналізу продуктивності БД:

– Виконати myisamchk --analyze для таблиці після того, як вона завантажена відповідними даними. Таким чином для кожної частини індексу буде оновлено значення, яке вказує середнє число рядків, які мають однакові значення (для унікальних індексів це завжди 1, звісно). MySQL буде використовувати це число, щоб вирішити, який індекс слід вибрати для зв'язування двох таблиць за допомогою "не константного виразу". Результат роботи analyze можна побачити в стовпці Cardinality після виконання команди SHOW INDEX FROM ім'я_таблиці.

– Щоб відсортувати індекс і дані відповідно до індексу, використовувати myisamchk --sort-index --sort-records = 1 (якщо потрібно відсортувати за індексом 1). Якщо є унікальний індекс, за яким ви хочете зчитувати всі записи в порядку, відповідному даному індексом, це - гарний спосіб прискорити зчитування записів.

Зазвичай в MySQL індексний блок займає 1024 байти, а вказівним - 4 байта. Для таблиці, яка містить 500000 рядків і має довжину індексу 3 (medium integer) буде потрібно log (500,000) / log (1024/3 * 2 / (3 + 4)) + 1 = 4 дискових операції пошуку.

Оскільки вищезгаданий індекс буде займати приблизно 500000 * 7 * 3/2 = 5,2Mб (якщо враховувати, що індексні буфера зазвичай заповнюються на 2/3), велика частина індексу, швидше за все, виявиться в пам'яті, і для того, щоб знайти рядок , потрібно лише 1-2 звернення до ОС для читання.

Оскільки все кеширується в ОС або на SQL-сервері, уповільнення роботи при збільшенні таблиці буде незначним. І лише після того, як даних стане так багато, що вони перестануть вміщатися в кеш, уповільнення роботи там, де робота програми зводиться тільки до операцій дискового пошуку (кількість яких зростає в log N), стане набагато відчутнішими. Щоб уникнути цього, слід збільшити індексний кеш так, щоб він вміщав збільшену кількість даних.

Проведене дослідження показало, що не існує ресурсів, які безпосередньо автоматизують проведення рефакторинга SQL-коду. Єдиний програмний продукт, який претендує на подібне – SQL Refactor, пропонує тільки можливості форматування коду, виділення частини SQL-коду в якості збереженої процедури і розбиття пропозиції CREATE TABLE на дві частини (з поділом вже існуючих колонок між двома таблицями). Ніяких сервісів по модифікації структури запитів не пропонується.

Литература:

1. Скачков Д.А. Исследование механизмов оптимизации времени отклика веб-приложений. – Наука, техника и образование. – Москва, 2014. – №6. – С.23-25.

2. Hein D. Simloid: Evolution of Biped Walking Using Physical Simulation / D. Hein – Berlin, Institute of Informatic, 2007. – 415 p.