MySQL: використання індексів
Продовжуючи вчорашню тему 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:
і MySQL виведе всю інформацію про таблиці tbl_name, включаючи позначки про індексацію полів.
Або більш докладно, тільки про індекси:
show index from tbl_name;
Залишається ще відзначити, що в індексі може брати участь безліч полів. Не обов'язково одне.
Якщо в базі накладних найчастіше проводиться пошук по сумі з урахуванням діапазону дат, то логічно створити індекс по цим двом полях: сума і дата. Саме, створити один індекс по двох полях, а не два індексу по кожному полю!
alter table tbl_name add index i_name (field1, field2);
Пошук по унікальним індексам проводиться трохи швидше, тому, якщо значення якогось поля має бути обов'язково унікальним - не полінуйтеся відзначити це в конструкції таблиці. MySQL віддячить вам швидкістю своїх відповідей на ваші питання.
Перш ніж попрощатися, я б хотів підказати вам одну корисну директиву MySQL. Називається вона explain.
Якщо ви поставите це слово перед будь-яким запитом до бази даних, MySQL не стане виводити вам результат запиту, а покаже детальну інформацію про те, якими засобами довелося скористатися і скільки операцій довелося провести, щоб отримати відповідь на ваш запит.
Це чарівне слово explain дозволить оцінити ефективність будь-якого запиту і відрегулювати всі індекси вашої бази.
Вдалою індексації, і нехай прибуде з вами порядок!