mysql 的排序
1.indexsort 利用有序索引获取有序数据
原理:
我们知道,mysql的基础数据结构是B+树,任何的一个表都是一颗B+树,你在表上建的索引也是一颗B+树,B+树的特别是在叶子节点上是有序,且前一个节点存在指向相邻节点的指针。
那么在写SQL中的ORDER BY语句时候,若是ORDER BY的条件和返回的数据都在一颗树上,那么就可以利用B+树自身的特点来天然排序了,自然效率会比较高。
使用条件:
1)查询的WHERE子句和ORDER BY子句中查询的字段在同一颗索引树上,
2)ORDER BY 字段的顺序是跟建立索引的顺序是一致的。
3)查询的字段也在同一颗索引树
以上三个条件必须同时满足
2.filesort 文件排序
原理:
这里的文件排序并不是字面那表示的意思,利用了磁盘IO来进行排序,不过是优化器告诉你,进行了一个排序操作,具体排序的地方还是内存,相对应的参数是sort_buffer_size 设定的大小
1)filesort不一定会产生临时表
2)filesort 与临时表数据写入磁盘是没有任何直接联系
使用情景:
任何非索引排序的场景下
3.实战:
CREATE TABLE `A_index` (
`c1` INT(11) NOT NULL AUTO_INCREMENT ,
`c2` SMALLINT(2) ,
`c3` VARCHAR(16) ,
`c4` VARCHAR(16) ,
`c5` VARCHAR(16) ,
PRIMARY KEY (`c1`),
KEY `c2_ind` (`c2`),
KEY `c3_c4_c5_ind` (`c3`,`c4`,`c5`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
1)查询的字段和ORDER BY 字段在同一个索引树中:
mysql> EXPLAIN SELECT c3 FROM A_index ORDER BY c3 ,c4 ,c5 ;
+----+-------------+---------+-------+---------------+--------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+--------------+---------+------+------+-------------+
| 1 | SIMPLE | A_index | index | NULL | c3_c4_c5_ind | 153 | NULL | 1 | Using index |
+----+-------------+---------+-------+---------------+--------------+---------+------+------+-------------+
1 row in set (0.02 sec)
2)查询的字段和where 及 order by 中的字段在同一颗树中
mysql> EXPLAIN SELECT c3 FROM A_index where c3="1" ORDER BY c3 ,c4 ,c5 ;
+----+-------------+---------+------+---------------+--------------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+--------------+---------+-------+------+--------------------------+
| 1 | SIMPLE | A_index | ref | c3_c4_c5_ind | c3_c4_c5_ind | 51 | const | 1 | Using where; Using index |
+----+-------------+---------+------+---------------+--------------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)
对于上面的差别是做了一次回表
3)where 及 order by 中的字段不在同一颗树中
mysql> EXPLAIN SELECT c3 FROM A_index where c2=1 ORDER BY c3 ,c4 ,c5 ;
+----+-------------+---------+------+---------------+--------+---------+-------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+--------+---------+-------+------+-----------------------------+
| 1 | SIMPLE | A_index | ref | c2_ind | c2_ind | 3 | const | 1 | Using where; Using filesort |
+----+-------------+---------+------+---------------+--------+---------+-------+------+-----------------------------+
1 row in set (0.01 sec)
在两颗索引树中后,就走的是filesort了。
4)查询的字段和ORDER BY 字段不在同一个索引树中:
mysql> EXPLAIN SELECT c2 FROM A_index ORDER BY c3 ,c4 ,c5 ;
+----+-------------+---------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+----------------+
| 1 | SIMPLE | A_index | ALL | NULL | NULL | NULL | NULL | 1 | Using filesort |
+----+-------------+---------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)
5)查询的字段和ORDER BY 字段在同一个索引树中,但是order by 顺序不同
mysql> EXPLAIN SELECT c3 FROM A_index ORDER BY c4 , c3 ,c5 ;
+----+-------------+---------+-------+---------------+--------------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+--------------+---------+------+------+-----------------------------+
| 1 | SIMPLE | A_index | index | NULL | c3_c4_c5_ind | 153 | NULL | 1 | Using index; Using filesort |
+----+-------------+---------+-------+---------------+--------------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)
看到排序使用的是filesort,对于c3的查找用的是索引
4.优化filesort
从上面的索引排序使用限制上可以看出,我们大部分的业务场景应该是在filesort下,那么就该想着如何优化filesort。
filesort的两种排序算法:
双路排序
1)取出所有排序的字段及对应行数据的指针信息(指向具体的叶子节点数据)
2)在内存中进行排序
3)再通过指针取得具体的数据
单路排序
1)去除所有排序的字段及所有行信息
2)在内存中进行排序
两种的差别在于第一种需要两次磁盘IO,而第二种只需要一次,但第二种需要更大的内存开销。
优化器如何选择主要是根据 max_length_for_sort_data 系统参数设置的大小
mysql> show variables like '%max_length%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| max_length_for_sort_data | 1024 |
+--------------------------+-------+
1 row in set (0.00 sec)
若是查询出来的数据大小 > max_length_for_sort_data ,那么使用双路排序
若是查询出来的数据大小 < max_length_for_sort_data ,那么使用单路排序
相关推荐
activiti5.9修复mysql order by 排序bug 详情见博客地址:http://blog.csdn.net/qq413041153/article/details/7740773#comments
1.如果索引了多列,要遵守最左前缀法则 2.当MySQL一旦估计检查的行数可能会”太多”,范围查找优化将不会被 3.索引列不应该作为表达式的一部分,即也不能在索
突发奇想,想了解一下mysql order by排序是以什么规则进行的? 好了,话不多说,直接进入正题吧。 MySql order by 单字段 建一测试表如下: CREATE TABLE `a` ( `code` varchar(255) DEFAULT NULL, `name` ...
select areaName from area order by convert(areaName USING gbk) COLLATE gbk_chinese_ci asc 说明:areaName为列名 area为表名 PS:这里再为大家推荐一款本站的相关在线工具供大家参考: 在线中英文根据首字母...
mysql 中order by 与group by的顺序是:selectfromwheregroup byorder by注意:group by 比order by先执行,order by不会对group by 内部进行排序,如果group by后只有一条记录,那么order by 将无效。要查出group ...
在一些情况下,MySQL可以直接使用索引来满足一个 ORDER BY 或 GROUP BY 子句而无需做额外的排序
创建表&创建索引 ...MySQL也能利用索引来快速地执行ORDER BY和GROUP BY语句的排序和分组操作。 通过索引优化来实现MySQL的ORDER BY语句优化: 1、ORDER BY的索引优化 如果一个SQL语句形如: SELECT
–按某一字段分组取最大(小)值所在行的数据 代码如下: /* 数据如下: nameval memo a 2 a2(a的第二个值) a 1 a1–a的第一个值 a 3 a3:a的第三个值 b 1 b1–b的第一个值 b 3 b3:b的第三个值 b 2 b2b2b2b2 b 4 b4b4 b ...
如果需要按照多个列进行排序,可以在`ORDER BY`子句中指定多个列名,并按照优先级逐个进行排序。 通过阅读和实践这个文档,您将能够更好地掌握MySQL数据库中排序查询的使用方法。无论是初学者还是有经验的用户,都...
数据库版本:MySQL 5.6.42 条件: 某字段代表该数据的状态取值为非负整数,0表示无状态。 需求: 以该字段升序排序,同时需要将值为0的数据放在最后。 首先我们看一下,表的结构: 正常的使用升序查询结果是这样的...
排序查询(order by) 电商中:我们想查看今天所有成交的订单,按照交易额从高到低排序,此时我们可以使用数据库中的排序功能来完成。 排序语法: select 字段名 from 表名 order by 字段1 [asc|desc],字段2 [asc...
mysql中order by 排序查询、asc升序、desc降序,group by 分组查询、having 只能用于group by子句、作用于组内,having条件子句可以直接跟函数表达式。使用group by 子句的查询语句需要使用聚合函数。
计算机后端-PHP视频教程. php与mysql加强- 07. php加强16-order by排序.wmv
首先先按某个字段进行order by,然后把有顺序的表进行分组,这样每组的成员都是有顺序的,而mysql默认取得分组的第一行。从而得到每组的最值。 select id, (@rowno := @rowno + 1) as rank, score, (C.end_ti
order by 从英文里理解就是行的排序方式,默认的为升序。 order by 后面必须列出排序的字段名,可以是... 您可能感兴趣的文章:sql中 order by 和 group by的区别深度分析mysql GROUP BY 与 ORDER BYgroup by,having,o
在一条SELECT语句中,如果不使用ORDER BY子句,结果中行的顺序是不可预料的。使用ORDER BY子句后可以保证结果中的行按一定顺序排列。 语法格式: ORDER BY {列名 | 表达式 | 列编号} [ASC | DESC] , ... 说明:ORDER...
工作过程中,各种业务需求在访问数据库的时候要求有order by排序。有时候不必要的或者不合理的排序操作很可能导致数据库系统崩溃。如何处理好order by排序呢?本文从原理以及优化层面介绍 order by 。 一 MySQL中...
在MySQL数据库中,Order by语句的使用频率是比较高的。但是众所周知,在使用这个语句时,往往会降低数据查询的性能。因为可能需要对数据库的记录进行重新排序。在这篇文章中,笔者就谈谈提高Order By语句查询效率的...
今天在使用ORDER BY的过程中...如果我们在执行select语句的时候使用ORDER BY (DESC),那么它首先会对所有记录按照关键字有一个排序,然后依次读取所需的记录,而不是先选出记录再进行降序排列。 一个概念性的错误,