Mysql性能优化之几个实际优化示例 左手的ㄟ右手 2022-08-06 00:25 208阅读 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本身也带用异步加载功能,但这里是延迟异步加载)加载数据: <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 优化前后对比 # <table> <tbody> <tr> <td style="background:rgb(217,217,217)"> <p><span style="font-size:14px">优化项</span></p> </td> <td style="background:rgb(217,217,217)"> <p><span style="font-size:14px">优化分析</span></p> </td> <td style="background:rgb(217,217,217)"> <p><span style="font-size:14px">优化前</span></p> </td> <td style="background:rgb(217,217,217)"> <p><span style="font-size:14px">优化后</span></p> </td> <td style="background:rgb(217,217,217)"> <p><span style="font-size:14px">不足之处</span></p> </td> </tr> <tr> <td> <p><span style="font-size:14px">积分查询页面优化</span></p> </td> <td> <p><span style="font-family:Calibri; font-size:14px">1.</span>重新编写查询过程,优化为只调用一次储存过程,将原来循环统计改为<span style="font-family:Calibri">1</span>条<span style="font-family:Calibri">sql</span>语句一次扫描统计</p> </td> <td> <p><span style="font-size:14px">按<span style="font-family:Calibri">123</span>条记算,最少查询数据库<span style="font-family:Calibri">3+123*2</span>,最多查询<span style="font-family:Calibri">3+123*5</span>,大批量增加数据将等比例增加查询时间</span></p> <p><span style="font-size:14px"><span style="color:red"><span style="font-family:Calibri">1424MS</span></span><span style="color:red">~</span><span style="font-family:Calibri"><span style="color:red">2984MS</span></span></span></p> </td> <td> <p><span style="font-size:14px">最多执行<span style="font-family:Calibri">3</span>条<span style="font-family:Calibri">SQL</span>语句且只做一次表扫描,大批量增加数据查询时间变化非常微小,各种查询几乎无差异</span></p> <p><span style="font-family:Calibri; font-size:14px"><span style="color:#0b050">36MS</span></span></p> </td> <td> <p><span style="font-size:14px">没有采用有利于数据库生成高效执行计划的写法,即都是动态的拼凑而成的语句,没有参数化和采用静态语句写法</span></p> </td> </tr> <tr> <td> <p><span style="font-size:14px">学生用户页面</span></p> </td> <td> <p><span style="font-size:14px">重新编写查询过程,不采用视图表关联方式</span></p> </td> <td> <p><span style="font-size:14px"><span style="font-family:Calibri"><strong><span style="color:red">4809</span></strong><span style="color:red">MS(<span style="font-size:14px">获取数据用时(其它同)</span></span></span></span></p> </td> <td> <p><span style="font-family:Calibri; font-size:14px"><strong><span style="color:#0b050">153</span></strong><span style="color:#0b050">MS</span></span></p> </td> <td> <p><span style="font-size:14px">首次加载页面仍有延迟(非数据查询问题),可能与<span style="font-family:Calibri">easyUI</span>的不当使用有关,使用<span style="font-family:Calibri">Chrome</span>与<span style="font-family:Calibri">IE</span>测试速度差异明显,全局普遍存在这个问题</span></p> </td> </tr> <tr> <td> <p><span style="font-size:14px">老师用户页面</span></p> </td> <td> <p><span style="font-size:14px">同上</span></p> </td> <td> <p><span style="font-family:Calibri; font-size:14px"><strong><span style="color:red">409</span></strong><span style="color:red">MS</span></span></p> </td> <td> <p><span style="font-family:Calibri; font-size:14px"><strong><span style="color:#0b050">51</span></strong><span style="color:#0b050">MS</span></span></p> </td> </tr> <tr> <td> <p><span style="font-size:14px">管理员用户页面</span></p> </td> <td> <p><span style="font-size:14px">同上</span></p> </td> <td> <p><span style="color:red"><span style="font-family:Calibri; font-size:14px">31MS</span></span></p> </td> <td> <p><span style="color:#0b050"><span style="font-family:Calibri; font-size:14px">26MS</span></span></p> </td> </tr> <tr> <td> <p><span style="font-size:14px">学生用户页工具条</span></p> </td> <td> <p><span style="font-size:14px">会重复加载,重复查询数据库</span></p> </td> <td> <p><span style="font-size:14px">重复加载<span style="font-family:Calibri">3</span>次,查询相同数据<span style="font-family:Calibri">3</span>次</span></p> </td> <td> <p><span style="font-size:14px">数据查询只加载一次,但<span style="font-family:Calibri">UI</span>仍会出现两次加载,<span style="font-family:Calibri">JS</span>异步加载</span></p> </td> <td> <p><span style="font-size:14px">一个全局都存在的问题,目前只通过修改<span style="font-family:Calibri">JS</span>脚本个别页面解决,未从全局角度解决</span></p> </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
还没有评论,来说两句吧...