暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / MySQL 降序索引简介？

MySQL 降序索引简介？

数据库干货铺 2022-12-15
505
点击上方蓝字关注我

使用过Oracle、SQLServer数据库的降序索引的同学，可能在使用MySQL8.0之前版本时有个疑惑，明明我已经创建了将需要索引，但是为何执行时走不了索引或者效果不理想？

1. 创建环境

分别在MySQL5.7 及MySQL8.0版本中创建如下表及数据

    # 创建表
     create table   test1(
     id int primary key auto_increment,
     name  varchar(100),
     create_time datetime
     );


    # 插入部分测试数据，有条件的创建更多数据更佳
     insert into  test1(name,creatE_time) values('anniuadaOAIFAPUHIA','2020-07-01 12:00:00');
     insert into  test1(name,creatE_time) values('CWQSsar3qcssg','2020-07-01 15:00:00');
     insert into  test1(name,creatE_time) values('vxfqrt2adafz','2020-07-01 21:30:00');
     insert into  test1(name,creatE_time) values('etxzwrwbdhegqgaheqhag','2020-07-02 01:30:00');
     insert into  test1(name,creatE_time) values('awrs433fsgvsfwtwg','2020-07-02 03:30:00');
     insert into  test1(name,creatE_time) values('awrs433fsgvsfwtwg','2020-07-02 07:32:00');
     insert into  test1(name,creatE_time) values('awrs433fsgvsfwtwg','2020-07-02 10:32:00');
     insert into  test1(name,creatE_time) values('tuilklmdadq','2020-07-02 15:32:00');
     insert into  test1(name,creatE_time) values('wesv2wqdshehq','2020-07-02 20:32:00');
     insert into  test1(name,creatE_time) values('89yoijnlkwr1','2020-07-03 02:56:00');
     insert into  test1(name,creatE_time) values('olj;nsaaq','2020-07-03 08:41:00');
     insert into  test1(name,creatE_time) values('ygo;jkdsaq','2020-07-03 16:20:00');

    2.  MySQL5.7中创建索引并查看执行计划

    2.1 MySQL5.7中创建升序索引 

    在MySQL5.7中创建升序索引，并执行SQL查看执行计划

      # 升序索引
      alter table test1 add key  idx_nameAsc_createtimeAsc( name,create_time);


      执行语句查看执行计划
        mysql> explain  select * from  test1 order by  name desc ,create_time ;
        +----+-------------+-------+------------+-------+---------------+---------------------------+---------+------+------+----------+-----------------------------+
        | id | select_type | table | partitions | type  | possible_keys | key                       | key_len | ref  | rows | filtered | Extra                       |
        +----+-------------+-------+------------+-------+---------------+---------------------------+---------+------+------+----------+-----------------------------+
        |  1 | SIMPLE      | test1 | NULL       | index | NULL          | idx_nameAsc_createtimeAsc | 309     | NULL |   12 |   100.00 | Using index; Using filesort |
        +----+-------------+-------+------------+-------+---------------+---------------------------+---------+------+------+----------+-----------------------------+


        2.2  MySQL5.7中创建降序索引 

        在MySQL5.7中创建降序索引，并执行SQL查看执行计划

          # 创建降序索引
          alter table test1 add key  idx_nameDesc_createtimeAsc( name desc ,create_time);
          执行SQL并查看执行计划
            mysql> explain  select * from  test1 order by  name desc ,create_time;
            +----+-------------+-------+------------+-------+---------------+---------------------------+---------+------+------+----------+-----------------------------+
            | id | select_type | table | partitions | type  | possible_keys | key                       | key_len | ref  | rows | filtered | Extra                       |
            +----+-------------+-------+------------+-------+---------------+---------------------------+---------+------+------+----------+-----------------------------+
            |  1 | SIMPLE      | test1 | NULL       | index | NULL          | idx_nameAsc_createtimeAsc | 309     | NULL |   12 |   100.00 | Using index; Using filesort |
            +----+-------------+-------+------------+-------+---------------+---------------------------+---------+------+------+----------+-----------------------------+

            发现使用的仍是升序索引，且用到了filesort
            2.3  MySQL5.7中查看索引情况
            查看索引情况会发现，MySQL5.7中，即使创建了降序索引，但是，排序方式依旧是升序（A[sc]）
              mysql> show index from test1;
              +-------+------------+----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
              | Table | Non_unique | Key_name                   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
              +-------+------------+----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
              | test1 |          0 | PRIMARY                    |            1 | id          | A         |          12 |     NULL | NULL   |      | BTREE      |         |               |
              | test1 |          1 | idx_nameAsc_createtimeAsc  |            1 | name        | A         |          10 |     NULL | NULL   | YES  | BTREE      |         |               |
              | test1 |          1 | idx_nameAsc_createtimeAsc  |            2 | create_time | A         |          12 |     NULL | NULL   | YES  | BTREE      |         |               |
              | test1 |          1 | idx_nameDesc_createtimeAsc |            1 | name        | A         |          10 |     NULL | NULL   | YES  | BTREE      |         |               |
              | test1 |          1 | idx_nameDesc_createtimeAsc |            2 | create_time | A         |          12 |     NULL | NULL   | YES  | BTREE      |         |               |
              +-------+------------+----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

              3.  MySQL8.0中创建索引并查看执行计划

              3.1 MySQL5.7中创建升序索引 

              在MySQL8.0中创建升序索引，并执行SQL查看执行计划

                # 升序索引
                alter table test1 add key  idx_nameAsc_createtimeAsc( name,create_time);


                执行语句查看执行计划
                  mysql> explain  select * from  test1 order by  name desc ,create_time ;
                  +----+-------------+-------+------------+-------+---------------+---------------------------+---------+------+------+----------+-----------------------------+
                  | id | select_type | table | partitions | type  | possible_keys | key                       | key_len | ref  | rows | filtered | Extra                       |
                  +----+-------------+-------+------------+-------+---------------+---------------------------+---------+------+------+----------+-----------------------------+
                  |1| SIMPLE      | test1 | NULL       | index | NULL          | idx_nameAsc_createtimeAsc | 309     | NULL |   12 |100.00| Using index; Using filesort |
                  +----+-------------+-------+------------+-------+---------------+---------------------------+---------+------+------+----------+-----------------------------+


                  结果和MySQL5.7 一致，也是需要进行filesort

                  3.2  MySQL8.0中创建降序索引 

                  在MySQL8.0中创建降序索引，并执行SQL查看执行计划

                    # 创建降序索引
                    alter table test1 add key  idx_nameDesc_createtimeAsc( name desc ,create_time);
                    执行SQL并查看执行计划
                      mysql> explain  select * from  test1 order by  name desc ,create_time ;
                      +----+-------------+-------+------------+-------+---------------+----------------------------+---------+------+------+----------+-------------+
                      | id | select_type | table | partitions | type  | possible_keys | key                        | key_len | ref  | rows | filtered | Extra       |
                      +----+-------------+-------+------------+-------+---------------+----------------------------+---------+------+------+----------+-------------+
                      |  1 | SIMPLE      | test1 | NULL       | index | NULL          | idx_nameDesc_createtimeAsc | 409     | NULL |   12 |   100.00 | Using index |
                      +----+-------------+-------+------------+-------+---------------+----------------------------+---------+------+------+----------+-------------+

                      可见，MySQL8.0中的降序索引被使用到了，且排序无需进行filesort
                      3.3  MySQL8.0中查看索引情况
                      查看索引情况会发现，MySQL8.0中，升序索引及降序索引的排序方式出现了区分了
                        +-------+------------+----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
                        | Table | Non_unique | Key_name                   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
                        +-------+------------+----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
                        | test1 |          0 | PRIMARY                    |            1 | id          | A         |          12 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
                        | test1 |          1 | idx_nameAsc_createtimeAsc  |            1 | name        | A         |          10 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
                        | test1 |          1 | idx_nameAsc_createtimeAsc  |            2 | create_time | A         |          12 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
                        | test1 |          1 | idx_nameDesc_createtimeAsc |            1 | name        | D         |          10 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
                        | test1 |          1 | idx_nameDesc_createtimeAsc |            2 | create_time | A         |          12 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
                        +-------+------------+----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------

                        4. 小结

                        • MySQL5.7中，可以创建降序索引，但只是停留在语法层面上，到MySQL8.0才能使用上降序索引

                        • 另外，如果在MySQL5.7及之前版本，order by 多个字段时，建议排序方式一致（可以均升序或均降序），这样方可无需filesort

                        往期精彩回顾

                        1.  MySQL高可用之MHA集群部署

                        2.  mysql8.0新增用户及加密规则修改的那些事

                        3.  比hive快10倍的大数据查询利器-- presto

                        4.  监控利器出鞘：Prometheus+Grafana监控MySQL、Redis数据库

                        5.  PostgreSQL主从复制--物理复制

                        6.  MySQL传统点位复制在线转为GTID模式复制

                        7.  MySQL敏感数据加密及解密

                        8.  MySQL数据备份及还原（一）

                        9.  MySQL数据备份及还原（二）

                        扫码关注     

                        mysql创建索引mysql数据库mysqlmysql执行计划索引
                        文章转载自数据库干货铺，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

                        评论