1 命名规范
1.1表命名
- 实体表使用尽量准确的英文单数表示，若一个英文单词不足以表示表的意义，可用下横杠分割，小写命名。（目前都是以t_开头，那就继续吧）
- 按日期时间分表须符合_YYYY[MM][DD]格式
这也是为将来有可能分表做准备的，比如t_table_name_201403。不具有时间特性的，直接以 t_tbname_001 这样的方式命名。
1.2字段
字段命名含义明确，小写字母，下横杠分割, 字段名字中不要含有表名（表已经代表了这个实体的含义）。
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(),(),()，合并插入。
网友评论