精华内容
下载资源
问答
  • 数据库优化手段

    千次阅读 2017-08-14 09:55:58
    程序员级别的优化有哪些手段?  (1)数据库的设置:如果你的数据库记录数不会超过30万条?如果你的数据库记录超过100万条?该如何设置数据库?一个或多个?  (2)数据库表的设置:当你的某个数据库表记录超过...

    程序员级别的优化有哪些手段? 

    (1)数据库的设置:如果你的数据库记录数不会超过30万条?如果你的数据库记录超过100万条?该如何设置数据库?一个或多个? 
    (2)数据库表的设置:当你的某个数据库表记录超过100万级别,而且每天大量增长,这是一个不得不考虑的问题。如果你的系统浏览量很大,即使是30万条记录也是需要考虑的。 
    (3)索引的使用:索引可以大大提高数据库访问速度。什么时候用?哪些字段使用? 
    (4)存储过程的使用:存储过程终归是比较好的,但是如果需要维护成百上千的存储过程,未必是划算的工程。 
    (5)高效的分页技术:数据库记录分页列表是大量必须使用的基本技术,怎样的分页是快速的? 

    宗旨你需要从上述5个方面考虑数据库的优化。 

    什么时候需要数据库优化? 
    (1)编写代码之前; 
    (2)系统速度慢了的时候; 

    下面就是一些具体的优化技巧了。 

    (1)超大量记录数据库的优化技巧 

    如果你的数据库表记录有超过100万级别,而且不断增长中。可以采取两个手段: 
    第一:将数据库表拆分到不同的库中,比如 tblMEMBER 就可以拆分到 DB1 与 DB2 中去。 
    实际上,可以拆分到 DB001 ... DB100 甚至更多的库中间去。 
    DB1 与 DB2 最好不在一块硬盘上。 
    第二:如果更大量级的数据,则最好拆分到不同的数据库服务器中去。 

    数据库的拆分带来的是查询等操作的复杂性。简单地可以通过 hash 或者 按序号 匹配不同的数据库。复杂一些,应该设置一个独立的应用服务器(软件)协调其中的操作。 

    (2)中等量级数据库的优化技巧 

    所谓中等量级数据库是指数据库100万-500万条记录左右(单个数据库表)。这样的数据库为了提高访问(响应)速度,可以将表拆分到更小的表。比如 tblMEMBER 可以拆分为 tblMEMBER_00 ... tblMEMBER_99 。 
    这样可以保证每个表的记录数不超过50万,那速度是"相当"快了。 

    (3)避免使用视图(viewport)与关联 

    视图viewport与关联都是为了程序员处理相对复杂的数据管理提供方便的手段。万物有其利,必有其弊。视图和关联提高了编程效率,都会较大地影响数据库的访问效率(事实上并不像一般资料说介绍的的那样高效),因此如果是web应用,则建议一般不要使用视图与关联。 

    (4)不要忘记索引(index)也不要滥用索引(index) 

    索引是提高数据库效率的简单又高效的方法。只要是设置了数据库表(table),就不要忘记设置索引(index)。将索引设置在经常用于排序的字段上,其他字段就不要设置了。 
    索引不是越多越好,也不是什么字段都适合建立索引的。数据重复性太多的字段不要设置索引。比如 tblMEMBER 的 iSex 字段只有 0 1 两个值,就不要设置索引。 

    (5)二进制的 text image 等字段应该单独设置别的表中 

    一般的数据库应用难免都需要保存比如描述、图片等信息;一般描述类信息用 text 字段,图片类信息用 image 字段;这里要说的是,不要将这些字段与其他字段放在一个表中。 
    比如: 

    1. tblMEMBER  
    2. id (int)  
    3. cName (varchar)(64)  
    4. cDescription (text)  
    5. bPhoto (image)  
    6. dDate (datetime)  
    7. 就应该拆分为3个表  
    8. tblMEMBER  
    9. id (int)  
    10. cName (varchar)(64)  
    11. dDate (datetime)  
    12. tblMEMBER_DESC  
    13. id (int)  
    14. cDescription (text)  
    15. dDate (datetime)  
    16. tblMEMBER_PHOTO  
    17. id (int)  
    18. bPhoto (image)  
    19. dDate (datetime)  

    (6)不要使用文本类型的 id 

    一般的数据库表都会以一个种子字段作为主键。可以在与不少年青的程序员朋友沟通过程中,发现他们很喜欢用字符串类型的作为系统的 id 号。 
    比如:id = XX XX XX XX 这样的字符串,每两个位置代表不同的类别等含义。 
    不知道是那本教材如此误人子弟,作出这样的表率 :< 
    作为系统的 id 号,一定要使用数字型的。 

    (7)数据库表table的字段field不要太多 

    本以为无需说明,也是发现不少的朋友,为了省事,一股脑把所有的相关字段都放在一个表中间。这样做的后果便是,程序写起来简单了,运行效率下来了。 
    无论字段多少,有两类字段是必须独立出去的:一是进程更新的字段,比如文章的点击次数字段iShow,二是二进制或者是text字段; 

    (8)将字符串(varchar)比较变成数字型(int)比较 

    每个系统都会有用户管理,其中必然有 昵称,密码,邮件等的字符串类型数据比较的问题。在数据库操作中,字符串比较的效率是相当低下的。因此遇到字符串的比较,必须将其转换为数字型比较。 
    具体做法是:在数据库表中增加相应的数字字段,比如 cNickname -> iNickNumber ,其中 iNickNumber 的数值为 cNickname 的 哈希值(如何计算字符串的哈希值?请参阅本站的其他文章)。 
    通过这样的转换,系统效率可以提高 100 倍哦!!! 

    (9)为每个数据库表(table)设置 datetime 字段 

    在许多情况下,很多的表是不需要 datetime 字段用于保存时间的。本文的建议是你应该为每个表都设置 datetime 字段,而且默认值为 getdate()。 
    我们的经验是,datetime 是实数,占用字节不多;在进行系统维护,远程备份等环节都会发挥意想不到的效果。 

    (10)适当使用存储过程(Stored Processing) 

    存储过程(sp)已经被大大地宣传了,本文也不例外地赞许采用存储过程。本文的建议是只在下列情况才使用存储过程:一是一个业务处理是事务,包含了多个处理过程;二是一种处理被高频使用,使用存储过程可以提高效率; 

    (11)使用高效的分页(ination)技术 

    数据库记录分页列表是大量必须使用的基本技术,因此本文建议你在每个数据库中建立下面的存储过程: 

    1. CREATE PROCEDURE xsp_ination  
    2. (  
    3. @tblName   varchar(64),                  
    4. @strGetFields varchar(256) = "*",   
    5. @fldName varchar(64)="",                  
    6. @PageSize   int = 20,                     
    7. @PageIndex  int = 1,                          
    8. @OrderType bit = 1,                       
    9. @strWhere  varchar(256) = ""      
    10. )  
    11. AS   
    12. BEGIN  
    13. declare @strSQL   varchar(1000)     
    14. declare @strTmp   varchar(110)       
    15. declare @strOrder varchar(400)     
    16. SET NOCOUNT ON  
    17. if @OrderType != 0  
    18.     begin  
    19.         set @strTmp = "<(select min"   
    20.         set @strOrder = " order by [" + @fldName +"] desc"   
    21.     end  
    22. else   
    23.     begin   
    24.         set @strTmp = ">(select max"   
    25.         set @strOrder = " order by [" + @fldName +"] asc"   
    26.     end   
    27. if @PageIndex = 1  
    28.     begin  
    29.         if @strWhere != ""     
    30.             set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ "  from " + @tblName + " where " + @strWhere + " " + @strOrder  
    31.         else   
    32.             set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ "  from "+ @tblName + " "+ @strOrder  
    33.     end  
    34. else   
    35.     begin  
    36.         set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ "  from "  
    37.                             + @tblName + " where [" + @fldName + "]" + @strTmp + "(["+ @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " ["+ @fldName + "] from " + @tblName + " " + @strOrder + ") as tblTmp)"+ @strOrder  
    38.         if @strWhere != ""   
    39.             set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ "  from "  
    40.                             + @tblName + " where [" + @fldName + "]" + @strTmp + "(["  
    41.                             + @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " ["   
    42.                             + @fldName + "] from " + @tblName + " where " + @strWhere + " "  
    43.                             + @strOrder + ") as tblTmp) and " + @strWhere + " " + @strOrder   
    44.     end  
    45. EXEC (@strSQL)  
    46. if @@error=0 return 1  
    47. SET NOCOUNT OFF  
    48. END  
    49. GO  


    使用方法是(C#): 

    1. sql = "EXEC [dbo].[xsp_ination] \"tblNEWS\",\"*\",\"id\",40," + pindex.ToString() + ",1,\"iType=" + type.ToString();  
    2. SqlDataReader sr = ExecuteReader(sql);  
    3. while (sr.Read())  
    4. {  
    5.    ...  
    6. }  
    7. sr.Close();  


    上面的优化技巧仅是一些常见的手段,如果你的系统(小系统就算了)遇到效率问题,可以与联高软件联系。 

     

    来源:http://www.legalsoft.com.cn/docs/968.html

     

     

    =============================================================

    本文首先讨论了基于第三范式的数据库表的基本设计,着重论述了建立主键和索引的策略和方案,然后从数据库表的扩展设计和库表对象的放置等角度概述了数据库管理系统的优化方案。 

    关键词: 优化(Optimizing) 第三范式(3NF) 冗余数据(Redundant Data) 索引(Index) 数据分割(Data Partitioning) 对象放置(Object Placement) 

    1 引言 

    数 据库优化的目标无非是避免磁盘I/O瓶颈、减少CPU利用率和减少资源竞争。为了便于读者阅读和理解,笔者参阅了Sybase、Informix和 Oracle等大型数据库系统参考资料,基于多年的工程实践经验,从基本表设计、扩展设计和数据库表对象放置等角度进行讨论,着重讨论了如何避免磁盘 I/O瓶颈和减少资源竞争,相信读者会一目了然。 

    2 基于第三范式的基本表设计 

    在基于表驱动的信息管理系统(MIS)中,基本表的 设计规范是第三范式(3NF)。第三范式的基本特征是非主键属性只依赖于主键属性。基于第三范式的数据库表设计具有很多优点:一是消除了冗余数据,节省了 磁盘存储空间;二是有良好的数据完整性限制,即基于主外键的参照完整限制和基于主键的实体完整性限制,这使得数据容易维护,也容易移植和更新;三是数据的 可逆性好,在做连接(Join)查询或者合并表时不遗漏、也不重复;四是因消除了冗余数据(冗余列),在查询(Select)时每个数据页存的数据行就 多,这样就有效地减少了逻辑I/O,每个Cash存的页面就多,也减少物理I/O;五是对大多数事务(Transaction)而言,运行性能好;六是物 理设计(Physical Design)的机动性较大,能满足日益增长的用户需求。 

    在基本表设计中,表的主键、外键、索引设计占有非常重要的地位,但系统设计人员往往只注重于满足用户要求,而没有从系统优化的高度来认识和重视它们。实际上,它们与系统的运行性能密切相关。现在从系统数据库优化角度讨论这些基本概念及其重要意义: 

     

    (1) 主键(Primary Key):主键被用于复杂的SQL语句时,频繁地在数据访问中被用到。一个表只有一个主键。主键应该有固定值(不能为Null或缺省值,要有相对稳定 性),不含代码信息,易访问。把常用(众所周知)的列作为主键才有意义。短主键最佳(小于25bytes),主键的长短影响索引的大小,索引的大小影响索 引页的大小,从而影响磁盘I/O。主键分为自然主键和人为主键。自然主键由实体的属性构成,自然主键可以是复合性的,在形成复合主键时,主键列不能太多, 复合主键使得Join*作复杂化、也增加了外键表的大小。人为主键是,在没有合适的自然属性键、或自然属性复杂或灵敏度高时,人为形成的。人为主键一般是 整型值(满足最小化要求),没有实际意义,也略微增加了表的大小;但减少了把它作为外键的表的大小。 

     

    (2)外键(Foreign Key):外键的作用是建立关系型数据库中表之间的关系(参照完整性),主键只能从独立的实体迁移到非独立的实体,成为后者的一个属性,被称为外键。 

     

    (3) 索引(Index):利用索引优化系统性能是显而易见的,对所有常用于查询中的Where子句的列和所有用于排序的列创建索引,可以避免整表扫描或访问, 在不改变表的物理结构的情况下,直接访问特定的数据列,这样减少数据存取时间;利用索引可以优化或排除耗时的分类*作;把数据分散到不同的页面上,就分散 了插入的数据;主键自动建立了唯一索引,因此唯一索引也能确保数据的唯一性(即实体完整性);索引码越小,定位就越直接;新建的索引效能最好,因此定期更 新索引非常必要。索引也有代价:有空间开销,建立它也要花费时间,在进行Insert、Delete和Update*作时,也有维护代价。索引有两种:聚 族索引和非聚族索引。一个表只能有一个聚族索引,可有多个非聚族索引。使用聚族索引查询数据要比使用非聚族索引快。在建索引前,应利用数据库系统函数估算 索引的大小。 

    ① 聚族索引(Clustered Index):聚族索引的数据页按物理有序储存,占用空间小。选择策略是,被用于Where子句的列:包括范围查询、模糊查询或高度重复的列(连续磁盘扫 描);被用于连接Join*作的列;被用于Order by和Group by子句的列。聚族索引不利于插入*作,另外没有必要用主键建聚族索引。 

    ② 非聚族索引(Nonclustered Index):与聚族索引相比,占用空间大,而且效率低。选择策略是,被用于Where子句的列:包括范围查询、模糊查询(在没有聚族索引时)、主键或外 键列、点(指针类)或小范围(返回的结果域小于整表数据的20%)查询;被用于连接Join*作的列、主键列(范围查询);被用于Order by和Group by子句的列;需要被覆盖的列。对只读表建多个非聚族索引有利。索引也有其弊端,一是创建索引要耗费时间,二是索引要占有大量磁盘空间,三是增加了维护代 价(在修改带索引的数据列时索引会减缓修改速度)。那么,在哪种情况下不建索引呢?对于小表(数据小于5页)、小到中表(不直接访问单行数据或结果集不用 排序)、单值域(返回值密集)、索引列值太长(大于20bitys)、容易变化的列、高度重复的列、Null值列,对没有被用于Where子语句和 Join查询的列都不能建索引。另外,对主要用于数据录入的,尽可能少建索引。当然,也要防止建立无效索引,当Where语句中多于5个条件时,维护索引 的开销大于索引的效益,这时,建立临时表存储有关数据更有效。 

    批量导入数据时的注意事项:在实际应用中,大批量的计算(如电信话单计费)用C 语言程序做,这种基于主外键关系数据计算而得的批量数据(文本文件),可利用系统的自身功能函数(如Sybase的BCP命令)快速批量导入,在导入数据 库表时,可先删除相应库表的索引,这有利于加快导入速度,减少导入时间。在导入后再重建索引以便优化查询。 

     

    (4)锁:锁是并行处理的重要机 制,能保持数据并发的一致性,即按事务进行处理;系统利用锁,保证数据完整性。因此,我们避免不了死锁,但在设计时可以充分考虑如何避免长事务,减少排它 锁时间,减少在事务中与用户的交互,杜绝让用户控制事务的长短;要避免批量数据同时执行,尤其是耗时并用到相同的数据表。锁的征用:一个表同时只能有一个 排它锁,一个用户用时,其它用户在等待。若用户数增加,则Server的性能下降,出现“假死”现象。如何避免死锁呢?从页级锁到行级锁,减少了锁征用; 给小表增加无效记录,从页级锁到行级锁没有影响,若在同一页内竞争有影响,可选择合适的聚族索引把数据分配到不同的页面;创建冗余表;保持事务简短;同一 批处理应该没有网络交互。 

     

    (5)查询优化规则:在访问数据库表的数据(Access Data)时,要尽可能避免排序(Sort)、连接(Join)和相关子查询*作。经验告诉我们,在优化查询时,必须做到: 

    ① 尽可能少的行; 

    ② 避免排序或为尽可能少的行排序,若要做大量数据排序,最好将相关数据放在临时表中*作;用简单的键(列)排序,如整型或短字符串排序; 

    ③ 避免表内的相关子查询; 

    ④ 避免在Where子句中使用复杂的表达式或非起始的子字符串、用长字符串连接; 

    ⑤ 在Where子句中多使用“与”(And)连接,少使用“或”(Or)连接; 

    ⑥ 利用临时数据库。在查询多表、有多个连接、查询复杂、数据要过滤时,可以建临时表(索引)以减少I/O。但缺点是增加了空间开销。 

    除非每个列都有索引支持,否则在有连接的查询时分别找出两个动态索引,放在工作表中重新排序。 

     

    3 基本表扩展设计 

    基 于第三范式设计的库表虽然有其优越性(见本文第一部分),然而在实际应用中有时不利于系统运行性能的优化:如需要部分数据时而要扫描整表,许多过程同时竞 争同一数据,反复用相同行计算相同的结果,过程从多表获取数据时引发大量的连接*作,当数据来源于多表时的连接*作;这都消耗了磁盘I/O和CPU时间。 

    尤其在遇到下列情形时,我们要对基本表进行扩展设计:许多过程要频繁访问一个表、子集数据访问、重复计算和冗余数据,有时用户要求一些过程优先或低的响应时间。 

    如何避免这些不利因素呢?根据访问的频繁程度对相关表进行分割处理、存储冗余数据、存储衍生列、合并相关表处理,这些都是克服这些不利因素和优化系统运行的有效途径。 

     

    3.1 分割表或储存冗余数据 

    分割表分为水平分割表和垂直分割表两种。分割表增加了维护数据完整性的代价。 

    水 平分割表:一种是当多个过程频繁访问数据表的不同行时,水平分割表,并消除新表中的冗余数据列;若个别过程要访问整个数据,则要用连接*作,这也无妨分割 表;典型案例是电信话单按月分割存放。另一种是当主要过程要重复访问部分行时,最好将被重复访问的这些行单独形成子集表(冗余储存),这在不考虑磁盘空间 开销时显得十分重要;但在分割表以后,增加了维护难度,要用触发器立即更新、或存储过程或应用代码批量更新,这也会增加额外的磁盘I/O开销。 

    垂 直分割表(不破坏第三范式),一种是当多个过程频繁访问表的不同列时,可将表垂直分成几个表,减少磁盘I/O(每行的数据列少,每页存的数据行就多,相应 占用的页就少),更新时不必考虑锁,没有冗余数据。缺点是要在插入或删除数据时要考虑数据的完整性,用存储过程维护。另一种是当主要过程反复访问部分列 时,最好将这部分被频繁访问的列数据单独存为一个子集表(冗余储存),这在不考虑磁盘空间开销时显得十分重要;但这增加了重叠列的维护难度,要用触发器立 即更新、或存储过程或应用代码批量更新,这也会增加额外的磁盘I/O开销。垂直分割表可以达到最大化利用Cache的目的。 

    总之,为主要过程分割表的方法适用于:各个过程需要表的不联结的子集,各个过程需要表的子集,访问频率高的主要过程不需要整表。在主要的、频繁访问的主表需要表的子集而其它主要频繁访问的过程需要整表时则产生冗余子集表。 

    注意,在分割表以后,要考虑重新建立索引。 

     

    3.2 存储衍生数据 

    对一些要做大量重复性计算的过程而言,若重复计算过程得到的结果相同(源列数据稳定,因此计算结果也不变),或计算牵扯多行数据需额外的磁盘I/O开销,或计算复杂需要大量的CPU时间,就考虑存储计算结果(冗余储存)。现予以分类说明: 

    若在一行内重复计算,就在表内增加列存储结果。但若参与计算的列被更新时,必须要用触发器更新这个新列。 

    若对表按类进行重复计算,就增加新表(一般而言,存放类和结果两列就可以了)存储相关结果。但若参与计算的列被更新时,就必须要用触发器立即更新、或存储过程或应用代码批量更新这个新表。 

    若对多行进行重复性计算(如排名次),就在表内增加列存储结果。但若参与计算的列被更新时,必须要用触发器或存储过程更新这个新列。 

    总之,存储冗余数据有利于加快访问速度;但违反了第三范式,这会增加维护数据完整性的代价,必须用触发器立即更新、或存储过程或应用代码批量更新,以维护数据的完整性。 

     

    3.3 消除昂贵结合 

    对 于频繁同时访问多表的一些主要过程,考虑在主表内存储冗余数据,即存储冗余列或衍生列(它不依赖于主键),但破坏了第三范式,也增加了维护难度。在源表的 相关列发生变化时,必须要用触发器或存储过程更新这个冗余列。当主要过程总同时访问两个表时可以合并表,这样可以减少磁盘I/O*作,但破坏了第三范式, 也增加了维护难度。对父子表和1:1关系表合并方法不同:合并父子表后,产生冗余表;合并1:1关系表后,在表内产生冗余数据。 

     

    4 数据库对象的放置策略 

    数据库对象的放置策略是均匀地把数据分布在系统的磁盘中,平衡I/O访问,避免I/O瓶颈。 

    ⑴ 访问分散到不同的磁盘,即使用户数据尽可能跨越多个设备,多个I/O运转,避免I/O竞争,克服访问瓶颈;分别放置随机访问和连续访问数据。 

    ⑵ 分离系统数据库I/O和应用数据库I/O。把系统审计表和临时库表放在不忙的磁盘上。 

    ⑶ 把事务日志放在单独的磁盘上,减少磁盘I/O开销,这还有利于在障碍后恢复,提高了系统的安全性。 

    ⑷ 把频繁访问的“活性”表放在不同的磁盘上;把频繁用的表、频繁做Join*作的表分别放在单独的磁盘上,甚至把把频繁访问的表的字段放在不同的磁盘上,把访问分散到不同的磁盘上,避免I/O争夺; 

    ⑸ 利用段分离频繁访问的表及其索引(非聚族的)、分离文本和图像数据。段的目的是平衡I/O,避免瓶颈,增加吞吐量,实现并行扫描,提高并发度,最大化磁盘 的吞吐量。利用逻辑段功能,分别放置“活性”表及其非聚族索引以平衡I/O。当然最好利用系统的默认段。另外,利用段可以使备份和恢复数据更加灵活,使系 统授权更加灵活。

    参考资料:http://www.xiaotianya.com/read-7-2026-1.html

    来源:http://www.cnblogs.com/shenpeng1024/archive/2008/03/17/1110642.html


    转载自:http://uule.iteye.com/blog/1499871

    展开全文
  • Django最为全面的数据库优化手段

    千次阅读 2019-09-12 15:26:03
    数据库访问优化 Django的数据库层提供了很多方法来帮助开发者充分的利用他们的数据库。这篇文档收集了相关文档的一些链接,添加了大量提示,并且按照优化数据库使用的步骤的概要来组织。 性能优先 作为通用的编程...

    数据库访问优化

    Django的数据库层提供了很多方法来帮助开发者充分的利用他们的数据库。这篇文档收集了相关文档的一些链接,添加了大量提示,并且按照优化数据库使用的步骤的概要来组织。

    性能优先

    作为通用的编程实践,性能的重要性不用多说。弄清楚你在执行什么查询以及你的开销花在哪里。你也可能想使用外部的项目,像django-debug-toolbar,或者直接监控数据库的工具。

    记住你可以优化速度、内存占用,甚至二者一起,这取决于你的需求。一些针对其中一个的优化会对另一个不利,但有时会对二者都有帮助。另外,数据库进程做的工作,可能和你在Python代码中做的相同工作不具有相同的开销。决定你的优先级是什么,是你自己的事情,你必须要权衡利弊,按需使用它们,因为这取决于你的应用和服务器。

    对于下面提到的任何事情,要记住在任何修改后验证一下,确保修改是有利的,并且足够有利,能超过你代码中可读性的下降。下面的所有建议都带有警告,在你的环境中大体原则可能并不适用,或者会起到相反的效果。

    使用标准数据库优化技巧

    ...包括:

    索引。在你决定哪些索引应该添加 之后,这一条具有最高优先级。使用Field.db_index或者Meta.index_together在Dhango中添加它们。考虑在你经常使用filter()、exclude()、order_by()和其它方法查询的字段上面添加索引,因为索引有助于加速查找。注意,设计最好的索引方案是一个复杂的、数据库相关的话题,它取决于你应用的细节。持有索引的副作用可能会超过查询速度上的任何收益。

    合理使用字段类型。

    我们假设你已经完成了上面这些显而易见的事情。这篇文档剩下的部分,着重于讲解如何以不做无用功的方式使用Django。这篇文档也没有强调用在开销大的操作上其它的优化技巧,像general purpose caching。

    理解查询集

    理解查询集(QuerySets) 是通过简单的代码获取较好性能至关重要的一步。特别是:

    理解查询集计算

    要避免性能问题,理解以下几点非常重要:

    QuerySets是延迟的。

    什么时候它们被计算出来。

    数据在内存中如何存储。

    理解缓存属性

    和整个QuerySet的缓存相同,ORM对象的属性的结果中也存在缓存。通常来说,不可调用的属性会被缓存。例如下面的博客模型示例:

    >>> entry = Entry.objects.get(id=1)
    >>> entry.blog   # Blog object is retrieved at this point
    >>> entry.blog   # cached version, no DB access
    

    但是通常来讲,可调用的属性每一次都会访问数据库。

    >>> entry = Entry.objects.get(id=1)
    >>> entry.authors.all()   # query performed
    >>> entry.authors.all()   # query performed again
    

    要小心当你阅读模板代码的时候 —— 模板系统不允许使用圆括号,但是会自动调用callable对象,会隐藏上述区别。

    要小心使用你自定义的属性 —— 实现所需的缓存取决于你,例如使用cached_property装饰符。

    使用with模板标签

    要利用QuerySet的缓存行为,你或许需要使用with模板标签。

    使用iterator()

    当你有很多对象时,QuerySet的缓存行为会占用大量的内存。这种情况下,采用iterator()解决。

    在数据库中而不是Python中做数据库的工作

    比如:

    在最基础的层面上,使用过滤器和反向过滤器对数据库进行过滤。

    使用F 表达式在相同模型中基于其他字段进行过滤。

    使用数据库中的注解和聚合。

    如果上面那些都不够用,你可以自己生成SQL语句:

    使用QuerySet.extra()

    extra()是一个移植性更差,但是功能更强的方法,它允许一些SQL语句显式添加到查询中。如果这些还不够强大:

    使用原始的SQL

    编写你自己的自定义SQL语句,来获取数据或者填充模型。使用django.db.connection.queries来了解Django为你编写了什么,以及从这里开始。

    用唯一的被或索引的列来检索独立对象

    有两个原因在get()中,用带有unique或者db_index的列检索独立对象。首先,由于查询经过了数据库的索引,所以会更快。其次,如果很多对象匹配查询,查询会更慢一些;列上的唯一性约束确保这种情况永远不会发生。

    所以,使用博客模型的例子:

    >>> entry = Entry.objects.get(id=10)
    

    会快于:

    >>> entry = Entry.object.get(headline="News Item Title")
    

    因为id被数据库索引,而且是唯一的。

    下面这样做会十分缓慢:

    >>> entry = Entry.objects.get(headline__startswith="News")
    

    首先, headline没有被索引,它会使查询变得很慢:

    其次,这次查找并不确保返回唯一的对象。如果查询匹配到多于一个对象,它会在数据库中遍历和检索所有这些对象。如果记录中返回了成百上千个对象,代价是非常大的。如果数据库运行在分布式服务器上,网络开销和延迟也是一大因素,代价会是它们的组合。

    一次性检索你需要的任何东西

    在不同的位置多次访问数据库,一次获取一个数据集,通常来说不如在一次查询中获取它们更高效。如果你在一个循环中执行查询,这尤其重要。有可能你会做很多次数据库查询,但只需要一次就够了。所以:

    使用QuerySet.select_related()和prefetch_related()

    充分了解并使用select_related()和prefetch_related():

    在视图的代码中,

    以及在适当的管理器和默认管理器中。要意识到你的管理器什么时候被使用和不被使用;有时这很复杂,所以不要有任何假设。

    不要获取你不需要的东西 使用QuerySet.values()和values_list()

    当你仅仅想要一个带有值的字典或者列表,并不需要使用ORM模型对象时,可以适当使用values()。对于在模板代码中替换模型对象,这样会非常有用 —— 只要字典中带有的属性和模板中使用的一致,就没问题。

    使用QuerySet.defer()和only()

    如果一些数据库的列你并不需要(或者大多数情况下并不需要),使用defer()和only()来避免加载它们。注意如果你确实要用到它们,ORM会在另外的查询之中获取它们。如果你不能够合理地使用这些函数,不如不用。

    另外,当建立起一个带有延迟字段的模型时,要意识到一些(小的、额外的)消耗会在Django内部产生。不要不分析数据库就盲目使用延迟字段,因为数据库必须从磁盘中读取大多数非text和VARCHAR数据,在结果中作为单独的一行,即使其中的列很少。 defer()和only()方法在你可以避免加载大量文本数据,或者可能要花大量时间处理而返回给Python的字段时,特别有帮助。像往常一样,应该先写出个大概,之后再优化。

    使用QuerySet.count()

    ...如果你想要获取大小,不要使用 len(queryset)。

    使用QuerySet.exists()

    ...如果你想要知道是否存在至少一个结果,不要使用if queryset。

    但是:

    不要过度使用 count() 和 exists()

    如果你需要查询集中的其他数据,就把它加载出来。

    例如,假设Email模型有一个body属性,并且和User有多对多的关联,下面的的模板代码是最优的:

    {% if display_inbox %}
      {% with emails=user.emails.all %}
        {% if emails %}
          <p>You have {{ emails|length }} email(s)</p>
          {% for email in emails %}
            <p>{{ email.body }}</p>
          {% endfor %}
        {% else %}
          <p>No messages today.</p>
        {% endif %}
      {% endwith %}
    {% endif %}
    

    这是因为:

    因为查询集是延迟加载的,如果‘display_inbox’为False,不会查询数据库。

    使用with意味着我们为了以后的使用,把user.emails.all储存在一个变量中,允许它的缓存被复用。

    {% if emails %}的那一行调用了QuerySet.bool(),它导致user.emails.all()查询在数据库上执行,并且至少在第一行以一个ORM对象的形式返回。如果没有任何结果,会返回False,反之为True。

    {{ emails|length }}调用了QuerySet.len()方法,填充了缓存的剩余部分,而且并没有执行另一次查询。

    for循环的迭代器访问了已经缓存的数据。

    总之,这段代码做了零或一次查询。唯一一个慎重的优化就是with标签的使用。在任何位置使用QuerySet.exists()或者QuerySet.count()都会导致额外的查询。

    使用QuerySet.update()和delete()

    通过QuerySet.update()使用批量的SQL UPDATE语句,而不是获取大量对象,设置一些值再单独保存。与此相似,在可能的地方使用批量deletes。

    但是要注意,这些批量的更新方法不会在单独的实例上面调用save()或者delete()方法,意思是任何你向这些方法添加的自定义行为都不会被执行,包括由普通数据库对象的信号驱动的任何方法。

    直接使用外键的值

    如果你仅仅需要外键当中的一个值,要使用对象上你已经取得的外键的值,而不是获取整个关联对象再得到它的主键。例如,执行:

    entry.blog_id
    

    而不是:

    entry.blog.id
    

    不要做无谓的排序

    排序并不是没有代价的;每个需要排序的字段都是数据库必须执行的操作。如果一个模型具有默认的顺序(Meta.ordering),并且你并不需要它,通过在查询集上无参调用order_by() 来移除它。

    向你的数据库添加索引可能有助于提升排序性能。

    整体插入

    创建对象时,尽可能使用bulk_create()来减少SQL查询的数量。例如:

    Entry.objects.bulk_create([
        Entry(headline="Python 3.0 Released"),
        Entry(headline="Python 3.1 Planned")
    ])
    

    ...更优于:

    Entry.objects.create(headline="Python 3.0 Released")
    Entry.objects.create(headline="Python 3.1 Planned")
    

    注意该方法有很多注意事项,所以确保它适用于你的情况。

    这也可以用在ManyToManyFields中,所以:

    my_band.members.add(me, my_friend)
    

    ...更优于:

    my_band.members.add(me)
    my_band.members.add(my_friend)
    

    ...其中Bands和Artists具有多对多关联。

    展开全文
  • 常见数据库优化方法

    千次阅读 2019-04-19 03:48:05
    对于后端开发人员来说,经常会和数据打交道,所以数据库优化很重要,今天总结下部分数据库优化知识。主要可以通过以下几种方式对数据库进行优化: 性能优化 表的设计合理化,符合三大范式(3NF) 1NF是对属性...

    对于后端开发人员来说,经常会和数据打交道,所以数据库的优化很重要,今天总结下部分数据库的优化知识。主要可以通过以下几种方式对数据库进行优化:

    性能优化

    • 表的设计合理化,符合三大范式(3NF)
      • 1NF是对属性的原子性约束,要求属性(列)具有原子性,不可再分解;(只要是关系型数据库都满足1NF)
      • 2NF是对记录的惟一性约束,要求记录有惟一标识,即实体的惟一性;
      • 3NF是对字段冗余性的约束,它要求字段没有冗余。 没有冗余的数据库设计可以做到。
    • 添加适当索引(index) [四种: 普通索引、主键索引、唯一索引unique、全文索引]
      • 较频繁的作为查询条件字段应该创建索引;
      • 唯一性太差的字段不适合单独创建索引,即使频繁作为查询条件;
      • 更新非常频繁的字段不适合创建索引
      • 不会出现在WHERE子句中的字段不该创建索引
    • 分表技术(水平分割、垂直分割);
    • 读写[写: update/delete/add]分离;
    • 存储过程 [模块化编程,可以提高速度];
    • 对mysql配置优化 [配置最大并发数my.ini, 调整缓存大小 ];
    • mysql服务器硬件升级;
    • 定时的去清除不需要的数据,定时进行碎片整理(MyISAM)。

    SQL语句优化

    • 通过show status命令了解各种SQL的执行频率;
    • 定位执行效率较低的SQL语句-(重点select;
    • 通过explain分析低效率的SQL;
    • 确定问题并采取相应的优化措施。

    添加索引

    • 索引主要可以分为以下几种:
      • 主键索引,主键自动的为主索引 (类型Primary);
      • 唯一索引 (UNIQUE);
      • 普通索引 (INDEX);
      • 全文索引 (FULLTEXT) [适用于MyISAM] ——》sphinx + 中文分词 coreseek [sphinx 的中文版 ];
      • 综合使用=>复合索引
    • 可能使用到索引
      • 对于创建的多列索引,只要查询条件使用了最左边的列,索引一般就会被使用。
      • 对于使用like的查询,查询如果是 ‘%aaa’ 不会使用到索引, ‘aaa%’ 会使用到索引。

     

    • 不使用索引
      • 如果条件中有or,即使其中有条件带索引也不会使用。
      • 对于多列索引,不是使用的第一部分,则不会使用索引。
      • like查询是以%开头
      • 如果列类型是字符串,那一定要在条件中将数据使用引号引用起来。否则不使用索引。(添加时,字符串必须’’)
      • 如果mysql估计使用全表扫描要比使用索引快,则不使用索引。

     

    • 作者:谭庆波
      链接:https://www.zhihu.com/question/36431635/answer/381557352
      来源:知乎
      著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。

    数据库优化分三个等级。

    第一级就是常见的简单DDL DML调优。SQL写的好不好啊,group by order by对不对啊,select别用*,记得要要hit index啊。数据库表设计好点啊,主键外键索引啊,执行计划看一看啊。市面上九成九的数据库调优都在这。

     

    第二级是DBA层级调优。MSSQL,ORACLE,MYSQL都各自有几十项配置。在不同的应用场景下需要针对性调整。比如MSSQL 的MAXDOP在有高并发且大SQL的情况下要适当调整限制,而全部是小SQL的情况下可以设置为无限。比如ORACLE的自动优化器在SQL性状相似的时候不开也没太大问题,CPU超过16个core的时候,parallel hint到底设置为几。这些都是学问。都没个定数,都是要DBA拿实际生产数据套研究的。oracle的AWR, entierprise manager, MSSQL的一堆管理SP,trace log,都是必读项。到这层,已经是每个公司每百程序员1,2个人的事情了。

     

    最后一级是数据库中间件和infra层级调优。上个月我处理过一个MSSQL集群死慢死慢,但是单node就没问题。后来查出来是windows 集群的仲裁设置有问题,导致太频繁failover,虽然failover号称无缝,但是实际上还是有10秒左右数据库无法访问的。这一级,基本上是千人以上的公司才会遇到的问题,也就几个人懂,但凡其中有2个人同时休假,就歇了

    作者:萝魏紫
    链接:https://www.zhihu.com/question/36431635/answer/489961019
    来源:知乎
    著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。

    展开全文
  • 面试求职:数据库常见面试题(数据库优化思路)

    万次阅读 多人点赞 2017-03-09 16:36:29
    面试回答数据库优化问题从以下几个层面入手:(1)、根据服务层面:配置mysql性能优化参数;(2)、从系统层面增强mysql的性能:优化数据表结构、字段类型、字段索引、分表,分库、数据库集群、读写分离等等。(3)...

    原文地址:http://www.2cto.com/database/201504/390838.html

    1. 主键 超键 候选键 外键

    主 键:

    数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键,且主键的取值不能缺失,即不能为空值(Null)。

    超 键:

    在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键,多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。

    候选键:

    最小超键,即没有冗余元素的超键。

    外 键:

    在一个表中存在的另一个表的主键称此表的外键。

    2.数据库事务的四个特性及含义

    数据库事务transanction正确执行的四个基本要素。ACID,原子性(Atomicity)、一致性(Correspondence)、隔离性(Isolation)、持久性(Durability)。


    原子性:整个事务中的所有操作,要么全部完成,要么全部不完成,不可能停滞在中间某个环节。事务在执行过程中发生错误,会被回滚(Rollback)到事务开始前的状态,就像这个事务从来没有执行过一样。


    一致性:在事务开始之前和事务结束以后,数据库的完整性约束没有被破坏。


    隔离性:隔离状态执行事务,使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务,运行在相同的时间内,执行 相同的功能,事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化,为了防止事务操作间的混淆,必须串行化或序列化请 求,使得在同一时间仅有一个请求用于同一数据。


    持久性:在事务完成以后,该事务所对数据库所作的更改便持久的保存在数据库之中,并不会被回滚。

    3.视图的作用,视图可以更改么?

    视图是虚拟的表,与包含数据的表不一样,视图只包含使用时动态检索数据的查询;不包含任何列或数据。使用视图可以简化复杂的sql操作,隐藏具体的细节,保护数据;视图创建后,可以使用与表相同的方式利用它们。
    视图不能被索引,也不能有关联的触发器或默认值,如果视图本身内有order by 则对视图再次order by将被覆盖。
    创建视图:create view XXX as XXXXXXXXXXXXXX;
    对于某些视图比如未使用联结子查询分组聚集函数Distinct Union等,是可以对其更新的,对视图的更新将对基表进行更新;但是视图主要用于简化检索,保护数据,并不用于更新,而且大部分视图都不可以更新。

    4.drop,delete与truncate的区别

    drop直接删掉表 truncate删除表中数据,再插入时自增长id又从1开始 delete删除表中数据,可以加where字句。

    (1) DELETE语句执行删除的过程是每次从表中删除一行,并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。TRUNCATE TABLE 则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存,删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。

    (2) 表和索引所占空间。当表被TRUNCATE 后,这个表和索引所占用的空间会恢复到初始大小,而DELETE操作不会减少表或索引所占用的空间。drop语句将表所占用的空间全释放掉。

    (3) 一般而言,drop > truncate > delete

    (4) 应用范围。TRUNCATE 只能对TABLE;DELETE可以是table和view

    (5) TRUNCATE 和DELETE只删除数据,而DROP则删除整个表(结构和数据)。

    (6) truncate与不带where的delete :只删除数据,而不删除表的结构(定义)drop语句将删除表的结构被依赖的约束(constrain),触发器(trigger)索引(index);依赖于该表的存储过程/函数将被保留,但其状态会变为:invalid。

    (7) delete语句为DML(data maintain Language),这个操作会被放到 rollback segment中,事务提交后才生效。如果有相应的 tigger,执行的时候将被触发。

    (8) truncate、drop是DLL(data define language),操作立即生效,原数据不放到 rollback segment中,不能回滚

    (9) 在没有备份情况下,谨慎使用 drop 与 truncate。要删除部分数据行采用delete且注意结合where来约束影响范围。回滚段要足够大。要删除表用drop;若想保留表而将表中数据删除,如果于事务无关,用truncate即可实现。如果和事务有关,或老师想触发trigger,还是用delete。

    (10) Truncate table 表名 速度快,而且效率高,因为:
    truncate table 在功能上与不带 WHERE 子句的 DELETE 语句相同:二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快,且使用的系统和事务日志资源少。DELETE 语句每次删除一行,并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据,并且只在事务日志中记录页的释放。

    (11) TRUNCATE TABLE 删除表中的所有行,但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值,请改用 DELETE。如果要删除表定义及其数据,请使用 DROP TABLE 语句。

    (12) 对于由 FOREIGN KEY 约束引用的表,不能使用 TRUNCATE TABLE,而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中,所以它不能激活触发器。

    5.索引的工作原理及其种类

    数据库索引,是数据库管理系统中一个排序的数据结构,以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树

    在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法。这种数据结构,就是索引。

    为表设置索引要付出代价的:一是增加了数据库的存储空间,二是在插入和修改数据时要花费较多的时间(因为索引也要随之变动)。

    \

     

    上图展示了一种可能的索引方式。左边是数据表,一共有两列七条记录,最左边的是数据记录的物理地址(注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的)。为了加快Col2的查找,可以维护一个右边所示的二叉查找树,每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针,这样就可以运用二叉查找在O(log2n)的复杂度内获取到相应数据。

    创建索引可以大大提高系统的性能。

    第一,通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。

    第二,可以大大加快数据的检索速度,这也是创建索引的最主要的原因。

    第三,可以加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义。

    第四,在使用分组和排序子句进行数据检索时,同样可以显著减少查询中分组和排序的时间。

    第五,通过使用索引,可以在查询的过程中,使用优化隐藏器,提高系统的性能。

    也许会有人要问:增加索引有如此多的优点,为什么不对表中的每一个列创建一个索引呢?因为,增加索引也有许多不利的方面。

    第一,创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加。

    第二,索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果要建立聚簇索引,那么需要的空间就会更大。

    第三,当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,这样就降低了数据的维护速度。

    索引是建立在数据库表中的某些列的上面。在创建索引的时候,应该考虑在哪些列上可以创建索引,在哪些列上不能创建索引。一般来说,应该在这些列上创建索引:在经常需要搜索的列上,可以加快搜索的速度;在作为主键的列上,强制该列的唯一性和组织表中数据的排列结构;在经常用在连接的列上,这些列主要是一些外键,可以加快连接的速度;在经常需要根据范围进行搜索的列上创建索引,因为索引已经排序,其指定的范围是连续的;在经常需要排序的列上创建索引,因为索引已经排序,这样查询可以利用索引的排序,加快排序查询时间;在经常使用在WHERE子句中的列上面创建索引,加快条件的判断速度。

    同样,对于有些列不应该创建索引。一般来说,不应该创建索引的的这些列具有下列特点:

    第一,对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为,既然这些列很少使用到,因此有索引或者无索引,并不能提高查询速度。相反,由于增加了索引,反而降低了系统的维护速度和增大了空间需求。

    第二,对于那些只有很少数据值的列也不应该增加索引。这是因为,由于这些列的取值很少,例如人事表的性别列,在查询的结果中,结果集的数据行占了表中数据行的很大比例,即需要在表中搜索的数据行的比例很大。增加索引,并不能明显加快检索速度。

    第三,对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为,这些列的数据量要么相当大,要么取值很少。

    第四,当修改性能远远大于检索性能时,不应该创建索引。这是因为,修改性能和检索性能是互相矛盾的。当增加索引时,会提高检索性能,但是会降低修改性能。当减少索引时,会提高修改性能,降低检索性能。因此,当修改性能远远大于检索性能时,不应该创建索引。

    根据数据库的功能,可以在数据库设计器中创建三种索引:唯一索引、主键索引和聚集索引

    唯一索引

    唯一索引是不允许其中任何两行具有相同索引值的索引。

    当现有数据中存在重复的键值时,大多数数据库不允许将新创建的唯一索引与表一起保存。数据库还可能防止添加将在表中创建重复键值的新数据。例如,如果在employee表中职员的姓(lname)上创建了唯一索引,则任何两个员工都不能同姓。 主键索引 数据库表经常有一列或列组合,其值唯一标识表中的每一行。该列称为表的主键。 在数据库关系图中为表定义主键将自动创建主键索引,主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时,它还允许对数据的快速访问。 聚集索引 在聚集索引中,表中行的物理顺序与键值的逻辑(索引)顺序相同。一个表只能包含一个聚集索引。

    如果某索引不是聚集索引,则表中行的物理顺序与键值的逻辑顺序不匹配。与非聚集索引相比,聚集索引通常提供更快的数据访问速度。

    局部性原理与磁盘预读

    由于存储介质的特性,磁盘本身存取就比主存慢很多,再加上机械运动耗费,磁盘的存取速度往往是主存的几百分分之一,因此为了提高效率,要尽量减少磁盘I/O。为了达到这个目的,磁盘往往不是严格按需读取,而是每次都会预读,即使只需要一个字节,磁盘也会从这个位置开始,顺序向后读取一定长度的数据放入内存。这样做的理论依据是计算机科学中著名的局部性原理当一个数据被用到时,其附近的数据也通常会马上被使用。程序运行期间所需要的数据通常比较集中。

    由于磁盘顺序读取的效率很高(不需要寻道时间,只需很少的旋转时间),因此对于具有局部性的程序来说,预读可以提高I/O效率。

    预读的长度一般为页(page)的整倍数。页是计算机管理存储器的逻辑块,硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块,每个存储块称为一页(在许多操作系统中,页得大小通常为4k),主存和磁盘以页为单位交换数据。当程序要读取的数据不在主存中时,会触发一个缺页异常,此时系统会向磁盘发出读盘信号,磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中,然后异常返回,程序继续运行。

    B-/+Tree索引的性能分析

    到这里终于可以分析B-/+Tree索引的性能了。

    上文说过一般使用磁盘I/O次数评价索引结构的优劣。先从B-Tree分析,根据B-Tree的定义,可知检索一次最多需要访问h个节点。数据库系统的设计者巧妙利用了磁盘预读原理,将一个节点的大小设为等于一个页,这样每个节点只需要一次I/O就可以完全载入。为了达到这个目的,在实际实现B-Tree还需要使用如下技巧:

    每次新建节点时,直接申请一个页的空间,这样就保证一个节点物理上也存储在一个页里,加之计算机存储分配都是按页对齐的,就实现了一个node只需一次I/O。

    B-Tree中一次检索最多需要h-1次I/O(根节点常驻内存),渐进复杂度为O(h)=O(logdN)。一般实际应用中,出度d是非常大的数字,通常超过100,因此h非常小(通常不超过3)。

    而红黑树这种结构,h明显要深的多。由于逻辑上很近的节点(父子)物理上可能很远,无法利用局部性,所以红黑树的I/O渐进复杂度也为O(h),效率明显比B-Tree差很多。

    综上所述,用B-Tree作为索引结构效率是非常高的。

    6.连接的种类

    查询分析器中执行:
    --建表table1,table2:
    create table table1(id int,name varchar(10))
    create table table2(id int,score int)
    insert into table1 select 1,'lee'
    insert into table1 select 2,'zhang'
    insert into table1 select 4,'wang'
    insert into table2 select 1,90
    insert into table2 select 2,100
    insert into table2 select 3,70
    如表
    -------------------------------------------------
    table1 | table2 |
    -------------------------------------------------
    id name |id score |
    1 lee |1 90|
    2 zhang| 2 100|
    4 wang| 3 70|
    -------------------------------------------------

    以下均在查询分析器中执行
    一、外连接
    1.概念:包括左向外联接、右向外联接或完整外部联接

    2.左连接:left join 或 left outer join
    (1)左向外联接的结果集包括 LEFT OUTER 子句中指定的左表的所有行,而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行,则在相关联的结果集行中右表的所有选择列表列均为空值(null)。
    (2)sql 语句
    select * from table1 left join table2 on table1.id=table2.id
    -------------结果-------------
    idnameidscore
    ------------------------------
    1lee190
    2zhang2100
    4wangNULLNULL
    ------------------------------
    注释:包含table1的所有子句,根据指定条件返回table2相应的字段,不符合的以null显示

    3.右连接:right join 或 right outer join
    (1)右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行,则将为左表返回空值。
    (2)sql 语句
    select * from table1 right join table2 on table1.id=table2.id
    -------------结果-------------
    idnameidscore
    ------------------------------
    1lee190
    2zhang2100
    NULLNULL370
    ------------------------------
    注释:包含table2的所有子句,根据指定条件返回table1相应的字段,不符合的以null显示

    4.完整外部联接:full join 或 full outer join
    (1)完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时,则另一个表的选择列表列包含空值。如果表之间有匹配行,则整个结果集行包含基表的数据值。
    (2)sql 语句
    select * from table1 full join table2 on table1.id=table2.id
    -------------结果-------------
    idnameidscore
    ------------------------------
    1lee190
    2zhang2100
    4wangNULLNULL
    NULLNULL370
    ------------------------------
    注释:返回左右连接的和(见上左、右连接)

    二、内连接
    1.概念:内联接是用比较运算符比较要联接列的值的联接

    2.内连接:join 或 inner join

    3.sql 语句
    select * from table1 join table2 on table1.id=table2.id
    -------------结果-------------
    idnameidscore
    ------------------------------
    1lee190
    2zhang2100
    ------------------------------
    注释:只返回符合条件的table1和table2的列

    4.等价(与下列执行效果相同)
    A:select a.*,b.* from table1 a,table2 b where a.id=b.id
    B:select * from table1 cross join table2 where table1.id=table2.id (注:cross join后加条件只能用where,不能用on)

    三、交叉连接(完全)

    1.概念:没有 WHERE 子句的交叉联接将产生联接所涉及的表的笛卡尔积。第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的大小。(table1和table2交叉连接产生3*3=9条记录)

    2.交叉连接:cross join (不带条件where...)

    3.sql语句
    select * from table1 cross join table2
    -------------结果-------------
    idnameidscore
    ------------------------------
    1lee190
    2zhang190
    4wang190
    1lee2100
    2zhang2100
    4wang2100
    1lee370
    2zhang370
    4wang370
    ------------------------------
    注释:返回3*3=9条记录,即笛卡尔积

    4.等价(与下列执行效果相同)
    A:select * from table1,table2

    7.数据库范式

    1 第一范式(1NF)

    在任何一个关系数据库中,第一范式(1NF)是对关系模式的基本要求,不满足第一范式(1NF)的数据库就不是关系数据库。
    所谓第一范式(1NF)是指数据库表的每一列都是不可分割的基本数据项,同一列中不能有多个值,即实体中的某个属性不能有多个值或者不能有重复的属性。如果出现重复的属性,就可能需要定义一个新的实体,新的实体由重复的属性构成,新实体与原实体之间为一对多关系。在第一范式(1NF)中表的每一行只包含一个实例的信息。简而言之,第一范式就是无重复的列。

    2 第二范式(2NF)

    第二范式(2NF)是在第一范式(1NF)的基础上建立起来的,即满足第二范式(2NF)必须先满足第一范式(1NF)。第二范式(2NF)要求数据库表中的每个实例或行必须可以被惟一地区分。为实现区分通常需要为表加上一个列,以存储各个实例的惟一标识。这个惟一属性列被称为主关键字或主键、主码。
    第二范式(2NF)要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性,如果存在,那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体,新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列,以存储各个实例的惟一标识。简而言之,第二范式就是非主属性非部分依赖于主关键字。

    3 第三范式(3NF)

    满足第三范式(3NF)必须先满足第二范式(2NF)。简而言之,第三范式(3NF)要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。例如,存在一个部门信息表,其中每个部门有部门编号(dept_id)、部门名称、部门简介等信息。那么在员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。如果不存在部门信息表,则根据第三范式(3NF)也应该构建它,否则就会有大量的数据冗余。简而言之,第三范式就是属性不依赖于其它非主属性。(我的理解是消除冗余)


    8.数据库优化的思路

    这个我借鉴了慕课上关于数据库优化的课程。

    1.SQL语句优化

    1)应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描。
    2)应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:
    select id from t where num is null
    可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:
    select id from t where num=0
    3)很多时候用 exists 代替 in 是一个好的选择
    4)用Where子句替换HAVING 子句 因为HAVING 只会在检索出所有记录之后才对结果集进行过滤

    2.索引优化

    看上文索引

    3.数据库结构优化

    1)范式优化: 比如消除冗余(节省空间。。) 2)反范式优化:比如适当加冗余等(减少join) 3)拆分表: 分区将数据在物理上分隔开,不同分区的数据可以制定保存在处于不同磁盘上的数据文件里。这样,当对这个表进行查询时,只需要在表分区中进行扫描,而不必进行全表扫描,明显缩短了查询时间,另外处于不同磁盘的分区也将对这个表的数据传输分散在不同的磁盘I/O,一个精心设置的分区可以将数据传输对磁盘I/O竞争均匀地分散开。对数据量大的时时表可采取此方法。可按月自动建表分区。
    4)拆分其实又分垂直拆分和水平拆分: 案例: 简单购物系统暂设涉及如下表: 1.产品表(数据量10w,稳定) 2.订单表(数据量200w,且有增长趋势) 3.用户表 (数据量100w,且有增长趋势) 以mysql为例讲述下水平拆分和垂直拆分,mysql能容忍的数量级在百万静态数据可以到千万 垂直拆分: 解决问题:表与表之间的io竞争 不解决问题:单表中数据量增长出现的压力 方案: 把产品表和用户表放到一个server上 订单表单独放到一个server上 水平拆分: 解决问题:单表中数据量增长出现的压力 不解决问题:表与表之间的io争夺
    方案: 用户表通过性别拆分为男用户表和女用户表 订单表通过已完成和完成中拆分为已完成订单和未完成订单 产品表 未完成订单放一个server上 已完成订单表盒男用户表放一个server上 女用户表放一个server上(女的爱购物 哈哈)

    4.服务器硬件优化

    这个么多花钱咯!

    9.存储过程与触发器的区别

    触发器与存储过程非常相似,触发器也是SQL语句集,两者唯一的区别是触发器不能用EXECUTE语句调用,而是在用户执行Transact-SQL语句时自动触发(激活)执行。触发器是在一个修改了指定表中的数据时执行的存储过程。常通过创建触发器来强制实现不同表中的逻辑相关数据的引用完整性和一致性。由于用户不能绕过触发器,所以可以用它来强制实施复杂的业务规则,以确保数据的完整性。触发器不同于存储过程,触发器主要是通过事件执行触发而被执行的,而存储过程可以通过存储过程名称名字而直接调用。当对某一表进行诸如UPDATE、INSERT、DELETE这些操作时,SQLSERVER就会自动执行触发器所定义的SQL语句,从而确保对数据的处理必须符合这些SQL语句所定义的规则。


    10.面试回答数据库优化问题从以下几个层面入手

    (1)、根据服务层面:配置mysql性能优化参数;

    (2)、从系统层面增强mysql的性能:优化数据表结构、字段类型、字段索引、分表,分库、读写分离等等。

    (3)、从数据库层面增强性能:优化SQL语句,合理使用字段索引。

    (4)、从代码层面增强性能:使用缓存和NoSQL数据库方式存储,如MongoDB/Memcached/Redis来缓解高并发下数据库查询的压力。

    (5)、减少数据库操作次数,尽量使用数据库访问驱动的批处理方法。

    (6)、不常使用的数据迁移备份,避免每次都在海量数据中去检索。

    (7)、提升数据库服务器硬件配置,或者搭建数据库集群。

    (8)、编程手段防止SQL注入:使用JDBC PreparedStatement按位插入或查询;正则表达式过滤(非法字符串过滤);

    展开全文
  • 数据库优化详解

    千次阅读 2020-03-06 10:59:44
    可以看出来,数据结构、SQL、索引是成本最低,且效果最好的优化手段数据库优化从以下几个方面优化: 数据库设计—三大范式、字段、表结构 数据库索引 存储过程 (模块化编程,可以提高速度) 分表分库 ...
  • MySQL数据库优化的八种方式(经典必看)

    万次阅读 多人点赞 2019-03-13 15:48:28
    MySQL数据库优化的八种方式(经典必看) 引言: 关于数据库优化,网上有不少资料和方法,但是不少质量参差不齐,有些总结的不够到位,内容冗杂 偶尔发现了这篇文章,总结得很经典,文章流量也很大,所以拿...
  • 数据库优化方案整理

    万次阅读 多人点赞 2018-08-29 16:05:16
    一:优化说明 A:有数据表明,用户可以承受的最大等待时间为8秒。...可以看出来,数据结构、SQL、索引是成本最低,且效果最好的优化手段。 C:性能优化是无止境的,当性能可以满足需求时即可,不要过度优化...
  • 一、数据库访问优化的五个法则 在实际开发,我们主要是需要对SQL语句进行优化,我们需要快速定位能性的瓶颈点,也就是说快速找到我们SQL主要的开销在哪里?根据木桶原理可以知道,最慢的设备往往是性能瓶颈。例如:...
  • MySQL数据库优化

    千次阅读 2016-02-26 13:04:44
    说到数据库优化,我在MySQL数据库引擎这篇文章当中对使用MyIsam存储引擎的表和使用InnoDB存储引擎的表之间对比的过程中发现,InnoDB存储引擎的表插入速度十分的慢,我创建了一个存储过程直接往数据库中插入一千万...
  • 数据库SQL优化大总结之 百万级数据库优化方案

    万次阅读 多人点赞 2016-06-23 09:43:50
    网上关于SQL优化的教程很多,但是比较杂乱。近日有空整理了一下,写出来跟大家分享一下,其中有错误和不足的地方,还请大家纠正补充。 这篇文章我花费了大量的时间查找资料、修改、排版,希望大家阅读之后,感觉好的...
  • 数据库优化

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

    千次阅读 2019-07-24 15:11:35
    读写分离不是银弹,并不是一有性能问题就上读写分离,而是应该先优化,例如优化慢查询,调整不合理的业务逻辑,引入缓存查询等只有确定系统没有优化空间后才考虑读写分离集群 !(数据库的架构方面--先是主从复制 →...
  • 互联网中常见优化手段

    千次阅读 2020-11-06 22:08:15
    性能优化对一个产品的重要性不言而喻,它直接影响网站的用户留存率,APP在商店的评分和用户粘性。一个响应慢的应用,即便它功能再强大,也留不住用户。 性能优化对一个程序员同样非常重要——如果你是一个有追求的...
  • 数据库优化——常用SQL优化

    千次阅读 2017-05-10 20:18:26
    摘要本文为数据库优化系列文章的第二篇文章 《数据库优化——常用SQL优化》

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 94,129
精华内容 37,651
关键字:

常见的数据库优化手段