各大网站大全,网站 外包合同,dedecms 调用网站名称,建设手机网站培训教程MySQL 降序索引简介#xff1a;在本教程中#xff0c;您将了解MySQL降序索引以及如何利用它来提高查询性能。MySQL降序索引简介降序索引是以降序存储键值的索引。在MySQL 8.0之前#xff0c;您可以DESC在索引定义中指定。但是#xff0c;MySQL忽略了它。与此同时#xff0…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: tCreate Table: CREATE TABLE t (a int(11) NOT NULL,b int(11) NOT NULL,KEY a_asc_b_desc (a,b)) ENGINEInnoDB DEFAULT CHARSETlatin11 row in set (0.00 sec)从MySQL 8.0开始如果DESC在索引定义中使用关键字则键值将按降序存储。在查询中请求降序时查询优化器可以利用降序索引。以下显示了MySQL 8.0中的表结构mysql SHOW CREATE TABLE t\G;*************************** 1. row ***************************Table: tCreate Table: CREATE TABLE t (a int(11) NOT NULL,b int(11) NOT NULL,KEY a_asc_b_desc (a,b DESC)) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci1 row in set (0.00 sec)MySQL降序索引示例首先使用不同顺序的四个索引重新创建表tDROP 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)BEGINDECLARE counter INT DEFAULT 0;REPEATSET counter : counter 1;-- insert dataINSERT INTO t(a,b)VALUES(ROUND((RAND() * (high-low))high),ROUND((RAND() * (high-low))high));UNTIL counter rowCountEND REPEAT;END$$DELIMITER ;存储的过程中插入的行数(rowCount)与之间的值low和high到a和b所述列t表。让我们10,000在t表中插入行其中随机值介于1和1000之间CALL insertSampleData(10000,1,1000);第三从t表中查询具有不同排序顺序的数据按升序排列a和b列中的值EXPLAIN SELECT*FROMtORDER 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*FROMtORDER 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*FROMtORDER 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*FROMtORDER 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降序索引来提高查询性能。
