搜索
首页数据库mysql教程Sql Server系列:数据库物理模式设计

Sql Server系列:数据库物理模式设计

Jun 07, 2016 pm 02:59 PM
serversql开发数据库模式物理系列设计

1、开发数据库时常用命名规范 1、使用不包含数字的单数表名,如果必要的话可以增加模块名前缀。 2、对于主键的命名,使用表名+ID的形式。 3、作为外键的列的名字应该与它们所对应的主键的列名相同,除非该外键维护的是一种自反/递归的关系。 4、避免使用缩写

1、开发数据库时常用命名规范

  1>、使用不包含数字的单数表名,如果必要的话可以增加模块名前缀。

  2>、对于主键的命名,使用表名+ID的形式。

  3>、作为外键的列的名字应该与它们所对应的主键的列名相同,除非该外键维护的是一种自反/递归的关系。

  4>、避免使用缩写。

2、主键设计原则

  1>、主键用途:惟一标识一行;作为一个可以被外部键有效引用的对象。

  2>、主键应当对用户没有意义。

  3>、主键应该是单列的,以便提高连接和筛选操作的效率。复合主键仅用于描述多对多关系的连接表中用两个外键来作为主键。

  4>、永远不要更新主键。

  5>、主键不应包含动态变化的数据,如时间戳、创建时间列、修改时间列等。

  6>、主键应当由计算机自动生成。

  7>、主键是通过Unique约束(非空约束)以及聚集或非聚集惟一索引来实现的。主键应当是没有实际意义的、单列的、由计算机生成的、不可编辑的,而且对用户来说是不可见的。

2.1、创建主键

  在表结构设计中使用聚集索引创建索引,并不是最好,由于每个表只能创建一个聚集索引,这样的做法会失去为每个表创建其他更有效的聚集索引的机会。

  sql语句创建非聚集索引主键:

<span>CREATE</span> <span>TABLE</span><span> dbo.Product
(
    ProductID </span><span>INT</span> <span>IDENTITY</span>(<span>1</span>,<span>1</span>) <span>PRIMARY</span> <span>KEY</span> <span>NONCLUSTERED</span><span>,
    ProductName </span><span>VARCHAR</span>(<span>100</span>) <span>NULL</span><span>
)</span>

2.2、主键使用标识列

  标识列优点:易于人工识别,占用空间小而且快。

  将标识列设置为使用聚集索引的主键(这是一种很常见,但并不好的方法),当单个用户检索一行记录的时候,其速度极快。然而,这样的设计方法会在数据库中形成锁争用的热点。

2.3、主键使用GUID

  GUID是由16个字节的16进制数组成,不会出现重复的数字。与标识列相比,GUID可以在更大的范围保证惟一性。

  GUID优点:复制使用GUID主键的数据库时不必额外做全面的检查,GUID值的随机性可以减少数据库的热点,可以使用多种方法来生成GUID的值。

  sql语句创建GUID主键表:

<span>CREATE</span> <span>TABLE</span><span> dbo.Product
(
    ProductID </span><span>UNIQUEIDENTIFIER</span> <span>DEFAULT</span>(<span>NEWID</span>()) <span>PRIMARY</span> <span>KEY</span> <span>NONCLUSTERED</span><span>,
    ProductName </span><span>VARCHAR</span>(<span>100</span>) <span>NULL</span><span>
)</span>

3、外键设计原则

  外键可以引用除了临时表以外的其他表的主键、Unique约束或惟一索引的列。

4、索引

  SQL Server使用两种基本类型的索引:聚集索引和非聚集索引。一般创建索引的列为:主键、外键、order by列。

  索引设计原则:

  ◊ 索引并不是越多越好,一个表中如果有大量的索引,不仅占用大量的磁盘空间,而且会影响INSERT、DELETE、UPDATE等语句的性能。当表数据更新时,索引也会进行调整和更新。

  ◊ 避免对经常更新的表进行过多的索引,并且索引的列尽可能少。对经常用于查询的字段应该创建索引,但要避免添加不必要的字段。

  ◊ 数据量小的表最好不要使用索引,由于数据较少,查询花费的时间可能比遍历索引的时间还要短,索引可能不会产生优化效果。

  ◊ 在条件表达式中经常用到的、不同值较多的列上建立索引,在不同值少的列上不要建立索引。比如【性别】字段是只有【男】和【女】两个不同值,因此就无须建立索引。如果建立索引,不但不会提高效率,反而会严重降低更新速度。

  ◊ 当唯一性是某种数据本身的特征时,指定唯一索引。使用唯一索引能够确保定义的列的数据完整性,提高查询速度。

  ◊ 在频繁进行排序或分组(ORDER BY或GROUP BY)的列上建立索引,如果待排序的列有多个,可以在这些列上建立组合索引。

