Mysql Объясните запрос с типом "ALL", когда используется индекс

Я выполнил запрос в Mysql, как показано ниже:

EXPLAIN
SELECT *
FROM(
        SELECT *  # Select Number 2
        FROM post
        WHERE   parentid = 13
        ORDER BY time, id
        LIMIT 1, 10
    ) post13_childs
JOIN post post13_childs_childs
ON post13_childs_childs.parentid = post13_childs.id

и результат:

id |select_type  |table               |type |possible_keys  |key      |key_len  |ref              |rows    |Extra
1  |PRIMARY      |<derived2>          |ALL  | NULL          | NULL    |NULL     |NULL             |10      |
1  |PRIMARY      |post13_childs_childs|ref  |parentid       |parentid |9        |post13_childs.id |10      |Using where
2  |DERIVED      |post                |ALL  |parentid       |parentid |9        |                 |153153  |Using where; Using filesort

Это означает, что он использовал индекс parentid, но отсканировал все строки из-за ALL и 153153. Почему индекс не мог помочь Full Scannig?

Хотя, если я запускаю производный запрос (выберите # 2) один, как показано ниже:

Explain
SELECT * FROM post  
WHERE parentid=13
ORDER BY time , id
LIMIT 1,10

желаемый результат:

id |select_type  |table  |type |possible_keys  |key      |key_len  |ref  |rows    |Extra
1  |SIMPLE       |post   |ref  |parentid       |parentid |9        |const|41      |Using where; Using filesort

Edit:

В таблице post указаны следующие индексы:

id (PRIMARY)
ParentId
время, id (timeid)

кол-во итоговых строк → 141280.
количество детей 13 (parentid=13) → 41
количество детей 11523 → 10119

Когда я добавляю индекс (parent,time,id), проблема первого запроса будет решена с помощью вывода explin для 13 → 40 строк, введите: ref
и для 11523 → 19538 строк, тип: ref!!! это означает, что все дочерние строки 11423 проверяются, когда я ограничил первые 10 строк.

Ответ 1

Ваш подзапрос:

    SELECT *  # Select Number 2
    FROM post
    WHERE   parentid = 13
    ORDER BY time, id
    LIMIT 1, 10;

Это упоминает три столбца явно, плюс все остальные столбцы. У вас есть три индекса. Вот как они могут быть использованы:

id (PRIMARY) - Этот индекс бесполезен. Хотя упоминается в предложении order by, это второе условие
parentid - этот индекс может использоваться для выполнения предложения where. Однако после того, как правильные данные будут вытащены, необходимо будет отсортировать их явно.
time, id (timeid) - этот индекс может использоваться для сортировки с большим НО. MySQL может сканировать индекс, чтобы получить все в правильном порядке. Но ему придется проверять, по очереди, выполняется ли условие на parentid.

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

Правильный индекс для этого подзапроса - это тот, который удовлетворяет предложению where и позволяет упорядочивать. Этот индекс parentid, time, id. Это не индекс покрытия (если только это не все столбцы в таблице). Но это должно уменьшить количество обращений к фактическим строкам до 10 из-за предложения limit.

Обратите внимание, что для полного запроса вам нужен индекс в parentid. И, к счастью, индекс на parentid, time, id считается таким индексом. Таким образом, вы можете удалить этот индекс. Индекс time, id, вероятно, не нужен, если вам это не требуется для других запросов.

Ваш запрос также фильтрует только те "дети", у которых есть "дети". Вполне возможно, что никакие строки не будут возвращены. Вы действительно намереваетесь left outer join?

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

Ответ 2

Выполнение ORDER BY, которому не помогает какой-либо индекс, может регулярно убивать производительность. Для внутреннего запроса у меня был бы индекс покрытия (parentID, time, id), чтобы оба предложения WHERE и ORDER BY могли использовать индекс. Поскольку parentID также является основой для повторных подключений, должно быть хорошо идти туда и быть довольно быстрым.