文档结构  
翻译进度:已翻译     翻译赏金:0 元 (?)    ¥ 我要打赏

未来的MySQL 8.0(可能)会有一个伟大的新功能: 支持基于磁盘的索引排序顺序((即,索引可以在物理上按降序排序)。 在MySQL 8.0实验室发布的(新的优化器预览)中, 当你创建一个索引时,你可以指定为“升序”或“降序”, 它同样支持 ( B-Tree 索引).。尤其当你在执行 “SELECT … ORDER BY event_date DESC, name ASC LIMIT 10″ 这类查询语句时帮助很大(ORDER BY子句以升序和降序排序)。.

MySQL 5.6 和 5.7 索引顺序

事实上,这种语法 (CREATE INDEX … col_name … [ASC | DESC]) 已经支持了很长一段时间, 但它是被保留的,用于未来的功能扩展:在 MySQL 5.6和5.7中,如果你创建了一个索引并且指定使用“DESC”关键字,它将被忽略(索引总是以递增的顺序创建的)。

第 1 段(可获 1.84 积分)

同时,MySQL(所有版本)可以向后扫描索引,以下两个查询会用到索引:

CREATE TABLE `events` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `event_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `date_name` (`event_date`,`name`)
) ENGINE=InnoDB AUTO_INCREMENT=2490312 DEFAULT CHARSET=latin1
mysql> explain select * from events order by event_date, name limit 10G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: events
   partitions: NULL
         type: index
possible_keys: NULL
          key: date_name
      key_len: 109
          ref: NULL
         rows: 10
     filtered: 100.00
        Extra: Using index
mysql> explain select * from events order by event_date desc, name desc limit 10G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: events
   partitions: NULL
         type: index
possible_keys: NULL
          key: date_name
      key_len: 109
          ref: NULL
         rows: 10
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)
第 2 段(可获 0.24 积分)

在第二个查询中, MySQL后向扫描两个字段的索引。.

 “LIMIT 10”在这里也很重要。 MySQL扫描顺序索引表 (并避免文件排序),在发现10行数据后终止扫描. 这使得查询几乎是瞬间完成的:

mysql> select * from events order by event_date desc, name desc limit 10;
+--------+-------+---------------------+
| id     | name  | event_date          |
+--------+-------+---------------------+
|      8 | test1 | 2016-10-09 10:01:06 |
|      7 | test1 | 2016-10-09 10:01:06 |
| 262125 | new2  | 2016-10-09 10:01:06 |
| 262124 | new2  | 2016-10-09 10:01:06 |
| 262123 | new2  | 2016-10-09 10:01:06 |
| 262122 | new2  | 2016-10-09 10:01:06 |
| 131053 | new1  | 2016-10-09 10:01:06 |
| 131052 | new1  | 2016-10-09 10:01:06 |
|      6 | test1 | 2016-10-09 10:01:05 |
|      5 | test1 | 2016-10-09 10:01:05 |
+--------+-------+---------------------+
10 rows in set (0.00 sec)
第 3 段(可获 0.61 积分)

但以不同的顺序呢:DESC 和 ASC ((在这个例子中比较有意义,比如我们想首先显示最新的事件,但也使用二级排序,按事件名称的字母排序). 这个查询使用了文件排序而且执行的更慢:

mysql> explain select * from events order by event_date desc, name asc limit 10G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: events
   partitions: NULL
         type: index
possible_keys: NULL
          key: date_name
      key_len: 109
          ref: NULL
         rows: 2017864
     filtered: 100.00
        Extra: Using index; Using filesort
1 row in set, 1 warning (0.00 sec)
mysql> select * from events order by event_date desc, name asc limit 10;
+--------+-------+---------------------+
| id     | name  | event_date          |
+--------+-------+---------------------+
| 131053 | new1  | 2016-10-09 10:01:06 |
| 131052 | new1  | 2016-10-09 10:01:06 |
| 262123 | new2  | 2016-10-09 10:01:06 |
| 262122 | new2  | 2016-10-09 10:01:06 |
| 262124 | new2  | 2016-10-09 10:01:06 |
| 262125 | new2  | 2016-10-09 10:01:06 |
|      7 | test1 | 2016-10-09 10:01:06 |
|      8 | test1 | 2016-10-09 10:01:06 |
| 131055 | new1  | 2016-10-09 10:01:05 |
| 131054 | new1  | 2016-10-09 10:01:05 |
+--------+-------+---------------------+
10 rows in set (2.41 sec)
第 4 段(可获 0.54 积分)

MySQL 8.0(实验室发布)

MySQL Server 8.0.0 Optimizer labs release包含了对于索引排序顺序的新的支持 (仅限InnoDB引擎).。下面是我们从上面查询的执行过程:

Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 5
Server version: 8.0.0-labs-opt MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql>  alter table events add key date_desc_name_asc(event_date desc, name asc);
Query OK, 0 rows affected (8.47 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show create table eventsG
*************************** 1. row ***************************
       Table: events
Create Table: CREATE TABLE `events` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `event_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `date_desc_name_asc` (`event_date` DESC,`name`)
) ENGINE=InnoDB AUTO_INCREMENT=2490312 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
第 5 段(可获 0.43 积分)

