Mysql中order by语句的优化详解

在某些情况中，MySQL可以使用一个索引来满足ORDER BY子句，而不需要额外的排序。where条件和order by使用相同的索引，并且order by的顺序和索引顺序相同，并且order by的字段都是升序或者都是降序。

一、建议使用一个索引来满足Order By子句。

在条件允许的情况下，笔者建议最好使用一个索引来满足Order By子句。如此的话，就可以避免额外的排序工作。这里笔者需要强调的一点是及时Order By子句不确切匹配索引，但是只要Where子句中所有未使用的索引部分和所有额外的 Order by子句中的列为常数，此时就可以使用索引。具体的来说，推荐如下的查询语句。

 代码如下 复制代码

1、select * from ad_user where is_active=’Y’ order by value;

在这条查询语句中，使用了两个列。在Where查询语句中，查询表中活动的记录。此时使用的是一个常数的条件。而在Order By子句中，则根据Value列的值来进行排序。如果在表设计中，为这个字段设置一个索引。此时使用这条语句来进行查询，则查询结果就不需要进行额外的排序工作，从而可以提高数据的查询效率。

这也就是说，如果Where条件语句与Order By条件语句一起使用，如果需要通过一个索引来提高查询效率的话，那么就必须满足一个条件，及where条件语句中所使用的参数值是常数，而不是变量。如果使用变量的话，这个方法就不奏效了。

例如：下列sql可以使用索引。

 

 代码如下 复制代码
   SELECT * FROM t1 ORDER BY key_part1,key_part2,... ;
    SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC;
    SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;

 
但是以下情况不使用索引。

 代码如下 复制代码

1) SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC；

--order by的字段混合ASC和DESC

2) SELECT * FROM t1 WHERE key2=constant ORDER BY key1；

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

3) SELECT * FROM t1 ORDER BY key1, key2；

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

2、注意有些情况下不能够使用索引来提高Order By语句的查询性能。

这里需要注意的是，并不是任何情况下都能够通过使用索引来提高Order Byz子句的查询效率。如对不同的关键字使用这个语句、混合使用ASC模式和DESC模式、用于查询条件的关键字与Order By语句中所使用的关键字不同、对关键字的非连续元素使用Order By子句、在同一条语句中使用不同的Order BY 和Group BY表达式、使用的表索引的类型不能够按顺序来保存行等情况，就无法通过使用索引来解决Order By语句的排序问题。此时就需要另想他法。如可以重新调整表结构或者查询语句，以满足使用这个特性的特定条件。

其实这里就遇到一个均衡的问题。如在查询时，Where条件语句中往往使用的是一个变量，这主要是为了提高语句的灵活性。这个变量接受前端用户传递过来的参数。此时如果用户同时有排序的需求，根据上面介绍的规则，就无法使用索引来提高查询的效率。此时作为开发人员，就需要评估，需要语句的灵活性还是需要查询的性能。通常情况下，对于记录量比较大的查询，同时其查询的格式比较固定，如大容量的月报与年报，此时就会倾向于查询语句的性能。而对于记录量比较少的查询，如日报表，或者使用频率比较高的查询语句，此时会更加的倾向于查询的灵活性。作为开发人员，现在需要关注的就是根据用户实际的情况，来选择合适的解决方式。

通常情况下，为了避免使用Order By语句导致的查询速度变慢的问题，先是需要考虑使用索引来解决问题。如果不能够通过索引来解决问题，那么可以通过缓存在一定程度来缓解。如可以增加soft_buffer_size变量的大小、根据实际情况调整Read_buffer_size变量的大小、更改tmpdir目录将其指向具有大量空闲空间的专用文件系统等等。有时候管理员可以使用这个特性将负载均匀分布到多个目录中去。

二、使用Explain关键字来确认是否可以通过索引来解决Order BY速度问题。

如果用户无法确定是否可以通过索引来提高Order By语句的查询效率，那么就可以凭借Explain关键字来帮助关键员进行判断。如可以通过使用explain select * from ad_user where is_active=’Y’ order by value(即在常规的查询语句前面加上一个explain关键字)，用来判断是否可以使用索引来提高查询的效率。判断的方法是：如果这个查询语句中，有一个using filesort这个字段，那么就非常的抱歉，无法通过使用索引来提高这个语句的查询效率。反之，没有这个字段，则说明可以通过索引来提高查询效率。

