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

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

 

1      优先任务场景描述

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

 

2      实例优化过程

2.1    积分优化

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

l  优化分析

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

 

l  优化思路

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

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

即把两个表进行关联，分组统计，用到的关键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  查询页面分析如下，即查询出每个用户的各种积分统计项，如下图：

 

l  优化分析

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

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

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

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

 

l  优化思路

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

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

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

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

 

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

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

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

 

<script language="javascript" type="text/javascript">
        $(document).ready(function () {
            //异步加载，首先加载用户数据
            setTimeout(function () {
                LoadUser();
            }, 0);
            //1秒后才加载下拉框中的菜单数据
            setTimeout(function () {
                var isLoadCourse = $("#hdIsLoadCourse").val();
                var classType = $("#hdClassType").val();
                var isShowSelectAll = $("#hdIsShowSelectAll").val();
                LoadCourseClassTree(isLoadCourse, classType, isShowSelectAll);
            }, 1000);
        });
        //刷新
        function Refresh(o) {
            LoadResourceType();
        }
    </script>

 

3   优化前后对比

 

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