Оптимальне використання MySQL

1. Вступ В процесі надання послуг хостингу ми звертаємо увагу на найбільш часто зустрічаються помилки,...
2. оптимізація запитів
3. ресурсомісткі операції
4. індекси
5. підтримка з'єднання

Вступ

В процесі надання послуг хостингу ми звертаємо увагу на найбільш часто зустрічаються помилки, які роблять користувачі при розробці своїх віртуальних серверів. Одним з "важких" місць для типового веб-майстра є робота з MySQL-сервером. Зазвичай вивчення принципів функціонування SQL і методів роботи з базами даних ведеться по літературі, з якої вибираються тільки актуальні на момент читання речі - як з'єднатися з базою, як зробити запит, як оновити інформацію або додати новий запис в базу даних і так далі.

Такий підхід, звичайно, дає бажаний результат - інтерфейси веб-сайту користувача в результаті виявляються інтегрованими з базою даних. Однак не завжди користувачі замислюються про те, наскільки оптимально працює їх база, як можна оптимізувати відбуваються при роботі з MySQL процеси і яке буде функціонування віртуального сервера при збільшилася навантаженні, "напливами" користувачів в результаті, наприклад, "розкрутки" сайту.

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

Які дані потрібно зберігати в MySQL

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

Замість картинок краще зберігати в MySQL інформацію, на основі якої можна генерувати посилання на статичні картинки в динамічно створюваних скриптами документах.

оптимізація запитів

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

SELECT good, price FROM books LIMIT 20,10

В результаті, MySQL "віддасть" Вам 10 записів з бази починаючи з 20-ї позиції. Видавши результат користувачеві, зробіть посилання "Наступні 10 товарів", як параметр передавши скрипту наступну позицію, з якої буде робитися висновок списку товарів, і використовуйте це число при генерації запиту до MySQL.

Також слід пам'ятати, що при складанні запитів до бази даних (SQL queries) слід запитувати тільки ту інформацію, яка Вам реально потрібна. Наприклад, якщо в базі 10 полів, а в даний момент реально потрібно отримати тільки два з них, замість запиту

SELECT * FROM table_name

використовуйте конструкцію виду

SELECT field1, field2 FROM table_name

Таким чином, Ви не будете навантажувати MySQL непотрібною роботою, займати зайву пам'ять і здійснювати додаткові дискові операції.

Також слід використовувати ключ WHERE там, де потрібно отримувати інформацію, що потрапляє під певний шаблон. Наприклад, якщо потрібно отримати з бази поля з назвами книг, автором яких є Іванов, слід використовувати конструкцію виду

SELECT title FROM books WHERE author = 'Іванов'

Також є ключ LIKE, який дозволяє шукати поля, значення яких "схожі" на заданий шаблон:

SELECT title FROM books WHERE author like 'Іванов%'

В даному випадку MySQL видасть назви книг, значення поля author у яких починаються з 'Іванов'.

ресурсомісткі операції

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

індекси

Індекси використовують для швидшого пошуку по значенню одного з полів. Якщо індекс не створюється, то MySQL здійснює послідовний перегляд всіх полів з найпершої записи до самої останньої, здійснюючи зіставлення вибраного значення з вихідним. Чим більше таблиця і чим більше в ній полів, тим довше здійснюється вибірка. Якщо ж у даній таблиці існує індекс для розглянутого стовпчика, то MySQL зможе зробити швидке позиціонування до фізичного розташування даних без необхідності здійснювати повний перегляд таблиці. Наприклад, якщо таблиця складається з 1000 рядків, то швидкість пошуку буде як мінімум в 100 разів швидше. Ця швидкість буде ще вище, якщо є необхідність звернутися відразу до всіх 1000 стовпцям, тому що в цьому випадку не відбувається витрат часу на позиціонування жорсткого диска.

В яких ситуаціях створення індексу доцільно:

1. Швидкий пошук рядків при використанні конструкції WHERE
   
2. Пошук рядків з інших таблиць при виконанні об'єднання
   
3. Пошук значення MIN () або MAX () для проіндексованого поля
   
4. Сортування або угрупування таблиці у випадку, якщо використовується проіндексувати поле
   
5. У деяких випадках повністю втрачається необхідність звертатися до файлу даних. Якщо все використовувані поля для деякої таблиці цифрові і формують лівобічний індекс для деякого ключа, то значення можуть бути повернуті повністю з індексного дерева з набагато більшою швидкістю.
   
6. Якщо виконуються запити виду
   SELECT * FROM tbl_name WHERE col1 = val1 AND col2 = val2;
   і існує змішаний індекс для полів col1 і col2, то дані будуть повернуті безпосередньо. Якщо ж створені окремі індекси для col1 і для col2, то оптимізатор спробує знайти найбільш обмежений індекс шляхом визначення того, який з індексів може знайти менше рядків, і буде використовувати цей індекс для отримання даних.
   Якщо у таблиці є змішаний індекс, то використовуватиметься будь лівосторонній збіг з існуючим індексом. Наприклад, якщо є змішаний індекс 3-х полів (col1, col2, col3), то індексний пошук можна здійснювати по полях (col1), (col1, col2) і (col1, col2, col3).
   

Детальніше про індексування:

Синтаксис create index
Опис механізму індексування

підтримка з'єднання

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

Також можна підтримувати так зване persistent (постійне) з'єднання до бази, але це можливо в повному обсязі при використанні більш складних середовищ програмування, ніж php або perl в звичайному CGI-режимі, коли інтерпретатор відповідної мови разово запускається веб-сервером для виконання прийшов запиту.