这里需要说明的是，通常情况下文件排序优化不仅仅可以用于记录排序关键字和行的位置，并且还会记录查询所需要的列。如此的话，就可以避免多次读取行的信息。为了让大家更加明白其中的道理笔者简单说明一下这工作的过程。通常情况下，文件排序优化包括四 个步骤。第一步读取与Where条件语句所匹配的行信息;第二步对于每个行、记录构成排序关键字和行位置的一系列值，并且记录查询所需要的列;第三步根据排序关键字排序元祖;第四步按排序的顺序检索行，不过此时是直接从排序的元祖读取所需要的列(使用的是第三个步骤中的结果)，而不会重新访问表中的数据。显然使用文件排序优化的思路，可以避免重复访问表，从而提高查询的效率

一次mysql order by 优化案例

 代码如下 复制代码

1.  desc SELECT ActionID, UserID, CreateUser, ActionType, ActionName, ActionComment, CreateDate, PointsRulesID,Reason,ObjectID,ByUserID,ByUserName,SubjectID FROM  wikiuseractionlog Where CreateDate>'0001-01-01 00:00:00 '  and ActionType in(10,9,19,20)  ORDER BY CreateDate DESC limit 93060,20;
   
| id | select_type | table             | type | possible_keys          | key  | key_len | ref  | rows  | Extra                       |
+----+-------------+-------------------+------+------------------------+------+---------+------+-------+-----------------------------+
|  1 | SIMPLE      | wikiuseractionlog | ALL  | idx_date_type,idx_date | NULL | NULL    | NULL | 91859 | Using where; Using filesort

看执行计划，没有用上索引，虽然我建了一个复合索引idx_date_type(createdate,actiontype)和一个列索引idx_date(createdate)，要使上面的语句走索引只要有下面几点：

1）需要将日期‘0001-01-01 00：00：00’改成2014-01-01 00:00:00  (2013以下都不行，不知道为什么)！！补充：DBA群有大神说：select 的结果集占全表的20%，就会走全表 ，而不走索引！ 我想可能是MYSQL优化器根据CBO成本估算，如果select数据量大，估计走索引的成本会比走全表的还大，所以才会全表扫描而不走索引！

2）需将createdate和actiontype做复合索引！一般情况下order by的字段如果要走索引优化，就需要将where条件下条件字段与order by的字段做联合索引！比如： select * from test_0719 order by name;这种条件下没有where 条件字段，就算name上有索引，也还是会发生文件排序，那这种条件下，要么是增加一个where 条件字段，然后把这个字段与name做联合索引！要么是把name字段也添加到where 条件下！  

3） limit 93060,20  这种语句应该避免，如果此表不是非常大！93060行就已经占据了全表比较大的量，那mysql优化器可能就不会选择索引，就算有索引也不会走！而是选择全表扫描！因为要取的数据量非常大！mysql优化器很可能会认为走索引的成本比走全表扫描的成本还大！  同上面第一点！

 代码如下 复制代码

mysql> desc SELECT ActionID, UserID, CreateUser, ActionType, ActionName, ActionComment, CreateDate, PointsRulesID,Reason,ObjectID,ByUserID,ByUserName,SubjectID FROM  wikiuseractionlog  ORDER BY CreateDate;
+----+-------------+-------------------+------+---------------+------+---------+------+-------+----------------+
| id | select_type | table             | type | possible_keys | key  | key_len | ref  | rows  | Extra          |
+----+-------------+-------------------+------+---------------+------+---------+------+-------+----------------+
|  1 | SIMPLE      | wikiuseractionlog | ALL  | NULL          | NULL | NULL    | NULL | 95220 | Using filesort |
+----+-------------+-------------------+------+---------------+------+---------+------+-------+----------------+
1 row in set (0.00 sec)

