数据库性能优化的文章铺天盖地，但最重要的是把这些恰当的应用到实际生产环境中，本文以真实的优化案例来详细的介绍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脚本个别页面解决，未从全局角度解决 |
