Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Оптимизатор не выбирает подходящий индекс / 19 сообщений из 19, страница 1 из 1
02.06.2017, 14:42
    #39464713
colibrii
colibrii
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизатор не выбирает подходящий индекс
Подскажите, пожалуйста, если кто сталкивался, почему может выбираться неверный индекс?
Есть таблица с полем t, по которому построен индекс.
В таблице миллионы строк, при этом для большинства значений t запрос на выборку количества заданных значений использует корректный индекс (t) и работает быстро (секунды).
А вот для одного значения (202) индекс не используется, даже будучи принудительно задан через USE INDEX и FORCE INDEX.
Индекс не битый, проверял на разных серверах и версиях MySQL (5.5.25 и 5.5.54).
EXPLAIN выдает только одно различие в типе запроса: ref или index, но мануал по оптимизатору ничего полезного мне по этой теме не дал.
Спасибо!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.06.2017, 18:56
    #39464959
miksoft
miksoft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизатор не выбирает подходящий индекс
colibrii,

Покажите DDL таблицы.
Гипотеза с потолка - поле t строкового типа и там реальное значение, например, '202 '. Т.е. 202 с пробелом.
Покажите вывод запроса:
Код: sql
1.
SELECT t, HEX(t), COUNT(*) FROM kladr GROUP BY t ORDER BY COUNT(*) DESC LIMIT 12
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.06.2017, 12:54
    #39465133
colibrii
colibrii
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизатор не выбирает подходящий индекс
miksoftcolibrii,

Покажите DDL таблицы.
Гипотеза с потолка - поле t строкового типа и там реальное значение, например, '202 '. Т.е. 202 с пробелом.
Покажите вывод запроса:
Код: sql
1.
SELECT t, HEX(t), COUNT(*) FROM kladr GROUP BY t ORDER BY COUNT(*) DESC LIMIT 12



Поле t целочисленное, загрузка данных делается скриптом, который передает число, т.е. пробел попасть туда не мог бы никак, даже случайно.
Как будто тут какой-то косяк с тем, что сервер воспринимает 202 как неприменимое к индексу значение.
Вот это ref=NULL в EXPLAIN говорит, что тип аргумента для поиска непонятен. Хотя для 204 или 210 всё нормально.
Пробовал CAST(202 AS UNSIGNED), не помогло.
Мистика...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.06.2017, 13:07
    #39465138
colibrii
colibrii
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизатор не выбирает подходящий индекс
Вот провел еще эксперимент.
Оптимизатор изучает статистику и принимает странное решение о неприменимости индекса.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.06.2017, 03:48
    #39465399
javajdbc
javajdbc
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизатор не выбирает подходящий индекс
colibrii,

...вы не стесняйтесь, как вас просили, покажите DDL.
и в частности все что связано с индексами.

Пока видно что есть какието индексы t_val , up_t и может быть еще какие.
При этом попытка USE INDEX(t) выглядит страных...
или у вас есть еще один индекс "t" ?
Попробуийте USE INDEX(t_val)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.06.2017, 21:16
    #39465588
miksoft
miksoft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизатор не выбирает подходящий индекс
colibrii,

И сколько всего записей в таблице?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.06.2017, 01:00
    #39465628
colibrii
colibrii
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизатор не выбирает подходящий индекс
javajdbccolibrii,

...вы не стесняйтесь, как вас просили, покажите DDL.
и в частности все что связано с индексами.

Пока видно что есть какието индексы t_val , up_t и может быть еще какие.
При этом попытка USE INDEX(t) выглядит страных...
или у вас есть еще один индекс "t" ?
Попробуийте USE INDEX(t_val)

Пробовал и USE INDEX и FORCE INDEX для t и t_val, ничего не меняется (индекс отображается в поле key, но не используется).

Удалил несвязанные поля и индексы для простоты, таблица такая:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
--
-- Структура таблицы 'klad'
--

