概述

今天主要分享一个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方面的内容，感兴趣的朋友可以关注下~