文档章节

MySQL如何优化ORDER BY

凯文加内特
 凯文加内特
发布于 2014/01/16 00:57
字数 986
阅读 131
收藏 12

在某些情况中,MySQL可以使用一个索引来满足ORDER BY子句,而不需要额外的排序。

即使ORDER BY不确切匹配索引,只要WHERE子句中的所有未使用的索引部分和所有额外的ORDER BY 列为常数,就可以使用索引。下面的查询使用索引来解决ORDER BY部分:

注:key_part1,key_part2为建立了索引的列

SELECT * FROM t1

    ORDER BY key_part1,key_part2,... ;

   

SELECT * FROM t1

    WHERE key_part1=constant

    ORDER BY key_part2;

   

SELECT * FROM t1

    ORDER BY key_part1 DESC, key_part2 DESC;

   

SELECT * FROM t1

    WHERE key_part1=1

    ORDER BY key_part1 DESC, key_part2 DESC;

在某些情况下,MySQL不能使用索引来解决ORDER BY,尽管它仍然使用索引来找到匹配WHERE子句的行。这些情况包括:

·         对不同的关键字使用ORDER BY

·                SELECT * FROM t1 ORDER BY key1, key2;

·         对关键字的非连续元素使用ORDER BY

·                SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2;

·         混合ASCDESC

·                SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;

·         用于查询行的关键字与ORDER BY中所使用的不相同:

·                SELECT * FROM t1 WHERE key2=constant ORDER BY key1;

·         你正联接许多表,并且ORDER BY中的列并不是全部来自第1个用于搜索行的非常量表。(这是EXPLAIN输出中的没有const联接类型的第1个表)

·         有不同的ORDER BYGROUP BY表达式。

·         使用的表索引的类型不能按顺序保存行。例如,对于HEAP表的HASH索引情况即如此。

通过EXPLAIN SELECT ...ORDER BY,可以检查MySQL是否可以使用索引来解决查询。如果Extra列内有Using filesort,则不能解决查询。参见7.2.1节,“EXPLAIN语法(获取关于SELECT的信息)”

文件排序优化不仅用于记录排序关键字和行的位置,并且还记录查询需要的列。这样可以避免两次读取行。文件排序算法的工作象这样:

1.    读行匹配WHERE子句的行,如前面所示。

2.    对于每个行,记录构成排序关键字和行位置的一系列值,并且记录查询需要的列。

3.    根据排序关键字排序元组

4.    按排序的顺序检索行,但直接从排序的元组读取需要的列,而不是再一次访问表。

该算法比以前版本的Mysql有很大的改进。

为了避免速度变慢,该优化只用于排序元组中的extra列的总大小不超过max_length_for_sort_data系统变量值的时候。(将该变量设置得太高的的迹象是将看到硬盘活动太频繁而CPU活动较低)

如果想要增加ORDER BY的速度,首先看是否可以让MySQL使用索引而不是额外的排序阶段。如果不能,可以尝试下面的策略:

·         增加sort_buffer_size变量的大小。

·         增加read_rnd_buffer_size变量的大小。

·         更改tmpdir指向具有大量空闲空间的专用文件系统。该选项接受几个使用round-robin(循环)模式的路径。在Unix中路径应用冒号(:)区间开,在WindowsNetWareOS/2中用分号()。可以使用该特性将负载均分到几个目录中。注释:路径应为位于不同物理硬盘上的文件系统的目录,而不是同一硬盘的不同的分区。

默认情况下,MySQL排序所有GROUP BY col1col2...查询的方法如同在查询中指定ORDER BY col1col2...。如果显式包括一个包含相同的列的ORDER BY子句,MySQL可以毫不减速地对它进行优化,尽管仍然进行排序。如果查询包括GROUP BY但你想要避免排序结果的消耗,你可以指定ORDER BY NULL禁止排序。例如:

INSERT INTO foo

SELECT a, COUNT(*) FROM bar GROUP BY a ORDER BY NULL;

本文转载自:http://dev.mysql.com/doc/refman/5.1/zh/optimization.html#order-by-optimization

凯文加内特
粉丝 341
博文 698
码字总数 110557
作品 0
青岛
后端工程师
私信 提问
MySQL查询优化器--非SPJ优化--ORDERBY优化

MySQL提供了两种排序算法操作(即ORDERBY操作,所以理解排序算法有助于理解ORDERBY优化),在查询执行计划中用“filesort”表示使用了外部文件进行排序。第一种算法是双路排序算法,只利用O...

1415699306
2014/08/31
0
0
MySQL优化(2)--------常用优化

前言   之前已经简单介绍了MySQL的优化步骤,那么接下来自然而是就是常用的SQL优化,比如inseer、group by等常用SQL的优化,会涉及SQL语句内部细节(这正是我缺乏的)。最后希望自己能记录...

JJian
2018/08/03
0
0
PHP 学习必备技能(基础略过)

1.面向对象编程 面向对象编程基本概念 类和对象的关系 如何定义类 成员属性(变量) 如何创建对象实例及如何访问对象属性 对象在内存中存在的形式 栈、堆、全局区、常量区和代码区的关系 成员方...

风雪中的舞者
2015/08/05
0
0
MySQL如何利用索引优化ORDER BY排序语句

1.MySQL索引通常是被用于提高WHERE条件的数据行匹配或者执行联结操作时匹配其它表的数据行的搜索速度。MySQL也能利用索引来快速地执行ORDER BY和GROUP BY语句的排序和分组操作。 通过索引优化...

fzxu_05
2013/03/27
0
0
MySQL 8.0里GROUP BY有变化,注意了

导 读 如题所示,MySQL 8.0开始 group by 默认是没有排序的,那MySQL 8.0之前和 8.0 就有可能结果出现不同 需要警惕 运行如下SQL: 下面是8.0 版本: 运行相同的SQL: 如果这种情况下 进行分...

老叶茶馆_
2018/11/28
0
0

没有更多内容

加载失败,请刷新页面

加载更多

【redis】spring boot利用redis的Keyspace Notifications实现消息通知

前言 需求:当redis中的某个key失效的时候,更新key对应数据在数据库的状态 1、修改redis.conf 安装的redis服务默认是: notify-keyspace-events "",修改成 notify-keyspace-events Ex; 位置...

时刻在奔跑
9分钟前
0
0
IT基础设施中的人工智能可以改变工作的方式

  如今,减少人工智能的宣传和炒作已成为IT领导者的主要工作。提供有关人工智能在何处以及如何将其添加到IT基础设施的深入指南将会提供帮助。   很多技术提供商正在投入巨资,将人工智能...

琴殇的
9分钟前
0
0
vue cli3创建测试打包环境(通过development、production、alpha指定不同的接口地址)

参考地址 前言:项目一般有开发环境,测试环境,生产环境;vue cli内置有开发和生产环境,可以用process.env.NODE_ENV区分,有时候我们和后台同事需要本地连接调试代码,又需要连接测试环境调...

hkaikai
15分钟前
1
0
360安全浏览器龙芯、飞腾、兆芯版

主页https://browser.360.cn/se/linux/ 龙芯 http://down.360safe.com/gc/browser360-cn-stable-10.0.2001.0-1.mips64el.rpm http://down.360safe.com/gc/browser360-cn-stable_10.0.2001.0......

gugudu
16分钟前
34
0
访问JSP时,浏览器显示为

问题: <servlet> <servlet-name>springMVC</servlet-name> <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class> <init-param>......

器石_
20分钟前
0
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部