mysql - 为什么 MySQL 会发出文件排序？

Question

这是我的表格定义:

CREATE TABLE difficulty (
  uuid binary(16) NOT NULL,
  createdTimestamp datetime(6) DEFAULT NULL,
  modifiedTimestamp datetime(6) DEFAULT NULL,
  name varchar(255) DEFAULT NULL,
  PRIMARY KEY (uuid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE exercise_session (
  uuid binary(16) NOT NULL,
  createdTimestamp datetime(6) DEFAULT NULL,
  modifiedTimestamp datetime(6) DEFAULT NULL,
  type varchar(16) DEFAULT NULL,
  status int(11) DEFAULT NULL,
  difficulty_uuid binary(16) DEFAULT NULL,

  PRIMARY KEY (uuid),
  KEY (difficulty_uuid),
  KEY (difficulty_uuid,modifiedTimestamp),
  KEY (modifiedTimestamp),
  KEY (status),
  FOREIGN KEY (difficulty_uuid) REFERENCES difficulty (uuid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

这是我的查询:

SELECT s.difficulty_uuid, s.modifiedTimestamp, d.name
FROM exercise_session s
INNER JOIN difficulty d ON s.difficulty_uuid=d.uuid
ORDER BY s.modifiedTimestamp DESC
LIMIT 20

一些数据:

INSERT INTO difficulty (uuid, createdTimestamp, modifiedTimestamp, name) VALUES
    (0x00000000000000000000000000000000, NULL, NULL, 'difficulty');

INSERT INTO exercise_session (uuid, createdTimestamp, modifiedTimestamp, type, status, difficulty_uuid) VALUES
    (0x00000000000000000000000000000000, NULL, '2017-09-09 03:47:42.000000', '0', 0, 0x00000000000000000000000000000000),
    (0x00000000000000000000000000000001, NULL, '2017-09-09 03:47:42.000000', '0', 0, 0x00000000000000000000000000000000);

当我运行以 EXPLAIN 为前缀的查询时，这是输出:

+------+-------------+-------+------+-----------------+-----------------+---------+--------------+------+---------------------------------+
| id   | select_type | table | type | possible_keys   | key             | key_len | ref          | rows | Extra                           |
+------+-------------+-------+------+-----------------+-----------------+---------+--------------+------+---------------------------------+
|    1 | SIMPLE      | d     | ALL  | PRIMARY         | NULL            | NULL    | NULL         |    1 | Using temporary; Using filesort |
|    1 | SIMPLE      | s     | ref  | difficulty_uuid | difficulty_uuid | 17      | dbname.d.uuid |    1 |                                 |
+------+-------------+-------+------+-----------------+-----------------+---------+--------------+------+---------------------------------+

为什么 MySQL/MariaDB 使用文件排序而不是使用复合索引？

Answer 1

在得出有关优化程序对它们的处理的结论之前，您可能应该用更多的测试数据填充这些表。每个表中只有一两行可能会触发非典型的优化程序计划。

我试着用几十行填充表格，得到了这个解释:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: s
   partitions: NULL
         type: index
possible_keys: difficulty_uuid,difficulty_uuid_2
          key: difficulty_uuid_2
      key_len: 26
          ref: NULL
         rows: 11
     filtered: 100.00
        Extra: Using where; Using index; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: d
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 16
          ref: test.s.difficulty_uuid
         rows: 1
     filtered: 100.00
        Extra: NULL

仍然是文件排序，但至少它以正确的顺序访问表:首先是 s，然后在其主键上连接到 d，从而产生 eq_ref 类型访问。

使用表扫描成本太高而无法考虑的索引提示，它使用修改后的时间戳上的索引:

EXPLAIN SELECT s.difficulty_uuid, s.modifiedTimestamp, d.name 
FROM exercise_session s FORCE INDEX(modifiedTimestamp) 
JOIN difficulty d ON s.difficulty_uuid=d.uuid 
ORDER BY s.modifiedTimestamp DESC LIMIT 20

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: s
   partitions: NULL
         type: index
possible_keys: NULL
          key: modifiedTimestamp
      key_len: 9
          ref: NULL
         rows: 11
     filtered: 100.00
        Extra: Using where; Backward index scan
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: d
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 16
          ref: test.s.difficulty_uuid
         rows: 1
     filtered: 100.00
        Extra: NULL

不再有任何文件排序，但我们看到 Backward index scan 这是 MySQL 8.0 中的新功能(我正在使用预览版进行测试)。

介绍此功能的博客http://mysqlserverteam.com/mysql-8-0-labs-descending-indexes-in-mysql/提到 MySQL 5.7 可以向后扫描索引，但它比进行正向索引扫描的成本高出约 15%。 Jeremey Cole 在 InnoDB index internals 上做过博客和演讲，我记得他确切地说明了为什么降序索引扫描的成本更高，但我记不清楚了，无法在这里解释。