Неселективный индекс Mariadb по внешним ключам
Я устраняю проблемы с очень медленным индексом на mariaDB. Это занимает более 10 секунд. В таблице более 10 миллионов строк. В предложении where указано «где a = x и b = y». Столбец b имеет выборочный индекс. Столбец a имеет неизбирательный индекс (всего 5 различных значений), но его нельзя удалить, поскольку a - внешний ключ. mariaDB использует пересечение двух индексов, что делает его работу намного хуже, чем использование индекса только для b. Я не знаю, как это решить, учитывая, что:
- Я не могу удалить индекс на a, так как хочу обеспечить целостность
- Я не хочу отменять оптимизацию индекса пересечения для всей базы данных
- Я не хочу использовать подсказки по индексу, поскольку это нестандартно (хотя я не уверен, какой еще вариант у меня есть).
Любые идеи?
Спасибо tadman, я добавляю несколько выводов ниже. Однако вопрос является общим: иностранный ключ автоматически добавляет индекс, этот индекс может быть неселективным (что часто имеет место для родительской таблицы), а затем оптимизатор использует пересечение, которое ухудшает производительность, если один из индексов является неизбирательным.
Стоит отредактировать вопрос и добавить этот контент с правильным форматированием. Это плохо отображается в комментариях.
Вы уже пробовали составные индексы для таблиц?
Ответы 1
a foreing key automatically adds an index...
Ну ... в MariaDB, да. Это ни в коем случае не стандартная функция; другие базы данных (Oracle, DB2, PostgreSQL и т. д.) не автоматически создают индексы для принудительного применения внешних ключей.
Я не могу полностью понять ваш вопрос, но мне кажется, что у вас есть два индекса: один использует столбец a, а другой - столбец b. И ... вы пытаетесь решить, какой использовать или как их комбинировать.
Сделать указанный запрос для использования индекса несложно. Если условием WHERE является where a=x and b=y, это идеальный случай, который можно сильно ускорить, создав индекс (b, a), как в:
create index ux_mytable_b_a on my_table (b, a);
Обратите внимание, что я использовал (b, a), а не (a, b). Таким образом, наиболее селективный столбец размещается первым, и у запроса меньше шансов на конфликт хэша и / или переполнение корзины.
Теперь по поводу вашего наблюдения:
mariaDB is using intersect of the 2 indices, which make it perform a lot worse...
Обратите внимание, что наличие комбинированного индекса в третьих (с двумя столбцами) позволяет избежать индекс пересечения. MariaDB автоматически выберет новый индекс, поскольку он быстрее, чем при использовании любой комбинации предыдущих.
Спасибо. Действительно, я добавил комбинированный индекс. Чтобы прояснить ситуацию: в mysql и mariadb внешние ключи автоматически создают индексы. такие индексы часто неселективны. однако нет способа удалить их (и сохранить внешний ключ) или отключить их как возможные ключи к оптимизатору. Это заставляет оптимизатор пересекаться с неизбирательными индексами, которые плохо работают по сравнению с использованием одного селективного индекса.
MariaDB не использует хеширование или ведра. (b,a) и (a,b) одинаково хороши, независимо от мощности физическое лицо.
@ItamarTayer - Если у вас уже есть INDEX(a,b) и добавлен FOREIGN KEY ... REFERENCES ... a ..., должен говорит, что составного индекса будет достаточно.
@RickJames Очко занято. MariaDB не использует хеш-таблицы. Тем не менее, b,a быстрее, чем a,b, поскольку b более селективен, чем a. Таким образом, фильтрация по значениям b сначала сильно сокращает строки-кандидаты по сравнению с фильтрацией сначала по a.
@TheImpaler - Но он не фильтрует то по одному, то по другому. Он фильтрует комбинацию с помощью детализации Один в BTree. Это эквивалентно объединению двух столбцов с получением единого индекса для этого объединенного столбца.
@TheImpaler - Если вы хотите обсудить этот вопрос, вот такой вопрос: stackoverflow.com/questions/12315496/…
@RickJames Вы правы. По какой-то причине я всегда склонен учитывать сканирование диапазона при запросе, но это был просто поиск равенства. Думаю, я склоняюсь к худшему сценарию.
Нам нужны такие вещи, как вывод
SHOW CREATE TABLEдля соответствующих таблиц. Может быть, ваш компьютер загорелся и на нем снижается скорость ЦП, или, может быть, он использует диски со скоростью вращения 5400 об / мин, мы понятия не имеем, что здесь может происходить. «Это медленно» - это наблюдение, а не диагностика, с которой мы можем работать.