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 имя_индекса;
Сами индексы — это достаточно скрытая часть базы данных. Они никак не влияют на формат написания запросов. Просто их наличие ускоряет выборку данных и замедляет их добавление и бэкап.
Но учитывая, как важна скорость в современном мире и как дешево место на дисках, не стесняйся добавлять индексы на все случаи жизни. Да простят меня админы…
ПЕРЕЙДИТЕ В ПОЛНУЮ ВЕРСИЮ