我已经创建了一个索引,针对我们特定的查询顺序: event_date descending降序,name 升序。现在查询的速度快的多:

mysql> explain select * from events order by event_date desc, name asc limit 10G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: events
   partitions: NULL
         type: index
possible_keys: NULL
          key: date_desc_name_asc
      key_len: 109
          ref: NULL
         rows: 10
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)
mysql> select * from events order by event_date desc, name asc limit 10;
+--------+-------+---------------------+
| id     | name  | event_date          |
+--------+-------+---------------------+
| 131052 | new1  | 2016-10-09 10:01:06 |
| 131053 | new1  | 2016-10-09 10:01:06 |
| 262122 | new2  | 2016-10-09 10:01:06 |
| 262123 | new2  | 2016-10-09 10:01:06 |
| 262124 | new2  | 2016-10-09 10:01:06 |
| 262125 | new2  | 2016-10-09 10:01:06 |
|      7 | test1 | 2016-10-09 10:01:06 |
|      8 | test1 | 2016-10-09 10:01:06 |
| 131054 | new1  | 2016-10-09 10:01:05 |
| 131055 | new1  | 2016-10-09 10:01:05 |
+--------+-------+---------------------+
10 rows in set (0.00 sec)
第 6 段(可获 0.28 积分)

该索引 (event_date desc, name asc) 满足两个条件:

  • Order by event_date desc, name asc: 正向索引扫描
  • Order by event_date asc, name desc: 反向索引扫描
mysql> explain select * from events order by event_date asc, name desc limit 10G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: events
   partitions: NULL
         type: index
possible_keys: NULL
          key: date_desc_name_asc
      key_len: 109
          ref: NULL
         rows: 10
     filtered: 100.00
        Extra: Using index; Backward index scan
1 row in set, 1 warning (0.00 sec)
第 7 段(可获 0.41 积分)

请注意上表额外列的反向索引的查询情况

这种情况是和以event_date,name为升序排序的索引情况相类似的,这可以优化无论是以event_data升序、name升序还是event_date降序,name降序(即两个字段都是相同的排序方式)的排序方式。

原始查询用了2.41秒运行的时间(并执行了一个文件过滤操作),现在使用新的索引的情况的查询时间大为缩减:

mysql> select * from events order by event_date desc, name asc limit 10;
+--------+-------+---------------------+
| id     | name  | event_date          |
+--------+-------+---------------------+
| 131052 | new1  | 2016-10-09 10:01:06 |
| 131053 | new1  | 2016-10-09 10:01:06 |
| 262122 | new2  | 2016-10-09 10:01:06 |
| 262123 | new2  | 2016-10-09 10:01:06 |
| 262124 | new2  | 2016-10-09 10:01:06 |
| 262125 | new2  | 2016-10-09 10:01:06 |
|      7 | test1 | 2016-10-09 10:01:06 |
|      8 | test1 | 2016-10-09 10:01:06 |
| 131054 | new1  | 2016-10-09 10:01:05 |
| 131055 | new1  | 2016-10-09 10:01:05 |
+--------+-------+---------------------+
10 rows in set (0.00 sec)
第 8 段(可获 0.93 积分)

请注意

降序索引只适用于InnoDB引擎:
mysql> create table events_myisam like events;
Query OK, 0 rows affected (0.02 sec)
mysql> alter table events_myisam engine=MyISAM;
ERROR 1178 (42000): The storage engine for the table doesn't support descending indexes

MySQL 5.7的解决方法

MySQL 5.7有一个(相当有限的)解决方法,就是通过创建(和索引)一个虚拟字段来实现这样的效果。在这里,索引的字段不能是一个字符串类型(varchar),我们需要通过无符号整数(即“id”,这是另一个表中的auto_increment字段)类型进行排序。  假设我们的查询将如下所示:“select * from events order by event_date desc,profile_id asc limit 10”。 在这种情况下,我们可以通过使profile_id为负并将其存储在“虚拟”(也称为“生成的”)列中来进行“反序”查询。
第 9 段(可获 1.28 积分)
mysql> alter table events_virt add  profile_id_negative int GENERATED ALWAYS AS ( -profile_id);                                                                                              |
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

