Ответ 1

Взглянув на ваш запрос и объяснение, похоже, что n.status = 1 в предложении where делает поиск очень неэффективным, потому что вам нужно вернуть весь набор, определенный объединениями, а затем применить статус = 1. Попробуйте запустить соединение из таблицы term_node, которая будет немедленно отфильтрована WHERE, а затем сделайте соединения немедленно добавив условие состояния. Попробуйте, пожалуйста, скажите мне, как это происходит.

 SELECT n.nid, n.title,
           ma.field_article_date_format_value, 
           ma.field_article_summary_value 
      FROM term_node tn
INNER JOIN node n ON n.nid=tn.nid AND n.status=1
INNER JOIN content_type_article ma FORCE INDEX (ix_article_date) ON n.nid=ma.nid 
     WHERE tn.tid= 153 
  ORDER BY ma.field_article_date_format_value DESC 
     LIMIT 0, 11;

Ответ 2

Using temporary; Using filesort означает, что MySQL должен построить временную таблицу результатов и отсортировать ее, чтобы получить нужный результат. Это часто является следствием конструкции ORDER BY ... DESC LIMIT 0,n, которую вы используете для получения последних сообщений. Само по себе это не признак неудачи. Смотрите это: http://www.mysqlperformanceblog.com/2009/03/05/what-does-using-filesort-mean-in-mysql/

Вот некоторые вещи, которые нужно попробовать. Я не совсем уверен, что они будут работать; это трудно понять, не имея ваших данных для экспериментов.

Есть ли индекс BTREE на content_type_article.field_article_date_format_value? Если это так, это может помочь.

Вам нужно отображать 11 самых последних статей? Или вы можете отобразить 11 последних статей, появившихся за последнюю неделю или месяц? Если это так, вы можете добавить эту строку в предложение WHERE. Он будет фильтровать ваши данные по дате, а не искать все пути назад к началу времени для соответствия статей. Это будет особенно полезно, если у вас есть давний сайт Drupal.

   AND ma.field_article_date_format_value >= (CURRENT_TIME() - INTERVAL 1 MONTH)

Сначала попробуйте перевернуть порядок операций INNER JOIN. Во-вторых, включите tid = 153 в критерий объединения. Это МОЖЕТ уменьшить размер таблицы темп, который нужно сортировать. Все мои предложения заключаются в следующем:

    SELECT n.nid, 
           n.title, 
           ma.field_article_date_format_value, 
           ma.field_article_summary_value
      FROM node n 
INNER JOIN term_node tn            ON (n.nid=tn.nid AND tn.tid = 153) 
INNER JOIN content_type_article ma ON n.nid=ma.nid
     WHERE n.status=1 
       AND ma.field_article_date_format_value >= (CURRENT_TIME() - INTERVAL 1 MONTH)
  ORDER BY ma.field_article_date_format_value DESC 
     LIMIT 0, 11;

Это

Ответ 3

1) Индексы покрытия

Я думаю, что простой ответ может быть "охватывающим индексы".

Особенно в таблице content_type_article. "Индекс покрытия" имеет выражение в ORDER BY как ведущий столбец и включает все столбцы, на которые ссылается запрос. Здесь индекс, который я создал (на моей тестовой таблице):

CREATE INDEX ct_article_ix9 
    ON content_type_article 
       (field_article_date_format_value, nid, field_article_summary_value);

И вот выдержка из EXPLAIN, которую я получаю из запроса (после создания таблиц примеров с использованием движка InnoDB, включая индекс покрытия для каждой таблицы):

_type  table type  key              ref          Extra                     
------ ----- ----- --------------   -----------  ------------------------
SIMPLE  ma   index ct_article_ix9   NULL         Using index
SIMPLE  n    ref   node_ix9         ma.nid       Using where; Using index
SIMPLE  tn   ref   term_node_ix9    n.nid,const  Using where; Using index

