Понимание mysql объясняет

Итак, я никогда не понимал объяснения MySQL. Я понимаю общие понятия, в которых вы должны иметь хотя бы одну запись в столбце possible_keys, чтобы использовать индекс, а простые запросы - лучше. Но в чем разница между ref и eq_ref? Каков наилучший способ оптимизации запросов.

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

+----+-------------+---------------------+--------+-----------------------------------------------------------+---------------------------------+---------+--------------------------------------+------+---------------------------------+
| id | select_type | table               | type   | possible_keys                                             | key                             | key_len | ref                                  | rows | Extra                           |
+----+-------------+---------------------+--------+-----------------------------------------------------------+---------------------------------+---------+--------------------------------------+------+---------------------------------+
|  1 | SIMPLE      | T6                  | ref    | yourock_achiever_achievement_id,yourock_achiever_alias_id | yourock_achiever_alias_id       | 4       | const                                |  244 | Using temporary; Using filesort |
|  1 | SIMPLE      | T5                  | eq_ref | PRIMARY                                                   | PRIMARY                         | 4       | paul.T6.achievement_id               |    1 | Using index                     |
|  1 | SIMPLE      | T4                  | ref    | yourock_achiever_achievement_id,yourock_achiever_alias_id | yourock_achiever_achievement_id | 4       | paul.T6.achievement_id               |  298 |                                 |
|  1 | SIMPLE      | yourock_alias       | eq_ref | PRIMARY                                                   | PRIMARY                         | 4       | paul.T4.alias_id                     |    1 | Using index                     |
|  1 | SIMPLE      | yourock_achiever    | ref    | yourock_achiever_achievement_id,yourock_achiever_alias_id | yourock_achiever_alias_id       | 4       | paul.T4.alias_id                     |  152 |                                 |
|  1 | SIMPLE      | yourock_achievement | eq_ref | PRIMARY                                                   | PRIMARY                         | 4       | paul.yourock_achiever.achievement_id |    1 |                                 |
+----+-------------+---------------------+--------+-----------------------------------------------------------+---------------------------------+---------+--------------------------------------+------+---------------------------------+
6 rows in set (0.00 sec)

Я надеялся узнать достаточно о том, что mysql объясняет, что запрос не понадобится. Увы, кажется, что вы не можете получить достаточную информацию из инструкции объяснения, и вам нужен необработанный SQL. Запрос:

SELECT  `yourock_achievement`.`id`,
        `yourock_achievement`.`modified`,
        `yourock_achievement`.`created`,
        `yourock_achievement`.`string_id`,
        `yourock_achievement`.`owner_id`,
        `yourock_achievement`.`name`,
        `yourock_achievement`.`description`,
        `yourock_achievement`.`owner_points`,
        `yourock_achievement`.`url`,
        `yourock_achievement`.`remote_image`,
        `yourock_achievement`.`image`,
        `yourock_achievement`.`parent_achievement_id`,
        `yourock_achievement`.`slug`,
        `yourock_achievement`.`true_points`
FROM    `yourock_achievement`
INNER JOIN
        `yourock_achiever`
ON       `yourock_achievement`.`id` = `yourock_achiever`.`achievement_id`
INNER JOIN
        `yourock_alias`
ON      `yourock_achiever`.`alias_id` = `yourock_alias`.`id`
INNER JOIN
        `yourock_achiever` T4
ON      `yourock_alias`.`id` = T4.`alias_id`
INNER JOIN
        `yourock_achievement` T5
ON      T4.`achievement_id` = T5.`id`
INNER JOIN
        `yourock_achiever` T6
ON      T5.`id` = T6.`achievement_id`
WHERE
        T6.`alias_id` = 6
ORDER BY
        `yourock_achievement`.`modified` DESC

Ответ 1

Пол:

eq_ref

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

eq_ref может использоваться для индексированных столбцов, которые сравниваются с помощью оператора =. Значение сравнения может быть константой или выражением, которое использует столбцы из таблиц, которые считываются перед этой таблицей. В следующих примерах MySQL может использовать объединение eq_ref для обработки ref_table:

SELECT * FROM ref_table,other_table
WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table
WHERE ref_table.key_column_part1=other_table.column
AND ref_table.key_column_part2=1;

исх

Все строки с соответствующими значениями индекса считываются из этой таблицы для каждой комбинации строк из предыдущих таблиц. ref используется, если соединение использует только самый левый префикс ключа или если ключ не является индексом PRIMARY KEY или UNIQUE (другими словами, если соединение не может выбрать одну строку на основе значения ключа). Если используемый ключ соответствует только нескольким строкам, это хороший тип соединения.

ref может использоваться для индексированных столбцов, которые сравниваются с помощью оператора = или < =. В следующих примерах MySQL может использовать объединение ref для обработки ref_table:

SELECT * FROM ref_table WHERE key_column=expr;

SELECT * FROM ref_table,other_table
WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table
WHERE ref_table.key_column_part1=other_table.column
AND ref_table.key_column_part2=1;

Они скопированы дословно из руководства MySQL: http://dev.mysql.com/doc/refman/5.0/en/using-explain.html

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

То, что сразу же появляется у меня в качестве возможного улучшения, - это "Использование временного файла с использованием filesort;". Это означает, что временная таблица была создана для удовлетворения запроса (не обязательно плохо) и что выбранный вами GROUP BY/ORDER BY не может быть извлечен из индекса, что приводит к файловому контуру.

Ответ 2

Кажется, что запрос обрабатывает записи (244 * 298 * 152) = 11,052,224, которые согласно Using temporary; Using filesort нужно сортировать.

Это может занять много времени.

Если вы разместите свой запрос здесь, мы, вероятно, сможем каким-то образом его оптимизировать.

Update:

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

Не удалось выполнить следующий запрос:

SELECT  COUNT(*)
FROM    `yourock_achievement`
INNER JOIN
        `yourock_achiever`
ON       `yourock_achievement`.`id` = `yourock_achiever`.`achievement_id`
INNER JOIN
        `yourock_alias`
ON      `yourock_achiever`.`alias_id` = `yourock_alias`.`id`
INNER JOIN
        `yourock_achiever` T4
ON      `yourock_alias`.`id` = T4.`alias_id`
INNER JOIN
        `yourock_achievement` T5
ON      T4.`achievement_id` = T5.`id`
INNER JOIN
        `yourock_achiever` T6
ON      T5.`id` = T6.`achievement_id`
WHERE
        T6.`alias_id` = 6