SQLinfo.ru - Все о MySQL Webew.ru: теория и практика веб-технологий

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

#1 03.10.2017 22:47:05

deadka
Администратор
Зарегистрирован: 14.11.2007
Сообщений: 2419

Индекс используется не полностью, хотя запрос (вроде как) этому способствует

Доброго дня, Коллеги!

Есть таблица t(address_id bigint, f1 bigint, f2 bigint, f3 bigint) типа innodb, в ней с полмиллиарда записей.

Инфраструктура: Debian 16.04,  mysql 5.6.16.

analyze table делал.

Есть primary key (address_id, f1)
и индексы i1(address_id, f2) и i2(address_id, f3)

EXPAIN запроса
SELECT * FROM `t` WHERE address_id = 212821379 AND t.`f3` IS NULL;
Показывает в секции key использование 8 байт индекса primary, в то же время в possible keys перечислены все
три индекса (включая primary).

Хотя казалось бы логичнее здесь использовать полностью индекс (address_id, f3).

К слову, если заменить запрос на SELECT * FROM `t` WHERE address_id = 212821379 AND t.`f3`=1;, то используется 17 байт индекса f3.

Ну и если подсказать use index(address_id, f3), то тоже используется 17 байт индекса f3 (правда число rows больше, может в том и дело)?

Прошу прокомментировать поведение explain - почему вторая половинка не хочет быть использована в is null конструкции (is not null тоже пробовал, explain выбирает тот же индекс, что и при is null), в то же время
если на строгое равенство, то индекс используется полностью?

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

Или дело в том, что оптимизатору ( если ему кажется, что он выберет слишком много записей по индексу) просто выгодней сделать FTS в рамках индекса? Тогда каков критерий - до какого количества предполагаемых процентов он будет выбирать по индексу, а в каком случае сделает "FTS" (в рамках индекса, конечно)?


Зеленый свет для слабаков, долги отдают только трусы, тру гики работают только в консоли...

Неактивен

 

#2 03.10.2017 23:01:42

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6756

Re: Индекс используется не полностью, хотя запрос (вроде как) этому способствует

Ты сам ответил на свой вопрос — rows больше при оценке использования индекса над f4 при использовании null, а должно быть сильно меньше. Насколько сильно — не знаю, но основная идея такая: любой вторичный индекс InnoDB в себе содержит на листе значение первичного ключа. А значит, если ты нашел N строк по вторичному ключу, и тебе надо достать остальные данные, то ты вынужден будешь сделать еще N заходов по первичному (можешь сделать не SELECT *, а SELECT f4, и план будет совсем другой — там не нужно будет бежать по первичному). То есть для того, чтобы быть эффективным, вторичный ключ должен найти сильно меньше значений, чем обход первичного + WHERE. У тебя ключи почти одинаковые, поэтому сильно эффективнее ему быть не получается.

Вообще, выглядит так, как будто у тебя не хватает шардирования (или хотя бы партиционирования) по address_id.

Неактивен

 

#3 04.10.2017 13:53:07

deadka
Администратор
Зарегистрирован: 14.11.2007
Сообщений: 2419

Re: Индекс используется не полностью, хотя запрос (вроде как) этому способствует

Угу, идею понял, спасибо!

Значит в том случае, если мы делаем SELECT именно звездочки, а не конкретно взятого поля:

То есть оптимизатор сравнивает примерное количество строк, которое он получит,
1) выгребя строки по первой половинке + при использовании индекса на второй половинке
и
2) выгребя строки по первой половинке и далее "локальным" FTS'ом в рамках того узла индекса, которое даёт условие where address_id, так?

И если он считает, что "мало" строк получит из второй половинки индекса, то достаёт по индексу, а если
считает, что "много" - то достает "локальным FTS"'ом.

Все так?


Зеленый свет для слабаков, долги отдают только трусы, тру гики работают только в консоли...

Неактивен

 

#4 04.10.2017 16:23:56

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Индекс используется не полностью, хотя запрос (вроде как) этому способствует

Имхо, нет.

Оптимизатор выбирает какой индекс использовать. Чтение в порядке вторичного ключа - непоследовательное. Если по индексу будет найдено мало строк, то ок. Если "много", то выгодней последовательный полный скан таблицы.
В данном случае альтернатива не полный скан, т.к. InnoDB хранит записи в порядке первичного ключа. Т.е. использование первой части primary это "последовательное" чтение.
Выгодней последовательно прочитать все строки, удовлетворяющие address_id = 212821379 и для каждой из них проверить условие на `f3`, отбросив не нужные.

Неактивен

 

#5 04.10.2017 20:20:43

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Индекс используется не полностью, хотя запрос (вроде как) этому способствует

vasya, значит все поменяется, если i2 будет первичным ключом?

Неактивен

 

#6 05.10.2017 03:31:40

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Индекс используется не полностью, хотя запрос (вроде как) этому способствует

не понял твою мысль, но отвечая формально - конечно все поменяется, ведь в этом случае f3 не сможет принимать null значения smile

Неактивен

 

#7 07.10.2017 14:15:23

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Индекс используется не полностью, хотя запрос (вроде как) этому способствует

vasya, да, не подумал об этом smile

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson