SQL编码及数据库优化.docx

SQL编码及优化目录SQL编码及优化11表41.1简化业务模型41.2适当建立数据冗余41.3降低记录字节数51.4避免动态扩展表的出现51.5避免使用视图51.6表关系51.7大数据量分表存储62索引62.1索引简介62.2创建索引72.3控制索引数量72.4保持索引有效72.5重建索引83语句编写83.1数据库缓存83.2执行计划分析93.2.1分析工具93.2.2优化要点103.2.3步骤分析103.3SQL示例123.3.1缓存的作用123.3.2索引扫描143.3.3索引匹配度164经验分享204.1优化建议205死锁225.1定义225.2预防22表简化业务模型熟悉业务场景，理解各功能或模块间的逻辑关系。对于结构清晰、关联单一的业务模型，最终的系统对数据库的操作形式也会简单很多,才有可能将单次操作消耗时间优化到毫秒级,支持大数据量、高并发的需求。在进行需求分析、业务模型定义、表结构定义时，需要有意识的简化表关联关系,减少表之间的相互依赖和层次。适当建立数据冗余数据冗余简单的说就是非键字段的重复出现；关联较为紧密，查询比较频繁的对象表之间可以存在适当的冗余字段，通过数据冗余查询关联，提高性能。由于冗余字段需要保持同步，增加编码难度和维护量，也会造成一定的性能消耗，建议仅模块内部存在冗余字段, 并时刻注意保持冗余字段的同步。降低记录字节数单条记录所占字节数，直接影响到整个数据库数据文件的大小，从而影响到磁盘IO以及数据库操作的速度，所以需要尽可能的减少单条记录所占字节数。根据业务需要设定合适的字段长度，不能随意指定较大的长度减少某些情况下才会生效的字段的数量避免动态扩展表的出现某些情况下，为了可以自由的进行属性扩展，采用了扩展表的方式，建立了一系列的扩展属性表，和主表进行关联。如果采用这种形式，必须遵循一个限制：扩展表的数据，仅用于单条记录的查询和更新。这种情况下扩展表的应用作用有限，因此，不建议采用扩展表的形式满足业务需要。避免使用视图一般情况下视图都会进行多表关联，这种情况下，系统会忽略原表的索引、主键等等，每次针对视图进行查询，都会按照视图的定义，读取磁盘，重组数据，性能会有极大的消耗。如在此情况下在进行数量汇总和分页，即使十万级别的数据也无法支持。表关系表的关系大致可以分为：一对一、一对多、多对多的关系。在一般情况下，它们是一对一的关系：即一张原始单据对应且只对应一个实体。在特殊情况下，它们可能是一对多或多对一的关系，即一张原始单证对应多个实体，或多张原始单证对应一个实体。正确识别表关系对我们数据库设计是有很大帮助的。大数据量分表存储大数据量表，对于查询统一以及更新的影响都非常大，对于频繁性操作，但是临时性较强的数据，需要进行分表存储和操作，以减少对大表进行频繁的操作带来的性能消耗。比较常见的有日志表、消息表、流程表，具体实现的时候，需要根据其也特点制定合适的分表策略。分表的方式主要有：将固定业务数据，分为固定的两个表，分别用于存储最新数据和历史数据将固定业务数据，按时间段（按年、月、周等），创建数据表将所有数据，按时间段创建数据帐套分表存储后，转移数据的操作可以按业务需要、操作方便性、时间要求，实现为手动或自动方式。分表存储后，相应的业务以及对应的查询统计和其他操作，也应分开来进行操作，否则分表就没有什么实际的意义。索引索引简介数据库表的数据是线性排列的方式，依次存储每条记录，以每条记录500个字节，100万条记录来计算，需要占用500M大小的磁盘空间，这些数据仅仅从磁盘读入到内存就需要很长时间。数据库索引，以树的方式，将数据以指定字段进行分类，并存储其实际记录指针，主要有以下特点：本身是经过排序的，查找速度很快。相对于实际数据表而言，存储空间更小，加载更快。如果索引的排序和需要的结果排序方式匹配，可以节省重新排序的时间。如果所有涉及字段都在索引中，不需要再查询实际数据表。创建索引根据业务需要分析出使用频率较高、数据量较大、关联查询较多的表，然后根据可能的查询操作设定合适的索引。如果是系统已完成开发，最好的方式是统计执行的查询语句，按使用频率、重要程度设定优先级，针对性的建立索引。下面是一些必须要创建索引的场景： 首页各信息面板（系统入口）各模块主界面数据查询和暂时（主工作场景）各数据统计导出操作（耗时较长的功能点）。。。创建索引需要注意以下几点：仅选择查询条件和排序包含的字段作为索引字段如果需要返回的字段非常少，也可以作为索引的一部分注意索引的字段顺序和排序方式和业务需要尽可能的匹配（Where条件的字段和索引前面的字段匹配，Order和剩余的字段匹配或剩余部分的前部分匹配）注意索引字段的长度总和不能超过900字节（数据库存在限制）索引名称统一为IX_TABLENAME_COLNAME（全部大写、第二段为表