Какой индекс поставить в таблицу mysql?

Question

[Иван Иванов]

Есть большая таблица больше 10 гигобайт, есть запрос такого типа
SELECT * from humans WHERE `lastname_id` = '223' AND `firstname_id` = '72' ORDER BY `rate` DESC LIMIT 10 OFFSET 10
Какой индекс необходимо поставить для максимального быстродействия?
Сейчас стоит на lastname_id, firstname_id и rate а также составной на всех этих трех полях.
Таблица innodb.

Answer 1

[Олег Лысенко]

При построении составных индексов учитывайте то, какой объем данных у вас будет попадать на первый и второй индекс. Выполнив два запроса SELECT lastname_id, count(*) FROM t GROUP BY lastname_id и SELECT firstname_id, count(*) FROM t GROUP BY firstname_id , можно увидеть, сколько записей содержит каждое значение колонки в таблице. Там, где меньше - ту колонку и нужно ставить первой в индекс, так как если среднее кол-во значений lastname_id будет равно 1000, а firstname_id - 20000, то MySQL сразу сократит количество перебираемых записей до 1000, вместо 20000.

Так же советую почитать про EXPLAIN - очень помогает находить тяжелые запросы и лучше понять, как MySQL делает поиск.

Comments

[Иван Иванов]

Когда проверяю через explain то вижу, что используются отдельные индексы lastname_id и firstname_id, а составной индекс (у меня он называется lastname_id_2) не используется.

И данная картинка не есть хорошо... Почему не используется составной индекс?

[Олег Лысенко]

Роман Кистин, поправлю Алексей по поводу автоматического создания индексов, при построении составного индекса, MySQL не создает еще и по отдельности, а в запросах может использовать часть индекса по возможности, поэтому добавление еще и отдельных индексов на каждую колонку в вашей ситуации избыточно. Тут важнее соблюдать порядок индексов для повышения селективности, об этом я писал выше и для того, чтобы правильно использовать части этого индекса в запросах, где используются не все колонки, добавленные в индекс. Пример: есть три поля A, B, C и индекс (A,B,C). Запрос с WHERE A = AND B = AND С = будет использовать индекс полностью, WHERE A = AND B = - только два его столбца, WHERE A = AND С = - только первый его столбец, а вот запрос WHERE B = AND С = или любой без поля А в условии - не будет использовать индекс вообще. Так происходит, по причине того, что MySQL использует индекс слева направо и как только он дойдет до первого несоответствия, дальше поиск будет идти без использования индекса.

Покажите show create table вашей таблицы и запрос, который пытаетесь выполнить.

[Иван Иванов]

Да, про чтение слева на право я знал. Хотя тут получается, что для поиска по колонке "А" можно не создавать отедльный индекс, если есть составной по "А,B,C".
Вот структура таблицы

