mysql物理设计

定义数据库，及表的命名规范：

1，数据库，表及字段的命名要遵循可读性原则， 表意性原则， 长名原则（不尽量或者不使用缩写，使用长一些的命名，这样以后会看起来更明白，但是也不太非常的长）

通一 主键的名称，比如是 id或者是 _id 等，这样比较好管理。表里面的字段命名也不需要带上表名什么的。什么意思就是什么意思就好，没必要搞得这样子。不过表命名可以 可是 模块的名称 作为前缀 ，比如有一个 模块 crm ,那么 crm 模块里面 用到的表是 user ，那么表名 就起为  crm_user这样比较好识别和 管理。

个人认为尽量不要使用 uuid 的bigint ，一般的小中型项目，int无符号都可以满足了。

 

对于分布式的主键呢。

一般都是使用 varchar 的 UUID，这样比较方便管理和使用对于分布式来说。

但是对于一些对接其他系统的场景，ID可能会让你痛不欲生，而洗数据也是体力活儿。如果ID过长极容易产生前端显示问题，毕竟js的long是15位的。所以实际运用中ID长度也要严格把控。所以UUID 才保存为varchar类型，而不是 bigint 这样比较方便写代码和传给客户端，不用进行 类型转换了。

 

 

文／codertom（简书作者）
原文链接：http://www.jianshu.com/p/a3828b42fce8
著作权归作者所有，转载请联系作者获得授权，并标注“简书作者”。

 

2, 为表中的字段选择合适的数据类型： 原则：

当一个列可以选择多种数据类型时，应该优先考虑数字类型，其次是日期或二进制类型，最后的字符类型（char 类型 比如 varcahr类型好）

对于相同级别的数据类型，应该优先选择占用空间小的数字类型

 

如何选择正确的整数类型？

tinyint  一个字节（无符号是取值范围 0--255）， smallint 两个字节 ， mediumint  三个字节

int 四个字节， bigint 八个字节 

 

如何选择正确的实数类型？

float  四个字节（不是精确类型） ， double 八个字节（不是精确类型），decimal (每四个字节存9个数字，小数点占一个字节) （精确类型，可是占用的字节数也会多一些，如果是财务，钱的数据就应该使用这个类型，其他类型的数据，就可以用 float类型或者是  double类型了）。

 

如何选择 varchar 和 char 类型 ？

varchar 用于存储变长的字符串，只占用必要的存储空间（也就是内容是多少，就占用多少的空间），

列的最大长度小于255 （字符串长度），则只占用一个额外字节用于记录字符串长度。

如果列的最大长度大于255，则只占用两个额外的字节用于记录字符串长度。如果 字符串长度比较长，就应该使用 text 等其他的 类型进行存储 。

varchar 长度的选择问题： 使用最小的符合需求的长度。

如果项目上线后，多人使用的情况下，如果修改了 varchar 的长度 那么是会 进行锁表，那么会产生比较大的系统性能问题的，导致项目崩掉或者是其他数据问题，最好不要这么做。

使用 varchar的场景：

1， 字符串列的最大长度比平均长度大很多

2， 字符串列很少被更新

3，使用了 多字节字符集存储字符串

 

char 类型：

char类型 是定长的， 字符串存储在 char 类型中的列会删除末尾的空格，

char类型的 最大宽度是 255个类型 。

char类型 适用的 场景：

1，适合存储 所长度近似的值 （比如手机号，身份证等）

2， char类型适合存储短字符串（比用 varchar 更加节省空间）

3， char类型适合存储那些经常被更新的字符串 

 

如何存储时间类型:

datetime = YYYY-MM-DD HH:MM:SS 格式存储时间类型

datetime (6) = YYYY-MM-DD HH:MM:SS:微秒的  格式存储 时间类型 

datetime类型和 时区无关，内容并不会随着时区的变化而变化，占8个字节

datetime是比较通用型时间类型，一般都可以使用，它表示的时间范围也是很大的。

timestamp 时间戳，存储的是 1970年 1月1日 ，到目前的秒数 

显示的是 YYYY-MM-DD HH:MM:SS 格式 的，占用4个字节，比datetime更加节省空间，可是 timestamp只存储到 2038年的 时间范围，而且它的时间显示依赖于设置的时区，随着时区的改变而改变 。但是当行修改的时候，可以自动修改timestamp列的值 .

 

date 类型，格式送 YYYY-MM-DD  ，优点，占用的字节数 比使用的字节数，datetime , int 存储要少，

使用 date 只存储3个字节 ，存储范围也是很大的

使用date类型，还可以使用 时间日期的函数进行计算 

time类型 格式是 HH:MM:SS 格式 的（可以指定宽度来存储微秒的数据）

存储时间日期的 注意事项：

1，不要使用字符串类型来存储日期时间类型（日期时间类型比如 字符串类型用的空间小）

2，日期时间类型在进行查找过滤时可以使用日期来进行对比 

3，日期时间类型有很丰富的函数，可以方便对其进行日期的计算

