精华内容
下载资源
问答
  • 从索引优化以及SQL语句优化2方面分析了关系数据库查询优化技术。使用合理有效的索引可以提高查询效率,创建索引应与查询需求紧密结合。对SQL查询语句从执行过程角度进行规划设计,可以有效减少查询中间过程的计算量,...
  • 查询优化关系数据库系统中有着非常重要的地位 关系查询优化是影响RDBMS性能的关键因素 由于关系表达式的语义级别很高,使关系系统可以从关系表达式中分析查询语义,提供了执行查询优化的可能性 查询优化的优点...

    查询优化

    在关系数据库系统中有着非常重要的地位
    关系查询优化是影响RDBMS性能的关键因素
    由于关系表达式的语义级别很高,使关系系统可以从关系表达式中分析查询语义,提供了执行查询优化的可能性

    查询优化的优点不仅在于用户不必考虑如何最好地表达查询以获得较好的效率,而且在于系统可以比用户程序的“优化”做得更好
    (1) 优化器可以从数据字典中获取许多统计信息,而用户程序则难以获得这些信息
    (2)如果数据库的物理统计信息改变了,系统可以自动对查询重新优化以选择相适应的执行计划。在非关系系统中必须重写程序,而重写程序在实际应用中往往是不太可能的。
    (3)优化器可以考虑数百种不同的执行计划,程序员一般只能考虑有限的几种可能性。
    (4)优化器中包括了很多复杂的优化技术,这些优化技术往往只有最好的程序员才能掌握。系统的自动优化相当于使得所有人都拥有这些优化技术

    RDBMS通过某种代价模型计算出各种查询执行策略的执行代价,然后选取代价最小的执行方案
    集中式数据库
    执行开销主要包括:
    磁盘存取块数(I/O代价)
    处理机时间(CPU代价)
    查询的内存开销
    I/O代价是最主要的
    分布式数据库
    总代价=I/O代价+CPU代价+内存代价+通信代价

    查询优化的总目标:
    选择有效的策略
    求得给定关系表达式的值
    使得查询代价最小(实际上是较小) 
    [例3] 求选修了2号课程的学生姓名。用SQL表达:
          SELECT  Student.Sname
           FROM  Student,SC
           WHERE  Student.Sno=SC.Sno AND                 SC.Cno=‘2’; 
    
    假定学生-课程数据库中有1000个学生记录,10000个选课记录
    其中选修2号课程的选课记录为50
    系统可以用多种等价的关系代数表达式来完成这一查询
    Q1=πSname(σStudent.Sno=SC.Sno∧Sc.Cno='2' (Student×SC))
    Q2=πSname(σSc.Cno='2' (Student     SC))
    Q3=πSname(Student      σSc.Cno='2'(SC))
    

    一、第一种情况
    Q1=πSname(σStudent.Sno=SC.Sno∧Sc.Cno=’2’ (Student×SC))
    1. 计算广义笛卡尔积
    把Student和SC的每个元组连接起来的做法:
    在内存中尽可能多地装入某个表(如Student表)的若干块,留出一块存放另一个表(如SC表)的元组。
    把SC中的每个元组和Student中每个元组连接,连接后的元组装满一块后就写到中间文件上
    从SC中读入一块和内存中的Student元组连接,直到SC表处理完。
    再读入若干块Student元组,读入一块SC元组
    重复上述处理过程,直到把Student表处理完
    2. 作选择操作
    依次读入连接后的元组,按照选择条件选取满足要求的记录
    假定内存处理时间忽略。读取中间文件花费的时间(同写中间文件一样)需5×104s
    满足条件的元组假设仅50个,均可放在内存
    3. 作投影操作
    把第2步的结果在Sname上作投影输出,得到最终结果
    第一种情况下执行查询的总时间≈105+2×5×104≈105s
    所有内存处理时间均忽略不计

    二、 第二种情况
    Q2=πSname(σSc.Cno=’2’ (Student SC))
    1. 计算自然连接
    执行自然连接,读取Student和SC表的策略不变,总的读取块数仍为2100块花费105 s
    自然连接的结果比第一种情况大大减少,为104个
    写出这些元组时间为104/10/20=50s,为第一种情况的千分之一
    2. 读取中间文件块,执行选择运算,花费时间也为50s。
    3. 把第2步结果投影输出。
    第二种情况总的执行时间≈105+50+50≈205s

    三、 第三种情况
    Q3=πSname(Student σSc.Cno=’2’(SC))
    1. 先对SC表作选择运算,只需读一遍SC表,存取100块花费时间为5s,因为满足条件的元组仅50个,不必使用中间文件。
    2. 读取Student表,把读入的Student元组和内存中的SC元组作连接。也只需读一遍Student表共100块,花费时间为5s。
    3. 把连接结果投影输出
    第三种情况总的执行时间≈5+5≈10s

    假如SC表的Cno字段上有索引
    第一步就不必读取所有的SC元组而只需读取Cno=‘2’的那些元组(50个)
    存取的索引块和SC中满足条件的数据块大约总共3~4块

    若Student表在Sno上也有索引
    第二步也不必读取所有的Student元组
    因为满足条件的SC记录仅50个,涉及最多50个Student记录
    读取Student表的块数也可大大减少

    总的存取时间将进一步减少到数秒

    把代数表达式Q1变换为Q2、 Q3,
    即有选择和连接操作时,先做选择操作,这样参加连接的元组就可以大大减少,这是代数优化
    在Q3中
    SC表的选择操作算法有全表扫描和索引扫描2种方法,经过初步估算,索引扫描方法较优
    对于Student和SC表的连接,利用Student表上的索引,采用index join代价也较小,这就是物理优化

    代数优化策略

    :通过对关系代数表达式的等价变换来提高查询效率

    关系代数表达式的等价:指用相同的关系代替两个表达式中相应的关系所得到的结果是相同的

    两个关系表达式E1和E2是等价的,可记为E1≡E2

    常用的等价变换规则:
    1.  连接、笛卡尔积交换律
       设E1和E2是关系代数表达式,F是连接运算的条件,则有
        E1  × E2≡E2  × E1
        E1      E2≡E2      E1
        E1      E2≡E2      E1
    2. 连接、笛卡尔积的结合律
       设E1,E2,E3是关系代数表达式,F1和F2是连接运算的条件,则有
        (E1  × E2) × E3≡E1 × (E2 × E3)
        (E1      E2)      E3≡E1      (E2      E3)
        (E1     E2)      E3≡E1     (E2      E3) 
    3. 投影的串接定律
                     (            (E))≡            (E)
        这里,E是关系代数表达式,Ai(i=12,…,n),Bj(j=12,…,m)是属性名且{A1,A2,…,An}构成{B1,B2,…,Bm}的子集。
    4. 选择的串接定律
               (     (E))≡           (E)
        这里,E是关系代数表达式,F1、F2是选择条件。
        选择的串接律说明选择条件可以合并。这样一次就可检查全部条件
    5. 选择与投影操作的交换律
        σF(          (E))≡            (σF(E))
        选择条件F只涉及属性A1,…,An。
        若F中有不属于A1,…,An的属性B1,…,Bm则有更一般的规则:
                    (σF(E))≡          (σF(                       (E)))
    
    6. 选择与笛卡尔积的交换律
        如果F中涉及的属性都是E1中的属性,则
             (E1×E2)≡     (E1)×E2
        如果F=F1∧F2,并且F1只涉及E1中的属性,F2只涉及E2中的属性,则由上面的等价变换规则146可推出:
             (E1×E2)≡     (E1)×     (E2)
        若F1只涉及E1中的属性,F2涉及E1和E2两者的属性,则仍有
            (E1×E2)≡      (     (E1)×E2)
        它使部分选择在笛卡尔积前先做。 
    
    7. 选择与并的分配律
        设E=E1∪E2,E1,E2有相同的属性名,则
        σF(E1∪E2)≡σF(E1)∪σF(E2)
    8. 选择与差运算的分配律
        若E1与E2有相同的属性名,则
        σF(E1-E2)≡σF(E1)-σF(E2)
    9. 选择对自然连接的分配律
        σF(E1    E2)≡σF(E1)      σF(E2)
        F只涉及E1与E2的公共属性 
    10. 投影与笛卡尔积的分配律
        设E1和E2是两个关系表达式,A1,…,An是E1的属性,B1,…,Bm是E2的属性,则
                            (E1×E2)≡            (E1)×            (E2)
    11. 投影与并的分配律
        设E1和E2有相同的属性名,则
                      (E1∪E2)≡            (E1)∪            (E2)
    

    查询树的启发式优化

    典型的启发式规则:
    1. 选择运算应尽可能先做。在优化策略中这是最重要、最基本的一条
    2. 把投影运算和选择运算同时进行(pipelining技术)
    如有若干投影和选择运算,并且它们都对同一个关系操作,则可以在扫描此关系的同时完成所有的这些运算以避免重复扫描关系,也避免存储中间关系
    3. 把投影同其前或其后的双目运算结合起来执行(pipelining技术)
    4. 把某些选择同在它前面要执行的笛卡尔积结合起来成为一个连接运算
    5. 找出公共子表达式
    如果这种重复出现的子表达式的结果不是很大的关系并且从外存中读入这个关系比计算该子表达式的时间少得多,则先计算一次公共子表达式并把结果写入中间文件是合算的
    当查询的是视图时,定义视图的表达式就是公共子表达式的情况
    遵循这些启发式规则,应用9.3.1的等价变换公式来优化关系表达式的算法。
    算法:关系表达式的优化
    输入:一个关系表达式的查询树
    输出:优化的查询树
    方法:
    (1) 利用等价变换规则4把形如σF1∧F2∧…∧Fn(E)变换为σF1(σF2(…(σFn(E))…))。
    (2) 对每一个选择,利用等价变换规则4~9尽可能把它移到树的叶端。
    (3) 对每一个投影利用等价变换规则3,5,10,11中的一般形式尽可能把它移向树的叶端。
    注意:
    等价变换规则3使一些投影消失
    规则5把一个投影分裂为两个,其中一个有可能被移向树的叶端
    (4) 利用等价变换规则3~5把选择和投影的串接合并成单个选择、单个投影或一个选择后跟一个投影。使多个选择或投影能同时执行,或在一次扫描中全部完成
    (5) 把上述得到的语法树的内节点分组。每一双目运算(×, ,∪,-)和它所有的直接祖先为一组(这些直接祖先是(σ,π运算)。
    如果其后代直到叶子全是单目运算,则也将它们并入该组
    但当双目运算是笛卡尔积(×),而且后面不是与它组成等值连接的选择时,则不能把选择与这个双目运算组成同一组,把这些单目运算单独分为一组

    例[5] 查询语句:检索学习课程名为MATH的女学生学号和姓名。
    该查询语句的关系代数表达式如下:
       πS#,SNAME(σCNAME=’MATH’∧SEX=’F’(C   SC   S))
    上式中, 符号用π、σ、×操作表示,可得下式
     πS#,SNAME(σCNAME=’MATH’∧SEX=’F’(πL
              (σC.C# = SC.C#∧SC.S# = S.S#(C×SC×S))))
    此处L是C、SC、S中全部属性,去除重复属性。
    
    展开全文
  • Oracle数据库是一种使用广泛的数据库系统,在实际应用中由于种种原因,不能使数据库的...本文从对数据库磁盘I/O优化、对SQL语句的优化等方面进行了简单的分析,通过一定的优化手段,可使数据库的性能得到进一步的提高。
  • 关系数据库的查询优化

    千次阅读 2018-07-17 16:29:43
    关系查询优化是影响关系数据库管理系统性能的关键因素。 一、查询优化概述 查询优化的优点不仅在于用户不必考虑如何最好地表达查询以获得较高的效率,而且在于系统可以比用户程序的“优化”做得更好。 1. 优化...

    关系查询优化是影响关系数据库管理系统性能的关键因素。

    一、查询优化概述

    查询优化的优点不仅在于用户不必考虑如何最好地表达查询以获得较高的效率,而且在于系统可以比用户程序的“优化”做得更好。
    1. 优化可以从数据字典中获得更多统计信息。
    2. 如果数据库的物理统计信息改变了,系统可以自动对查询进行重新优化以选择相适应的执行计划。在非关系系统中则必须重写程序,而重写程序在实际应用中往往是不太可能的。
    3. 优化器可以考虑数百种不同的执行计划,而程序员一般只能考虑有限的几种可能性。
    4. 优化器中包括了很多复杂的优化技术,这些优化技术往往只有最好的程序员才能掌握。系统的自动优化相当于使得所有人都拥有这些优化技术。
    在集中式数据库中,查询执行开销主要包括磁盘存取块数(I/O代价)、处理机时间(CPU代价)以及查询的内存开销。在分布式数据库中还要加上通信代价,即:
    总代价=I/O代价+CPU代价+内存代价+通信代价。
    由于磁盘I/O操作涉及机械动作,需要时间与内存操作相比要高几个数量级,在计算查询代价时一般用查询处理读写的块数作为衡量单位。
    查询优化的总目标是选择有效的策略,求得给定关系表达式的值,使得查询代价较小,因为查询优化的搜索空间有时非常大,实际系统选择的策略不一定是最优的,而是较优的。

    二、代数优化

    代数优化是基于关系代数等价变换规则的优化方法。
    代数优化策略是通过对关系代数表达式的等价变换来提高查询效率。所谓关系代数表达式的等价是指用相同的关系代替两个表达式中相应的关系所得到的结果是相同的。两个关系表达式E1和E2是等价的。

    查询树的启发式优化

    典型的启发式规则:
    1. 选择运算应尽可能先做。
    2. 把投影运算和选择运算同时进行。如有若干投影和选择运算,并且它们都对同一个关系操作,则可以在扫描次关系的同时完成所有这些运算以避免重复扫描关系。
    3. 把投影同其前或后的双目运算结合起来,没有必要为了去掉某些字段而扫描一遍关系。
    4. 把某些选择同在它前面要执行的笛卡儿积结合起来成为一个连接运算,连接(特别是等值连接)运算要啊比同样关系上的笛卡儿积省很多时间。
    5. 找出公共子表达式。

    三、物理优化

    代数优化改变查询语句中操作的次序和组合,但不涉及底层的存取路径。物理优化就是要选择高效合理的操作算法或存取路径,求得优化的查询计划,达到查询优化的目标。
    选择的方法可以是:
    1. 基于规则的启发式优化。
    2. 基于代价估算的优化。
    3. 两者结合的优化方法。常常先使用启发式规则选取若干个较优的候选方案,减少代价估算的工作量,然后分别计算这些候选方案的执行代价,较快地选出最终的优化方法。

    基于启发式规则的存取路径选择优化

    选择操作的启发式规则
    对于小关系,使用全表顺序扫描,即使选择列上有索引。
    对于大关系,启发式规则有:
    1. 对于选择条件是“主码=值”的查询,查询结果最多是一个元组,可以选择主码索引,一般都关系数据库管理系统会自动建立主码索引。
    2. 对于选择条件是“非主属性=值”的查询,并且选择列上有索引,则要估算查询结果的元组数目,如果比例较小(<10%)可以使用索引扫描方法,否则还是使用全表顺序扫描。
    3. 对于选择条件是属性上的非等值查询或者范围查询,并且选择列上有索引,同样要估算查询结果的元组数目,如果选择率<10%可以使用索引扫描方法,否则使用全表顺序扫描。
    4. 对于用AND连接的合取选择条件,如果有涉及这些属性的组合索引,则优先采用组合索引扫描方法;如果某些属性上有一般索引,则可以使用索引扫描方法,否则使用全表顺序扫描。
    5. 对于OR连接的析取选择条件,一般使用全表顺序扫描。

    基于代价估算的优化

    启发式规则优化是定性的选择,比较粗糙,但是实现简单而且优化本身的代价较小,适合解释执行的系统。因为解释执行的系统,其优开销包含在查询总开销之中,在编译执行的系统中,一次编译优化,多次执行,查询优化和查询执行是分开的,因此,可以用精细复杂一些的基于代价的优化方法。

    四、查询计划的执行

    查询优化完成后,关系数据库管理系统为用户查询生成了一个查询计划。该计划的执行可以分为自顶向下和自底向上两种执行方法。
    1. 自顶向下
    系统反复向查询计划顶端的操作符发出需要查询结果元组的请求,操作符收到请求后,就试图计算下一个(几个)元组并返回这些元组。在计算时,如果操作符的输入缓冲区为空,它就会向其孩子操作符发送需求元组的请求……这种需求元组的请求会一直传到叶子节点,启动叶子操作符运行,并返回其父操作符一个(几个)元组,父操作符再计算自己的输出返回给上层操作符,直至顶端操作符。重复这一过程,直到处理完整个关系。
    2. 自底向上
    查询计划从叶结点开始执行,叶节点操作符不断地产生元组并将它们放入其输出缓冲区中,直到缓冲区填满为止,这时它必须等待其父操作符将元组从该缓冲区中取走才能急促执行,然后其父结点操作符开始执行,利用下层的输入元组来产生它自己的输出元组,直到其输出缓冲区满为止,重复这个过程,直到产生所有的输出元组。

    向下的执行方式是一种被动的、需求驱动的执行方式。而自底向上的执行方式是一种主动的执行方式。

    总结:
    对于比较复杂的查询,尤其是涉及连接和嵌套的查询,不要把优化的任务全部放在关系数据库管理系统上,应该找出关系数据库管理系统的优化规律,以写出适合关系数据库管理系统自动优化的SQL语句,对于关系数据库管理系统不能优化的查询需要重写查询语句,进行手工调整以优化性能。

    展开全文
  • 关系数据库系统及其查询优化
  • 从含义与优势方面探讨了关系数据库语言SQL概况,论述了基于关系数据库SQL的查询方法,提出了基于关系数据库SQL的优化技术:更新系统统计信息、优化系统产品设计、合理使用系统索引、避免或简化排序、使用存储过程、改进...
  • 关系数据库优化操作

    千次阅读 2016-12-23 01:09:28
    目前使用率最多的数据库均为"关系数据库",例如:oracle,MySql... 1.关系数据库的瓶颈: 高并发读写需求-----------针对网站类用户的并发性访问非常高,而一台数据库的最大连接数有限,且硬盘I/O有限,其不能满足很多...

    目前使用率最多的数据库均为"关系型数据库",例如:oracle,MySql...

    1.关系型数据库的瓶颈:

    高并发读写需求-----------针对网站类用户的并发性访问非常高,而一台数据库的最大连接数有限,且硬盘I/O有限,其不能满足很多人同时连接

    海量数据的高效率读写-------当表中数据量太大,每次的读写速率都将非常缓慢(解决方案:分表.分库)

    高扩展性和可用性------一台数据库服务器扩充到多台时,不下电情况很难做到,当服务器挂了,业务都将中断

    故而,需要对数据库进行优化!

    2.MySql数据库的优化技术

    第一步:定位慢查询Sql

    1.      先关闭mysql服务—在服务中中止

    2.      开启慢查询并把慢查询记录到日志中:           在mysql的安装目录下执行命令: bin\mysqld.exe--safe-mode --slow- query-log    

    3.      查看mysql安装目录下my.ini文件中datadir(ctrl+f)的路径值所对应的目录下是否有生成有生成当前时间下后缀为.log 的文件,若有,表示成功(新生成的三个文件不能删除)

    4. 进行测试:   先设置慢查询时间---->查看慢查询时间--->执行需要测试sql语句--->查看是否有慢查询语句(若有,可以在.log日志文件中查看),如图:

    注意:通过 show  session/global status(找出) ,其中session是默认值,一般都用global

    开发中,测试的时机为:自检/测试功能的时候开启慢查询

    查询语句: .log中

    第二步:分析该Sql效率地的原因

    explain(通过 explain 语句可以分析,mysql如何执行你的sql语句)分析,如下图:

    第三步:结构设计优化

    一.表机构设计

    1.范式 3范式规范与反3NF设计,具体视情况而定

    备注:三范式指:1.表的列具有原子性,不可再分解;2.建立主键,实现记录唯一性;3.不能出现冗余数据,特殊情况可建立外键

    2.选择合适的存储引擎(myisam+INNODB+memory)

    当表对事务要求不高,主要是以查询和添加为主的时候,一般用myisam,实例:bbs中的发帖表.回复表

    反之对事务要求高,保存的数据都是重要数据的时候,则使用INNODB,例如:订单表.账号表

    数据变化频繁,不需要入库,同时又频繁的查询和修改,一般用memory,速度极快

    备注:MyISAM和INNODB的区别:

    1.MyISAM不支持事务,INNODB支持

    2.MyISAM查询和添加速度较快(底层类似链表,可以采用二叉树查询)

    3.MyISAM支持全文索引,INNODB不支持

    4.锁机制不同,MyISAM是表锁,INNODB是行锁

    5.MyISAM不支持外键,INNODB支持

    设置/修改存储引擎语法:

    Create table 表名(字段列表) engine存储引擎名称;

    例如:Create table employee(id  int ,name varchar) engine myisam;

    二.索引:索引(Index)是帮助DBMS高效获取数据数据结构

    1.索引算法: 
    备注: 1.btree代表二叉树算法
      2.hash就像Map,通过一个key直接就能找到value    
      3.FullText全文索引算法,只有myisam中有用,且只能对表中的文本区域(char vachar text)进行索引

    总结:使用索引把全表查找变为索引查找,减少查询次数,增加效率。而索引查找效率的取决于索引算法。也就是索引(Index)是帮助DBMS高效获取数据的数据结构

    2.索引的---添加/修改/删除/查询

    1):mysql中索引的分类:

    普通索引:允许重复的值出现,可以再任何字段上面添加

    唯一索引:除了不能有重复的记录外,其它和普通索引一样,可以在值是唯一的字段添加(用户名、手机号码、身份证、email,QQ),可以为null,并且可以有多个null

    主键索引:是随着设定主键而创建的,也就是把某个列设为主键的时候,数据库就会給改列创建索引。这就是主键索引.唯一且没有null值

    全文索引:用来对表中的文本域(char,varchar,text)进行索引,全文索引针对MyISAM有用

    2):添加索引:

    普通索引的创建,是先创建表,然后在创建普通索引.

    唯一索引:除了不能有重复的记录外,其它和普通索引一样

    主键索引:是随着设定主键而创建的,也就是把某个列设为主键的时候,数据库就会給改列创建索引。这就是主键索引.唯一且没有null值

    全文索引:用来对表中的文本域(char,varchar,text)进行索引,全文索引针对MyISAM有用,如下图

    //创建表,同时设置fulltext字段----------------------------全文检索案例
         CREATE TABLE articles2 (
           id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
           title VARCHAR(200),
           body TEXT,
           fulltext(title,body)
         )engine=myisam charset utf8;
    //添加数据
    INSERT INTO articles2 (title,body) VALUES   
         ('MySQL Tutorial','DBMS stands for DataBase ...'),
         ('How To Use MySQL Well','After you went through a ...'),
         ('Optimizing MySQL','In this tutorial we will show ...'),
         ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
         ('MySQL vs. YourSQL','In the following database comparison ...'),
         ('MySQL Security','When configured properly, MySQL ...');
    
    EXPLAIN SELECT * FROM articles2 WHERE title LIKE '%MySql%';//模糊查询---不支持全文检索
    
    explain select * from articles2 where match(title,body) against('database');//正确方式--能支持全文检索
    3):查询

    语法:show INDEX from 表名;

    4):删除 

    语法:ALTER TABLE 表名 drop INDEX 索引名;//删除索引  

    5):修改

    流程:先删除后添加=修改

    3:索引的代价是:

    1).占用磁盘空间

    2):对dml(删除/添加/修改)操作有影响,因为要维护索引,效率变低.

    4.使用场景:较为频繁的作为查询条件的字段

    a.肯定在where条件经常使用

    b: 该字段的内容不是唯一的几个值(sex)

    c: 字段内容不是频繁变化.

    5):总结:普通索引(单列索引):该索引只在一个列上面创建

    复合索引(多列索引):该索引只在多个列上面创建

    6):使用小技巧:

    a:对于创建的多列索引(复合索引),不是使用的第一部分就不会使用索引。

    b:使用like的适合,若%在前面('%aaa')不会使用索引,反之('aa%'),则会使用索引

    c:使用or的适合,必须所有的条件都使用索引此sql才会使用索引

    d:如果列类型是字符串,那一定要在条件中将数据使用引号引用起来。否则不使用索引

    e:如果mysql估计使用全表扫描要比使用索引快,则不使用索引。

    .分表 :分为 水平分割()和垂直分割(列)

    1.水平分表

    表中数据量巨大时,我们要经常查询。则可以按照合适的策略拆分为多张小表。尽量在单张表中查询,减少扫描次数

    2.垂直分表

    表记录数并不多,但是字段却很长,表占用空间很大,检索表时需要执行大量I/O,严重降低了性能。这个时候需要把大的字段拆分到另一个表,并且该表与原表是一对一的关系(外键)。 (JOIN)


    四.分区:将数据分段划分在多个位置存放,可以是同一块磁盘也可以在不同的机器

    MySQL 5.1 中新增了分区(Partition)功能,优势也越来越明显了:

    --与单个磁盘或文件系统分区相比,可以存储更多的数据

    --很容易删除不用或者过时的数据

    一些查询可以得到极大的优化可以并发查询

    --涉及到 SUM()/COUNT()等聚合函数时,可以并发进行

    --IO吞吐量更大

    五.sql优化小技巧(批量处理)

    DDL(数据库定义语言):使用场景, 一次性插入上百万条数据

    1.通过禁用索引来提供导入数据性能 。 这个操作主要针对有数据库的表,追加数据

    //去除键      alter table test3 DISABLEkeys;

    //批量插入数据         insert into test3 select * from test;

    //恢复键            alter table test3 ENABLEkeys;

     变多次维护索引为一次维护。

    2 关闭唯一校验

    set unique_checks=0  关闭

    set unique_checks=1  开启

    变多次唯一性校验为一次校验。

    3.修改事务提交方式(导入)

    set autocommit=0   关闭自动提交

    //批量插入

    set autocommit=1   开启

    变多次数据库事务为一次提交

    DML(增加/修改/删除)

    1.将多条增/删/改优化成一条sql

    六.集群:主从同步(日志),读写分离,主备切换

    实现步骤一: 主从复制

    一、准备主从mysql

    1、分别构造主、从数据库并输出日志(方便定位问题)

          拷贝

         改端口

         拷贝数据

    拷贝原理数据库的data.dir mysql到模拟的主从数据库

          配置日志路径

        2、安装及启动:

          mysqld --install MySQLXY --defaults-file="C:\ProgramFiles\MySQL\MySQL Server X.Y\my.ini"

          net start MySQLXY

          sc delete master

    测试

    二、master服务器配置:

             1、修改master方的mysql.ini :

                         log-bin=mysql-bin

                         server-id=1

                         innodb_flush_log_at_trx_commit=1

                         sync_binlog=1

                         binlog_ignore_db=mysql

                         binlog_checksum=none

             2、重启master服务,登录

    3、授权savle服务器的使用的账号及权限

                   场景:

                      master主服务器: 192.168.1.101

                      slave从服务器     :  192.168.1.102

                1) 授权给slave数据库服务器192.168.10.131(master用户,只对slave服务器开放)

                     语法为:GRANT REPLICATION SLAVE ON *.* to '用户名'@'192.168.0.102'identified

                            by ‘密码’;

                     Mysql>  GRANT REPLICATION SLAVE ON *.* to'yhptest'@'192.168.77.128'identified

                             by 'admin';

                             参数说明:

                                      yhptest:slave连接master使用的账号

                                      IDENTIFIED BY 'admin' :slave连接master使用的密码

                                      192.168.77.128:slave IP

                   

                  2)查询主数据库状态

                       Mysql> show master status;

               +--------------------+----------+---------------+------------------------------+

                | File                            | Position  | Binlog_Do_DB | Binlog_Ignore_DB |

               +--------------------+----------+---------------+------------------------------+

                | mysql-bin.000002  | 226         |                          |       mysql  |

               +--------------------+----------+---------------+------------------------------+

                 记录File  和 Position的值,在slave端使用

    三、slave服务器配置

            1、修改slave服务器的配置文件my.ini将 server-id = 1修改为 server-id = 10,并确保这个

               ID没有被别的MySQL服务所使用。

            2、启动slave服务器,登录

           3、在slave端,配置master链接信息 (执行语句)

               1) 配置

    Mysql>  change master to

                       master_host='192.168.77.1',  #master IP

                        master_user='yhptest',          #master数据库通过GRANT授权的账号

                       master_password='admin',        #master数据库通过GRANT授权的密码

                        master_port=3307,              #master数据库的密码

                       master_log_file='mysql-bin.000001',

                                  #master数据库中通过show master status显示的File名称

                       master_log_pos=296        

                                   #master数据库的通过show master status显示的Position的值

                 2) (重启)连接

                 Mysql> start slave;

     

                 3)主从同步检查

                show slave status;

                 其中Slave_IO_Running 与Slave_SQL_Running 的值都必须为YES,才表明状态正常。

          4、 测试

              1) 在master上,建库、建表、添加数据

               2) 刷新slave库,记录也存在

    由此,整个MySQL主从复制的过程就完成了,接下来,我们进行MySQL读写分离的安装与配置

    如果主服务器已经存在应用数据,则在进行主从复制时,需要做以下处理:

         (1)主数据库进行锁表操作,不让数据再进行写入动作

               mysql> FLUSH TABLES WITH READLOCK;

         (2)查看主数据库状态

               mysql> show master status;

        (3)复制数据文件

               将主服务器的数据文件(整个/opt/mysql/data目录)复制到从服务器,建议通过tar归档压         

               缩后再传到从服务器解压。

        (4)取消主数据库锁定

              mysql> UNLOCK TABLES; 

    步骤二------------------读写分离

    使用MySQL Proxy实现读写分离

    在此使用配置文件的方式来进行配置。

    配置文件mysql-proxy.conf中的内容主要包括:

    [mysql-proxy]

    admin-username=root

    admin-password=123456

    admin-lua-script=C:/mysql-proxy/lib/mysql-proxy/lua/admin.lua

    proxy-backend-addresses=192.168.1.101:3306

    proxy-read-only-backend-addresses=192.168.1.102:3306

    proxy-lua-script=C:/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua

    log-file=C:/mysql-proxy/log/mysql-proxy.log

    log-level=debug

    daemon=true

    keepalive=true

     

    执行命令:

    mysql-proxy -P 192.168.5.100:4040--defaults-file=E:/mysql-proxy/bin/mysql-proxy.conf

    查看日志文件mysql-proxy.log:

    2015-10-19 16:27:40: (critical)plugin proxy 0.8.5 started

    2015-10-19 16:27:40: (debug) maxopen file-descriptors = 512

    2015-10-19 16:27:40: (message)proxy listening on port 192.168.174.133:4040

    2015-10-19 16:27:40: (message)added read/write backend: 192.168.174.130:3306

    2015-10-19 16:27:40: (message)added read-only backend: 192.168.174.131:3306

    出现以上日志信息则表示MySQL Proxy启动成功,此时便可以实现读写分离了。

     

    注意:由于rw-splitting.lua中的min_idle_connections的默认值为4,即当会话数达到最小为4时,才会进行读写分离,在此我们将其改为1,则可直接进行读写分离。

    后端主机需要创建授权帐号

    mysql> GRANT ALL ON *.* TOmike@'192.168.%.%' IDENTIFIED BY '321';

    mysql> FLUSH PRIVILEGES;

    七.MySql使用小技巧

    1.修改密码

    1)、停掉mysql服务

    2)、在mysql.ini下,添加“skip-grant-tables”配置(mysqld这样可以去掉登录时的密码验证)

    3)、重启mysql

    4)、在cmd下输入mysql -uroot -P3306 ,无密码登录

    5)、use mysql;

    6)、set password=password(‘123’)  --error

           update user setpassword=password('admin') where user='root' and host='localhost';

    7)、重启,或直接flushprivileges;

    2.随机获取行(rand函数)---可用于抽奖

    order by rand() limit 1//随机获取一行

    八.分布式: 按业务分要访问那个集群.

    比如:dao层中的订单相关业务就访问订单DB集群

    商品相关业务就访问商品DB集群

    九.缓存..略..见Redis

    展开全文
  • 关系数据库原理与Mysql使用优化关系数据库原理与Mysql使用优化
  • 数据库优化 - 实例优化

    千次阅读 2019-10-25 10:30:00
    从网上去搜数据库优化基本都是从SQL层次进行优化的,很少有提及到数据库本身的实例优化。就算有也都是基于某个特定数据库的实例优化,本文涵盖目前市面上所有主流数据库的实例优化(Oralce、MySQL、POSTGRES、达梦)...
    从网上去搜数据库优化基本都是从SQL层次进行优化的,很少有提及到数据库本身的实例优化。就算有也都是基于某个特定数据库的实例优化,本文涵盖目前市面上所有主流数据库的实例优化(Oralce、MySQL、POSTGRES、达梦),按照文章的配置能够将你数据库性能用到80%或以上。

    数据库优化方法论

    这部分为理论知识,不感兴趣的同学可以直接跳到后面参数配置部分。

    数据库优化目标

    根据角色的不同,数据库优化分为以下几个目标:

    • 业务角度(关键用户): 减少用户页面响应时间

    • 数据库角度(开发): 减少数据库SQL响应时间

    • 数据库服务器角度(运维): 充分使用数据库服务器物理资源 减少数据库服务器CPU使用率 减少数据库服务器IO使用率 减少数据库服务器内存使用率

    数据库优化指标

    • SQL平均响应时间变短

      • 优化前:数据库平均响应时间500ms

      • 优化目标:数据库平均响应时间200ms

    • 数据库服务器CPU占用率变少

      • 优化前:数据库高峰期CPU使用率70%

      • 优化目标:数据库高峰期CPU使用率50%

    • 数据库服务器IO使用率变低

      • 优化前:数据库IO WAIT为30%

      • 优化目标:数据库IO WAIT低于10%

    数据库优化误区

    在进行数据库优化的时候可能会有以下几个误区:

    • 优化之前一定要深入了解数据库内部原理 优化是有“套路”的,照着这些“套路”你也可以很好的完成数据库优化

    • 不断调整数据库参数就可以最终实现优化 有时候设计不合理怎么调整参数都不行

    • 不断调整操作系统参数就可以最终实现优化 同上

    • 数据库性能由应用、数据库架构决定,与应用开发关系不大 恰恰相反,应用开发的关系很大

    • 必须要做读写分离,必须要弄分库分表 数据量级只有达到一定的比例才有必要做读写分离,分表分库,否则徒增复杂度。一般来说Oracle的单表量级可以达到1亿,MySQL到1000万~2000万

    数据库优化流程

    完整的数据库优化流程如下:

    640?wx_fmt=png

    首先需要尽可能的了解优化问题,收集问题期间系统信息并做好存档。根据当前系统问题表现制定优化目标并与客户沟通目标达成一致;通过一系列工具分析系统问题,制定优化方案,方案评审完成后由各负责人员进行实施。若达到优化目标则编写优化报告,否则需要重新制定优化方案。

    数据库实例优化

    数据库实例优化遵循三句口诀:日志不能小、缓存足够大、连接要够用。

    数据库事务提交后需要将事务对数据页的修改刷( fsync)到磁盘上,才能保证数据的持久性。这个刷盘,是一个随机写,性能较低,如果每次事务提交都要刷盘,会极大影响数据库的性能。数据库在架构设计中都会采用如下两个优化手法:

    • 先将事务写到日志文件RedoLog(WAL),将随机写优化成顺序写

    • 加一层缓存结构Buffer,将每次写优化成批量写

    所以日志跟缓存对数据库实例尤其重要。而连接如果不够用,数据库会直接抛出异常,系统无法访问。

    数据库参数优化

    主流数据库架构都有如下的共同点:

    • 数据缓存

    • SQL解析区

    • 排序内存

    • REDO及UNDO

    • 锁、LATCH、MUTEX

    • 监听及连接

    • 文件读写性能

    接下来我们根据不同的数据库调整参数以使数据库达到最佳性能。

    ORACLE

    参数分类参数名参数值备注
    数据缓存SGA_TAGET、MEMORY_TARGET物理内存70-80%越大越好
    数据缓存DB_CACHE_SIZE物理内存70-80%越大越好
    SQL解析SHARED_POOL_SIZE4-16G不建议设置过大
    监听及连接PROCESSES、SESSIONS、OPEN_CURSORS根据业务需求设置一般为业务预估连接数的120%
    其他SESSION_CACHED_CURSORS大于200软软解析

    MYSQL(INNODB)

    参数分类参数名参数值备注
    数据缓存INNODB_BUFFER_POOL_SIZE物理内存50-80%一般来说越大性能越好
    日志相关Innodb_log_buffer_size16-32M根据运行情况调整
    日志相关sync_binlog1、100、01安全性最好
    监听及连接max_connections根据业务情况调整可以预留一部分值
    文件读写性能innodb_flush_log_at_trx_commit2安全和性能的折中考虑
    其他wait_timeout,interactive_timeout28800避免应用连接定时中断

    POSTGRES

    参数分类参数名参数值备注
    数据缓存SHARED_BUFFERS物理内存10-25% 
    数据缓存CACHE_BUFFER_SIZE物理内存50-60% 
    日志相关wal_buffer8-64M不建议设置过大过小
    监听及连接max_connections根据业务情况调整一般为业务预估连接数的120%
    其他maintenance_work_mem512M或更大 
    其他work_mem8-16M原始配置1M过小
    其他checkpoint_segments32或者更大 

    达梦数据库

    参数分类参数名参数值备注
    数据缓存MEMROY_TARGET、MEMROY_POOL物理内存90% 
    数据缓存BUFFER物理内存60%数据缓存
    数据缓存MAX_BUFFER物理内存70%最大数据缓存
    监听及连接max_sessions根据业务需求设置一般为业务预估连接数的120%

    总结

    数据库的优化手法太多太多,有换磁盘阵列升级硬件,有改写SQL脚本添加索引,还有数据库参数调整优化性能,甚至还可以调整数据库架构。本文从数据库本身参数进行调优,大家根据上面几张表中的参数进行调整基本能达到数据库最佳性能的80%。

     

    640?wx_fmt=jpeg

     

    更多内容请访问:www.javadaily.cn

    展开全文
  • 关系数据库的查询与优化,对于数据库中的关系运算,进一步加深理解!
  • MySQL数据库优化的八种方式(经典必看)

    万次阅读 多人点赞 2019-03-13 15:48:28
    MySQL数据库优化的八种方式(经典必看) 引言: 关于数据库优化,网上有不少资料和方法,但是不少质量参差不齐,有些总结的不够到位,内容冗杂 偶尔发现了这篇文章,总结得很经典,文章流量也很大,所以拿...
  • 随着信息技术的快速发展,查询优化是查询处理过程中必须考虑的重要...通过对大型数据库查询语句执行过程的讨论,提出了相应的优化设计方法。实践证明,这种优化过程简单、方便、有效,可提高查询响应速度,改善数据库性能。
  • [数据库]关系数据库的查询优化

    千次阅读 2014-11-17 15:02:35
    查询优化的最终目的是为了提高数据库系统的性能,但查询为什么在效率上会有差别呢?对于SQL查询,相同的 表存在着不同的存取方法,两个表的连接存在着不同的连接 方法,多个表的连接也存在着不同的连接次序.因此...
  • 关系数据库SQL语句的设计优化研究.pdf
  • 关系数据库查询优化分析

    千次阅读 2008-01-07 12:52:00
    因此人们往往通过对查询语句进行优化来提高整个数据库的性能。举例来说,如果一个数据库表信息积累到上百万甚至是上千万条记录,全表扫描一次需要数十分钟,甚至数小时;但如果采用比全表扫描更好的查询策略,往往...
  • 全面深入Mysql数据库优化

    千人学习 2019-09-26 11:44:58
    课程简介 MySQL 是最流行的关系型数据库管理系统,在 WEB 应用方面 MySQL 是最好的 RDBMS(关系数据库管理系统)应用软件之一, 广泛的应用在各个领域。 本课程作为MySQL高级课程, 主要讲解了MySQL中的视图/存储过程/...
  • 数据库优化思路

    万次阅读 2016-05-04 00:29:48
    最近在学习后端,弄到数据库这一块,一直听到数据库优化,下午在公司老师提了下,现在记录下,大体的方法。首先: 最根本的是优化MYSQL的 一些配置参数,因为MYSQL原生只支持,数十个并发访问。要是数量级是万级百万...
  • 基于关系数据库的SQL查询语句的优化.pdf
  • 数据库优化

    千次阅读 2018-07-06 18:01:35
    出处:https://www.cnblogs.com/easypass/archive/2010/12/08/1900127.html1.数据库访问优化法则要正确的优化SQL,我们需要快速定位能性的瓶颈点,也就是说快速找到我们SQL主要的开销在哪里?而大多数情况性能最慢的...
  • Android 性能优化之数据库优化(一)

    千次阅读 2016-09-12 10:26:41
    Android 性能优化之数据库优化(一)
  • 数据库SQL优化大总结之 百万级数据库优化方案

    万次阅读 多人点赞 2016-06-23 09:43:50
    网上关于SQL优化的教程很多,但是比较杂乱。近日有空整理了一下,写出来跟大家分享一下,其中有错误和不足的地方,还请大家纠正补充。 这篇文章我花费了大量的时间查找资料、修改、排版,希望大家阅读之后,感觉好的...
  • 1.对查询进行优化,要尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。 2、应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描 3、应尽量避免...
  • 只要是关系数据库都自动满足第一范式。  数据库的分类:  关系数据库:MySQL/ORACLE/Sql Server/DB2等  非关系数据库:特点是面向对象或者集合  nosql数据库:MongoDB(特点是面向文档)  2、第二范式...
  • 数据库优化方案

    千次阅读 2013-09-22 10:03:59
    最近研究了下数据库优化方案,对于分库分表有了些最基本的了解,写下这边文章,以此纪念 一、存储 1、垂直切分 首先将数据库DB中的数据根据业务的关系划分,或者说表之间的关系,垂直划分成3个数据库DB1、DB2、DB...
  • 数据库技术是计算机信息系统和应用系统的重要基础。基于开发人员在关系型数据库...本文主要从索引优化的角度出发,提升了SQL检索的效率,并且归纳出SQL检索优化的常用方法,然后总结了SQL数据库优化过程中的注意事项。
  • MySql数据库优化

    千次阅读 2020-01-21 11:36:50
    数据库优化,是一种综合性的技术,不是通过某一种方式让数据库效率提高很多,而是通过各个方面的优化,来是数据库效率明显的稳步的提高。 主要包括以下: 1、库表的设计优化(三种范式) 2、库表添加合适的索引...
  • oracle数据库优化

    万次阅读 2013-05-16 09:20:05
    oracle数据库优化 转自:http://www.cnblogs.com/lodestar/archive/2007/04/19/719933.html  这是以前在程序员上发表的一篇文章 1 概述  数据库是信息系统中核心的部分,数据库的高效性、安全性...
  • 优化方案 9、应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。 案例分析 优化方案 10.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 336,417
精华内容 134,566
关键字:

关系数据库优化