SQL编码及数据库优化Word格式.docx
- 文档编号:17184756
- 上传时间:2022-11-28
- 格式:DOCX
- 页数:19
- 大小:257.52KB
SQL编码及数据库优化Word格式.docx
《SQL编码及数据库优化Word格式.docx》由会员分享,可在线阅读,更多相关《SQL编码及数据库优化Word格式.docx(19页珍藏版)》请在冰豆网上搜索。
3.3.1缓存的作用12
3.3.2索引扫描14
3.3.3索引匹配度16
4经验分享20
4.1优化建议20
5死锁22
5.1定义22
5.2预防22
1表
1.1简化业务模型
熟悉业务场景,理解各功能或模块间的逻辑关系。
对于结构清晰、关联单一的业务模型,最终的系统对数据库的操作形式也会简单很多,才有可能将单次操作消耗时间优化到毫秒级,支持大数据量、高并发的需求。
在进行需求分析、业务模型定义、表结构定义时,需要有意识的简化表关联关系,减少表之间的相互依赖和层次。
1.2适当建立数据冗余
数据冗余简单的说就是非键字段的重复出现;
关联较为紧密,查询比较频繁的对象表之间可以存在适当的冗余字段,通过数据冗余查询关联,提高性能。
由于冗余字段需要保持同步,增加编码难度和维护量,也会造成一定的性能消耗,建议仅模块内部存在冗余字段,并时刻注意保持冗余字段的同步。
1.3降低记录字节数
单条记录所占字节数,直接影响到整个数据库数据文件的大小,从而影响到磁盘IO以及数据库操作的速度,所以需要尽可能的减少单条记录所占字节数。
●根据业务需要设定合适的字段长度,不能随意指定较大的长度
●减少某些情况下才会生效的字段的数量
1.4避免动态扩展表的出现
某些情况下,为了可以自由的进行属性扩展,采用了扩展表的方式,建立了一系列的扩展属性表,和主表进行关联。
如果采用这种形式,必须遵循一个限制:
扩展表的数据,仅用于单条记录的查询和更新。
这种情况下扩展表的应用作用有限,因此,不建议采用扩展表的形式满足业务需要。
1.5避免使用视图
一般情况下视图都会进行多表关联,这种情况下,系统会忽略原表的索引、主键等等,每次针对视图进行查询,都会按照视图的定义,读取磁盘,重组数据,性能会有极大的消耗。
如在此情况下在进行数量汇总和分页,即使十万级别的数据也无法支持。
1.6表关系
表的关系大致可以分为:
一对一、一对多、多对多的关系。
在一般情况下,它们是一对一的关系:
即一张原始单据对应且只对应一个实体。
在特殊情况下,它们可能是一对多或多对一的关系,即一张原始单证对应多个实体,或多张原始单证对应一个实体。
正确识别表关系对我们数据库设计是有很大帮助的。
1.7大数据量分表存储
大数据量表,对于查询统一以及更新的影响都非常大,对于频繁性操作,但是临时性较强的数据,需要进行分表存储和操作,以减少对大表进行频繁的操作带来的性能消耗。
比较常见的有日志表、消息表、流程表,具体实现的时候,需要根据其也特点制定合适的分表策略。
分表的方式主要有:
●将固定业务数据,分为固定的两个表,分别用于存储最新数据和历史数据
●将固定业务数据,按时间段(按年、月、周等),创建数据表
●将所有数据,按时间段创建数据帐套
分表存储后,转移数据的操作可以按业务需要、操作方便性、时间要求,实现为手动或自动方式。
分表存储后,相应的业务以及对应的查询统计和其他操作,也应分开来进行操作,否则分表就没有什么实际的意义。
2索引
2.1索引简介
数据库表的数据是线性排列的方式,依次存储每条记录,以每条记录500个字节,100万条记录来计算,需要占用500M大小的磁盘空间,这些数据仅仅从磁盘读入到内存就需要很长时间。
数据库索引,以树的方式,将数据以指定字段进行分类,并存储其实际记录指针,主要有以下特点:
●本身是经过排序的,查找速度很快。
●相对于实际数据表而言,存储空间更小,加载更快。
●如果索引的排序和需要的结果排序方式匹配,可以节省重新排序的时间。
●如果所有涉及字段都在索引中,不需要再查询实际数据表。
2.2创建索引
根据业务需要分析出使用频率较高、数据量较大、关联查询较多的表,然后根据可能的查询操作设定合适的索引。
如果是系统已完成开发,最好的方式是统计执行的查询语句,按使用频率、重要程度设定优先级,针对性的建立索引。
下面是一些必须要创建索引的场景:
●首页各信息面板(系统入口)
●各模块主界面数据查询和暂时(主工作场景)
●各数据统计导出操作(耗时较长的功能点)
●。
。
创建索引需要注意以下几点:
●仅选择查询条件和排序包含的字段作为索引字段
●如果需要返回的字段非常少,也可以作为索引的一部分
●注意索引的字段顺序和排序方式和业务需要尽可能的匹配(Where条件的字段和索引前面的字段匹配,Order和剩余的字段匹配或剩余部分的前部分匹配)
●注意索引字段的长度总和不能超过900字节(数据库存在限制)
●索引名称统一为IX_TABLENAME_COLNAME(全部大写、第二段为表名,第三段为首字段名,如果为多个,后面增加序号)
2.3控制索引数量
由于索引的增加会影响更新的速度,所以在进行业务分析的时候,最好仅提供必须、足够的操作,屏蔽其他操作,这样可以减少所需索引的数量,保证更新操作的顺利进行。
小数据量表可以不建立索引,大数据量表除主键之外以6个以内为准。
2.4保持索引有效
无效的索引,除了增加磁盘存储、降低操作速度,没有任何用处,只有在和查询语句匹配的时候,索引才可以发挥应有的作用,匹配度越高,查询速度越快。
在进行产品研发、维护、项目二次开发的过程中,需要根据业务和开发的情况,随时对现有索引进行增删和调整,保持索引的有效性。
2.5重建索引
由于索引数据会因为不断的更新发生变化,可能无法保持最利于查询的状态,因此每隔一段时期对索引进行重建,也会起到一定的效果。
3语句编写
一般系统80%以上的操作都是数据查询操作,基本上100%的数据库更新操作之前都会有数据查询动作。
编写合适的查询语句,建立相匹配的索引,以加快查询的速度,这也是数据库优化的最通用和有效的手段。
3.1数据库缓存
当SQLServer执行SQL语句时,如果需要的数据已经在其内存中,则直接从内存缓冲区读取并进行必要的运算然后输出执行结果。
如果数据还未在内存中,则首先将数据从磁盘上读入内存Buffer中。
而我们通常评价SQL性能指标中的IO逻辑读取数对应的正是从内存缓冲区读取的页数,而IO物理读取数则对应数据从磁盘读取的页数。
数据库会分配一定的内存区域,用于缓存从磁盘读入的实际数据以及索引数据,以便减少磁盘IO。
由于内存大小相对于整个数据库数据文件来说,还是比较小,所以缓存会将使用不频繁的数据从内存移除,以便读入新的数据,再次用到原来的数据时,只能重新读入。
所以只有真正合理的利用缓存,提高缓存命中率,才可以真正的减少磁盘IO。
提高缓存利用率的原则是:
让用不到的数据永远不被读入到缓存(这里包括实际的数据以及索引数据),只有这样,才可以不挤占常用数据的空间。
提高缓存利用率的具体方式,主要还是从查询语句和索引的着手。
数据库的缓存大小对系统其它系统的运行也会有一定的影响,所以数据库服务器缓存的大小,需要根据实际情况进行设定。
缓存优化:
我们可以选择针对那些执行计划占用较大内存、而被重用次数较少的SQL语句进行重点分析。
看其调用方式是否合理;
3.2执行计划分析
数据库都会提供sql语句的查询计划分析工具,用于进行查询语句的调优,虽然实际的执行计划以及执行时间会因为各种情况出现一定的偏差,但还是可以用于进行查询语句的优化。
3.2.1分析工具
下图为SqlServerd的计划分析工具入口(新建查询后显示的工具栏上):
注:
其中按钮一作用是直接对当前语句进行计划分析,按钮二的作用是在点击“执行”执行语句的同时显示执行计划。
下图为分析后给出的查询执行计划图示(执行的动作、顺序及时间百分比):
下图为具体的步骤的执行信息图示(IO和cpu开销,估计执行次数,算法和输出):
3.2.2优化要点
匹配较好的查询语句和索引,其执行计划的调整,需要考虑以下几点:
●减少最终需要从磁盘读取的数据量(磁盘IO)
●最快的速度减少参与中间计算的数据量(嵌套查询及计划分析)
●减少最终返回的数据量(返回结果)
●减少内存的运算量(排序、联合等等)
3.2.3步骤分析
计划经过分解后,会将整条语句解析成不同的动作类型,其中主要的步骤类型如下:
3.2.3.1表扫描
通过直接遍历查询整个数据表,来查找需要的数据。
当不存在合适的索引,或者数据库认为执行索引会更慢的情况下,会执行全表扫描。
对于大数据量表的查询,进行全表遍历非常慢,其执行计划中,不应该存在此类执行步骤。
3.2.3.2索引扫描
如果有匹配的索引,会根据索引进行数据查找,执行计划中会出现此类步骤。
由于进行索引查询比全表遍历的速度快很多。
所以,性能优化时,需要尽可能的将“表遍历”转变为“索引扫描”。
3.2.3.3键查找
即使查询已经匹配了索引,如果条件、排序、返回结果中存在索引中没有的字段时,还是需要再对实际的数据表进行查找,获取需要的数据参与计算。
如果索引扫描步骤已经过滤掉了大部分数据,需要按键查找其他字段的数据量比较小时,速度会比较快(相对于“索引扫描”来说,会占用更多的时间,所以仍是优化的对象)。
注:
对于“键查找“,优化方式如下:
●条件、排序相关字段应该尽量在索引中,不能因此引起“键查找”
●最终的返回结果需要的字段较多,索引中不存在时,可以引起“键查找”
●参与查询的字段较少时,所有的字段都可以建立在索引中,不需要进行“键查找”
3.2.3.4排序
返回的数据记录,一般都要求进行排序,如果查询到的中间结果与要求的排序顺序不一致时,需要在内存中对其进行重新排序。
由于计算量非常大,所以排序的速度也非常慢。
对于“排序“,优化方式是:
●建立合适的索引或主键,和最后要求的排序顺序完全一致,不需要进行“排序”
●在进行排序之前,尽可能的通过过滤条件,降低参与排序的记录数量,减少最终排序需要进行的循环和判断次数
3.3SQL示例
本章节以实际的表结构sql示例,演示不同查询不同的索引及查询语句对于执行效率的影响,以帮助大家理解性能优化的必要性和基本方法。
这些示例会使用“dbccdropcleanbuffers”命令提前清除缓存,并使用“selectgetdate()”语句获取执行时间
3.3.1缓存的作用
3.3.1.1查询1
无缓存的情况下,进行全表扫描,耗时将近6s
dbccdropcleanbuffers
selectgetdate()
selectID,NAME,CONTENT,CREATETIMEfromTestwhereNAME='
bdff'
3.3.1.2查询2
多次执行后,不清除缓存,需要进行全表扫描,耗时0.060s
3.3.1.3结论
单用户对100万条记录进行全表扫描就需要花费6秒,说明了全表遍历的速度非常慢。
不清理缓存,再次执行时花费0.060秒,说明之前的查询时间都大部分消耗在了io上,缓存命中率较高的情况下,性能提升和io降低非常明显。
即使数据都在缓存中,查询出结果耗时0.060毫秒,说明内存计算部分,如果不合理规划,也会占用很多时间,也需要想办法进行优化。
随着数据量的增大、并发的增多,不常用数据会被很快移出缓存,命中率会不断下降,查询速度会受到很大影响,所以不能简单认为有缓存支持就可以了,还需要想办法优化查询计划,提高缓存命中率。
3.3.2索引扫描
3.3.2.1查询1
100万数据,先清除缓存,需要进行全表扫描,耗时6s
BDFF'
3.3.2.2查询2
100万数据,先清除缓存,需要匹配索引,并进行键查找,耗时0.067s
selectID,NAME,CONTENT,CREATETIMEfromTestwhereID='
3.3.2.3查询3
100万数据,先清除缓存,仅需要匹配索引,耗时0.023s
selectID,CREATETIMEfromTESTwhereID='
DBFF'
3.3.2.4结论
就搜索算法而言,纯索引扫描最快,带键查找的索引扫描次之,全表扫描最慢。
因此,优化过程中,应主要避免表扫描的存在,减少键查找的使用,尽量仅采用索引扫描。
3.3.3索引匹配度
3.3.3.1查询1
按时间排序和查询,获取前50行,耗时大于6s
select*from
(selectID,CREATETIME,ROW_NUMBER()over(orderbyCREATETIME)asidxfromTestwhereCREATETIME>
10000)A
whereidx>
0andidx<
50
3.3.3.2查询2
按ID排序和查询,获取前50行,耗时0.177s
(selectID,CREATETIME,ROW_NUMBER()over(orderbyID)asidxfromTest)A
3.3.3.3查询3
按ID排序和查询,指定更精确的查询条件,获取前50行,耗时0.013s
(selectID,CREATETIME,ROW_NUMBER()over(orderbyID)asidxfromTestwhereID='
00001D60-66FD-44AA-B9B2-086434ED7D5F'
)A
3.3.3.4结论
即使查询采用了索引,下列情况也会导致查询效率很低:
●没有条件或者条件过滤作用太差,中间结果太多
●排序字段和索引不匹配,内存中需要进行大量的排序计算
4经验分享
4.1优化建议
1.建立匹配的索引,避免全表扫描(会占用大量io)
2.建立并遵循SQL编码规范,SQL语句应简洁、易读、便于维护
3.为提高查询性能,应限制操作的数据量,尽量在小结果集上操作,减少行数和列数
4.使用高选择性的where子句,让数据库返回必须的数据集,而不是返回大量数据给应用程序,再由应用程序进行筛选
5.Where子句中列的顺序与需使用的索引顺序保持一致
6.可能引起索引失效的操作,如NOT、!
=、<
>
、!
<
、NOTEXISTS、NOTIN、NOTLIKE,like‘%XXX’及函数等
7.尽量使用“>
=”,不要使用“>
”
8.LIKE操作符可以应用通配符查询,里面的通配符组合可能达到几乎是任意的查询,但是如果用得不好则会产生性能上的问题,如LIKE‘%XXX%’这种查询不会引用索引,而LIKE‘XXX%’则会引用范围索引
9.尽量用相同数据类型进行比较,避免发生数据类型转换
10.当需要验证是否有符合条件的记录时,使用exists,不要使用count(*),前者在第一个匹配记录处返回,后者需要遍历所有匹配记录
11.在有min、max、distinct、orderby、groupby操作的列上建索引,避免额外的排序开销
12.尽量保持短事务,尽量不要在事务中处理与数据库操作无关的工作
13.对于无法优化的SQL,需要从业务上进行合理的拆分
14.避免对于大数据量表全表进行数量统计(会对全表或全索引进行遍历)
15.避免复杂的多表关联(避免4个以上的表关联查询)
16.减少查询的嵌套(不能超过两层)
17.不允许使用子查询获取字段的值
18.不允许进行视图嵌套
19.限制返回的结果数量和字段数(简单操作不允许超过100行,导出和统计类操作不允许超过5000条,否则业务设计应该进行调整)
20.应尽量避免在where子句中对字段进行表达式操作(selectidfromtwherenum/2=100)
21.应尽量避免在where子句中对字段进行函数操作
22.用Where子句替换HAVING子句
23.查询尽量用确定的列名,少用*号(Select*from)
24.注意一些or子句和union子句之间的替换;
对于有比较多or运算的查询,建议分成多个查询,用unionall联结起来;
25.用Unionall替换Union(UNION在进行表链接后会筛选掉重复的记录,所以在表链接后会对所产生的结果集进行排序运算,删除重复的记录再返回结果)
26.尽量避免使用insert、update做大批量的数据处理(大数据操作会使页级锁升级为表级锁)
27.不对进行大数据量数据统计(至少应使用过滤条件过滤后再进行统计)
28.减少distinct、orderby、groupby、having、join等查询方式(会引起计算)
29.复杂查询中尝试降低表间关系(减少多对多操作)
30.注意存储过程中参数和数据类型的关系
31.避免过多的使用触发器(触发器有较高的执行效率,但是过多的触发器会降低程序的可维护性)
32.对于不需要在用户界面上展示,但需要与数据库进行多次、大量数据交互的情况,最好的选择就是使用存储过程,可以减少网络通讯次数,降低网络流量,而存储过程已经过预编译,其执行速度也很快
33.开发中减少访问数据库的次数
34.对于以上所述内容,在不同版本的数据库上可能存在一定的差异,具体以实际执行结果为准
5死锁
5.1定义
当多个进程同时访问一个数据库时,其中的每个进程拥有的资源都是其他进程所需的,由此造成的每个进程都无法继续下去的情况。
5.2预防
1、按同一顺序访问数据库对象
不同的存储过程、触发器、动态SQL语句段按照同一的顺序同时访问多张表;
2、尽量使用短事务
使用事务时,尽量缩短事务的逻辑处理过程,及早提交或回滚事务;
3、避免事务中的人为干预
避免在事务操作中,处理大数据或需要与用户交互的操作;
4、使用低隔离级别
尽量不要人为提高事务级别,使用默认或降低操作级别;
5、避免批量Insert或Update大量数据
6、对于访问频繁的表添加索引
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQL 编码 数据库 优化