4.1、非聚集索引

  创建非聚集索引SQL语句:

<span>CREATE</span> <span>NONCLUSTERED</span> <span>INDEX</span> IX_ProductNo <span>ON</span> dbo.Product(ProductNo)

4.2、聚集索引

  聚集索引将表中的记录行与索引键值以同样的物理顺序存储。由于数据只可能有一种物理存储顺序,因此每个表只能有一个聚集索引。通过聚集索引检索数据只需要更少的逻辑读取的次数,任意非聚集索引都将指向聚集索引的ID而不需要指向数据页。

  聚集索引将具有同样索引键值的记录行集中到尽可能少的数据页中,因而减少了检索一组记录时需要读取的数据页数。所以,对于那些经常作为条件来查询一组记录的列,如类似于OrderDetail.OrderID这样的从表外键,对其建立聚集索引是一个很好的选择。基于同样的原因,对于只返回一行记录的查询,使用聚集索引不会明显地提高性能。

 

  对于聚集索引,常见的错误认识有:

  1>、聚集索引会降低insert操作的速度,因为必须要向后移动一半的数据来为插入的行腾出空间。这种认识是错误的,因为可以利用填充因子控制填充的百分比,从而在索引页上为新插入的数据保留空间。如果索引页填满了,SQL Server将会进行页拆分,在这种情况下只有第一个页才会受到影响。

  2>、在使用标识列的主键上创建聚集索引是一种好的设计方法,它可以使对表的操作达到最快速度。这种认识是错误的,它浪费了创建其他更有效的聚集索引的机会。并且,使用这种方法把每个新插入的记录行都存储到表尾部的同一个数据页中,这将导致数据库的热点与锁争用。虽然行锁可以部分地缓解这个热点问题,但是,根据要锁定的行的数量、页上的行的数量、用户的数量,锁管理器有可能会把行锁升级为页锁,此时又会出现热点问题。

  3>、聚集索引是具有魔力的。如果哪个查询的速度不够快,那么久在该列上创建聚集索引,对于表的操作速度一定会得到提高。这种认识是错误的,聚集索引只是比非聚集索引稍微快一点点。因为在每个表上只能创建一个聚集索引,索引它也是一种宝贵的性能资源,只有在那些经常作为条件查询一组记录行的列上才应该创建聚集索引。

  SQL语句创建聚集索引:

<span>CREATE</span> <span>CLUSTERED</span> <span>INDEX</span> IX_OrderID <span>ON</span> dbo.OrderDetail(OrderID)

  SQL语句删除索引:

<span>DROP</span> <span>INDEX</span> OrderDetail.IX_OrderID

4.3、组合索引

   组合索引是拥有多个索引列的聚集或非聚集索引。因为组合索引包含多个索引列,所以只有在创建表之后,才能使用create index DDL命令来创建。

  SQL语句创建组合索引:

<span>CREATE</span> <span>NONCLUSTERED</span> <span>INDEX</span> IX_ProductNoProductName <span>ON</span> dbo.Product(ProductNo,ProductName)

  在组合索引中,索引排列的先后顺序非常重要。因为只有这样的一些查询才能够使用组合索引:它们在查询条件中所包含的索引列在组合索引中是从第一个索引列开始按照从左到右的顺序依次排列的。上例的组合索引中,索引列的顺序是ProductNo、ProductName,只以ProductName为条件的查询将不会使用这个索引,只有以ProductNo或者ProductNo和ProductName为条件的查询才能使用这个索引。

