你好，问题太长标题放不下，请看内容区域。

Question

问题1：您好，我想问一下我这样理解对不对！这个表中有三个字段id(primary key),name(index),deptId(foreign key),索引在B+树上的存储内容除了索引本身，应该还有主键，所以下面select id就不会产生filesort，但是当我们想把所有字段都查出来的时，select 的执行顺序在order by之前，根据name排序但是没有deptId这个字段的信息，所以必须在select查出来的记录中再排序一次。

Answer 1

理解正确。
https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain-extra-information

Using filesort (JSON property: using_filesort)

MySQL must do an extra pass to find out how to retrieve the rows in sorted order. The sort is done by going through all rows according to the join type and storing the sort key and pointer to the row for all rows that match the WHERE clause. The keys then are sorted and the rows are retrieved in sorted order. See Section 8.2.1.14, “ORDER BY Optimization”.

Using index (JSON property: using_index)

The column information is retrieved from the table using only information in the index tree without having to do an additional seek to read the actual row. This strategy can be used when the query uses only columns that are part of a single index.

For InnoDB tables that have a user-defined clustered index, that index can be used even when Using index is absent from the Extra column. This is the case if type is index and key is PRIMARY.