mysql> desc SELECT ActionID, UserID, CreateUser, ActionType, ActionName, ActionComment, CreateDate, PointsRulesID,Reason,ObjectID,ByUserID,ByUserName,SubjectID FROM  wikiuseractionlog Where CreateDate>'2014-01-01 00:00:00 '  ORDER BY CreateDate DESC  limit 93060,20;
+----+-------------+-------------------+-------+------------------------+---------------+---------+------+------+-------------+
| id | select_type | table             | type  | possible_keys          | key           | key_len | ref  | rows | Extra       |
+----+-------------+-------------------+-------+------------------------+---------------+---------+------+------+-------------+
|  1 | SIMPLE      | wikiuseractionlog | range | idx_date_type,idx_date | idx_date_type | 8       | NULL | 3737 | Using where

4）此种情况下虽然有单独的索引idx_date(createdate)，但发现依旧用不上索引，原因是order by的字段也必须出现在where 条件里，此处的原因大概也就是，排序操作是在最后的，首先提取数据的时候，如果where条件里的字段有索引，在提取出来的时候就已经做了排序操作了，此后再order by的时候就不用filesort了，不然则，在最后提取出来的数据里order by的时候，自然就要做filesort了！！

5）下面第一个例子用不上索引！第二个则可以！将ActionType in(10,9,19,20) 改成or 也不行！此处涉及到in 优化，可以尝试改成union all

 代码如下 复制代码

mysql> desc SELECT ActionID, UserID, CreateUser, ActionType, ActionName, ActionComment, PointsRulesID,Reason,ObjectID,ByUserID,ByUserName,SubjectID FROM  wikiuseractionlog Where ActionType in(10,9,19,20)  ORDER BY CreateDate;
+----+-------------+-------------------+------+---------------+------+---------+------+-------+-----------------------------+
| id | select_type | table             | type | possible_keys | key  | key_len | ref  | rows  | Extra                       |
+----+-------------+-------------------+------+---------------+------+---------+------+-------+-----------------------------+
|  1 | SIMPLE      | wikiuseractionlog | ALL  | idx_type_date | NULL | NULL    | NULL | 90747 | Using where; Using filesort |
+----+-------------+-------------------+------+---------------+------+---------+------+-------+-----------------------------+
1 row in set (0.00 sec)

mysql> desc SELECT ActionID, UserID, CreateUser, ActionType, ActionName, ActionComment, PointsRulesID,Reason,ObjectID,ByUserID,ByUserName,SubjectID FROM  wikiuseractionlog Where ActionType ='10'  ORDER BY CreateDate;
+----+-------------+-------------------+------+---------------+---------------+---------+-------+-------+-------------+
| id | select_type | table             | type | possible_keys | key           | key_len | ref   | rows  | Extra       |
+----+-------------+-------------------+------+---------------+---------------+---------+-------+-------+-------------+
|  1 | SIMPLE      | wikiuseractionlog | ref  | idx_type_date | idx_type_date | 3       | const | 45373 | Using where |
+----+-------------+-------------------+------+---------------+---------------+---------+-------+-------+-------------+
1 row in set (0.00 sec)

总结：1

order by 后的字段，如果要走索引，须与where 条件里的某字段建立复合索引！！或者说orcer by后的字段如果要走索引排序，它要么与where 条件里的字段建立复合索引【这里建立复合索引的时候，需要注意复合索引的列顺序为（where字段，order by 字段），这样才能满足最左列原则，原因可能是order by字段并能算在where 查询条件中！】，要么它自身要在where 条件里被引用到！

总结：2

表a       id为普通字段,上面建有索引
select * from a order by id   (用不上索引)
select id from a order by id (能用上索引)
select * from a where id=XX order by id  (能用上索引)
意思是说order by 要避免使用文件系统排序，要么把order by的字段出现在select 后，要么使用order by字段出现在where 条件里，要么把order by字段与where 条件字段建立复合索引！

时间： 2024-11-17 16:26:03

Mysql中order by语句的优化详解的相关文章

MySQL中replace into语句的用法详解_Mysql

在向表中插入数据的时候,经常遇到这样的情况: 1.首先判断数据是否存在: 2.如果不存在,则插入: 3.如果存在,则更新.   在 SQL Server 中可以这样写: 复制代码 代码如下: if not exists (select 1 from table where id = 1) insert into table(id, update_time) values(1, getdate()) else update table set update_time = getdate() whe

mysql中order by 语句的用法 索引优化

