MySQL не использует индексы с предложением WHERE IN?

Ответ 1

См. Как MySQL использует индексы.

Также подтвердите, сохраняет ли MySQL выполнение полного сканирования таблицы после добавления в таблицу user_metrics дополнительных 2000-ти строк. В небольших таблицах доступ по индексу на самом деле более дорогой (I/O-мудрый), чем сканирование таблицы, и оптимизатор MySQL может принять это во внимание.

В отличие от моего предыдущего сообщения, оказывается, что MySQL также использует оптимизатор с затратами, что очень хорошая новость - это, если вы выполняете свой ANALYZE по крайней мере один раз, когда считаете, что объем данных в вашей базе данных является отражением будущего повседневного использования.

При работе с оптимизаторами на основе затрат (Oracle, Postgres и т.д.) вам необходимо периодически запускать ANALYZE на ваши различные таблицы, так как их размер увеличивается более чем на 10-15%. (Postgres сделает это автоматически для вас по умолчанию, тогда как другие РСУБД оставят эту ответственность перед администратором баз данных, то есть вы.) Через статистический анализ ANALYZE поможет оптимизатору лучше понять, сколько операций ввода-вывода (и другие связанные ресурсы, такие как ЦП, необходимые, например, для сортировки) будут задействованы при выборе между различными планами исполнения кандидатов. Невозможность запуска ANALYZE может привести к очень плохим, иногда катастрофическим решениям о планировании (например, миллисекунды-запросы, занимающие, иногда, часы из-за плохих вложенных циклов на JOIN s.)

Если после запуска ANALYZE производительность по-прежнему неудовлетворительна, вы, как правило, сможете обойти проблему, используя подсказки, например. FORCE INDEX, тогда как в других случаях вы могли бы наткнуться на ошибку MySQL (например, этот более старый, который мог укусить вас, если бы вы использовали Rails 'nested_set).

Теперь , поскольку вы находитесь в приложении Rails, это будет громоздким (и победить цель ActiveRecord), чтобы выдать свои пользовательские запросы с помощью подсказок, вместо того чтобы продолжать использовать ActiveRecord -генерированные.

Я упомянул, что в нашем Rails-приложении все запросы SELECT опустились ниже 100 мс после перехода на Postgres, тогда как некоторые из сложных объединений, сгенерированных ActiveRecord, из-за вложенного времени из-за вложенных в MySQL циклов с внутренним сканированием таблицы, даже если индексы были доступны. Оптимизатор не идеален, и вы должны знать о вариантах. Другие потенциальные проблемы с производительностью, о которых нужно знать, помимо оптимизации плана запроса, являются блокировкой. Однако это выходит за рамки вашей проблемы.

Ответ 2

Попробуйте включить этот индекс:

SELECT `user_metrics`.*
FROM `user_metrics` FORCE INDEX (index_user_metrics_on_user_id)
WHERE (`user_metrics`.user_id IN (N,N,N,N,N,N,N,N,N,N,N,N))

Я только что проверил, он использует индекс для точно такого же запроса:

EXPLAIN EXTENDED
SELECT * FROM tests WHERE (test IN ('test 1', 'test 2', 'test 3', 'test 4', 'test 5', 'test 6', 'test 7', 'test 8', 'test 9'))

1, 'SIMPLE', 'tests', 'range', 'ix_test', 'ix_test', '602', '', 9, 100.00, 'Using where'

Ответ 3

Иногда MySQL не использует индекс, даже если он доступен. Одно обстоятельство, при котором это происходит, - это когда оптимизатор оценивает, что использование индекса потребует от MySQL доступа к очень большому проценту строк в таблице. (В этом случае сканирование таблицы, вероятно, будет намного быстрее, потому что оно требует меньше запросов.)

Какой процент строк соответствует вашему предложению IN?

Ответ 4

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

В последнее время у меня такая же проблема. Затем я решаю использовать самообслуживание для решения моей проблемы. Проблема не в MySQL. Проблема в том, что мы. Тип возврата из подзапроса отличается от нашей таблицы. Поэтому мы должны указать тип подзапроса для типа столбца select. Ниже приведен пример кода:

select `user_metrics`.* 
from `user_metrics` um 
join (select `user_metrics`.`user_id` in (N, N, N, N) ) as temp 
on um.`user_id` = temp.`user_id`

Или мой собственный код:

Старый: (Не использовать индекс: ~ 4 с)

SELECT 
    `jxm_character`.*
FROM
    jxm_character
WHERE
    information_date IN (SELECT DISTINCT
            (information_date)
        FROM
            jxm_character
        WHERE
            information_date >= DATE_SUB('2016-12-2', INTERVAL 7 DAY))
        AND `jxm_character`.`ranking_type` = 1
        AND `jxm_character`.`character_id` = 3146089;

Новое: (Использовать индекс: ~ 0,02 с)

SELECT 
    *
FROM
    jxm_character jc
        JOIN
    (SELECT DISTINCT
        (information_date)
    FROM
        jxm_character
    WHERE
        information_date >= DATE_SUB('2016-12-2', INTERVAL 7 DAY)) AS temp 
        ON jc.information_date = STR_TO_DATE(temp.information_date, '%Y-%m-%d')
        AND jc.ranking_type = 1
        AND jc.character_id = 3146089;

jxm_character:

• Записи: ~ 3.5M
• PK: jxm_character (information_date, rank_type, character_id)

SHOW VARIABLES LIKE '%version%';

'protocol_version', '10'
'version', '5.1.69-log'
'version_comment', 'Source distribution'

Последнее примечание: убедитесь, что вы понимаете правило индекса MySQL слева.

P/s: Извините за мой плохой английский. Я отправляю свой код (производство, конечно), чтобы очистить свое решение: D.

Ответ 5

Будет ли лучше, если вы удалите избыточные скобки вокруг предложения where?

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