Індекси в mysql

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

Сама по собі побудова таблиць і звернення до їх вмісту - настільки проста і зрозуміла річ, що багато (в тому числі і я, каюсь), освоївши основи, просто перестають вивчати оптимізацію таблиць.

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

Що ж це за звір такий - індексація? Спробую пояснити на прикладі, який згадується в кожній статті про індексацію.

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

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

Припустимо, ви в бібліотеці шукайте книгу "Як вирощувати квіти в сухій місцевості ".

Якщо почати тупо перебирати всі книги в бібліотеці, то на це піде не один день або навіть не один місяць, якщо бібліотека велика.

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

То якого ж чорта ми, та й я, змушуємо наші комп'ютери шукати потрібні нам дані методом тупого перебору?

Тільки тому що вони це роблять швидко?

Так, швидко. Але це якщо пошук треба зробити в тисячах записів. А якщо мова йде про мільйони? Як тільки до бази звернуться тисячі людей, ваш провайдер абсолютно резонно зробить вам зауваження (зі мною таке сталось, вигнали з сервака 👀).

Отже. Правильно побудовані індекси дозволяють знаходити потрібну інформацію "в один клік".

MySQL використовує додаткове місце на диску для зберігання індексних файлів. Це треба знати тільки для того, щоб не плодити кучу непотрібних індексів. Так давайте відразу і визначимося з тим, що нам треба індексувати. А індексувати треба ті поля таблиці, за якими відбувається пошук або сортування даних.

Наприклад, у є таблиця books і таблиця authors. У першій зберігається інформація про книги, а в другій - інформація про їх авторів.

Найлогічніше - шукати в бд книги по назві і автору.

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

Якщо ж додати в таблицю books індекс по полю book_name, то MySQL створить індекс цієї таблиці. Тобто, відсортує таблицю за вказаним полем і розставить мітки і посилання на комірки в реальній таблиці.

Іншими словами, індекс - це копія таблиці, але відсортована за певними правилами, де кожна комірка - це посилання на батьківську комірку в основній таблиці. Сподіваюсь що висловилась достатньо ясно.

Зробивши індекс по полю book_name, комп'ютер знайде за першими буквами потрібні книжки практично миттєво, адже йому тепер не треба перебирати всю таблицю, а досить подивитися ту її частину, де зберігаються записи на потрібну букву.

Ще простіше кажучи, якщо шукати книги на букву "М", то комп'ютер не стане перебирати записи, що починаються на інші літери, знаючи, що там немає записів, які відповідають запиту.

І якщо в книжковій базі знаходиться 100.000 книг, серед яких тільки 30 на букву Ж, то за запитом "знайти всі книги на букву Ж", комп'ютер перебере тільки 30 записів при наявності індексу, або перебере 100.000 записів при його відсутності.

По-моєму, користь очевидна.

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

Скажімо, якщо в таблиці книг є поле book_date, що зберігає дату публікації книги, то додавання індексу до такого полю буде виглядати приблизно так:

           alter table books add index i_date (book_date);

Ця директива вказує MySQL створити індекс по полю book_date.

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

Створення ж індексу по текстовому полю ускладнюється тільки тим, що треба вказати кількість символів від початку запису, за якими треба побудувати індекс. І тут ви вже самі повинні оцінити розмір бази, схожість перших символів різних записів і так далі.

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

А якщо індексувати коди підшипників (думаю, у них є коди), то індексувати краще якомога більше символів, бо велика кількість виробів можуть нести коди, що відрізняються тільки останніми буквами або цифрами.

Іноді не треба спеціально будувати індекси. Досить того, що MySQL сам побудує індекси по полю, якщо при оголошенні структури таблиці ми поставимо полю унікальність UNIQUE, яка говорить про те, що дане поле не може зберігати два однакових значення.

Якщо ви хочете побачити, які поля в таблиці проіндексовані, виконайте команду:

           desc tbl_name;

і MySQL виведе всю інформацію про таблицю tbl_name, враховуючи дані про індексацію полів.

Або більш докладно, тільки про індекси:

           show index from tbl_name;

Залишається ще відзначити, що індексувати можна безліч полів. Не обов'язково одне.

Перш ніж попрощатися, я б хотіла подякувати, що ви прочитали статтю. Сподіваюсь, комусь вона згодиться. І хотіла би підказати вам одну корисну директиву MySQL. Називається вона explain.

Якщо ви поставите це слово перед будь-яким запитом, MySQL не стане виводити вам результат запиту, а покаже детальну інформацію про те, якими засобами довелося скористатися і скільки операцій довелося провести, щоб отримати відповідь на ваш запит.

explain дозволить оцінити ефективність будь-якого запиту і відрегулювати всі індекси вашої бази.