Використання індексів при ’’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 (не люблю, на загал, експериментувати і оновлювати нормально працюючий софт...)

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

Ctrl + Enter

Konstantin Reu Sr. Data Warehouse Engineer в D&B 21.05.2011 19:48

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

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

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

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

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

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

Ответить

Поддержать

Павло Дзіковський 20.05.2011 10:02

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

Василий Ковбасюк Project manager в Luxoft 19.05.2011 16:54

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

Павло Дзіковський 19.05.2011 17:04

Так! Дійсно:

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

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

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

reality_hacker клирик 3-его уровня, советник боевого губернатора в Гильдия Серых Братьев 19.05.2011 20:25

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

Павло Дзіковський

Павло Дзіковський 19.05.2011 20:41

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

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

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

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

reality_hacker клирик 3-его уровня, советник боевого губернатора в Гильдия Серых Братьев 19.05.2011 21:29

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

Павло Дзіковський 19.05.2011 21:39

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

reality_hacker клирик 3-его уровня, советник боевого губернатора в Гильдия Серых Братьев 19.05.2011 21:45

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

Павло Дзіковський 19.05.2011 21:50

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

reality_hacker клирик 3-его уровня, советник боевого губернатора в Гильдия Серых Братьев 19.05.2011 22:07

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

А у тебя?

Павло Дзіковський 19.05.2011 22:25

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

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”

reality_hacker клирик 3-его уровня, советник боевого губернатора в Гильдия Серых Братьев 19.05.2011 22:33

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

Павло Дзіковський 19.05.2011 22:40

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

reality_hacker клирик 3-его уровня, советник боевого губернатора в Гильдия Серых Братьев 19.05.2011 22:44

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

Павло Дзіковський 19.05.2011 22:50

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

reality_hacker клирик 3-его уровня, советник боевого губернатора в Гильдия Серых Братьев 19.05.2011 23:02

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

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

Павло Дзіковський 19.05.2011 23:11

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

Сергей 20.05.2011 00:34

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

reality_hacker клирик 3-его уровня, советник боевого губернатора в Гильдия Серых Братьев 20.05.2011 00:39

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

Павло Дзіковський 20.05.2011 08:15

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

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

reality_hacker клирик 3-его уровня, советник боевого губернатора в Гильдия Серых Братьев 20.05.2011 09:54

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

reality_hacker клирик 3-его уровня, советник боевого губернатора в Гильдия Серых Братьев 19.05.2011 09:11

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

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

Павло Дзіковський 19.05.2011 09:30

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

grubberr 19.05.2011 16:27

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

reality_hacker клирик 3-его уровня, советник боевого губернатора в Гильдия Серых Братьев 19.05.2011 20:23

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

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

26 комментариев

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

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

26 комментариев

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

Воспользуйтесь аккаунтом