MySQL 降序索引 (Descending Indexes)

MySQL 8.0 版本开始支持降序索引(Descending Indexes),在 8.0 之前的版本,使用关键字 DESC 定义降序索引时,实际上 DESC 是被忽略的,从 8.0 版本开始,DESC 定义的索引,真的就是降序的索引。

在 8.0 之前,能够以相反的顺序对索引进行扫描,但是会降低性能,降序索引能够实现相同的效果,且不会损耗性能。另外一个例子,当一个查询 SQL,需要按多个字段,以不同的顺序进行排序时,8.0 之前无法使用索引已排序的特性,因为 order by 的顺序与索引的顺序不一致,而使用降序索引,就能够指定联合索引中每一个字段的顺序,以适应 SQL 语句中的 order by 顺序,让 SQL 能够充分使用索引已排序的特性,提升 SQL 性能。

来看一个例子,表结构如下:

CREATE TABLE t (
  c1 INT, c2 INT,
  INDEX idx1 (c1 ASC, c2 ASC),
  INDEX idx2 (c1 ASC, c2 DESC),
  INDEX idx3 (c1 DESC, c2 ASC),
  INDEX idx4 (c1 DESC, c2 DESC)
);

表中有两个字段,c1 和 c2,根据 c1,c2 的不同顺序的组合,创建了 4 个索引,由于索引字段的排序不同,因此对于不同的 order by 顺序,优化器可以使用的索引也不相同,如果 order by 顺序与索引字段的顺序一致,那么就可以避免额外的 filesort ,从而提升性能。

ORDER BY c1 ASC, c2 ASC    -- 优化器选择使用 idx1
ORDER BY c1 DESC, c2 DESC  -- 优化器选择使用 idx4
ORDER BY c1 ASC, c2 DESC   -- 优化器选择使用 idx2
ORDER BY c1 DESC, c2 ASC   -- 优化器选择使用 idx3
添加降序索引语法:

除了在建表时创建降序索引以外,还可以使用改表添加索引的语法来创建降序索引。

  • alter table tb add index idx_1(name desc, address asc);
  • create index idx_2 on tb(name desc, address desc);
降序索引的限制条件:
  • 只有 InnoDB 存储引擎支持降序索引,其他引擎不支持。
  • 如果二级索引或者主键索引包含降序字段,那么 change buffer 将不支持这样的索引。
  • InnoDB 全文索引的 FTS_DOC_ID 字段不能被定义为降序索引。
  • 只要升序索引支持的数据类型,降顺索引都支持。
  • 降序索引支持普通的字段和虚拟字段。
  • 对于使用聚合函数 max()/min(),但是没有使用 group by 子句,不能使用降序索引进行优化。
  • 降序索引只支持 BTREE 索引,不支持 HASH 索引,同样也不支持全文索引和空间索引。

发表评论