MySQL中GROUP 新葡京32450网址BY分组取前N条记录实现,MySQL在2016年仍然保持强劲的数据库流行度增长趋势

发布时间:2019-11-24  栏目:数据  评论:0 Comments

本文实例讲述了mysql使用GROUP
BY分组实现取前N条记录的方法。分享给大家供大家参考,具体如下:

有你,查询数据我什么都不怕。快快掌握!!

1.慢SQL消耗了70%~90%的数据库CPU资源;

前言

MySQL中GROUP BY分组取前N条记录实现

出大招的工具:

2.SQL语句独立于程序设计逻辑,相对于对程序源代码的优化,对SQL语句的优化在时间成本和风险上的代价都很低;

MySQL在2016年仍然保持强劲的数据库流行度增长趋势。越来越多的客户将自己的应用建立在MySQL数据库之上,甚至是从Oracle迁移到MySQL上来。但也存在部分客户在使用MySQL数据库的过程中遇到一些比如响应时间慢,CPU打满等情况。阿里云RDS专家服务团队帮助云上客户解决过很多紧急问题。现将《ApsaraDB专家诊断报告》中出现的部分常见SQL问题总结如下,供大家参考。

mysql分组,取记录

1.使用LIKE、BETWEEN、IN进行模糊查询

3.SQL语句可以有不同的写法;

常见SQL错误用法

GROUP BY之后如何取每组的前两位下面我来讲述mysql中GROUP
BY分组取前N条记录实现方法。

eg1:

下面是我总结的一些SQL常见的优化方法,每个案例都简单易懂,在开发过程中可以作为参考:

  1. LIMIT 语句

这是测试表(也不知道怎么想的,当时表名直接敲了个aa,汗~~~~):

SELECT * FROM Students

 

分页查询是最常用的场景之一,但也通常也是最容易出问题的地方。比如对于下面简单的语句,一般DBA想到的办法是在type,
name,
create_time字段上加组合索引。这样条件排序都能有效的利用到索引,性能迅速提升。

方法一:复制代码 代码如下:SELECT
a.id,a.SName,a.ClsNo,a.Score FROM aa a LEFT JOIN aa b ON a.ClsNo=b.ClsNo
AND a.Score

WHERE 姓名 like ‘张%’

不使用子查询

SELECT *

1、 LEFT JOIN aa b ON a.ClsNo=b.ClsNo AND a.Score

eg2:

 

FROM  operation

同一个班级,分数比当前学生高的记录,那就意味这成绩垫底的学生,将会产生三条记录

SELECT StudentID, Score FROM SCore WHERE Score

例:SELECT * FROM t1 WHERE id (SELECT id
FROM t2 WHERE name=’hechunyang’);
子查询在MySQL5.5版本里,内部执行计划器是这样执行的:先查外表再匹配内表,而不是先查内表t2,当外表的数据很大时,查询速度会非常慢。
在MariaDB10/MySQL5.6版本里,采用join关联方式对其进行了优化,这条SQL会自动转换为
SELECT t1.* FROM t1 JOIN t2 ON t1.id = t2.id;

WHERE  type = ‘SQLStats’

2、 group by a.id,a.SName,a.ClsNo,a.Score having
count<2a.id,a.SName,a.ClsNo,a.Score可以代表一个学生<2,那就只剩第一第二了。

BETWEEN 60 AND 80

但请注意的是:优化只针对SELECT有效,对UPDATE/DELETE子查询无效,固生产环境应避免使用子查询

AND name = ‘SlowLog’

方法二:复制代码 代码如下:SELECT *
FROM aa a WHERE 2> FROM aa WHERE ClsNo=a.ClsNo and Score>a.Score)
ORDER BY a.ClsNo,a.Score
DESC;这个我觉得是比较有意思的,取每一条记录,判断同一个班级,大于当前成绩的同学是不是小于2个人。

eg3:

 

ORDER  BY create_time

方法三:复制代码 代码如下:SELECT *
FROM aa WHERE id IN (SELECT id FROM aa WHERE ClsNo=a.ClsNo ORDER BY
Score DESC LIMIT 2) ORDER BY a.ClsNo,a.Score
DESC;这种方式进过测试不通过,ERROR 1235 : This version of MySQL doesn’t
yet support ‘LIMIT & IN/ALL/ANY/SOME subquery’
,不能在这几个子查询中使用limit。

SELECT SName AS 学生姓名,SAddress AS 地址

避免函数索引

