龙空技术网

从一则性能优化案例来体会mysql数据库分组汇总的妙用

波波说运维 255

前言:

现时我们对“mysql中分组查询的功能是什么”大约比较注重,朋友们都想要分析一些“mysql中分组查询的功能是什么”的相关内容。那么小编同时在网络上网罗了一些有关“mysql中分组查询的功能是什么””的相关文章,希望看官们能喜欢,兄弟们一起来了解一下吧!

概述

今天主要分享一个mysql数据库性能优化的案例,这里通过一个需求实现来体验下两个方案的优劣。


需求

在公司门户系统中实现每个用户查看各自发布的文章列表(假设每个列表显示 10条文章记录)的时候,能够在文章名称后面显示该文章的留言数量。

这里大家可以想一下自己会怎么去实现..

方案选择

方案1(90%选择):

1、通过“SELECT id,subject,url FROM t WHERE user_id=? limit 10”得到第一页的文章相关信息;

2、通过第 1 步结果集中的 10 个文章 id 循环运行十次“SELECT COUNT(*) FROM t_comment WHERE t_id=?”来得到每个文章的留言数量然后再拼接起来展现对象。


方案2:

1、通过“SELECT id,subject,url FROM t WHERE user_id=? limit 10”得到第一页的文章相关信息;

2、通过程序拼装上面得到的 10 个 文章 的 id,再通过 in 查询“SELECT t_id,count(*) FROM t_comment WHERE t_id in (?) GROUP BY t_id ” 一次得到 10 个 文章的所有回复数量,再组装两个结果集得到展现对象。

方案比较

1、从 MySQL 执行的 SQL 数量来看 ,第一种解决方案为 11(1+10=11)条 SQL 语句,第二种解决方案为 2 条 SQL 语句(1+1);

2、从应用程序与数据库交互来看,第一种为 11 次,第二种为 2 次;

3、从数据库的 IO 操作来看,简单假设每次 SQL 为 1 个 IO,第一种最少 11 次 IO,第二种小于等于 11次 IO,而且只有当数据非常离散的情况下才会需要 11 次;

4、从数据库处理的查询复杂度来看,第一种为两类很简单的查询,第二种有一条 SQL 语句有 GROUPBY 操作,比第一种解决方案增加了排序分组操作;

5、从应用程序结果集处理来看,第一种11 次结果集的处理,第二中2次结果集的处理,但是第二种解决方案中第二次结果处理数量是第一次的 10 倍;

6、从应用程序数据处理来看,第二种比第一种多了一个拼装 t_id的过程。

性能消耗分析

1、由于 MySQL 对客户端每次提交的 SQL 不管是相同还是不同,都需要进行完全解析,这个动作主要消耗的资源是数据库主机的 CPU,那么这里第一种方案和第二种方案消耗 CPU 的比例是 11:2。SQL 语句的解析动作在整个 SQL 语句执行过程中的整体消耗的 CPU 比例是较多的;

2、应用程序与数据库交互所消耗的资源基本上都在网络方面,同样也是 11:2;

3、数据库 IO 操作资源消耗为小于或者等于 1:1;

4、第二种解决方案需要比第一种多消耗内存资源进行排序分组操作,由于数据量不大,多出的消耗在语句整体消耗中占用比例会比较小,大概不会超过 20%,大家可以针对性测试;

5、结果集处理次数也为 11:2,但是第二中解决方案第二次处理数量较大,整体来说在结果集方面两次的性能消耗区别不大;

6、应用程序数据处理方面所多出的这个t_id 的拼装所消耗的资源是非常小的,甚至比应用程序与 MySQL 做一次简单的交互所消耗的资源还要少。

总结

从整体资源消耗来看,第二中方案会远远优于第一种解决方案。觉得有用的朋友多帮忙转发哦!后面会分享更多devops和DBA方面的内容,感兴趣的朋友可以关注下~

标签: #mysql中分组查询的功能是什么