CREATE TABLE IF NOT EXISTS klad (
  id int(4) unsigned NOT NULL AUTO_INCREMENT,
  t int(4) unsigned NOT NULL,
  lvl int(4) unsigned NOT NULL,
  nm varchar(127) NOT NULL DEFAULT 'Новый',
  dt date NOT NULL,
  PRIMARY KEY (id),
  KEY t_val (t,nm)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC AUTO_INCREMENT=18619425 ;
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.06.2017, 01:04
    #39465631
colibrii
colibrii
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизатор не выбирает подходящий индекс
miksoftcolibrii,

И сколько всего записей в таблице?

Статистика вот такая, как на картинке.
Оптимизатор немного ошибается в оценках для значений 195 и 206, но не критично (не на порядок). И берет правильный индекс сам.
А вот 202 какое-то заколдованное число, он думает, что их 18 млн, а не 1 млн. Даже если индекс указать принудительно, он использован не будет.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.06.2017, 03:09
    #39465640
javajdbc
javajdbc
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизатор не выбирает подходящий индекс
colibrii,

kel_len=387 -- чертовшина какаято, там же только два поля Т и НМ.
Советую сделать так:
1. создать таблицу БЕЗ индексов кром примари.
2. салить данные.
3. создать индекс ТОЛЬКО по "Т"
4. протестировать
5. если ОК, удалит индекс по "Т" и сделат' индекс по Т,НМ
6. протестировать.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.06.2017, 03:10
    #39465641
javajdbc
javajdbc
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизатор не выбирает подходящий индекс
поправки:
1. создать таблицу БЕЗ индексов кромЕ примари.
2. Залить данные.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.06.2017, 09:05
    #39465690
Melkij
Melkij
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизатор не выбирает подходящий индекс
javajdbckel_len=387 -- чертовшина какаято, там же только два поля Т и НМ.
Цифра правильная, 4 байта int + varchar (2 + 127*3 байта) как раз получается 387 байт каждая индексная запись.
Но вот почему просмотр всего индекса целиком?.. Не знаю.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.06.2017, 19:40
    #39466184
miksoft
miksoft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизатор не выбирает подходящий индекс
colibrii,

Попробуйте:
1) OPTIMIZE TABLE (возможно, уже этого окажется достаточно).
2) Создайте индекс отдельно по полю t. С целью минимизации его размера имеет смысл заменить тип поля на SMALLINT или даже TINYINT (предварительно нужно убедиться, что фактические величины поля t гарантированно укладываются в рамки этих типов).
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
06.06.2017, 00:02
    #39466283
colibrii
colibrii
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизатор не выбирает подходящий индекс
miksoftcolibrii,

Попробуйте:
1) OPTIMIZE TABLE (возможно, уже этого окажется достаточно).
2) Создайте индекс отдельно по полю t. С целью минимизации его размера имеет смысл заменить тип поля на SMALLINT или даже TINYINT (предварительно нужно убедиться, что фактические величины поля t гарантированно укладываются в рамки этих типов).

Дело в том, что у меня не стоит задачи победить именно этот случай, а нужно гарантировать отсутствие подобного в будущем.
Для этого нужно понять, как рассуждает оптимизатор, когда игнорирует мой совет про индекс.
Ну, я знаю, что если индекс подразумевает сканирование больше 5% таблицы, то он игнорируется, но тут, надеюсь, не этот случай. Хотя в %% получается как раз 5, да. Но я же велю ему именно использовать индекс. Да и для других условий получается до 10% надо сканировать (для 206, например, см. скриншот выше), и оптимизатору это пофиг.

Если создать таблицу заново и построить отдельно индекс по полю t, то всё норм, но мне это не подходит (не могу такое выдать заказчику, ибо уволят сразу).
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
06.06.2017, 05:34
    #39466322
javajdbc
javajdbc
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизатор не выбирает подходящий индекс
colibrii,

...вместо причитаний, больше пользы принесут результаты
тестов которые я и МикСофт вас попросили сделать...

...можете ли вы воспроизвести сбой на 202 начиная с
чистой таблицы и заливки данных?
Возможно значения NM как-то влияют, хотя, по идее, не должны.

...USE INDEX -- только совет. FORCE INDEX -- вот это приказ,
и то , где-то писали -- не 100% обязательный к выполнению.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
06.06.2017, 11:40
    #39466564
