精华内容
下载资源
问答
  • 数据库调优,其中一个重点就是应用程序的SQL调优。而应用程序的调优范围比较广,可以从调整业务的角度做调优,也可以从重构代码的角度调优。但是无论用什么办法调优,都必须要先读懂SQL的执行计划,了解应用程序的...
  • 数据库调优

    2019-02-11 09:21:38
    参考: MySQL/Oracle数据库优化总结(非常全面) MYSQL数据库调优,MYSQL 调优方法使用必读 数据库SQL调优的几种方式
    展开全文
  • 10g 11g Red Hat 5,6 6u5 OEL 6 DBA 体系结构和SQL 3天 数据库企业级安装部署 1天 数据库对象管理 1天 备份恢复 1天 闪回 1天 数据迁移 2天 DataGuard容灾 2天 DB-Tuning 数据库调优 2天 SQL-Tuning SQL开发调优 2天...
  • 数据库调优的方法

    2016-03-30 14:52:00
    数据库调优的方法 1.引言 数据库调优可以使数据库...建立索引编写高效的SQL 语句能有效避免低性能操作;通过锁的调优解 决并发控制方面的性能问题。 数据库调优技术可以在不同的数据库系统中使用,它不必纠缠于复...

           数据库调优的方法


    1.引言   


        数据库调优可以使数据库应用运行得更快,它需要综合考虑各种复杂的因素。将数据均 匀分布在磁盘上可以提高I/O 利用率,提高数据的读写性能;适当程度的非规范化可以改善 系统查询性能;建立索引和编写高效的SQL 语句能有效避免低性能操作;通过锁的调优解 决并发控制方面的性能问题。     数据库调优技术可以在不同的数据库系统中使用,它不必纠缠于复杂的公式和规则,然 而它需要对程序的应用、数据库管理系统、查询处理、并发控制、操作系统以及硬件有广泛 而深刻的理解。

    2.计算机硬件调优

    2.1 数据库对象的放置策略     利用数据库分区技术,均匀地把数据分布在系统的磁盘中,平衡I/O 访问,避免I/O 瓶 颈:   


        (1)访问分散到不同的磁盘,即使用户数据尽可能跨越多个设备,多个I/O 运转,避免 I/O 竞争,克服访问瓶颈;分别放置随机访问和连续访问数据。   
        (2)分离系统数据库I/O 和应用数据库I/O,把系统审计表和临时库表放在不忙的磁盘 上。   
        (3)把事务日志放在单独的磁盘上,减少磁盘I/O 开销,这还有利于在障碍后恢复,提 高了系统的安全性。   
        (4)把频繁访问的“活性”表放在不同的磁盘上;把频繁用的表、频繁做Join的表分别 放在单独的磁盘上,甚至把频繁访问的表的字段放在不同的磁盘上,把访问分散到不同的磁 盘上,避免I/O 争夺。

    2.2 使用磁盘硬件优化数据库


         RAID (独立磁盘冗余阵列)是由多个磁盘驱动器(一个阵列)组成的磁盘系统。通过将磁盘阵列当作一个磁盘来对待,基于硬件的RAID允许用户管理多个磁盘。使用基于硬件的 RAID与基于操作系统的RAID相比较,基于硬件的RAID能够提供更佳的性能。如果使用基于操作系统的RAID,那么它将占据其他系统需求的CPU周期;通过使用基于硬件的RAID, 用户在不关闭系统的情况下能够替换发生故障的驱动器。   
    SQL Server 一般使用RAID等级0、1  和5。  
        RAID 0 是传统的磁盘镜象,阵列中每一个磁盘都有一个或多个磁盘拷贝,它主要用来 提供最高级的可靠性,使RAID 0成倍增加了写操作却可以并行处理多个读操作,从而提高 了读操作的性能。
        RAID 1 是磁盘镜像或磁盘双工,能够为事务日志保证冗余性。
        RAID 5带奇偶的磁盘条带化,即将数据信息和校验信息分散到阵列的所有磁盘中,它可以消除一个校验盘的瓶颈和单点失效问题,RAID 5 也会增加写操作,也可以并行处理一个读操作,还 可以成倍地提高读操作的性能。   
        相比之下,RAID 5 增加的写操作比RAID 0 增加的要少许多。在实际应用中,用户的读操作要求远远多于写操作请求,而磁盘执行写操作的速度很快,以至于用户几乎感觉不到增加的时间,所以增加的写操作负担不会带来什么问题。在性能较好的服务器中一般都会选择使用RAID 5 的磁盘阵列卡来实现,对于性能相对差一些的服务器也可利用纯软件的方式来实现RAID 5。

    3.关系系统与应用程序调优


        3.1 应用程序优化 

       从数据库设计者的角度来看,应用程序无非是实现对数据的增加、修改、删除、查询和体现数据的结构和关系。设计者在性能方面的考虑因素,总的出发点是:把数据库当作奢侈 的资源看待,在确保功能的同时,尽可能少地动用数据库资源。包括如下原则:   
    (    1)不访问或少访问数据库;   
        (2)简化对数据库的访问;   
        (3)使访问最优;   
        (4 )对前期及后续的开发、部署、调整提出要求,以协助实现性能目标。  
         另外,不要直接执行完整的SQL  语法,尽量通过存储过程来调用SQL Server。客户与服务器连接时,建立连接池,让连接尽量得以重用,以避免时间与资源的损耗。非到不得已, 不要使用游标结构,确实使用时,注意各种游标的特性。

    3.2  基本表设计优化


         在基于表驱动的信息管理系统中,基本表的设计规范是第三范式。第三范式的基本特征 是非主键属性只依赖于主键属性。基于第三范式的数据库表设计具有很多优点:一是能消除 冗余数据、节省磁盘存储空间;二是有良好的数据完整性限制(基于主外键的参照完整限制 和基于主键的实体完整性限制),这使得数据容易维护、移植和更新;三是数据的可逆性好, 在做连接查询或者合并表时不遗漏、不重复;四是消除了冗余数据(这里主要指冗余列), 使得查询时每个数据页存储的数据行增多,这样就有效地减少了逻辑I/O,同时也减少了物 理I/O;五是对大多数事务而言,运行性能好;六是物理设计的机动性较大,能满足日益增 长的用户需求。
        基于第三范式设计的库表虽然有其优越性,然而在实际应用中有时不利于系统运行性能 的优化:例如需要部分数据时而要扫描整表,许多过程同时竞争同一数据,反复用相同行计 算相同的结果,过程从多表获取数据时引发大量的连接操作,当数据来源于多表时的连接操 作;这都消耗了磁盘I/O 和CPU 时间。特别需要提出的是,在遇到下述情形时,我们要对 基本表进行扩展设计优化:许多过程要频繁访问一个表、子集数据访问、重复计算和冗余数 据,有时用户要求一些过程优先或低的响应时间,为避免以上不利因素,我们通常根据访问 的频繁程度对相关表进行分割处理、存储冗余数据、存储衍生列、合并相关表处理,这些都 是克服这些不利因素和优化系统运行的有效途径。
        (1)分割表     分割表可分为水平分割表和垂直分割表两种:水平分割是按照行将一个表分割为多个 表,这可以提高每个表的查询速度,但是由于造成了多表连接,所以应该在同时查询或更新 不同分割表中的列的情况比较少的情况下使用。垂直分割是对于一个列很多的表,若某些列 的访问频率远远高于其它列,在不破坏第三范式的前提下将主键和这些列作为一个表,将主 键和其它列作为另外一个表。一种是当多个过程频繁访问表的不同列时,可将表垂直分成几 个表,减少磁盘I/O。通过减少列的宽度,增加了每个数据页的行数,一次I/O 就可以扫描 更多的行,从而提高了访问每一个表的速度。垂直分割表可以达到最大化利用Cache 的目的。 分割表的缺点是要在插入或删除数据时要考虑数据的完整性,用存储过程维护。
        (2)存储衍生数据     对一些要做大量重复性计算的过程而言,若重复计算过程得到的结果相同,或计算牵扯 多行数据需额外的磁盘I/O 开销,或计算复杂需要大量的C P U 时间,就考虑存储计算结果:若在一行或多行进行重复性计算,就在表内增加列存储结果,但若参与计算的列被更新时, 必须要用触发器或存储过程更新这个新列。总之,存储冗余数据有利于加快访问速度,但违 反了第三范式,这会增加维护数据完整性的代价,必须用触发器立即更新、或存储过程更新, 以维护数据的完整性。

    3.3 修改应用技术模式


         引入“中间表”的概念,在实际单据未进入核心业务流程前,采用“中间表”的技术思 路,就是在实际用户操作过程中,实际操作的是一个临时表,在进行数据某个阶段审核(进 入下一个环节)后,将临时表的数据写入正式表,并且删除临时表的数据,这样始终保持用 户操作表的固定的数据量而且控制增长,可以定期清除。     采用临时表技术首先需将要操作的数据集插入到临时表中,这会给系统带来额外的开 销。这里假设临时表中的数据集远小于源数据表中的数据集,因此在进行数据连接操作或对 数据集进行频繁读操作时,系统的性能会提高几倍甚至几十倍不等。   
        并非所有情况都适宜用临时表技术。一般来说,下面两种情况适宜采用临时表技术进行 处理:    
        (1)对数据量较大的表进行连接操作,并且连接操作的结果是一个小结果集。
        (2)对数据量较大的表进行频繁访问,访问的范围比较固定且比较集中。
        合理使用临时表技术,有助于提高应用系统对大数据表的实时处理的性能。

    4.数据库索引优化   


        索引是建立在表上的一种数据组织,它能提高访问表中一条或多条记录的特定查询效 率。利用索引优化系统性能是显而易见的,对所有常用于查询中的Where  子句的列和所有 用于排序的列创建索引,可以避免整表扫描或访问,在不改变表的物理结构的情况下,直接 访问特定的数据列,这样可以减少数据存取时间;利用索引可以优化或排除耗时的分类操作, 把数据分散到不同的页面上,这样就分散了插入的数据;主键自动建立了唯一索引,因此唯 一索引也能确保数据的唯一性(即实体完整性)。总之,索引可以加快查询速度、减少I/O 操作、消除磁盘排序。     优化索引可以避免扫描整个表,减少因查询造成的开销。一般说来建立索引要注意以下 几点:    
        (1)检查被索引的列或组合索引的首列是否出现在PL/SQL 语句的WHERE 子句中, 这是“执行计划”能用到相关索引的必要条件。比较一下列中唯一键的数量和表中记录的行 数,就可以判断该列的可选择性。如果该列的“唯一键的数量/表中记录行数”的比值越接近 于1,则该列的可选择行越高。在可选择性高的列上进行查询,返回的数据就较少,比较适 合索引查询。相反,比如性别列上只有两个值,可选择行就很小,不适合索引查询。因此, 在查询中经常作为条件表达式且不同值较多的列上建立索引,不同值较少的列上不要建立索 引。    
        (2)索引的创建也是需要代价的,对于删除、某些更新、插入操作,对于每个索引都 要进行相应的删除、更新、插入操作。从而导致删除、某些更新、插入操作的效率变低。因 此频繁进行删除、插入操作的表不要建立过多的索引。    
        (3)查询经常用到的列上建立非聚簇索引,在频繁进行范围查询、排序、分组的列上 建立聚簇索引。    
        (4 )对于不存在重复值的列,创建唯一索引优于创建非唯一索引。    
        (5)当数据库表更新大数据后,删除并重新建立索引来提高查询速度。    
        (6)当对一个表的update 操作远远多于select 操作时,不应创建索引。    
        (7)如果索引列是函数的参数,则索引在查询时用不上,该列也不适合索引。    
        (8)Hash   Join (HJ )由于须做HASH 运算,索引的存在对数据查询速度几乎没有影 响。    
        (9)在主键上建立索引,尤其当经常用它作为连接的时候;在经常用于连接而又未指 定为外键的列上建立索引。   
         (10)经常同时存取多列,且每列都含有重复值,可以考虑建立复合索引来覆盖一个或 一组查询,并且把查询引用最频繁的列作为前导列。                                    
        (11)尽使用较窄的索引,这样数据页每页上能因存放较多的索引行而减少操作。    
        (12)并行查询将不会用到索引。    
        (13)索引中存储值不能为全空。    
        (14)查询中较少用到的列、数据量较大的列均不应建立索引。

    5.SQL 语句优化   


        在完成了系统设计、索引设计等工作以后,就要考虑在使用过程中对语句的设计了。 影响数据库应用程序性能的一个重要因素是SQL 语句,按其影响严重程度,依次可分为: 无谓的SQL,拙劣的SQL,复杂的SQL。     无谓的SQL:它们对数据库的访问,并不存在技术、技能上的问题,但却不是必要的, 超出了实际业务需求。其结果是浪费了宝贵的主机资源、占用了网络流量,降低了系统性能。     拙劣的SQL:它们对数据库的访问并不是多余的,所体现的业务逻辑或结果是正确的, 但是“写法”不够好,导致数据库处理起来不够优化。     复杂的SQL:数据库中多表(或视图)关联,条件复杂、冗长,计算复杂,使用冷僻 的SQL 技术等。     其中,无谓的SQL 和拙劣的SQL 属于开发技能方面的问题;复杂的SQL 属于设计技 能方面的问题,设计到数据库的结构。     在使用结构化查询语言来执行查询时,推荐以下举措:   
        (1)择运算应尽可能先做,并在对同一个表进行多个选择运算时,选择影响较大的语 句放在前面;较弱的选择条件写在后面,这样就可以先根据较严格的条件得出数据较小的 信息,再在这些信息中根据后面较弱的条件得到满足条件的信息。   
        (2)应避免使用相关子查询。把子查询转换成联结来实现。对于主查询的每一条记录子 查询都要执行一次,嵌套的层次越多效率越低。避免对子句使用数学运算符。即不要对数 据表的属性列进行操作。SQL 概念上将位于WHERE  子句中的相关子查询,处理成获取参 数并且返回一个单独的值或值的集合的函数。因为子查询要对应位于外层查询的每一个元组 进行单独的计算。从而导致大量的随机磁盘I/O 操作。所以在实际应用中若可以用连接代替 的子查询,则用连接实现。例如,有以下相关子查询语句:   
    SELECT ProductName FROM Products WHERE EXISTS     (SELECT * FROM OrderDetails     WHERE     Discount >= 25 AND  Products.ProductID= OrderDetails.ProjectID) ;   
    用连接查询实现如下:   
    SELECT   ProductName   FROM    Products , OrderDetails     WHERE Discount >= 25 AND Products.ProductID= OrderDetails.ProjectID   
        (3)字段提取按照“ 需多少,提多少” 的原则,避免“SELECT *”。“SELECT *”需 要数据库返回相应表的所有列信息,这对于一个列较多的表无疑是一项费时的操作。   
        (4)避免使用!=(或<>)、IS NULL 或IS NOT NULL、IN、NOT IN等这样的操作符,避免在WHERE 子句中使用非聚合表达式。这些操作符会使系统无法使用索引,而只能直接搜 索表中的数据。例如,SELECT id,name FROM employee WHERE id!=B%      优化器将无法通过索引来确定将要命中的行数,因此需要搜索该表的所有行。   
        (5)避免使用OR,用UNION 代替。OR 语句的执行原理并不是利用列上的索引根据每 个语句分别查找再将结果求并集,而是先取出满足每个OR 子句的行,存入临时数据库的 工作表中,再建立唯一索引以去掉重复行,最后从这个临时表中计算结果。这样使用可能 造成索引失效,导致顺序扫描整个表,大大降低查询效率。   
        (6)在执行连接前对关系作适当的预处理,预处理的方法有两种,在连接属性上建立 索引和对关系进行排序。   
        (7)将一个大的查询拆成多步执行查询。   
        (8)如果应用程序使用循环,可考虑在查询内放入循环。

    6.事务处理调优   


        数据库的日常运行过程中可能面临多个用户同时对数据库的并发操作带来的数据不一 致的问题,如:丢失更新、脏读和不可重复读等。并发控制的主要方法是封锁,锁就是在一段时间内禁止用户做某些操作以避免产生数据不一致。   
        数据库应用程序将其工作分成若干个事务进行处理。当一个事务执行时,它访问数据库 并执行一些本地计算。开发人员可以假设每一个事务都会被隔离地执行—没有任何并发动 作。因为隔离的概念提供了透明性,这种对事务处理方式的保证有时被称为原子性保证。但 是,如果把应用程序中的事务序列作为一个整体来看,则并没有上面所说的那种保证。在一 个应用程序执行的两个事务之间,可能会执行另外一个应用程序的事务,而且第二个应用程 序的执行可能修改了第一个应用程序中的两个事务(或其中的一个)需要访问的数据项。因 此,事务的长度对保证正确性有着重要影响。   
        尽管将事务切分成较小粒度可以提高执行效率,但会因此破坏执行的正确性。这种性能 和正确性之间的矛盾充斥并发控制的整个调优过程。考虑事务的性能我们要考虑到:事务使 用的锁的个数(在所有其他条件相同的情况下,使用的锁个数越少,性能越好);锁的类型  (读锁对性能更有利);事务持有锁的时间长短(持有时间越短,性能越好)。
        关于锁的调优 有以下建议:
       (1)使用特殊的系统程序来处理长的读操作。对于一个只读的事务R 来说,它“看到” 的数据库的状态一直是事务R  开始时的状态。只读查询可以不需要封锁开销,在不造成阻 塞和死锁的情况下,只读的查询可以与其他对同一数据进行更新的较小的事务并行地执行。
       (2)消除不必要的封锁。只有一个事务执行时,或所有事务都是只读事务时,用户应 利用配置选项减少锁的个数,从而减小锁管理模块的内存开销和执行封锁操作的处理时间开 销。
       (3)根据事务的内容将事务切分成较小的事务。事务所要求的锁越多,它需要等待其 他事务释放某个锁的可能就越大。事务T 执行的时间越长,被T  阻塞的事务等待的时间可 能就越长。因此,在可能发生阻塞的情况下,利用较短的事务较好。    
        (4 )在应用程序允许的情况下,适当降低隔离级别。    
        (5)选择适当的封锁粒度。页级封锁阻止并发事务访问或修改该页面上所有记录,表 级封锁阻止并发事务访问或修改表内所有的页面;记录级封锁(行级锁)比页级封锁粒度好, 页级封锁比表级封锁粒度好。长事务(指要访问表内几乎所有页面的事务)应该尽可能使用 表级封锁来防止死锁,而短事务应该使用记录级封锁来提高并发度。    
        (6)只在数据库很少被访问时才修改有关数据定义的数据(系统目录或元数据)。每个 能够编译、添加或删除表、添加或删除索引、改变属性定义的事务都必须访问目录数据,因 此,目录很容易成为热点,也因而成为瓶颈。    
        (7)减少访问热点(大量事务访问和更新的数据)。只有在更新某热点的事务完成滞后, 其他的事务才能获得这个热点上的锁,因此热点可能成为瓶颈。    
        (8)死锁检测周期的调优。   
         以上每个建议都可以独立于其他建议来运用,但是在调优时必须检测是否能体现合适 的隔离性保证。

    7.总结   


        数据库性能优化的基本原则就是通过尽可能少的磁盘访问获得所需要的数据。本文从计 算机硬件、关系系统与应用程序、数据库索引、SQL  语句、事务处理几个比较共性的方面 分析了数据库性能优化的问题,  提出了若干数据库性能优化的策略。当然实现优化的方法还 有很多,  要根据具体情况而定。对于不同的应用情况,我们应该具体情况具体分析,  各方面优 化措施综合运用,  以使数据库性能得到提高。数据库应用系统的性能是一项全民工程,开发 团队的所有人都有责任为性能做贡献,树立性能意识,使之成为日常工作的习惯而不是单独 成为某一阶段的工作,要未雨绸缪,不要寄希望于某一个环节的工作。


    [转自] http://www.myfreelinux.com/?p=538

    面试中回答关于oracle数据库优化的方法

    1关于优化器的优化配置
    介绍oracle数据库优化的基本方式,基于规则的优化,基于规则包括全表扫描,扫描第一行
    (需要上网查看一下)
    基于成本的优化,基于选择的优化
    也就是oracle的3种优化器,关于优化器的配置可以通过oracle数据库启动的时候通过初始化
    数据库配置文件来配置 通过配置init.ora文件的optimizer_mode也就是优化模式的参数来
    配置这个配置是一个全局的共享配置。优化模式可以根据具体的生产环境在用户会话级别和sql
    级别进行在配置 ,来覆盖全局的优化模式配置
    使用基于成本的优化器要运行一个命令 analyze(分析)让oracle分析表 统计统计数据对象
    达到更好的优化效果.(如果针对表执行了analyze 将自动使用基于成本的优化器)
    2oracle访问表的方式优化
    a 全表扫描模式 全表扫面oracle采用读取数据块的方式对全部表进行扫面块的大小可以在
    init.ora中配置 数据块的大小应该是系统的数据块的整数倍。适当调节数据块的大小可以
    起到性能优化的作用
    b 通过rowid访问数据表 rowid表记录的物理位置信息标记,oracle通过索引与rowid建立
    联系通过适当的在字段上建立索引可以高速高效的访问数据表的记录,从而达到数据库优化
    查询的目的。
    3 共享sql语句
    为了不重复解析SQL语句oracle采用共享SQL的方式提高SQL语句执行效率。oracle把执行过的
    语句放入到一个叫做SGA内存区域的share buffer pool 中 也就是系统全局区域的缓存共享池
    如果客户端执行的SQL语句完全相同oracle首先查找share buffer pool 如果存在这个语句
    直接执行这个语句。不过oracel对SQL语句的匹配要求很严格是在字符级别进行匹配的,包括
    执行语句的字符匹配。所以在实际的项目中尽量要求编码人员统一规范编写SQL语句尽量做到
    统一以提高数据库的性能和执行效率。还有就是oracle基于对象的共享匹配,包括用户对象,
    变量的绑定在权限对象分配中和dba沟通做好系统优化的准备。
    我们可以适当的提高SGA中share buffer pool 的大小 来优化数据库的执行效率
    4 根据oracle的SQL语句解析器进行优化
    首先解释一下oracle 解释SQL的语句的顺序,oracle在解释SQL语句的时候是按照从右到左
    的顺序进行解析。所有要把检索效率最高的表,记录最少的表写在FROM的最右表也就是基础
    表的选择
    5 交叉表优先选择
    在多对多的表关联中会遇到这样的情况。在sql语句的书写方面尽量把引用表放在FROM的
    右边
    6 基于wher条件书写顺序的解释优化
    首先介绍一下oracle
    以上两个数据库优化的基本原则就是根据oracle解析sql语句和oracle自身对在扫描表的时候
    做的优化基础上尽量优先选择出记录少的表放在from的最右边

    7尽量减少与数据库的交互减少系统的IO访问量
    8 不要用*
    9 使用decode函数减少处理时间
    10 整合简单的无关联的数据库访问 较少数据库服务器的IO操作
    11删除重复记录 尽量使用RID
    12 用TRUNCATE替代DELETE
    13 在编写pl/sql时候尽量多使用cimmit 以便释放系统资源
    只要有可能,在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少:
    COMMIT所释放的资源:
    a. 回滚段上用于恢复数据的信息.
    b. 被程序语句获得的锁
    c. redo log buffer 中的空间
    d. ORACLE为管理上述3种资源中的内部花费
    14. 用Where子句替换HAVING子句
    避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤.
    这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销.

    例如:

    低效:
    SELECT REGION,AVG(LOG_SIZE)
    FROM LOCATION
    GROUP BY REGION
    HAVING REGION REGION != ‘SYDNEY’
    AND REGION != ‘PERTH’

    高效
    SELECT REGION,AVG(LOG_SIZE)
    FROM LOCATION
    WHERE REGION REGION != ‘SYDNEY’
    AND REGION != ‘PERTH’
    GROUP BY REGION
    (译者按: HAVING 中的条件一般用于对一些集合函数的比较,如COUNT() 等等.
    除此而外,一般的条件应该写在WHERE子句中)

    15 尽量少用子查询 合并可以合并的子查询
    例如:
    低效
    SELECT TAB_NAME
    FROM TABLES
    WHERE TAB_NAME = ( SELECT TAB_NAME
    FROM TAB_COLUMNS
    WHERE VERSION = 604)
    AND DB_VER= ( SELECT DB_VER
    FROM TAB_COLUMNS
    WHERE VERSION = 604)

    高效
    SELECT TAB_NAME
    FROM TABLES
    WHERE (TAB_NAME,DB_VER)
    = ( SELECT TAB_NAME,DB_VER)
    FROM TAB_COLUMNS
    WHERE VERSION = 604)

    Update 多个Column 例子:
    低效:
    UPDATE EMP
    SET EMP_CAT = (SELECT MAX(CATEGORY) FROM EMP_CATEGORIES),
    SAL_RANGE = (SELECT MAX(SAL_RANGE) FROM EMP_CATEGORIES)
    WHERE EMP_DEPT = 0020;

    高效:
    UPDATE EMP
    SET (EMP_CAT, SAL_RANGE)
    = (SELECT MAX(CATEGORY) , MAX(SAL_RANGE)
    FROM EMP_CATEGORIES)
    WHERE EMP_DEPT = 0020;

    15. 使用表的别名(Alias)
    当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上.这样一来,
    就可以减少解析的时间并减少那些由Column歧义引起的语法错误.

    (译者注: Column歧义指的是由于SQL中不同的表具有相同的Column名,
    当SQL语句中出现这个Column时,SQL解析器无法判断这个Column的归属)

    转载于:https://www.cnblogs.com/ios9/p/5337181.html

    展开全文
  • 数据库调优教程——优化sql语句

    千次阅读 2017-07-26 21:04:15
    五、 优化Sql语句 上一章讲了如何设计一张好的表,一张好的表自然需要好的sql语句去操作它。本章就来聊聊如何优化sql语句。 ...1. Sql语句优化原则 ...尽可能避免复杂的join子查询   2. 优化l

    五、           优化Sql语句


    上一章讲了如何设计一张好的表,一张好的表自然需要好的sql语句去操作它。本章就来聊聊如何优化sql语句。


    1.      Sql语句优化原则

    优化需要优化的Query

    定位优化对象性能瓶颈

    从Explain入手

    尽可能在索引中完成排序

    只取自己需要的Column

    尽可能避免复杂的join和子查询

     

    2.     优化limit

    1. select * from test1 order by id limit 99999,10  
    select * from test1 order by id limit 99999,10

    原语句虽然使用了id索引,但是相当于从第一行定位到99999行再去扫描后10行,相当于扫描全表

    如果改为
    1. select * from test1 where id>=100000 order by id limit 10  
    select * from test1 where id>=100000 order by id limit 10

    则直接定位到100000查找


    3.     尽量避免SELECT *命令


    4.      不让排序

    在使用group by 分组查询时,默认分组后,还会排序,可能会降低速度.

     

    比如: 


    group by后面增加 order by null 就可以防止排序.


    5.     使用连接来替代子查询

    有些情况下,可以使用连接来替代子查询。因为使用join,MySQL不需要在内存中创建临时表。

    [糟糕的效率]

    1. select * from sales2 where company_id not in(select id from company2)  
    select * from sales2 where company_id not in(select id from company2)

    [简单处理方式]

    1. select * from dept, emp where dept.deptno=emp.deptno;   
    select * from dept, emp where dept.deptno=emp.deptno; 

    [左外连接,效率最高]


    1. select * from dept left join emp on dept.deptno=emp.deptno;    
    select * from dept left join emp on dept.deptno=emp.deptno;  

    6.     利用LIMIT 1取得唯一行

    有时,当你要查询一张表是,你知道自己只需要看一行。在这种情况下,增加一个LIMIT 1会令你的查询更加有效。这样数据库引擎发现只有1后将停止扫描,而不是去扫描整个表或索引

     

    7.     使用 EXISTS代替in

     EXISTS要远比IN的效率高。里面关系到full table scan和range scan。几乎将所有的IN操作符子查询改写为使用EXISTS的子查询

     

    8.     不要手贱

    没有必要时不要用DISTINCT和ORDER BY

    这些动作可以改在客户端执行,它们增加了额外的开销



    本章结束,下一章聊聊mysql数据库的其他优化方法。
    展开全文
  • 分布式数据库调优实践

    千次阅读 2020-06-23 07:40:37
    数据库调优实践案例 数据库作为基础数据支撑层的核心部分,对于应用平台整体性能表现有着决定性的影响。因此,数据库性能优化可以说是最考验DBA能力的工作了。本文我们就由数据库内核专家来,以 SequoiaDB 5.0 ...

    数据库调优实践案例

     

    数据库作为基础数据支撑层的核心部分,对于应用和平台整体性能表现有着决定性的影响。因此,数据库性能优化可以说是最考验DBA能力的工作了。本文我们就由数据库内核专家来,以 SequoiaDB 5.0 内核的部分性能优化为例,带领各位数据库爱好者揭开数据库性能优化的“神秘面纱”。

     

    通常优化思路:

    提高数据库性能的方式有很多,总结起来从易到难无外乎如下三种:

    1. 最简单直观的是通过使用数据库提供的工具,找到SQL语句执行中消耗资源最大或耗时最长的部分,也即性能瓶颈。然后通过调整数据本身或数据库配置解决这些性能瓶颈。比如说发现数据分布不均匀,我们可以通过切分数据(split)达到数据均衡(rebalance);再比如我们发现某些网络时延较长,在确定不是网络本身的问题后,我们可以通过调整连接端口数和通讯处理线程提高数据库消息处理能力;再比如单点磁盘IO过多,需要调整缓存或调整部分数据的分布。SequoiaDB提供了图形化的性能诊断工具SequoiaPerf,可以协助用户完成上述的调优。
    2. 业界经验证明,效果最明显,成本最低的方法其实是SQL语句的调优,通常是通过理解分析访问计划,对比实际语句执行时的开销来判断语句是否优化。比如对比索引读和表读的个数判断否创建使用了合适的索引;对比访问计划的打分和时间执行开销来判断表/集合/索引的统计信息是否反映当前最新的状态;观察锁等待时间来判断系统中是否存在应用持锁时间过长阻塞其他应用而;对比join两边表的返回数据集以及使用的过滤条件判断使用join的类型是否合理。SequoiaDB 提供了完善监控功能,通过结合图形化的sequoiaPerf 与snapshot,用户可以相对容易的定位和实现SQL语句的调优。
    3. 前两种方式通常是DBA或应用开发者就能完成的任务,第三种是数据库内核的优化。这主要是数据库厂商在不断的实践中,通过各种相对底层的性能诊断工具,定位和优化数据引擎的性能。

     

    内核调优

    在数据库内核的调优中,开发人员通常会跑一定的workload或benchmark,使用操作系统或三方提供的工具,持续监控系统各类资源的使用情况,在高并发系统中,也会关注并发控制中使用的锁和原子变量带来的开销。下面我们通过TPCC场景下的逐步优化SequoiaDB内核的过程,来了解我们是如何使用工具来定位优化数据引擎的。

     

    1. CPU usage

    我们常使用两大神器观察CPU使用情况:top 和 perf。top能动态的显示linux 系统中各进程/线程以及内存使用的汇总信息。

    /

    以上图为例,我们知道这台机器的CPU基本上被用满了,其中系统CPU占13%,用户CPU占81.7%。如果CPU出现过多的空闲,往往意味着系统要么还可以增加负载提高性能,要么有瓶颈导致CPU上不去,比如说并发不好,太多等待,串行化太多。在这个例子中,我们没有看到等IO的情况,idle的比例也非常小,这都是好的现象。在CPU用满的情况下,优化系统也意味着要尽量减少开销,让系统能尽可能的跑多点任务。需要注意的是,如果系统CPU过高,意味着CPU不是在执行跟程序逻辑相关的指令,也可以理解为是overhead。根据以往的经验,这里系统CPU占比还是偏高。使用线程模式,更进一步分析,我们可以看到潜在的问题可能是在系统调用,context switch和并发控制的mutex上。

     

    至于更精确的定位,就要perf出马了。注意的是SequoiaDB 的代码编译时加入了debug symbol,这样会带来一定的性能损失,但能够极大的方便问题诊断和定位。

     

    perf 是linux提供的一种基于event的性能搜集分析工具,能够分析CPU/内存/锁等资源的统计信息。perf本身已经提供了相当完整的文字的报表输出功能。

     

    比如这里能看到system_call 也是跟sys_futex 相关的,通常是线程/进程同步共享资源互踩时造成的,还有部分是通讯线程相关的。这样我们的方向就可以从各种锁冲突入手。Perf也能提供锁冲突的信息。

    为了简单直观的分析结果,我们还使用火焰图(flame graph)来用图形的方式展现结果,以利用更快的发现问题。下面两张图分别提供了CPU和锁的使用统计:从中我们发现的确有几处热的Latch/mutex。比如内存分配时使用共享内存池,这是会造成等锁的现象,我们可以通过使用线程上独享的内存池解决;还要部分内部表的物理锁冲突严重,我们通过增加锁的控制粒度减少冲突;再有就是尽量减少锁内操作,比如内存分配,磁盘IO尽可能的搬出热锁保护范围。通过一系列优化,我们实现了5%左右的性能提升。

    CPU 火焰图

    锁火焰图

     

    2. Memory allocation

    内存是个好东西,现在计算机系统内存越来越大,软件也尽量通过使用内存来实现空间换时间以提高系统相应速度。但是动态内存分配常常成为了高性能软件的性能瓶颈。我们通过perf 来抓取系统内存的使用情况,并用火焰图显示出来:

     

    这里明显看到的是很多动态内存分配发生在一个set的插入过程中。Std::set内部使用的红黑树,每次结点的插入都要进行内存分配。为了减少系统内存的动态分配与回收,SequoiaDB实现了一整套自己的内存管理机制。最开始尽量在线程预分配好的内存池上分配空间,这点和tcmalloc的原理很接近,这时的开销最小,内存事先已经从操作系统分配好了,而且本线程上分配是无锁的。但是如果线程内存池用完了,我们会到一个共享的预分配好的内存池上分配,这时会多一个锁的开销。但这两处都用完了,我们才向操作系统申请。从火焰图上看,我们基本上都走到向操作系统分配的分支中了。针对这种情况,我们优化了set的实现。当set中结点数量较小时,我们用一个flat的较小的array存放数据,避免了动态内存分配。当结点数较多时,我们再转化成树型结构以提高查找效率。但是我们会提高线程上允许的缓冲池的大小,特别是小结构线程池大小。最终我们避免了绝大多少的动态内存分配与回收,提升了系统性能。通过这块的分析,我们也反过来帮助确定那些query会用到大量数据,并优化对应的query。

     

    3. Cache line misses

    大家知道现代CPU的主频非常高,常有超过3GHz,执行指令速度非常快。但是我们存储访问速度始终跟不上,高速的内存又非常贵,这就是现代CPU里有几级不同速度不同大小内存的原因,常见的CPU内集成有L1,L2,L3级缓存。CPU执行时需要从缓存中获取指令和数据。在我们编译程序的时候,编译器会试图优化程序,使得CPU能有效的重用或预提取数据和指令。当CPU在缓存中找不到合适的指令和数据时,就不等不从主存甚至磁盘上读取他们,这样的开销非常大,我们用CPU cache line miss来衡量这中情况出现的频繁程度。

     

    我们还是通过perf命令来搜集cache line miss的情况,

     

    详细信息分解开来,最大一块是由monitor引起的

     

    然后我们检查monitor相关的代码,发现代码中有个switch语句公有14个分支,但最常用的一个分支放在了后面。我们只需要将其挪到前面,我们的miss就有显著下降。

     

    还有另外一种情况造成严重的cache line miss,就是使用原子变量,特别是频繁使用的原子变量。因为一旦该变量被变更了,所有cache 里的值都会变成无效,那么CPU使用时一定会碰到cache line miss。我们通过分析代码逻辑,对于某些常用的确不需要时时精确的值,我们可以在程序逻辑开始存为本地变量,避免过多的直接访问。对于一些只需要单线程访问的变量,我们也避免使用原子变量。

     

    小结:

    上面我们通过几个例子,为大家展现了如何通过系统工具进行数据库内核性能优化,同样的思路也可以适用于其他底层软件的开发调试。在实际的实践过程中,除了使用合适的工具,更重要的是还要细心,有耐心和钻研的精神,一步步的下手,从现象中抽丝剥茧,找到根本原因。

    展开全文
  • 数据库调优2

    2013-10-28 17:36:00
    1.引言  数据库调优可以使数据库应用运行得更快,它需要综合考虑各种复杂的因素。...数据库调优技术可以在不同的数据库系统中使用,它不必纠缠于复杂的公式规则,然 而它需要对程序的应用、...
  • 数据库调优可以使数据库应用运行得更快,它需要综合考虑各种复杂的...数据库调优技术可以在不同的数据库系统中使用,它不必纠缠于复杂的公式规则,然而它需要对程序的应用、数据库管理系统、查询处理、并发控...
  • 数据库调优目标 简单的目的:运行更快,响应更快,吞吐量更大 不过目标太泛,不够具体,所以我们需要根据精细的问题定位去调优。通常是以下几种: 用户反馈 根据用户的问题反馈,找到数据相关的问题 日志分析 ...
  • Mysql数据库调优——索引 参考资料: MySQL分区:https://www.bilibili.com/video/BV1E7411q7Nx MySQL_基础+高级篇- 数据库 -sql -mysql教程_mysql视频_mysql入门_尚硅谷:...
  • 数据库调优(1)

    2014-04-04 17:43:58
    据库调优(1) 1.引言  数据库调优可以使数据库应用运行得更快,它需要综合考虑各种复杂的因素。将数据均 匀分布在磁盘上可以... 数据库调优技术可以在不同的数据库系统中使用,它不必纠缠于复杂的公式规则...
  • 外部调优 1,使用连接池。 性能提升10倍 2, 阻止不必要的参数不让传进来 3,增加最大连接数,及时释放连接 内部调优 1,改mysql进程(对MySQL服务器(也称为mysqld)进行调优。)  对这个进程进行调优...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 1,691
精华内容 676
关键字:

数据库调优和sql调优