4，如果要使用 int类型存储日期，那就使用 timestamp类型（如果在这个时间范围内，timestamp存储其实就是存储int的，只是显示的方式还是以前那个样子而已）

 

如何为 InnoDB选择主键：

主键应该尽可能的小（可以提高查询效率）

主键应该是顺序增长的（这样可以增加插入的效率，减少IO消耗，提高性能）

主键可以和业务主键不同，为了保证唯一性，可以加唯一的索引

 

明确指定数据列的NOT NULL属性可使MySQL在检索过程中不用去判断数据列是否是NULL

 

尽量使用 NOT NULL

NULL 类型比较特殊，SQL 难优化。虽然 MySQL NULL类型和 Oracle 的NULL 有差异，会进入索引中，但如果是一个组合索引，那么这个NULL 类型的字段会极大影响整个索引的效率。此外，NULL 在索引中的处理也是特殊的，也会占用额外的存放空间。
很多人觉得 NULL 会节省一些空间，所以尽量让NULL来达到节省IO的目的，但是大部分时候这会适得其反，虽然空间上可能确实有一定节省，倒是带来了很多其他的优化问题，不但没有将IO量省下来，反而加大了SQL的IO量。所以尽量确保 DEFAULT 值不是 NULL，也是一个很好的表结构设计优化习惯

 

LOB类型：强烈反对在数据库中存放 LOB 类型数据，虽然数据库提供了这样的功能，但这不是他所擅长的，我们更应该让合适的工具做他擅长的事情，才能将其发挥到极致

 

尽量使用TIMESTAMP类型，因为其存储空间只需要 DATETIME 类型的一半。对于只需要精确到某一天的数据类型，建议使用DATE类型，因为他的存储空间只需要3个字节，比TIMESTAMP还少。不建议通过INT类型类存储一个unix timestamp 的值，因为这太不直观，会给维护带来不必要的麻烦，同时还不会带来任何好处。

 

非万不得已不要使用 TEXT 数据类型，其处理方式决定了他的性能要低于char或者是varchar类型的处理。定长字段，建议使用 CHAR 类型，不定长字段尽量使用 VARCHAR，且仅仅设定适当的最大长度，而不是非常随意的给一个很大的最大长度限定，因为不同的长度范围，MySQL也会有不一样的存储处理。

.数字类型：非万不得已不要使用DOUBLE，不仅仅只是存储长度的问题，同时还会存在精确性的问题。同样，固定精度的小数，也不建议使用DECIMAL，建议乘以固定倍数转换成整数存储，可以大大节省存储空间，且不会带来任何附加维护成本。对于整数的存储，在数据量较大的情况下，建议区分开 TINYINT / INT / BIGINT 的选择，因为三者所占用的存储空间也有很大的差别，能确定不会使用负数的字段，建议添加unsigned定义。当然，如果数据量较小的数据库，也可以不用严格区分三个整数类型。

可以参考：  http://blog.csdn.net/aya19880214/article/details/45871733

 

BLOB和TEXT类型

         它们都是为存储大数据而设计的，BLOB是采用二进制/TEXT采用字符存储。MySQL不能将BLOB和TEXT列全部进行索引（只能做前面某长度的索引，因此在查询SQL时不能用LIKE前模糊匹配，那样就走不到索引了），也不能使用这些索引进行排序。

   技巧：使用枚举代替字符串类型，枚举可以把一些重复的字符串存储成一个预定义的集合，MySQL在存储枚举时非常紧凑，MySQL在列中保存值为枚举中的位置整数。枚举最不好的是字符串是固定的，添加或删除必须使用ALTER TABLE。因此对于未来会改变的字符串，使用枚举不是一个好主意，除非能接受在枚举末尾添加元素，由于枚举有一个映射转换过程，所以枚举虽然能减少存储空间，但是也会增加一些额外开销。

 

1. 复合类型我们一般用tinyint,更快的时间更省的空间以及更容易扩展
2. 关于手机号，推荐用char(11),char(11)在查询上更有效率，因为手机号是一个活跃字段参与逻辑会很多。
3. 一些常用字段举例
   姓名：char(20)
   价格：DECIMAL(7, 3)
   产品序列号：SMALLINT(5) unsigned
   文章内容: TEXT
   MD5: CHAR(32)
   ip: char(15)
   time: int(10)
   email char(32)

 

BLOB和TEXT类型，BLOB以二进制存储（1、0数据流，搞硬件编程的比较熟悉），TEXT以字符形式存储，都是用来存储很大数据的类型，注意，他们也是字符串类型，只是存储形式不同。

BLOB和TEXT类型，在MySQL中会被作为对象处理，尽量避免使用。

在使用text和blob字段类型时要注意以下几点，以便更好的发挥数据库的性能。

①BLOB和TEXT值也会引起自己的一些问题，特别是执行了大量的删除或更新操作的时候。删除这种值会在数据表中留下很大的"空洞"，以后填入这些"空洞"的记录可能长度不同,为了提高性能,建议定期使用 OPTIMIZE TABLE 功能对这类表进行碎片整理.

②使用合成的（synthetic）索引。合成的索引列在某些时候是有用的。一种办法是根据其它的列的内容建立一个散列值，并把这个值存储在单独的数据列中。接下来你就可以通过检索散列值找到数据行了。但是，我们要注意这种技术只能用于精确匹配的查询（散列值对于类似<或>=等范围搜索操作符 是没有用处的）。我们可以使用MD5()函数生成散列值，也可以使用SHA1()或CRC32()，或者使用自己的应用程序逻辑来计算散列值。请记住数值型散列值可以很高效率地存储。同样，如果散列算法生成的字符串带有尾部空格，就不要把它们存储在CHAR或VARCHAR列中，它们会受到尾部空格去除的影响。

合成的散列索引对于那些BLOB或TEXT数据列特别有用。用散列标识符值查找的速度比搜索BLOB列本身的速度快很多。

③在不必要的时候避免检索大型的BLOB或TEXT值。例如，SELECT *查询就不是很好的想法，除非你能够确定作为约束条件的WHERE子句只会找到所需要的数据行。否则，你可能毫无目的地在网络上传输大量的值。这也是 BLOB或TEXT标识符信息存储在合成的索引列中对我们有所帮助的例子。你可以搜索索引列，决定那些需要的数据行，然后从合格的数据行中检索BLOB或 TEXT值。

④把BLOB或TEXT列分离到单独的表中。在某些环境中，如果把这些数据列移动到第二张数据表中，可以让你把原数据表中 的数据列转换为固定长度的数据行格式，那么它就是有意义的。这会减少主表中的碎片，使你得到固定长度数据行的性能优势。它还使你在主数据表上运行 SELECT *查询的时候不会通过网络传输大量的BLOB或TEXT值。

 

 

还有就是对于 bit 类型的使用 ，如果是 bit(1) 就代表只有一位的数字，不是 0，就是 1 。

这样子对应java的类型就是 boolean布尔类型，可以用来进行布尔的判断。 true代表 1， 0代表 false 

比如可以 直接这样进行 使用   select *  from table where flage=true  ; 就可以查询出 flage(bit （1） ) =1 的所有数据了。如果是 bit(2) 就代表是2位了，不能存入 1位的，如果输入 1，那么默认就会存储是 01 两位的。

使用BIT数据类型保存位段值。BIT(M)类型允许存储M位值。M范围为1到64,BIT(1)定义一个了只包含单个比特位的字段, BIT(2)是存储2个比特位的字段,一直到64位。要指定位值,可以使用b'value'符。value是一个用0和1编写的二进制值。例如,b'111'和b'100000000'分别表示7和128。如果为BIT(M)列分配的值的长度小于M位,在值的左边用0填充。例如,为BIT(6)列分配一个值b'101',其效果与分配b'000101'相同。

MySQL把BIT当做字符串类型, 而不是数据类型。当检索BIT(1)列的值, 结果是一个字符串且内容是二进制位0或1, 而不是ASCII值”0″或”1″。

通常我们使用 bit(1) 来表示 布尔类型的，一般也是这样使用的。

 

总的来说，进行数据库表的物理的 表的字段结构进行设计的时候呢，要权衡 好，查询 的效率问题和 我们程序员使用的方便的角度。 不要太 追求极端了，要平衡好。而且个人认为，不要站在 数据库设计者的角度看问题，应该站在我们程序员的角度看问题。比如 一些 字段类型，全部都使用  varchar  类型 又如何？ 真的就对效率产生很大影响了？ 如果效率太低了，可以进行 索引，或者一些表插入和查询而已就用用   myisam  存储引擎， 一些有事物的就用    innodb   等，增加数据库服务器，服务器的配置的。目前服务器又不贵了。而且 只有项目赚到了钱了，分库，分表，读写分离，分布式什么的， 那不是小意思。所以不太在意，不要太完美，好用才是王道。提高开发效率才好，不要让开发 把相当大的部分的事情 浪费在 数据库方面去。

 

 

总结

其实在开发设置中，还是结合 实际的情况来进行，特别是 公司里面没有 DBA 的情况下，对于数据设计来说，当然是 尽量按照 可以使用 数据库 性能 最优的方式了，，但是实际开发中，我们为了开发效率，是可以 不按照 规定来的，比如 主键就使用  varchar ；

  设计字段的时候，就是 默认可以为 null ， 因为随着业务发展，有些字段是可以为 null ， 这样如果我们 设计为 非 null，我们不是插入的时候要插入 默认值？ 影响开发效率 。。。等等总之，如果 对于中小公司，和 项目情况来 ， 开发效率和后期维护成本才是王道，，，至于数据库的设计这块，没必要，也不能有完美的 设计的。 再我看来， 目前出现了那么多数据库类型， 数据库就是 为了开发和维护服务的，毕竟 我们是开发人员，不是专业的DBA ，，， 我们不想 没事老加班，，为了 追求所谓的完美，没意义

 

 

 

转载于:https://my.oschina.net/ouminzy/blog/780164