MySQL: почему при использовании индексов все еще используется файловая сортировка?

Question

MySQL-5.6.24-win32.1432006610

У меня есть две таблицы для сообщений пользователей.

TMessageBody (id, body) хранит тело сообщений.

TMessage (id, uid, folderId, msgBodyId, subject) хранит сообщения пользователей в папках, таких как Входящие, Исходящие.

---

Создать таблицу SQL:

create table TMessageBody (
    id          int unsigned    not null    primary key auto_increment,
    body        text            not null
);


create table TMessage (
    id          int unsigned    not null    primary key auto_increment,
    uid         int unsigned    not null,
    folderId    int unsigned    not null,
    msgBodyId   int unsigned    not null,
    subject     varchar(256)    not null
);

---

Некоторые тестовые данные:

insert into TMessageBody 
    (body) values 
    ('Here is body 1')
    ,('Here is body 2')
    ,('Here is body 3')
    ,('Here is body 4')
    ,('Here is body 5')
    ,('Here is body 6')
    ,('Here is body 7')
    ,('Here is body 8')
    ,('Here is body 9')
    ;

insert into TMessage
    (uid, folderId, msgBodyId, subject) values
    (1, 999, 1, 'Hello jack')
    , (1, 999, 2, 'Jack, how are you')
    , (1, 888, 3, 'Good morning jack')
    , (2, 888, 4, 'I love you, rose')
    , (2, 999, 5, 'I love you, rose')
    , (3, 888, 6, 'Peter, please call back')
    , (3, 999, 7, 'What are you doing, Peter')
    , (3, 999, 8, 'Happy birthday, perter')
    , (4, 999, 9, 'Let me know if you are ready')
    ;

---

Индексы:

create index Idx_MsgBodyId      on TMessage(msgBodyId);
create index Idx_Uid_FolderId   on TMessage(uid, folderId);

---

1.FileSort показывает, когда folderId не в предложении WHERE

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

SET @uid=3;
SET @folderId=999;

EXPLAIN
SELECT *
FROM        TMessage
INNER JOIN  TMessageBody 
ON          TMessage.msgBodyId=TMessageBody.id
WHERE       TMessage.uid=@uid
                #AND TMessage.folderId=@folderId
ORDER BY    TMessage.id DESC
;

Результат EXPLAIN:

mysql> EXPLAIN
-> SELECT *
->  FROM            TMessage
->  INNER JOIN      TMessageBody
->  ON              TMessage.msgBodyId=TMessageBody.id
->  WHERE           TMessage.uid=@uid
->                      #AND TMessage.folderId=@folderId
->  ORDER BY        TMessage.id DESC
->  ;
+----+-------------+--------------+--------+--------------------------------+------------------+---------+-------------------------+------+-----------------------------+
| id | select_type | table        | type   | possible_keys                  | key              | key_len | ref                     | rows | Extra                       |
+----+-------------+--------------+--------+--------------------------------+------------------+---------+-------------------------+------+-----------------------------+
|  1 | SIMPLE      | TMessage     | ref    | Idx_MsgBodyId,Idx_Uid_FolderId | Idx_Uid_FolderId | 4       | const                   |    3 | Using where; Using filesort |
|  1 | SIMPLE      | TMessageBody | eq_ref | PRIMARY                        | PRIMARY          | 4       | test.TMessage.msgBodyId |    1 | NULL                        |
+----+-------------+--------------+--------+--------------------------------+------------------+---------+-------------------------+------+-----------------------------+
2 rows in set (0.00 sec)

2. FileSort исчезает, когда folderId в предложении WHERE

Запрос такой же, как и выше, за исключением предложения WHERE:

SET @uid=3;
SET @folderId=999;

EXPLAIN
SELECT *
FROM        TMessage
INNER JOIN  TMessageBody 
ON          TMessage.msgBodyId=TMessageBody.id
WHERE       TMessage.uid=@uid
            　　AND TMessage.folderId=@folderId
