Как оптимизировать запросы 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