ReSQL.ru
     
powered by simpleCommunicator - 2.0.59     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Оптимизатор не выбирает подходящий индекс
19 сообщений из 19, страница 1 из 1
Оптимизатор не выбирает подходящий индекс
    #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, 14:42
| Ответить | Цитировать | Написать  
Оптимизатор не выбирает подходящий индекс
    #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
02.06.2017, 18:56
| Ответить | Цитировать | Написать  
Оптимизатор не выбирает подходящий индекс
    #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, 12:54
| Ответить | Цитировать | Написать  
Оптимизатор не выбирает подходящий индекс
    #39465138
colibrii
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
colibrii
Гость
Вот провел еще эксперимент.
Оптимизатор изучает статистику и принимает странное решение о неприменимости индекса.
...
Рейтинг: 0 / 0
03.06.2017, 13:07
| Ответить | Цитировать | Написать  
Оптимизатор не выбирает подходящий индекс
    #39465399
Фотография javajdbc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
javajdbc
Участник
colibrii,

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

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

И сколько всего записей в таблице?
...
Рейтинг: 0 / 0
04.06.2017, 21:16
| Ответить | Цитировать | Написать  
Оптимизатор не выбирает подходящий индекс
    #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:00
| Ответить | Цитировать | Написать  
Оптимизатор не выбирает подходящий индекс
    #39465631
colibrii
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
colibrii
Гость
miksoftcolibrii,

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

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

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

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

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

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

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

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

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

...USE INDEX -- только совет. FORCE INDEX -- вот это приказ,
и то , где-то писали -- не 100% обязательный к выполнению.
...
Рейтинг: 0 / 0
06.06.2017, 05:34
| Ответить | Цитировать | Написать  
Оптимизатор не выбирает подходящий индекс
    #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, 11:40
| Ответить | Цитировать | Написать  
Оптимизатор не выбирает подходящий индекс
    #39466851
colibrii
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
colibrii
Гость
javajdbccolibrii,

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

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

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

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

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

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

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

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


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