性能文章>MySQL全面瓦解9:查询的排序、分页相关>

MySQL全面瓦解9:查询的排序、分页相关原创

5月前
2883019

概述

数据库中的数据直接呈现出来一般不是我们想要的,所以我们上两节演示了如何对数据进行过滤的方法。除了对数据进行过滤,

我们可能还需要对数据进行排序,比如想从列表中了解消费最高的项,就可能需要对金额字段做降序排序,想看年龄从小到大的分布情况,就可能需要对user表的age字段进行升序排序。

也可能需要对数据进行限制,比如我们需要对付款的1~10,11~20,21~30 名的用户分别赠予不同的礼品,这时候对数据的限制就很有用了。

备注:下面脚本中[]包含的表示可选,| 分隔符表示可选其一。

数据排序 order by

语法格式如下:

1、需要排序的字段跟在order by之后;

2、asc 和 desc表示排序的规则,asc:升序,desc:降序,默认为升序 asc;

3、排序可以指定多次字段,多字段排序之间用逗号隔开。

4、多字段排序中,越靠前优先级越高,下面中cname1优先排序,当cname1等值的时候,cname2开始排序,直至所有字段都排序完。

select cname from tname order by cname1 [asc|desc],cname2 [asc|desc]...;

 

单个字段排序

举个例子,在销售额中通按照交易的订单进行金额额度降序的方式显示:

1 mysql> select * from t_order;
 2 +---------+---------+---------+-------+
 3 | orderid | account | amount  | goods |
 4 +---------+---------+---------+-------+
 5 |       8 | brand   | 52.2    |     2 |
 6 |       9 | hen     | 1752.02 |     7 |
 7 |      10 | helyn   | 88.5    |     4 |
 8 |      11 | sol     | 1007.9  |    11 |
 9 |      12 | diny    | 12      |     1 |
10 |      13 | weng    | 52.2    |     5 |
11 |      14 | sally   | 99.71   |     9 |
12 +---------+---------+---------+-------+
13 7 rows in set
14 
15 mysql> select * from t_order order by amount desc;
16 +---------+---------+---------+-------+
17 | orderid | account | amount  | goods |
18 +---------+---------+---------+-------+
19 |       9 | hen     | 1752.02 |     7 |
20 |      11 | sol     | 1007.9  |    11 |
21 |      14 | sally   | 99.71   |     9 |
22 |      10 | helyn   | 88.5    |     4 |
23 |       8 | brand   | 52.2    |     2 |
24 |      13 | weng    | 52.2    |     5 |
25 |      12 | diny    | 12      |     1 |
26 +---------+---------+---------+-------+
27 7 rows in set

 

多个字段排序

多个字段排序用逗号隔开,优先级从左到右逐次递减,如下图,如果金额一致,则按照购买商品数量从多到少排序:

mysql> select * from t_order order by amount desc,goods desc;
 +---------+---------+---------+-------+
 | orderid | account | amount  | goods |
 +---------+---------+---------+-------+
 |       9 | hen     | 1752.02 |     7 |
 |      11 | sol     | 1007.9  |    11 |
 |      14 | sally   | 99.71   |     9 |
 |      10 | helyn   | 88.5    |     4 |
 |      13 | weng    | 52.2    |     5 |
 |       8 | brand   | 52.2    |     2 |
 |      12 | diny    | 12      |     1 |
 +---------+---------+---------+-------+
 7 rows in set

 

按alias排序

按照别名排序或者做条件查询的目的都是为了简化代码,方便使用,别名可以是英文,也可以是中文:

 mysql> select account as ac,amount as am,goods as gd from t_order order by am,gd desc;
    
 +-------+---------+----+
 | ac    | am      | gd |
 +-------+---------+----+
 | diny  | 12      |  1 |
 | weng  | 52.2    |  5 |
 | brand | 52.2    |  2 |
 | helyn | 88.5    |  4 |
 | sally | 99.71   |  9 |
 | sol   | 1007.9  | 11 |
 | hen   | 1752.02 |  7 |
 +-------+---------+----+
 7 rows in set

 

字段排序中使用函数

下面使用了abs取绝对值函数,所以在 am字段降序排序中,-99.99 排在 99.71之上。

 1 mysql> select * from t_order;
 2 +---------+---------+---------+-------+
 3 | orderid | account | amount  | goods |
 4 +---------+---------+---------+-------+
 5 |       8 | brand   | 52.2    |     2 |
 6 |       9 | hen     | 1752.02 |     7 |
 7 |      10 | helyn   | 88.5    |     4 |
 8 |      11 | sol     | 1007.9  |    11 |
 9 |      12 | diny    | 12      |     1 |