Обратите внимание, что в плане нет 'Using filesort', а в плане отображается 'Using index' для каждой таблицы, на которую ссылается запрос, что в основном означает, что все данные, необходимые для запроса, извлекаются со страниц индекса, с нет необходимости ссылаться на любые страницы из базовой таблицы. (В ваших таблицах гораздо больше строк, чем в моих тестовых таблицах, но если вы можете получить план объяснения, который выглядит так, вы можете получить лучшую производительность.)

Для полноты здесь весь вывод EXPLAIN:

+----+-------------+-------+-------+---------------+----------------+---------+---------------------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key            | key_len | ref                 | rows | Extra                    |
+----+-------------+-------+-------+---------------+----------------+---------+-------- ------------+------+--------------------------+
|  1 | SIMPLE      | ma    | index | NULL          | ct_article_ix9 | 27      | NULL                |    1 | Using index              |
|  1 | SIMPLE      | n     | ref   | node_ix9      | node_ix9       | 10      | testps.ma.nid,const |   11 | Using where; Using index |
|  1 | SIMPLE      | tn    | ref   | term_node_ix9 | term_node_ix9  | 10      | testps.n.nid,const  |   11 | Using where; Using index |
+----+-------------+-------+-------+---------------+----------------+---------+---------------------+------+--------------------------+
3 rows in set (0.00 sec)

Я не внес никаких изменений в ваш запрос, кроме как опустить подсказку FORCE INDEX. Вот два других "охватывающих индекса", которые я создал в двух других таблицах, указанных в запросе:

CREATE INDEX node_ix9
    ON node (`nid`,`status`,`title`);

CREATE INDEX term_node_ix9
    ON term_node (nid,tid);

(Обратите внимание, что если nid является ключом кластеризации в таблице node, вам может не понадобиться индекс покрытия в таблице node.)

2) Использовать коррелированные подзапросы вместо объединений?

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

Обратите внимание, что этот запрос существенно отличается от исходного запроса. Разница в том, что с этим запросом строка из таблицы context_type_article будет возвращена только один раз. С помощью запроса с помощью соединений строка из этой таблицы может быть сопоставлена ​​с несколькими строками из таблиц node и term_node, которые будут возвращать эту же строку более одного раза. Это может рассматриваться как желательное или нежелательное, оно действительно зависит от мощности и соответствует ли набор результатов спецификации.

 SELECT ( SELECT n2.nid
            FROM node n2 
           WHERE n2.nid = ma.nid
             AND n2.status = 1
           LIMIT 1
        ) AS `nid`
      , ( SELECT n3.title 
            FROM node n3
           WHERE n3.nid = ma.nid
             AND n3.status = 1
           LIMIT 1
        ) AS `title`
      , ma.field_article_date_format_value
      , ma.field_article_summary_value
   FROM content_type_article ma
  WHERE EXISTS 
        ( SELECT 1
            FROM node n1
           WHERE n1.nid = ma.nid
             AND n1.status = 1
         )                 
     AND EXISTS
         ( SELECT 1
             FROM term_node tn
            WHERE tn.nid = ma.nid
             AND tn.tid = 153
         )
   ORDER BY ma.field_article_date_format_value DESC
   LIMIT 0,11

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

Здесь вывод объяснения для этого запроса:

+----+--------------------+-------+-------+---------------+----------------+---------+---------------------+------+--------------------------+
| id | select_type        | table | type  | possible_keys | key            | key_len | ref                 | rows | Extra                    |
+----+--------------------+-------+-------+---------------+----------------+---------+---------------------+------+--------------------------+
|  1 | PRIMARY            | ma    | index | NULL          | ct_article_ix9 | 27      | NULL                |   11 | Using where; Using index |
|  5 | DEPENDENT SUBQUERY | tn    | ref   | term_node_ix9 | term_node_ix9  | 10      | testps.ma.nid,const |   13 | Using where; Using index |
|  4 | DEPENDENT SUBQUERY | n1    | ref   | node_ix9      | node_ix9       | 10      | testps.ma.nid,const |   12 | Using where; Using index |
|  3 | DEPENDENT SUBQUERY | n3    | ref   | node_ix9      | node_ix9       | 10      | testps.ma.nid,const |   12 | Using where; Using index |
|  2 | DEPENDENT SUBQUERY | n2    | ref   | node_ix9      | node_ix9       | 10      | testps.ma.nid,const |   12 | Using where; Using index |
+----+--------------------+-------+-------+---------------+----------------+---------+---------------------+------+--------------------------+
5 rows in set (0.00 sec)

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

