VB.net 2010 视频教程 VB.net 2010 视频教程 python基础视频教程
SQL Server 2008 视频教程 c#入门经典教程 Visual Basic从门到精通视频教程
当前位置:
首页 > 数据库 > sql数据库 >

  • sql语句大全之数据库优化案例——————某知

写在前面

　　记得在自己学习数据库知识的时候特别喜欢看案例，因为优化的手段是容易掌握的，但是整体的优化思想是很难学会的。这也是为什么自己特别喜欢看案例，今天也分享自己做的优化案例。

　　之前分享过OA系统、HIS系统，今天我们来一个最常见的ERP，ERP系统各行各业都在用，不同行业也有不同的特点，博主在做研发的时候还自己写过ERP也算是比较熟悉了。

　　不管是本文分享的零售类，还是鞋服门店、家居、汽车、地产等等，也不管是某友、某碟，ERP有一个共同的特点，单据流程长，业务复杂，热点表明显，数据量大，涉及众多系统接口，各种大数据的统计报表....传统行业又缺乏DBA精心管理。

　　慢是普遍的！

　　最近一直很忙，博客产出也少的可怜，今天整理了一下自己做过优化或各种方案的客户已经超过千家，涉及各行各业，今天分享的案例算是在这些客户中比较典型的了！没有什么高大上都是常见的问题！在之前的博客中都有过提及，那么本篇我们就结合之前的技术点来看看这个案例。学习优化手段的看官们可以参见我的优化系列：
 

SQL SERVER全面优化-------Expert for SQL Server 诊断系列

 

--------------博客地址---------------------------------------------------------------------------------------

Expert 诊断优化系列 http://www.cnblogs.com/double-K/

 

 

废话不多说，直接开整-----------------------------------------------------------------------------------------

 

用户现象

　　系统慢！保存个单据要好几分钟，很多操作都超时，尤其到下午4点左右各种超时，收款什么的都收不了，

　　查个报表一个小时，下班了还没查完，经常因为系统慢而加班，

　　业务部门已经怨声载道，这个事情已经上报公司高层IT部分压力非常大！

系统环境

　　首先我们来看一下这个系统配置及现状，为什么说这个客户经典？往下看就知道了...

　　

　　先来看看系统配置 ：

　　

　　

 

　　　服务器的配置是：8路 24 core 做了超线程 384个逻辑CPU，内存1T，磁盘全闪

　　　

 　　　　SQL用了2012版本，补丁已经最新，而且服务器配置全部能够识别

　　　　没错。相当牛逼得配置！

　　

 　　　　

　　

　　数据库的大小在1.2个T

 

　　咋一看也许数据量太大了，导致性能的问题！可又一想这么强力的服务器也不至于那么慢呀，难道是代码的问题？难道需要分库分表？

数据库指标

　　那么我们再看一下数据库的一些表象：

　　每秒请求数量：

　　

　　用户连接数：

　　

 

 

　　语句执行情况：

　　

　　

　　

 

 

　　等待情况：

　　

 

　　

 

　　等待时间：

　　

 

 　　CPU指标：

　　

 

　　内存一些指标：

　　

 

　　

 

 

　　磁盘队列：

　　

 

 

 -------------------还很多指标就不一一展示了------------------

 

 　　看到这些基本的指标，除了慢你能看出什么？问题出在哪里？怎么样快速解决？能有一个优化的步骤呈现在眼前么？

 

分析

　　系统是真的很慢，慢语句数量很多系统阻塞也很严重，确实和客户反映的慢可以吻合。那为什么这么慢？什么原因导致的？

　　我总结一般性能慢常和6大因素有关：

  1. 　　业务压力
  2. 　　硬件
  3. 　　环境
  4. 　　代码
  5. 　　数据库内部运行因素
  6. 　　架构

 

　奉上一幅草图

　　

　　系统压力：访问压力（也是我们常说的并发）其实并不大，用户连接数也没想像的那么多

　　硬件：在内存和磁盘IO确实存在压力

　　环境 ：服务器和数据库版本什么的没什么问题，具体配置一会儿再看。

　　代码 ：最不想分析代码，我们留到最后

　　数据库内部运行因素：从各种指标来分析，系统语句等待时间太长，导致语句完成慢，而等待主要有两部分：

  1. 　硬件资源确实有压力
  2. 　语句之前的阻塞太严重了，＂ＬＣＫ＿Ｍ＿＂，而且等待时间过长，竟然平均达到几百秒

　　再分析...这么强的硬件，并不大的访问压力，竟然造成瓶颈？语句写的烂？程序实现的不好？缺索引？环境配置不对？

　　下面我们来看看....

 

优化阶段一（常规优化）

　　很多时候系统慢要究其原因，难道上线时候就这么慢？那不可能，厂商根本无法交付的！那么问题来了，什么时候开始慢的？对系统做过哪些调整？

　　简单的调研开始...

　　我靠！！！厂商完全不配合，工程师对系统及其不熟悉，一问三不知，最近做什么改动也说不清，用户也不知道。厂商给的结论：继续加硬件....更强的IO....数据分离减小数据量！

　　协调厂商完全协调不动，基本没戏了！

　　既然是数据库问题，那我们就数据库下手吧！从一名数据库从业人员来说，看到这样的系统一定要先解决大面积等待问题！个人经验来看很多系统大面积等待解决系统会有个很大的提升和改善！

　　配合一些常规的调优手段阶段一开始了,主要给系统大面积创建影响高开销大的索引，调整系统参数，优化tempDB等....具体不细说了，前面系列文章中都有！

 

　　预期：

　　一般系统上面一轮优化会有明显的改善，我认为这一轮以后系统会明显变快，语句运行环境合适，索引什么的合理资源消耗自然就少，内存和IO压力也会有所减少。

