INDEX: індекси

1. Причини появи індексів

Ще одна важлива річ, без якої не може бути баз даних, — це індекси.

Уяви собі ситуацію, коли в таблиці user є 10 мільйонів користувачів, і тобі потрібно вивести всіх, у кого рівень вищий за 90. Цей запит написати дуже просто:

SELECT * FROM user WHERE level > 90
        

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

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

Добре, а що робити, якщо тепер нам потрібно відібрати користувачів, чия дата реєстрації була до 2020 року? Знову сортувати за датою реєстрації та використовувати бінарний пошук.

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

А як зберігати дані, відсортовані одночасно за різними полями?

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

Припустимо, є 10 користувачів з id: {1, 2, 3, 4, 5, 6, 7, 8, 9, 10}.

Ти вирішуєш відсортувати їх за рівнем: тоді масив їх id буде, наприклад, таким: {9, 2, 3, 1, 5, 4, 8, 6, 7, 10}.

А якщо відсортувати їх за датою, ми отримаємо, скажімо: {10, 1, 8, 7, 2, 3, 5, 9, 6}.

Масиви цих id називаються індексами. Самі елементи великі, їх ми не чіпаємо. У Java ми не чіпаємо об'єкти, а зберігаємо їх посилання, в SQL ми не чіпаємо реальні рядки, а зберігаємо їх номери.

Давай я ще раз запишу це у вигляді Java-коду:

List<String> list = List.of("А", "С", "B", "Z", "Сc", "Bb", "Zz", "Y"); // це список об'єктів
List<String> alphabeticsList = новий ArrayList(list);
Collections.sort(alphabeticsList); //колекція відсортована за абеткою
 
List<String> lengthList = новий ArrayList(list);
Collections.sort(lengthList, lengthComparator); //колекція відсортована за довжиною рядків

Сортування колекцій не означає переміщення реальних елементів. Колекція зберігає не реальні об'єкти, а посилання на них. Також і в таблицях SQL. Реальні рядки лежать собі та й лежать.

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

Сподіваюся, порівняння з Java трохи допомогло. Трохи практики — і для тебе використання індексів також стане найочевиднішим рішенням.

2. Додавання індексів до таблиці

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

Додати індекс до таблиці дуже просто:

ALTER TABLE таблиця
    ADD INDEX ім'я_індексу (колонка);
        

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

Додати зіставний індекс до таблиці теж дуже просто:

ALTER TABLE таблиця
    ADD INDEX ім'я_індексу (колонка1, колонка2, колонка3, ...);
        

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

ALTER TABLE таблиця
    DROP INDEX ім'я_індексу;
        

Самі індекси — це доволі прихована частина бази даних. Вони не впливають на формат написання запитів. Просто їх наявність прискорює вибірку даних та уповільнює їх додавання та бекап.

Але враховуючи те, наскільки важливою є швидкість у сучасному світі і яке дешеве місце на дисках, не соромся додавати індекси на всі випадки життя. Хай пробачать мені адміни…