服务器 数据库设计技巧--2

8.应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。如:

select id from t wheresubstring(name,1,3)='abc'--name以abc开头的id

select id from t wheredatediff(day,createdate,'2005-11-30')=0--‘2005-11-30’生成的id

应改为:

select id from t where name like 'abc%'

select id from t wherecreatedate>='2005-11-30' and createdate<'2005-12-1'

9.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。

10.在使用索引字段作为条件时,如果该索引是复合索引,那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引,否则该索引将不会被使用,并且应尽可能的让字段顺序与索引顺序相一致。

11.很多时候用 exists是一个好的选择,尽量用exists代替in:

select num from a where num in(select numfrom b)

用下面的语句替换:

select num from a where exists(select 1from b where num=a.num)

SELECT SUM(T1.C1)FROM T1 WHERE(

(SELECT COUNT(*)FROM T2 WHERET2.C2=T1.C2>0)

用下面的语句替换:

SELECT SUM(T1.C1) FROM T1WHERE EXISTS(

SELECT * FROM T2 WHERE T2.C2=T1.C2)

两者产生相同的结果,但是后者的效率显然要高于前者。因为后者不会产生大量锁定的表扫描或是索引扫描。

如果你想校验表里是否存在某条纪录,不要用count(*)那样效率很低,而且浪费服务器资源。可以用EXISTS代替。如:

IF (SELECT COUNT(*) FROM table_name WHEREcolumn_name = 'xxx')

可以写成:

IF EXISTS (SELECT * FROM table_name WHEREcolumn_name = 'xxx')

经常需要写一个T_SQL语句比较一个父结果集和子结果集,从而找到是否存在在父结果集中有而在子结果集中没有的记录,如:

SELECT a.hdr_key FROM hdr_tbl a---- tbl a 表示tbl用别名a代替

WHERE NOT EXISTS (SELECT * FROM dtl_tbl bWHERE a.hdr_key = b.hdr_key)

SELECT a.hdr_key FROM hdr_tbl a

LEFT JOIN dtl_tbl b ON a.hdr_key =b.hdr_key WHERE b.hdr_key IS NULL

SELECT hdr_key FROM hdr_tbl

WHERE hdr_key NOT IN (SELECT hdr_key FROMdtl_tbl)

三种写法都可以得到同样正确的结果,但是效率依次降低。

效率:exists>inner(left,right)join>in

12.尽量使用表变量来代替临时表。如果表变量包含大量数据,请注意索引非常有限(只有主键索引)。

13.避免频繁创建和删除临时表,以减少系统表资源的消耗。

14.临时表并不是不可使用,适当地使用它们可以使某些例程更有效,例如,当需要重复引用大型表或常用表中的某个数据集时。但是,对于一次性事件,最好使用导出表。

15.在新建临时表时,如果一次性插入数据量很大,那么可以使用 select into 代替 create table,避免造成大量 log ,以提高速度;如果数据量不大,为了缓和系统表的资源,应先createtable,然后insert。

16.如果使用到了临时表,在存储过程的最后务必将所有的临时表显式删除,先 truncate table TableName,然后 drop tableTableName,这样可以避免系统表的较长时间锁定。

17.在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ,在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。

18.尽量避免大事务操作,提高系统并发能力。

19.尽量避免向客户端返回大数据量,若数据量过大,应该考虑相应需求是否合理。

20. 避免使用不兼容的数据类型。例如float和int、char和varchar、binary和varbinary是不兼容的。数据类型的不兼容可能使优化器无法执行一些本来可以进行的优化操作。例如:

SELECTname FROM employee WHERE salary > 60000

在这条语句中,如salary字段是money型的,则优化器很难对其进行优化,因为60000是个整型数。我们应当在编程时将整型转化成为钱币型,而不要等到运行时转化。

21.充分利用连接条件,在某种情况下,两个表之间可能不只一个的连接条件,这时在 WHERE 子句中将连接条件完整的写上,有可能大大提高查询速度。

例:

SELECT SUM(A.AMOUNT) FROM ACCOUNT A,CARD BWHERE A.CARD_NO = B.CARD_NO

SELECT SUM(A.AMOUNT) FROM ACCOUNT A,CARD BWHERE A.CARD_NO = B.CARD_NO AND A.ACCOUNT_NO=B.ACCOUNT_NO

第二句将比第一句执行快得多。

22、使用视图加速查询

把表的一个子集进行排序并创建视图,有时能加速查询。它有助于避免多重排序操作,而且在其他方面还能简化优化器的工作。例如:

SELECT cust.name,rcvbles.balance,……other columns

FROM cust,rcvbles

WHERE cust.customer_id =rcvlbes.customer_id

AND rcvblls.balance>0

AND cust.postcode>“98000”

ORDER BY cust.name

如果这个查询要被执行多次而不止一次,可以把所有未付款的客户找出来放在一个视图中,并按客户的名字进行排序:

CREATE VIEW DBO.V_CUST_RCVLBES

AS

SELECT cust.name,rcvbles.balance,……other columns

FROM cust,rcvbles

WHERE cust.customer_id =rcvlbes.customer_id

AND rcvblls.balance>0

ORDER BY cust.name

然后以下面的方式在视图中查询:

SELECT * FROM V_CUST_RCVLBES

WHERE postcode>“98000”

视图中的行要比主表中的行少,而且物理顺序就是所要求的顺序,减少了磁盘I/O,所以查询工作量可以得到大幅减少。

23、能用DISTINCT的就不用GROUP BY

SELECT OrderID FROM Details WHERE UnitPrice> 10 GROUP BY OrderID

可改为:

SELECT DISTINCT OrderID FROM Details WHEREUnitPrice > 10

24.能用UNION ALL就不要用UNION

UNION ALL不执行SELECT DISTINCT函数,这样就会减少很多不必要的资源。

25.尽量不要用SELECT INTO语句。

SELECTINTO 语句会导致表锁定,阻止其他用户访问该表。

上面我们提到的是一些基本的提高查询速度的注意事项,但是在更多的情况下,往往需要反复试验比较不同的语句以得到最佳方案。最好的方法当然是测试,看实现相同功能的SQL语句哪个执行时间最少,但是数据库中如果数据量很少,是比较不出来的,这时可以用查看执行计划,即:把实现相同功能的多条SQL语句考到查询分析器,按CTRL+L看查所利用的索引,表扫描次数(这两个对性能影响最大),总体上看询成本百分比即可。

http://www.cnblogs.com/yongheng178/archive/2010/09/18/1829989.html

数据库命名规范

表1. 基本数据库对象命名

数据库对象

前缀

举例

表(Table)

Student

字段、列(Column)

Title,CustomerName

视图(View)

v或vw

vActivity,VW_Car

存储过程(Stored procedure)

pr或proc

prDelOrder

触发器(Trigger)

tr或trig

trOrder_D

索引(Index)

ix_

ix_CustomerID

主键(Primary key)

PK_

PK_Admin

外键(Foreign key)

Fk_

FK_Order_OrderType

Check约束(Check Constraint)

CH_

CK_TableColumn

Unique约束

UQ_

UQ_TableColumn

Default默认值

DF_

DF_Status

用户定义数据类型(User-defined data type)

udt

udtPhone

用户定义函数(User-defined function)

FN或Fun_

fnDueDate

(1)表格、字段的命名:

单数表名、字段名 还是 复数表名、字段名 (用单数来命名表名)?可能大家很少会考虑到给表名起单数还是复数,比如,对存储客人信息的表,我们应该起Customer,还是Customers?我主张起单数表名,下面是来自《SQL Server 2000 宝典》的一段引用:主张用复数表名的阵营认为:表是由一组记录构成的,所以应当使用复数名词来命名它。他们经常使用的理由是:客户表是客户们的集合,而集合意味着多个,因此应当称他们为Customers表。除非你只有一个客户,但这种情况你根本用不着数据库。根据笔者的非正式调查,有3/4的SQL Server开发人员支持使用单数命名。这些开发人员认为,客户表是客户的集合,而不是客户们的集合。一组行不应当也不会被成为rows set(行们的集合),而会被称为row set(行集)。并且,通常在讨论时人们会使用单数名称来称呼表,说Customer表比说Customers表听起来更为清晰,避免无谓的表格后缀(没必要添加无所谓的后缀)。特殊情况下可以加复数,如用户表User,而User是数据库关键字,如果使用User表的时候,一般这样select * from [User]。此时最好使用Users

这两点我想大家都知道:1、表是用来存储数据信息的。2、表是行的集合。那么如果表名已经能够很好地说明其包含的数据信息,就不需要再添加体现上面两点的后缀了。

(2)多对多关系中连接表(中间表)的命名

大家知道,如果要实现两个实体间的多对多关系,需要三张表,其中一张是解析表。考虑下面这样一个多对多关系,这是一个经典的学生选课问题:一个学生可以选很多门课,一门课可以有很多学生。此时为了实现上面的关系,就需要一张解析表(这张表只存储学生ID和课程ID,而学生的信息和课程信息分别存在各自的表中),这个表的起名,建议的写法是将两个表的表名合并(如果表名比较长可做简化),此处如 StudentCourse。这个表中字段分别命名为StudentId、CourseID(既是此表的复合主键,同时分别为连接Student表和Course表的外键,等下到主键和外键的命名处再说),这样就实现了学生和课程之间的多对多关系,当然,这个关系还可以加点额外的东西,比如给StudentCourse表中加AccessLevel字段,值域D{只读,完全,禁止},就可以实现访问级别。

(3)约定俗成的字段名前/后缀

数据库开发的时间久了,慢慢就会摸索出一个规律来:就是很多的字段都有些共同的特性。比如说,有的字段是代表时间的(例如发帖时间,评论时间),有的是代表数量的(例如浏览数,评论数),有的是代表真假类型的(例如是否将博客随笔显示在首页)。对于这种同一类型的字段,应该使用统一的 前缀或者 后缀去标识它。

我们来举几个例子看得更明白一点。

以大家都熟悉的论坛来说,需要记录会员最后一次登录的时间,这时候一般人都会把这个字段命名为LoginTime 或者 LoginDate。这时候,已经产生了一个歧义:对于另一名开发者来说,如果仅看表的字段名称,不去看表的内容,很容易将LoginTime理解成 登录的次数,因为,Time还有一个很常用的意思,就是次数。为了避免这种情况发生,应该明确的规定:所有表示时间的字段,统一以 Date 来作为结尾。我们经常需要统计发帖数、回帖数信息,这时候,开发人员通常会这样去命名字段:PostAmount、PostTime、PostCount,同样,由于Time的歧义,我们首先排除掉不使用PostTime作为字段名。接下来,Amount 和 Count 都可以表示计数的意思,用哪个合适呢?这里,我推荐使用Count。为什么呢?如果你做过Asp开发,相信一定知道 RecordCount 这个属性,命名的时候有一个原则:就是使用约定俗成的名称,而不要去自创名称。既然微软都用Count后缀来表示数目,我们为什么不呢?

于是,所有表示数目的字段,都应该以Count作为结尾。将这一概念做以推广,很容易得出,浏览次数为 ViewCount,登录次数为LoginCount 等等。再举一个例子,我们很少在数据库里直接保存图片等二进制数据,通常是仅保存图片的URL路径;在文章管理系统中,如果是转载文章,也会用到记录文章出处的字段。个人建议所有代表链接的字段,均为Url结尾。于是,图片路径的字段命名为 ImageUrl,文章出处字段的命名为SourceUrl。

最后一个例子,我们经常需要用到布尔值,比方说,这篇随笔要不要显示到首页,这篇随笔是不是保存到草稿箱等等。同样,按照微软的建议,布尔类型的值均以 Is、Has、Exist 或者 Can开头。如果让我来建表示是否将随笔放到首页的字段,它的名字一定是这样的:IsOnIndex

(4)字段命名时需注意的一个问题

我发现有很多开发人员喜欢给字段加上表名作为它的前缀,举个例子,如果有个表叫User,那么他就会将这个表中的字段命名为:UserId、UserPassword、UserName、UserPhone 等等。个人认为,这是没有必要的,因为你已经确切的知道了这个表存储的是User的信息,那么其中的字段必然是针对于User的。而且,在Join连接操作中,你的SQL代码看上去也会更加的精简一些,诸如 [User].UserName =Aritcle.ArticleAuthor 这样的代码完全可以实现为 [User].Name = Article.Author。(没必要添加无所谓的后缀)

这里还存在一个特例,就是表的外键包含的字段。在这种情况下,我倾向于使用表名+ID 的方式,比如 CategoryId、UserId 等。假设有表Article,那么它的主键我会命名为Id,关联用户表User的外键包含的字段,我会命名为UserId。之所以这样,是因为在语言(比如C#)中创建对象时,有时候会使用代码生成器(根据数据库的字段名生成对象的字段、属性名),此时生成的代码更规整一些。(对于外键要用到,外表名+Id)

(5)外键的命名

外键的命名为 fk_外键所在的表名_外键引用的表名。因为外键所在的表为从表,所以上式可以写为 fk_从表名_主表名。外键包含的字段的命名,外键包含的字段和外键是完全不同的概念。外键包含字段的命名,建议为:外键所在的表名 + Id。考虑这样一个关系,表Hotel,字段Id, Name, CityId。表City,字段Id,Name。因为一个城市可能有好多家酒店,所以是一个一对多的关系,City是主表(1方),Hotel是从表(多方)。在Hotel表中,CityId是做为外键使用。在实现外键的时候我们可以这样写:

Alter Table HotelInfo

Add Constraint fk_HotelInfo_City ForeignKey (CityID) References City(ID)

On Delete No Action On update No Action

很明显,fk_HotelInfo_City是外键的名字,CityId是外键包含的字段的名字。

在创建数据库表的时候,一般需要写成三个SQL脚本文件。第一个文件仅包含所有的创建表的SQL语句,即CreateTable 语句。第二个文件包含删除关系和表的语句,其中,所有删除关系的语句,即Drop Constraint 语句集中在这个文件的上半部分,所有删除表的语句,Drop Table语句,集中在这个文件的下半部分。第三个文件包含建立表之间关系的语句。这种做法会在你移植数据库的时候产生较大的便利,原因我就不解释了,您一试便知。而对于多对多关系中解析表的外键包含的字段,顺理往下推,我们可以这样写(再次回到学生选课的多对多例子中):

建立解析表StudentCourse与Student表的外键关系:

Alter Table StudentCourse

Add Constraint fk_StudentCourse_StudentForeign Key (StudentId) References Student (Id)

On Delete No Action On Update No Action

建立解析表StudentCourse与Course 表的外键关系:

Alter Table StudentCourse

Add Constraint fk_StudentCourse_CourseForeign Key (CourseId) References Course(Id)

On Delete No Action On Update No Action

(6)以Not Null的思路建表

我发现很多开发人员在建表的时候,如果要新建一个字段,他的思路是这样的:默认这个字段是可以为Null的,然后去判断是不是非要NotNull不可,如果不是这样,OK,这个字段可以为Null,接着继续进行下一个字段。结果往往是一张表除了主键以外所有的字段都可以为Null。之所以会有这样的思路,是因为Null好啊,程序不容易出错啊,你插入记录的时候如果不小心忘输了一个字段,程序依然可以Run,而不会出现 “XX字段不能为Null”的错误消息。但是,这样做的结果却是很严重的,也会使你的程序变得更加繁琐,你不得不进行一些无谓的空值处理,以避免程序出错。更糟的是,如果一些重要数据,比如说订单的某一项值为Null了,那么大家知道,任何值与Null相操作(比如加减乘除),结果都是Null,导致的结果就是订单的总金额也为Null。

你可以运行下面的代码尝试一下:

Select Null + 5 As Result

你可能会说,就算我将字段设置成Not Null,但是它依然可以接受空字符串,这样一来在程序中还是要进行空值处理。请别忘了,数据库还赋予你一个强力武器,就是 Check 约束,当你需要确保一个字段既不可以为Null,又不可以为空的时候,可以这么写:

ColumnName Varchar(50) Not Null Constraint ck_ColumnNameCheck(Len(ColumnName) > 0)

所以,合理的思维方式应该是这样的:默认这个字段是 Not Null的,然后判断这个字段是不是非为Null不可,如果不是这样,OK,这个字段是Not Null的,进行下一个字段。

一个建表的范例脚本个建表的范例脚本

我正在建立我自己的个人空间,其中的文章表是这样写的:

Create Table Article

(

Id Int Identity(1,1) Not Null,

Title Varchar(50) Not Null Constraint uq_ArticleTitleUnique,

Keywords Varchar(50) Not Null,

Abstract Varchar(500) Not Null,

Author Varchar(50) Not Null Default '张三',

Type TinyInt Not Null Default 0 Constraintck_ArticleType Check(Type in (0,1,2)), -- 0,原创;1,编译;2,翻译

IsOnIndex Bit Not Null Default 1, -- 是否显示在首页

Content Text Not Null,

SourceCode Varchar(100) Null, -- 程序源码的下载路径

Source Varchar(50) Not Null Default 'TraceFact', -- 文章出处

SrcUrl Varchar(150) Null, -- 文章出处的URL

PostDate DateTime Not Null Default GetDate(),

ViewCount Int Not Null Default 0,

ClassId Int Not Null -- 外键包含的字段,文章类别

Constraint pk_Article Primary Key(Id) -- 建立主键

)

可以看到,在这里我使用了Check 约束,以确保文章类型只能为0,1,2。这里,我想说的是Check约束的命名规则:尽管Check约束是针对字段的,但在同一数据库中,却不能有同名的Check约束。所以,建议使用 ck_ + 表名 + 字段名来命名它,比如这个范例脚本中的 ck_ArticleType。除此以外,我还使用了Unique约束,以确保文章标题的唯一性。由于这是我的博客文章表,不应该出现重复的题目,这样可以避免在使用 Insert 语句时插入重复值。类似于Check约束,这里的命名规则是:uq_+ 表名 + 字段名。

(7)触发器的命名

由三部分构成:

前缀(tr),描述了数据库对象的类型。

基本部分,描述触发器所加的表。

后缀(_I、_U、_D),显示了修改语句(Insert,Update及Delete)

(8)存储过程的命名

大家知道,系统存储过程的前缀是 sp_,为了避免将用户存储过程与系统存储过程混淆,这里我推荐大家使用 pr 作为自己定义的存储过程的命名。

同时,命名的规则是:采用自解释型的命名,比如:prGetItemById。

这里,有个有意思的地方值得深思。我们按上面规则命名存储过程的时候,可以用两种方式:

动词放前面,名词放后面。

名词放前面,动词放后面。

我个人推荐使用方式2,现在说说原因:

以NorthWind 为例,假如对于 Employees 表你有4个存储过程,分别命名为:prEmployeeInsert、prEmployeeUpdate、prEmployeeDelById、prEmployeeGetById

同时对于 Products 表你有类似的4个存储过程,分别命名为:prProductInsert、prProductUpdate、prProductDelById、prProductGetById

这时,你用企业管理器查看时,会发现存储过程像下面这样整整齐齐的排列:

prEmployeeDelById

prEmployeeGetById

prEmployeeInsert

prEmployeeUpdate

prProductDelById

prProductGetById

prProductInsert

prProductUpdate

很容易就会发现,当你的存储过程越多时,这种命名方法的优势就越明显。

(9)存储过程中参数的命名

存储过程中的入口参数,我建议与其对应的字段名相同,这里,假设要写一个更新Northwind数据库Employees表的存储过程(做了简化),可以这么写:

Create Procedure prEmployeeUpdateById

@EmployeeId Int,

@LastName NVarchar(20),

@FirstName NVarchar(10)

As

Update Employees Set

LastName = @LastName,

FirstName = @FirstName

Where

EmployeeId = @EmployeeId

If @@error <> 0 or @@RowCount = 0

Raiserror 16001 ‘更新用户失败’

总结:

(Id统一这样命名不进行ID获取id这样的命名)

1.数据库表的命名:用头个字母大写的方式进行命名,对于有多个单词组成的在适当看具体情况进行裁剪。

2.对于字段命名,采取和表命名一样的规则,个人习惯的命名规则是:表名+字段名。(自己觉得可以允许这样适当的冗余)

3.外键命名:外表名+Id

4.对于存储过程的命名遵循上面所说的原则,pr+名词(通常指表名)+动词操作。

5.对于存储过程中参数的命名:和该存储过程所作用的数据表的相关字段一样也就是在其前面加一个@。对于存储过程中相应临时参数的命名也是一样的,用头个字母大写的方式进行命名。

6.对于触发器的命名:用tr前缀:tr+所作用的表名+After/Before(I/D/U) trProductAfterI

原文发布于微信公众号 - Golang语言社区(Golangweb)

原文发表时间:2016-11-03

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏C#

Oracle常用的SQL方法总结

 在项目中一般需要对一些数据进行处理,以下提供一些基本的SQL语句:    1.基于条件的插入和修改:需要在表中插入一条记录,插入前根据key标识判断。如果标识...

2129
来自专栏杨建荣的学习笔记

有趣的rownum测试(r10笔记第49天)

rownum在平时的使用中总是一个很自然的语法。如果说这个rownum是否有规律,可能很多人都会模棱两可。到底是还是不是呢,我们来做几个测试来说明。 这个结果也...

34612
来自专栏互联网开发者交流社区

数据库的总结

1554
来自专栏个人随笔

初识MySQL

sc delete mysql 删除服务! 一:数据库介绍 引入: 我们之前使用的数据都是存储在内存中的!比如说我们写一个注册功能。 我们首先需要在内存中创建...

3907
来自专栏电光石火

mybatis获取update的id

平常我门都是更新数据,用更新的条件再查询一次,得到更新的记录。这样我门就进行了两次数据库操作,链接了两次数据库。增加了接口的处理事件,因为链接数据库是很耗时的操...

2K8
来自专栏Java帮帮-微信公众号-技术文章全总结

Web-第二十四天 Oracle学习【悟空教程】

ORACLE数据库系统是美国ORACLE公司(甲骨文)提供的以分布式数据库为核心的一组软件产品,是目前最流行的客户/服务器(CLIENT/SERVER)或B/S...

2862
来自专栏FreeBuf

基于约束的SQL攻击

前言 值得庆幸的是如今开发者在构建网站时,已经开始注重安全问题了。绝大部分开发者都意识到SQL注入漏洞的存在,在本文我想与读者共同去探讨另一种与SQL数据库相关...

2265
来自专栏Venyo 的专栏

使用 Elasticsearch 的 NGram 分词器处理模糊匹配

接到一个任务:用 Elasticsearch 实现搜索银行支行名称的功能。大概就是用户输入一截支行名称或拼音首字母,返回相应的支行名称。比如,用户输入"工行"或...

4536
来自专栏黑泽君的专栏

day37_Spring学习笔记_05_CRM_01

CRM : custom releation manager 客户关系管理系统,用于维护客户和公司之间关系。 我们要做的是:学校 和 大家 之间关系。

772
来自专栏微服务生态

用尽洪荒之力整理的Mysql数据库32条军规

2、控制单表数据量 int型不超过1000w,含char则不超过500w; 合理分表; 限制单库表数量在300以内;

913

扫码关注云+社区

领取腾讯云代金券