10 |      13 | weng    | 52.2    |     5 |
11 |      14 | sally   | 99.71   |     9 |
12 |      15 | brand1  | -99.99  |     5 |
13 +---------+---------+---------+-------+
14 8 rows in set
15 
16 mysql> select account as ac,amount as am,goods as gd from t_order order by abs(am) desc;
17    
18 +--------+---------+----+
19 | ac     | am      | gd |
20 +--------+---------+----+
21 | hen    | 1752.02 |  7 |
22 | sol    | 1007.9  | 11 |
23 | brand1 | -99.99  |  5 |
24 | sally  | 99.71   |  9 |
25 | helyn  | 88.5    |  4 |
26 | brand  | 52.2    |  2 |
27 | weng   | 52.2    |  5 |
28 | diny   | 12      |  1 |
29 +--------+---------+----+
30 8 rows in set

 

与Where条件结合使用

order 在 where 条件之后,根据where已经过滤好的数据再进行排序。下面是过滤出购买金额>80 且 购买数量>5的数据,并且按照价格降序排序。

 1 mysql> select * from t_order;
 2 +---------+---------+---------+-------+
 3 | orderid | account | amount  | goods |
 4 +---------+---------+---------+-------+
 5 |       8 | brand   | 52.2    |     2 |
 6 |       9 | hen     | 1752.02 |     7 |
 7 |      10 | helyn   | 88.5    |     4 |
 8 |      11 | sol     | 1007.9  |    11 |
 9 |      12 | diny    | 12      |     1 |
10 |      13 | weng    | 52.2    |     5 |
11 |      14 | sally   | 99.71   |     9 |
12 |      15 | brand1  | -99.99  |     5 |
13 +---------+---------+---------+-------+
14 8 rows in set
15 
16 mysql> select * from t_order where amount>80 and goods>5 order by amount desc;
17 +---------+---------+---------+-------+
18 | orderid | account | amount  | goods |
19 +---------+---------+---------+-------+
20 |       9 | hen     | 1752.02 |     7 |
21 |      11 | sol     | 1007.9  |    11 |
22 |      14 | sally   | 99.71   |     9 |
23 +---------+---------+---------+-------+

 

数据limit

很多时候我们过滤出符合要求的数据之后,还需要得到这些数据中的某一个具体区间,比如对付款超过1000的用户的第1~10,11~20,21~30 名分别赠予不同的礼品,这时候就要使用limit操作了。

limit用来限制select查询返回的数据,常用于数据排行或者分页等情况。

语法格式如下:

select cname from tname limit [offset,] count;

1、offset表示偏移量,就是指跳过的行数,可以省略不写,默认为0,表示跳过0行,如 limit 8 等同于 limit 0,8。

2、count:跳过偏移量offset之后开始取的数据行数,有count行。

3、limit中offset和count的值不能用表达式。

获取前n条记录

如下图,limit n 和 limit 0,n 是一致的:

 1 mysql> select * from t_order;
 2 +---------+---------+---------+-------+
 3 | orderid | account | amount  | goods |
 4 +---------+---------+---------+-------+
 5 |       8 | brand   | 52.2    |     2 |
 6 |       9 | hen     | 1752.02 |     7 |
 7 |      10 | helyn   | 88.5    |     4 |
 8 |      11 | sol     | 1007.9  |    11 |
 9 |      12 | diny    | 12      |     1 |
10 |      13 | weng    | 52.2    |     5 |
11 |      14 | sally   | 99.71   |     9 |
12 |      15 | brand1  | -99.99  |     5 |
13 +---------+---------+---------+-------+
14 8 rows in set
15 
16 mysql> select * from t_order limit 2
17 ;
18 +---------+---------+---------+-------+
19 | orderid | account | amount  | goods |
20 +---------+---------+---------+-------+
21 |       8 | brand   | 52.2    |     2 |
22 |       9 | hen     | 1752.02 |     7 |
23 +---------+---------+---------+-------+
24 2 rows in set
25 
26 mysql> select * from t_order limit 0,2;
27 +---------+---------+---------+-------+
28 | orderid | account | amount  | goods |
29 +---------+---------+---------+-------+
30 |       8 | brand   | 52.2    |     2 |
31 |       9 | hen     | 1752.02 |     7 |
32 +---------+---------+---------+-------+
33 2 rows in set 

 

limit限制单条记录