ORDER BY    TMessage.id DESC
;

Результат EXPLAIN:

mysql> EXPLAIN
-> SELECT *
->  FROM            TMessage
->  INNER JOIN      TMessageBody
->  ON              TMessage.msgBodyId=TMessageBody.id
->  WHERE           TMessage.uid=@uid
->                            AND TMessage.folderId=@folderId
->  ORDER BY        TMessage.id DESC
->  ;
+----+-------------+--------------+--------+--------------------------------+------------------+---------+-------------------------+------+-------------+
| id | select_type | table        | type   | possible_keys                  | key              | key_len | ref                     | rows | Extra       |
+----+-------------+--------------+--------+--------------------------------+------------------+---------+-------------------------+------+-------------+
|  1 | SIMPLE      | TMessage     | ref    | Idx_MsgBodyId,Idx_Uid_FolderId | Idx_Uid_FolderId | 8       | const,const             |    2 | Using where |
|  1 | SIMPLE      | TMessageBody | eq_ref | PRIMARY                        | PRIMARY          | 4       | test.TMessage.msgBodyId |    1 | NULL        |
+----+-------------+--------------+--------+--------------------------------+------------------+---------+-------------------------+------+-------------+
2 rows in set (0.00 sec)

---

Вопрос:

Разница между двумя запросами заключается в том, folderId столбец находится в WHERE пункт. Согласно результату EXPLAIN, оба запроса используют Idx_Uid_FolderId индекс. Я хочу знать, почему один показывает FileSort, а другой нет.

---

Обновить

Пытался использовать ORDER BY TMessage.folderId, TMessage.id DESC в первом запросе. Но Using filesort все еще существует в результате EXPLAIN.

mysql> EXPLAIN
-> SELECT *
->  FROM        TMessage
->  INNER JOIN  TMessageBody
->  ON          TMessage.msgBodyId=TMessageBody.id
->  WHERE       TMessage.uid=@uid
->                   #AND TMessage.folderId=@folderId
->  ORDER BY    TMessage.folderId, TMessage.id DESC
->  ;
+----+-------------+--------------+--------+--------------------------------+------------------+---------+-------------------------+------+-----------------------------+
| id | select_type | table        | type   | possible_keys                  | key              | key_len | ref                     | rows | Extra                       |
+----+-------------+--------------+--------+--------------------------------+------------------+---------+-------------------------+------+-----------------------------+
|  1 | SIMPLE      | TMessage     | ref    | Idx_MsgBodyId,Idx_Uid_FolderId | Idx_Uid_FolderId | 4       | const                   |    3 | Using where; Using filesort |
|  1 | SIMPLE      | TMessageBody | eq_ref | PRIMARY                        | PRIMARY          | 4       | test.TMessage.msgBodyId |    1 | NULL                        |
+----+-------------+--------------+--------+--------------------------------+------------------+---------+-------------------------+------+-----------------------------+
2 rows in set (0.06 sec)

Answer 1

Думайте об индексе как о сцепленном значении.

В этом случае ваш индекс

uid | folderId | id

id является последним, потому что это ссылка в вашем вторичном индексе на ваш первичный ключ.

В первом сценарии вы фильтруете по uid, а затем сортируете по id. Проблема в том, что MySQL не может предположить, что id упорядочен из-за того, что индекс на самом деле упорядочен по filterId до того, как будет отфильтрован по id.

например:

uid | folderId | id
  1 |        1 |  1
  1 |        2 |  2
  2 |        1 |  3

Поэтому этот индекс нельзя использовать для сортировки, поскольку сортировка индекса отличается от сортировки по предложению.

Теперь мой вопрос: почему вы пытаетесь избежать сортировки файлов? Если вы не сталкиваетесь с проблемами производительности, использование файловой сортировки прекрасно. Несмотря на название, сортировка выполняется в памяти, если не указано иное (с использованием временного).