MySQL 降序索引简介?
2023-02-25 18:20:34 时间
使用过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
相关文章
- 学生数据库管理系统
- SpringDataJpa 用MySQL语句怎么分页,spring全家桶SpringDataJpa 用MySQL语句怎么分页
- Docker创建MySQL容器模板命令
- Elasticsearch对应MySQL的对应关系
- 使用SpringDataJpa保存(save)报错误:SQL Error: 1062, SQLState: 23000 控制台会报:Duplicate entry ‘数‘ for key ‘PRIMA
- Navicat Premium 连接sqlserver数据库时提示安装Client失败,解决方案
- Mysql查询当前用户所有数据库语句(SHOW DATABASES)
- MySQL语句-查看当前数据库有哪些表(SHOW TABLES)
- MySQL5.0版本以上新增的 information_schema 数据库是什么?
- MariaDB数据库备份之逻辑备份
- MariaDB数据库创建用户
- MariaDB数据库给用户授权
- MariaDB数据库刷新权限表命令
- MariaDB数据库删除用户命令
- PhpStudy 2016搭建-sqli-libs靶场
- MySQL手动注入步骤
- Pikachu靶场-SQL注入-数字型注入(post)过关步骤
- Pikachu靶场-SQL注入-字符型注入(get)过关步骤
- 利用SQL注入漏洞实现MySQL数据库读写文件
- Kali-工具-sqlmap常见用法