Примеры таблиц

Вот примеры таблиц (вместе с индексами), которые я построил и заполнил, на основе информации из вашего вопроса:

CREATE TABLE `node` (`id` INT PRIMARY KEY, `nid` INT, `title` VARCHAR(10),`status` INT);
CREATE INDEX node_ix9 ON node (`nid`,`status`,`title`);
INSERT INTO `node` VALUES (1,1,'foo',1),(2,2,'bar',0),(3,3,'fee',1),(4,4,'fi',0),(5,5,'fo',1),(6,6,'fum',0),(7,7,'derp',1);
INSERT INTO `node` SELECT id+7,nid+7,title,`status` FROM node;
INSERT INTO `node` SELECT id+14,nid+14,title,`status` FROM node;
INSERT INTO `node` SELECT id+28,nid+28,title,`status` FROM node;
INSERT INTO `node` SELECT id+56,nid+56,title,`status` FROM node;

CREATE TABLE content_type_article (id INT PRIMARY KEY, nid INT, field_article_date_format_value DATETIME, field_article_summary_value VARCHAR(10));
CREATE INDEX ct_article_ix9 ON content_type_article (field_article_date_format_value, nid, field_article_summary_value);
INSERT INTO content_type_article VALUES (1001,1,'2012-01-01','foo'),(1002,2,'2012-01-02','bar'),(1003,3,'2012-01-03','fee'),(1004,4,'2012-01-04','fi'),(1005,5,'2012-01-05','fo'),(1006,6,'2012-01-06','fum'),(1007,7,'2012-01-07','derp');
INSERT INTO content_type_article SELECT id+7,nid+7, DATE_ADD(field_article_date_format_value,INTERVAL 7 DAY),field_article_summary_value FROM content_type_article;
INSERT INTO content_type_article SELECT id+14,nid+14, DATE_ADD(field_article_date_format_value,INTERVAL 14 DAY),field_article_summary_value FROM content_type_article;
INSERT INTO content_type_article SELECT id+28,nid+28, DATE_ADD(field_article_date_format_value,INTERVAL 28 DAY),field_article_summary_value FROM content_type_article;
INSERT INTO content_type_article SELECT id+56,nid+56, DATE_ADD(field_article_date_format_value,INTERVAL 56 DAY),field_article_summary_value FROM content_type_article;

CREATE TABLE term_node (id INT, tid INT, nid INT);
CREATE INDEX term_node_ix9 ON term_node (nid,tid);
INSERT INTO term_node VALUES (2001,153,1),(2002,153,2),(2003,153,3),(2004,153,4),(2005,153,5),(2006,153,6),(2007,153,7);
INSERT INTO term_node SELECT id+7, tid, nid+7 FROM term_node;
INSERT INTO term_node SELECT id+14, tid, nid+14 FROM term_node;
INSERT INTO term_node SELECT id+28, tid, nid+28 FROM term_node;
INSERT INTO term_node SELECT id+56, tid, nid+56 FROM term_node;

Ответ 4

MySQL "оптимизирует" ваш запрос, чтобы он сначала выбирал из таблицы term_node, даже если вы сначала определяете выбор из node. Не зная данных, я не уверен, что является оптимальным способом. Таблица term_node, безусловно, связана с тем, что ваши проблемы с производительностью, так как там выбирается ~ 19 000 записей.

Пределы без ORDER BY почти всегда быстрее, потому что MySQL останавливается, как только находит указанный предел. С ORDER BY сначала нужно найти все записи и отсортировать их, а затем получить указанный предел.

