当前位置: 凤凰彩票官方app > MySQL数据库 > 正文

DB开拓标准

时间:2019-11-14 16:13来源:MySQL数据库
将数据库中存储时间的数据类型改为varchar(),这时最好让这些时间是数据库中自动生成的(一个没有格式的输入也可能会导致输出错误),因为存储类型为varchar(),所以获取到的值也就

将数据库中存储时间的数据类型改为varchar(),这时最好让这些时间是数据库中自动生成的(一个没有格式的输入也可能会导致输出错误),因为存储类型为varchar(),所以获取到的值也就被认为是一个字符串,直接将数据库中的时间字符串进行转化(这时那些转化函数是能识别数据库中的时间函数的),客户端的时间格式不再影响转换过程。

1 命名规范

不过数据库中存储时间的类型如果为字符型也会带来一些麻烦:

1.1表命名

  • 实体表使用尽量准确的英文单数表示,若一个英文单词不足以表示表的意义,可用下横杠分割,小写命名。(目前都是以t_开头,那就继续吧)
  • 按日期时间分表须符合_YYYY[MM][DD]格式
    这也是为将来有可能分表做准备的,比如t_table_name_201403。不具有时间特性的,直接以 t_tbname_001 这样的方式命名。

数据库中的时间仅仅是用来显示、查找的,那么影响还不算大,但如果对时间字段进行一些算法如计算星期、DateDiff、DateAdd等,那就麻烦了,尤其实在大型数据查询中转换类型是会影响效率的

1.2字段

字段命名含义明确,小写字母,下横杠分割, 字段名字中不要含有表名(表已经代表了这个实体的含义)。

总结

数据库中存储日期的字段类型到底应该用varchar还是datetime ?这两种方法各有优势,datetime可以借用sql函数库中运算函数,增加了时间在各种运算上的效率;而varchar类型则可以在字符编码上显出优势。在 存储的时间将来不需要进行大量计算 的前提下,可以考虑选择varchar类型,反之,选择datetime类型。

1.3索引

非唯一索引命名为idx_列名1_列名2
唯一索引命名为uk_列名1_列名2
如果列名太长或列太多,可酌情精简

表名、字段名,索引名支持最多64个字符,但为了统一规范、易于辨识以及减少传输量,禁止超过32个字符。

2 库表基础规范

2.1存储引擎

统一使用Innodb存储引擎
5.5版本开始mysql默认存储引擎就是InnoDB,5.7版本开始,系统表都放弃MyISAM了

2.2字符集

字符集统一使用UTF8。如果遇到EMOJ等表情符号的存储需求,可使用UTF8MB4字符集

2.3注释

所有表和字段都尽量需要添加注释

2.4主键

所有表都必须要显式指定主键
主键尽量采用自增方式,InnoDB表实际是一棵索引组织表,顺序存储可以提高存取效率,充分利用磁盘空间。并且有利于bufferpool中储存更多的二级索引记录(二级索引里包含了主键值)

3 字段规范

3.1字符串类型

类型 范围(字节) char(N) 0-255 varchar(N) 0-65535 text 0-65535
注:N为字符数而不是字节数,根据编码不同占用不同的字节,UTF8编码下,一个中文字符占用3个字节,GBK编码下一个中文字符占用2个字节

  • varchar类型保存可变长度字符串,范围0-65535(但受到单行最大64kb的限制),尽量根据业务需求定义合适的字段长度,不要为了图省事,直接定义为varchar(1024)或更长等等。
    eg.用varchar(30)去存放abcd,实际使用4+1=5个字节,因为还需要使用额外1个字节来标识字符串长度(0-255使用1个字节,超过255需要2个字节)。

  • char类型是使用固定长度空间进行存储,不容易产生碎片,范围0-255。
    eg.CHAR(30)能放30个字符,存放abcd时,尾部会以空格补齐,实际占用空间 30个字节 。检索它的时候尾部空格会被去除。

  • 对于text字段,MySQL不允许有默认值。varchar允许有默认值

  • varchar(768+)和text在存储形态是一样的(都是需要额外的2个字节来标识字符串长度,且都会进行overflow存储)

特别注意,varchar大字段一样的会降低性能,所以在设计中还是一个原则大字段要拆出去,主表还是要尽量的瘦

3.2 整数类型

