MySQL индексы и ускорение выборки данных

Рейтинг: 1Ответов: 3Опубликовано: 24.04.2015

Создаётся простая таблица данных:

CREATE TABLE IF NOT EXISTS `table_1` (
    id INT NOT NULL AUTO_INCREMENT,
    user_id INT( 33 ),
    user_name VARCHAR( 255 ),
    PRIMARY KEY ( `id` )
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Как видно из этого примера, в колонке id создаются уникальные ключи (за счёт AUTO_INCREMENT). Поле user_id также содержит уникальные значения идентификаторов пользователей, генерируемые скриптом (использую mt_rand) во время регистрации в личном кабинете.

Для выборки делаю следующий запрос:

SELECT `user_name` FROM `table_1` WHERE `user_id`=28572

Задумался об использовании индексов в своей таблице данных, для ускорения выборки. В документации написано:

Наличие индекса может существенно повысить скорость выполнения некоторых запросов и сократить время поиска необходимых данных за счет физического или логического их упорядочивания.

Вопросы:

  1. Нужно ли создавать индексы для поля user_id для ускорения выборки из миллионов записей, если все значения идентификаторов в этом поле и так уникальны?

  2. Если всё таки нужно, то какой индекс создать: кластерный или не кластерный (не совсем понимаю различия между ними)?

  3. Условие WHERE (в моём примере сверху) заставляет СУБД перебирать все записи в таблице? Или же СУБД сразу обращается конкретно только к тем записям, которые удовлетворяют условиям поиска (user_id = 28572), не затрагивая при этом остальные записи?

sql mysql
Источник: Stack Overflow на русском

Ответы

▲ 4

Нужно ли создавать индексы для поля user_id для ускорения выборки из миллионов записей, если все значения идентификаторов в этом поле и так уникальны?

Да, нужно - потому что собираетесь часто делать выборку из таблицы по признаку содержимого этого поля. Раз архитектурой обусловлена уникальность данных, попадающих в это поле, будет хорошей идеей использовать UNIQUE индекс. Если на поле назначен уникальный индекс - БД не даст вставить запись с дублирующимся его значением. Такой шаг в сторону нормализации.

Если всё таки нужно, то какой индекс создать: кластерный или не кластерный(не совсем понимаю различия между ними)?

Кластерный индекс не нужен, пока Вы явно не осознаете его необходимость. Сюрприз: в InnoDB первичный ключ всегда кластерный. И он у Вас уже есть. Не думайте пока об этом.

Условие WHERE заставляет СУБД перебирать все записи в таблице?

Да. Узнать это (и многое другое) можно, выполнив запрос с ключевым словом EXPLAIN перед ним (EXPLAIN SELECT * FROM ... WHERE ... ORDER BY ... LIMIT ...). Если происходит выборка с условием, и условие включает в себя поле, не покрытое подходящим индексом - MySQL будет, скорее всего, выполнять полнотабличное сканирование. Это дорогая с точки зрения ввода-вывода операция, поэтому грамотная расстановка индексов - суть половина успеха оптимизации БД для быстрой работы.

▲ 4

Добавлю к ответу @Mirdin

  1. Класерный индекс физически упорядочивает таблицу по индексу. Самый быстрый (для поиска). Очевидно что он может быть только один. PK всегда кластерный индекс по умолчанию.

  2. Вы можете сократить время выполнения на 50% (в среднем) если добавите LIMIT 0,1 - т.к. база не знает о уникальности вашего столбца. Без лимита она будет перебирать все значения таблицы, даже если уже найдёт совпадение.

Но правильно да, сделать этот столбец:

ALTER TABLE `table_1` ADD UNIQUE INDEX `user_id`

Касательно вашего комментария. Это довольно сложный вопрос и он сильно зависит от того какие запросы вы делаете. Если у вас составной индекс по столбцам (А, Б, В) то индекс принесёт пользу когда вы ищете (WHERE) по одному или нескольким столбам слева, т.е. А, (А и Б), (А и Б и В). А если вы ищете только по Б - работать не будет. С другой стороны если у вас есть отдельные индексы по, например A и Б, то при включении обоих полей скорее всего отработает только 1 индекс, а второй будет простой поиск (хотя тут я не уверен). Нужно понимать что наибольший урон приходится на первую фильтрацию - т.е. когда сканируется таблица на миллион записей, например. Если по одному индексу отсеялось 99%, то поиск по 10000 записей, даже без индекса, уже не так смертельно.

В общем как вы наверное поняли однозначного ответа нет. Действуйте последовательно, добавьте один индекс и посмотрите на результат. Вполне возможно что одного индекса будет достаточно.

▲ 2
  1. Да, если часто фильтруете именно по этому полю.
  2. Не кластерный, у вас уже есть PK.
  3. Сервер не владеет магией, поэтому, если нет индекса, будет перебирать все записи в таблице, если есть то пройдется по структуре которую реализует индекс, но это тоже не "сразу обращается конкретно только к тем записям"...

P.S. Написал вообщем, конкретно в MySQL могут быть какие-то отличия.