Простая задача - переместить условие WHERE в предложение JOIN, где и должно быть. Этот фильтр специфичен для соединения таблицы. Это гарантирует, что MySQL не оптимизирует его неправильно.

INNER JOIN term_node tn ON n.nid=tn.nid AND tn.tid=153

Более сложная задача - сделать SELECT в таблице term_node и JOIN. Это называется DIVIVED TABLE, и вы увидите, что он определен как таковой в EXPLAIN. Поскольку вы сказали, что это много-ко многим, я добавил параметр DISTINCT, чтобы уменьшить количество записей для присоединения.

SELECT ...
FROM node n
INNER JOIN content_type_article ma FORCE INDEX (ix_article_date) ON n.nid=ma.nid
INNER JOIN (SELECT DISTINCT nid FROM term_node WHERE tid=153) tn ON n.nid=tn.nid
WHERE n.status=1
ORDER BY ma.field_article_date_format_value DESC 
LIMIT 0,11

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

Ответ 5

Вы действительно хотите избежать операции сортировки вообще, если можете, воспользовавшись предварительно отсортированным индексом.

Чтобы узнать, возможно ли это, представьте, что ваши данные денормализованы в одну таблицу и убедитесь, что все, что должно быть включено в ваше предложение WHERE, является специфицированным с ОДИНОЧНОЙ ЦЕННОСТЬю. например если вы должны использовать предложение IN в одном из столбцов, сортировка неизбежна.

Вот скриншот некоторых примерных данных:

Итак, если у вас есть данные, денормализованные, вы можете запрашивать tid и статус с помощью одиночных значений, а затем сортировать по дате по убыванию. Это будет означать, что следующий индекс в этом случае будет работать отлично:

create index ix1 on denormalisedtable(tid, status, date desc);

Если у вас есть это, ваш запрос попадет только в 10 лучших строк и не будет необходимости сортировать.

Итак - как вы получаете ту же производительность БЕЗ денормализации...

Я думаю, вы должны использовать предложение STRAIGHT_JOIN, чтобы заставить порядок, выбранный MySQL из таблиц, - вы хотите, чтобы он выбирал из таблицы, которую вы последние из SORTING.

Попробуйте следующее:

SELECT n.nid, 
        n.title, 
        ma.field_article_date_format_value, 
        ma.field_article_summary_value
FROM node n 
STRAIGHT_JOIN term_node tn            ON n.nid=tn.nid 
STRAIGHT_JOIN content_type_article ma ON n.nid=ma.nid
WHERE tn.tid= 153 
    AND n.status=1 
ORDER BY ma.field_article_date_format_value DESC 
LIMIT 0, 11;

Идея состоит в том, чтобы заставить MySQL выбирать из таблицы node, а затем из таблицы term_node и THEN FINALLY из таблицы content_type_article (таблица, содержащая колонку, которую вы сортируете).

Последнее соединение является вашим самым важным, и вы хотите, чтобы это произошло с использованием индекса, так что предложение LIMIT может работать без необходимости сортировки данных.

Этот единственный индекс MIGHT делает трюк:

create index ix1 on content_type_article(nid, field_article_date_format_value desc);

или

create index ix1 on content_type_article(nid, field_article_date_format_value desc, field_article_summary_value);

(для индекса покрытия)

Я говорю MIGHT, потому что я недостаточно знаю о оптимизаторе MySQL, чтобы знать, достаточно ли он достаточно умен, чтобы обрабатывать множественные значения столбцов "nid", которые будут загружаться в content_type_article без необходимости прибегать к данным.

Логически, он должен иметь возможность работать быстро - например, если в итоговую таблицу content_type_article вводится 5 значений nid, то она должна иметь возможность получить 10 лучших из каждого непосредственно из индекса и объединить результаты вместе, а затем выбрать финальную верхнюю 10, что означает в общей сложности 50 строк, считанных из этого таблицы, установленной в полной версии 19006, которую вы видите сейчас.

Сообщите мне, как это происходит.

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