LIMIT  1000, 10;

更多关于MySQL相关内容感兴趣的读者可查看本站专题:《MySQL日志操作技巧大全》、《MySQL事务操作技巧汇总》、《MySQL存储过程技巧大全》、《MySQL数据库锁相关技巧汇总》及《MySQL常用函数大汇总》

FROM Students

 

好吧,可能90%以上的DBA解决该问题就到此为止。但当 LIMIT 子句变成 “LIMIT
1000000,10” 时,程序员仍然会抱怨:我只取10条记录为什么还是慢?

希望本文所述对大家MySQL数据库计有所帮助。

WHERE SAddress IN (‘北京’,’广州’,’上海’)

例:SELECT * FROM t WHERE YEAR(d) >=
2016;
由于MySQL不像Oracle那样支持函数索引,即使d字段有索引,也会直接全表扫描。
应改为—–>
SELECT * FROM t WHERE d >= ‘2016-01-01’;

要知道数据库也并不知道第1000000条记录从什么地方开始,即使有索引也需要从头计算一次。出现这种性能问题,多数情形下是程序员偷懒了。在前端数据浏览翻页,或者大数据分批导出等场景下,是可以将上一页的最大值当成参数作为查询条件的。SQL重新设计如下:

2.通配符

 

SELECT  *

eg:

用IN来替换OR

FROM    operation

SELECT * FROM 数据表

 

WHERE    type = ‘SQLStats’

WHERE 编号 LIKE ’00[^8]%[AC]%

 

AND      name = ‘SlowLog’

3.SUM() 

 

AND      create_time > ‘2017-03-16 14:00:00’

AVG() 

低效查询
SELECT * FROM t WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30;
—–>
高效查询
SELECT * FROM t WHERE LOC_IN IN (10,20,30);

ORDER BY create_time limit 10;

MAX()、MIN() 

 

在新设计下查询时间基本固定,不会随着数据量的增长而发生变化。

COUNT()

LIKE双百分号无法使用到索引

  1. 隐式转换

eg1:

SELECT * FROM t WHERE name LIKE
‘%de%’;
—–>
SELECT * FROM t WHERE name LIKE ‘de%’;
目前只有MySQL5.7支持全文索引(支持中文)

SQL语句中查询变量和字段定义类型不匹配是另一个常见的错误。比如下面的语句:

SELECT SUM(Score)  AS  学号为23的学生总分

 

mysql> explain extended SELECT *

FROM  Score 

读取适当的记录LIMIT
M,N

> FROM  my_balance b

WHERE  StudentID =23

 

> WHERE  b.bpn = 14000000123

eg2:

 

>      AND b.isverified IS NULL ;

SELECT AVG(SCore) AS 及格平均成绩

SELECT *
FROM t WHERE 1;
—–>
SELECT * FROM t WHERE 1 LIMIT 10;

mysql> show warnings;

FROM Score

 

| Warning | 1739 | Cannot use ref access on index ‘bpn’ due to type or
collation conversion on field ‘bpn’

WHERE Score >=60

避免数据类型不一致

其中字段bpn的定义为varchar(20),MySQL的策略是将字符串转换为数字之后再比较。函数作用于表字段,索引失效。

eg3:

 

上述情况可能是应用程序框架自动填入的参数,而不是程序员的原意。现在应用框架很多很繁杂,使用方便的同时也小心它可能给自己挖坑。

SELECT AVG(SCore) AS 平均成绩, MAX (Score) AS 最高分,

 

  1. 关联更新、删除

    MIN (Score) AS 最低分

SELECT *
FROM t WHERE id = ’19’;
—–>
SELECT * FROM t WHERE id = 19;

虽然MySQL5.6引入了物化特性,但需要特别注意它目前仅仅针对查询语句的优化。对于更新或删除需要手工重写成JOIN。

FROM Score

 

比如下面UPDATE语句,MySQL实际执行的是循环/嵌套子查询(DEPENDENT
SUBQUERY),其执行时间可想而知。

WHERE Score >=60

分组统计可以禁止排序

UPDATE operation o

eg4:

 

SET    status = ‘applying’

SELECT COUNT (*)  AS 及格人数

 

