• MySQL 8.0新特性 — 降序索引

    小编:啊南 59阅读 2020.11.20

    前言

    相信大家都知道,索引是有序的;不过,在MySQL之前版本中,只支持升序索引,不支持降序索引,这会带来一些问题;在最新的MySQL 8.0版本中,终于引入了降序索引,接下来我们就来看一看。

    降序索引单列索引

    (1)查看测试表结构

    mysql> show create table sbtest1\G
    *************************** 1. row ***************************
           Table: sbtest1
    Create Table: CREATE TABLE `sbtest1` (
      `id` int unsigned NOT NULL AUTO_INCREMENT,
      `k` int unsigned NOT NULL DEFAULT '0',
      `c` char(120) NOT NULL DEFAULT '',
      `pad` char(60) NOT NULL DEFAULT '',
      PRIMARY KEY (`id`),
      KEY `k_1` (`k`)
    ) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci MAX_ROWS=1000000
    1 row in set (0.00 sec)

    (2)执行SQL语句order by ... limit n,默认是升序,可以使用到索引

    mysql> explain select * from sbtest1 order by k limit 10;
    +----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-------+
    | id | select_type | table   | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
    +----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-------+
    |  1 | SIMPLE      | sbtest1 | NULL       | index | NULL          | k_1  | 4       | NULL |   10 |   100.00 | NULL  |
    +----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-------+
    1 row in set, 1 warning (0.00 sec)

    (3)执行SQL语句order by ... desc limit n,如果是降序的话,无法使用索引,虽然可以相反顺序扫描,但性能会受到影响

    mysql> explain select * from sbtest1 order by k desc limit 10;
    +----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+---------------------+
    | id | select_type | table   | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra               |
    +----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+---------------------+
    |  1 | SIMPLE      | sbtest1 | NULL       | index | NULL          | k_1  | 4       | NULL |   10 |   100.00 | Backward index scan |
    +----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+---------------------+
    1 row in set, 1 warning (0.00 sec)

    (4)创建降序索引

    mysql> alter table sbtest1 add index k_2(k desc);
    Query OK, 0 rows affected (6.45 sec)
    Records: 0  Duplicates: 0  Warnings: 0

    (5)再次执行SQL语句order by ... desc limit n,可以使用到降序索引

    mysql> explain select * from sbtest1 order by k desc limit 10;
    +----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-------+
    | id | select_type | table   | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
    +----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-------+
    |  1 | SIMPLE      | sbtest1 | NULL       | index | NULL          | k_2  | 4       | NULL |   10 |   100.00 | NULL  |
    +----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-------+
    1 row in set, 1 warning (0.00 sec)
    多列索引

    (1)查看测试表结构

    mysql> show create table sbtest1\G
    *************************** 1. row ***************************
           Table: sbtest1
    Create Table: CREATE TABLE `sbtest1` (
      `id` int unsigned NOT NULL AUTO_INCREMENT,
      `k` int unsigned NOT NULL DEFAULT '0',
      `c` char(120) NOT NULL DEFAULT '',
      `pad` char(60) NOT NULL DEFAULT '',
      PRIMARY KEY (`id`),
      KEY `k_1` (`k`),
      KEY `idx_c_pad_1` (`c`,`pad`)
    ) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci MAX_ROWS=1000000
    1 row in set (0.00 sec)

    (2)对于多列索引来说,如果没有降序索引的话,那么只有SQL 1才能用到索引,SQL 4能用相反顺序扫描,其他两条SQL语句只能走全表扫描,效率非常低

    SQL 1:select * from sbtest1 order by c,pad limit 10;

    SQL 2:select * from sbtest1 order by c,pad desc limit 10;

    SQL 3:select * from sbtest1 order by c desc,pad limit 10;

    SQL 4:explain select * from sbtest1 order by c desc,pad desc limit 10;

    mysql> explain select * from sbtest1 order by c,pad limit 10;
    +----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
    | id | select_type | table   | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra |
    +----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
    |  1 | SIMPLE      | sbtest1 | NULL       | index | NULL          | idx_c_pad_1 | 720     | NULL |   10 |   100.00 | NULL  |
    +----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
    1 row in set, 1 warning (0.00 sec)
    
    mysql> explain select * from sbtest1 order by c,pad desc limit 10;
    +----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+
    | id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra          |
    +----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+
    |  1 | SIMPLE      | sbtest1 | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 950738 |   100.00 | Using filesort |
    +----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+
    1 row in set, 1 warning (0.00 sec)
    
    mysql> explain select * from sbtest1 order by c desc,pad limit 10;
    +----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+
    | id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra          |
    +----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+
    |  1 | SIMPLE      | sbtest1 | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 950738 |   100.00 | Using filesort |
    +----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+
    1 row in set, 1 warning (0.01 sec)
    
    mysql> explain select * from sbtest1 order by c desc,pad desc limit 10;
    +----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+---------------------+
    | id | select_type | table   | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra               |
    +----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+---------------------+
    |  1 | SIMPLE      | sbtest1 | NULL       | index | NULL          | idx_c_pad_1 | 720     | NULL |   10 |   100.00 | Backward index scan |
    +----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+---------------------+
    1 row in set, 1 warning (0.00 sec)

    (3)创建相应的降序索引

    mysql> alter table sbtest1 add index idx_c_pad_2(c,pad desc);
    Query OK, 0 rows affected (1 min 11.27 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    mysql> alter table sbtest1 add index idx_c_pad_3(c desc,pad);
    Query OK, 0 rows affected (1 min 14.22 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    mysql> alter table sbtest1 add index idx_c_pad_4(c desc,pad desc);
    Query OK, 0 rows affected (1 min 8.70 sec)
    Records: 0  Duplicates: 0  Warnings: 0

    (4)再次执行SQL,均能使用到降序索引,效率大大提升

    mysql> explain select * from sbtest1 order by c,pad desc limit 10;
    +----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
    | id | select_type | table   | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra |
    +----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
    |  1 | SIMPLE      | sbtest1 | NULL       | index | NULL          | idx_c_pad_2 | 720     | NULL |   10 |   100.00 | NULL  |
    +----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
    1 row in set, 1 warning (0.00 sec)
    
    mysql> explain select * from sbtest1 order by c desc,pad limit 10;
    +----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
    | id | select_type | table   | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra |
    +----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
    |  1 | SIMPLE      | sbtest1 | NULL       | index | NULL          | idx_c_pad_3 | 720     | NULL |   10 |   100.00 | NULL  |
    +----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
    1 row in set, 1 warning (0.00 sec)
    
    mysql> explain select * from sbtest1 order by c desc,pad desc limit 10;
    +----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
    | id | select_type | table   | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra |
    +----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
    |  1 | SIMPLE      | sbtest1 | NULL       | index | NULL          | idx_c_pad_4 | 720     | NULL |   10 |   100.00 | NULL  |
    +----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
    1 row in set, 1 warning (0.00 sec)
    总结

    MySQL 8.0引入的降序索引,最重要的作用是,解决了多列排序可能无法使用索引的问题,从而可以覆盖更多的应用场景。

    关联标签: