一、设计原则
1.选择优化的数据类型
MySQL支持很多种不同的数据类型，并且选择正确的数据类型对于获得高性能至关重要。不管选择何种类型，下面的简单原则都会有助于做出更好的选择：
(1).更小通常更好
一般来说，要试着使用正确地存储和表示数据的最小类型。更小的数据类型通常更快，因为它们使用了更少的磁盘空间、内存和CPU缓存，而且需要的CPU周期也更少。
但是要确保不人低估需要保存的值，在架构中的多个地方增加数据类型的范围是一件极其费力的工作。如果不确实需要什么数据类型，就选择你认为不会超出范围的最小类型。
(2).简单就好
越简单的数据类型，需要的CPU周期就越少。例如：比较整数的代价小于比较字符，因为字符集和排序规则使字符比较更复杂。
(3).尽量避免空（NULL）
要尽可地把字段定义为NOT NULL 。即使应用程序无须保存NULL，也有许多表包含了可为空的列，这仅仅是因为它为默认选项，除非真的要保存NULL，否则就把列定义为NOT NULL。
因为MySQL难以优化了使用了可空列的查询，它会使索引、索引统计和值更加复杂。可空列需要更多的存储空间，还需要在MySQL内部进行特殊处理。当可空列被索引的时候，每条记录都需要一个额外的字节，还能导致MyISAM中固定大小的索引（例如：一个整数列上的索引）变成可变大小的索引。
即使要在表中存储可为空的字段，也是有办法不使用NULL的，可以考虑使用0，特殊值或字符串来代替它。
需要注意：虽然把NULL列改为NOT NULL 带来的性能提升很小，所以除非确定它引入了问题，否则就不要把它当成优先的优化措施。但如果计划对列进行索引，就要尽量避免把它设置为可为空（NULL）
二、字段类型的选择
1.整数
数字有两种类型：整数和实数。
（1）INT数据类型可以用来保存那些不包含小数点的数字。INT代表整数。
默认长度：有些整数类型以及他们最多所能拥有的数字位我们必须有所了解：
·TINYINT——这个类型最多可容纳三位数。
·SMALLINT——最多可容纳五位数。
·MEDIUMINT——最多可容纳八位数。
·INT——可以容纳十位数。
·BIGINT——最多可容纳二十位数。
存储空间：
如果存储整数，就可以使用这几种整数类型：
tinyint, smallint, mediumint, int, bigint
,它们分别需要
8、16、24、32、64位
存储空间。
（2）整数类型有可选的unsigned（无符号）属性，它表示不允许为负数，并大致把正上限提高了一倍，例如：tinyint unsigned保存的翻围为0到255，而不是-127到128。
Signed(有符号)和unsigned(无符号)类型占用的存储空间是一样的，性能也一样。因此可以根据实际情况采用合适的类型。
你的选择将会决定MySQL把数据保存在内存中还是磁盘上，然而，整数运算通常使用64位的bingint整数。
MySQL还允许你对整数类型定义宽度，比如int(11)。这对于大在多数应用程序是没有意义的，它不限制值的范围，只规定了mysql的交互工具(例如命令客户端)用来显示字符的个数。对于存储计算，int(1)和int(20)是一样的。
2.实数
实数有分数部分，然而，它们并不仅仅是分数。可以使用decimal保存比出bigint还大的整数。MySQL同时支持精确与非精确类型。
（1）Float和double类型支持使用标准的浮点运算进行近似计算。如果想知道浮点运算到底如何进行，则要研究生平台浮点数的具体实现。
比较起decimal类型，浮点类型保存同样大小的值使用的空间通常更小，float类型占用4个字节，double占用8个字节，而且精度更大，范围更广。和整数一样，你选择的仅仅是存储类型。mysql在内部对浮点类型使用double进行计算。
（2）由于需要额外的空间和计算开销，只有在需要对小数进行精确的时候才使用decimal，比如保存金融数据。
DECIMAL最适合保存那些将被用于计算的数据。在MySQL中，我们可以指定保存一些正当的数字。还可以指定是否允许存在负值。
指定DECIMAL类型的长度会有些棘手。例如，如果你需要在小数点前面保存五位数，且小数点后只保留三位，那么在数据库中其适当的长度将是：Decimal(5+3,3)或 Decimal(8,3)，可以使用的数据包括：12345.678，56872.690，11.6和12.568等。而这些数字则会引发出错信息：128781.1，8972865.231。
3.字符串类型
Varchar和char类型
varchar：保存了可变长度的字符串，是使用得最多的字符串类型，它能比固定类型占用更少的存储空间，因为它只占用了自已需要的空间（也就是说较短的值占用的空间更小）。它使用额外的1-2个字节来存储值的长度。Varchar能节约空间，所以对性能有帮助。然而，由于行的长度是可变的，它们在更新的时候可能会发生变化，这会引起额外的工作。当最大长度远大于平均长度，并且很少发生更新的时候，通常适合用varchar。这时候碎片就不会成为问题，还有你使用复杂的字符集，如utf-8时，它的每个字符都可能会占用不同的存储空间。Varchar存取值时候，MySQL不会去掉字符串末尾的空格。
char：固定长度，char存取值时候，MySQL会去掉末尾的空格。Char在存储很短的字符串或长度近似相同的字符的时候很有用。例如,char适用于存储密码的MD5哈希值，它的长度总是一样的。对于经常改变的值，char也好于varchar，因为固定长度的行不容易产生碎片，对于很短的列，char的效率也高于varchar。Char(1)字符串对于单字节字符集只会占用1个字节，而varchar(1)则会占用2个字节，因为有一个字节用来存储其长度。
Char和varchar的兄弟类型为binary和varbinary，它们用于保存二进制的字符串，二进制字符串的传统的字符串很类似，但是它们保存的是字节而不是字符。填充也有所不同，MySQL使用\0（0字节）填充binary值，而不是空格，并且不会在获取数据的时候把填充的值截掉。
使用varchar(5)和varchar(200)保存“hello”占用的空间是一样的，但是使用较短的列有很大的优势，较大的列会使用更多的内存，因为MySQL通常会分配固定大小的内存块来保存值。这对排序或使用基于内存的临时表尤其不好。同样的事情也会发生在使用文件排序或基于磁盘的临时表的时候。
4.BLOB和TEXT类型
BLOB和TEXT分别用二进制和字符形式保存大量数据。
事实在，它们各有自的数据类型家族：字符类型有tinytext, smalltext, text, mediumtext和longtext, 二进制类型有tinyblob, smallblob, blob, medicmblob, longblob，BLOB 等同于smallblob, TEXT等同于smalltext
和其它类型不同，MySQL把blob, text当成有实体的对象来处理，存储引擎通常会特别地保存它们。InnoDB在它们较大的时候会使用单独的“外部”存储来进行保存，每个值在行里面都需要1-4字节，并且还需要足够的外部存储空间来保存实际的值。
BLOB和TEXT唯一的区别：
就是BLOB保存的是二进制数据，没有字符集和排序规则，TEXT保存的是字符数据，有字符集和排序规则。
MySQL对BLOB、TEXT列的排序方式和其它类型不同，它不会按照字符串的长度进行排序，而只是按照max_sort_length规定的前若干个字节进行排序，如果只按照开始的几个字符排序，就可以减少max_sort_length的值或使用ORDER BY SUBSTRING(column, length)。MySQL不能索引这些数据类型的完整长度，也不能为排序而使用索引。
5.日期和时间类型
MySQL可以使用多种类型来保存各种日期和时间值，比中year和date，MySQL能存储的最细的时间粒度是秒，然而，它可以用毫秒的粒度进行暂时的运算。
DATETIME 和 TIMESTAMP的区别：
MySQL提供两种相似的数据类型：DATETIME 和 TIMESTAMP，对于很多应用程序，它们都能正常工作，但是在某些情况下，一种会好于另外一种。
DATETIME：能够保存大范围的值，从1001年到9999年，精度为秒，它把日期和时间封装到一个格式为yyyy:MM:dd:HH:mm:ss的整数当中，与时区无关。它使用了8个字节存储空间。
TIMESTAMP：保持了自1970年1月1日午夜（格林尼治标准时间）以来的秒数，它和Unix的时间戳相同。它只使用了4个字节存储空间。因此它比DATETIME的范围小得多。它表示自能从1970年到2038年。MySQL提供了FROM_UNIXTIME()函数把Unix时间戳转换为日期，并提供UNIX_TIMESTAMP()函数把日期转换为Unix时间戳。
但是，TIMESTAMP显示的值依赖于时区，MySQL服务器、操作系统及客户端连接都有时区设置。因此，保存0值的TIMESTAMP实际显示的时间是美国东部的时间1969-12-31 19:00:00，与格林尼治标准时间（GMT）相差5小时。
TIMESTAMP也有DATETIME没有的特殊性质，在默认情况下，如果插入的行没有定义TIMESTAMP列的值，MySQL就会把它设置为当前时间。在更新的时候，如果没有显示地定义TIMESTAMP列的值，MySQL也会自动更新它。可以配置TIMESTAMP列的插入和更新行为。最后，TIMESTAMP默认是NOT NULL，这也和其它的数据类型不一样！
6.使用ENUM代替固定字符串类型
ENUM列可以存储65535个不同的字符串，MySQL以非常紧凑的方式保存了它们，根据列表中值的数量，MySQL会把它们压缩到1-2个字节中，MySQL在内部会把每个值都保存为整数，以表示值在列表中的位置，并且还保留了一份“查找表”来表示整数和字符串在表的.frm文件中的映射关系。
Enum最不好的一面是字符串是固定的，如果需要添加或者删除字符串必须使用ALTER TABLE，因此，对于一系列未知可能会改变的字符串，使用enum就不是一个好主意，MySQL在内部的权限表中使用enum来保存Y值和N值。
由于MySQL把每个值保存为整数，并且须进行查找才能把它转换成字符串形式，所以enum有一些开销。这通常可以由它们较小的大小进行弥补，但不总是这样，在特定情况下，把char或varchar列和enum列进行联接，可能会比联接另一个chara或varchar列慢。
7.选择标识符
为标识列选择好的数据类型非常重要，你可能会更多地用它们和其他列做比较，还可能把它们用作其它表的外键，因为选择标识符列选择数据类型的时候，你也可能是在为相关的表选择数据类型。
当为标识符列选择数据类型的时候，不仅要考虑存储类型，还要考虑MySQL如何对它们进行计算和比较。例如：mysql会在内部把enum和set类型保存为整数，但是在比较的时候把它们转换为字符串。
一旦选择了数据类型，要确保在相关表中使用同样的类型。类型之前要精确匹配，包括诸如unsigned这样的属性。混合不同的数据类型会导致性能问题，即使没有性能问题，隐式的类型转换也能导致难以察觉的错误，在你已经忘记了自己是在对不同类型做比较的时候，这些错误就会突然出现。
选择最小的数据类型能表明所需值的范围，并且为将来留出增长的空间。例如，如果用porvince_id来表示中国的省份，那么我们知道它不会产成千上万个值，因类就没有必要使用int，用tinyint就足够了，它比int小3个节字，如果把一个表的主键是tinyint，而另一个表以int作为外键，那么就会造成较大的性能差距。
整数通常是标识符的最佳选择，因为它速度快，并且能使用auto_increment。
Enum和set通常不合适用作标识符，尽管它适合用来做静态的，包含了状态和“类型”和值的“定义表”。
Enum和set列适合用来性别、国家、省份这些固定不变的信息。
要尽可能的避免使用字符串来做标识符，因为它们占用了很多空间并且通常比整数类型要慢，特别注意不要在myisam表上使用字符串标识符。myisam默认情况下为字符串使用了压缩索引，这使查找更为缓慢。
MyISAM使用前缀压缩来减小索引大小，默认情况下会压缩字符串，也可以压缩整数
可以使用create table时用PACK_KEYS控制索引压缩的方式。
PACK_KEYS在MySQL手册中如下描述：
如果您希望索引更小，则把此选项设置为1。这样做通常使更新速度变慢，同时阅读速度加快。把选项设置为0可以取消所有的关键字压缩。把此选项设置为DEFAULT时，存储引擎只压缩长的CHAR或VARCHAR列（仅限于MyISAM）。
如果您不使用PACK_KEYS，则默认操作是只压缩字符串，但不压缩数字。如果您使用PACK_KEYS=1，则对数字也进行压缩。
8.特殊类型的数据
一些数据类型没有直接对应的内建数据类型，精度低于秒的时间戳就是一个例子，另一个例子就是IP地址，人们通常使用varchar(15)来保存IP地址。但是，IP地址实际上是无符号的32位整数，而不是字符串。使用小数点来进行分纯粹是为了增加它的可读性。在实际使用时应用用无符号整数来存储IP地址。MySQL提供了INET_ATON()和INET_NTOA()函数在IP地址和整数之前转换。
MYSQL数据库命名与其设计规范
你是否对获得MYSQL数据库命名与其设计规范 的实际操作感到十分头疼？如果是这样子的话，以下的文章将会给你相应的解决方案，以下的文章主要是介绍获得MYSQL数据库命名与其设计规范 的方案，以下就是相关内容的具体描述。
1) 标准化和规范化
数据的标准化有助于消除数据库中的数据冗余。标准化有好几种形式，但Third Normal Form（3NF）通常被认为在性能、扩展性和数据完整性方面达到了最好平衡。简单来说，遵守3NF 标准的数据库的表设计原则是：
“One Fact in One Place”即某个表只包括其本身基本的属性，当不是它们本身所具有的属性时需进行分解。表之间的关系通过外键相连接。它具有以下特点：有一组表专门存放通过键连接起来的关联数据。
举例：某个存放客户及其有关定单的3NF 数据库就可能有两个表：Customer和Order。Order表不包含定单关联客户的任何信息，但表内会存放一个键值，该键指向Customer表里包含该客户信息的那一行。
事实上，为了效率的缘故，对表不进行标准化有时也是必要的。
2) 数据驱动
采用数据驱动而非硬编码的方式，许多策略变更和维护都会方便得多，大大增强系统的灵活性和扩展性。
举例，假如用户界面要访问外部数据源（文件、XML 文档、其他数据库等），不妨把相应的连接和路径信息存储在用户界面支持表里。还有，如果用户界面执行工作流之类的任务（发送邮件、打印信笺、修改记录状态等），那么产生工作流的数据也可以存放在数据库里。角色权限管理也可以通过数据驱动来完成。事实上，如果过程是数据驱动的，你就可以把相当大的责任推给用户，由用户来维护自己的工作流过程。
3) 考虑各种变化
在设计数据库的时候考虑到哪些数据字段将来可能会发生变更。
举例，姓氏就是如此（注意是西方人的姓氏，比如女性结婚后从夫姓等）。所以，在建立系统存储客户信息时，在单独的一个数据表里存储姓氏字段，而且还附加起始日和终止日等字段，这样就可以跟踪这一数据条目的变化。
2.数据库涉及字符规范
采用26个英文字母(区分大小写)和0-9这十个自然数,加上下划线'_'组成,共63个字符.不能出现其他字符(注释除外).
注意事项:
1) 以上MYSQL数据库命名都不得超过30个字符的系统限制.变量名的长度限制为29(不包括标识字符@).
2) 数据对象、变量的命名都采用英文字符,禁止使用中文命名.绝对不要在对象名的字符之间留空格.
3) 小心保留词,要保证你的字段名没有和保留词、数据库系统或者常用访问方法冲突
5) 保持字段名和类型的一致性,在命名字段并为其指定数据类型的时候一定要保证一致性.假如数据类型在一个表里是整数,那在另一个表里可就别变成字符型了.
3.数据库MYSQL数据库命名规范
数据库,数据表一律使用前缀
正式数据库名使用小写英文以及下划线组成,尽量说明是那个应用或者系统在使用的.比如:
- web_19floor_net
- web_car
备份数据库名使用正式库名加上备份时间组成,如:
- web_19floor_net_20070403
- web_car_20070403
4.数据库表MYSQL数据库命名规范
数据表名使用小写英文以及下划线组成,尽量说明是那个应用或者系统在使用的.
相关应用的数据表使用同一前缀,如论坛的表使用cdb_前缀,博客的数据表使用supe_前缀,前缀名称一般不超过5字
比如:
- web_user
- web_group
- supe_userspace
备份数据表名使用正式表名加上备份时间组成,如:
- web_user_20070403
- web_group_20070403
- supe_userspace_20070403
5.字段MYSQL数据库命名规范
字段名称使用单词组合完成,首字母小写,后面单词的首字母大写,最好是带表名前缀.
如 web_user 表的字段:
- userId
- userName
- userPassword
表与表之间的相关联字段要用统一名称,
如 web_user 表里面的 userId 和 web_group 表里面的 userId 相对应
6.字段类型规范
规则:用尽量少的存储空间来存数一个字段的数据.
比如能用int的就不用char或者varchar
能用tinyint的就不用int
能用varchar(20)的就不用varchar(255)
时间戳字段尽量用int型，如created:表示从'1970-01-01 08:00:00'开始的int秒数，采用英文单词的过去式；gmtCreated:表示datetime类型的时间，即形如'1980-01-01 00:00:00'的时间串，Java中对应的类型为Timestamp
7.数据库设计文档规范
所有数据库设计要写成文档,文档以模块化形式表达.大致格式如下：
'-------------------------------------------
' 表名： web_user
' 作者： Aeolus(傻鱼)
' 日期： 2007-04-11
' 版本： 1.0
' 描述： 保存用户资料
' 具体内容：
' UserID int,自动增量 用户代码
' UserName char(12) 用户名字
' ......
'--------------------------------------------
8.索引使用原则：
1) 逻辑主键使用唯一的成组索引,对系统键(作为存储过程)采用唯一的非成组索引,对任何外键列采用非成组索引.考虑数据库的空间有多大,表如何进行访问,还有这些访问是否主要用作读写.
2) 大多数数据库都索引自动创建的主键字段,但是可别忘了索引外键,它们也是经常使用的键,比如运行查询显示主表和所有关联表的某条记录就用得上.
3) 不要索引blob/text等字段,不要索引大型字段(有很多字符),这样作会让索引占用太多的存储空间.
4) 不要索引常用的小型表
不要为小型数据表设置任何键,假如它们经常有插入和删除操作就更别这样作了.对这些插入和删除操作的索引维护可能比扫描表空间消耗更多的时间.
9.sql语句规范
所有sql关键词全部大写,比如SELECT,UPDATE,FROM,ORDER,BY等,所有的表名和库名都要用``包含
如:
SELECT COUNT(*) FROM `cdb_members` WHERE `userName` = 'aeolus';
10.其他设计技巧
1) 避免使用触发器
触发器的功能通常可以用其他方式实现.在调试程序时触发器可能成为干扰.假如你确实需要采用触发器,你最好集中对它文档化.
2) 使用常用英语(或者其他任何语言)而不要使用编码或者拼音首字母缩写
在创建下拉菜单、列表、报表时最好按照英语名排序.假如需要编码或者拼音首字母缩写,可以在旁边附上用户知道的英语.
3) 保存常用信息
让一个表专门存放一般数据库信息非常有用.在这个表里存放数据库当前版本、最近检查/修复(对Access)、关联设计文档的名称、客户等信息.这样可以实现一种简单机制跟踪数据库,当客户抱怨他们的数据库没有达到希望的要求而与你联系时,这样做对非客户机/服务器环境特别有用.
4) 包含版本机制
在数据库中引入版本控制机制来确定使用中的数据库的版本.时间一长,用户的需求总是会改变的.最终可能会要求修改数据库结构.把版本信息直接存放到数据库中更为方便.
5) 编制文档
对所有的快捷方式、MYSQL数据库命名规范、限制和函数都要编制文档.
采用给表、列、触发器等加注释的数据库工具.对开发、支持和跟踪修改非常有用.
对数据库文档化,或者在数据库自身的内部或者单独建立文档.这样,当过了一年多时间后再回过头来做第2 个版本,犯错的机会将大大减少.
6) 测试、测试、反复测试
建立或者修订数据库之后,必须用用户新输入的数据测试数据字段.最重要的是,让用户进行测试并且同用户一道保证选择的数据类型满足商业要求.测试需要在把新数据库投入实际服务之前完成.
7) 检查设计
在开发期间检查数据库设计的常用技术是通过其所支持的应用程序原型检查数据库.换句话说,针对每一种最终表达数据的原型应用,保证你检查了数据模型并且查看如何取出数据.