这边我们获取支付金额中最大和最小的的一条记录。可以先使用 order 条件进行排序,然后limit 第1条记录即可:

 mysql> select * from t_order;
  +---------+---------+---------+-------+
  | orderid | account | amount  | goods |
  +---------+---------+---------+-------+
  |       8 | brand   | 52.2    |     2 |
  |       9 | hen     | 1752.02 |     7 |
  |      10 | helyn   | 88.5    |     4 |
  |      11 | sol     | 1007.9  |    11 |
  |      12 | diny    | 12      |     1 |
  |      13 | weng    | 52.2    |     5 |
  |      14 | sally   | 99.71   |     9 |
  |      15 | brand1  | -99.99  |     5 |
  +---------+---------+---------+-------+
  8 rows in set
 
  mysql> select * from t_order where amount>0 order by amount desc limit 1;
  +---------+---------+---------+-------+
  | orderid | account | amount  | goods |
  +---------+---------+---------+-------+
  |       9 | hen     | 1752.02 |     7 |
  +---------+---------+---------+-------+
  1 row in set
 
  mysql> select * from t_order where amount>0 order by amount asc limit 1;
  +---------+---------+--------+-------+
  | orderid | account | amount | goods |
  +---------+---------+--------+-------+
  |      12 | diny    | 12     |     1 |
  +---------+---------+--------+-------+
  1 row in set

 

获取(m,n)区间记录

即跳过m条,获取n条,示例如下,我们跳过两条,从第三条开始,连取四条的操作:

 1 mysql> select * from t_order order by amount;
 2 +---------+---------+---------+-------+
 3 | orderid | account | amount  | goods |
 4 +---------+---------+---------+-------+
 5 |      15 | brand1  | -99.99  |     5 |
 6 |      12 | diny    | 12      |     1 |
 7 |       8 | brand   | 52.2    |     2 |
 8 |      13 | weng    | 52.2    |     5 |
 9 |      10 | helyn   | 88.5    |     4 |
10 |      14 | sally   | 99.71   |     9 |
11 |      11 | sol     | 1007.9  |    11 |
12 |       9 | hen     | 1752.02 |     7 |
13 +---------+---------+---------+-------+
14 8 rows in set
15 
16 mysql> select * from t_order order by amount limit 2,4;
17 +---------+---------+--------+-------+
18 | orderid | account | amount | goods |
19 +---------+---------+--------+-------+
20 |       8 | brand   | 52.2   |     2 |
21 |      13 | weng    | 52.2   |     5 |
22 |      10 | helyn   | 88.5   |     4 |
23 |      14 | sally   | 99.71  |     9 |
24 +---------+---------+--------+-------+
25 4 rows in set

 

分页的做法与这个类似,我们程序业务上看到的分页一般有 pageIndex,pageSize等参数,我们通常的做法是 limit pageIndex*pageSize,pageSize。

这边假设有31条数据,每页数量pageSize=10,页面索引pageIndex默认0,则第一页就是 limit 0,10,第二页 limit10,10,第三页 limit 20,10,第四页 limit 30,10。

注意点:

1、limit 后面不能使用表达式,只能使用明确的数值,否则会爆出异常,比如 limit 0*10,10,是不对的,这个上面提过了。

2、limit后续的数值只能是正整数和0,也就是说,不能是负数,否则同样会报错。

3、排序字段的值相同情况下,排序后分页会出现混乱重复的情况。

第3点详细说明下:假如根据age排序,但是有多个age都是20岁的同学,这时候我们3条记录一页,就会出现分页混乱数据重复。因为年龄相同的人有多个,这是几个人的排序在每次查询的时候会有不确定性。

举个例子,下面的分页,混乱了

 1 mysql> select * from user3;
 2 +----+------+-------+
 3 | id | age  | name  |
 4 +----+------+-------+
 5 |  1 |   20 | brand |
 6 |  2 |   22 | sol   |
 7 |  3 |   20 | helen |
 8 |  4 | 19.5 | diny  |
 9 |  6 |   19 | a     |
10 |  7 |   20 | b     |
11 |  8 |   20 | c     |
12 |  9 |   20 | d     |
13 | 10 |   20 | e     |
14 | 11 |   23 | f     |
15 +----+------+-------+
16 10 rows in set
17 
18 mysql> select * from user3 order by age limit 0,3; 
19 +----+------+-------+
20 | id | age  | name  |
21 +----+------+-------+
22 |  6 |   19 | a     |
23 |  4 | 19.5 | diny  |
24 |  3 |   20 | helen |
25 +----+------+-------+
26 3 rows in set
27 
28 mysql> select * from user3 order by age limit 3,3;
29  
30 +----+-----+-------+
31 | id | age | name  |
32 +----+-----+-------+
33 |  3 |  20 | helen |
34 |  7 |  20 | b     |
35 |  8 |  20 | c     |
36 +----+-----+-------+
37 3 rows in set
38 
39 mysql> select * from user3 order by age limit 6,3;
40  
41 +----+-----+-------+
42 | id | age | name  |
43 +----+-----+-------+
44 |  7 |  20 | b     |
45 |  3 |  20 | helen |
46 |  2 |  22 | sol   |
47 +----+-----+-------+
48 3 rows in set 

