Как оптимизировать запросы MySQL на основе плана EXPLAIN

Рассматривая план запроса EXPLAIN, как определить, какие оптимизации лучше всего сделать?

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

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

id   select_type   table   type     possible_keys    key       key_len   ref                    rows   Extra
 1   SIMPLE        S       const    PRIMARY,l,p,f4   PRIMARY         2   const                     1   Using temporary
 1   SIMPLE        Q       ref      PRIMARY,S        S               2   const                   204   Using index
 1   SIMPLE        V       ref      PRIMARY,n,Q      Q               5   const,db.Q.QID            6   Using where; Using index; Distinct
 1   SIMPLE        R1      ref      PRIMARY,L        L             154   const,db.V.VID          447   Using index; Distinct
 1   SIMPLE        W       eq_ref   PRIMARY,w        PRIMARY         5   const,db.R.RID,const      1   Using where; Distinct
 1   SIMPLE        R2      eq_ref   PRIMARY,L        PRIMARY       156   const,db.W.RID,const      1   Using where; Distinct

Я правильно интерпретирую заключительную строку плана выполнения следующим образом:

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

Если это так, моя проблема заключается в фильтрации, которая происходит на этом заключительном этапе. Если условие не приводит к фильтрации (например, WHERE `Col_1_to_3` IN (1,2,3)), запрос выполняется очень быстро (~ 50 мс); однако, если условие ограничивает выбранные строки (WHERE `Col_1_to_3` IN (1,2)), запрос занимает значительно больше времени (~ 5 с). Если ограничение на одно совпадение (WHERE `Col_1_to_3` IN (1)), оптимизатор предлагает совсем другой план выполнения (который работает чуть лучше 5 с, но все же намного хуже 50 мс). Кажется, что нет лучшего индекса, который можно использовать в этой таблице (если он уже полностью использует первичный ключ для возврата одной строки на результат?).

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

В то время как я сопротивлялся, включая запрос и схему, полностью здесь (так как я действительно мог бы знать, что искать, а не просто сказать ответ), я понимаю, что необходимо заранее обсудить:

SELECT DISTINCT
    `Q`.`QID`
FROM
    `S`
    NATURAL JOIN `Q`
    NATURAL JOIN `V`
    NATURAL JOIN `R` AS `R1`
    NATURAL JOIN `W`

    JOIN `R` AS `R2` ON (
            `R2`.`SID` = `S`.`SID`
        AND `R2`.`RID` = `R1`.`RID`
        AND `R2`.`VID` = `S`.`V_id`
        AND `R2`.`Col_1_to_3` IN (1,2) -- this is where performance suffers!
    )

WHERE
    AND `S`.`SID` = @x
    AND `W`.`WID` = @y
;

Определение таблицы R:

CREATE TABLE `R` (
  `SID` smallint(6) unsigned NOT NULL,
  `RID` smallint(6) unsigned NOT NULL,
  `VID` varchar(50) NOT NULL DEFAULT '',
  `Col_1_to_3` smallint(1) DEFAULT NULL,
  `T` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`SID`,`RID`,`VID`),
  KEY `L` (`SID`,`VID`,`Col_1_to_3`),
  CONSTRAINT `R_f1` FOREIGN KEY (`SID`) REFERENCES `S` (`SID`),
  CONSTRAINT `R_f2` FOREIGN KEY (`SID`, `VID`) REFERENCES `V` (`SID`, `VID`),
  CONSTRAINT `R_f3` FOREIGN KEY (`SID`, `VID`, `Col_1_to_3`) REFERENCES `L` (`SID`, `VID`, `LID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Ответы

Ответ 1

Зависит от того, что вы собираетесь делать и что такое запрос.

Как правило, для каждой строки в EXPLAIN, которая имеет Using where, вам необходимо использовать ее с помощью столбца index (possible keys и keys). Это ваши фильтры и включают WHERE и ON. Сказав, что Using index еще лучше. Это означает, что существует индекс покрытия, и MySQL может извлекать данные прямо из индекса, а не посещать строку в данных таблицы.

Строки, в которых нет Using where, и он возвращает большое количество строк, следует посмотреть. Они возвращают значения для всех строк в таблице. Я не знаю, каков ваш запрос, поэтому я не знаю, беспокоиться ли здесь. Попробуйте фильтровать набор результатов, чтобы уменьшить размер и повысить производительность.

Обычно вам следует избегать просмотра Using filesort или Using temporary, хотя это плохо, если вы не ожидаете их.

Файлы Filesort обычно появляются с предложением ORDER. Обычно вы хотите, чтобы MySQL использовал индекс покрытия (Using index), чтобы строки возвращались уже по порядку с сервера. Если это не так, то MySQL должен их заказывать позже, используя filesort.

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