INDEX: индексы

7.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 = new ArrayList(list);
Collections.sort(alphabeticsList); //коллекция отсортированная по алфавиту

List<String> lengthList = new ArrayList(list);
Collections.sort(lengthList, lengthComparator); //коллекция отсортированная по длине строк

Сортировка коллекций не означает перемещение реальных элементов. Коллекция хранит не реальные объекты, а ссылки на них. Так же и в таблицах SQL. Реальные строки лежат себе и лежат.

И когда нам нужно часто делать выборки по какому-то полю, то мы добавляем еще один индекс к таблице (аналог новой коллекции в Java) и сортируем строки таблицы, храним их отсортированный порядок в специальном файле индексов.

Надеюсь, сравнение с Java немного помогло. Немного практики — и для тебя использование индексов тоже станет самым очевидным решением.

7.2 Добавление индексов в таблицу

Индекс можно указать сразу во время создания таблицы или же добавить после. Чаще всего встречается именно второй сценарий – индексы добавляют по мере роста размеров таблицы и замедления выборки данных.

Добавить индекс в таблицу очень просто:

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

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

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

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

Индексы занимают достаточно много места на диске, так что если какой-то индекс тебе больше не нужен, ты всегда можешь удалить его:

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

Сами индексы — это достаточно скрытая часть базы данных. Они никак не влияют на формат написания запросов. Просто их наличие ускоряет выборку данных и замедляет их добавление и бэкап.

Но учитывая, как важна скорость в современном мире и как дешево место на дисках, не стесняйся добавлять индексы на все случаи жизни. Да простят меня админы…