我们的做法是使用重复值字段做排序的时候再加个唯一依据(一般可以设主键),就不会混乱了。

如下示例,正常了:

 1 mysql> select * from user3;
 2 +----+------+-------+
 3 | id | age  | name  |
 4 +----+------+-------+
 5 |  1 |   20 | brand |
 6 |  2 |   22 | sol   |
 7 |  3 |   20 | helen |
 8 |  4 | 19.5 | diny  |
 9 |  6 |   19 | a     |
10 |  7 |   20 | b     |
11 |  8 |   20 | c     |
12 |  9 |   20 | d     |
13 | 10 |   20 | e     |
14 | 11 |   23 | f     |
15 +----+------+-------+
16 10 rows in set
17 
18 mysql> select * from user3 order by age,id limit 0,3;
19  
20 +----+------+-------+
21 | id | age  | name  |
22 +----+------+-------+
23 |  6 |   19 | a     |
24 |  4 | 19.5 | diny  |
25 |  1 |   20 | brand |
26 +----+------+-------+
27 3 rows in set
28 
29 mysql> select * from user3 order by age,id limit 3,3;
30  
31 +----+-----+-------+
32 | id | age | name  |
33 +----+-----+-------+
34 |  3 |  20 | helen |
35 |  7 |  20 | b     |
36 |  8 |  20 | c     |
37 +----+-----+-------+
38 3 rows in set
39 
40 mysql> select * from user3 order by age,id limit 6,3;
41  
42 +----+-----+------+
43 | id | age | name |
44 +----+-----+------+
45 |  9 |  20 | d    |
46 | 10 |  20 | e    |
47 |  2 |  22 | sol  |
48 +----+-----+------+
49 3 rows in set

 

上述总结

1、order by cname [asc|desc] 用于对查询结果排序,asc为升序,desc为降序,可以省略,省略情况下默认为asc。

2、limit用来限制查询结果返回的行数,包含2个参数(offset,count),offset:表示跳过多少行,count:表示跳过offset行之后取的行数。limit中offset可以省略,默认值为0;limit中offset 和 count都必须大于等于0;limit中offset和count的值不能用表达式。

3、分页排序时,排序字段不要有重复值,重复值情况下可能会导致分页结果混乱,建议在后面加一个主键排序。

 

请先登录,再评论

暂无回复,快来写下第一个回复吧~

为你推荐

5G时代,如何彻底搞定海量数据库的设计与实践
5G时代,业务数据越来越丰富,业务使用MySQL数据库作为后台存储,存储引擎使用InnoDB,会带来哪些挑战?如何针对公司业务特点及MySQL数据库特性,制定若干数据库使用规范供一线RD在设计业务时参
MySQL之KEY分区引发的血案
需求背景业务表tb_image部分数据如下所示,其中id唯一,image_no不唯一。image_no表示每个文件的编号,每个文件在业务系统中会生成若干个文件,每个文件的唯一ID就是字段id:业务表t
Prometheus时序数据库-数据的插入
前言在之前的文章里,笔者详细的阐述了Prometheus时序数据库在内存和磁盘中的存储结构。有了前面的铺垫,笔者就可以在本篇文章阐述下数据的插入过程。 监控数据的插入在这里,笔者并不会去讨论Promt
Prometheus时序数据库-数据的查询
前言在之前的博客里,笔者详细阐述了Prometheus数据的插入过程。但我们最常见的打交道的是数据的查询。Prometheus提供了强大的Promql来满足我们千变万化的查询需求。在这篇文章里面,笔者
解Bug之路-主从切换"未成功"?
前言数据库主从切换是个非常有意思的话题。能够稳定的处理主从切换是保证业务连续性的必要条件。今天笔者就来讲讲主从切换过程中一个小小的问题。 故障场景最近线上进行主从切换,大部分应用都切过去了,但是某些应
MySQL 死锁套路:一次诡异的批量插入死锁问题分析
线上最近出现了批量insert的死锁,百思不得解。死锁记录如下:```2018-10-26T11:04:41.759589Z 8530809 [Note] InnoDB: (1) TRANSACTI
MySQL 死锁套路:唯一索引下批量插入顺序不一致
死锁的本质是资源竞争,批量插入如果顺序不一致很容易导致死锁,我们来分析一下这个情况。为了方便演示,把批量插入改写为了多条 insert。先来做几个小实验,简化的表结构如下:```CREATE TABL
MySQL 死锁套路:再来看一例走不同索引更新的例子
前面有文章介绍了利用调试MySQL源码的方式来调试锁相关的信息,这里利用这个工具来解决一个比较简单的问题,线上的表字段较多,这里简单成为了一个表:```CREATE TABLE `t3` ( `id