CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT NOT NULL,
    email VARCHAR(100) NOT NULL
);
INSERT INTO users (name, age, email) VALUES
('Alice', 25, 'alice@example.com'),
('Bob', 30, 'bob@example.com'),
('Charlie', 22, 'charlie@example.com'),
('David', 28, 'david@example.com'),
('Eve', 35, 'eve@example.com');

CREATE INDEX idx_name_age ON users(name,age);


EXPLAIN SELECT NAME,age FROM users ORDER BY name, age;
EXPLAIN SELECT NAME,age FROM users ORDER BY NAME ASC,age ASC;

EXPLAIN SELECT NAME,age FROM users ORDER BY age,NAME;
EXPLAIN SELECT NAME,age FROM users ORDER BY name asc,age desc;
SHOW INDEX FROM users;

文件排序

在MySQL中，filesort 是一种用于对查询结果进行排序的机制。当MySQL无法使用索引直接完成排序操作时，就会使用 filesort 算法在内存或磁盘上对数据进行排序。

触发 filesort 的场景

  1. 没有合适的索引：如果查询中的 ORDER BY 子句涉及的列没有被索引覆盖，MySQL就无法利用索引的有序性来直接返回排序结果，从而触发 filesort。例如：
SELECT * FROM users ORDER BY age;

如果 age 列没有索引，MySQL就需要对 users 表中的所有记录进行读取，并在内存或磁盘上进行排序。

  1. 索引不满足最左前缀原则：对于复合索引，如果 ORDER BY 子句没有遵循索引的最左前缀原则，也会触发 filesort。例如，有一个复合索引 (col1, col2, col3)，以下查询会触发 filesort：
SELECT * FROM table_name ORDER BY col2;

因为没有从索引的最左边的列 col1 开始使用索引。

filesort 的执行方式

MySQL的 filesort 有两种主要的执行方式：

  1. 两次扫描算法：

    • 第一次扫描：MySQL会从存储引擎中读取满足查询条件的行数据，并将需要排序的列和行指针（用于获取整行数据）存储到一个临时数据结构中。
    • 排序：对这个临时数据结构按照 ORDER BY 列进行排序。
    • 第二次扫描：根据排序后的行指针，再次从存储引擎中读取整行数据并返回给客户端。
      这种方式需要两次访问数据，适用于排序数据量较大，无法一次性全部加载到内存中的情况。

  2. 一次扫描算法：

    • 扫描与排序：MySQL会从存储引擎中读取满足查询条件的行数据，将查询所需要的所有列（包括 ORDER BY 列）都存储到一个临时数据结构中，并在内存中对这个临时数据结构进行排序。
    • 返回结果：直接从排序后的临时数据结构中返回结果给客户端。
      这种方式只需要一次访问数据，适用于排序数据量较小，可以全部加载到内存中的情况。

优化 filesort

  1. 添加合适的索引：通过为 ORDER BY 子句涉及的列添加索引，使MySQL能够利用索引的有序性直接完成排序。例如，对于 SELECT * FROM users ORDER BY age，可以添加 CREATE INDEX idx_age ON users(age);。
  2. 避免不必要的排序：如果查询结果不需要严格的顺序，尽量不要使用 ORDER BY 子句，这样可以避免 filesort 操作。
  3. 减少排序数据量：通过 LIMIT 子句限制返回的行数，减少需要排序的数据量。例如：
SELECT * FROM users ORDER BY age LIMIT 10;

这样MySQL只需要对前10条排序后的数据进行处理，而不需要对整个表进行排序。

通过理解和优化 filesort，可以显著提高MySQL查询的性能。