Как сделать индекс использования запроса JOIN?

Ответ 1

Если у вас много категорий, этот запрос не может быть эффективным. Ни один индекс не может охватывать сразу две таблицы в MySQL.

Вы должны выполнить денормализацию: добавьте last_updated, has_comments и deleted в article_categories:

CREATE TABLE `article_categories` (
  `article_id` int(11) NOT NULL DEFAULT '0',
  `category_id` int(11) NOT NULL DEFAULT '0',
  `last_updated` timestamp NOT NULL,
  `has_comments` boolean NOT NULL,
  `deleted` boolean NOT NULL,
  PRIMARY KEY (`article_id`,`category_id`),
  KEY `category_id` (`category_id`),
  KEY `ix_articlecategories_category_comments_deleted_updated` (category_id, has_comments, deleted, last_updated)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

и запустите этот запрос:

SELECT  *
FROM    (
        SELECT  article_id
        FROM    article_categories
        WHERE   (category_id, has_comments, deleted) = (78, 1, 0)
        ORDER BY
                last_updated DESC
        LIMIT   100, 20
        ) q
JOIN    articles a
ON      a.id = q.article_id

Конечно, вы должны обновлять article_categories, а также всякий раз, когда вы обновляете соответствующие столбцы в article. Это можно сделать в триггере.

Обратите внимание, что столбец has_comments является логическим: это позволит использовать предикат равенства для сканирования одного диапазона по индексу.

Также обратите внимание, что LIMIT переходит в подзапрос. Это делает MySQL использовать поиск в конце строки, который он не использует по умолчанию. Посмотрите эту статью в своем блоге о том, почему они повышают производительность:

• MySQL ORDER BY/LIMIT производительность: поиск в конце строки

Если вы были на SQL Server, вы можете сделать индексируемое представление по вашему запросу, что по существу сделало бы денормализованную индексированную копию article_categories с дополнительными полями, автоматически поддерживаемыми сервером.

К сожалению, MySQL не поддерживает это, и вам придется создать такую ​​таблицу вручную и написать дополнительный код, чтобы синхронизировать его с базовыми таблицами.

Ответ 2

Прежде чем перейти к конкретному запросу, важно понять, как работает индекс.

При соответствующей статистике этот запрос:

select * from foo where bar = 'bar'

... будет использовать индекс на foo(bar), если он будет избирательным. Это означает, что если bar = 'bar' составляет выбор большинства строк таблицы, это будет быстрее, чтобы просто прочитать таблицу и устранить строки, которые не применяются. Напротив, если bar = 'bar' означает только выбор нескольких строк, чтение индекса имеет смысл.

Предположим, что теперь мы бросаем в предложение order и у вас есть индексы на каждом из foo(bar) и foo(baz):

select * from foo where bar = 'bar' order by baz

Если bar = 'bar' является очень избирательным, он дешево захватывает все строки, которые соответствуют, и сортирует их в памяти. Если это вообще не выборочно, индекс на foo(baz) имеет мало смысла, потому что вы все равно получите всю таблицу: использование этого означало бы движение вперед и назад на дисковых страницах для чтения строк по порядку, что очень дорого.

Отбросить в ограничительном разделе, и foo(baz) может внезапно иметь смысл:

select * from foo where bar = 'bar' order by baz limit 10

Если bar = 'bar' является очень избирательным, он по-прежнему является хорошим вариантом. Если это вообще не выборочно, вы быстро найдете 10 подходящих строк, сканируя индекс на foo(baz) - вы можете прочитать 10 строк или 50, но вы скоро найдете 10 хороших.

Предположим, что последний запрос с индексами на foo(bar, baz) и foo(baz, bar). Индексы читаются слева направо. Один из них имеет очень хороший смысл для этого потенциального запроса, другой может вообще ничего не делать. Думайте о них так:

bar   baz    baz   bar
---------    ---------
bad   aaa    aaa   bad
bad   bbb    aaa   bar
bar   aaa    bbb   bad
bar   bbb    bbb   bar

Как вы можете видеть, индекс на foo(bar, baz) позволяет начать чтение с ('bar', 'aaa') и выборка строк по порядку с этой точки вперед.

Индекс на foo(baz, bar), напротив, дает строки, отсортированные по baz, независимо от того, что может быть bar. Если bar = 'bar' не является выборочным в качестве критерия, вы быстро столкнетесь с соответствующими строками для своего запроса, и в этом случае имеет смысл его использовать. Если он очень избирательный, вы можете закончить повторение строк строк до того, как найдете достаточно, чтобы соответствовать bar = 'bar' - он все равно может быть хорошим вариантом, но он оптимальным.

С учетом этого, вернемся к исходному запросу...

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

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

Я предполагаю, что в большинстве статей есть комментарий или более, так что это тоже не будет избирательным. То есть там также мало нужно индексировать его.

Без фильтра вашей категории параметры индекса достаточно очевидны: articles(last_updated); возможно, с столбцом счетчика справа, а удаленный флаг - влево.

С фильтром вашей категории все зависит от...

Если фильтр категории очень селективен, на самом деле имеет смысл выбрать все строки, находящиеся в этой категории, отсортировать их в памяти и выбрать верхние совпадающие строки.

Если ваш фильтр категории не является выборочным и дает почти статью, индекс в articles(last_update) имеет смысл: допустимые строки по всему месту, поэтому читайте строки по порядку, пока не найдете достаточно, чтобы соответствовать и voilà.

В более общем случае это просто смутно избирательно. Насколько мне известно, собранные статистические данные не слишком много смотрят на корреляции. Таким образом, у планировщика нет хорошего способа оценить, найдет ли он статьи с подходящей категорией достаточно быстро, чтобы прочитать последний индекс. Соединение и сортировка в памяти обычно будут дешевле, поэтому планировщик пойдет с этим.

Во всяком случае, у вас есть два варианта принудительного использования индекса.

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

http://dev.mysql.com/doc/refman/5.5/en/index-hints.html

Будьте осторожны, потому что иногда планировщик действительно прав, не желая использовать индекс, который вам нужен, или вице-версию. Кроме того, это может стать правильным в будущей версии MySQL, поэтому имейте это в виду, когда вы сохраняете свой код на протяжении многих лет.

Изменить: STRAIGHT_JOIN, как указано в работе DRap, с аналогичными предостережениями.

Другой заключается в том, чтобы сохранить дополнительный столбец для тегов часто выбранных статей (например, поле tinyint, которое установлено в 1, когда оно относится к вашей конкретной категории), а затем добавить индекс, например. articles(cat_78, last_updated). Поддерживайте его с помощью триггера, и все будет хорошо.

Ответ 3

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

KEY IX_Articles_last_updated (last_updated, id, title, comment_cnt, deleted),

Если это не помогает, вы можете играть с FORCE INDEX:

SELECT  a.*
FROM    article_categories AS c FORCE INDEX (IX_Articles_last_updated)
JOIN    articles AS a FORCE INDEX (PRIMARY)
ON      a.id = c.article_id
WHERE   c.category_id = 78
        AND a.comment_cnt > 0
        AND a.deleted = 0
ORDER BY 
        a.last_updated
LIMIT   100, 20

Имя индекса, использующего первичный ключ, всегда является "основным".

Ответ 4

Вы можете использовать влияние MySQL для использования KEYS или INDEXES

Для

• Заказ или
• Группирование, или
• Регистрация

За дополнительной информацией следуйте этой ссылке. Я хотел использовать это для соединения (т.е. USE INDEX FOR JOIN (My_Index), но это не сработало, как ожидалось. Удаление части FOR JOIN значительно ускорило мой запрос - от более чем 3,5 часов до 1-2 секунд. Просто потому, что MySQL был принудительно для использования правильного индекса.

Ответ 5

Я бы имел следующие индексы:

таблица статей - INDEX (удаленный, last_updated, comment_cnt)

article_categories таблица - INDEX (article_id, category_id) - у вас уже есть этот индекс

затем добавьте Straight_Join, чтобы заставить запрос, указанный в списке, вместо него пытаться использовать таблицу article_categories, используя любую статистику, которая может помочь в запросе.

SELECT STRAIGHT_JOIN
      a.*
   FROM
      articles AS a
         JOIN article_categories AS c
            ON a.id = c.article_id
            AND c.category_id = 78
   WHERE
          a.deleted = 0
      AND a.comment_cnt > 0
   ORDER BY 
      a.last_updated
   LIMIT 
      100, 20

В соответствии с комментарием/обратной связью, я бы рассмотрел реверсирование на основе набора, если записи категорий намного меньше оснований... таких как

SELECT STRAIGHT_JOIN
      a.*
   FROM
      article_categories AS c
         JOIN articles as a
            ON c.article_id = a.id
           AND a.deleted = 0
           AND a.Comment_cnt > 0
   WHERE
      c.category_id = 78
   ORDER BY 
      a.last_updated
   LIMIT 
      100, 20

В этом случае я бы обеспечил индекс в таблице статей

index - (id, deleted, last_updated)

Ответ 6

Прежде всего, я бы рекомендовал прочитать статью 3 способа использования индексов MySQL.

И теперь, когда вы знаете основы, вы можете оптимизировать этот конкретный запрос.

MySQL не может использовать индекс для упорядочения, он просто может выводить данные в порядке индекса. Поскольку MySQL использует вложенные петли для присоединения, поле, которое вы хотите заказать, должно быть в первой таблице в соединении (вы видите порядок объединения в результатах EXPLAIN и можете влиять на него, создавая определенные индексы и (если это не помогает), заставляя необходимые индексы).

Еще одна важная вещь: перед заказом вы получаете все столбцы для всех фильтрованных строк из таблицы a, а затем пропускаете, вероятно, большинство из них. Гораздо эффективнее получить список требуемых идентификаторов строк и извлечь только те строки.

Для выполнения этой работы вам понадобится индекс покрытия (deleted, comment_cnt, last_updated) в таблице a, и теперь вы можете переписать запрос следующим образом:

SELECT *
FROM (
  SELECT a.id
  FROM articles AS a,
  JOIN article_categories AS c
    ON a.id = c.article_id AND c.category_id = 78
  WHERE a.comment_cnt > 0 AND a.deleted = 0
  ORDER BY a.last_updated
  LIMIT 100, 20
) as ids
JOIN articles USING (id);

P.S. Определение таблицы для таблицы a не содержит столбца comment_cnt;)