数据库设计规范

 

数据库对象设计规范

一、存储引擎的选择

1、如无特殊需求，必须使用innodb存储引擎。

可以通过 > show variables like 'default_storage_engine'; 来查看当前默认引擎。主要有MyISAM 和 InnoDB引擎。

基本的差别为：MyISAM类型不支持事务处理等高级处理，而InnoDB类型支持。MyISAM类型的表强调的是性能，其执行速度比InnoDB类型更快，但是不提供事务支持，而InnoDB提供事务支持以及外部键等高级数据库功能。

二、字符集的选择

1、如无特殊要求，必须使用utf8或utf8mb4。

在国内，选择对中文和各语言支持都非常完善的utf8格式是最好的方式，MySQL在5.5之后增加utf8mb4编码，mb4就是most bytes 4的意思，专门用来兼容四字节的unicode。所以utf8mb4是utf8的超集，除了将编码改为utf8mb4外不需要做其他转换。当然，为了节省空间，一般情况下使用utf8也就够了。可以使用如下脚本来查看数据库的编码格式

> SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';

> SHOW VARIABLES Like '%char%';

三、表设计规范

1、不同应用间所对应的数据库表之间的关联应尽可能少，不允许使用外键对表之间进行关联，确保组件对应表之间的独立性，为系统或表结构的重构提供可能性。目前业内的做法一般由程序控制参照完整性。

2、表设计的角度不应该针对整个系统进行数据库设计，而应该根据系统架构中组件划分，针对每个组件所处理的业务进行数据库设计。

3、表必须要有PK，主键的优势是唯一标识、有效引用、高效检索，所以一般情况下尽量有主键字段。

4、一个字段只表示一个含义。

5、表不应该有重复列，使用comment从句添加表和列的备注 从一开始就进行数据字典的维护。

6、禁止使用复杂数据类型(数组,自定义等)，Json类型的使用视情况而定。

7、需要join的字段(连接键)，数据类型必须保持绝对一致，避免隐式转换。比如关联的字段都是int类型。

8、设计应至少满足第三范式,尽量减少数据冗余。一些特殊场景允许反范式化设计，但在项目评审时需要对冗余字段的设计给出解释。

9、TEXT字段作为大体量文本存储，必须放在独立的表中 , 用PK与主表关联。如无特殊需要，尽量不使用TEXT、BLOB字段。

10、需要定期删除(或者转移)过期数据的表，通过分表解决，常规的做法是按照2/8法则将操作频率较低的历史数据迁移到历史表中，按照时间或者则自增Id做切割点。

11、单表字段数不要太多，建议最多不要大于50个。过度的宽表对性能也是很大的影响。

12、MySQL在处理大表时，性能就开始明显降低，所以建议单表物理大小限制在16GB，表中数据行数控制在2000W内。业内的规则是超过2000W性能开始明显降低。但是这个值是灵活的，你可以根据实际情况进行测试来判断，比如阿里的标准就是500W，百度的是2000W。实际上是否宽表，单行数据所占用的空间都有起到作用的。

13、如果数据量或数据增长在前期规划时就较大，那么在设计评审时就应加入分表策略。分析数据拆分的做法：垂直拆分（垂直分库和垂直分表）、水平拆分（分库分表和库内分表）。

14、无特殊需求，尽量不要使用分区表。

15、减小表宽度、尽量做到冷热数据分。MySQL限制单表最多存储4096列，每行数据大小不超过65535 BYTE = 64K，控制宽度，对列进行垂直拆分，减少IO，保留热数据的内存缓存命中率，使用字段进行查询，避免读入无用的冷数据。

16、禁止在表中建立预留字段。预留字段难以做到见名知意，预留字段难以确认存储类型，修改类型，会引起全表锁定，修改表字段代价 >> 增加表字段。

17、禁止在数据库存储图片、文件等bin数据。禁止在线上库做压力测试。禁止从开发环境、测试环境直连生产环境数据库。

四、字段设计规范

1、INT：如无特殊需要，存放整型数字使用UNSIGNED INT型，整型字段后的数字代表显示长度。比如 id int(11) NOT NULL。

2、DATETIME：所有需要精确到时间(时分秒)的字段均使用DATETIME,不要使用TIMESTAMP类型。

对于TIMESTAMP，它把写入的时间从当前时区转化为UTC（世界标准时间）进行存储。查询时，将其又转化为客户端当前时区进行返回。而对于DATETIME，不做任何改变，基本上是原样输入和输出。

另外DATETIME存储的范围也比较大：

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'。

但是特殊情况，对于跨时区的业务，TIMESTAMP更为合适。

禁止使用字符串存储日期型数据，无法用日期函数进行计算和比较，使用字符串存储占用更多的空间，TIMESTAMP（4BYTE），DATETIME（8BYTE）。

3、VARCHAR：所有动态长度字符串全部使用VARCHAR类型,类似于状态等有限类别的字段,也使用可以比较明显表示出实际意义的字符串,而不应该使用INT之类的数字来代替；VARCHAR(N)，N表示的是字符数而不是字节数。比如VARCHAR(255)，可以最大可存储255个字符（字符包括英文字母，汉字，特殊字符等）。但N应尽可能小，因为MySQL一个表中所有的VARCHAR字段最大长度是65535个字节，且存储字符个数由所选字符集决定。如UTF8存储一个字符最大要3个字节，那么varchar在存放占用3个字节长度的字符时不应超过21845个字符。同时，在进行排序和创建临时表一类的内存操作时，会使用N的长度申请内存。(如无特殊需要，原则上单个varchar型字段不允许超过255个字符)