4.4、索引选项

  1>、Unique索引

  SQL Server的索引具有几个选项,包括惟一性、空间分配和性能选项。

  unique索引选项并非只是一个具有惟一约束的索引;对于unique索引可以进行索引优化。在创建主键或者unique约束时,系统都会自动地为它们创建unique索引。

CREATE UNIQUE INDEX IX_OrderNo ON dbo.Order.OrderNo

   2>、索引填充因子(FillFactor)和填充索引(PAD_INDEX)

  索引需要在其索引树上保留一点空间,这样,在插入新的记录时就不必对索引进行重构。填充因子指定了在索引上填有数据空间所占的百分比。

  填充因子应该设置为多少才最合适,取决于数据库的用途。如果数据库主要用于数据检索,就应该将填充因子设为较大的值以尽可能填满一个索引页的空间。如果需要对表进行大量插入操作,在索引页上保留一些空间就可以提供更新操作的性能。如果对于表插入操作的数量变化幅度较大,将填充因子设置为一个中间的值,并选择填充索引选项就比较合适。

<span>CREATE</span> <span>UNIQUE</span> <span>INDEX</span> IX_OrderNo <span>ON</span> dbo.<span>Order</span>.OrderNo <span>WITH</span> <span>FILLFACTOR</span><span>=</span><span>85</span> PAD_INDEX

   3>、索引排序选项(ASC|DESC)

  尽管SQL Server可以创建降序索引,但不建议改变默认的升序索引顺序。这样做并不能改善性能,但却容易在今后造成一些混乱。除非明确地在Order by字句中使用desc选项,否则,所有使用order by字句的查询仍将按照升序排序。

  创建升序或者降序索引的方法是:在create index DDL命令中的列名之后使用asc或者desc选项。

<span>CREATE</span> <span>UNIQUE</span> <span>INDEX</span> IX_OrderNo <span>ON</span> dbo.<span>Order</span>.OrderNo <span>DESC</span> <span>WITH</span> <span>FILLFACTOR</span><span>=</span><span>85</span> PAD_INDEX

  4>、忽略重复键选项(IGNORE_DUP_KEY)

  忽略重复键选项不是作用于索引的,它指定了索引对今后的数据修改操作的处理方式。

  通常,事务都是原子的,这意味着整个事务中的操作作为一个逻辑单元要么全部成功,要么全部失败。如果设置了忽略重复键选项,允许insert事务只提交那些为unique索引所接收的记录行,而忽略那些违反了unique索引的惟一性的记录行。

  这样选项不会破坏unique索引。在表中仍然不会插入重复的值,所以数据库的一致性是完整无损的,但事务的原子性却被违反了。

  利用这个选项可以方便地向数据库中导入大量可能存在问题的记录行,但是消弱了数据库的ACID属性。

<span>CREATE</span> <span>UNIQUE</span> <span>INDEX</span> IX_OrderNo <span>ON</span> dbo.<span>Order</span>.OrderNo <span>WITH</span> IGNORE_DUP_KEY
声明
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn
解释InnoDB缓冲池及其对性能的重要性。解释InnoDB缓冲池及其对性能的重要性。Apr 19, 2025 am 12:24 AM

InnoDBBufferPool通过缓存数据和索引页来减少磁盘I/O,提升数据库性能。其工作原理包括:1.数据读取:从BufferPool中读取数据;2.数据写入:修改数据后写入BufferPool并定期刷新到磁盘;3.缓存管理:使用LRU算法管理缓存页;4.预读机制:提前加载相邻数据页。通过调整BufferPool大小和使用多个实例,可以优化数据库性能。

MySQL与其他编程语言:一种比较MySQL与其他编程语言:一种比较Apr 19, 2025 am 12:22 AM

MySQL与其他编程语言相比,主要用于存储和管理数据,而其他语言如Python、Java、C 则用于逻辑处理和应用开发。 MySQL以其高性能、可扩展性和跨平台支持着称,适合数据管理需求,而其他语言在各自领域如数据分析、企业应用和系统编程中各有优势。

学习MySQL:新用户的分步指南学习MySQL:新用户的分步指南Apr 19, 2025 am 12:19 AM

