MySQL: Сортировка по первичному ключу или индекс?

Question

[Роман Янчук]

Начал оптимизировать базу данных. Подошел к изучению вопроса индексов в MySQL.
Как известно, ёмкость запросов определяется командой EXPLAIN.
Есть таблица ID MEMBER VIP
Запрос:
SELECT * FROM vip WHERE member='1'
Команда explain показывает значение rows - 8500. То-есть, чтобы выполнить запрос приходится проверять все строки.

Получается, мне надо установить индекс на поле member и проблема будет решена.
Но! Почему при таком запросе

SELECT * FROM vip WHERE member='1' ORDER BY id DESC LIMIT 0,1

Команда explain показывает значение rows - 1. То-есть, все отлично. Запись нашлась сразу.

ID при этом является ПЕРВИЧНЫМ КЛЮЧОМ, то-есть основным индексом.

Получается, для уникальных значений достаточно всего лишь сортировки поля по первичному ключу?
Для неуникальных, понятно, не подойдет, так как неизвестен LIMIT.

Или все-таки я что-то не так понимаю и индекс на поле member в данном случае обязателен?

Заранее спасибо за уделенное время.

Answer 1

[Wol_fi]

Каждый лист btree индекса содержит в себе значение primary key для конкретного значения. (извините за тавтологию)

Comments

[Роман Янчук]

Спасибо за ответ.
Так что в итоге? Можно на `member` индекс не устанавливать и пользоваться сортировкой? Скорость выполнения запроса будет одинаковой?

[Wol_fi]

Роман Янчук: нет, на mebmer нужен индекс, просто индекс mebmer содержит в себе помимо значения поля member ещё и значение поля primary key, в нашем случае - id. Поэтому у вас в данном случае происходит выборка и сортировка по одному и тому же индексу, что и даёт такой результат.
Так же можете пробовать EXPLAIN SELECT id FROM vip WHERE member = '1' LIMIT 1;
Explain вам покажет, что к таблице данный запрос не обращался, и все данные вывел из индекса (так называемый "покрывающий" индекс).

[Wol_fi]

Роман Янчук: если есть желание, хорошая книжка на эту тему - www.ozon.ru/context/detail/id/6573935 . Там хорошо всё это дело объясняется.

[Роман Янчук]

Wol_fi: Использовав Ваш запрос, rows остался на значение 8500, то-есть прочитав все значения.
Без индекса на member и сортировки по ID rows остается на значение 1.
То-есть не смотря на то, что при отсутствие индекса на member и при сортировке по ID (Пер.ключ) rows выдает 1, скорость работы все-равно ниже, нежели если установить индекс на member и убрать сортировку по ID?

Спасибо за уделенное время. Если не состав труда, уточните еще по этому моменту.
За ссылку на книгу благодарю.

[Роман Янчук]

Wol_fi: Уточню, я так и не устанавливал индекс на member. Все запросы выполнялись исключительно при наличие ПЕРВИЧНОГО КЛЮЧА на ID

[Роман Янчук]

Wol_fi: Так-же и в другой таблице...
ID MATCH

Делаю составной индекс на match и ID, при запросе
SELECT * FROM `test` WHERE `match`='500' ORDER BY `id` DESC LIMIT 0,1
rows колеблется до 40. В зависимости от того, сколько повторяющихся `match` такого значения.

Снимаю составной индекс (удаляю), остается только первичный ключ на ID. Повторяю запрос:
SELECT * FROM `test` WHERE `match`='500' ORDER BY `id` DESC LIMIT 0,1
rows всегда 1.

Вот это меня волнует. Судя по значению rows, получается, в таких случаях индекс вообще не нужен? :-( Или все-таки я туплю?

[Wol_fi]

Роман Янчук: Индекс нужен в любом случае. Сортировка по первичному ключу (без других индексов) будет работать только если у вас нет блока WHERE и JOIN'ов или у вас в блоке WHERE только первичный ключ. Составной индекс вам не нужен. Обычный индекс хранит primary key по умолчанию(без участия извне)

[Роман Янчук]

Wol_fi: Спасибо!
Проверил на скорость выполнения запроса.
Не смотря на то, что rows при explain показывает всегда 1, скорость запроса зависит от отдаленности строки при сортировке. То-есть если сортируем DESC, и строка находится близко (ID 8000), то скорость высока, а если ID 100, то запрос выполняется чуть больше секунды.
Получается, значение rows в данном случае вводит в заблуждение.
Спасибо большое за консультацию.