网站维护管理,南宁网站提升排名,产品设计排版效果图,wordpress xml 导入失败一直以来就想总结一下自己这么多年来在关系数据库上积累的经验。奈何自己是一个比较懒的人一直不想动手去写。扎克伯格曾说过#xff1a;“想做一件事的话#xff0c;最好的办法就是先开始”。索性就先写一点东西#xff0c;这些东西不会太长#xff0c;自然也不会包括太多… 一直以来就想总结一下自己这么多年来在关系数据库上积累的经验。奈何自己是一个比较懒的人一直不想动手去写。扎克伯格曾说过“想做一件事的话最好的办法就是先开始”。索性就先写一点东西这些东西不会太长自然也不会包括太多细节如果有纸上谈兵的情况也敬请谅解。 本文的主要内容是对关系数据库的设计及优化总结出一些基本原则这些原则可以帮助提高数据库的设计质量的同时避免一些常见的性能问题。软件行业没有银弹任何原则是指一种参考困难的是懂得何时打破这些原则原则一 OLTP负载尽量遵循第三范式 不少程序员对数据库范式理解不透彻甚至嗤之以鼻为了简便在不同的表冗余字段甚至害怕一对一一对多多对多这种关系。阿里还流传着不要关联数据表的神奇传说。关系数据库不使用关系那就没有必要用关系数据库了。 遵守第三范式带来的好就是减少数据冗余同时使得模型中对单一表的CRUD操作不会互相影响专业术语叫做更新异常。更新异常往往是大量bug的滋生地。更新异常甚至会大大影响查询性能。 最后重申一下本条原则适用范围当你在使用关系数据库键值对文档数据库除外去设计业务模型的时候OLTP负载就是频繁的增加删除修改型的应用场景尽量遵循业务实体中的关系并将它们设计为满足第三范式的数据模型。原则二 选择正确的数据类型和长度 很多程序员在设计数据类型和长度方面显得非常草率甚至是不负责任的。例如将日期类型存储为字符串型将数字类型也设计为字符串型一切类型均为字符串型并随意给与50100200的可变长度一了百了。这种随意性是对程序质量的亵渎。很多人说这不影响开发能跑起来就行了。 本条原则要强调针对你要解决的问题请慎重选择数据类型。例如时间请用合适的日期类型datetimedate, smalldate。整数请选择int,bigint等货币类型请选择decimal类型。不合适的类型会导致数据转换在程序中也会导致数据转换这不仅仅是性能杀手更是众多bug的来源。 请慎重选择适合的长度如果业务需求仅需要10个长度的字符那就给与10个长度不要因为害怕长度不够而给与20个或者200个长度。慷慨往往是不明智的。 在具有大量数据的表中不合适的长度会浪费大量存储空间这将导致数据库每一页中存储的数据行数就会变少数据库引擎加载到内存中的页也就随之变少进而影响到查询的性能。同时数据库的备份也明显增大影响备份还原的效率。 如果遵循本条原则精心选择数据类型和长度尤其是对那些可能包含海量数据的大表将显著提高数据模型的设计质量为后续的程序开发奠定了良好的基础。原则三 请正确创建索引 在我的早期职业生涯的时候曾遇到过不给任何表增加索引的设计仅有主键理由是索引会减缓INSERTUPDATE效率。也遇到过给每个字段都增加索引的匪夷所思的设计。 要了解如何正确使用索引必须了解索引是提高查询效率大多数人都知道索引类似书本最后一页的词汇索引告诉你某一词汇在书中的多少页。但很多人对多因的基本结构缺乏了解虽然大多知道索引是B-Tree但不知道B-Tree为什么能提高查询效率。简单的讲就是B-Tree是一颗特殊的平衡树找到某一数据所用的时间总是一定的。例如在1000000万个订单中找到任意订单记录所需时间是一样的。这是由B-Tree结构保证的数据库引擎从根节点出发查找一条特定记录总是跨越2-3层所以时间总是相等的这个时间并不与数据量相关而仅仅与树的深度相关。这就是索引能提高查询效率的基本原理。 请创建合适的聚集索引很多人把聚集索引和主键相混淆因为大多数数据库引擎会自动在主键上创建聚集索引。其实聚集索引就是按照主键顺序来实际组织磁盘上数据的物理顺序因为物理数据只能按照一种顺序组织所以一个表只能有一个聚集索引。聚集索引就是物理数据本身这是一个非常重要的概念。聚集索引一般使用自增长的数据类型int类型是大多数的选择这可以避免数据库引擎维护聚集索引。 创建合适的非聚集索引按照某些字段查询记录的需求是非聚集索引使用的适合场景例如按照姓名年龄性别查询一些员工的时候如果在这三个字段上创建一个非聚集索引将会显著改善查询性能。非聚集索引的页只包含非聚集索引数据如果要查询表中其他列的数据就要做一次lookup操作通过聚集索引把对应的记录提取出来。为避免lookup操作就产生了覆盖索引 考虑创建覆盖索引例如很多场景下仅需要根据姓名查询但是需要同时提取年龄等信息select name,age where name ’张三‘), 我们仅仅为name创建一个非聚集索引就可以大幅度提高查询效率但如果为name在增加一个覆盖索引name(age)就可以直接从索引中提取需要的数据而不必要再去做一个lookup操作从聚集索引中查找这将大大提高查询性能。 考虑重建索引对索引性能影响最大的就是碎片频繁的insert,update 可能会导致大量的页拆分进而导致索引页的连续地址和物理的连续地址不同影响索引顺序扫描导致性能下降。索引中有个填充率的参数就是为了避免页拆分而预留的空间但是填充率过低又会导致索引页过多降低索引查询效率。所以好的解决办法是当碎片过多的时候可以考虑重建索引。重建后索引页的顺序和物理顺序会保持一直。 索引带来的附加好处索引带来查询性能提升的同时还可以大大降低锁的争用因为查询可以利用索引优化器会尽可能少的对数据行和页进行加锁并发能力就可能提高。优化器可以利用索引的排序有效降低对CPU的消耗。对于Order by 或 gourp by 子句将会带来性能提升同时降低CPU使用率。CPU使用率降低会提高数据库服务器的吞吐量。 最后请为那些经常被执行的查询创建合适的索引主要分析where后面的条件。同时请谨慎考虑索引创建如果你不确定数据库引擎是否正确使用索引请不要随意创建索引因为索引会占用磁盘空间还会导致insert,update效率降低。原则四 使用合适的隔离级别以SQLServer为例 关系数据库最大的基本特征就是保持数据一致性为了保持数据一致性而施加了很多锁。事务的隔离级别主要是用于控制某一事务受其他事务的影响的程度这一程度会影响其他事务对同一数据的读取和修改操作进而影响并发能力。 较低的隔离级别可以增强许多用户同时访问数据的能力但也增加了用户可能遇到的并发副作用例如脏读或丢失更新的数量。相反较高的隔离级别减少了用户可能遇到的并发副作用的类型但需要更多的系统资源并增加了一个事务阻塞其他事务的可能性。应平衡应用程序的数据完整性要求与每个隔离级别的开销在此基础上选择相应的隔离级别。最高隔离级别可序列化保证事务在每次重复读取操作时都能准确检索到相同的数据但需要通过执行某种级别的锁定来完成此操作而锁定可能会影响多用户系统中的其他用户。最低隔离级别未提交读可以检索其他事务已经修改、但未提交的数据。在未提交读中所有并发副作用都可能发生但因为没有读取锁定或版本控制所以开销最少。 重要选择事务隔离级别不影响为保护数据修改而获取的锁。事务总是在其修改的任何数据上获取排他锁并在事务完成之前持有该锁不管为该事务设置了什么样的隔离级别。对于读取操作事务隔离级别主要定义保护级别以防受到其他事务所做更改的影响。 例如SQLServer的默认隔离级别READ COMMITTED数据库引擎会使用共享锁防止其他事务在当前事务执行读取操作期间修改行。共享锁还会阻止语句在其他事务完成之前读取由这些事务修改的行。共享锁类型确定它将于何时释放。行锁在处理下一行之前释放。页锁在读取下一页时释放表锁在语句完成时释放。如果查询某个表中获取大量数据并未有效使用索引共享锁可能会升级到锁页和锁表其他对该表的insert update delete操作将不得不等到锁释放给用户带来的影响就是这些操作变慢了。 如果业务允许读取一定的未提交数据可以考虑使用 READ UNCOMMITTED数据库引擎会使用最少的锁当查询数据时候不会阻塞insert update delete 操作。一般对于查询日志表审计表等允许容忍一定的脏读情况下使用该隔离级别这样可以不阻塞业务表的操作。 如果需要更高的一致性例如在一个事务修改某一行但未提交时候限制其他事务修改同样的行那么可以使用 REPEATABLE READ。但是并发级别会大大降低除非你确保需要此种业务场景否则应谨慎使用。 对于需要频读取数据并又要尽量保证不阻塞其他事务insert,update,delete操作的场景编写查询语句的时候可以考虑使用READ_COMMITTED_SNAPSHOT 隔离级别。已提交读隔离使用行版本控制提供语句级读取一致性。读取操作只需要 SCH-S 表级别的锁不需要页锁或行锁。也就是说SQL Server数据库引擎使用行版本控制来呈现每个语句其中包含在语句开始时存在的数据的事务一致性快照。不使用锁来防止其他事务更新数据。 该隔离级别会导致更新时候出现版本冲突异常这点需要应用程序抓取异常并进行重试。该隔离级别就可以满足读写互不影响。很多人并未完全理解事务隔离级别就盲目采用所谓读写分离技术增加设计的复杂度。总结 关系数据库引擎依然是当今最最流行满足常见商业逻辑需要的ACID特性的技术之一并且建立在可靠的数学集合理论之上。设计精良的关系数据库模型会提升应用程序的可靠性。设计糟糕的模型会导致上层应用的失败。 以上所属的一些原则有助于提提高数据模型设计的质量充分利用数据库提供的特性可以简化设计方案。
