`

MySQL优化三之order by

 
阅读更多

 

 

ORDER BY 可以匹配索引是限制的

具体可以参考下http://dev.mysql.com/doc/refman/5.6/en/order-by-optimization.html


本地做了下测试,发现跟单纯的读文档的理解差别还是挺大的。

知识点一:ORDER BY的字段是否走索引跟表的数据量有关系,表的数据量比较小的时候会走全表扫描,数据量比较大的时候才会走索引,这可能也是MySQL基于代价的结果。

 

知识点二:WHERE KEY_PART1 > CONST ORDER BY KEY_PART1,KEY_PART2也是可以走索引的,但是官方没有列举这么仔细。

 

知识点三:MySQL两种排序方式一种是需要回表的,另外一种不需要回表。

一般情况包含大字段的时候会需要回表,否则直接都拿出来了对关键字排序即可。

 

知识点四:这里filesort并不是说通过磁盘文件进行排序,仅仅告诉我们进行了一个排序操作。

 

知识点五:如果排序字段同时存在两个表中,或者join完之后排序,则需要在临时表中进行,一般如果order by字段在驱动表上则先对驱动表进行排序再做join也是顺序的。

 

知识点六:如果无法避免排序,该如何优化。

1.增大max_length_for_sort_data 如果所有字段的最大长度小于这个参数值的时候,MySQL会选择第二种排序算法,否则选择第一种。

2.去掉不必要的字段,如果内存不够但是增大max_length_for_sort_data,则需要排序的数据会分成很多段进行,效率比较低,去掉不必要的字段来适应max_length_for_sort_data。

3.增大sort_buffer_size参数设置:

增大这个参数并不是为了让MySQL可以选择第二种排序算法,而是为了让数据减少排序的分段。

 

 

ORDER BY可以走索引的情况:

 

-- Order by
explain SELECT * FROM `mytest`.`table_rm002` ORDER BY `float`,`real`;

-- Order by的排序顺序必须一致
explain SELECT * FROM `mytest`.`table_rm002` ORDER BY `float` asc,`real` asc;

-- Order by不一定完全匹配索引,但是where中必须是常量
explain SELECT * FROM `mytest`.`table_rm002` where `float` = 100  ORDER BY `real` asc;

-- Order by是组合索引,where中必须是常量
explain SELECT * FROM `mytest`.`table_rm002` where `float` = 100  ORDER BY `float` asc,`real` asc;

-- Order by是组合索引,where中必须是常量【不能走索引】
explain SELECT * FROM `mytest`.`table_rm002` where `real` = 100  ORDER BY `float` asc,`real` asc;

-- 下面三种待验证
SELECT * FROM t1
  WHERE key_part1 > constant
  ORDER BY key_part1 ASC;

SELECT * FROM t1
  WHERE key_part1 < constant
  ORDER BY key_part1 DESC;

SELECT * FROM t1
  WHERE key_part1 = constant1 AND key_part2 > constant2
  ORDER BY key_part2

测试了下官网给出的三个模板SQL有点令人误解:
其实这样的格式也是可以使用索引的
SELECT * FROM t1
  WHERE key_part1 > constant
  ORDER BY key_part1, key_part2;

 

 

测试案例:

构建10条数据的表



 

看看10条数据的ORDER BY索引会怎么走,竟然没有走索引,而是使用了using filesort



 

测试50W条数据情况



 

 

看看50W条数据的ORDER BY索引会怎么走,发现走了索引idx_cmplx,奇怪吧,反正我是有点颠覆了价值观。



 

  

 

    看吧,虽然这里的key_part1是< ,order by是key_part1和key_part2但是仍然走了索引

    

mysql> explain SELECT * FROM `mytest`.`table_rm002` where `float`  < 100  order by `float`,`real` \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: table_rm002
         type: range
possible_keys: idx_cmplx,idx_float
          key: idx_cmplx
      key_len: 5
          ref: NULL
         rows: 59
        Extra: Using where
1 row in set (0.01 sec)

 

 

 

附上测试中使用的SQL语句

-- 查看Query的执行计划
explain SELECT * FROM `mytest`.`table_rm002` ORDER BY `float`,`real`;

-- 重建表索引
analyze table   `mytest`.`table_rm002`;

-- 删除数据
delete from  `mytest`.`table_rm002`;

-- 构造数据
insert into   `mytest`.`table_rm002` select * from `mytest`.`table_rm001` limit 500000;

-- 表的创建语句
CREATE TABLE `table_rm002` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `varchar` varchar(32) DEFAULT NULL,
  `tinyint` tinyint(4) DEFAULT NULL,
  `smallint` smallint(6) DEFAULT NULL,
  `mediumint` mediumint(9) DEFAULT NULL,
  `bigint` bigint(20) DEFAULT NULL,
  `integer` int(11) DEFAULT NULL,
  `float` float DEFAULT NULL,
  `real` double DEFAULT NULL,
  `decimal` decimal(10,0) DEFAULT NULL,
  `boolean` tinyint(1) DEFAULT NULL,
  `date` date DEFAULT NULL,
  `datetime` datetime DEFAULT NULL,
  `time` time DEFAULT NULL,
  `year` year(4) DEFAULT NULL,
  `text` text,
  `blob` blob,
  PRIMARY KEY (`id`),
  KEY `idx_float` (`float`),
  KEY `idx_varchar` (`varchar`),
  KEY `idx_cmplx` (`float`,`real`)
) ENGINE=MyISAM AUTO_INCREMENT=3000001 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT

 

  • 大小: 136.4 KB
  • 大小: 194 KB
  • 大小: 154.1 KB
  • 大小: 185.9 KB
0
1
分享到:
评论
2 楼 labreeze 2015-05-22  
greatwqs 写道
说明 MySQL这方面还是比较智能   


赞同,MySQL的算法还是比较智能的。
1 楼 greatwqs 2015-05-21  
说明 MySQL这方面还是比较智能   

相关推荐

    MySQL数据库优化SQL篇PPT课件.pptx

    从执行计划、SELECT语句、IN和EXIST语句、LIMIT语句、RAND函数、Order by、Group by、Distinct和Count等方面对MySQL数据库优化进行了详细的讲解。 一、执行计划 执行计划是MySQL数据库优化的重要步骤。执行计划...

    MySQL 通过索引优化含ORDER BY的语句

    MySQL数据库在处理含`ORDER BY`的SQL语句时,索引优化是非常关键的一环,因为这直接影响到查询性能。以下是一些关于如何利用索引来优化`ORDER BY`语句的知识点: 1. **合理创建索引**:索引可以显著提高数据读取...

    mysql性能优化.pptx

    - 索引优化SQL:避免全表扫描,尽可能利用索引来加速查询,减少`SELECT`、`JOIN`、`ORDER BY`、`GROUP BY`操作的复杂度。 - 索引维护:定期重建和优化索引,确保其整洁高效。 4. **数据库表结构优化** - **数据...

    MySQL利用索引优化ORDER BY排序语句的方法

    创建表&创建索引 create table tbl1 ( id int unique, sname varchar(50), index tbl1_index_sname(sname desc...通过索引优化来实现MySQL的ORDER BY语句优化: 1、ORDER BY的索引优化 如果一个SQL语句形如: SELECT

    MySQL Order By索引优化方法

    总之,理解`ORDER BY`和`GROUP BY`的索引优化策略,以及如何通过`EXPLAIN`分析查询计划,可以帮助我们有效地优化查询,避免不必要的`filesort`操作,从而提升MySQL数据库的性能。在设计索引时,应考虑查询语句的常见...

    MySQL GUI Tools MYSQL优化工具

    - **查询优化**: 优化SQL语句,避免全表扫描和子查询,使用JOIN代替子查询,合理使用LIMIT和ORDER BY。 - **存储引擎选择**: 根据数据访问模式选择合适的存储引擎,如InnoDB支持事务处理,MyISAM适合读取密集型...

    Mysql优化 PPT

    这可能包括遵循规范的SQL编写习惯,避免在WHERE子句中使用函数,合理使用LIMIT和ORDER BY,以及避免过度依赖临时表。 总的来说,这份"Mysql优化 PPT"应该是一份全面的指南,帮助读者理解MySQL性能优化的各个方面,...

    Mysql优化方法介绍.ppt

    MySQL 优化方法主要涵盖...总之,MySQL优化涉及多个层面,包括合理选择存储引擎、优化字段类型、建立合适索引以及编写高效的SQL语句。每个环节都对数据库性能有着直接影响,需要根据实际业务需求进行综合考虑和调整。

    mysql性能优化教程.pdf (by caoz)

    SELECT * FROM user WHERE area = '$area' AND sex = '$sex' ORDER BY lastlogin DESC LIMIT 0, 30; ``` - **解决方案**:创建`area + sex + lastlogin`的复合索引,这样可以同时利用地区和性别筛选结果,并按...

    【DISTINCT】优化之MySQL官方文档翻译

    ### DISTINCT优化之MySQL官方文档翻译解析 #### 一、引言 在数据库查询操作中,经常需要使用`DISTINCT`关键字来去除重复记录,确保结果集中的每一条记录都是唯一的。然而,在某些场景下,使用`DISTINCT`可能会导致...

    MYSQL优化资料

    MySQL优化是指通过一系列技术和策略来提高数据库性能,确保系统能够高效地处理大量数据请求。以下是对MySQL优化的详细探讨: 一、SQL查询优化 1. 索引优化:创建合适的索引来加速查询。主键自动创建唯一索引,非...

    MySQL数据库查询优化

    课程大纲: 第1课 数据库与关系代数 综述数据库、关系代数、查询优化技术 综述数据库调优技术 预计时间1小时 第2课 数据库查询优化技术总揽 ...真正认识、掌握MySQL的查询优化技术,大步流星步入查询优化的高手之列。

    mysql优化方案

    本文讨论了 MYSQL 优化方案的多个方面,涵盖 BIOS 设置优化、IO 子系统优化、Schema 设计优化、索引设计优化和无法使用索引的场景等方面的知识点,旨在帮助读者更好地了解 MYSQL 优化的方法和策略。

    mysql 优化.docx

    我们可以通过使用其他方法来优化ORDER BY RAND()语句,提高SQL语句的性能。 七、in和exists的区分 in和exists是SQL语句中常用的两个关键字。in和exists的主要区别在于驱动顺序的改变。如果是in,那么先执行子查询...

    辛星笔记之MySQL优化篇

    在深入探讨辛星笔记之MySQL优化篇的内容之前,我们首先要了解MySQL优化的几个关键方面,这将帮助我们更好地理解文档中提供的知识点。 MySQL优化通常包括对数据库查询的优化、数据库结构的设计优化、服务器配置的...

    MySQL优化大揭秘.rar

    考虑在经常用于WHERE子句、JOIN条件和ORDER BY语句的列上创建索引。 - **避免全表扫描**:尽量避免使用SELECT *,只选择需要的列,减少I/O操作。 - **使用EXPLAIN分析查询**:理解查询执行计划,找出可能导致全表...

    MySQL中(JOIN/ORDER BY)语句的查询过程及优化方法

    在MySQL查询语句过程和EXPLAIN语句基本概念及其优化中介绍了EXPLAIN语句,并举了一个慢查询例子: 可以看到上述的查询需要检查1万多记录,并且使用了临时表和filesort排序,这样的查询在用户数快速增长后将成为噩梦...

    mysql查询优化之索引优化

    本文将深入探讨“mysql查询优化之索引优化”这一主题。 首先,了解索引的基本概念至关重要。索引是数据库为了快速查找数据而创建的一种数据结构,类似于书籍的目录,它使得数据检索更快,减少了全表扫描的可能性。...

    MySQL的or、in、union与索引优化

    SELECT * FROM order WHERE status IN (0, 1) ORDER BY date DESC; ``` 此查询可以利用`status`和`date`的索引来提高效率,特别是如果这两个字段都有索引的话。 4. **组合条件查询**: ``` SELECT * FROM ...

Global site tag (gtag.js) - Google Analytics