MasterZiv
MasterZiv
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизатор не выбирает подходящий индекс
Дело в том, что у меня не стоит задачи победить именно этот случай, а нужно гарантировать отсутствие подобного в будущем.


Этого невозможно сделать в принципе, никогда. Стоимостные оптимизаторы в принципе так устроены, что они порядка 20% запросов не могут оптимизировать принципиально, базируясь на тех принципах, на которых они устроены. Это для хороших оптимизаторов даже верно, а в MySQL оптимизатор мягко говоря не шикарный.


Для этого нужно понять, как рассуждает оптимизатор, когда игнорирует мой совет про индекс.

Когда игнорирует HINT, это либо баг оптимизатора/СУБД, либо индекс для данного запроса не может использоваться в принципе.



У тебя запрос со статистикой какой-то очень странный, результат которого ты дал. Там во-первых, для всех значений число записей одинаково, во-вторых, оно очень большое -- больше миллиона записей. При такой низкой селективности запросу использовать индекс в принципе не выгодно, и индекс не должен использоваться для этого значения.

Тебе какой дали запрос ?

Код: plaintext
SELECT t, HEX(t), COUNT(*) FROM kladr  GROUP BY t  ORDER BY COUNT(*) DESC LIMIT 12

А ты чё написал ?

Если создать таблицу заново и построить отдельно индекс по полю t, то всё норм, но мне это не подходит (не могу такое выдать заказчику, ибо уволят сразу).


Ты должен на таблицы строить такие индексы, которые нужны базе данных, а не заказчику. Это не дело заказчика, думать какие индексы делать, его дело --деньги платить.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
06.06.2017, 14:45
    #39466851
colibrii
colibrii
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизатор не выбирает подходящий индекс
javajdbccolibrii,

...вместо причитаний, больше пользы принесут результаты
тестов которые я и МикСофт вас попросили сделать...

...можете ли вы воспроизвести сбой на 202 начиная с
чистой таблицы и заливки данных?
Возможно значения NM как-то влияют, хотя, по идее, не должны.

...USE INDEX -- только совет. FORCE INDEX -- вот это приказ,
и то , где-то писали -- не 100% обязательный к выполнению.

Так я сделал всё, что советовали.
ANALYZE TABLE не помогает. Изменить тип данных мне не позволят, так же как и архитектуру.

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

Рано или поздно я найду в чем проблема, именно проверяя кучу гипотез, но хотелось бы раньше, чем позже. Потому я и задал вопрос, что может кто знает логику оптимизатора для этого достаточно примитивного случая.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
06.06.2017, 20:33
    #39467292
miksoft
miksoft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизатор не выбирает подходящий индекс
colibriiANALYZE TABLE не помогает.ANALYZE TABLE и OPTIMIZE TABLE - разные вещи. Хотя и второе включает в себя первое.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
06.06.2017, 20:39
    #39467297
miksoft
miksoft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизатор не выбирает подходящий индекс
colibriiПри том же объеме, но загруженные в другой последовательности, данные не дают такого косяка при выборе индекса.В качестве танцев с бубном - можно попробовать ALTER TABLE ... ORDER BY t и затем ANALYZE TABLE.
Возможно, это изменить статистику в нужную сторону.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.06.2017, 03:37
    #39467440
colibrii
colibrii
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизатор не выбирает подходящий индекс
miksoftcolibriiANALYZE TABLE не помогает.ANALYZE TABLE и OPTIMIZE TABLE - разные вещи. Хотя и второе включает в себя первое.

Опечатался. Я пробовал и OPTIMIZE, и REPAIR, и ANALYZE... Нет эффекта, потому что ошибок там нет.
Как ни странно, заработал FORCE INDEX. Я его пробовал на одном сервере только до этого, не работало. Теперь работает на всех. Остановлюсь на нем.
EXPLAIN теперь говорит, что оптимизатор прикидывает, что записей с кодом 202 - 10 млн штук (реально их 1 млн). Неудивительно, что индекс не хочет браться.

В общем, проблема решена, спасибо!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Оптимизатор не выбирает подходящий индекс / 19 сообщений из 19, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]