Використання індексів при ’’order by

Розбираюсь з ефективністю виконання запитів. Таблиця res індексована по полю ag, містить записи змінної довжини (серед инших полів, зокрема, є тип text). Версії MySQL: 4.1.22-community-nt, 5.0.51a-community.

explain select ag from res order by ag
Результат: using index

explain select ag, cliens from res order by ag
Результат: using filesort

explain select * from res order by ag
Результат: using filesort

Тобто мені треба вибирати і сортувати по індексу не тільки індексоване поле — а ще й всякі-різні инші. Але запит індексованого поля — сервер сортує по индексу, запити ж більшої кількости полів — вже ні.

Чому індекс використовується в першому випадку, але не використовується в другому і третьому — мені, з загальних міркувань, зрозуміло :-) Але в мануалі не бачу згадки про таке обмеження — то, можливо, воно так чи инакше обходиться? Чи проблеми нема в новій версії MySQL (не люблю, на загал, експериментувати і оновлювати нормально працюючий софт...)

Чи хто що підкаже?

Допустимые теги: blockquote, code, em, pre, i, li, ol, strike, strong, u, ul, a.
Ctrl + Enter
Допустимые теги: blockquote, code, em, pre, i, li, ol, strike, strong, u, ul, a.
Ctrl + Enter

не спец я по mysql. Но как я вижу проблема понятна и логична.

в первом случае индекс покрывающий, поэтому он и используется.

второй случай и третий — у Вас есть кластерный индекс. Оптимизатор считает что дешевле использовать его (ес-но путем сканирования), из-за того что исли б он заюзал индекс по полю ag, то ему бы прилось прибегать к большому колучеству операций «key lookup»

Если ваша цель оптимизировать второй запрос, то индекс ag переписать как составной (ag, client)

или если MYSQL позволяет создавать индексы с include columns, то это будет лучший вариант

TSQL style: сreate index idx on res(ag) include (clients)

Сторгували-сьмо :-) Спасибі за розбір теми.

Во 2м и 3м случаях будет использоваться индекс, если будет указано условие поиска по ag, иначе сервер не видит нужды вычитывать блоки индекса, т.к. все равно выбираются все блоки таблицы (в ОП отсортирует, зачастую это дешевле чем дополнительное чтение), в 1м случае он может обойтись ТОЛЬКО блоками индекса.

Так! Дійсно:

explain select ag, cliens from res where ag > ’aa’ order by ag

Результат: Using where

І це цілком логічно :-) Спасибі.

Не ну если цель просто заюзать индекс, то Ок, но думается мне что такой запрос будет работать намного медленнее чем простая сортировка таблицы если фильтр не отсеет значительное количество элементов.

Це тільки приклад. Реальний запит, який мені підходить:

select ag, cliens from res where ag = ’aa’ order by ag, cliens

Індекс по (ag, cliens) вже є — так що вибирається порівняно невелика частина таблиці і все виглядає гарно-швидко.

InnoDB таблиці — так, матиму на увазі, звісно ж, але перше треба, щоб знайшлась підходяща задача для такої серйозної революції в своїй БД.

И какой глубинный смысл в этом запросе вообще сортировать по аг если там гарантировано будет одно значение?

Ні, сортування йде по двох полях — перше з них фіксоване, друге — проходиться по індексу (ag, cliens)

Если убрать ag из order клозы что изменится?

Не буде підходящого індексу, бо індекс MySQL може юзати індекс (ag, cliens) або по першій колонці, або по двох разом. І навіть якщо буде індекс (cliens), то, все’дно, це було би подвійне юзанян індексів — спершу на вибірку. потім на сортування. Так же — все робиться за один прохід.

Только что выполнил у себя запрос и так и этак, план получился одинаковый: Using where; Using index

А у тебя?

Ось повний тест:

create table res1 (clavis char(100) primary key, ag varchar(100) not null, cliens char(100) not null, additio text)

alter table res1 add index (ag, cliens)

insert into res1 values (’aa’, ’bb’, ’cc’, ’dd’)
insert into res1 values (’aa1′, ’bb’, ’cc’, ’dd’)
insert into res1 values (’aa2′, ’bb’, ’cc’, ’dd’)

insert into res1 values (’aa3′, ’bb1′, ’cc’, ’dd’)

explain select * from res1 where ag = ’bb’ order by ag, cliens

“Using where”

explain select * from res1 where ag = ’bb’ order by clavis

“Using where; Using filesort”

Шoта ты нахимичил, т.к. у тебя в обоих случаях индекс не используется.

Ой? В обох випадках «Possible keys: ag» — так що індекс використовується. Але в другому випадку є ще додатковий «Using filesort», якого нема в першому — оце й економія, про яку мова.

Possible — не значит что он используется, а вот когда он пишет в колонке Extra: Using index как у меня — то значит.

У нас можуть бути різні версії сервера і, вочевидь, йдеться про різні запити. З тими таблицями і з тими запитами, з якими я маю справу, «Possible keys» практично завжди показує індекси, які використовуються.

Possible keys" практично завжди показує індекси, які використовуються.

откуда ты знаешь?

Ну, маю таке відчуття :-) Якщо ж серйозно — вже хочу вийти з диспуту, притомився.

using index — означает что результат выбирается используя только индекс без обращения к самой таблице.

Правильно, именно этого вроде и пытается добится топик стартер, но судя по его explain plan у него это не получается.

Ні, я не добиваюсь формування результату по самому індексу — це видно хоча б по тому, що пишу в запитах «select *».

Але в наведеному прикладі в другому випадку робиться додаткове сортування вибраних результатів — «Using filesort», в першому ж — ні. Саме цього я й добивався.

Да, согласен, здесь я был не прав.

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

По логике вещей тебе должен помочь clustered index: dev.mysql.com/...dex-types.html т.е. нужно сделать так что бы ag был кластерным индексом.

Причину я, звісно ж, розумію, що вже... За посилання — спасибі, розбиратимусь.

впринципе такое поведения сервера вроде как вполне логично,
проще отсортировать таблицу заново чем идти по индексу и построчно заглядывать в основную таблицу за остальными данными ( которые могут быть где угодно на диске )
как вариант расширить индекс на несколько полей т.е. для второго запроса сделать index (’ag’, ’cliens’ ) - тогда сервер должен заюзать Covering Index как в первом запросе

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

Подписаться на комментарии