精华内容
下载资源
问答
  • MySQL 降序索引简介:在本教程中,您将了解MySQL降序索引以及如何利用它来提高查询性能。MySQL降序索引简介降序索引是以降序存储键值的索引。在MySQL 8.0之前,您可以DESC在索引定义中指定。但是,MySQL忽略了它。...

    MySQL 降序索引

    简介:在本教程中,您将了解MySQL降序索引以及如何利用它来提高查询性能。

    MySQL降序索引简介

    降序索引是以降序存储键值的索引。在MySQL 8.0之前,您可以DESC在索引定义中指定。但是,MySQL忽略了它。与此同时,MySQL可以以相反的顺序扫描索引,但成本很高。

    以下语句创建一个带索引的新表:

    CREATE TABLE t(

    a INT NOT NULL,

    b INT NOT NULL,

    INDEX a_asc_b_desc (a ASC, b DESC)

    );

    当您使用SHOW CREATE TABLE在MySQL 5.7,你会发现,DESC如下图所示被忽略:

    mysql> SHOW CREATE TABLE t\G;

    *************************** 1. row ***************************

    Table: t

    Create Table: CREATE TABLE `t` (

    `a` int(11) NOT NULL,

    `b` int(11) NOT NULL,

    KEY `a_asc_b_desc` (`a`,`b`)

    ) ENGINE=InnoDB DEFAULT CHARSET=latin1

    1 row in set (0.00 sec)

    从MySQL 8.0开始,如果DESC在索引定义中使用关键字,则键值将按降序存储。在查询中请求降序时,查询优化器可以利用降序索引。

    以下显示了MySQL 8.0中的表结构:

    mysql> SHOW CREATE TABLE t\G;

    *************************** 1. row ***************************

    Table: t

    Create Table: CREATE TABLE `t` (

    `a` int(11) NOT NULL,

    `b` int(11) NOT NULL,

    KEY `a_asc_b_desc` (`a`,`b` DESC)

    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

    1 row in set (0.00 sec)

    MySQL降序索引示例

    首先,使用不同顺序的四个索引重新创建表t:

    DROP TABLE t;

    CREATE TABLE t (

    a INT,

    b INT,

    INDEX a_asc_b_asc (a ASC , b ASC),

    INDEX a_asc_b_desc (a ASC , b DESC),

    INDEX a_desc_b_asc (a DESC , b ASC),

    INDEX a_desc_b_desc (a DESC , b DESC)

    );

    其次,使用下面的存储过程来插入行到t表:

    DELIMITER $$

    CREATE PROCEDURE insertSampleData(

    IN rowCount INT,

    IN low INT,

    IN high INT

    )

    BEGIN

    DECLARE counter INT DEFAULT 0;

    REPEAT

    SET counter := counter + 1;

    -- insert data

    INSERT INTO t(a,b)

    VALUES(

    ROUND((RAND() * (high-low))+high),

    ROUND((RAND() * (high-low))+high)

    );

    UNTIL counter >= rowCount

    END REPEAT;

    END$$

    DELIMITER ;

    存储的过程中插入的行数(rowCount)与之间的值low和high到a和b所述列t表。

    让我们10,000在t表中插入行,其中随机值介于1和1000之间:

    CALL insertSampleData(10000,1,1000);

    第三,从t表中查询具有不同排序顺序的数据:

    按升序排列a和b列中的值:

    EXPLAIN SELECT

    *

    FROM

    t

    ORDER BY a , b; -- use index a_asc_b_asc

    这是输出:

    +----+-------------+-------+------------+-------+---------------+-------------+---------+------+-------+----------+-------------+

    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

    +----+-------------+-------+------------+-------+---------------+-------------+---------+------+-------+----------+-------------+

    | 1 | SIMPLE | t | NULL | index | NULL | a_asc_b_asc | 10 | NULL | 10192 | 100.00 | Using index |

    +----+-------------+-------+------------+-------+---------------+-------------+---------+------+-------+----------+-------------+

    1 row in set, 1 warning (0.03 sec)

    按升序对a列中的值进行排序,按降序对列 b 中的值进行排序:

    EXPLAIN SELECT

    *

    FROM

    t

    ORDER BY a , b DESC; -- use index a_asc_b_desc

    输出是:

    +----+-------------+-------+------------+-------+---------------+--------------+---------+------+-------+----------+-------------+

    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

    +----+-------------+-------+------------+-------+---------------+--------------+---------+------+-------+----------+-------------+

    | 1 | SIMPLE | t | NULL | index | NULL | a_asc_b_desc | 10 | NULL | 10192 | 100.00 | Using index |

    +----+-------------+-------+------------+-------+---------------+--------------+---------+------+-------+----------+-------------+

    1 row in set, 1 warning (0.01 sec)

    按降序对a列中的值进行排序,按升序对列 b 中的值进行排序:

    EXPLAIN SELECT

    *

    FROM

    t

    ORDER BY a DESC , b; -- use index a_desc_b_asc

    以下说明输出:

    +----+-------------+-------+------------+-------+---------------+--------------+---------+------+-------+----------+-------------+

    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

    +----+-------------+-------+------------+-------+---------------+--------------+---------+------+-------+----------+-------------+

    | 1 | SIMPLE | t | NULL | index | NULL | a_desc_b_asc | 10 | NULL | 10192 | 100.00 | Using index |

    +----+-------------+-------+------------+-------+---------------+--------------+---------+------+-------+----------+-------------+

    1 row in set, 1 warning (0.42 sec)

    按列a和b降序对值进行排序:

    EXPLAIN SELECT

    *

    FROM

    t

    ORDER BY a DESC , b DESC; -- use index a_desc_b_desc

    以下显示输出:

    +----+-------------+-------+------------+-------+---------------+---------------+---------+------+-------+----------+-------------+

    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

    +----+-------------+-------+------------+-------+---------------+---------------+---------+------+-------+----------+-------------+

    | 1 | SIMPLE | t | NULL | index | NULL | a_desc_b_desc | 10 | NULL | 10192 | 100.00 | Using index |

    +----+-------------+-------+------------+-------+---------------+---------------+---------+------+-------+----------+-------------+

    1 row in set, 1 warning (0.01 sec)

    在本教程中,您学习了如何使用MySQL降序索引来提高查询性能。

    展开全文
  • 性能问题简介在MySQL8之前的版本,如果order by中既有升序,又有降序两种混合排序,数据库走了索引之后,还需要使用文件排序,才能获得应用所需要的结果,由于多了文件排序操作,所以SQL语句的执行效率会很低。...

    性能问题简介

    在MySQL8之前的版本,如果order by中既有升序,又有降序两种混合排序,数据库走了索引之后,还需要使用文件排序,才能获得应用所需要的结果,由于多了文件排序操作,所以SQL语句的执行效率会很低。

    6dba4a01e30247d8d3deed27601b971c.png

    索引排序

    在某个字段上创建普通索引之后,大家都知道,字段的值在索引结构里是排过序的,在MySQL8以前的版本,只支持升序排序(asc),不支持降序排序(desc)。虽然在MySQL5.7的版本中,可以创建降序索引的语法,可是在数据库底层是没有实现其功能的,真正降序功能是在MySQL8。

    优化案例

    创建模拟表和数据

    分别在MySQL8.0.18和MySQL5.7.28上创建好测试表

    mysql> select version();+-----------+| version() |+-----------+| 5.7.28    |+-----------+1 row in set (0.01 sec)mysql> show create table t_test_1G;*************************** 1. row ***************************       Table: t_test_1Create Table: CREATE TABLE `t_test_1` (  `id` int(11) NOT NULL,  `col_1` int(11) DEFAULT NULL,  `col_2` char(10) NOT NULL DEFAULT '',  `col_3` varchar(10) NOT NULL DEFAULT '',  `check_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,  PRIMARY KEY (`id`),) ENGINE=InnoDB DEFAULT CHARSET=utf8mb41 row in set (0.01 sec)mysql> select version();+-----------+| version() |+-----------+| 8.0.18    |+-----------+1 row in set (0.01 sec)mysql> show create table t_test_1G;*************************** 1. row ***************************       Table: t_test_1Create Table: CREATE TABLE `t_test_1` (  `id` int(11) NOT NULL,  `col_1` int(11) DEFAULT NULL,  `col_2` char(10) NOT NULL DEFAULT '',  `col_3` varchar(10) NOT NULL DEFAULT '',  `check_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,  PRIMARY KEY (`id`),) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.01 sec)

    使用存储过程进行造数,调用CALL p_create_data(15000);,可以制造15000条记录。

    delimiter $$create PROCEDURE p_create_data(IN loop_times INT)begindeclare var int default 1;while var < loop_times DOset var=var+1;INSERT INTO `t_test_1`( `id`,`col_1`, `col_2`, `col_3`) VALUES       (var,var,var,var);end while;end$$delimiter ;CALL p_create_data(15000);

    测试SQL

    在不使用降序索引的情况下,来看看SQL语句在MySQL8.0.18和MySQL5.7.28执行计划

    测试SQL语句,col_2字段是升序排序,而col_3字段按照降序排序。

    select * from t_test_1 where col_2 like '100%' order by col_2,col_3 desc;

    分别在MySQL8.0.18和MySQL5.7.28创建col_2,col_3的复合索引

    mysql> alter table t_test_1 add index (col_2,col_3);Query OK, 0 rows affected, 1 warning (0.06 sec)Records: 0  Duplicates: 0  Warnings: 1
    276fa8c6b55a47f0b9d12c69a337bdfe.png

    MySQL8.0.18执行计划

    mysql> select version();+-----------+| version() |+-----------+| 8.0.18    |+-----------+1 row in set (0.00 sec)mysql> explain select * from t_test_1 where col_2 like '100%' order by col_2,col_3 desc;+----+-------------+----------+------------+-------+---------------+-------+---------+------+------+----------+---------------------------------------+| id | select_type | table    | partitions | type  | possible_keys | key   | key_len | ref  | rows | filtered | Extra                                 |+----+-------------+----------+------------+-------+---------------+-------+---------+------+------+----------+---------------------------------------+|  1 | SIMPLE      | t_test_1 | NULL       | range | col_2         | col_2 | 40      | NULL |  100 |   100.00 | Using index condition; Using filesort |+----+-------------+----------+------------+-------+---------------+-------+---------+------+------+----------+---------------------------------------+1 row in set, 1 warning (0.01 sec)

    MySQL5.7.28执行计划

    mysql> select version();+-----------+| version() |+-----------+| 5.7.28    |+-----------+1 row in set (0.00 sec)mysql> explain select * from t_test_1 where col_2 like '100%' order by col_2,col_3 desc;+----+-------------+----------+------------+-------+---------------+-------+---------+------+------+----------+---------------------------------------+| id | select_type | table    | partitions | type  | possible_keys | key   | key_len | ref  | rows | filtered | Extra                                 |+----+-------------+----------+------------+-------+---------------+-------+---------+------+------+----------+---------------------------------------+|  1 | SIMPLE      | t_test_1 | NULL       | range | col_2,col_2_2 | col_2 | 40      | NULL |  100 |   100.00 | Using index condition; Using filesort |+----+-------------+----------+------------+-------+---------------+-------+---------+------+------+----------+---------------------------------------+1 row in set, 1 warning (0.01 sec)

    当在MySQL8.0.18和MySQL5.7.28创建索引时,不制定列的排列顺序,字段默认都是升序排序的,所以这种情况,无论是在5.7还是8的版本,都需要做filesort。

    降序索引

    在MySQL8.0.18里创建索引可以制定列的排列顺序,所以按照SQL的order by字段排列顺序,我们可以在创建复合索引时,制定列的排列顺序和order by字段排序顺序一致,这样SQL就不用再做filesort了,来一起看看效果吧。

    在MySQL8.0.18中创建降序索引

    mysql> select version();+-----------+| version() |+-----------+| 8.0.18    |+-----------+1 row in set (0.00 sec)mysql> alter table t_test_1 add index (col_2,col_3 desc);Query OK, 0 rows affected (0.09 sec)Records: 0  Duplicates: 0  Warnings: 0

    查看执行计划

    mysql> select version();+-----------+| version() |+-----------+| 8.0.18    |+-----------+1 row in set (0.00 sec)mysql> explain select * from t_test_1 where col_2 like '100%' order by col_2,col_3 desc;+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+| id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                 |+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+|  1 | SIMPLE      | t_test_1 | NULL       | range | col_2_2       | col_2_2 | 40      | NULL |  100 |   100.00 | Using index condition |+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+1 row in set, 1 warning (0.01 sec)

    可以看到使用了降序索引的SQL语句,已经不再需要做filesort了,MySQL8.0.18的这个降序索引新特性真的能解决一些排序场景的性能问题,还是一个很使用的新功能。

    c9babc22d1f2d727e8d3042df6dd7b4f.png

    关注

    1.如果您喜欢这篇文章,请点赞+转发

    2.如果您特别喜欢,请加关注

    展开全文
  • 不过,在MySQL之前版本中,只支持升序索引,不支持降序索引,这会带来一些问题;在最新的MySQL 8.0版本中,终于引入了降序索引,接下来我们就来看一看。降序索引单列索引(1)查看测试表结构mysql> show create ...

    前言

    相信大家都知道,索引是有序的;不过,在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引入的降序索引,最重要的作用是,解决了多列排序可能无法使用索引的问题,从而可以覆盖更多的应用场景。

    以上就是MySQL8.0中的降序索引的详细内容,更多关于MySQL 降序索引的资料请关注脚本之家其它相关文章!

    展开全文
  • 什么是降序索引大家可能对索引比较熟悉,而对降序索引比较陌生,事实上降序索引是索引的子集。我们通常使用下面的语句来创建一个索引:create index idx_t1_bcd on t1(b,c,d);上面sql的意思是在t1表中,针对b,c,d三...

    什么是降序索引

    大家可能对索引比较熟悉,而对降序索引比较陌生,事实上降序索引是索引的子集。

    我们通常使用下面的语句来创建一个索引:

    create index idx_t1_bcd on t1(b,c,d);

    上面sql的意思是在t1表中,针对b,c,d三个字段创建一个联合索引。

    但是大家不知道的是,上面这个sql实际上和下面的这个sql是等价的:

    create index idx_t1_bcd on t1(b asc,c asc,d asc);

    asc表示的是升序,使用这种语法创建出来的索引叫做升序索引。也就是我们平时在创建索引的时候,创建的都是升序索引。

    可能你会想到,在创建的索引的时候,可以针对字段设置asc,那是不是也可以设置desc呢?

    当然是可以的,比如下面三个语句:

    create index idx_t1_bcd on t1(b desc,c desc,d desc);create index idx_t1_bcd on t1(b asc,c desc,d desc);create index idx_t1_bcd on t1(b asc,c asc,d desc);

    这种语法在mysql中也是支持的,使用这种语法创建出来的索引就叫降序索引,关键问题是:在Mysql8.0之前仅仅只是语法层面的支持,底层并没有真正支持。

    我们分别使用Mysql7、Mysql8两个版本来举例子说明一下:

    在Mysql7、Mysql8中分别创建一个表,有a,b,c,d,e五个字段:

    create table t1 (a int primary key,b int,c int,d int,e varchar(20)) engine=InnoDB;

    然后分别创建一个降序索引:

    create index idx_t1_bcd on t1(b desc,c desc,d desc);

    创建成功后,我们使用以下sql查看一下索引信息:

    show index from t1;

    Mysql7中你将得到结果:2da5dcdf88f95558d2764fab62ceb08a.png

    Mysql8中你将得到结果:9a2da5ae4aee333ee00d53d96ee23254.png

    我们只关心Key_name为idx_t1_bcd的三行记录,细心的你应该可以发现,这两个结果中的Collation字段的结果是不一样的:

    • 在Mysql7中,Collation字段的结果为A,A,A,表示b,c,d三个字段的排序方式是asc

    • 在Mysql8中,Collation字段的结果为D,D,D,表示b,c,d三个字段的排序方式是desc

    但是我们在创建索引的时候,明明在语法层面已经指定了b,c,d三个字段的排序方式是desc,这就可以看出来在Mysql7中降序索引只是语法层面的支持,底层并没有真正支持,并且固定是升序索引。而在Mysql8中则真正从底层支持了降序索引。

    到此为止,大家应该对升序索引和降序索引有了一个大概的了解,但并没有真正理解,因为大家并不知道升序索引与降序索引底层到底是如何实现的。

    升序索引底层实现

    我们知道,索引是用来提高查询速度的,但是为什么索引能提高查询速度呢?

    给定你一个数列,比如[1,3,7,9,2,5,4,6,8],这是一个无序的数列或数组,现在如果想提高这个数列的查询速度,你首先会做什么?我相信大部分人都能够想到先排序,先把这个无序的数列,按从小到大的顺序进行排序,比如得到[1,2,3,4,5,6,7,8,9],有了这个有序的数列之后,我们就可以利用比如二分法等等算法来提高这个数列的查询速度了。

    我举这个例子想告诉大家的是:想提高数据集合的查询速度,首先你可以对这些数据进行排序。

    所以,对Mysql表中的存储的数据也是一样的,我们如果想提高这个表的查询速度,我们可以先对这个表里的数据进行排序,那么表里的某一行数据包括了很多字段,我们现在想对这些数据行进行排序,我们应该根据哪些字段来确定这个顺序呢?这就是索引,在创建索引的时候你所指定的列就是用来对表里的数据行进行排序的。

    比如我们仍然利用上面所创建的t1表,向t1表里插入8条数据:

    insert into t1 values(4,3,1,1,'d');insert into t1 values(1,1,1,1,'a');insert into t1 values(8,8,8,8,'h');insert into t1 values(2,2,2,2,'b');insert into t1 values(5,2,3,5,'e');insert into t1 values(3,3,2,2,'c');insert into t1 values(7,4,5,5,'g');insert into t1 values(6,6,4,4,'f');

    那么这些数据肯定是存储在文件中的,所以文件中保存这些数据的格式大概如下,顺序与插入顺序保持一致:

    4311d
    1111a
    8888h
    2222b
    5235e
    3322c
    7455g
    6644f

    注意,t1是Innodb的存储引擎,而且a字段是主键,所以Innodb存储引擎在处理这些插入的数据时,会按主键进行排序,也就是上面我说的文件中保存这些数据的格式是不准确的,因为不想篇幅太长,所以不去深究,感兴趣的同学可以关注一波公众号:1点25,我会专门写一篇文章来讲解Innodb中索引的具体实现,包括B+树到底是如何生成的。

    而如果我们基于上面的这种存储方式,来查找数据,比如查找a=3的这行记录,查找需要从第一行记录开始查找,那么要查找6次,而如果我们将上面的数据按照a字段的大小来进行排序:

    1111a
    2222b
    3322c
    4311d
    5235e
    6644f
    7455g
    8888h

    排好序之后,如果我们还是查找a=3的这行记录,我们只需要查3次了。而且这样还有一个好处就是,如果我们现在需要查找a=3.5这行数据,如果我们基于未排序之前的存储方式,我们需要查询所有8行数据最终确定a=3.5这行数据不存在,而如果我们利用排好序之后的存储方式,我们就只需要查4次就好了,因为当你查到4311d这行记录时,你会发现4>3.5了,已经可以确定a=3.5的这行记录不存在了。

    而如果我们现在对t1创建一个索引,就像上面创建索引一样,如果我们写的是下面的sql:

    create index idx_t1_bcd on t1(b,c,d);

    这个sql表示要对t1创建一个索引,索引字段是b,c,d,并且是升序的,所以实际上就是对原本的数据按照b,c,d三个字段进行排序,那么排序之后类似:

    1111a
    2222b
    5235e
    4311d
    3322c
    7455g
    6644f
    8888h

    可以好好看下,上面的记录是按照b,c,d三个字段来对数据行就行排序的,比如1111a中的b,c,d三个字段的值是111,而2222b中的b,c,d三个字段的值是222, 111是小于222的,所以对应的行排在前面。

    那么数据如果这样排序有什么好处呢?其实和刚刚按a字段排序之后的好处是类似的,比如你现在想来查找b=4 and c=4 and d=4的数据也是能查询更快的,实际上这就是索引的原理:我们对某个表创建一个索引,就是对这个表中的数据进行排序,而排好序之后的数据是能够提高查询速度。

    还有一点需要注意的是,排序有很多中方式,或者所可以利用一些数据结构,比如二叉树、红黑树、B+树,这些数据结构实际上就是对数据进行排序,只是排序的形式各不相同而已,每种数据结构有它各自的特点,而大家应该都知道,Mysql中用得最多的就是B+树了,还是一样,因为篇幅不想太长,感兴趣的同学可以关注一波公众号:1点25,我会专门写一篇文章来讲解Innodb中索引的具体实现,包括B+树到底是如何生成的。

    相信,看到这里,大家应该对索引重新有了认识,只不过我们上面举的几个例子都是升序排序,而且排好序之后的数据不仅可以提高查询速度,而且对于order by也是管用的,比如我们如果现在想对t1进行order by b asc,c asc,d asc;对于这个排序,如果已经在t1表建立了b,c,d的升序索引,那么就代表对t1表中的数据已经提前按照b,c,d排好序了,所以对于order by语句可以直接使用已经排好序的数据了,不用利用filesort再次进行排序了。

    而且如果我们的order by是order by b desc, c desc, d desc,同样可以利用b,c,d的升序索引,因为如果是order by b asc,c asc,d asc就从上往下遍历即可,如果是order by b desc, c desc, d desc就从下往上遍历即可。

    那么,如果是order by b asc, c desc, d desc呢?这个order by是不是就没有办法利用b,c,d的升序索引了。

    这个时候就需要降序索引了。

    降序索引底层实现

    我们花了较大篇幅介绍了升序索引的实现原理,总结来说就是对表中的数据按照指定的字段比较大小进行升序排序

    升序是什么?是数据进行大小比较后,是小的在上,大的在下,或者如果是B+树的话就是小的在左,大的在右。而降序就是大的在上,小的在下,或者如果是B+树的话就是大的在左,小的在右

    所以,对于上面的那份原始数据:

    4311d
    1111a
    8888h
    2222b
    5235e
    3322c
    7455g
    6644f

    如果我们将这份数据按照a desc进行排序就是:

    8888h
    7455g
    6644f
    5235e
    4311d
    3322c
    2222b
    1111a

    非常简单吧,那如果我们将这份数据按照b desc, c desc, d desc排序就是:

    8888h
    6644f
    7455g
    3322c
    4311d
    5235e
    2222b
    1111a

    也非常简单,那如果我们要将这份数据按照b desc, c asc, d desc排序呢?这是不是就有点懵了?

    其实不难,排序其实就是对数据比较大小,我们用下面三行数据来模拟一下:

    3322c
    7455g
    4311d

    首先,按照b desc, c desc, d desc来排序,得到结果如下:

    7455g
    3322c
    4311d

    按照b desc, c asc, d desc来排序,得到结果如下:

    7455g
    4311d
    3322c

    可能一部分大佬已经能理解,实际上b desc所表达的意思就是b字段数据大者在上,数据小者在下,数据相等的话则开始比较c字段,而c字段是按升序排的,也就是c字段数据小者在下,数据大者在上。所以就得到了上面的结果。

    这就是降序索引

    总结

    实际上升序索引和降序索引是不同的排序方式而已,Mysql8中正在实现了降序索引后,我们在创建索引时更加灵活,可以根据业务需要的排序规则来创建合适的索引,这样能使你的查询更快。

    当然本文只讲了原理,大家一定要知道Mysql中排序利用的B+树,而不是上面我举例的那种很简单的方式,但是就算用B+树原理也是一样的,比较数据的大小而已。

    还有一点,现在只有Innodb存储引擎支持降序索引

    展开全文
  • 作者 许升辉·沃趣科技数据库工程师出品 沃趣科技| 简介MySQL从8.0开始终于支持真正的降序索引了,实际上,在以前的版本当中,语法上可以通过desc来指定索引为降序,但实际上创建的仍然是常规的升序索引。...
  • mysql5中,索引存储的排序方式是ASC的,没有DESC的索引。现在能够理解为啥order by 默认是按照ASC来排序的了吧?虽然索引是ASC的,但是也可以反向进行检索,就相当于DESC了。如果您在ORDER BY 语句中使用了 DESC排序...
  • MySQL問題一 升序和降序淺談MySQL升序和降序,便於我們讀取數據。MySQL 排序我們知道從 MySQL 表中使用 SQL SELECT 語句來讀取數據。如果我們需要對讀取的數據進行排序,我們就可以使用 MySQL 的 ORDER BY 子句來設定...
  • 什么是降序索引大家可能对索引比较熟悉,而对降序索引比较陌生,事实上降序索引是索引的子集。我们通常使用下面的语句来创建一个索引:create index idx_t1_bcd on t1(b,c,d);上面sql的意思是在t1表中,针对b,c,d三...
  • MySQL 8.0终于支持降序索引了。其实,从语法上,MySQL 4就支持了,但正如官方文档所言,"they are parsed but ignored",实际创建的还是升序索引。无图无真相,同一个建表语句,看看MySQL 5.7和8.0的区别。create ...
  • mysql降序与升序

    千次阅读 2019-03-13 10:51:17
    通过 ”order by“实现,语法 :order by 字段 asc/desc。 sql:select * from tablename order by filename1 desc; 解释:上面语句的意思就是根据”filename1字段“排序,...备注:asc是表示升序,desc表示降序。 ...
  • 或者/g结束,换句话说,仅按ENTER不执行命令输入help或者h获得帮助输入quit或exit退出MySql不区分大小写,但是很多人愿意对重要语句用大写,用表名等用小写,便于阅读在 MySQL 中,事务通常以 BEG...
  • XtraBackup 是一个用来备份MySQL的InnoDB数据库的开源工具。例如执行增量备份的命令是:# ./xtrabackup --backup --target-dir=/backup/delta --incremental-basedir=/backup/baseXtraBackup 是一个用来备份MySQL的...
  • 为了先按0然后最大的顺序排序,可以使用以下语法-select*...让我们首先创建一个表-mysql>createtableDemoTable(Valueint);使用插入命令在表中插入记录-mysql>insertintoDemoTab...
  • 在 8.0 之前,能够以相反的顺序对索引进行扫描,但是会降低性能,降序索引能够实现相同的效果,且不会损耗性能。另外一个例子,当一个查询 SQL,需要按多个字段,以不同的顺序进行排序时,8.0 之前无法使用索引已...
  • 在使用order by排序时,NULL会被认为比任何值都小,这个行为与MySQL一致,但是与Oracle不一致。order by后面须加SELECT列的别名。当SELECT某列时,如果没有指定列的别名,则列名会被作为列的别名。当SQL语句set flag...
  • 废话没有,全是干货!!!!使用MySQL命令前须知道的操作知识1.命令输入在mysql>之后; 2.命令用;或g结束,换句话说,仅按Enter不执行命令; 3.... 4....5.进入MySQL编写界面:mysql -uroot -p(...
  • 实现效果:去重—取最新的—排序 例子 : 按用户ID获取历史记录中...本文由【waitig】发表在等英博客 本文固定链接:Mysql 数据记录去重后按字段排序 欢迎关注本站官方公众号,每日都有干货分享! 点赞 (0)赏分享 (0)
  • MYSQL降序排列

    万次阅读 2009-04-15 10:45:00
    mysql> SELECT artist_id , caption FROM artist_lang ORDER BY artist_id desc;
  • 问题详情怎么从mysql按某一字段降序排列后取出有并列数据的记录?想要求出学号为202开头的学生总分前N名的语文平均分。但是以下php语句查询后,$num取总分前7名的时候,显示出来的学号明显不对。请问:这个错在哪里...
  • 推荐:MySQL分页技术、6种分页方法总结这篇文章主要介绍了MySQL分页技术、6种分页方法总结,本文总结了6种分页的方法并分别一一讲解它们的特点,需要的朋友可以参考下 概述 有朋友问: MySQL的分页似乎一直是个问题,有...
  • "oxen" # 设置单复数名 db_table = 'music_album' # 设置数据库中的对应的表名 get_latest_by = ['-priority', 'order_date'] # 取最后一个,按照priority降序, order_date升序 permissions = [('can_deliver_pizzas...
  • MYSQL数据库,一个字段存的格式如"18,2","13,2","10,1",评分的功能,对应的总分为18/2,13/2,10/1,请问用什么查询语句查询出相除后的结果以降序排列。
  • mysql默认自增ID是从1开始了,但当我们如果有插入表或使用delete删除id之后ID就会不会从1开始了哦。使用mysql时,通常表中会有一个自增的id字段,但当我们想将表中的数据清空重新添加数据时,希望id重新从1开始计数...
  • 让我们首先创建一个表-mysql>createtableDemoTable(Valueint);使用插入命令在表中插入一些记录-mysql>insertintoDemoTablevalues(10);mysql>insertintoDemoTablevalues(70);mysql>insertintoDemoTa...
  • 学生表 成绩表 实现代码: SELECT a1.student_id '学号', d.student_name '姓名', a1.score 'Java基础', a2.score 'Java高级', a3.score '前端', b.sumscore '总成绩', ... ( SELECT ss.student_id, ss.score ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 2,507
精华内容 1,002
热门标签
关键字:

mysql降序

mysql 订阅