精华内容
下载资源
问答
  • 关于Xcode编译性能优化的研究工作总结本文为原创文章,转载注明出处,谢谢!本文链接:关于Xcode编译性能优化的研究工作总结近来(8月1–8月12)结合Xcode的官方文档和网上资料经验对Xcode的一些配置选项进行了编译...

    关于Xcode编译性能优化的研究工作总结

    本文为原创文章,转载注明出处,谢谢!本文链接:关于Xcode编译性能优化的研究工作总结

    近来(8月1–8月12)结合Xcode的官方文档和网上资料经验对Xcode的一些配置选项进行了编译优化的尝试研究,所谓优化主要从编译耗时及编译出的安装包大小进行优化。在研究分析过程中将手上的几个Demo项目进行了编译测试,有Swift项目也有Object-C项目。此外,对于不同配置的相应原理也做了较深入的挖掘分析。

    总的来说,对Xcode的Build Setting 进行配置选项的修改是最直接的编译设置。本工作总结除了从Xcode本身的配置进行优化以外,还从外部环境、Xcode插件以及外部硬件配置的编译优化进行了研究分析。


    目录


    一、编译时长优化 Swift编译优化 Find Implicit Dependencies
    二、编译时长优化 Architectures
    三、编译时长优化 Precompile Prefix Header 预编译头文件
    四、编译时长优化 Swift Compile - Code Generation Optimization Level
    五、加载RAM磁盘编译Xcode项目
    六、编译线程数和Debug Information Format
    6.1、 提高XCode编译时使用的线程数
    6.2、 将Debug Information Format改为DWARF
    七、Link-Time Optimizations 链接时优化
    八、加装SSD固态硬盘
    九、安装包大小优化 Asset Catalog Compiler - Options Optimization
    十、安装包大小优化 Flatten Compiles XIB Files
    十一、安装包大小优化 清理未被使用的图片资源LSUnusedResources
    十二、安装包大小优化 Deployment Postprocessing和Strip Linked Product
    十三、安装包大小优化 Linking->Dead Code Stripping
    十四、Injection for Xcode 高效Xcode编译调试插件
    14.1 Injection
    14.2 Limitations of Injection(局限性)


    一、编译时长优化 Swift编译优化 Find Implicit Dependencies

    对所编译项目的Scheme进行配置
    Product > Scheme > Edit Scheme > Build
    Build Opitions选项中,去掉Find Implicit Dependencies.
    这里写图片描述

    原理:
    选中Find Implicit Dependencies时,编译以下内容:
    * 项目所有的文件
    * 被修改的frameworks文件
    未选中Find Implicit Dependencies时,编译以下内容:
    * 项目中必要的文件
    * 不会重新编译frameworks文件,即时你对其中的文件做了修改

    Test:
    对不同设置下(是否选中Find Implicit Dependencies)的项目编译时间进行比较。
    注:每次编译前 进行clean操作(shift + command + k),达到消除Xcode自身增量编译带来的干扰。

    Result:
    对手头的两个demo进行了编译耗时的比较:
    这里写图片描述

    对于两个不同的项目,该配置所带来的编译优化效果并不一定都能体现。
    这里写图片描述
    对两个工程的framework文件进行对比之后发现,LoveFreshBeen的framework文件要比DXDoctor的少得多。所以应用该配置时,DXDoctor编译时所反映出来的效果会更明显。

    缺点分析:
    在这个选项(Find Implicit Dependencies)被选中的情况下,即使你只是对项目进行了很细微的改变,项目中的所有资源文件都会被重新编译一遍。也会对所有被改变的frameworks进行编译。没有选中这个选项时,只会对文件中的一些Swift文件进行编译,编译耗时会显著的下降。只是,在这种模式下,你对frameworks中的文件所进行的修改将不会进行重新编译。
    
    结论:
    视修改的项目文件的不同,对两种Scheme进行选择,择一使用以提高编译性能。
    

    参考:Swift Slow Compile Times Fix


    二、编译时长优化 Architectures

    在Build Settings中,有个Architectures配置选项。
    这里写图片描述
    Architectures
    是指定工程支持的指令集的集合,如果设置多个architecture,则生成的二进制包会包含多个指令集代码,提及会随之变大。

    Valid Architectures
    有效的指令集集合,Architectures与Valid Architectures的交集来确定最终的数据包含的指令集代码。

    Build Active Architecture Only
    指定是否只对当前连接设备所支持的指令集编译,默认Debug的时候设置为YES,Release的时候设为NO。Debug设置为YES时只编译当前的architecture版本,生成的包只包含当前连接设备的指令集代码;设置为NO时,则生成的包包含所有的指令集代码(上述的V艾力达Architecture与Architecture的交集)。所以为了更快的编译速度,Debug应设为YES,而Release应设为NO。

    注:Debug设置为YES时,如果连接的设备是arm64的(iPhone 5s,iPhone 6(plus)等),则Valid Architecture中必须包含arm64,否则编译会出错。这种模式下编译出来的版本是向下兼容的,即:编译出的armv6版本可在armv7版本上运行。
    

    参考:苹果官方“Xcode Build Setting Reference”
    关于Xcode “Build Setting”中的Architectures详解


    三、编译时长优化 Precompile Prefix Header 预编译头文件

    Build Setting > Apple LLVM 7.1 - Language
    这里写图片描述
    Xcode 6及之后版本默认不使用pch文件参与项目编译,原因有二:
    * 去掉自动导入的系统框架类库的头文件们可以提高源文件的复用性,便于迁移;
    * 一个庞大的Prefix Header会增加Build耗时。

    但对于原有项目应用了pch文件的情况,就需要对Xcode的Build Setting进行配置以使用pch。

    当Precompile Prefix Header设为NO时,头文件pch不会被预编译,而是在每个用到它导入的框架类库中编译一次。每个引用了pch内容的.m文件都要编译一次pch,这会降低项目的编译速度。

    将Precompile Prefix Header设为YES时,pch文件会被预编译,预编译后的pch会被缓存起来,从而提高编译速度。
    需要编译的pch文件在Prefix Header中注册即可。

    手动创建pch文件: xcode6中如何添加pch文件
    参考 :Xcode Precompile Prefix Header浅析
    Why isn’t ProjectName-Prefix.pch created automatically in Xcode 6?


    四、编译时长优化 Swift Compile - Code Generation Optimization Level

    Build Setting > Swift Compile - Code Generation > Optimization Level
    这里写图片描述
    Swift 编译优化选项,对手上的两个demo进行了以下测试:
    这里写图片描述

    Debug None[-Onone] Release Fast[-O] 是Xcode在Debug模式下编译Swift项目的最优选项,通过测试可以看出,在默认配置情况下和自定义情况下的编译耗时存在比较明显的差异。


    五、加载RAM磁盘编译Xcode项目

    DerivedData

    Xcode会在文件系统中集中的缓存临时信息。

    每次对Xcode iOS项目进行clean、build或者在iOS虚拟机上launch,Xcode都会在DeriveData文件夹中进行读写操作。换句话说,就是将Derived Data的读写从硬盘移动到内存中。
    DeriveData文件夹中包含了所有的build信息、debug- 和 release- built targets以及项目的索引。当遇到零散索引(odd index)问题(代码块补全工作不正常、经常性的重建索引、或者运行项目缓慢)时,它可以有效地删除衍生数据。删除这个文件夹将会导致所有Xcode上的项目信息遭到破坏。

    Step 1
    将DeriveData下的文件删除:

    rm -rf ~/Library/Developer/Xcode/DerivedData/*

    删除的这些数据,Xcode会在Build时重新写入的。

    Step 2
    在~/Library/Developer/Xcode/DerivedData.上部署安装2 GB大小的RAM磁盘。
    进到~/Library/Developer/Xcode/DerivedData.

    cd ~/Library/Developer/Xcode/DerivedData

    创建2 GB的RAM磁盘(size的计算公式 size = 需要分配的空间(M) * 1024 * 1024 / 512):

    hdid -nomount ram://4194304

    此行命令后将会输出RAM磁盘的驱动名字:/dev/diskN(N为数字)。

    初始化磁盘:

    newfs_hfs -v DerivedData /dev/rdiskN

    有以下输出:
    Initialized /dev/rdisk3 as a 2 GB case-insensitive HFS Plus volume

    安装磁盘:

    diskutil mount -mountPoint ~/Library/Developer/Xcode/DerivedData /dev/diskN

    这会在已存在的DeriveData上安装一个卷,用于隐藏旧的文件。这些文件仍会占据空间,但在移除RAM磁盘之前都无法访问。

    在重启或从Finder中弹出RAM磁盘时,磁盘中的内容将会消失。下次再创建磁盘时,Xcode将会重新构建它的索引和你的项目中间文件。

    创建虚拟磁盘后, 并不是直接占用掉所有分配的空间, 而是根据虚拟磁盘中的文件总大小来逐渐占用内存.
    
注:如果创建的虚拟磁盘已满, 会导致编译的失败. 此时清除掉Derived Data后重新编译, 就算有足够的空间也还是有可能会导致编译失败. 重启Xcode可以解决此问题.

    对手头Demo进行编译测试,由于编译本身读写内容较少,耗时较短,都在10s到20s之内,所以提速感觉不明显,在1s到2s间(10%左右),也许应用到较大的项目中会有比较好的体现。

    参考: Reduce XCode build times
    【iOS Tip】提高Xcode编译速度


    六、编译线程数和Debug Information Format

    6.1、 提高XCode编译时使用的线程数

    defaults write com.apple.Xcode PBXNumberOfParallelBuildSubtasks 8  

    其后的数字为指定的编译线程数。XCode默认使用与CPU核数相同的线程来进行编译,但由于编译过程中的IO操作往往比CPU运算要多,因此适当的提升线程数可以在一定程度上加快编译速度。


    6.2、 将Debug Information Format改为DWARF

    在工程对应Target的Build Settings中,找到Debug Information Format这一项,将Debug时的DWARF with dSYM file改为DWARF。

    这一项设置的是是否将调试信息加入到可执行文件中,改为DWARF后,如果程序崩溃,将无法输出崩溃位置对应的函数堆栈,但由于Debug模式下可以在XCode中查看调试信息,所以改为DWARF影响并不大。

    需要注意的是,将Debug Information Format改为DWARF之后,会导致在Debug窗口无法查看相关类类型的成员变量的值。当需要查看这些值时,可以将Debug Information Format改回DWARF with dSYM file,clean(必须)之后重新编译即可。

    注:6.2 的解决方案为Xcode的默认设置,进行反向设置时,编译速度改变不大;
    

    参考:To speed up the XCode compile and link speed (200%+)


    Apple LLVM 7.1 - Code Generation Link-Time Optimization
    这里写图片描述
    Link-Time Optimization执行链接时优化(LTO)。在Clang/LLVM领域,这意味着链接器获得的是LLVM字节码,而不是通常的目标文件。这些字节码在一种更抽象的层次上代表程序这里写链接内容的执行过程,允许LTO得以进行,但是坏处是,仍然需要将他们转换成机器代码,在链接时需要额外的处理时间。

    参数设为YES时,能够优化链接时间;目标文件以LLVM二进制文件格式存储,在链接期间,优化了整个程序。

    将其设为NO时,可以减少Link阶段的时间。对于Link阶段耗时较长的项目,整体编译优化体现较为明显。


    八、加装SSD固态硬盘

    固态硬盘传输速度能达到500MB/s,其中读取速度达到400-600MB每秒,写入速度达到200MB每秒。而传统硬盘读取速度极限也无法超越200MB每秒,写入速度在100MB每秒左右。如果遇到非连续的散片数据,SSD能体现出极快的读写速度。而传统机械硬盘由于磁头寻道等原因,传输速度偏慢。

    SSD加快了程序的I/O速率,读写速度比普通硬盘快,从而提升Xcode的编译速度。受限于各种硬件原因,没有进行测试。

    参考: SSD 硬盘能否明显加快编译的速度?
    固态硬盘的好处,固态硬盘与普通硬盘的区别


    九、安装包大小优化 Asset Catalog Compiler - Options Optimization

    Build Setting > Asset Catalog Compiler - Options

    这里写图片描述
    在Optimization 优化设置项有三个选项,不指定、time和Space。
    这里写图片描述

    Optimization nothing是Xcode默认的设置。
    与预想的不同,在选择Optimization time 时,编译时长并没有得到优化。
    但在Optimization space时,编译耗时基本没有波动,但编译生成的app 大小有不小程度的优化。


    十、安装包大小优化 Flatten Compiles XIB Files

    是否扁平化编译XIB文件。
    这里写图片描述
    官方解释是:指定是否在编译时剥离nib文件以优化它们的大小,设为YES时编译出来的nib文件会被压缩但是不能编辑。

    Description: Boolean value. Specifies whether to strip a nib files to
    reduce their size. The resulting nib file is more compact but is not
    editable.

    这里写图片描述

    测试app大小的同时也对编译耗时进行了测试,在两种编译模式下的编译耗时基本没有变化。

    参考:What does the “Flatten compiled xib files” project build option “do”
    官方文档 Interface Builder Compiler Build Settings


    十一、安装包大小优化 清理未被使用的图片资源LSUnusedResources

    这里写图片描述
    项目的开发过程总是会经历较长期的迭代,不断的添加功能的同时会引入大量的图片资源。需求变更、业务逻辑修改等需要移除某些功能模块时就会导致这些前期加入的图片资源问价被忽略而遗留在编译的安装包中,长此以往会使得安装包变得格外臃肿。特别是类似于手Q项目的开发,开发人员多,产品迭代频繁,开发时间紧俏,开发人员轮换等特点更有可能导致这样的后果。

    一个较为传统的清理方法时将图片资源的文件名一一复制粘贴到Xcode的全局变量查找中去查找该字符串,如果返回的结果为零,则该资源很有可能没被使用。之所以是“很有可能”,是因为在代码中,资源有时是通过字符串拼接的方式进行引用的。

    在这里提供一个github上的开源工具 LSUnusedResources ,这个工具是对github上的另一个开源工具Unused的优化改进(匹配速度、结果准确性),作者针对源码、Xib、Storyboard 和 plist 等文件,先全文搜索其中可能是引用了资源的字符串,然后用资源名和字符串做匹配,从而找出未被使用的资源,比Unused的查找速度要快得多。

    使用起来也比较简单:
    1、将工程目录路径拷贝到Folder或通过Browse浏览文件目录;
    2、在Resource指定要查找的资源类型;
    (经过本人测试,发现该工具在未指定Resource类型时所查找出来的资源不是很准确,列举出 的资源事实上是正在使用的,所以我在测试时指定查找了png类型的文件。)
    3、单击Search以查阅结果。

    注:为了避免对资源的误删操作,建议在该工具输出结果后对结果中的资源名复制并在Xcode的全局查找中进行校验。
    

    下载安装:LSUnusedResources.app.zip
    Github地址:LSUnusedResources
    参考链接:查找XCode工程中没被使用的图片资源


    十二、安装包大小优化 Deployment Postprocessing和Strip Linked Product

    Xcode中Strip Linked Product 的默认设置为YES,但是Deployment Postprocessing的默认设置为NO。在Deployment Postprocessing 是Deployment的总开关,所以在打开这个选项之前 Strip Linked Product是不起作用的。

    注:当Strip Linked Product设为YES的时候,运行app,断点不会中断,在程序中打印[NSThread callStackSymbols]也无法看到类名和方法名。而在程序崩溃时,函数调用栈中也无法看到类名和方法名。
    

    这里写图片描述

    打开这两个选项之后进行编译,编译出的安装包大小有了较大程度的优化:
    这里写图片描述

    参考:Xcode中和symbols有关的几个设置


    十三、安装包大小优化 Linking->Dead Code Stripping

    将Dead Code Stripping 设置为YES 也能够一定程度上对程序安装包进行优化,只是优化的效果一般,对于一些比较小的项目甚至没有什么优化体现,所以这里也就没有上测试数据。

    Dead Code Stripping 是对程序编译出的可执行二进制文件中没有被实际使用的代码进行Strip操作。

    对于更深层次的解读,在参考链接的文章里有详细描述。
    参考:Dead Code Stripping


    十四、Injection for Xcode 高效Xcode编译调试插件

    14.1 Injection

    github上的开源项目,Xcode插件。

    对于iOS开发者来说,XCode有个另人十分难耐的特性——编译时长的问题。也许工作的时候你能够为自己找到一个闲下来喝杯咖啡的正当的借口,然而,多次的调试编译过程足以让你喝上好多杯咖啡了。应该说,Injection是iOS开发者的福音,它在很大程度上优化了XCode的性能,提升了开发者的工作效率。

    Injection能够在app运行时动态地向Swift或者OC文件注入新代码并且即时地呈现在运行中的模拟器的app上,从而达到提高程序编译速度,提高开发效率的目的。开发者不需要重新编译重新运行整个项目,这样的优化使得编译周期从7秒缩短至1秒。从XCode的输出台来看,每次在进行代码注入之后都只会编译被注入了代码的文件。这么一听有点类似于增量编译。

    设想这样一个场景,对于一个编译启动需要10分钟的项目,如果你想对某个功能的动画效果进行微调,是否意味着你需要以至少10分钟为一个调试周期去对你的改动进行测试,而injection则能够在程序运行时动态的改动方法实现,并呈现在模拟器或真机上。

    Injection Github https://github.com/johnno1962/injectionforxcode
    或者到这里去看看他的演示:https://www.youtube.com/watch?v=uftvtmyZ8TM

    对于Injection的安装使用,可以到第一个链接里下载package。Injection团队为开发者准备了一套傻瓜式的配置流程,基本上都是单击continue就行了,然后重启你的Xcode。装成功后你会看到product > injection plugin。此时你应该已经装成功了。点击 Product >Injection Plugin > Patch Project for Injection 选项, 之后插件会在main.m 中插入两段代码。

    #ifdef DEBUG
    static char _inMainFilePath[] = __FILE__;
    static const char *_inIPAddresses[] = {"10.12.1.67", "127.0.0.1", 0};
    
    #define INJECTION_ENABLED
    #import "/tmp/injectionforxcode/BundleInjection.h"
    #endif

    这不会影响程序原有代码,如果要还原,随时可以通过点击 Revert Injection’s Changes 选项来还原。你可以开搞了。

    用一个demo做实验,将project运行起来,在运行时对你的代码进行改动,可以使用快捷键Ctrl + =快速运行。也可以在 product > injection plugin > inject and reset app。 你会发现你改动的代码所在类的左上角有一个蓝色的进度条,一秒不到的时间就能够完成注入并运行在你的app上。当然,你也能够在你改动的代码的方法里边加上一个断点,快捷键Ctrl + = ,你会发现运行时会停在你设定的breakpoint上。

    对于Swift文件injection好像还不能做到完美支持,github上有相关的解释,我还没有深入的尝试,有兴趣的童鞋可以去看看,顺便交流交流。
    injection是Xcode IDE的一个扩展,允许你去对类的一个方法实现打补丁而不需要重启app。官方的原理如下:

    It performs this by parsing the build logs of the application to
    determine how a source file was last compiled. With this it wraps the
    result of re-compiling into a bundle which is injected into the
    application using the dynamic loader. At this point there are two
    versions of a class in the app, the original and a new modified
    version from the bundle. The modified version is then “swizzled” onto
    the original class so changes take effect.

    (个人翻译)它通过解析程序的编译日志来确定最后一次编译的源文件。通过动态加载程序把重新编译的结果打包到被注入代码的app中。此时有两个版本的类应用,最初的和一个新的修改版本的包。这个修改后的版本,被“swizzled到”原始类中而生效。

    除此之外,injection插件还有一个参数调节器Tunable Parameters,对于UI开发来说是个利器。比如对颜色的确定,对字体大小的界定等等。运行app,然后对参数进行修改就能够动态的进行调试了。直观而且方便。
    这里写图片描述
    对于 Tunable Parameters的使用我还没有涉足,它的使用目前仅限于Swift项目,还需要在项目中进行一些诸如添加头部代码的配置,有兴趣的童鞋可以到这里了解:
    https://github.com/johnno1962/injectionforxcode/blob/master/documentation/tunable_parameters.md
    其实也不复杂,就是在新建一个main.m文件之后加上几行代码。
    在使用injection时,一个新的Xcode项目文件将会在原本项目的文件里生成(iOSInjectionProject或OSXInjectionProject)。这个文件是用于存放那些被injecte的项目文件的,建议将其加入到.gitignore 中,直接忽略。

    每一次的项目文件被injected,在injection项目目录里的injectionCount.txt中的数字就会增加。它可以很直观的告诉你通过injection进行了多少的文件改动。

    如果你想在真机或Appcode上进行测试:
    你需要做一些轻量级的配置:在你的main.m文件加上如下几行代码:

    #ifdef DEBUG
    static char _inMainFilePath[] = __FILE__;
    static const char *_inIPAddresses[] = {"10.12.1.67", "127.0.0.1", 0};
    
    #define INJECTION_ENABLED
    #import "/tmp/injectionforxcode/BundleInjection.h"
    #endif

    这个配置也可以通过Product > Injection Plugin > Patch Project For Injection 来进行自动配置。对于Swift文件,你需要添加一个空的main.m文件来完成配置。

    至于使用Appcode的盆友,可以上github上看看教程:
    https://github.com/johnno1962/injectionforxcode


    14.2 Limitations of Injection(局限性)

    贴github原文:

    There are limitations of course, largely centering around static
    variables, static or global functions and their Swift equivalents.
    Consider the following Objective-C code.
    这里写图片描述

    • One potential problem is when the new version of the class is
      loaded, it comes with it’s own versions of static variables such as
      sharedInstance and the once token. After injection has occurred, this
      would generate a new singleton instance.

    To prevent this, class methods with the prefix “shared” are not
    swizzled on injection to support this common idiom.

    • It can be tough to look through all of the memory of a running
      application. In order to determine the classes and instances to call
      the injected callbacks on, Injection performs a “sweep” to find all
      objects in memory. Roughly, this involves looking at an object, then
      recursively looking through objects which it refers to. For example,
      the object’s instance variables and properties.

      This process is seeded using the application’s delegate and all windows. Once all the in-memory reference are collected, Injection
      will then filter these references to ones that it has compiled and
      injected. Then sending them the messages referenced in the callbacks
      section.

      If no references are found, Injection will look through all objects that are referred to via sharedInstance. If that fails, well,
      Injection couldn’t find your instance. This is one way in which you
      may miss callbacks in your app.

    • The function dispatch_on_main does not inject, as it has been
      statically linked into the application. It does however, inject by
      proxy in the case shown via the doSomething method. dispatch_on_main
      will have been linked locally to a version in the object file being
      injected.

    injection作为Xcode的插件,还是有局限性的。
    injection的作用域主要集中在静态变量、静态或全局函数及其Swift的当量(按:Swift equivalents)。

    以下是作者贴的示例代码:
    这里写图片描述
    1)有一个潜在的问题,当类的新版本被加载,它带有自己的静态变量版本sharedInstance和once标记。发生injected后,将产生一个新的单一实例。

      To prevent this, class methods with the prefix "shared" are not swizzled on injection to support this common idiom.       
      以上这句我捉摸了很久还是没有吃透。
    

    2)它可以浏览所有的正在运行的应用程序的内存。为了确定类和实例能够调用injectied的回调,injection会执行一次“扫描”,找到在内存中的所有对象。粗略说,这涉及了一个对象,然后通过递归寻找它所指向的对象。例如对象的实例变量和内容(properties)。

    3)This process is seeded using the application’s delegate and all windows.(按:这个过程通过应用程序的代理和所有的窗口)。一旦所有在内存中的引用被收集,injection将会过滤这些它已经编译和injected的引用,。然后再将被引用信息的回调部分发送出去。

    4)如果没有找到引用注入的内容,Injection将通过sharedInstance查找所有被涉及到的对象。如果没有找到任何对象,那么,Injection将找不到你的实例。这会导致你无法在你的app中进行回调函数的调用。

    5)函数dispatch_on_main无法被injected,因为它已被静态地链接到应用程序。但是,injection可以通过代码示例里的doSomething方法进行inject。dispatch_on_main将会被链接到本地的在被injected对象文件的一个新版本中。

    以上内容参考:https://github.com/johnno1962/injectionforxcode

    对于某些童鞋的疑问:injection的编译效率与XCode自身的增量编译有什么优势?我已经在github上Issue了作者并得到了如下回复:
    这里写图片描述
    但是具体到底能够提升多少,这个有待进一步的测试。

    展开全文
  • SQL优化最干货总结 - MySQL(2020最新版)

    万次阅读 多人点赞 2020-06-29 16:55:47
    MySQL - SQL优化干货总结(吐血版),别辜负了自己的梦想,欢迎白嫖、点赞、收藏。

    小伙伴想精准查找自己想看的MySQL文章?喏 → MySQL专栏目录 | 点击这里

    前言

    BATJTMD等大厂的面试难度越来越高,但无论从大厂还是到小公司,一直未变的一个重点就是对SQL优化经验的考察。一提到数据库,先“说一说你对SQL优化的见解吧?”。SQL优化已经成为衡量程序猿优秀与否的硬性指标,甚至在各大厂招聘岗位职能上都有明码标注,如果是你,在这个问题上能吊打面试官还是会被吊打呢?

    (注:如果看着模糊,可能是你撸多了)

    目录

    前言

    SELECT语句 - 语法顺序:

    SELECT语句 - 执行顺序:

    SQL优化策略

    一、避免不走索引的场景

    二、SELECT语句其他优化

    三、增删改 DML 语句优化

    四、查询条件优化

    五、建表优化

    一张照片背后的故事(自娱角)


    有朋友疑问到,SQL优化真的有这么重要么?如下图所示,SQL优化在提升系统性能中是:(成本最低 && 优化效果最明显) 的途径。如果你的团队在SQL优化这方面搞得很优秀,对你们整个大型系统可用性方面无疑是一个质的跨越,真的能让你们老板省下不止几沓子钱。

    • 优化成本:硬件>系统配置>数据库表结构>SQL及索引。
    • 优化效果:硬件<系统配置<数据库表结构<SQL及索引。
    String result = "嗯,不错,";
    
    if ("SQL优化经验足") {
        if ("熟悉事务锁") {
            if ("并发场景处理666") {
                if ("会打王者荣耀") {
                    result += "明天入职" 
                }
            }
        }
    } else {
        result += "先回去等消息吧";
    } 
    
    Logger.info("面试官:" + result );

    别看了,上面这是一道送命题。

    好了我们言归正传,首先,对于MySQL层优化我一般遵从五个原则:

    1. 减少数据访问: 设置合理的字段类型,启用压缩,通过索引访问等减少磁盘IO
    2. 返回更少的数据: 只返回需要的字段和数据分页处理 减少磁盘io及网络io
    3. 减少交互次数: 批量DML操作,函数存储等减少数据连接次数
    4. 减少服务器CPU开销: 尽量减少数据库排序操作以及全表查询,减少cpu 内存占用
    5. 利用更多资源: 使用表分区,可以增加并行操作,更大限度利用cpu资源

    总结到SQL优化中,就三点:

    • 最大化利用索引;
    • 尽可能避免全表扫描;
    • 减少无效数据的查询;

    理解SQL优化原理 ,首先要搞清楚SQL执行顺序:

    SELECT语句 - 语法顺序:

    1. SELECT 
    2. DISTINCT <select_list>
    3. FROM <left_table>
    4. <join_type> JOIN <right_table>
    5. ON <join_condition>
    6. WHERE <where_condition>
    7. GROUP BY <group_by_list>
    8. HAVING <having_condition>
    9. ORDER BY <order_by_condition>
    10.LIMIT <limit_number>


     

    SELECT语句 - 执行顺序:

    FROM
    <表名> # 选取表,将多个表数据通过笛卡尔积变成一个表。
    ON
    <筛选条件> # 对笛卡尔积的虚表进行筛选
    JOIN <join, left join, right join...> 
    <join表> # 指定join,用于添加数据到on之后的虚表中,例如left join会将左表的剩余数据添加到虚表中
    WHERE
    <where条件> # 对上述虚表进行筛选
    GROUP BY
    <分组条件> # 分组
    <SUM()等聚合函数> # 用于having子句进行判断,在书写上这类聚合函数是写在having判断里面的
    HAVING
    <分组筛选> # 对分组后的结果进行聚合筛选
    SELECT
    <返回数据列表> # 返回的单列必须在group by子句中,聚合函数除外
    DISTINCT
    # 数据除重
    ORDER BY
    <排序条件> # 排序
    LIMIT
    <行数限制>


    SQL优化策略

    声明:以下SQL优化策略适用于数据量较大的场景下,如果数据量较小,没必要以此为准,以免画蛇添足。


    一、避免不走索引的场景

    1. 尽量避免在字段开头模糊查询,会导致数据库引擎放弃索引进行全表扫描。如下:

    SELECT * FROM t WHERE username LIKE '%陈%'

    优化方式:尽量在字段后面使用模糊查询。如下:

    SELECT * FROM t WHERE username LIKE '陈%'

    如果需求是要在前面使用模糊查询,

    • 使用MySQL内置函数INSTR(str,substr) 来匹配,作用类似于java中的indexOf(),查询字符串出现的角标位置,可参阅《MySQL模糊查询用法大全(正则、通配符、内置函数等)》
    • 使用FullText全文索引,用match against 检索
    • 数据量较大的情况,建议引用ElasticSearch、solr,亿级数据量检索速度秒级
    • 当表数据量较少(几千条儿那种),别整花里胡哨的,直接用like '%xx%'。

     

    2. 尽量避免使用in 和not in,会导致引擎走全表扫描。如下:

    SELECT * FROM t WHERE id IN (2,3)

    优化方式:如果是连续数值,可以用between代替。如下:

    SELECT * FROM t WHERE id BETWEEN 2 AND 3

    如果是子查询,可以用exists代替。详情见《MySql中如何用exists代替in》如下:

    -- 不走索引
    select * from A where A.id in (select id from B);
    -- 走索引
    select * from A where exists (select * from B where B.id = A.id);


     
    3. 尽量避免使用 or,会导致数据库引擎放弃索引进行全表扫描。如下:

    SELECT * FROM t WHERE id = 1 OR id = 3

    优化方式:可以用union代替or。如下:

    SELECT * FROM t WHERE id = 1
       UNION
    SELECT * FROM t WHERE id = 3

    4. 尽量避免进行null值的判断,会导致数据库引擎放弃索引进行全表扫描。如下:

    SELECT * FROM t WHERE score IS NULL

    优化方式:可以给字段添加默认值0,对0值进行判断。如下:

    SELECT * FROM t WHERE score = 0

     

    5.尽量避免在where条件中等号的左侧进行表达式、函数操作,会导致数据库引擎放弃索引进行全表扫描。

    可以将表达式、函数操作移动到等号右侧。如下:

    -- 全表扫描
    SELECT * FROM T WHERE score/10 = 9
    -- 走索引
    SELECT * FROM T WHERE score = 10*9

     

    6. 当数据量大时,避免使用where 1=1的条件。通常为了方便拼装查询条件,我们会默认使用该条件,数据库引擎会放弃索引进行全表扫描。如下:

    SELECT username, age, sex FROM T WHERE 1=1

    优化方式:用代码拼装sql时进行判断,没 where 条件就去掉 where,有where条件就加 and。


    7. 查询条件不能用 <> 或者 !=

    使用索引列作为条件进行查询时,需要避免使用<>或者!=等判断条件。如确实业务需要,使用到不等于符号,需要在重新评估索引建立,避免在此字段上建立索引,改由查询条件中其他索引字段代替。


    8. where条件仅包含复合索引非前置列

    如下:复合(联合)索引包含key_part1,key_part2,key_part3三列,但SQL语句没有包含索引前置列"key_part1",按照MySQL联合索引的最左匹配原则,不会走联合索引。详情参考《联合索引的使用原理》

    select col1 from table where key_part2=1 and key_part3=2


    9. 隐式类型转换造成不使用索引 

    如下SQL语句由于索引对列类型为varchar,但给定的值为数值,涉及隐式类型转换,造成不能正确走索引。 

    select col1 from table where col_varchar=123; 


    10. order by 条件要与where中条件一致,否则order by不会利用索引进行排序

    -- 不走age索引
    SELECT * FROM t order by age;
    
    -- 走age索引
    SELECT * FROM t where age > 0 order by age;

    对于上面的语句,数据库的处理顺序是:

    • 第一步:根据where条件和统计信息生成执行计划,得到数据。
    • 第二步:将得到的数据排序。当执行处理数据(order by)时,数据库会先查看第一步的执行计划,看order by 的字段是否在执行计划中利用了索引。如果是,则可以利用索引顺序而直接取得已经排好序的数据。如果不是,则重新进行排序操作。
    • 第三步:返回排序后的数据。

    当order by 中的字段出现在where条件中时,才会利用索引而不再二次排序,更准确的说,order by 中的字段在执行计划中利用了索引时,不用排序操作。

    这个结论不仅对order by有效,对其他需要排序的操作也有效。比如group by 、union 、distinct等。

    11. 正确使用hint优化语句

    MySQL中可以使用hint指定优化器在执行时选择或忽略特定的索引。一般而言,处于版本变更带来的表结构索引变化,更建议避免使用hint,而是通过Analyze table多收集统计信息。但在特定场合下,指定hint可以排除其他索引干扰而指定更优的执行计划。

    1. USE INDEX 在你查询语句中表名的后面,添加 USE INDEX 来提供希望 MySQL 去参考的索引列表,就可以让 MySQL 不再考虑其他可用的索引。例子: SELECT col1 FROM table USE INDEX (mod_time, name)...
    2. IGNORE INDEX 如果只是单纯的想让 MySQL 忽略一个或者多个索引,可以使用 IGNORE INDEX 作为 Hint。例子: SELECT col1 FROM table IGNORE INDEX (priority) ...
    3. FORCE INDEX 为强制 MySQL 使用一个特定的索引,可在查询中使用FORCE INDEX 作为Hint。例子: SELECT col1 FROM table FORCE INDEX (mod_time) ...

    在查询的时候,数据库系统会自动分析查询语句,并选择一个最合适的索引。但是很多时候,数据库系统的查询优化器并不一定总是能使用最优索引。如果我们知道如何选择索引,可以使用FORCE INDEX强制查询使用指定的索引。《MySQL中特别实用的几种SQL语句送给大家》博文建议阅读,干货

    例如:

    SELECT * FROM students FORCE INDEX (idx_class_id) WHERE class_id = 1 ORDER BY id DESC;

     

    二、SELECT语句其他优化

    1. 避免出现select *

    首先,select * 操作在任何类型数据库中都不是一个好的SQL编写习惯。

    使用select * 取出全部列,会让优化器无法完成索引覆盖扫描这类优化,会影响优化器对执行计划的选择,也会增加网络带宽消耗,更会带来额外的I/O,内存和CPU消耗。

    建议提出业务实际需要的列数,将指定列名以取代select *。具体详情见《为什么大家都说SELECT * 效率低》


    2. 避免出现不确定结果的函数

    特定针对主从复制这类业务场景。由于原理上从库复制的是主库执行的语句,使用如now()、rand()、sysdate()、current_user()等不确定结果的函数很容易导致主库与从库相应的数据不一致。另外不确定值的函数,产生的SQL语句无法利用query cache。


    3.多表关联查询时,小表在前,大表在后。

    在MySQL中,执行 from 后的表关联查询是从左往右执行的(Oracle相反),第一张表会涉及到全表扫描,所以将小表放在前面,先扫小表,扫描快效率较高,在扫描后面的大表,或许只扫描大表的前100行就符合返回条件并return了。

    例如:表1有50条数据,表2有30亿条数据;如果全表扫描表2,你品,那就先去吃个饭再说吧是吧。

    4. 使用表的别名

    当在SQL语句中连接多个表时,请使用表的别名并把别名前缀于每个列名上。这样就可以减少解析的时间并减少哪些友列名歧义引起的语法错误。


    5. 用where字句替换HAVING字句

    避免使用HAVING字句,因为HAVING只会在检索出所有记录之后才对结果集进行过滤,而where则是在聚合前刷选记录,如果能通过where字句限制记录的数目,那就能减少这方面的开销。HAVING中的条件一般用于聚合函数的过滤,除此之外,应该将条件写在where字句中。

    where和having的区别:where后面不能使用组函数

    6.调整Where字句中的连接顺序

    MySQL采用从左往右,自上而下的顺序解析where子句。根据这个原理,应将过滤数据多的条件往前放,最快速度缩小结果集。


    三、增删改 DML 语句优化

    1. 大批量插入数据

    如果同时执行大量的插入,建议使用多个值的INSERT语句(方法二)。这比使用分开INSERT语句快(方法一),一般情况下批量插入效率有几倍的差别。

    方法一:

    insert into T values(1,2); 
    
    insert into T values(1,3); 
    
    insert into T values(1,4);

    方法二:

    Insert into T values(1,2),(1,3),(1,4); 


    选择后一种方法的原因有三。 

    • 减少SQL语句解析的操作,MySQL没有类似Oracle的share pool,采用方法二,只需要解析一次就能进行数据的插入操作;
    • 在特定场景可以减少对DB连接次数
    • SQL语句较短,可以减少网络传输的IO。

    2. 适当使用commit

    适当使用commit可以释放事务占用的资源而减少消耗,commit后能释放的资源如下:

    • 事务占用的undo数据块;
    • 事务在redo log中记录的数据块; 
    • 释放事务施加的,减少锁争用影响性能。特别是在需要使用delete删除大量数据的时候,必须分解删除量并定期commit。


    3. 避免重复查询更新的数据

    针对业务中经常出现的更新行同时又希望获得改行信息的需求,MySQL并不支持PostgreSQL那样的UPDATE RETURNING语法,在MySQL中可以通过变量实现。

    例如,更新一行记录的时间戳,同时希望查询当前记录中存放的时间戳是什么,简单方法实现:

    Update t1 set time=now() where col1=1; 
    
    Select time from t1 where id =1; 

    使用变量,可以重写为以下方式: 

    Update t1 set time=now () where col1=1 and @now: = now (); 
    
    Select @now; 

    前后二者都需要两次网络来回,但使用变量避免了再次访问数据表,特别是当t1表数据量较大时,后者比前者快很多。


    4.查询优先还是更新(insert、update、delete)优先

    MySQL 还允许改变语句调度的优先级,它可以使来自多个客户端的查询更好地协作,这样单个客户端就不会由于锁定而等待很长时间。改变优先级还可以确保特定类型的查询被处理得更快。我们首先应该确定应用的类型,判断应用是以查询为主还是以更新为主的,是确保查询效率还是确保更新的效率,决定是查询优先还是更新优先。下面我们提到的改变调度策略的方法主要是针对只存在表锁的存储引擎,比如 MyISAM 、MEMROY、MERGE,对于Innodb 存储引擎,语句的执行是由获得行锁的顺序决定的。MySQL 的默认的调度策略可用总结如下:

    1)写入操作优先于读取操作。

    2)对某张数据表的写入操作某一时刻只能发生一次,写入请求按照它们到达的次序来处理。

    3)对某张数据表的多个读取操作可以同时地进行。MySQL 提供了几个语句调节符,允许你修改它的调度策略:

    • LOW_PRIORITY关键字应用于DELETE、INSERT、LOAD DATA、REPLACE和UPDATE;
    • HIGH_PRIORITY关键字应用于SELECT和INSERT语句;
    • DELAYED关键字应用于INSERT和REPLACE语句。


           如果写入操作是一个 LOW_PRIORITY(低优先级)请求,那么系统就不会认为它的优先级高于读取操作。在这种情况下,如果写入者在等待的时候,第二个读取者到达了,那么就允许第二个读取者插到写入者之前。只有在没有其它的读取者的时候,才允许写入者开始操作。这种调度修改可能存在 LOW_PRIORITY写入操作永远被阻塞的情况。

    SELECT 查询的HIGH_PRIORITY(高优先级)关键字也类似。它允许SELECT 插入正在等待的写入操作之前,即使在正常情况下写入操作的优先级更高。另外一种影响是,高优先级的 SELECT 在正常的 SELECT 语句之前执行,因为这些语句会被写入操作阻塞。如果希望所有支持LOW_PRIORITY 选项的语句都默认地按照低优先级来处理,那么 请使用--low-priority-updates 选项来启动服务器。通过使用 INSERTHIGH_PRIORITY 来把 INSERT 语句提高到正常的写入优先级,可以消除该选项对单个INSERT语句的影响。


    四、查询条件优化

    1. 对于复杂的查询,可以使用中间临时表 暂存数据;


    2. 优化group by语句

    默认情况下,MySQL 会对GROUP BY分组的所有值进行排序,如 “GROUP BY col1,col2,....;” 查询的方法如同在查询中指定 “ORDER BY col1,col2,...;” 如果显式包括一个包含相同的列的 ORDER BY子句,MySQL 可以毫不减速地对它进行优化,尽管仍然进行排序。

    因此,如果查询包括 GROUP BY 但你并不想对分组的值进行排序,你可以指定 ORDER BY NULL禁止排序。例如:

    SELECT col1, col2, COUNT(*) FROM table GROUP BY col1, col2 ORDER BY NULL ;

    3. 优化join语句

    MySQL中可以通过子查询来使用 SELECT 语句来创建一个单列的查询结果,然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的 SQL 操作,同时也可以避免事务或者表锁死,并且写起来也很容易。但是,有些情况下,子查询可以被更有效率的连接(JOIN)..替代。


    例子:假设要将所有没有订单记录的用户取出来,可以用下面这个查询完成:

    SELECT col1 FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )


    如果使用连接(JOIN).. 来完成这个查询工作,速度将会有所提升。尤其是当 salesinfo表中对 CustomerID 建有索引的话,性能将会更好,查询如下:

    SELECT col1 FROM customerinfo 
       LEFT JOIN salesinfoON customerinfo.CustomerID=salesinfo.CustomerID 
          WHERE salesinfo.CustomerID IS NULL 

    连接(JOIN).. 之所以更有效率一些,是因为 MySQL 不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。


    4. 优化union查询

    MySQL通过创建并填充临时表的方式来执行union查询。除非确实要消除重复的行,否则建议使用union all。原因在于如果没有all这个关键词,MySQL会给临时表加上distinct选项,这会导致对整个临时表的数据做唯一性校验,这样做的消耗相当高。

    高效:

    SELECT COL1, COL2, COL3 FROM TABLE WHERE COL1 = 10 
    
    UNION ALL 
    
    SELECT COL1, COL2, COL3 FROM TABLE WHERE COL3= 'TEST'; 

    低效:

    SELECT COL1, COL2, COL3 FROM TABLE WHERE COL1 = 10 
    
    UNION 
    
    SELECT COL1, COL2, COL3 FROM TABLE WHERE COL3= 'TEST';

    5.拆分复杂SQL为多个小SQL,避免大事务

    • 简单的SQL容易使用到MySQL的QUERY CACHE; 
    • 减少锁表时间特别是使用MyISAM存储引擎的表; 
    • 可以使用多核CPU。

    6. 使用truncate代替delete

    当删除全表中记录时,使用delete语句的操作会被记录到undo块中,删除记录也记录binlog,当确认需要删除全表时,会产生很大量的binlog并占用大量的undo数据块,此时既没有很好的效率也占用了大量的资源。

    使用truncate替代,不会记录可恢复的信息,数据不能被恢复。也因此使用truncate操作有其极少的资源占用与极快的时间。另外,使用truncate可以回收表的水位,使自增字段值归零。

    7. 使用合理的分页方式以提高分页效率

    使用合理的分页方式以提高分页效率 针对展现等分页需求,合适的分页方式能够提高分页的效率。

    案例1: 

    select * from t where thread_id = 10000 and deleted = 0 
       order by gmt_create asc limit 0, 15;


           上述例子通过一次性根据过滤条件取出所有字段进行排序返回。数据访问开销=索引IO+索引全部记录结果对应的表数据IO。因此,该种写法越翻到后面执行效率越差,时间越长,尤其表数据量很大的时候。

    适用场景:当中间结果集很小(10000行以下)或者查询条件复杂(指涉及多个不同查询字段或者多表连接)时适用。


    案例2: 

    select t.* from (select id from t where thread_id = 10000 and deleted = 0
       order by gmt_create asc limit 0, 15) a, t 
          where a.id = t.id; 

    上述例子必须满足t表主键是id列,且有覆盖索引secondary key:(thread_id, deleted, gmt_create)。通过先根据过滤条件利用覆盖索引取出主键id进行排序,再进行join操作取出其他字段。数据访问开销=索引IO+索引分页后结果(例子中是15行)对应的表数据IO。因此,该写法每次翻页消耗的资源和时间都基本相同,就像翻第一页一样。

    适用场景:当查询和排序字段(即where子句和order by子句涉及的字段)有对应覆盖索引时,且中间结果集很大的情况时适用。

    五、建表优化

    1. 在表中建立索引,优先考虑where、order by使用到的字段。


    2. 尽量使用数字型字段(如性别,男:1 女:2),若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。
    这是因为引擎在处理查询和连接时会 逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了。


    3. 查询数据量大的表 会造成查询缓慢。主要的原因是扫描行数过多。这个时候可以通过程序,分段分页进行查询,循环遍历,将结果合并处理进行展示。要查询100000到100050的数据,如下:

    SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY ID ASC) AS rowid,* 
       FROM infoTab)t WHERE t.rowid > 100000 AND t.rowid <= 100050

    4. 用varchar/nvarchar 代替 char/nchar

    尽可能的使用 varchar/nvarchar 代替 char/nchar ,因为首先变长字段存储空间小,可以节省存储空间,其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。
    不要以为 NULL 不需要空间,比如:char(100) 型,在字段建立时,空间就固定了, 不管是否插入值(NULL也包含在内),都是占用 100个字符的空间的,如果是varchar这样的变长字段, null 不占用空间。

     

    一张照片背后的故事(自娱角)

     

    这是由蒋玉树先生所拍摄的照片《小店》
    在四川凉山
    母亲临时有事
    让自己的女儿帮忙看店

    突然天上下起了雪
    在这个只有一面墙的小店里
    女孩儿一边搓着手
    一边欣赏着美景

     

    这些MySQL文章你可能也会喜欢:

    《MySQL中的 utf8 并不是真正的UTF-8编码 ! !》

    《MySQL中特别实用的几种SQL语句送给大家》

    《SQL 查询语句先执行 SELECT?兄弟你认真的么?》

    《有意思,原来SQL中的NULL是这么回事儿》

     

    展开全文
  • Unity优化总结

    千次阅读 多人点赞 2014-07-04 02:17:18
    我们的游戏已经在wp8、ios和android平台上线了。这是我做的第一个Unity项目,期间遇到过不少困难和挫折,但是我和小... 项目中后期,我做了一些优化工作,这里记录总结一下。 一、纹理压缩格式 非GPU支持的纹理...

        我们的游戏已经在wp8、ios和android平台上线了。这是我做的第一个Unity项目,期间遇到过不少困难和挫折,但是我和小伙伴们一路摸索,现在,游戏已经上线一段时间,并且很稳定。对于Unity,我一直在项目中学习,我会写一系列的文章记录自己的学习,欢迎看到文章的朋友多多交流。

        项目中后期,我做了一些优化工作,这里记录总结一下。

    一、纹理压缩格式

        非GPU支持的纹理格式,需要经过CPU解码;而GPU支持的纹理格式,GPU直接解码和显示,GPU的解码有很多优化,随机访问、快速寻址和并行解码等,因此效率高得多。而且,压缩过的纹理文件通常更小,比如ETC1是8:1的压缩比,文件小就意味着加载更快,更节约系统带宽。在手机上对比测试一下加载一个1MB的文件和一个8MB文件的耗时吧。

        在ios设备上,请使用PVR格式。wp8和win8设备上,DXT格式。android设备,不透明贴图选择通用支持的ETC1格式;而透明贴图,4大GPU厂商各自有自己的压缩格式,可以把贴图分成一张RGB图和一张alpha通道图,都用ETC1格式,游戏里再合成;也可以简单选择RGBA4444格式。

        实测效果:显著提高渲染表现。我第一次打wp8包在lumia 520上运行,比较卡,声音有卡顿;改纹理压缩格式为DXT1/DXT5之后,在lumia 520上运行比较流畅,声音卡顿现象也没有了。

        另外,贴图建议做成方形的,一般不要超过1024x1024。我们最初在lumia 520上偶尔有纹理丢失现象,后来我把几个2048的贴图拆成1024x1024的,问题再也没有出现过,原因没搞明白,知道的朋友朋友请不吝赐教。

    2. 限帧

        在移动设备上,Unity默认是60帧/秒,建议关掉垂直同步,把FPS限为30,进入后台时为1。限帧可以显著的减少发热和耗电。

    3. 图集/材质/Mesh合并

        我仅仅通过优化图集,就将游戏的内存占用降低了30M。而且,因为DrawCall减少了,游戏中一个比较复杂的关卡列表界面,渲染耗时减少了一半。

    4. 资源优化

        我们的战斗场景是3D的,我测试的时候发现这个3D场景渲染表现很差,打开战斗场景,在Galaxy S4上竟然只有35FPS左右!要命的是我们没有美术,美术都是外包的,外包那边的同学不懂移动平台的优化。我找了一些美术优化的文章发给他,估计他也没看懂,他改了几次,渲染表现没有任何改善。最后只能我自己上阵了,我看不懂那些种类繁多的美术资源,采用最笨的二分法,最后查到一个水花溅起的烟雾(Fog)效果是性能瓶颈。这个效果的表现力很弱,跟产品、策划和美术商量之后把这个效果关闭了。然后,比S4配置差很多的手机也能跑满60FPS了。

    5. 脚本优化

        很多时候,性能瓶颈点不在于渲染,而是脚本代码!我们要删除脚本中为空或不需要的默认方法,尽量少在Update中做事情,脚本不用时把它deactive。

        经过以上五步优化之后,在lumia 520,iphone 4和三星9100上测试,游戏都可以达到60FPS,满足了上线需求。但是,因为战斗场景元素最多,渲染表现最差,而且玩家在战斗场景中的时间最长,我后面又针对战斗做了一些优化。

    6. 资源卸载、垃圾回收

       策划的同学反馈过,战斗的时候,偶尔会卡顿。观察发现,是有规律的定时卡顿,针对加载的Assets,我们每30秒自动进行一次资源卸载(UnloadUnusedAssets),有时还会触发垃圾收集(GC.Collect)。改为进、出战场时卸载未被引用的资源,而战斗中不再定时卸载,解决了偶尔卡顿的问题。

    7. 资源预加载

        以空间换时间的方法。创建一张新卡牌或特效时,掉帧明显,改为进入战场时,预加载卡牌和技能特效资源,效果很好。

    8. 优化战斗卡牌渲染

        经过前面那些优化之后,我仍然不满足,一直在寻求继续的优化,让玩家的战斗体验可以更好。

        我们战斗时DrawCall能达到120个左右,我一直在考虑怎么降低DrawCall,3D场景占了20多个,这块没法优化了(没有美术啊%>_<%)。我就把目光盯向卡牌,战场上最多可以有20张卡牌,所以,卡牌是DrawCall的“贡献大户”。先问问策划,卡牌上是否有可以不显示或者合并的内容,策划的同学们寸步不让。吃了闭门羹,只能回来自己琢磨,后来终于想到,显示的内容我无法减少,但是我可以把所有的内容渲染到一起,这样以后绘制时只需要绘制渲染出的纹理而不需要绘制卡牌上“零散”的内容。

        卡牌上有卡像、卡框、种族、等级、名字、等级、攻/防等,贡献了5~6个DrawCall,而这些内容除了攻/防数字,别的内容在战斗时都不会改变,把他们渲到一张新纹理上,DrawCall就只有2个了:新纹理和攻/防数字!ps: 攻/防数字也可以和别的内容渲染到一起,相对游戏的帧率,数字改变并不算频繁,每次数字改变重新绘制并不会带来显著的开销。

        想明白之后,实现就很简单了。然后,分别打了wp8、ios和android包在相应设备上测试。测试结果:DrawCall降低了一半,帧数提高到了原来的2倍。而且,之前一直未较好解决的发热问题,完全解决了。在战场中打了30多分钟,lumia 520、Galaxy S4和iPhone 4s微微温,iPod Touch5没有发温。对比了一下当前正火的《刀塔传奇》,完胜:-D。

        

        从上面的对比图可以看到,渲染优化之后,虽然卡牌更多,但是FPS仍然提升了近一倍,优化的效果非常好。卡牌越多,优化的效果会越显著。

    9. 优化NGUI

        NGUI有些地方效率很低。

        1). 动画。如果有动画,NGUI可能会重建动画控件所在的整个UIPanel的DrawCall,对CPU开销很大。所以,如果有动画控件,一定要把它单独挂到一个UIPanel下。

        2). 锚点。UIAnchor每一帧就重算位置信息,so foolish!只需要初始化和窗口大小变化的时候计算就好(对于移动游戏窗口大小不会变化^_^)。

        3). 列表。NGUI列表(UIGrid+UIDragablePanel, ver2.6)如果放子item较多,会非常卡,比如你加了100个好友/有100张卡牌,不仅掉帧厉害,拖动的时候也非常卡。可以根据显示需要,创建出显示个数+x个子item,循环重复利用。通讯类app比如微信、QQ、通讯录应该都是这么优化的。

        4). 其它。可以在一些性能瓶颈点用Unity原生方式实现,代替使用NGUI。我们的主场景是可拖动的长条形地图,最开始做的同学直接用NGUI列表来做,后来我测试发现这里是严重的性能瓶颈,改用Unity SpriteRenderer实现,根据需要自己做拖动、点击(不规则地图,多边形碰撞盒),效率提升非常明显。

        以上就是我在做我们的项目时,摸索出的适合我们游戏的一些Unity优化方案。因为我们的游戏是2D的,所以可能对于3D游戏的优化,有些方案可能并不适用,也不够全面。目前有些地方,我们做的也不是很好,优化是一项长期、持续的工作,后面我还会一直进行下去。

     

        另外,我翻译了最新版的Unity官方文档:图形性能优化,从原理和方法上讲了图形优化,非常实用。

        下一篇文章预告:Unity自动打包工具,可以一键生成几十个平台/渠道的安装包。

        转载请注明出处: http://blog.csdn.net/ynnmnm/article/details/36759789。作者:夜风。

    展开全文
  • 个人年终工作总结的开头怎么 每个上班族到了年终都需要年终总结,那么大家知道个人年终工作总结的开头怎么?以下是小编为您整理的“学生读《傅雷家书》有感400字”,供您参考。 个人年终工作总结的开头怎么...
  • 数据库SQL优化总结之 百万级数据库优化方案

    万次阅读 多人点赞 2016-06-23 09:43:50
    近日有空整理了一下,出来跟大家分享一下,其中有错误和不足的地方,还请大家纠正补充。 这篇文章我花费了大量的时间查找资料、修改、排版,希望大家阅读之后,感觉好的话推荐给更多的人,让更多的人看到、纠正...



    网上关于SQL优化的教程很多,但是比较杂乱。近日有空整理了一下,写出来跟大家分享一下,其中有错误和不足的地方,还请大家纠正补充。

    这篇文章我花费了大量的时间查找资料、修改、排版,希望大家阅读之后,感觉好的话推荐给更多的人,让更多的人看到、纠正以及补充。

     

    一、百万级数据库优化方案


    1.对查询进行优化,要尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。

    2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:

    select id from t where num is null

    最好不要给数据库留NULL,尽可能的使用 NOT NULL填充数据库.

    备注、描述、评论之类的可以设置为 NULL,其他的,最好不要使用NULL。

    不要以为 NULL 不需要空间,比如:char(100) 型,在字段建立时,空间就固定了, 不管是否插入值(NULL也包含在内),都是占用 100个字符的空间的,如果是varchar这样的变长字段, null 不占用空间。


    可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:

    select id from t where num = 0


    3.应尽量避免在 where 子句中使用 != 或 <> 操作符,否则将引擎放弃使用索引而进行全表扫描。

    4.应尽量避免在 where 子句中使用 or 来连接条件,如果一个字段有索引,一个字段没有索引,将导致引擎放弃使用索引而进行全表扫描,如:

    select id from t where num=10 or Name = 'admin'

    可以这样查询:

    select id from t where num = 10
    union all
    select id from t where Name = 'admin'


    5.in 和 not in 也要慎用,否则会导致全表扫描,如:

    select id from t where num in(1,2,3)

    对于连续的数值,能用 between 就不要用 in 了

    select id from t where num between 1 and 3

    很多时候用 exists 代替 in 是一个好的选择:

    select num from a where num in(select num from b)

    用下面的语句替换:

    select num from a where exists(select 1 from b where num=a.num)

     

    6.下面的查询也将导致全表扫描:

    select id from t where name like%abc%

    若要提高效率,可以考虑全文检索。

    7.如果在 where 子句中使用参数,也会导致全表扫描。因为SQL只有在运行时才会解析局部变量,但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然 而,如果在编译时建立访问计划,变量的值还是未知的,因而无法作为索引选择的输入项。如下面语句将进行全表扫描:

    select id from t where num = @num

    可以改为强制查询使用索引:

    select id from t with(index(索引名)) where num = @num

    .应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。如:

    select id from t where num/2 = 100

    应改为:

    select id from t where num = 100*2


    9.应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。如:

    select id from t where substring(name,1,3) = ’abc’       -–name以abc开头的id
    select id from t where datediff(day,createdate,’2005-11-30′) = 0    -–‘2005-11-30’    --生成的id

    应改为:

    select id from t where name like 'abc%'
    select id from t where createdate >= '2005-11-30' and createdate < '2005-12-1'


    10.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。

    11.在使用索引字段作为条件时,如果该索引是复合索引,那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引,否则该索引将不会被使用,并且应尽可能的让字段顺序与索引顺序相一致。

    12.不要写一些没有意义的查询,如需要生成一个空表结构:

    select col1,col2 into #t from t where 1=0

    这类代码不会返回任何结果集,但是会消耗系统资源的,应改成这样:
    create table #t(…)

    13.Update 语句,如果只更改1、2个字段,不要Update全部字段,否则频繁调用会引起明显的性能消耗,同时带来大量日志。

    14.对于多张大数据量(这里几百条就算大了)的表JOIN,要先分页再JOIN,否则逻辑读会很高,性能很差。

    15.select count(*) from table;这样不带任何条件的count会引起全表扫描,并且没有任何业务意义,是一定要杜绝的。


    16.索引并不是越多越好,索引固然可以提高相应的 select 的效率,但同时也降低了 insert 及 update 的效率,因为 insert 或 update 时有可能会重建索引,所以怎样建索引需要慎重考虑,视具体情况而定。一个表的索引数最好不要超过6个,若太多则应考虑一些不常使用到的列上建的索引是否有 必要。

    17.应尽可能的避免更新 clustered 索引数据列,因为 clustered 索引数据列的顺序就是表记录的物理存储顺序,一旦该列值改变将导致整个表记录的顺序的调整,会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列,那么需要考虑是否应将该索引建为 clustered 索引。

    18.尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。这是因为引擎在处理查询和连 接时会逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了。

    19.尽可能的使用 varchar/nvarchar 代替 char/nchar ,因为首先变长字段存储空间小,可以节省存储空间,其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。

    20.任何地方都不要使用 select * from t ,用具体的字段列表代替“*”,不要返回用不到的任何字段

    21.尽量使用表变量来代替临时表。如果表变量包含大量数据,请注意索引非常有限(只有主键索引)。

    22. 避免频繁创建和删除临时表,以减少系统表资源的消耗。临时表并不是不可使用,适当地使用它们可以使某些例程更有效,例如,当需要重复引用大型表或常用表中的某个数据集时。但是,对于一次性事件, 最好使用导出表。

    23.在新建临时表时,如果一次性插入数据量很大,那么可以使用 select into 代替 create table,避免造成大量 log ,以提高速度;如果数据量不大,为了缓和系统表的资源,应先create table,然后insert。

    24.如果使用到了临时表,在存储过程的最后务必将所有的临时表显式删除,先 truncate table ,然后 drop table ,这样可以避免系统表的较长时间锁定。

    25.尽量避免使用游标,因为游标的效率较差,如果游标操作的数据超过1万行,那么就应该考虑改写。

    26.使用基于游标的方法或临时表方法之前,应先寻找基于集的解决方案来解决问题,基于集的方法通常更有效。

    27.与临时表一样,游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法,尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时 间允许,基于游标的方法和基于集的方法都可以尝试一下,看哪一种方法的效果更好。

    28.在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ,在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。

    29.尽量避免大事务操作,提高系统并发能力。

    30.尽量避免向客户端返回大数据量,若数据量过大,应该考虑相应需求是否合理。

     

    实际案例分析:拆分大的 DELETE 或INSERT 语句,批量提交SQL语句
      如果你需要在一个在线的网站上去执行一个大的 DELETE 或 INSERT 查询,你需要非常小心,要避免你的操作让你的整个网站停止相应。因为这两个操作是会锁表的,表一锁住了,别的操作都进不来了。
      Apache 会有很多的子进程或线程。所以,其工作起来相当有效率,而我们的服务器也不希望有太多的子进程,线程和数据库链接,这是极大的占服务器资源的事情,尤其是内存。
      如果你把你的表锁上一段时间,比如30秒钟,那么对于一个有很高访问量的站点来说,这30秒所积累的访问进程/线程,数据库链接,打开的文件数,可能不仅仅会让你的WEB服务崩溃,还可能会让你的整台服务器马上挂了。
      所以,如果你有一个大的处理,你一定把其拆分,使用 LIMIT oracle(rownum),sqlserver(top)条件是一个好的方法。下面是一个mysql示例:

    复制代码
    while(1){
    
       //每次只做1000条
    
       mysql_query(“delete from logs where log_date <= ’2012-11-01’ limit 1000”);
    
       if(mysql_affected_rows() == 0){
    
         //删除完成,退出!
         break;
      }
    
    //每次暂停一段时间,释放表让其他进程/线程访问。
    usleep(50000)
    
    }
    复制代码

     

    二、数据库访问性能优化


    特别说明:

    1、  本文只是面对数据库应用开发的程序员,不适合专业DBA,DBA在数据库性能优化方面需要了解更多的知识;

    2、  本文许多示例及概念是基于Oracle数据库描述,对于其它关系型数据库也可以参考,但许多观点不适合于KV数据库或内存数据库或者是基于SSD技术的数据库;

    3、  本文未深入数据库优化中最核心的执行计划分析技术。

     

    读者对像:

    开发人员:如果你是做数据库开发,那本文的内容非常适合,因为本文是从程序员的角度来谈数据库性能优化。

    架构师:如果你已经是数据库应用的架构师,那本文的知识你应该清楚90%,否则你可能是一个喜欢折腾的架构师。

    DBA(数据库管理员):大型数据库优化的知识非常复杂,本文只是从程序员的角度来谈性能优化,DBA除了需要了解这些知识外,还需要深入数据库的内部体系架构来解决问题。

     

         在网上有很多文章介绍数据库优化知识,但是大部份文章只是对某个一个方面进行说明,而对于我们程序员来说这种介绍并不能很好的掌握优化知识,因为很多介绍只是对一些特定的场景优化的,所以反而有时会产生误导或让程序员感觉不明白其中的奥妙而对数据库优化感觉很神秘。

         很多程序员总是问如何学习数据库优化,有没有好的教材之类的问题。在书店也看到了许多数据库优化的专业书籍,但是感觉更多是面向DBA或者是PL/SQL开发方面的知识,个人感觉不太适合普通程序员。而要想做到数据库优化的高手,不是花几周,几个月就能达到的,这并不是因为数据库优化有多高深,而是因为要做好优化一方面需要有非常好的技术功底,对操作系统、存储硬件网络、数据库原理等方面有比较扎实的基础知识,另一方面是需要花大量时间对特定的数据库进行实践测试与总结。

         作为一个程序员,我们也许不清楚线上正式的服务器硬件配置,我们不可能像DBA那样专业的对数据库进行各种实践测试与总结,但我们都应该非常了解我们SQL的业务逻辑,我们清楚SQL中访问表及字段的数据情况,我们其实只关心我们的SQL是否能尽快返回结果。那程序员如何利用已知的知识进行数据库优化?如何能快速定位SQL性能问题并找到正确的优化方向?

    面对这些问题,笔者总结了一些面向程序员的基本优化法则,本文将结合实例来坦述数据库开发的优化知识。

         要正确的优化SQL,我们需要快速定位能性的瓶颈点,也就是说快速找到我们SQL主要的开销在哪里?而大多数情况性能最慢的设备会是瓶颈点,如下载时网络速度可能会是瓶颈点,本地复制文件时硬盘可能会是瓶颈点,为什么这些一般的工作我们能快速确认瓶颈点呢,因为我们对这些慢速设备的性能数据有一些基本的认识,如网络带宽是2Mbps,硬盘是每分钟7200转等等。因此,为了快速找到SQL的性能瓶颈点,我们也需要了解我们计算机系统的硬件基本性能指标,下图展示的当前主流计算机性能指标数据。



    从图上可以看到基本上每种设备都有两个指标:

    延时(响应时间):表示硬件的突发处理能力;

    带宽(吞吐量):代表硬件持续处理能力。

     

    从上图可以看出,计算机系统硬件性能从高到代依次为:

    CPU——Cache(L1-L2-L3)——内存——SSD硬盘——网络——硬盘

    由于SSD硬盘还处于快速发展阶段,所以本文的内容不涉及SSD相关应用系统。

    根据数据库知识,我们可以列出每种硬件主要的工作内容:

    CPU及内存:缓存数据访问、比较、排序、事务检测、SQL解析、函数或逻辑运算;

    网络:结果数据传输、SQL请求、远程数据库访问(dblink);

    硬盘:数据访问、数据写入、日志记录、大数据量排序、大表连接。

     

    根据当前计算机硬件的基本性能指标及其在数据库中主要操作内容,可以整理出如下图所示的性能基本优化法则:



    这个优化法则归纳为5个层次:

    1、  减少数据访问(减少磁盘访问)

    2、  返回更少数据(减少网络传输或磁盘访问)

    3、  减少交互次数(减少网络传输)

    4、  减少服务器CPU开销(减少CPU及内存开销)

    5、  利用更多资源(增加资源)

     

    由于每一层优化法则都是解决其对应硬件的性能问题,所以带来的性能提升比例也不一样。传统数据库系统设计是也是尽可能对低速设备提供优化方法,因此针对低速设备问题的可优化手段也更多,优化成本也更低。我们任何一个SQL的性能优化都应该按这个规则由上到下来诊断问题并提出解决方案,而不应该首先想到的是增加资源解决问题。

    以下是每个优化法则层级对应优化效果及成本经验参考:

     

    优化法则

    性能提升效果

    优化成本

    减少数据访问

    1~1000

    返回更少数据

    1~100

    减少交互次数

    1~20

    减少服务器CPU开销

    1~5

    利用更多资源

    @~10

     

    接下来,我们针对5种优化法则列举常用的优化手段并结合实例分析。


    接下来,我们针对5种优化法则列举常用的优化手段并结合实例分析。

     

    二、oracle数据库两个基本概念

    数据块是数据库中数据在磁盘中存储的最小单位,也是一次IO访问的最小单位,一个数据块通常可以存储多条记录,数据块大小是DBA在创建数据库或表空间时指定,可指定为2K、4K、8K、16K或32K字节。下图是一个Oracle数据库典型的物理结构,一个数据库可以包括多个数据文件,一个数据文件内又包含多个数据块;



    ROWID是每条记录在数据库中的唯一标识,通过ROWID可以直接定位记录到对应的文件号及数据块位置。ROWID内容包括文件号、对像号、数据块号、记录槽号,如下图所示:



    三、数据库访问优化法则详解

    减少数据访问

    创建并使用正确的索引

    数据库索引的原理非常简单,但在复杂的表中真正能正确使用索引的人很少,即使是专业的DBA也不一定能完全做到最优。

    索引会大大增加表记录的DML(INSERT,UPDATE,DELETE)开销,正确的索引可以让性能提升1001000倍以上,不合理的索引也可能会让性能下降100倍,因此在一个表中创建什么样的索引需要平衡各种业务需求。

    索引常见问题:

    索引有哪些种类?

    常见的索引有B-TREE索引、位图索引、全文索引,位图索引一般用于数据仓库应用,全文索引由于使用较少,这里不深入介绍。B-TREE索引包括很多扩展类型,如组合索引、反向索引、函数索引等等,以下是B-TREE索引的简单介绍:

    B-TREE索引也称为平衡树索引(Balance Tree),它是一种按字段排好序的树形目录结构,主要用于提升查询性能和唯一约束支持。B-TREE索引的内容包括根节点、分支节点、叶子节点。

    叶子节点内容:索引字段内容+表记录ROWID

    根节点,分支节点内容:当一个数据块中不能放下所有索引字段数据时,就会形成树形的根节点或分支节点,根节点与分支节点保存了索引树的顺序及各层级间的引用关系。

             一个普通的BTREE索引结构示意图如下所示:




    如果我们把一个表的内容认为是一本字典,那索引就相当于字典的目录,如下图所示:




    图中是一个字典按部首+笔划数的目录,相当于给字典建了一个按部首+笔划的组合索引。

    一个表中可以建多个索引,就如一本字典可以建多个目录一样(按拼音、笔划、部首等等)。

    一个索引也可以由多个字段组成,称为组合索引,如上图就是一个按部首+笔划的组合目录。

    SQL什么条件会使用索引?

    当字段上建有索引时,通常以下情况会使用索引:

    INDEX_COLUMN = ?

    INDEX_COLUMN > ?

    INDEX_COLUMN >= ?

    INDEX_COLUMN < ?

    INDEX_COLUMN <= ?

    INDEX_COLUMN between ? and ?

    INDEX_COLUMN in (?,?,...,?)

    INDEX_COLUMN like ?||'%'(后导模糊查询)

    T1. INDEX_COLUMN=T2. COLUMN1(两个表通过索引字段关联)

     

    SQL什么条件不会使用索引?

     

     

    查询条件

    不能使用索引原因

    INDEX_COLUMN <> ?

    INDEX_COLUMN not in (?,?,...,?)

    不等于操作不能使用索引

    function(INDEX_COLUMN) = ?

    INDEX_COLUMN + 1 = ?

    INDEX_COLUMN || 'a' = ?

    经过普通运算或函数运算后的索引字段不能使用索引

    INDEX_COLUMN like '%'||?

    INDEX_COLUMN like '%'||?||'%'

    含前导模糊查询的Like语法不能使用索引

    INDEX_COLUMN is null

    B-TREE索引里不保存字段为NULL值记录,因此IS NULL不能使用索引

    NUMBER_INDEX_COLUMN='12345'

    CHAR_INDEX_COLUMN=12345

    Oracle在做数值比较时需要将两边的数据转换成同一种数据类型,如果两边数据类型不同时会对字段值隐式转换,相当于加了一层函数处理,所以不能使用索引。

    a.INDEX_COLUMN=a.COLUMN_1

    给索引查询的值应是已知数据,不能是未知字段值。

    注:

    经过函数运算字段的字段要使用可以使用函数索引,这种需求建议与DBA沟通。

    有时候我们会使用多个字段的组合索引,如果查询条件中第一个字段不能使用索引,那整个查询也不能使用索引

    如:我们company表建了一个id+name的组合索引,以下SQL是不能使用索引的

    Select * from company where name=?

    Oracle9i后引入了一种index skip scan的索引方式来解决类似的问题,但是通过index skip scan提高性能的条件比较特殊,使用不好反而性能会更差。

     

     

    我们一般在什么字段上建索引?

    这是一个非常复杂的话题,需要对业务及数据充分分析后再能得出结果。主键及外键通常都要有索引,其它需要建索引的字段应满足以下条件:

    1、字段出现在查询条件中,并且查询条件可以使用索引;

    2、语句执行频率高,一天会有几千次以上;

    3、通过字段条件可筛选的记录集很小,那数据筛选比例是多少才适合?

    这个没有固定值,需要根据表数据量来评估,以下是经验公式,可用于快速评估:

    小表(记录数小于10000行的表):筛选比例<10%

    大表:(筛选返回记录数)<(表总记录数*单条记录长度)/10000/16

          单条记录长度≈字段平均内容长度之和+字段数*2

     

    以下是一些字段是否需要建B-TREE索引的经验分类:

     

     

     

    字段类型

    常见字段名

    需要建索引的字段

    主键

    ID,PK

    外键

    PRODUCT_ID,COMPANY_ID,MEMBER_ID,ORDER_ID,TRADE_ID,PAY_ID

    有对像或身份标识意义字段

    HASH_CODE,USERNAME,IDCARD_NO,EMAIL,TEL_NO,IM_NO

    索引慎用字段,需要进行数据分布及使用场景详细评估

    日期

    GMT_CREATE,GMT_MODIFIED

    年月

    YEAR,MONTH

    状态标志

    PRODUCT_STATUS,ORDER_STATUS,IS_DELETE,VIP_FLAG

    类型

    ORDER_TYPE,IMAGE_TYPE,GENDER,CURRENCY_TYPE

    区域

    COUNTRY,PROVINCE,CITY

    操作人员

    CREATOR,AUDITOR

    数值

    LEVEL,AMOUNT,SCORE

    长字符

    ADDRESS,COMPANY_NAME,SUMMARY,SUBJECT

    不适合建索引的字段

    描述备注

    DESCRIPTION,REMARK,MEMO,DETAIL

    大字段

    FILE_CONTENT,EMAIL_CONTENT

     

     

    如何知道SQL是否使用了正确的索引?

    简单SQL可以根据索引使用语法规则判断,复杂的SQL不好办,判断SQL的响应时间是一种策略,但是这会受到数据量、主机负载及缓存等因素的影响,有时数据全在缓存里,可能全表访问的时间比索引访问时间还少。要准确知道索引是否正确使用,需要到数据库中查看SQL真实的执行计划,这个话题比较复杂,详见SQL执行计划专题介绍。

     

    索引对DML(INSERT,UPDATE,DELETE)附加的开销有多少?

    这个没有固定的比例,与每个表记录的大小及索引字段大小密切相关,以下是一个普通表测试数据,仅供参考:

    索引对于Insert性能降低56%

    索引对于Update性能降低47%

    索引对于Delete性能降低29%

    因此对于写IO压力比较大的系统,表的索引需要仔细评估必要性,另外索引也会占用一定的存储空间。

     

    1.2、只通过索引访问数据

    有些时候,我们只是访问表中的几个字段,并且字段内容较少,我们可以为这几个字段单独建立一个组合索引,这样就可以直接只通过访问索引就能得到数据,一般索引占用的磁盘空间比表小很多,所以这种方式可以大大减少磁盘IO开销。

    如:select id,name from company where type='2';

    如果这个SQL经常使用,我们可以在type,id,name上创建组合索引

    create index my_comb_index on company(type,id,name);

    有了这个组合索引后,SQL就可以直接通过my_comb_index索引返回数据,不需要访问company表。

    还是拿字典举例:有一个需求,需要查询一本汉语字典中所有汉字的个数,如果我们的字典没有目录索引,那我们只能从字典内容里一个一个字计数,最后返回结果。如果我们有一个拼音目录,那就可以只访问拼音目录的汉字进行计数。如果一本字典有1000页,拼音目录有20页,那我们的数据访问成本相当于全表访问的50分之一。

    切记,性能优化是无止境的,当性能可以满足需求时即可,不要过度优化。在实际数据库中我们不可能把每个SQL请求的字段都建在索引里,所以这种只通过索引访问数据的方法一般只用于核心应用,也就是那种对核心表访问量最高且查询字段数据量很少的查询。

    1.3、优化SQL执行计划

    SQL执行计划是关系型数据库最核心的技术之一,它表示SQL执行时的数据访问算法。由于业务需求越来越复杂,表数据量也越来越大,程序员越来越懒惰,SQL也需要支持非常复杂的业务逻辑,但SQL的性能还需要提高,因此,优秀的关系型数据库除了需要支持复杂的SQL语法及更多函数外,还需要有一套优秀的算法库来提高SQL性能。

    目前ORACLESQL执行计划的算法约300种,而且一直在增加,所以SQL执行计划是一个非常复杂的课题,一个普通DBA能掌握50种就很不错了,就算是资深DBA也不可能把每个执行计划的算法描述清楚。虽然有这么多种算法,但并不表示我们无法优化执行计划,因为我们常用的SQL执行计划算法也就十几个,如果一个程序员能把这十几个算法搞清楚,那就掌握了80%SQL执行计划调优知识。

    由于篇幅的原因,SQL执行计划需要专题介绍,在这里就不多说了。

     

    2、返回更少的数据

    2.1、数据分页处理

    一般数据分页方式有:

    2.1.1、客户端(应用程序或浏览器)分页

    将数据从应用服务器全部下载到本地应用程序或浏览器,在应用程序或浏览器内部通过本地代码进行分页处理

    优点:编码简单,减少客户端与应用服务器网络交互次数

    缺点:首次交互时间长,占用客户端内存

    适应场景:客户端与应用服务器网络延时较大,但要求后续操作流畅,如手机GPRS,超远程访问(跨国)等等。

    2.1.2、应用服务器分页

    将数据从数据库服务器全部下载到应用服务器,在应用服务器内部再进行数据筛选。以下是一个应用服务器端Java程序分页的示例:

    List list=executeQuery(“select * from employee order by id”);

    Int count= list.size();

    List subList= list.subList(10, 20);

     

    优点:编码简单,只需要一次SQL交互,总数据与分页数据差不多时性能较好。

    缺点:总数据量较多时性能较差。

    适应场景:数据库系统不支持分页处理,数据量较小并且可控。

     

    2.1.3、数据库SQL分页

    采用数据库SQL分页需要两次SQL完成

    一个SQL计算总数量

    一个SQL返回分页后的数据

    优点:性能好

    缺点:编码复杂,各种数据库语法不同,需要两次SQL交互。

     

    oracle数据库一般采用rownum来进行分页,常用分页语法有如下两种:

     

    直接通过rownum分页:

    select * from (

             select a.*,rownum rn from

                       (select * from product a where company_id=? order by status) a

             where rownum<=20)

    where rn>10;

    数据访问开销=索引IO+索引全部记录结果对应的表数据IO

     

    采用rowid分页语法

    优化原理是通过纯索引找出分页记录的ROWID,再通过ROWID回表返回数据,要求内层查询和排序字段全在索引里。

    create index myindex on product(company_id,status);

     

    select b.* from (

             select * from (

                       select a.*,rownum rn from

                                (select rowid rid,status from product a where company_id=? order by status) a

                       where rownum<=20)

             where rn>10) a, product b

    where a.rid=b.rowid;

    数据访问开销=索引IO+索引分页结果对应的表数据IO

     

    实例:

    一个公司产品有1000条记录,要分页取其中20个产品,假设访问公司索引需要50IO2条记录需要1个表数据IO

    那么按第一种ROWNUM分页写法,需要550(50+1000/2)IO,按第二种ROWID分页写法,只需要60IO(50+20/2);

     

    2.2、只返回需要的字段

    通过去除不必要的返回字段可以提高性能,例:

    调整前:select * from product where company_id=?;

    调整后:select id,name from product where company_id=?;

     

    优点:

    1、减少数据在网络上传输开销

    2、减少服务器数据处理开销

    3、减少客户端内存占用

    4、字段变更时提前发现问题,减少程序BUG

    5、如果访问的所有字段刚好在一个索引里面,则可以使用纯索引访问提高性能。

    缺点:增加编码工作量

    由于会增加一些编码工作量,所以一般需求通过开发规范来要求程序员这么做,否则等项目上线后再整改工作量更大。

    如果你的查询表中有大字段或内容较多的字段,如备注信息、文件内容等等,那在查询表时一定要注意这方面的问题,否则可能会带来严重的性能问题。如果表经常要查询并且请求大内容字段的概率很低,我们可以采用分表处理,将一个大表分拆成两个一对一的关系表,将不常用的大内容字段放在一张单独的表中。如一张存储上传文件的表:

    T_FILEID,FILE_NAME,FILE_SIZE,FILE_TYPE,FILE_CONTENT

    我们可以分拆成两张一对一的关系表:

    T_FILEID,FILE_NAME,FILE_SIZE,FILE_TYPE

    T_FILECONTENTID, FILE_CONTENT

             通过这种分拆,可以大大提少T_FILE表的单条记录及总大小,这样在查询T_FILE时性能会更好,当需要查询FILE_CONTENT字段内容时再访问T_FILECONTENT表。

     

    3、减少交互次数

    3.1batch DML

    数据库访问框架一般都提供了批量提交的接口,jdbc支持batch的提交处理方法,当你一次性要往一个表中插入1000万条数据时,如果采用普通的executeUpdate处理,那么和服务器交互次数为1000万次,按每秒钟可以向数据库服务器提交10000次估算,要完成所有工作需要1000秒。如果采用批量提交模式,1000条提交一次,那么和服务器交互次数为1万次,交互次数大大减少。采用batch操作一般不会减少很多数据库服务器的物理IO,但是会大大减少客户端与服务端的交互次数,从而减少了多次发起的网络延时开销,同时也会降低数据库的CPU开销。

     

    假设要向一个普通表插入1000万数据,每条记录大小为1K字节,表上没有任何索引,客户端与数据库服务器网络是100Mbps,以下是根据现在一般计算机能力估算的各种batch大小性能对比值:

     

     

     单位:ms

    No batch

    Batch=10

    Batch=100

    Batch=1000

    Batch=10000

    服务器事务处理时间

    0.1

    0.1

    0.1

    0.1

    0.1

    服务器IO处理时间

    0.02

    0.2

    2

    20

    200

    网络交互发起时间

    0.1

    0.1

    0.1

    0.1

    0.1

    网络数据传输时间

    0.01

    0.1

    1

    10

    100

    小计

    0.23

    0.5

    3.2

    30.2

    300.2

    平均每条记录处理时间

    0.23

    0.05

    0.032

    0.0302

    0.03002

     

     

    从上可以看出,Insert操作加大Batch可以对性能提高近8倍性能,一般根据主键的UpdateDelete操作也可能提高2-3倍性能,但不如Insert明显,因为UpdateDelete操作可能有比较大的开销在物理IO访问。以上仅是理论计算值,实际情况需要根据具体环境测量。

     

    3.2In List

    很多时候我们需要按一些ID查询数据库记录,我们可以采用一个ID一个请求发给数据库,如下所示:

    for :var in ids[] do begin

      select * from mytable where id=:var;

    end;

     

    我们也可以做一个小的优化, 如下所示,用ID INLIST的这种方式写SQL

    select * from mytable where id in(:id1,id2,...,idn);

     

    通过这样处理可以大大减少SQL请求的数量,从而提高性能。那如果有10000ID,那是不是全部放在一条SQL里处理呢?答案肯定是否定的。首先大部份数据库都会有SQL长度和IN里个数的限制,如ORACLEIN里就不允许超过1000个值

    另外当前数据库一般都是采用基于成本的优化规则,当IN数量达到一定值时有可能改变SQL执行计划,从索引访问变成全表访问,这将使性能急剧变化。随着SQLIN的里面的值个数增加,SQL的执行计划会更复杂,占用的内存将会变大,这将会增加服务器CPU及内存成本。

    评估在IN里面一次放多少个值还需要考虑应用服务器本地内存的开销,有并发访问时要计算本地数据使用周期内的并发上限,否则可能会导致内存溢出。

    综合考虑,一般IN里面的值个数超过20个以后性能基本没什么太大变化,也特别说明不要超过100,超过后可能会引起执行计划的不稳定性及增加数据库CPU及内存成本,这个需要专业DBA评估。

     

    3.3、设置Fetch Size

    当我们采用select从数据库查询数据时,数据默认并不是一条一条返回给客户端的,也不是一次全部返回客户端的,而是根据客户端fetch_size参数处理,每次只返回fetch_size条记录,当客户端游标遍历到尾部时再从服务端取数据,直到最后全部传送完成。所以如果我们要从服务端一次取大量数据时,可以加大fetch_size,这样可以减少结果数据传输的交互次数及服务器数据准备时间,提高性能。

     

    以下是jdbc测试的代码,采用本地数据库,表缓存在数据库CACHE中,因此没有网络连接及磁盘IO开销,客户端只遍历游标,不做任何处理,这样更能体现fetch参数的影响:

    String vsql ="select * from t_employee";

    PreparedStatement pstmt = conn.prepareStatement(vsql,ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);

    pstmt.setFetchSize(1000);

    ResultSet rs = pstmt.executeQuery(vsql);

    int cnt = rs.getMetaData().getColumnCount();

    Object o;

    while (rs.next()) {

        for (int i = 1; i <= cnt; i++) {

           o = rs.getObject(i);

        }

    }

     

    测试示例中的employee表有100000条记录,每条记录平均长度135字节

     

    以下是测试结果,对每种fetchsize测试5次再取平均值:

     

    fetchsize

     elapse_time(s)

    1

    20.516

    2

    11.34

    4

    6.894

    8

    4.65

    16

    3.584

    32

    2.865

    64

    2.656

    128

    2.44

    256

    2.765

    512

    3.075

    1024

    2.862

    2048

    2.722

    4096

    2.681

    8192

    2.715

     



    Oracle jdbc fetchsize默认值为10,由上测试可以看出fetchsize对性能影响还是比较大的,但是当fetchsize大于100时就基本上没有影响了。fetchsize并不会存在一个最优的固定值,因为整体性能与记录集大小及硬件平台有关。根据测试结果建议当一次性要取大量数据时这个值设置为100左右,不要小于40。注意,fetchsize不能设置太大,如果一次取出的数据大于JVM的内存会导致内存溢出,所以建议不要超过1000,太大了也没什么性能提高,反而可能会增加内存溢出的危险。

    注:图中fetchsize128以后会有一些小的波动,这并不是测试误差,而是由于resultset填充到具体对像时间不同的原因,由于resultset已经到本地内存里了,所以估计是由于CPUL1,L2 Cache命中率变化造成,由于变化不大,所以笔者也未深入分析原因。

     

    iBatisSqlMapping配置文件可以对每个SQL语句指定fetchsize大小,如下所示:

     

    <select id="getAllProduct" resultMap="HashMap" fetchSize="1000">

    select * from employee

    </select>

     

    3.4、使用存储过程

    大型数据库一般都支持存储过程,合理的利用存储过程也可以提高系统性能。如你有一个业务需要将A表的数据做一些加工然后更新到B表中,但是又不可能一条SQL完成,这时你需要如下3步操作:

    a:将A表数据全部取出到客户端;

    b:计算出要更新的数据;

    c:将计算结果更新到B表。

     

    如果采用存储过程你可以将整个业务逻辑封装在存储过程里,然后在客户端直接调用存储过程处理,这样可以减少网络交互的成本。

    当然,存储过程也并不是十全十美,存储过程有以下缺点:

    a、不可移植性,每种数据库的内部编程语法都不太相同,当你的系统需要兼容多种数据库时最好不要用存储过程。

    b、学习成本高,DBA一般都擅长写存储过程,但并不是每个程序员都能写好存储过程,除非你的团队有较多的开发人员熟悉写存储过程,否则后期系统维护会产生问题。

    c、业务逻辑多处存在,采用存储过程后也就意味着你的系统有一些业务逻辑不是在应用程序里处理,这种架构会增加一些系统维护和调试成本。

    d、存储过程和常用应用程序语言不一样,它支持的函数及语法有可能不能满足需求,有些逻辑就只能通过应用程序处理。

    e、如果存储过程中有复杂运算的话,会增加一些数据库服务端的处理成本,对于集中式数据库可能会导致系统可扩展性问题。

    f、为了提高性能,数据库会把存储过程代码编译成中间运行代码(类似于javaclass文件),所以更像静态语言。当存储过程引用的对像(表、视图等等)结构改变后,存储过程需要重新编译才能生效,在24*7高并发应用场景,一般都是在线变更结构的,所以在变更的瞬间要同时编译存储过程,这可能会导致数据库瞬间压力上升引起故障(Oracle数据库就存在这样的问题)

     

    个人观点:普通业务逻辑尽量不要使用存储过程,定时性的ETL任务或报表统计函数可以根据团队资源情况采用存储过程处理。

     

    3.5、优化业务逻辑

    要通过优化业务逻辑来提高性能是比较困难的,这需要程序员对所访问的数据及业务流程非常清楚。

    举一个案例:

    某移动公司推出优惠套参,活动对像为VIP会员并且2010123月平均话费20元以上的客户。

    那我们的检测逻辑为:

    select avg(money) as avg_money from bill where phone_no='13988888888' and date between '201001' and '201003';

    select vip_flag from member where phone_no='13988888888';

    if avg_money>20 and vip_flag=true then

    begin

      执行套参();

    end;

     

    如果我们修改业务逻辑为:

    select avg(money) as  avg_money from bill where phone_no='13988888888' and date between '201001' and '201003';

    if avg_money>20 then

    begin

      select vip_flag from member where phone_no='13988888888';

      if vip_flag=true then

      begin

        执行套参();

      end;

    end;

    通过这样可以减少一些判断vip_flag的开销,平均话费20元以下的用户就不需要再检测是否VIP了。

     

    如果程序员分析业务,VIP会员比例为1%,平均话费20元以上的用户比例为90%,那我们改成如下:

    select vip_flag from member where phone_no='13988888888';

    if vip_flag=true then

    begin

      select avg(money) as avg_money from bill where phone_no='13988888888' and date between '201001' and '201003';

      if avg_money>20 then

      begin

        执行套参();

      end;

    end;

    这样就只有1%VIP会员才会做检测平均话费,最终大大减少了SQL的交互次数。

     

    以上只是一个简单的示例,实际的业务总是比这复杂得多,所以一般只是高级程序员更容易做出优化的逻辑,但是我们需要有这样一种成本优化的意识。

     

    3.6、使用ResultSet游标处理记录

    现在大部分Java框架都是通过jdbc从数据库取出数据,然后装载到一个list里再处理,list里可能是业务Object,也可能是hashmap

    由于JVM内存一般都小于4G,所以不可能一次通过sql把大量数据装载到list里。为了完成功能,很多程序员喜欢采用分页的方法处理,如一次从数据库取1000条记录,通过多次循环搞定,保证不会引起JVM Out of memory问题。

     

    以下是实现此功能的代码示例,t_employee表有10万条记录,设置分页大小为1000

     

    d1 = Calendar.getInstance().getTime();

    vsql = "select count(*) cnt from t_employee";

    pstmt = conn.prepareStatement(vsql);

    ResultSet rs = pstmt.executeQuery();

    Integer cnt = 0;

    while (rs.next()) {

             cnt = rs.getInt("cnt");

    }

    Integer lastid=0;

    Integer pagesize=1000;

    System.out.println("cnt:" + cnt);

    String vsql = "select count(*) cnt from t_employee";

    PreparedStatement pstmt = conn.prepareStatement(vsql);

    ResultSet rs = pstmt.executeQuery();

    Integer cnt = 0;

    while (rs.next()) {

             cnt = rs.getInt("cnt");

    }

    Integer lastid = 0;

    Integer pagesize = 1000;

    System.out.println("cnt:" + cnt);

    for (int i = 0; i <= cnt / pagesize; i++) {

             vsql = "select * from (select * from t_employee where id>? order by id) where rownum<=?";

             pstmt = conn.prepareStatement(vsql);

             pstmt.setFetchSize(1000);

             pstmt.setInt(1, lastid);

             pstmt.setInt(2, pagesize);

             rs = pstmt.executeQuery();

             int col_cnt = rs.getMetaData().getColumnCount();

             Object o;

             while (rs.next()) {

                       for (int j = 1; j <= col_cnt; j++) {

                                o = rs.getObject(j);

                       }

                       lastid = rs.getInt("id");

             }

             rs.close();

             pstmt.close();

    }

     

    以上代码实际执行时间为6.516

     

    很多持久层框架为了尽量让程序员使用方便,封装了jdbc通过statement执行数据返回到resultset的细节,导致程序员会想采用分页的方式处理问题。实际上如果我们采用jdbc原始的resultset游标处理记录,在resultset循环读取的过程中处理记录,这样就可以一次从数据库取出所有记录。显著提高性能。

    这里需要注意的是,采用resultset游标处理记录时,应该将游标的打开方式设置为FORWARD_READONLY模式(ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY),否则会把结果缓存在JVM里,造成JVM Out of memory问题。

     

    代码示例:

     

    String vsql ="select * from t_employee";

    PreparedStatement pstmt = conn.prepareStatement(vsql,ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);

    pstmt.setFetchSize(100);

    ResultSet rs = pstmt.executeQuery(vsql);

    int col_cnt = rs.getMetaData().getColumnCount();

    Object o;

    while (rs.next()) {

             for (int j = 1; j <= col_cnt; j++) {

                       o = rs.getObject(j);

             }

    }

    调整后的代码实际执行时间为3.156

     

    从测试结果可以看出性能提高了1倍多,如果采用分页模式数据库每次还需发生磁盘IO的话那性能可以提高更多。

    iBatis等持久层框架考虑到会有这种需求,所以也有相应的解决方案,在iBatis里我们不能采用queryForList的方法,而应用该采用queryWithRowHandler加回调事件的方式处理,如下所示:

     

    MyRowHandler myrh=new MyRowHandler();

    sqlmap.queryWithRowHandler("getAllEmployee", myrh);

     

    class MyRowHandler implements RowHandler {

        public void handleRow(Object o) {

           //todo something

        }

    }

     

    iBatisqueryWithRowHandler很好的封装了resultset遍历的事件处理,效果及性能与resultset遍历一样,也不会产生JVM内存溢出。

     

    4、减少数据库服务器CPU运算

    4.1、使用绑定变量

    绑定变量是指SQL中对变化的值采用变量参数的形式提交,而不是在SQL中直接拼写对应的值。

    非绑定变量写法:Select * from employee where id=1234567

    绑定变量写法:

    Select * from employee where id=?

    Preparestatement.setInt(1,1234567)

     

    JavaPreparestatement就是为处理绑定变量提供的对像,绑定变量有以下优点:

    1、防止SQL注入

    2、提高SQL可读性

    3、提高SQL解析性能,不使用绑定变更我们一般称为硬解析,使用绑定变量我们称为软解析。

    1和第2点很好理解,做编码的人应该都清楚,这里不详细说明。关于第3点,到底能提高多少性能呢,下面举一个例子说明:

     

    假设有这个这样的一个数据库主机:

    24CPU 

    100块磁盘,每个磁盘支持IOPS160

    业务应用的SQL如下:

    select * from table where pk=?

    这个SQL平均4IO3个索引IO+1个数据IO

    IO缓存命中率75%(索引全在内存中,数据需要访问磁盘)

    SQL硬解析CPU消耗:1ms  (常用经验值)

    SQL软解析CPU消耗:0.02ms(常用经验值)

     

    假设CPU每核性能是线性增长,访问内存Cache中的IO时间忽略,要求计算系统对如上应用采用硬解析与采用软解析支持的每秒最大并发数:

     

     

    是否使用绑定变量

    CPU支持最大并发数

    磁盘IO支持最大并发数

    不使用

    2*4*1000=8000

    100*160=16000

    使用

    2*4*1000/0.02=400000

    100*160=16000

     

     

    从以上计算可以看出,不使用绑定变量的系统当并发达到8000时会在CPU上产生瓶颈,当使用绑定变量的系统当并行达到16000时会在磁盘IO上产生瓶颈。所以如果你的系统CPU有瓶颈时请先检查是否存在大量的硬解析操作。

     

    使用绑定变量为何会提高SQL解析性能,这个需要从数据库SQL执行原理说明,一条SQLOracle数据库中的执行过程如下图所示:

     



    当一条SQL发送给数据库服务器后,系统首先会将SQL字符串进行hash运算,得到hash值后再从服务器内存里的SQL缓存区中进行检索,如果有相同的SQL字符,并且确认是同一逻辑的SQL语句,则从共享池缓存中取出SQL对应的执行计划,根据执行计划读取数据并返回结果给客户端。

    如果在共享池中未发现相同的SQL则根据SQL逻辑生成一条新的执行计划并保存在SQL缓存区中,然后根据执行计划读取数据并返回结果给客户端。

    为了更快的检索SQL是否在缓存区中,首先进行的是SQL字符串hash值对比,如果未找到则认为没有缓存,如果存在再进行下一步的准确对比,所以要命中SQL缓存区应保证SQL字符是完全一致,中间有大小写或空格都会认为是不同的SQL

    如果我们不采用绑定变量,采用字符串拼接的模式生成SQL,那么每条SQL都会产生执行计划,这样会导致共享池耗尽,缓存命中率也很低。

     

    一些不使用绑定变量的场景:

    a、数据仓库应用,这种应用一般并发不高,但是每个SQL执行时间很长,SQL解析的时间相比SQL执行时间比较小,绑定变量对性能提高不明显。数据仓库一般都是内部分析应用,所以也不太会发生SQL注入的安全问题。

    b、数据分布不均匀的特殊逻辑,如产品表,记录有1亿,有一产品状态字段,上面建有索引,有审核中,审核通过,审核未通过3种状态,其中审核通过9500万,审核中1万,审核不通过499万。

    要做这样一个查询:

    select count(*) from product where status=?

    采用绑定变量的话,那么只会有一个执行计划,如果走索引访问,那么对于审核中查询很快,对审核通过和审核不通过会很慢;如果不走索引,那么对于审核中与审核通过和审核不通过时间基本一样;

    对于这种情况应该不使用绑定变量,而直接采用字符拼接的方式生成SQL,这样可以为每个SQL生成不同的执行计划,如下所示。

    select count(*) from product where status='approved'; //不使用索引

    select count(*) from product where status='tbd'; //不使用索引

    select count(*) from product where status='auditing';//使用索引

     

    4.2、合理使用排序

    Oracle的排序算法一直在优化,但是总体时间复杂度约等于nLog(n)。普通OLTP系统排序操作一般都是在内存里进行的,对于数据库来说是一种CPU的消耗,曾在PC机做过测试,单核普通CPU1秒钟可以完成100万条记录的全内存排序操作,所以说由于现在CPU的性能增强,对于普通的几十条或上百条记录排序对系统的影响也不会很大。但是当你的记录集增加到上万条以上时,你需要注意是否一定要这么做了,大记录集排序不仅增加了CPU开销,而且可能会由于内存不足发生硬盘排序的现象,当发生硬盘排序时性能会急剧下降,这种需求需要与DBA沟通再决定,取决于你的需求和数据,所以只有你自己最清楚,而不要被别人说排序很慢就吓倒。

    以下列出了可能会发生排序操作的SQL语法:

    Order by

    Group by

    Distinct

    Exists子查询

    Not Exists子查询

    In子查询

    Not In子查询

    Union(并集),Union All也是一种并集操作,但是不会发生排序,如果你确认两个数据集不需要执行去除重复数据操作,那请使用Union All 代替Union

    Minus(差集)

    Intersect(交集)

    Create Index

    Merge Join,这是一种两个表连接的内部算法,执行时会把两个表先排序好再连接,应用于两个大表连接的操作。如果你的两个表连接的条件都是等值运算,那可以采用Hash Join来提高性能,因为Hash Join使用Hash 运算来代替排序的操作。具体原理及设置参考SQL执行计划优化专题。

     

    4.3、减少比较操作

    我们SQL的业务逻辑经常会包含一些比较操作,如a=ba<b之类的操作,对于这些比较操作数据库都体现得很好,但是如果有以下操作,我们需要保持警惕:

    Like模糊查询,如下所示:

    a like ‘%abc%’

     

    Like模糊查询对于数据库来说不是很擅长,特别是你需要模糊检查的记录有上万条以上时,性能比较糟糕,这种情况一般可以采用专用Search或者采用全文索引方案来提高性能。

    不能使用索引定位的大量In List,如下所示:

    a in (:1,:2,:3,…,:n)   ----n>20

    如果这里的a字段不能通过索引比较,那数据库会将字段与in里面的每个值都进行比较运算,如果记录数有上万以上,会明显感觉到SQLCPU开销加大,这个情况有两种解决方式:

    a、  in列表里面的数据放入一张中间小表,采用两个表Hash Join关联的方式处理;

    b、  采用str2varList方法将字段串列表转换一个临时表处理,关于str2varList方法可以在网上直接查询,这里不详细介绍。

     

    以上两种解决方案都需要与中间表Hash Join的方式才能提高性能,如果采用了Nested Loop的连接方式性能会更差。

    如果发现我们的系统IO没问题但是CPU负载很高,就有可能是上面的原因,这种情况不太常见,如果遇到了最好能和DBA沟通并确认准确的原因。

     

    4.4、大量复杂运算在客户端处理

    什么是复杂运算,一般我认为是一秒钟CPU只能做10万次以内的运算。如含小数的对数及指数运算、三角函数、3DESBASE64数据加密算法等等。

    如果有大量这类函数运算,尽量放在客户端处理,一般CPU每秒中也只能处理1-10万次这样的函数运算,放在数据库内不利于高并发处理。

     

    5、利用更多的资源

    5.1、客户端多进程并行访问

    多进程并行访问是指在客户端创建多个进程(线程),每个进程建立一个与数据库的连接,然后同时向数据库提交访问请求。当数据库主机资源有空闲时,我们可以采用客户端多进程并行访问的方法来提高性能。如果数据库主机已经很忙时,采用多进程并行访问性能不会提高,反而可能会更慢。所以使用这种方式最好与DBA或系统管理员进行沟通后再决定是否采用。

     

    例如:

    我们有10000个产品ID,现在需要根据ID取出产品的详细信息,如果单线程访问,按每个IO5ms计算,忽略主机CPU运算及网络传输时间,我们需要50s才能完成任务。如果采用5个并行访问,每个进程访问2000ID,那么10s就有可能完成任务。

    那是不是并行数越多越好呢,开1000个并行是否只要50ms就搞定,答案肯定是否定的,当并行数超过服务器主机资源的上限时性能就不会再提高,如果再增加反而会增加主机的进程间调度成本和进程冲突机率。

     

    以下是一些如何设置并行数的基本建议:

    如果瓶颈在服务器主机,但是主机还有空闲资源,那么最大并行数取主机CPU核数和主机提供数据服务的磁盘数两个参数中的最小值,同时要保证主机有资源做其它任务。

    如果瓶颈在客户端处理,但是客户端还有空闲资源,那建议不要增加SQL的并行,而是用一个进程取回数据后在客户端起多个进程处理即可,进程数根据客户端CPU核数计算。

    如果瓶颈在客户端网络,那建议做数据压缩或者增加多个客户端,采用map reduce的架构处理。

    如果瓶颈在服务器网络,那需要增加服务器的网络带宽或者在服务端将数据压缩后再处理了。

     

    5.2、数据库并行处理

    数据库并行处理是指客户端一条SQL的请求,数据库内部自动分解成多个进程并行处理,如下图所示:



    并不是所有的SQL都可以使用并行处理,一般只有对表或索引进行全部访问时才可以使用并行。数据库表默认是不打开并行访问,所以需要指定SQL并行的提示,如下所示:

    select /*+parallel(a,4)*/ * from employee;

     

    并行的优点:

    使用多进程处理,充分利用数据库主机资源(CPU,IO),提高性能。

    并行的缺点:

    1、单个会话占用大量资源,影响其它会话,所以只适合在主机负载低时期使用;

    2、只能采用直接IO访问,不能利用缓存数据,所以执行前会触发将脏缓存数据写入磁盘操作。

     

    注:

    1、并行处理在OLTP类系统中慎用,使用不当会导致一个会话把主机资源全部占用,而正常事务得不到及时响应,所以一般只是用于数据仓库平台。

    2、一般对于百万级记录以下的小表采用并行访问性能并不能提高,反而可能会让性能更差。


    参考文章:http://www.cnblogs.com/easypass/archive/2010/12/08/1900127.html

          http://www.cnblogs.com/yunfeifei/p/3850440.html



    展开全文
  • 如何优化MySQL千万级大表,我了6000字的解读

    万次阅读 多人点赞 2019-10-21 20:03:03
    千万级大表如何优化,这是一个很有技术含量的问题,通常我们的直觉思维都会跳转到拆分或者数据分区,在此我想做一些补充和梳理,想和大家做一些这方面的经验总结,也欢迎大家提出建议。 从一开始脑海里开始也是...
  • cache性能优化总结

    万次阅读 2018-01-12 19:00:43
    cache性能优化总结
  • 关于NoSQL的思考:为什么我们要优化存储的性能
  • 5分钟学会试用期工作总结

    万次阅读 多人点赞 2018-07-11 13:50:17
    感谢您在百忙之中抽出珍贵的时间看我的试用期工作总结。 时光飞逝,转眼间已经过了三个月的时光。意味着我的试用期生涯已接近尾声,首先特别感谢 公司领导和同事们给予我的鼓励和帮助,让我可以很快的融入虫洞...
  • Spark优化总结

    千次阅读 2016-08-06 10:54:05
    本篇文章是关于我在学习Spark过程中遇到的一些问题及总结,分为Spark优化、RDD join问题、遇到的问题、总结、参考资料几个部分。 一:Spark优化 1、设置序列化器为KryoSerializer  Spark默认使用的是Java序列化...
  • oracle性能优化总结

    千次阅读 2016-04-06 18:37:56
    Oracle性能优化 一.数据库优化的方向 1.程序设计(这点最重要,如果程序本身设计有问题,再怎么进行下面的优化都是徒劳的。) 2.操作系统优化 3.硬件优化 4.数据库优化 5.SQL语句优化 二.硬件...
  • 数据库优化之读写分离

    万次阅读 2016-11-02 15:19:43
    Mycat的前期工作需要 Java 环境,ubuntu下直接apt-get就可以下载,网上教程很多,下载好java之后再配置一下jdk环境变量 Whereis jvm 查看jvm路径 然后 vi /etc/profile export JAVA_HOME=/usr/lib...
  • Oracle数据库优化总结优化方法

    千次阅读 2018-09-10 15:58:15
    (1)SELECT子句中避免使用' * ':ORACLE在解析的过程中,会将' * '依次转换成所有的列名, 这个工作是通过查询数据字典完成的,这意味着将耗费更多的时间。 (2)sql语句用大写的:因为oracle总是先解析sql语句,把...
  • android优化总结

    万次阅读 2018-06-27 10:15:08
    1. 冷启动优化 也就是第一次启动app,而非app退到后台,再进入。在app冷启动的时候,如果在application做大量的初始化,就会导致启动速度慢,可能导致在启动的瞬间会长时间白屏。设置启动窗口主题的方式来替换系统...
  • 效率优化总结

    千次阅读 2007-11-30 09:06:00
    最近一程序,跟效率优化打上了交道,把其中的体会下来,供大家讨论分享,我想效率优化工作可以分为如下几个步骤:(1)查找影响效率的瓶颈之处:定位的方法当然是使用时间函数,一般精确的使用GetTickCount就...
  • Oracle数据库优化总结

    万次阅读 多人点赞 2016-09-25 15:36:08
    总结和记录了Oracle数据库SQL优化的方法和创建索引的建议。
  • SEO优化是一个长期的工作,不要通过非正规的方法去优化你的站,这样查出来容易被K。还有你每天坚持的花点时间按照上面的这些流程,去对自己的网站进行一个检测,我相信你会发现你哪些地方做的不足,哪些地方做的好,...
  • Oracle SQL优化 总结

    万次阅读 多人点赞 2011-11-24 17:05:17
    之前的blog中零零散散的整理了一些优化相关的内容,找起来比较麻烦,所以总结一下,查看的时候方便一点。这篇BLog只看SQL 优化的相关的注意事项,数据库优化部分以后有空在整理。 SQL 的优化主要涉及几个方面: (1...
  • sql优化面试总结

    万次阅读 2018-04-02 11:42:24
    1、选择最有效率的表名顺序(只在基于规则的优化器中有效): ORACLE 的解析器按照从右到左的顺序处理FROM子句中的表名,FROM子句中在最后的表(基础表 driving table)将被最先处理,在FROM子句中包含多个表的情况下...
  • Android性能优化总结

    千次阅读 2014-10-17 09:24:06
    任何不慎的操作,都有可能对性能造成比较大的影响,要知道程序的性能是可以累加的,多处的性能低下,会影响整体的性能,其后果可能也是多方面的,本文总结了目前工作中,所需要知道的大部分性能优化点,一部分个人...
  • 操作系统使得计算机硬件对程序员透明,即程序员在代码时不用考虑机器到底如何工作。但是了解CPU内高速缓存的作用能帮助程序员设计出更高效的代码。下面简单介绍一下CPU内的高速缓存和程序优化方法 CPU的高速缓存 ...
  • 帆软报表优化总结

    千次阅读 2019-01-29 10:13:28
    date: 2019/1/24 11:17:16 前言 之前在做报表时没有注意那么多细节,上次给甲方公司做他们总公司汇总,明细报表,这种报表动辄几十...因此找到了帆软官方中对于报表性能优化的多个文章,进行仔细查看和对SQL优化处...
  • 前言成为一名优秀的Android开发,需要一份完备的知识体系,在这里,让我们一起成长为自己所想的那样~。内存优化可以说是性能优化中最重要的优化点之一,可以说,如果你没有掌...
  • 项目优化总结

    千次阅读 2018-09-27 09:20:14
    最近一个多月算是比较忙的,但是具体忙了什么却不太好说清楚,主要是因为做的事比较繁琐还不太容易量化,说简单点,就是和组内其他几个同事一起接手一个算是开发好的项目,并进行一定的优化。 说到这个项目...
  • 优化程序性能总结

    千次阅读 2015-10-23 14:34:57
    性能优化有三个层次: 系统层次 算法层次 代码层次 系统层次关注系统的控制流程和数据流程,优化主要考虑如何减少消息传递的个数;如何使系统的负载更加均衡;如何充分利用硬件的性能和设施;如何减少系统额外...
  • C++代码优化方法总结

    千次阅读 2008-11-07 21:09:00
    C++代码优化方法总结 优化是一个非常大的主题,本文并不是去深入探讨性能分析理论,算法的效率,况且我也没有这个能力。我只是想把一些可以简单的应用到你的C++代码中的优化技术总结在这里,这样,当你遇到几种不同...
  • MYSQL多层面优化总结

    2016-06-15 20:21:19
    下面就这个问题谈一下我在平时工作中的一些总结. 着重从多个层面来总结这块的优化技巧. 硬件层面 使用高速的存储设备, ssd 或者 Fusion io卡 考虑使用磁盘阵列 操作系统层面 尽可能的扩大innodb buffer pool,一般...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 302,803
精华内容 121,121
关键字:

优化工作报告怎么写