那么我们可以将date字段一起加到索引中:

mysql> alter table events_virt add key event_date_profile_id_negative(event_date, profile_id_negative);
Query OK, 0 rows affected (7.21 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show create table events_virtG
*************************** 1. row ***************************
       Table: events_virt
Create Table: CREATE TABLE `events_virt` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `event_date` datetime DEFAULT NULL,
  `profile_id` int(11) DEFAULT NULL,
  `profile_id_negative` int(11) GENERATED ALWAYS AS (-(`profile_id`)) VIRTUAL,
  PRIMARY KEY (`id`),
  KEY `event_date_profile_id_negative` (`event_date`,`profile_id_negative`)
) ENGINE=InnoDB AUTO_INCREMENT=2424793 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
第 10 段(可获 0.13 积分)

现在我们可以使用profile_id_negative索引做降序排序:

mysql> explain select * from events_virt order by event_date desc, profile_id_negative desc limit 10G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: events_virt
   partitions: NULL
         type: index
possible_keys: NULL
          key: event_date_profile_id_negative
      key_len: 11
          ref: NULL
         rows: 10
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)
mysql> select * from events_virt order by event_date desc, profile_id_negative desc limit 10;
+--------+-------+---------------------+------------+---------------------+
| id     | name  | event_date          | profile_id | profile_id_negative |
+--------+-------+---------------------+------------+---------------------+
|      7 | test1 | 2016-10-09 10:01:06 |         24 |                 -24 |
|      8 | test1 | 2016-10-09 10:01:06 |         80 |                 -80 |
| 131052 | new1  | 2016-10-09 10:01:06 |     131063 |             -131063 |
| 131053 | new1  | 2016-10-09 10:01:06 |     131117 |             -131117 |
| 262125 | new2  | 2016-10-09 10:01:06 |     262130 |             -262130 |
| 262123 | new2  | 2016-10-09 10:01:06 |     262169 |             -262169 |
| 262124 | new2  | 2016-10-09 10:01:06 |     262193 |             -262193 |
| 262122 | new2  | 2016-10-09 10:01:06 |     262210 |             -262210 |
|      5 | test1 | 2016-10-09 10:01:05 |         80 |                 -80 |
|      6 | test1 | 2016-10-09 10:01:05 |        101 |                -101 |
+--------+-------+---------------------+------------+---------------------+
10 rows in set (0.00 sec)
第 11 段(可获 0.2 积分)

查询速度会快很多,但和下面查询的返回结果是一样的:

mysql> select * from events_virt order by event_date desc, profile_id asc limit 10;
+--------+-------+---------------------+------------+---------------------+
| id     | name  | event_date          | profile_id | profile_id_negative |
+--------+-------+---------------------+------------+---------------------+
|      7 | test1 | 2016-10-09 10:01:06 |         24 |                 -24 |
|      8 | test1 | 2016-10-09 10:01:06 |         80 |                 -80 |
| 131052 | new1  | 2016-10-09 10:01:06 |     131063 |             -131063 |
| 131053 | new1  | 2016-10-09 10:01:06 |     131117 |             -131117 |
| 262125 | new2  | 2016-10-09 10:01:06 |     262130 |             -262130 |
| 262123 | new2  | 2016-10-09 10:01:06 |     262169 |             -262169 |
| 262124 | new2  | 2016-10-09 10:01:06 |     262193 |             -262193 |
| 262122 | new2  | 2016-10-09 10:01:06 |     262210 |             -262210 |
|      5 | test1 | 2016-10-09 10:01:05 |         80 |                 -80 |
|      6 | test1 | 2016-10-09 10:01:05 |        101 |                -101 |
+--------+-------+---------------------+------------+---------------------+
10 rows in set (2.52 sec)
第 12 段(可获 0.16 积分)

结论

在MySQL 8.0(实验版)预览版本之中嵌入了这个强大的新的索引排序顺序功能,可以显著地提高频繁慢查询的性能,例如:order by field1 desc,field2 asc limit N。

此功能可以在其他数据库中找到(例如,在MongoDB中)。如果可以将这个功能将在集成到到MySQL 5.7,那我们可以在5.7这个版本中使用它。

在这里,我要感谢Oracle的 Mysql团队 开发出这个强大的功能,如果您对其他MySQL优化器功能感兴趣,请参考 Manyi Lu’s presentation at Percona Live Amsterdam 中的演示文稿,其中他谈到了MySQL 8.0的其他强大功能:例如直方图,不可见索引,通用表表达式和扩展JSON支持。

第 13 段(可获 1.6 积分)

文章评论