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
相关推荐
创建表&创建索引 create table tbl1 ( id int unique, sname varchar(50), index tbl1_index_sname(sname desc...通过索引优化来实现MySQL的ORDER BY语句优化: 1、ORDER BY的索引优化 如果一个SQL语句形如: SELECT
4、当你的程序和数据库结构/SQL语句已经优化到无法优化的程度,而程序瓶颈并不能顺利解决,那就是应该考虑使用诸如memcached这样的分布式缓存系统的时候了。 5、习惯和强迫自己用EXPLAIN来分析你SQL语句的性能。 一...
课程大纲: 第1课 数据库与关系代数 综述数据库、关系代数、查询优化技术 综述数据库调优技术 预计时间1小时 第2课 数据库查询优化技术总揽 ...真正认识、掌握MySQL的查询优化技术,大步流星步入查询优化的高手之列。
在MySQL查询语句过程和EXPLAIN语句基本概念及其优化中介绍了EXPLAIN语句,并举了一个慢查询例子: 可以看到上述的查询需要检查1万多记录,并且使用了临时表和filesort排序,这样的查询在用户数快速增长后将成为噩梦...
本文从原理以及优化层面介绍 order by 。 一 MySQL中order by的原理 1 利用索引的有序性获取有序数据 当查询语句的 order BY 条件和查询的执行计划中所利用的 Index 的索引键(或前面几个索引键)完全一致,且...
在一些情况下,MySQL可以直接使用索引来满足一个 ORDER BY 或 GROUP BY 子句而无需做额外的排序
关于mysql索引一些优化介绍与创建原则,还有对order by排序的算法的介绍等等
1、理解MYSQL的Query Optimizer 2、Query语句优化基本思路和原则 3、充分利用Explain 和 Profiling 4、合理设计并利用索引 5、order by、group by 和 DISTINCT优化 6、小结
在MySQL数据库中,Order by语句的使用频率是比较高的。但是众所周知,在使用这个语句时,往往会降低数据查询的性能。因为可能需要对数据库的记录进行重新排序。在这篇文章中,笔者就谈谈提高Order By语句查询效率的...
1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。 2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如: select id ...
本篇文章我们将了解ORDER BY语句的优化,在此之前,你需要对索引有基本的了解,不了解的老少爷们可以先看一下我之前写过的索引相关文章。现在让我们开始吧。 MySQL中的两种排序方式 1.通过有序索引顺序扫描直接返回...
文章目录测试数据1 sql执行顺序2 order by 和 group by什么时候会出现Using filesort — 理论3 order by 和 group by什么时候会出现Using filesort — 实践3.1 不会出现 Using filesort的情况 — 符合最佳左前缀法则...
MySQL通常使用GROUPBY(本质上是排序动作)完成DISTINCT操作,如果DISTINCT操作和ORDERBY操作组合使用,通常会用到临时表.这样会影响性能. 在一些情况下,MySQL可以使用索引优化DISTINCT操作,但需要活学活用.本文涉及一个...
有大量重复值、且经常有范围查询( > ,,> =,)和order by、group by发生的列,可考虑建立群集索引; b.经常同时存取多列,且每列都含有重复值可考虑建立组合索引; c.组合索引要尽量使关键查询形成索引覆盖,...
今天在使用ORDER BY的过程中出现了一点问题,发现之前对ORDER BY理解是错误的。 之前在w3s网站上看到ORDER BY的用法,以为是对选出来的数据按关键字升序或者降序排列,结果今天尝试select数据集数据的时候,发现使用...
排序查询(order by) 电商中:我们想查看今天所有成交的订单,按照交易额从高到低排序,此时我们可以使用数据库中的排序功能来完成。 排序语法: select 字段名 from 表名 order by 字段1 [asc|desc],字段2 [asc...
众所周知,在MySQL中,如果直接 ORDER BY RAND() 的话,效率非常差,因为会多次执行。事实上,如果等值查询也是用 RAND() 的话也如此,我们先来看看下面这几个SQL的不同执行计划和执行耗时。 首先,看下建表DDL,这...
MySQL Order By keyword是用来给记录中的数据进行分类的。MySQL Order By Keyword根据关键词分类ORDER BY keyword是用来给记录中的数据进行分类的。 代码如下:SELECT column_name(s) FROM table_name ORDER BY ...