mysql не использует индекс при простом условии ИЛИ

Я столкнулся с давней проблемой MySQL, отказывающейся использовать индекс для, казалось бы, простых вещей. Рассматриваемый запрос:

SELECT c.*
FROM app_comments c
LEFT JOIN app_comments reply_c ON c.reply_to = reply_c.id
WHERE (c.external_id = '840774' AND c.external_context = 'deals')
 OR (reply_c.external_id = '840774' AND reply_c.external_context = 'deals')
ORDER BY c.reply_to ASC, c.date ASC

ОБЪЯСНИТЕ:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  c   ALL external_context,external_id,idx_app_comments_externals NULL    NULL    NULL    903507  Using filesort
1   SIMPLE  reply_c eq_ref  PRIMARY PRIMARY 4   altero_full.c.reply_to  1   Using where

Есть указатели на external_id а также external_context отдельно, и я также попытался добавить составной индекс (idx_app_comments_externals), но это совершенно не помогло.

Запрос выполняется за 4-6 секунд в производственной среде (>1 млн записей), но удаление части OR из условия WHERE уменьшает это значение до 0,05 секунды (хотя он по-прежнему использует сортировку файлов). Ясно, что индексы здесь не работают, но я понятия не имею, почему. Кто-нибудь может это объяснить?

PS Мы используем MariaDB 10.3.18, может это здесь вина?

Источник

3 ответа

Решение

MySQL (и MariaDB) не могут оптимизировать ORусловия в разных столбцах или таблицах. Обратите внимание, что в контексте плана запросаc а также reply_cсчитаются разными таблицами. Эти запросы обычно оптимизируются "вручную" с помощью операторов UNION, которые часто содержат много дублирования кода. Но в вашем случае и с совсем недавней версией, которая поддерживает CTE (общие табличные выражения), вы можете избежать большинства из них:

WITH p AS (
    SELECT *
    FROM app_comments
    WHERE external_id      = '840774'
      AND external_context = 'deals'
)
SELECT * FROM p
UNION DISTINCT
SELECT c.* FROM p JOIN app_comments c ON c.reply_to = p.id
ORDER BY reply_to ASC, date ASC

Хорошими индексами для этого запроса были бы составные индексы по (external_id, external_context) (в любом порядке) и отдельный на (reply_to).

Вы не избежите "сортировки файлов", но это не должно быть проблемой, когда данные фильтруются до небольшого набора.

Источник

С предикатами равенства на external_id а также external_context столбцы в предложении WHERE, MySQL может эффективно использовать индекс... когда эти предикаты определяют подмножество строк, которые могут удовлетворить запрос.

Но с OR добавлен в WHERE предложение, теперь строки, которые должны быть возвращены из cкоторые не ограниченыexternal_id а также external_contentценности. Теперь возможно, что строки с другими значениями этих столбцов могут быть возвращены; строки с любыми значениями этих столбцов.

И это сводит на нет большое преимущество использования операции сканирования диапазона индексов... очень быстро, исключая возможность рассмотрения огромных массивов строк. Да, сканирование диапазона индекса используется для быстрого поиска строк. Это правда. Но суть дела в том, что операция сканирования диапазона использует индекс для быстрого обхода миллионов и миллионов строк, которые невозможно вернуть.

Это поведение не характерно для MariaDB 10.3. Мы собираемся наблюдать такое же поведение в MariaDB 10.2, MySQL 5.7, MySQL 5.6.

Я сомневаюсь в операции соединения: нужно ли возвращать несколько копий строк изc когда есть несколько совпадающих строк из reply_c? Или спецификация просто возвращает отдельные строки изc?

Мы можем рассматривать требуемый набор результатов как две части.

1) ряды из app_contents с предикатами равенства на external_id а также external_context

  SELECT c.*
    FROM app_comments c
   WHERE c.external_id       = '840774'
     AND c.external_context  = 'deals'
   ORDER
      BY c.external_id
       , c.external_context
       , c.reply_to
       , c.date

Для оптимальной производительности (без учета индекса покрытия из-за * в списке SELECT) подобный индекс можно использовать для удовлетворения как операции сканирования диапазона, так и порядка (исключая операцию использования файловой сортировки)

   ... ON app_comments (external_id, external_context, reply_to, date)

2) Вторая часть результата - это reply_to строки, связанные с совпадающими строками

  SELECT d.*
    FROM app_comments d
    JOIN app_comments e
      ON e.id = d.reply_to
   WHERE e.external_id       = '840774'
     AND e.external_context  = 'deals'
   ORDER
      BY d.reply_to
       , d.date

Тот же индекс, рекомендованный ранее, можно использовать для доступа к строкам в e(операция сканирования диапазона). В идеале этот индекс также должен включатьidстолбец. Наш лучший вариант, вероятно, - изменить индекс, чтобы включитьid столбец после date

   ... ON app_comments (external_id, external_context, reply_to, date, id)

Или, для эквивалентной производительности, за счет дополнительного индекса, мы могли бы определить такой индекс:

   ... ON app_comments (external_id, external_context, id)

Для доступа к строкам из d при сканировании диапазона нам, вероятно, понадобится индекс:

   ... ON app_comments (reply_to, date)

Мы можем объединить два набора с UNION ALLоператор набора; но есть вероятность, что одна и та же строка будет возвращена обоими запросами. АUNIONоператор заставит уникальную сортировку исключить повторяющиеся строки. Или мы могли бы добавить условие ко второму запросу, чтобы исключить строки, которые будут возвращены первым запросом.

  SELECT d.*
    FROM app_comments d
    JOIN app_comments e
      ON e.id = d.reply_to
   WHERE e.external_id       = '840774'
     AND e.external_context  = 'deals'
  HAVING NOT ( d.external_id      <=> '840774'
           AND d.external_context <=> 'deals'
             )
   ORDER
      BY d.reply_to
       , d.date

Объединив две части, оберните каждую часть в набор скобок, добавьте оператор набора UNION ALL и оператор ORDER BY в конце (вне скобок), примерно так:

(
  SELECT c.*
    FROM app_comments c
   WHERE c.external_id       = '840774'
     AND c.external_context  = 'deals'
   ORDER
      BY c.external_id
       , c.external_context
       , c.reply_to
       , c.date
)
UNION ALL
(
  SELECT d.*
    FROM app_comments d
    JOIN app_comments e
      ON e.id = d.reply_to
   WHERE e.external_id       = '840774'
     AND e.external_context  = 'deals'
  HAVING NOT ( d.external_id      <=> '840774'
           AND d.external_context <=> 'deals'
             )
   ORDER
      BY d.reply_to
       , d.date
)
ORDER BY `reply_to`, `date`

Для этого потребуется операция "Использование файловой сортировки" над объединенным набором, но теперь у нас есть действительно хороший шанс получить хороший план выполнения для каждой части.

У меня все еще есть вопрос о том, сколько строк мы должны вернуть, когда есть несколько совпадающих строк reply_to.

Источник

Однако индекс имени не используется для поиска в следующих запросах:

SELECT * FROM test
WHERE last_name='Jones' OR first_name='John';

введите описание ссылки здесь

Источник
Другие вопросы по тегам