将字符串转化为数字类型存储（INET_AION、IP->Integer；INET_NTOA、Integer->IP）对于非负整数，优先使用无符号整型存储（id），VARCHAR(N)代表的是字符数，不是BYTE数，VARCHAR(255)，可以保存255中文字符，使用UTF8，实际占用765BYTE，过大长度消耗更多的内存。

4、TEXT：仅仅当字符数量可能超过20000个的时候,才可以使用TEXT类型来存放字符类数据,因为所有MySQL数据库都会使用UTF8字符集。所有使用TEXT类型的字段必须和原表进行分拆，与原表主键单独组成另外一个表进行存放，与大文本字段的隔离的目的。如无特殊需要，不使用MEDIUMTEXT、TEXT、LONGTEXT类型。TinyText、Text（64K）、MidumText、LongText进行排序等查询，无法使用内存临时表，而必须使用磁盘临时表。读取数据时，需要二次查询。非用不可的情况，可以把BLOB和TEXT拆分到单独的拓展表中。

5、金额相关需要使用精确浮点型数据存储，使用DECIMAL，严禁使用FLOAT和DOUBLE。float、double不精确，decimal精确，decimal占用空间由宽度决定，可以存储比bigint更大的整数数据。

6、如无特殊需要，尽量不使用BLOB类型

7、如无特殊需要，字段建议使用NOT NULL属性，可用默认值代替NULL。索引NULL列，需要额外的空间，要占用更多的空间，进行比较和计算时，需要对NULL值做特别的处理

8、自增字段类型必须是整型且必须为UNSIGNED，推荐类型为INT或BIGINT，并且自增字段必须是主键或者主键的一部分。

五、索引设计规范

1、索引区分度

索引必须创建在索引选择性（区分度）较高的列上，选择性的计算方式为: select count(distinct c_name)/count() from tablename; 如果区分度结果小于0.2，则不建议在此列上创建索引，大概率会拖慢SQL执行。

2、遵循最左前缀

对于确定需要组成组合索引的多个字段，设计时建议将选择性高的字段靠前放。使用时，组合索引的首字段，必须在where条件中，且需要按照最左前缀规则去匹配。

3、禁止使用外键，可以在程序级别来约束完整性。外键用于保证数据的参照完整性，建议置于业务端实现。外键影响父表和子表的写操作，从而降低性能。不建议使用外键约束，但一定在表与表之间的关联键上建立索引。

4、Text类型字段如果需要创建索引，必须使用前缀索引。

5、单张表的索引数量理论上应控制在5个以内。经常有大批量插入、更新操作表，应尽量少建索引，索引建立的原则理论上是多读少写的场景。提高查询效率，降低插入和更新效率。MySQL优化器，会评估索引，生成最优执行计划，索引过多，导致生成执行计划时间过长，降低效率。

6、ORDER BY，GROUP BY，DISTINCT的字段需要添加在索引的后面，形成覆盖索引。

7、正确理解和计算索引字段的区分度，区分度高的索引，可以快速得定位数据，区分度太低，无法有效的利用索引，可能需要扫描大量数据页，和不使用索引没什么差别。

8、正确理解和计算前缀索引的字段长度，合适的长度要保证高的区分度和最恰当的索引存储容量，只有达到最佳状态，才是保证高效率的索引。

9、联合索引注意最左匹配原则：必须按照从左到右的顺序匹配，MySQL会一直向右匹配索引直到遇到范围查询(>、<、between、like)然后停止匹配。

如：depno=1 and empname>'' and job=1 如果建立(depno,empname,job)顺序的索引，job是用不到索引的。

10、应需而取策略，查询记录的时候，不要一上来就使用，只取需要的数据，可能的话尽量只利用索引覆盖，可以减少回表操作，提升效率。

11、正确判断是否使用联合索引（上面联合索引的使用那一小节有说明判断规则），也可以进一步分析到索引下推（IPC），减少回表操作，提升效率。

12、避免索引失效的原则：禁止对索引字段使用函数、运算符操作，会使索引失效。这是实际上就是需要保证索引所对应字段的“干净度”。

13、避免非必要的类型转换，字符串字段使用数值进行比较的时候会导致索引无效。

14、模糊查询'%value%'会使索引无效，变为全表扫描，因为无法判断扫描的区间，但是'value%'是可以有效利用索引。

15、索引覆盖排序字段，这样可以减少排序步骤，提升查询效率。

16、尽量的扩展索引，非必要不新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

举例子： 比如一个品牌表，建立的的索引如下，一个主键索引，一个唯一索引

PRIMARY KEY (id),

UNIQUE KEY uni_brand_define (app_id,define_id)

业务代码中的检索语句如下的时候，应该立即警告了，即没有覆盖索引，也没按照最左前缀原则：

> select brand_id,brand_name from ds_brand_system where status=? and define_id=? and app_id=?

建议改成如下：

> select brand_id,brand_name from ds_brand_system where app_id=? and define_id=? and status=?

17、InnoDB表，必须有一个主键 - B树

InnoDB使用主键按照顺序组织表结构，如果没有主键，会按照表顺序，选择第一个非空唯一索引组织表结构，如果没有符合以上规则的，MySQL会自动生成6BYTE主键（性能不佳）。不适用频繁更新的列作为主键，不使用联合索引作为主键，不使用UUID、MD5、HASH、字符串作为主键（无法保证顺序增长）。建议选择/使用自增ID列，索引于B树。

六、约束设计规范

1、PK应该是有序并且无意义的，由开发人员自定义，尽可能简短，并且是自增序列。

2、表中除PK以外,还存在唯一性约束的,可以在数据库中创建以“uk_”作为前缀的唯一约束索引。

3、PK字段不允许更新。

4、禁止创建外键约束，外键约束由程序控制。

5、如无特殊需要，所有字段必须添加非空约束，即not null。

6、如无特殊需要，所有字段必须有默认值。