Mysql性能优化之几个实际优化示例

左手的ㄟ右手 2022-08-06 00:25 266阅读 0赞

数据库性能优化的文章铺天盖地，但最重要的是把这些恰当的应用到实际生产环境中，本文以真实的优化案例来详细的介绍Mysql数据库方面的先化技巧，主要的优化技术为：（1）把逐个循环的子查询变为一个查询统计语句，（2）采用异步加载，（3）尽可能减少查询时使用的表数量，本文分别详细描述。

# 1      优先任务场景描述 #

公司几个领导对某系统进行了试用，领导们对功能未提出要求，但普遍存在页面速度太慢的问题，并做了一个列表，要求必须尽快解决，我临时接到了这项任务，对其时行性能优化，这里仅列出非常典型优化效果非常明显示的任务项。

# 2      实例优化过程 #

## 2.1    积分优化 ##

l  查询页面分析如下，即查询出每个用户的各种积分统计项，如下图：

![Center][]

l  优化分析

经过查看代码（C\# MVC4）发现，先查询出一个用户列表，然后再对每个用户单独查询积分，每个用户可能需要查询5次数据库，按上图中的123条数据计算，则总共会访问数据库3+123\*5=618次，而实际上查询的表只有两个，一个是用户表有123条记录，一个是积分项表共1360条记录，如此小的数据量，查询却花费了5秒多时间，其中主要是性能点在于访问数据库过多，另外返回一个值用DataTable来传值也是一个低级错误，且代码的可读性非常差，原实现部分代码如下：

![Center 1][]

l  优化思路

此业务有两个关联的表，一个用户表：一个用户一条记录，一个是积分表：用户做了什么贡献（如上传资源等）以及所获的积分是多少，表关联如下：

![Center 2][]

而业务中需要查询出，最近三年（从今天日期计算前三年）、近五年、某一学期的积份，实质上完全可以通过一次表扫描即可完成这些统计，示例如下：

![Center 3][]

即把两个表进行关联，分组统计，用到的关键mysql语法（其它数据库也支持）见上图标注部分，其中“sum(CASE WHEN I.CreateDate BETWEEN DATE\_ADD(NOW(),INTERVAL -3 YEAR) AND NOW() THEN I.Score ELSE 0 END) asThreeYearsScore”统计技巧为，首先是按用户ID进行分组的，然后判断是否在某个时间段，用创建日期来判断，如果在则统计进来，不在则为0即不统计进来，其它积分统计项原理一样，也是统计此时间段积分，而所有统计是在一次表扫描中完成，性能肯定比多次查询高很多，尤其是当数据量很大时差异更明显示。

l  具体优化实现代码

以下仅列出储存过程DEMO代码，如下：

