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 != '') THENselect 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) THENselect 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 != '') THENset colums = CONCAT(colums,',sum(CASE WHEN ',temp,' THEN I.Score ELSE 0 END) as DateTimeScore');END IF;IF(p_userName != '') THENset cond = CONCAT(cond,'and U.FullName like ''%',p_userName,'%''');END IF;-- 接接sql语句set @sqlstr = CONCAT('select U.UserID,U.FullName,U.MembershipUserName',colums,'FROMuserinfo U join Integral I on U.UserID = I.UserIDwhere1=1 ',cond,'GROUP BYU.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脚本个别页面解决，未从全局角度解决