mysql 索引 order by_【mysql】【索引】中order by 使用索引问题

作者：2023面试高手 | 2024-04-27 00:38:27

踩

order by 后面的字段命中索引

问题：order by 排序字段会不会使用索引？

创建表结构

DROP TABLE IF EXISTS test.test;

CREATE TABLE test.test(

id int(10) not null auto_increment,

a int(10) not null,

b int(10) not null,

c int(10) not null,

PRIMARY key (`id`)

)ENGINE INNODB DEFAULT CHARSET utf8 COMMENT '测试表';

批量插入1000万条

mysql> explain select * from test order by b desc limit 10;

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

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

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

1 row in set, 1 warning (0.00 sec)

现象：执行计划是全盘扫描

增加索引

mysql> ALTER TABLE test ADD INDEX idx_b (b);

Query OK, 0 rows affected (12.57 sec)

再次查询

mysql> explain select * from test order by b desc limit 10;

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

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

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

1 row in set, 1 warning (0.00 sec)

现象：命中索引,执行计划只扫描10行

结论：order by后面的的字段需要创建索引。

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/2023面试高手/article/detail/493907