mysql中的order by

最新推荐文章于 2024-05-31 09:50:51 发布

aoerqileng

最新推荐文章于 2024-05-31 09:50:51 发布

阅读量370

点赞数

分类专栏： MYSQL

本文链接：https://blog.csdn.net/aoerqileng/article/details/100123622

版权

MYSQL 专栏收录该内容

274 篇文章 2 订阅

订阅专栏

order by中带limit与不带limit返回的顺序不一定是相同的。

下面这种语句也是会使用索引的
SELECT * FROM t1
WHERE key_part1 = constant
ORDER BY key_part2;

 KEY `idx_c` (`TABLE_NAME`,`TABLE_ROWS`)
explain select table_name,TABLE_ROWS from dba_test order by table_name asc,TABLE_ROWS desc;
+----+-------------+----------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------------+
| id | select_type | table    | partitions | type  | possible_keys | key   | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+----------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | dba_test | NULL       | index | NULL          | idx_c | 203     | NULL | 6038 |   100.00 | Using index; Using filesort |
+----+-------------+----------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

filesort也不是完全在磁盘文件中的排序，也可能是在内存中的排序
https://dev.mysql.com/doc/refman/8.0/en/order-by-optimization.html