mysql如何做orderby_MySQL
In general, the optimizer will skip the sort procedure for the ORDER BY clause if it sees that the rows
will be in order anyway. But let's examine some exceptional situations.
For the query:
SELECT column1 FROM Table1 ORDER BY 'x';
the optimizer will throw out the ORDER BY clause. This is another example of dead code elimination.
For the query:
SELECT column1 FROM Table1 ORDER BY column1;
the optimizer will use an index on column1, if it exists.
For the query:
SELECT column1 FROM Table1 ORDER BY column1+1;
the optimizer will use an index on column1, if it exists. But don't let that fool you! The index is only
for finding the values. (It's cheaper to do a sequential scan of the index than a sequential scan of the table,
that's why index is a better join type than ALL — see Section 3.2.2.4, “The index Join Type”.)
There will still be a full sort of the results.
For the query:
SELECT * FROM Table1
WHERE column1 > 'x' AND column2 > 'x'
ORDER BY column2;
i
f both column1 and column2 are indexed, the optimizer will choose an index on ... column1. The
fact that ordering takes place by column2 values does not affect the choice of driver in this case.
See: /sql/sql_select.cc, test_if_order_by_key(), and /sql/sql_select.cc,
test_if_skip_sort_order().
ORDER BY Optimization [http://dev.mysql.com/doc/refman/5.1/en/order-by-optimization.html],
provides a description of the internal sort procedure which we will not repeat here, but urge you to read,
because it describes how the buffering and the quicksort mechanisms operate.
See: /sql/sql_select.cc, create_sort_index().
上一篇: 邻接表来存储数据
推荐阅读
-
Navicat连接mysql报错1862
-
mybatis批量insert数据动态表明(mysql批量insert正确方式)
-
[视频教程] ubuntu系统下安装最新版的MySQL
-
PHP连接MySQL数据库的三种方式实例分析【mysql、mysqli、pdo】
-
在Windows系统上搭建Nginx+Python+MySQL环境的教程
-
PHP Swoole异步MySQL客户端实现方法示例
-
详解Spring Boot Mysql 版本驱动连接池方案选择
-
APMServ一键快速搭建Apache+PHP+MySQL+Nginx+Memcached+ASP运行平台教程
-
MySQL Embedded程序启动成功实验
-
Python实现简单的文件传输与MySQL备份的脚本分享