类型 字节 最小值 最大值 (带符号的/无符号的) (带符号的/无符号的) TINYINT 1 -128 127 0 255 SMALLINT 2 -32768 32767 0 65535 MEDIUMINT 3 -8388608 8388607 0 16777215 INT 4 -2147483648 2147483647 0 4294967295 BIGINT 8 -9.22337E+18 9.22337E+18 0 1.84467E+19

  • 所有整数类型的字段尽量使用合适的大小。可以为整数类型指定宽度(比如int(11),这种括号里的值),但大多数时候没有意义,它不会限制值的合法范围
  • 可以使用tinyint存储状态值。比enum减少cpu开销,且容易维护。
  • 建议使用 UNSIGNED 存储非负数值
    相比不使用 unsigned,可以扩大一倍使用数值范围

3.3 实数类型

  • 可以使用decimal存储比bigint还要大的整数
  • float和double类型是不精确的类型,支持使用标准的浮点运算进行近似计算,float占4个字节,double占8个字节
  • decimal类型用于存储精确的小数,占用字节数按照小数和整数分别计算,每9位数占4字节,小数点占一个字节。有效数字65,小数部分最多30。
  • 使用decimal 需要额外的空间和计算开销,所以应该尽量只是在对小数进行精确计算的时候才使用,例如存储财务数据。如果数据量大,也可以考虑使用bigint来存储,这样能避免使用浮点存储不精确和decimal精确存储代价高的问题。

3.4 时间类型

时间类型统一用datetime,如涉及时区,则用timestamp

每个表中都必须包含2个字段:create_time和edit_time
create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
edit_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间'

Datetime 和 Timestamp 几乎以同样的方式工作。两种都保存日期和时间信息,毫秒部分最高精确度都是 6 位数。在表示日期/时间部分时 Timestamp (4 字节) 比 Datetime (5 字节) 少使用 1 字节。当保存毫秒部分时两种都使用额外的空间 (1-3 字节)。Timestamp 存储范围从 ‘1970-01-01 00:00:01.000000’ 到 ‘2038-01-19 03:14:07.999999’ 。 Datetime 存储范围从 ‘1000-01-01 00:00:00.000000’ 到 ‘9999-12-31 23:59:59.999999’ 。

3.5 NOT NULL和默认值

除了必须为NULL,建议字段都定义为NOT NULL。因为null值会影响cordinate统计,影响优化器对索引的选择,而且需要额外的存储空间。

3.6 同一意义的字段定义必须相同

同一意义的字段定义包括字段类型和长度范围必须相同
比如t_item表中的id和t_item_batch表中的item_id,需要定义成一样的类型。如果类型不一样,关联的时候会发生强制类型转换,不会用到索引。如果范围不一样,那么一个表正常插入,另一个表就超出范围报错了。

4 SQL使用规范

4.1 不允许select * 查询

只取出需要的字段,减少网络带宽消耗,能有效利用覆盖索引,表结构变更对程序基本无影响

4.2 不建议使用join,left join

请将复杂查询拆分为多个简单查询,减少锁表的范围和时间

4.3 拒绝高并发的计数器

不允许在mysql中进行高并发的类计数器行为. 比如:字段+1操作

4.4 禁止where条件中使用函数

where条件中不要使用函数或进行(隐式)强制转换
凤凰彩票平台手机版,例如,
1、date(create_time)='2016-11-01'改用create_time>='2016-11-01' and create_time>'2016-11-02'
2、t_preference表的latitude_no字段是varchar类型,所以在使用的时候一定要在值上加引号latitude_no='341245',不能直接latitude_no=341245,这样会无法利用索引

4.5 不建议使用union,union all

数据量大的情况下,或者有大字段的表,不建议使用union,union all,会创建磁盘上的临时表,影响性能。

4.6 不允许在数据库端进行复杂计算

影响sql效率,cpu消耗严重

4.7 拒绝大事务

比如在一个事务里进行多个select,多个update,如果是高频事务,会严重影响MySQL并发能力,因为事务持有的锁等资源只在事务rollback/commit时才能释放。

4.8 尽量采用批量SQL

减少与数据库交互的次数,尽量采用批量SQL语句,但需要控制好个数。
例如INSERT INTO VALUES(),(),(),合并插入。

本文参考
http://seanlook.com/2016/05/11/mysql-dev-principle-ec/

编辑:MySQL数据库 本文来源:DB开拓标准

关键词: