分享一则性能优化案例--分组汇总的妙用
概述
今天主要分享一个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方面的内容,感兴趣的朋友可以关注下~