mysql数据库设计规范.docx
- 文档编号:7773314
- 上传时间:2023-01-26
- 格式:DOCX
- 页数:11
- 大小:25.08KB
mysql数据库设计规范.docx
《mysql数据库设计规范.docx》由会员分享,可在线阅读,更多相关《mysql数据库设计规范.docx(11页珍藏版)》请在冰豆网上搜索。
mysql数据库设计规范
mysql,数据库设计规范
篇一:
MySQL设计规范
MySQL设计规范
MYSQL设计规范...........................................................................................................................................................................1
1.数据库设计..............................................................................................................................................................................1
字段..................................................................................................................................................................................1
表和字段命名.......................................................................................................................................................1字段结构................................................................................................................................................................2SQL语句.........................................................................................................................................................................2性能与效率.....................................................................................................................................................................3
定长与变长表.......................................................................................................................................................3运算与检索............................................................................................................................................................3结构优化与索引优化..........................................................................................................................................4查询优化................................................................................................................................................................4兼容性问题和效率查询语句.............................................................................................................................6分享一些SQL语句.............................................................................................................................................7
1.数据库设计
字段
表和字段命名
MySQL常见的表类型介绍:
A:
MyISAM数据表又分为MyISAMSatic(静态MyISAM)、MyISAMDynamic(动态MyISAM)、MyISAMCompressed(压缩MyISAM)。
B:
InnoDB占用空间大,但是支持事务处理。
C:
HELP表类型是放在内存中的速度很快。
所有数据表名称,只要其名称是可数名词,则必须以复数方式命名,例如:
oms_members(用户表)、oms_serverlist(主机表);存储多项内容的字段,或代表数量的字段,也应当以复数方式命名,例如:
params(parameters,自定义代码的参数个数)。
当几个表间的字段有关连时,要注意表与表之间关联字段命名的统一,如omsgroup表中的id与groupcorr表中的id。
(举例)
代表id自增量的字段,通常用以下几种形式:
?
?
?
最常用的核心id,或经常在URL中进行调用的,尽量用简写的形式,例如tid、pid、uid;有功能性作用,URL中偶尔用到的id,使用全称的形式,例如pluginid;没有功能性作用,只为管理和维护方便而设的id,可以使用全称的形式,也可只将其命名为
id。
字段结构
允许NULL值的字段,数据库在进行比较操作时,会先判断其是否为NULL,非NULL时才进行值的比对。
因此基于效率的考虑,所有字段均不能为空,即全部NOTNULL;
预计不会存储非负数的字段,例如各项id、数量等,必须设置为UNSIGNED类型。
UNSIGNED类型比非UNSIGNED类型所能存储的正整数范围大一倍,因此能获得更大的数值存储空间;
存储开关、选项数据的字段,通常使用tinyint
(1)非UNSIGNED类型,少数情况也可能使用enum()结果集的方式。
tinyint作为开关字段时,通常1为打开;0为关闭;-1为特殊数据,例如N/A(不可用);高于1的为特殊结果或开关二进制数组合;
任何类型的数据表,字段空间应当本着足够用,不浪费的原则,数值类型的字段取值范围见下表:
SQL语句
所有SQL语句中,除了表名、字段名称以外,全部语句和函数均需大写,应当杜绝小写方式或大小写混杂的写法。
例如select*frommembers;是不符合规范的写法。
很长的SQL语句应当有适当的断行,依据JOIN、FROM、ORDERBY等关键字进行界定。
通常情况下,在对多表进行操作时,要根据不同表名称,对每个表指定一个1~2个字母的缩写,以利于语句简洁和可读性。
如下的语句范例,是符合规范的:
性能与效率
定长与变长表
包含任何varchar、text等变长字段的数据表,即为变长表,反之则为定长表。
?
对于变长表,由于记录大小不同,在其上进行许多删除和更改将会使表中的碎片更多。
需要定期运
行OPTIMIZETABLE以保持性能。
而定长表就没有这个问题;
?
如果表中有可变长的字段,将它们转换为定长字段能够改进性能,因为定长记录易于处理。
但在试
图这样做之前,应该考虑下列问题:
?
使用定长列涉及某种折衷。
它们更快,但占用的空间更多。
char(n)类型列的每个值总要占用n个
字节(即使空串也是如此),因为在表中存储时,值的长度不够将在右边补空格;
?
而varchar(n)类型的列所占空间较少,因为只给它们分配存储每个值所需要的空间,每个值再加
一个字节用于记录其长度。
因此,如果在char和varchar类型之间进行选择,需要对时间与空间作出折衷;
?
变长表到定长表的转换,不能只转换一个可变长字段,必须对它们全部进行转换。
而且必须使用一
个ALTERTABLE语句同时全部转换,否则转换将不起作用;
?
?
有时不能使用定长类型,即使想这样做也不行。
例如对于比255字符更长的串,没有定长类型;在设计表结构时如果能够使用定长数据类型尽量用定长的,因为定长表的查询、检索、更新速度都
很快。
必要时可以把部分关键的、承担频繁访问的表拆分,例如定长数据一个表,非定长数据一个表;
进行表结构设计时,应当做到恰到好处,反复推敲,从而实现最优的数据存储体系。
运算与检索
数值运算一般比字符串运算更快。
例如比较运算,可在单一运算中对数进行比较。
而串运算涉及几个逐字节的比较,如果串更长的话,这种比较还要多。
如果串列的值数目有限,应该利用普通整型或emum类型来获得数值运算的优越性。
更小的字段类型永远比更大的字段类型处理要快得多。
对于字符串,其处理时间与串长度直接相关。
一般情况下,较小的表处理更快。
对于定长表,应该选择最小的类型,只要能存储所需范围的值即可。
例如,如果mediumint够用,就不要选择bigint。
对于可变长类型,也仍然能够节省空间。
一个TEXT类型的值用2字节记录值的长度,而一个LONGTEXT则用4字节记录其值的长度。
如果存储的值长度永远不会超过64KB,使用TEXT将使每个值节省2字节。
结构优化与索引优化
索引不是万能的。
索引能加快查询速度,而索引优化和查询优化是相辅相成的,既可以依据查询对索引进行优化,也可以依据现有索引对查询进行优化,这取决于修改查询或索引,哪个对现有产品架构和效率的影响最小。
首先,根据产品的实际运行和被访问情况,找出哪些SQL语句是最常被执行的。
最常被执行和最常出现在程序中是完全不同的概念。
最常被执行的SQL语句,又可被划分为对大表(数据条目多的)和对小表(数据条目少的)的操作。
无论大表或小表,有可分为读(SELECT)多、写(UPDATE/INSERT)多或读写都多的操作。
对常被执行的SQL语句而言,对大表操作需要尤其注意:
?
写操作多的,通常可使用写入缓存的方法,先将需要写或需要更新的数据缓存至文件或其他表,
定期对大表进行批量写操作,同时,应尽量使得常被读写的大表为定长类型,即便原本的结构
中大表并非定长。
大表定长化,可以通过改变数据存储结构和数据读取方式,将一个大表拆成
一个读写多的定长表,和一个读多写少的变长表来实现;
?
读操作多的,需要依据SQL查询频率设置专门针对高频SQL语句的索引和联合索引。
而小表就相对简单,加入符合查询要求的特定索引,通常效果比较明显。
同时,定长化小表也有益于效率和负载能力的提高。
字段比较少的小定长表,甚至可以不需要索引。
其次,看SQL语句的条件和排序字段是否动态性很高(即根据不同功能开关或属性,SQL查询条件和排序字段的变化很大的情况),动态性过高的SQL语句是无法通过索引进行优化的。
惟一的办法只有将数据缓存起来,定期更新,适用于结果对实效性要求不高的场合。
MySQL索引,常用的有PRIMARYKEY、INDEX、UNIQUE几种,详情请查阅MySQL文档。
通常,在单表数据值不重复的情况下,PRIMARYKEY和UNIQUE索引比INDEX更快,请酌情使用。
事实上,索引是将条件查询、排序的读操作资源消耗,分布到了写操作中,索引越多,耗费磁盘空间越大,写操作越慢。
因此,索引决不能盲目添加。
对字段索引与否,最根本的出发点,依次仍然是SQL语句执行的概率、表的大小和写操作的频繁程度。
查询优化
MySQL中并没有提供针对查询条件的优化功能,因此需要开发者在程序中对查询条件的先后顺序人工进行优化。
例如如下的SQL语句:
事实上无论a>’0’还是b 开发者需要牢记这个原则:
最先出现的条件,一定是过滤和排除掉更多结果的条件;第二出现的次之;以此类推。
因而,表中不同字段的值的分布,对查询速度有着很大影响。
而ORDERBY中的条件,只与索引有关,与条件顺序无关。
除了条件顺序优化以外,针对固定或相对固定的SQL查询语句,还可以通过对索引结构进行优化,进而实现相当高的查询速度。
原则是:
在大多数情况下,根据WHERE条件的先后顺序和ORDERBY的排序字段的先后顺序而建立的联合索引,就是与这条SQL语句匹配的最优索引结构。
尽管,事实的产品中不能只考虑一条SQL语句,也不能不考虑空间占用而建立太多的索引。
同样以上面的SQL语句为例,最优的当table表的记录达到百万甚至千万级后,可以明显的看到索引优化带来的速度提升。
依据上面条件优化和索引优化的两个原则,当table表的值为如下方案时,可以得出最优的条件顺序方案:
EXPLAIN语句是检测索引和查询能否良好匹配的简便方法。
在phpMyAdmin或其他MySQL客户端中运行EXPLAIN+查询语句,例如EXPLAINSELECT*FROMtableWHEREa>’0’ANDb 值得提出的是,Usingfilesort是最不应当出现的情况,如果EXPLAIN得出此结果,说明数据库为这个查询专门建立了一个用以缓存结果的临时表文件,并在查询结束后删除。
众所周知,硬盘I/O速度始终是计算机存储的瓶颈,因此,查询中应当尽全力避免高执行频率的SQL语句使用filesort。
尽管,开发者永远都不可能保证产品中的全部SQL语句都不会使用filesort。
限于篇幅,本文档远远没有涵盖数据库优化的方方面面,例如:
联合索引与普通索引的可重用性、
篇二:
Mysql数据库设计规范
Mysql数据库规范
Version创建时间XX-08-01
1.命名规范
(1)库名、表名、字段名必须使用小写字母,并采用下划线分割。
(2)库名、表名、字段名尽量不要超过32个字符。
(3)库名、表名、字段名必须见名知意。
命名与业务、产品线等相关联。
(4)库名、表名、字段名禁止使用MySQL保留字。
(保留字列表见refman//en/)
2.基础规范
(1)使用INNODB存储引擎。
(2)表字符集使用UTF8字符集,校验字符集使用utf8_general_ci
(3)所有表都需要添加注释;除主键外的其他字段都需要增加注释。
(4)禁止在数据库中存储图片、文件等大数据。
(5)每张表数据量建议控制在5000W以内。
(6)禁止在线上做数据库压力测试。
(7)禁止从测试、开发环境直连线上数据库。
3.库表设计
(1)禁止使用分区表。
(2)将大字段、访问频率低的字段拆分到单独的表中存储,分离冷热数据。
(3)采用合适的分库分表策略。
例如千库十表、十库百表等。
4.字段设计
(1)建议使用UNSIGNED存储非负数值。
(2)建议使用INTUNSIGNED存储IPV4。
(3)用DECIMAL代替FLOAT和DOUBLE存储精确浮点数。
例如与货币、金融相关的数据。
(4)INT类型固定占用4字节存储,
(5)区分使用TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT数据类型。
例如取值范围为0-80时,使用TINYINTUNSIGNED。
(6)强烈建议使用TINYINT来代替ENUM类型。
(7)尽可能不使用TEXT、BLOB类型。
(8)禁止在数据库中存储明文密码。
(9)使用尽可能小的VARCHAR字段。
VARCHAR(N)中的N表示字符数而非字节数。
(10)区分使用DATETIME和TIMESTAMP。
存储年使用YEAR类型。
存储日期使用DATE类型。
存储时间(精确到秒)建议使用TIMESTAMP类型。
(11)所有字段均定义为NOTNULL。
5.索引规范
(1)单张表中索引数量不超过5个。
(2)单个索引中的字段数不超过5个。
(3)索引名必须全部使用小写。
篇三:
设计MySql数据库的基本原则
MySQL对于成为一个非常快速的数据库服务器有着当之无愧的名声,它也非常容易设置和使用。
随着它作为网站后端数据库得声望日增,其效果在去年开始有明显提高。
但是很多MySQL用户更多地知道如何创建一个数据库并编写对它的查询。
就像成千上万的人通过载闲暇时用Linux做实验来学习Unix那样,很多人通过玩MySQL学习关系数据库。
这些MySQL新手的大多数既没有关系数据库理论的背景,又没有时间阅读MySQL手册全文。
因此,我们决定研究某些方法,你可以用针对优化性能来调节MySQL。
在读完本文后,你将理解一些帮助你设计你的MySQL数据库和查询的技术,值得你的应用很有效率。
我们将假定你熟悉MySQL和SQL基础,但不假定你有这两方面的广博知识。
只存储你需要的信息
这听上去是常识,但人们常常采取“厨房下水道”的方式进行数据库设计。
他们认为可能项要得每样东西都要存储并设计数据库保存所有者这些数据。
你需要对你的需求现实些,并确定取确实需要什么信息。
你常常能随意产生一些数据而不把它存在数据库表中。
在这种情况下,从一个应用开发者的角度看也有道理这样做。
例如,在线目录的产品表可能包含各种产品的名称、介绍、尺寸、重量和价格。
除了价格,你可能想存储每个项目相关的税和运输成本。
但实际上不必这样做。
首先税和运输成本可以方便地(由你的应用或MySQL)计算出来。
其次,如果税和运输成本改变了,你可能必须编写必要的查询更新每个产品记录中的税和运输的费率。
有时人们认为这太难不能在以后往数据库表中加入字段,所以他们感觉不得不定义尽可能多的列。
这是明显的概念错误。
在MySQL中,你可以用ALTERTABLE命令方便地修改表定义以适应你改变的需求。
例如,如果你突然认识到你需要给你的产品表增加一个级别列(可能你想允许用户在你的目录中给产品评级),你可以这样做:
ALTERTABLEproductsADDrankINTEGER
这给你的产品表增加了一个整数类型的级别列,你能用ALTERTABLE做什么的完整介绍参见MySQL手册。
只要求你需要的东西--要清晰
就像说“只存储你需要的东西”那样,这可能看来是常识,但这一点常常被忽视,为什么呢?
因为在一个应用开发时,需求经常改变,所以很多查询最终看来是这样:
SELECT*FROMsometable
当你不能肯定你将需要哪一列时,要求所有列明显是最省力的事情,然而随着你的表不断增大和修改,这可能变成一个性能问题。
最好是在你的最初开发完成后再花些时间并确定你真正从你的查询中需要什么:
SELECTname,rank,descriptionFROMproducts
这带来了一个相关的观点,即代码维护比性能更重要。
大多数变成语言(Perl、Python、PHP、Java等)允许通过字段名和数字编号访问一条查询的结果,这意味着你可以访问命名字段或字段0都可以得到相同的数据。
长期看,最好使用列名而不是其编号位置,为什么?
因为一个表中或一条查询中地列的相对位置可以改变。
它们在表中可能因为重复使用ALTERTABLE而改变,它们在查询中将因重写了查询而忘记更新应用逻辑来匹配而改变。
当然,你仍然需要小心改变列名!
但如果你使用列名而非标号位置,如列名改变,你可以用grep搜索源代码或使用编辑器的搜索能力查找你需要修改的代码。
规范化你的表结构
如果你以前从未听说过“数据规范化”,不要害怕。
规范化可能是一个复杂的专题,你可以从只理解最基本的规范化概念中正真正获益。
理解它的最容易的方法是认为你的表是一个电子报表。
如果你想以一个报表跟踪你的CD收藏,你可以如图1种那样进行设计:
图1
引用
albumtrack1track2track10
------------------------
BillboardTopHits-1984LoverboyShoutSt.Elmo'sFire
(BillyOcean)(TearsforFears)(JohnParr)
这看上去很合理。
大多数CD只有10首曲子,对否?
不尽然。
如果你拥有一张有100首曲子的CD且几张超过20首改怎么办。
这意味着用这种方法,在极端的情况下,你将需要一个非常宽的表格(或一个超过100个字段的表)来保存所有的数据。
规范化表结构的目标是使“空单元”的数量最少,在上述CD表的情况下,如果你允许CD可能包含100首曲子,你会有很多这样的空单元。
不管你何时处理可能扩展到类似该CD表那样数量的字段列表,它是你需要将你的数据分割成2个或更多表的标志,然后你一起访问并获得你需要的数据。
很多关系数据库的新手不真正知道关系数据库管理系统中关系是什么。
简单地说,就像一组信息存在可以基于共性数据联结(JOIN)在一起的不同表中,很不幸,这听上去更学术化和含糊,但CD数据库提出了一个具体情况,我们可以研究如何规范数据。
每个CD列表有一个固定的属性(标题、艺术家、年份、分类)集和一个不定的属性(曲目表)集的理解给了我们一些如何分成成能相互关联的表的思路。
你可以创建一个所有专辑及其固定属性的表,另一个包含这些专辑的所有曲目的表。
这样不是水平思考(像表格),你垂直思考--就好像你创建列表而不是行--并建立一个如图2的表结构:
专辑的编号(MySQL镜自动为你生成,因为我们在列上使用了AUTO_INCREMENT属性)关联不同曲目到一给定专辑,tracks表中的album_id字段匹配专辑表中的一个id。
这样要获得
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- mysql 数据库 设计规范
![提示](https://static.bdocx.com/images/bang_tan.gif)