MySQL Order By keyword是用来给记录中的数据进行分类的. MySQL Order By Keyword根据关键词分类 ORDER BY keyword是用来给记录中的数据进行分类的. MySQL Order By语法  代码如下 复制代码 SELECT column_name(s) FROM table_name ORDER BY column_name 注意:SQL语句是"字母大小写不敏感"的语句(它不区分字母的大小写),即:"ORDER BY"

MYSQL分页limit速度太慢优化详解

在mysql中limit可以实现快速分页,但是如果数据到了几百万时我们的limit必须优化才能有效的合理的实现分页了,否则可能卡死你的服务器哦. 当一个表数据有几百万的数据的时候成了问题! 如 * from table limit 0,10 这个没有问题 当 limit 200000,10 的时候数据读取就很慢,可以按照一下方法解决 第一页会很快 PERCONA PERFORMANCE CONFERENCE 2009上,来自雅虎的几位工程师带来了一篇"EfficientPagination Us

mysql中date_add与date_sub函数使用详解

mysql 中 DATE_ADD(date,INTERVAL expr type) 和 DATE_SUB(date,INTERVAL expr type) 这些函数执行日期运算. date 是一个 DATETIME 或DATE值,用来指定起始时间. expr 是一个表达式,用来指定从起始日期添加或减去的时间间隔值.  Expr是一个字符串;对于负值的时间间隔,它可以以一个 '-'开头. type 为关键词,它指示了表达式被解释的方式. 关键词INTERVA及 type 分类符均不区分大小写. m

Mysql中Btree 与 Hash 索引比较详解

mysql最常用的索引结构是btree(O(log(n))),但是总有一些情况下我们为了更好的性能希望能使用别的类型的索引.hash就是其中一种选择,例如我们在通过用户名检索用户id的时候,他们总是一对一的关系,用到的操作符只是=而已,假如使用hash作为索引数据结构的话,时间复杂度可以降到O(1).不幸的是,目前的mysql版本(5.6)中,hash只支持MEMORY和NDB两种引擎,而我们最常用的INNODB和MYISAM都不支持hash类型的索引. 不管怎样,还是要了解一下这两种索引的区别

mysql中null,not null,default,auto_increment详解

NULL 和 NOT NULL 修饰符: 可以在每个字段后面都加上这NULL 或 NOT NULL 修饰符来指定该字段是否可以为空(NULL),还是说必须填上数据(NOT NULL).MySQL默认情况下指定字段为NULL修饰符,如果一个字段指定为NOT NULL,MySQL则不允许向该字段插入空值(这里面说的空值都为NULL),因为这是"龟定".  代码如下 复制代码 /* 创建好友表,其中id ,name ,pass都不能为空 */ create table friends ( i

Mysql中where与having用法区别详解

让我们先运行2个sql语句:  代码如下 复制代码 SELECT * FROM `welcome` HAVING id >1 LIMIT 0 , 30 SELECT * FROM `welcome` WHERE id >1 LIMIT 0 , 30 查看一下结果吧,怎么样?是不是查询到相同的结果. 让我们再看2个sql语句:  代码如下 复制代码 SELECT user, MAX(salary) FROM users GROUP BY user HAVING MAX(salary)>10

mysql中ASCII、ORD函数用法详解

一,ASCII(str1) 返回字符串str的最左面字符的ASCII代码值.如果str是空字符串,返回0.如果str是NULL,返回NULL 举例: 1.  代码如下 复制代码 mysql> select ascii('hi'); +-----+ | ascii('hi') | +-----+ |         104 | +-----+ 1 row in set 104是h的ASCII值 2.输出b和B的ASCII值  代码如下 复制代码 mysql> SELECT ASCII('b')A

mysql中int和varchar的长度详解

int 从 -2^31 (-2,147,483,648) 到 2^31 – 1 (2,147,483,647) 的整型数据(所有数字).存储大小为 4 个字节.int 的 SQL-92 同义字为 integer varchar 长度是0-255个字符哦 mysql 字段中int后面所跟数字有何意义? varchar后的数字又有何意义?  代码如下 复制代码 mysql> create table t(a int(1)); Query OK, 0 rows affected (0.10 sec)