WHERE  o.id IN (SELECT id

FROM Score

SELECT goods_id,count(*) FROM t GROUP BY
goods_id;
默认情况下,MySQL对所有GROUP BY
col1,col2…的字段进行排序。如果查询包括GROUP
BY,想要避免排序结果的消耗,则可以指定ORDER BY NULL禁止排序。
—–>
SELECT goods_id,count(*) FROM t GROUP BY goods_id ORDER BY
NULL;

FROM  (SELECT o.id,

WHERE Score>=60

 

o.status

4.分组查询用法

避免随机取记录

FROM  operation o

SELECT …… FROM  <表名> 

 

WHERE  o.group = 123

WHERE  ……

 

AND o.status NOT IN ( ‘done’ )

GROUP BY ……

 

ORDER  BY o.parent,

eg:

SELECT *
FROM t1 WHERE 1=1 ORDER BY RAND() LIMIT 4;
MySQL不支持函数索引,会导致全表扫描
—–>
SELECT * FROM t1 WHERE id >= CEIL(RAND()*1000) LIMIT 4; 

o.id

SELECT COUNT(*) AS 人数, SGrade AS 年级

 

LIMIT  1) t);

FROM  Students

禁止不必要的ORDER
BY排序

执行计划:

 GROUP BY SGrade

 

+—-+——————–+——-+——-+—————+———+———+——-+——+—————————————————–+

eg2:

 

| id | select_type        | table | type  | possible_keys | key    |
key_len | ref  | rows | Extra                                         
    |

SELECT CourseID, AVG(Score) AS 课程平均成绩

SELECT count(1) FROM user u LEFT JOIN
user_info i ON u.id = i.user_id WHERE 1 = 1 ORDER BY u.create_time
DESC;
—–>
SELECT count(1) FROM user u LEFT JOIN user_info i ON u.id =
i.user_id;

+—-+——————–+——-+——-+—————+———+———+——-+——+—————————————————–+

FROM Score

 

| 1  | PRIMARY            | o    | index |              | PRIMARY | 8   
  |      | 24  | Using where; Using temporary                        |

GROUP BY CourseID

 

| 2  | DEPENDENT SUBQUERY |      |      |              |        |       
|      |      | Impossible WHERE noticed after reading const tables |

eg3:

 

| 3  | DERIVED            | o    | ref  | idx_2,idx_5  | idx_5  | 8 
    | const | 1    | Using where; Using filesort                       
|

SELECT CourseID, AVG(Score) AS 课程平均成绩

批量INSERT插入

+—-+——————–+——-+——-+—————+———+———+——-+——+—————————————————–+

FROM Score

 

重写为JOIN之后,子查询的选择模式从DEPENDENT
SUBQUERY变成DERIVED,执行速度大大加快,从7秒降低到2毫秒。

GROUP BY CourseID

 

UPDATE operation o

ORDER BY AVG(Score)

 

JOIN  (SELECT o.id,

5.多列分组

INSERT INTO t (id, name)
VALUES(1,’Bea’);
INSERT INTO t (id, name) VALUES(2,’Belle’);
INSERT INTO t (id, name) VALUES(3,’Bernice’);
—–>
INSERT INTO t (id, name) VALUES(1,’Bea’),
(2,’Belle’),(3,’Bernice’);

o.status

SELECT COUNT(*) AS 人数,SGrade AS 年级,SSex AS   性别

 

FROM  operation o

 FROM StudentS

WHERE  o.group = 123

GROUP BY SGrade,SSex

AND o.status NOT IN ( ‘done’ )

ORDER BY SGrade

ORDER  BY o.parent,

6.分组筛选

o.id

SELECT …… FROM  <表名>

LIMIT  1) t

WHERE ……

ON o.id = t.id

GROUP BY ……

SET    status = ‘applying’

HAVING……

执行计划简化为:

eg:

+—-+————-+——-+——+—————+——-+———+——-+——+—————————————————–+

SELECT COUNT(*) AS 人数,SGrade AS 年级

| id | select_type | table | type | possible_keys | key  | key_len |
ref  | rows | Extra                                              |

FROM Students

+—-+————-+——-+——+—————+——-+———+——-+——+—————————————————–+

GROUP BY SGrade

| 1  | PRIMARY    |      |      |              |      |        |      | 
    | Impossible WHERE noticed after reading const tables |

HAVING COUNT(*)>15

| 2  | DERIVED    | o    | ref  | idx_2,idx_5  | idx_5 | 8      |
const | 1    | Using where; Using filesort                        |

7.多表连接查询

+—-+————-+——-+——+—————+——-+———+——-+——+—————————————————–+

新葡京32450网址 1内连接(INNER JOIN)

  1. 混合排序

留下评论

网站地图xml地图