-- ----------------------------
 -- Procedure definition for `sp_Integral_Query`
 -- ----------------------------
 DROP PROCEDURE IF EXISTS `sp_Integral_Query`;
 DELIMITER ;;
 CREATE DEFINER=`root`@`%` PROCEDURE `sp_Integral_Query`(IN `p_yearTerm` varchar(50),IN `p_termId` int,IN `p_userName` varchar(50),IN `p_startTime` datetime,IN `p_endTime` datetime)
 BEGIN
 	/*
 	修改日期：2014-12-4
 	作者：陈鹏
 	功能描述：查询学生积分
 	调试调用示例:call sp_Integral_Query('2013学年',1,'邓敏','2014-1-1','2014-5-1');
 	*/
 	-- 定义变量，分组顺序定义----------------------------------------
 	-- 查询学年
 	DECLARE yearStartDate datetime;
 	DECLARE yearEndDate datetime;
 	-- 查询学期
 	DECLARE termStartDate datetime;
 	DECLARE termEndDate datetime;
 	-- 按日期跨度查询
 	DECLARE searchStartDate datetime;
 	DECLARE searchEndDate datetime;
 	-- 按用户模糊查询
 	DECLARE searchUserName VARCHAR(50) DEFAULT p_userName;
 	
 	DECLARE colums VARCHAR(1000) DEFAULT '';
 	DECLARE cond VARCHAR(1000) DEFAULT '';
 	DECLARE temp VARCHAR(100) DEFAULT '';
 	DECLARE curDate datetime DEFAULT Now();
 	DECLARE termNumber1 float(11,4);
 
 	-- 注入过虑
 	set searchUserName = REPLACE(searchUserName,'''','''''');
 
 	-- 判断并找出日期开始结束条件-------------------------------------
 	-- 固定需要查询的列（近三年、近五年）
 	set colums = CONCAT(colums,',sum(CASE WHEN I.CreateDate BETWEEN DATE_ADD(NOW(),INTERVAL -3 YEAR) AND NOW() THEN I.Score ELSE 0 END) as ThreeYearsScore');
 	set colums = CONCAT(colums,',sum(CASE WHEN I.CreateDate BETWEEN DATE_ADD(NOW(),INTERVAL -5 YEAR) AND NOW() THEN I.Score ELSE 0 END) as FiveYearsScore');
 	-- 学年
 	IF (p_yearTerm != '') THEN
 		select min(BeginDate),max(EndDate),AVG(TermNumber) into yearStartDate,yearEndDate,termNumber1 from Term where TermNo = p_yearTerm;
 		-- select yearStartDate,yearEndDate;
 		set colums = CONCAT(colums,',sum(CASE WHEN I.CreateDate BETWEEN ''',yearStartDate,''' AND ''',yearEndDate,''' THEN I.Score ELSE 0 END) as YearScore');
 		set colums = CONCAT(colums,',sum(CASE WHEN I.CreateDate BETWEEN ''',yearStartDate,''' AND ''',yearEndDate,''' THEN I.Score ELSE 0 END)*',termNumber1,' as YearPerformance');
 	END IF;
 	-- 学期
 	IF(p_termId > 0) THEN
 		select BeginDate,EndDate,TermNumber into termStartDate,termEndDate,termNumber1 from Term where termid = p_termId;
 		-- select termStartDate,termEndDate,termNumber1;
 		set colums = CONCAT(colums,',sum(CASE WHEN I.CreateDate BETWEEN ''',termStartDate,''' AND ''',termEndDate,''' THEN I.Score ELSE 0 END) as Score');
 		set colums = CONCAT(colums,',sum(CASE WHEN I.CreateDate BETWEEN ''',termStartDate,''' AND ''',termEndDate,''' THEN I.Score ELSE 0 END)*',termNumber1,' as TermPerformance');
 	END IF;
 	-- 时间
 	set temp = '';
 	IF(p_startTime > '1900-1-1') then 
 		set temp = CONCAT(temp,'I.CreateDate >=''',p_startTime,'''');
 	END IF;
 	if(p_endTime > '1900-1-1') then 
 		set temp = CONCAT(temp,(case when temp != '' then ' and ' else '' end),'I.CreateDate <=''',p_endTime,'''');
 	END IF;
 	IF(temp != '') THEN
 		set colums = CONCAT(colums,',sum(CASE WHEN ',temp,' THEN I.Score ELSE 0 END) as DateTimeScore');
 	END IF;
 	IF(p_userName != '') THEN
 		set cond = CONCAT(cond,'and U.FullName like ''%',p_userName,'%''');
 	END IF;
 
 	-- 接接sql语句
 	set @sqlstr = CONCAT('
 	select 
 	U.UserID,
 	U.FullName,
 	U.MembershipUserName',colums,'
 	FROM
 	userinfo U join Integral I on U.UserID = I.UserID
 	where
 	1=1 ',cond,'
 	GROUP BY
 	U.UserID');
 	-- select @sqlstr;
 
 	-- 执行并返回结果
 	PREPARE stmt FROM @sqlstr;
 	EXECUTE stmt;
 END
 ;;
 DELIMITER ;

## 2.2    用户列表查询 ##

l  查询页面分析如下，即查询出每个用户的各种积分统计项，如下图：

![Center 4][]

l  优化分析

经过跟踪测试发现上图中主要性能问题在：

（1）所属班级的目录非常耗时，因为每一条记录都要用班级ID在组织架构表中递归的查询出目录的父级，直到组织架构的根节点；

（2）查询时使用了一个视图，实际当前列表中的字段使用一个表即可获取，不用从视图（视图关联了另两张不相关的表）中查询；

（3）工具条中的所属班级下拉菜单加载也非常慢，这里也要进行优化。

l  优化思路

征对以上情况，分别制定了以下策略：

（1）      在程序启动时（IIS启动时）和application\_start方法(asp.net MVC启动事件)中，把组织架构数据全部查询出来，并逐个计算出每个节点ID的父录目，每条记录按字典存放，即ID为键，值为当前记录实体信息，并进行缓存（这部分数据结构复杂，但数据量不大），在查询时，只需要查出记录的其它信息，而所属班级则直接从缓存的字典对象中按键获取即可，另外考虑到缓存的更新问题，可以在更新组织架构时更新或清除缓存，达时实时更新的效果；

（2）      不采用视图，另外写方法直接从表中查询；

（3）      对工具条中的组织架构从上述缓存中构建，不访问学据库，另外，由于工具条不需要在页面中立即加载，可延迟两秒再加载（用户首先看到的是主列表，而工具条下拉框中的数据本身看不到，让后台慢慢加载，基本不影响用户体验）。

l  具体优化实现代码（由于实现的代码非常简单，这里不一一列出，重在优化的思路）

（1）缓存中获取班级目录节点代码：

![Center 5][]

（2）此处使用了easyUI作为UI框架构，这里采用了JS脚本方式异步调用AJAX（虽然AJAX本身也带用异步加载功能，但这里是延迟异步加载）加载数据：

# 3   优化前后对比 #

<table> 
 <tbody> 
 <tr> 
 <td style="background:rgb(217,217,217)"> 优化项 </td> 
 <td style="background:rgb(217,217,217)"> 优化分析 </td> 
 <td style="background:rgb(217,217,217)"> 优化前 </td> 
 <td style="background:rgb(217,217,217)"> 优化后 </td> 
 <td style="background:rgb(217,217,217)"> 不足之处 </td> 
 </tr> 
 <tr> 
 <td> 积分查询页面优化 </td> 
 <td> 1.重新编写查询过程，优化为只调用一次储存过程，将原来循环统计改为1条sql语句一次扫描统计 </td> 
 <td> 按123条记算，最少查询数据库3+123*2，最多查询3+123*5，大批量增加数据将等比例增加查询时间 1424MS～2984MS </td> 
 <td> 最多执行3条SQL语句且只做一次表扫描，大批量增加数据查询时间变化非常微小，各种查询几乎无差异 36MS </td> 
 <td> 没有采用有利于数据库生成高效执行计划的写法，即都是动态的拼凑而成的语句，没有参数化和采用静态语句写法 </td> 
 </tr> 
 <tr> 
 <td> 学生用户页面 </td> 
 <td> 重新编写查询过程，不采用视图表关联方式 </td> 
 <td> 4809MS(获取数据用时（其它同) </td> 
 <td> 153MS </td> 
 <td> 首次加载页面仍有延迟（非数据查询问题），可能与easyUI的不当使用有关，使用Chrome与IE测试速度差异明显，全局普遍存在这个问题 </td> 
 </tr> 
 <tr> 
 <td> 老师用户页面 </td> 
 <td> 同上 </td> 
 <td> 409MS </td> 
 <td> 51MS </td> 
 </tr> 
 <tr> 
 <td> 管理员用户页面 </td> 
 <td> 同上 </td> 
 <td> 31MS </td> 
 <td> 26MS </td> 
 </tr> 
 <tr> 
 <td> 学生用户页工具条 </td> 
 <td> 会重复加载，重复查询数据库 </td> 
 <td> 重复加载3次，查询相同数据3次 </td> 
 <td> 数据查询只加载一次，但UI仍会出现两次加载，JS异步加载 </td> 
 <td> 一个全局都存在的问题，目前只通过修改JS脚本个别页面解决，未从全局角度解决 </td> 
 </tr> 
 </tbody> 
</table>

[Center]: /images/20220806/c5d13dbaa157465ebb9ec3f68230f2d9.png
[Center 1]: /images/20220806/567694a1b9d54842861094c73d1d83b5.png
[Center 2]: /images/20220806/5714f572a6a04a9cb2151a904fa1642d.png
[Center 3]: /images/20220806/21904096902740a0821e01f225bee582.png
[Center 4]: /images/20220806/a2d940a92a604d6eb9bd9953d036b8a3.png
[Center 5]: /images/20220806/3e99c339ca4d45afb993efcdaf9566b5.png