MySQL використання індексів
Побудова таблиць і звернення до вмісту — настільки проста і зрозуміла річ, що багато хто, освоївши основні прийоми, просто перестають вивчати синтаксис побудови та оптимізації таблиць глибше.
А тим часом, лише індексація таблиць часом піднімає продуктивність сайту в кілька разів.
Що ж таке індексація? Спробую пояснити з прикладу.
Я думаю, зайве пояснювати, навіщо все це робиться, і чому бібліотекарі так ревно стежать за порядком розміщення книг на стелажах. Але я дозволю собі звернути вашу увагу на порівняльну ефективність пошуку в такій структурованій системі, яку, до речі, зазвичай нехтують при побудові та використанні комп'ютерних баз даних.
Припустимо, ви в бібліотеці шукайте якусь книжку.
Якщо ви почнете тупо перебирати всі книги у бібліотеці, то у вас на це піде не один день або навіть не один місяць, якщо це велика бібліотека.
Так якого ж хрону, перепрошую за різкість, ми змушуємо наші комп'ютери шукати необхідні нам дані методом тупого перебору? Лише тому, що вони це роблять швидко?
Так, швидко. Але це якщо пошук треба зробити у тисячах записів. А якщо йдеться про мільйони? Чи ви думаєте, що ваша база даних не така велика, щоб займатися її оптимізацією? Помиляєтесь, дорогі мої. Як тільки до вашої бази звернуться сотні людей, так ваші тисячі записів відразу перетворяться для движка баз даних в мільйони! І ваш провайдер цілком слушно зробить вам зауваження.
Отже. У тому, що бази даних треба індексувати — не сумнівається жоден розсудливий програміст. Правильно збудовані індекси дозволяють знаходити потрібну інформацію "в один дотик".
Як це відбувається технічно, нам знати не обов'язково. Достатньомати на увазі, що MySQL, як і будь-який інший движок баз даних, використовує додаткове місце на диску для зберігання індексних файлів. Це треба знати лише для того, щоб не плодити непотрібних індексів. У всьому треба знати міру. Навіть у такій корисній справі, як індексація таблиць.
Тож давайте відразу і визначимося з тим, що нам треба індексувати.
А індексувати треба ті поля таблиці, якими відбувається пошук чи сортування даних.
Якщо ж ми додамо в таблицю books індекс поля book_name (назва книги), то MySQL створить індекс цієї таблиці. Тобто, відсортує таблицю за вказаним полем і розставить мітки та посилання на комірки в реальній таблиці.
Побудувавши індекс по полю book_name, наш комп'ютер знайде по перших літерах потрібні нам твори практично миттєво, адже йому тепер не треба перебирати всю таблицю, а достатньо подивитися на її частину, де зберігаються записи на потрібну літеру.
Ще простіше кажучи, якщо ми шукаємо книги на літеру "М", то комп'ютер не перебиратиме записи, що починаються на інші літери, чудово знаючи, що там немає записів, що задовольняють запиту.
І якщо в нашій книжковій базі знаходиться 100.000 книг, серед яких лише 30 на літеру Ж, то на запит "знайти всі книги на літеру Ж", комп'ютер перебере лише 30 записів за наявності індексу, або перебере 100.000 записів за його відсутності.
На мою думку, користь очевидна.
До речі, індексація текстових полів — це трохи складніше, ніж індексація полів іншого типу. Поясню. Цифрові, літерні, булеви, поля дат, часу та інші індексуються, як правило, без будь-яких додаткових роздумів.
alter table tbl_books add index i_date (book_date);
Ця директива вказує MySQL створити індекс по полю book_date.
Так само можна створити індекси для інших полів, за якими нам цікаво проводити пошук або сортування.
Створення індексу по текстовому полю ускладнюється тільки тим, що треба вказати кількість символів від початку запису, за якими треба побудувати індекс.
І тут ви самі повинні оцінити розмір бази, схожість перших символів різних записів тощо.
Наприклад, якщо ми будуємо індекс назви вулиць, то нам не треба індексувати їх по всій довжині, а можна припустити, що переважна більшість записів почнуть відрізнятися вже десь на п'ятій букві.
А якщо ми індексуємо коди кулькових підшипників, то краще індексувати якомога більше символів, бо велика кількість виробів можуть нести коди, що відрізняються тільки останніми літерами або цифрами.
Якщо ви бажаєте побачити, які поля в таблиці проіндексовані, дайте команду MySQL:
desc tbl_name;
MySQL виведе всю інформацію про таблицю tbl_name, включаючи позначки про індексацію полів.
Або більш детально, тільки про індекси:
show index from tbl_name;
Залишається відзначити, що у індексі може брати участь безліч полів. Не обов'язково одне.
Якщо в базі накладних найчастіше проводиться пошук за сумою з урахуванням діапазону дат, то логічно створити індекс цих двох полів: сума і дата. Саме створити один індекс по двох полях, а не два індекси по кожному полю!
alter table tbl_name add index i_name (field1, field2);
Пошук за унікальними індексами проводиться трохи швидше, тому якщо значення якогось поля має бути обов'язково унікальним — не полінуйтеся відзначити це в конструкції таблиці. MySQL віддячить вам швидкістю своїх відповідей на ваші запитання.
Про синтаксис створення індексів найкращенаписано тут: http://www.mysql.com/doc/A/L/ALTER_TABLE.html.
Перш ніж попрощатися, я хотів би підказати вам одну корисну директиву MySQL. Називається вона explain.
Якщо ви поставите це слово перед будь-яким запитом до бази даних, MySQL не стане виводити вам результат запиту, а покаже детальну інформацію про те, якими засобами довелося скористатися і скільки операцій довелося зробити, щоб отримати відповідь на ваш запит.
Це чарівне словоexplain дозволить оцінити ефективність будь-якого запиту та відрегулювати всі індекси вашої бази.
Вдалої індексації, і нехай прибуде з вами порядок!