MySQL值得学习,因为它是强大的开源数据库管理系统,适用于数据存储、管理和分析。1)MySQL是关系型数据库,使用SQL操作数据,适合结构化数据管理。2)SQL语言是与MySQL交互的关键,支持CRUD操作。3)MySQL的工作原理包括客户端/服务器架构、存储引擎和查询优化器。4)基本用法包括创建数据库和表,高级用法涉及使用JOIN连接表。5)常见错误包括语法错误和权限问题,调试技巧包括检查语法和使用EXPLAIN命令。6)性能优化涉及使用索引、优化SQL语句和定期维护数据库。

MySQL:初学者的基本技能MySQL:初学者的基本技能Apr 18, 2025 am 12:24 AM

MySQL适合初学者学习数据库技能。1.安装MySQL服务器和客户端工具。2.理解基本SQL查询,如SELECT。3.掌握数据操作:创建表、插入、更新、删除数据。4.学习高级技巧:子查询和窗口函数。5.调试和优化:检查语法、使用索引、避免SELECT*,并使用LIMIT。

MySQL:结构化数据和关系数据库MySQL:结构化数据和关系数据库Apr 18, 2025 am 12:22 AM

MySQL通过表结构和SQL查询高效管理结构化数据,并通过外键实现表间关系。1.创建表时定义数据格式和类型。2.使用外键建立表间关系。3.通过索引和查询优化提高性能。4.定期备份和监控数据库确保数据安全和性能优化。

MySQL:解释的关键功能和功能MySQL:解释的关键功能和功能Apr 18, 2025 am 12:17 AM

MySQL是一个开源的关系型数据库管理系统,广泛应用于Web开发。它的关键特性包括:1.支持多种存储引擎,如InnoDB和MyISAM,适用于不同场景;2.提供主从复制功能,利于负载均衡和数据备份;3.通过查询优化和索引使用提高查询效率。

SQL的目的:与MySQL数据库进行交互SQL的目的:与MySQL数据库进行交互Apr 18, 2025 am 12:12 AM

SQL用于与MySQL数据库交互,实现数据的增、删、改、查及数据库设计。1)SQL通过SELECT、INSERT、UPDATE、DELETE语句进行数据操作;2)使用CREATE、ALTER、DROP语句进行数据库设计和管理;3)复杂查询和数据分析通过SQL实现,提升业务决策效率。

初学者的MySQL:开始数据库管理初学者的MySQL:开始数据库管理Apr 18, 2025 am 12:10 AM

MySQL的基本操作包括创建数据库、表格,及使用SQL进行数据的CRUD操作。1.创建数据库:CREATEDATABASEmy_first_db;2.创建表格:CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY,titleVARCHAR(100)NOTNULL,authorVARCHAR(100)NOTNULL,published_yearINT);3.插入数据:INSERTINTObooks(title,author,published_year)VA

See all articles

热AI工具

Undresser.AI Undress

Undresser.AI Undress

人工智能驱动的应用程序,用于创建逼真的裸体照片

AI Clothes Remover

AI Clothes Remover

用于从照片中去除衣服的在线人工智能工具。

Undress AI Tool

Undress AI Tool

免费脱衣服图片

Clothoff.io

Clothoff.io

AI脱衣机

AI Hentai Generator

AI Hentai Generator

免费生成ai无尽的。

热工具

螳螂BT

螳螂BT

Mantis是一个易于部署的基于Web的缺陷跟踪工具,用于帮助产品缺陷跟踪。它需要PHP、MySQL和一个Web服务器。请查看我们的演示和托管服务。

PhpStorm Mac 版本

PhpStorm Mac 版本

最新(2018.2.1 )专业的PHP集成开发工具

Dreamweaver CS6

Dreamweaver CS6

视觉化网页开发工具

SecLists

SecLists

SecLists是最终安全测试人员的伙伴。它是一个包含各种类型列表的集合,这些列表在安全评估过程中经常使用,都在一个地方。SecLists通过方便地提供安全测试人员可能需要的所有列表,帮助提高安全测试的效率和生产力。列表类型包括用户名、密码、URL、模糊测试有效载荷、敏感数据模式、Web shell等等。测试人员只需将此存储库拉到新的测试机上,他就可以访问到所需的每种类型的列表。

SublimeText3汉化版

SublimeText3汉化版

中文版,非常好用