　　结果：

　　系统内存，IO压力趋于平稳，慢语句数量有所减少，但依然很多，阻塞依然存在，超过2分钟的语句依然很多。

　　

　　优化前

　　

 

　　优化后

　　

 

 

　　优化前

　　

　　优化后

　　

 

　　

优化阶段二（针对语句）

 　　再次分析解决大面积语句阻塞的系统，发现现在的情况，主要有如下几个：

  1. 内存某些时候还是存在波动，但整体IO 内存已经不是瓶颈。
  2. 系统中有SLEEPING的程序阻塞时间长
  3. 部分功能语句依然慢，消耗的资源很高。

　　再次对系统调研：

  1. 执行的慢语句是什么业务，是业务功能？还是报表？还是接口？
  2. 系统中频繁且较慢的语句。
  3. 系统中阻塞的操作是什么。　　

　　

　　调研后，我遇到了最常见也是最大的问题： 语句慢由于程序！在HIS的优化案例中就是因为程序大量使用自定义函数，我们没法改，我们巧妙的绕过。那么这次我们如何绕过？

 　　

　　一：报表

　　分析中发现程序系统中消耗最多资源的主要是报表。

　　报表通过一系列复杂的查询插入到物理临时表，啥叫物理临时表？ 就是非#temp 而是真真正正的插入到表中，用完在delete！

　　插入在删除，中间还有跟业务表关联操作，导致报表也会阻塞业务！

　　插入删除的数据量是多少？ 你们猜一下？？

　　千万级别....

　　

　　二：接口

　　接口程序中频繁调用业务数据并发更新频繁....导致业务受阻...

 

　　三：问题代码

　　代码的问题主要有两个：

　　1.代码较复杂，需要细致优化。

　　2.程序中存在连接泄露，简单理解成程序报错后事务不能有效处理，导致事务未提交阻塞系统

　　

 

　　针对第一部分报表，语句更是复杂至极...这东西不是短期就可以优化的，考虑分出去

　　针对第二部分接口，修改接口视图，包括写法优化、添加索引、调用频率等；

　　针对第三部分业务语句进行细致优化，查询提示，计划向导、重编译等等手段...

　　

　　

优化阶段三（报表分离）

　　经过前两个阶段的优化一般系都会明显好转，只剩报表没有处理，和一部分高消耗的频繁接口查询，这部分我们采用报表分离的方式去解决。

　　这里面我们遇到一个问题，报表要写物理表！用2012 自带的AlwaysOn是没有办法实现的（辅助节点只能读）

　　

　　使用发布订阅，又不能同时满足数据安全和业务连续的要求，客户又不满意。

　　

　　我们想到是否可以把写入物理表变成写入#temp 临时表？ 软件厂商给出的结论是：不可能....

　　

 　   那这里面我们使用了第三方的产品Moebius集群（这里真的不是广告....）

 

　　如何实现：　　

　　多活集群，几个节点数据实时一致，这样的基本知识就不普及了...集群介绍也免了

　　首先程序只有一个连接字符串没法把报表指向到辅助服务器，我们只能通过Moebius集群的前端调度引擎，定制规则把报表所使用的存储过程定点指向到第二台服务器，解决了程序不能分离的问题。

　　其次Moebius集群可以实现两个节点都可写，以满足辅助节点报表查询写入物理表的需要。

　　再次临时表的写入量太大，千万级别数据同步也是问题，这里好就好在程序中写入的物理临时表都是以“Temp_” 开头并以GUID类型结尾。我们在这里设置了只要这样的表写入不会反向同步给主节点，这样根据规则控制双向同步满足了报表的要求，最终实现了报表的分离。

　　报表快了？ 当然没有，只是分离不可能快，但是好处有两个：

  1. 　　OLAP和OLTP分离事务阻塞得到解决
  2. 　　报表服务器和业务服务器可以根据自身的业务特别进行单独的个性化设置
  3. 　　根据报表的要求我们配置高速IO的硬件

 

　　预期：

　　语句已经优化，阻塞情况也被解决，CPU、内存、磁盘压力也没有了，系统肯定快起来了！

　　结果：

　　系统快起来了！

　　

　　最终业务系统节点全天24小时的慢语句数量：（虽然还有慢语句存在，毕竟是TB级别的数据量，不影响业务运行客户完全可以接受！）

　　

 

--------------博客地址---------------------------------------------------------------------------------------

Expert 诊断优化系列 http://www.cnblogs.com/double-K/

 

 

-----------------------------------------------------------------------------------------------------

 

　　总结 ： 系统慢往往我们要全面分析，本文提供的维度：

  1. 　　业务压力
  2. 　　硬件
  3. 　　环境
  4. 　　代码
  5. 　　数据库内部运行因素
  6. 　　架构

 

　　　　往往优化真的不是简单的调一调语句，加一加硬件，全面地分析是根本解决性能问题的首要任务。

　　当然不是所有的优化都可以彻底解决，如本文中报表的改善是通过读写分离的方式实现，很多时候在ERP系统中报表的处理方式都是如此，报表如果细致优化，那需要多长时间呀！也许都是重写了。

 

　　本文的优化过程主要是：全面分析系统问题——〉宏观层面解决（环境、数据库内部运行因素、硬件压力）——〉低效代码调整——〉架构方案实现（稳定、安全、高效）——〉最终系统顺畅 无压力

 

　　当然此案例中客户的数据量已经到了可以做数据分离，分区分表的阶段，但分享本案例的原因也在于，不要认为上TB的数据一定就要分库分表的各种拆分，在性能调优的简单付出中依然可以收获更大的收益，真心希望看官们在选择分库分表付出的极大代价之前可以找专业的人全面分析一下，仔细评估你的系统到底是什么瓶颈！



相关教程