CREATE TABLE `finder_humans` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `lastname_id` int(11) NOT NULL,
 `firstname_id` int(11) NOT NULL,
 `nickname` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `vk_id` bigint(20) DEFAULT NULL,
 `bdate` date DEFAULT NULL,
 `info` varchar(1023) COLLATE utf8_unicode_ci DEFAULT NULL,
 `photo` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `city_id` int(11) DEFAULT NULL,
 `country_id` int(11) DEFAULT NULL,
 `home_town` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `home_phone` varchar(63) COLLATE utf8_unicode_ci DEFAULT NULL,
 `skype` varchar(127) COLLATE utf8_unicode_ci DEFAULT NULL,
 `facebook` varchar(127) COLLATE utf8_unicode_ci DEFAULT NULL,
 `twitter` varchar(127) COLLATE utf8_unicode_ci DEFAULT NULL,
 `livejournal` varchar(127) COLLATE utf8_unicode_ci DEFAULT NULL,
 `instagram` varchar(127) COLLATE utf8_unicode_ci DEFAULT NULL,
 `occupation_type` enum('work','school','university') COLLATE utf8_unicode_ci DEFAULT NULL,
 `occupation_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `rate` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `vk_id` (`vk_id`),
 KEY `patronymic` (`nickname`),
 KEY `birthday` (`bdate`),
 KEY `city_id` (`city_id`),
 KEY `country_id` (`country_id`),
 KEY `firstname_id` (`firstname_id`),
 KEY `lastname_id` (`lastname_id`),
 KEY `rate` (`rate`),
 KEY `lastname_id_2` (`lastname_id`,`firstname_id`,`rate`)
) ENGINE=InnoDB AUTO_INCREMENT=134257090 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Запрос SELECT * from finder_humans WHERE `lastname_id` = '223' AND `firstname_id` = '72' ORDER BY `rate` DESC LIMIT 10 OFFSET 10

[Олег Лысенко]

Роман Кистин,

Хотя тут получается, что для поиска по колонке "А" можно не создавать отедльный индекс, если есть составной по "А,B,C".

- да.

В вашем случае созданы избыточные одиночные индексы, составной создается последним. Если вы сделаете так

KEY `lastname_id_2` (`lastname_id`,`firstname_id`,`rate`),
KEY `firstname_id` (`firstname_id`),
KEY `lastname_id` (`lastname_id`),
KEY `rate` (`rate`)

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

[Иван Иванов]

Олег Лысенко, Почему индексы избыточны? Да, выходит что индекс lastname_id можно не создавать, но если поиск будет происходить только по firstname_id, то mysql не сможет использовать составной индекс lastname_id_2 для поиска, верно? Аналогично и с rate.
И еще, разве от порядка объявления индексов будет зависеть, какой индекс будет использовать mysql?

[Олег Лысенко]

Роман Кистин, верно, но подобные вещи зависят от того, какие запросы вы собираетесь делать. Тут либо меняется порядок колонок в индексе, либо создаются разные составные индексы под ваши запросы. В любом случае, составной индекс будет работать в разы лучше, чем пересечение двух одиночных. Поэтому, если нельзя покрыть вариации ваших запросов одним составным, стоит подумать о создании нескольких по разным комбинациям полей.

[Иван Иванов]

В общем, проблему решил удалением лишнего индекса lastname_id, теперь mysql использует нужный индекс и не использует filesort

[Иван Иванов]

Олег Лысенко, Опять напоролся на те же грабли. Какого ч*рта mysql не хочет использовать подходящий индекс? Например
EXPLAIN SELECT * FROM `mp3s` AS `mp3` WHERE `artist` = 'Аркадий Кобяков' AND `id` > '453509' ORDER BY `id` LIMIT 10

Используется PRIMARY и как следствие перебор 52534363 строк, вместо того, чтобы использовать подходящий составной artist_2. Как заставить mysql использовать нужный индекс?

[Олег Лысенко]

Роман Кистин, show create table покажите

[Иван Иванов]

Олег Лысенко,

CREATE TABLE `mp3s` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `artist` varchar(511) COLLATE utf8_unicode_ci NOT NULL,
 `aid` int(11) NOT NULL,
 `oid` int(11) NOT NULL,
 `lid` int(11) NOT NULL,
 `song` varchar(511) COLLATE utf8_unicode_ci NOT NULL,
 `duration` int(11) NOT NULL,
 `show_artist_url` int(11) NOT NULL,
 `show_song_url` int(11) NOT NULL,
 `url_time` bigint(20) NOT NULL,
 `size` int(11) DEFAULT NULL,
 `bitrate` int(11) DEFAULT NULL,
 `artist_id` int(11) DEFAULT NULL,
 `public` enum('0','1') COLLATE utf8_unicode_ci NOT NULL DEFAULT '1',
 PRIMARY KEY (`id`),
 KEY `artist` (`artist`(255),`song`(255)),
 KEY `oid` (`oid`,`aid`),
 KEY `size` (`size`),
 KEY `lid` (`lid`),
 KEY `artist_2` (`artist`(255),`id`)
) ENGINE=InnoDB AUTO_INCREMENT=117625329 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

[Олег Лысенко]

Роман Кистин, у меня, например, MySQL выбирает другой индекс, но все равно не тот. В этом и есть кривость самого механизма БД по выбору оптимального индекса, это довольно известная проблема. В таких случаях посмотрите в эту сторону, не зря разработчики предоставляют возможность явно указать, какой индекс использовать.

[Иван Иванов]

Олег Лысенко, Супер, то что надо, спасибо! Осталось только это прикрутить к Kohana ORM.

Answer 2

[Алексей]

Если у вас стоит составной индекс на три поля lastname_id, firstname_id и rate, то автоматически mysql создает индекс для каждого поля в отдельности. Индексы ставятся на те поля, по которым идет поиск в запросах. В данном случае у вас правильно настроены индексы, если исходить из вашего SQL-запроса.

Comments

[Иван Иванов]

"Если у вас стоит составной индекс на три поля lastname_id, firstname_id и rate, то автоматически mysql создает индекс для каждого поля в отдельности" - это не так. Я их создавал сам, mysql их не создает автоматически. И еще, когда проверяю через explain то вижу, что используются отдельные индексы lastname_id и firstname_id, а составной индекс (у меня он называется lastname_id_2) не используется.

И данная картинка не есть хорошо...