MySQL8倒序索引测试

在mysql8中创建倒序索引，测试执行结果

测试环境版本

mysql> select @@version;
+-------------------+
| @@version         |
+-------------------+
| 8.0.32-commercial |
+-------------------+
1 row in set (0.00 sec)
 

创建倒序索引

mysql> create table t100(c1 int,c2 int,index idx_c1_c2(c1,c2 desc));
Query OK, 0 rows affected (0.85 sec)

mysql> show create table t100\G
*************************** 1. row ***************************
       Table: t100
Create Table: CREATE TABLE `t100` (
  `c1` int DEFAULT NULL,
  `c2` int DEFAULT NULL,
  KEY `idx_c1_c2` (`c1`,`c2` DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

测试表中插入测试数据

DELIMITER ;;
CREATE PROCEDURE test_insert()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i < 100000 DO
INSERT INTO t100 SELECT RAND() * 100000, RAND() * 100000;
SET i = i + 1;
END WHILE;
COMMIT;
END ;;
DELIMITER ;
CALL test_insert();

查看执行结果

mysql> explain select * from t100 order by c1,c2 desc limit 5;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t100  | NULL       | index | NULL          | idx_c1_c2 | 10      | NULL |    5 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)


mysql> explain select * from t100 order by c1 desc,c2 desc limit 5;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-----------------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows   | filtered | Extra                       |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-----------------------------+
|  1 | SIMPLE      | t100  | NULL       | index | NULL          | idx_c1_c2 | 10      | NULL | 100211 |   100.00 | Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)


mysql> explain select * from t100 order by c1,c2  limit 5;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-----------------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows   | filtered | Extra                       |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-----------------------------+
|  1 | SIMPLE      | t100  | NULL       | index | NULL          | idx_c1_c2 | 10      | NULL | 100211 |   100.00 | Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)