【mysql】数据库对象设计规范
数据库对象设计规范
存储引擎的选择
1、如⽆特殊需求，必须使⽤innodb存储引擎。
可以通过  show variables like 'default_storage_engine' 来查看当前默认引擎。主要有MyISAM 和 InnoDB，从5.5版本开始默认使⽤ InnoDB 引擎。
基本的差别为：MyISAM类型不⽀持事务处理等⾼级处理，⽽InnoDB类型⽀持。MyISAM类型的表强调的是性能，其执⾏速度⽐InnoDB类型更快，但是不提供事务⽀持，⽽InnoDB提供事务⽀持以及外部键等⾼级数据库功能。
字符集的选择
1、如⽆特殊要求，必须使⽤utf8或utf8mb4。
在国内，选择对中⽂和各语⾔⽀持都⾮常完善的utf8格式是最好的⽅式，MySQL在5.5之后增加utf8mb4编码，mb4就是most bytes 4的意思，专门⽤来兼容四字节的unicode。
所以utf8mb4是utf8的超集，除了将编码改为utf8mb4外不需要做其他转换。当然，为了节省空间，⼀般情况下使⽤utf8也就够了。
可以使⽤如下脚本来查看数据库的编码格式
1 SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';
2 -- 或
3 SHOW VARIABLES Like '%char%';
表设计规范
1、不同应⽤间所对应的数据库表之间的关联应尽可能减少，不允许使⽤外键对表之间进⾏关联，确保组件对应的表之间的独⽴性，为系统或表结构的重构提供可能性。⽬前业内的做法⼀般由程序控制参照完整性。
2、表设计的⾓度不应该针对整个系统进⾏数据库设计，⽽应该根据系统架构中组件划分，针对每个组件所处理的业务进⾏数据库设计。
3、表必须要有PK，主键的优势是唯⼀标识、有效引⽤、⾼效检索，所以⼀般情况下尽量有主键字段。
4、⼀个字段只表⽰⼀个含义。
5、表不应该有重复列。
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、⽆特殊需求，严禁使⽤分区表
字段设计规范
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更为合适。
3、VARCHAR：所有动态长度字符串全部使⽤VARCHAR类型，类似于状态等有限类别的字段，也使⽤可以⽐较明显表⽰出实际意义的字符串,⽽不应该使⽤INT之类的数字来代替；VARCHAR(N)，
N表⽰的是字符数⽽不是字节数。⽐如VARCHAR(255)，可以最⼤可存储255个字符（字符包括英⽂字母，汉字，特殊字符等）。但N应尽可能⼩，因为MySQL⼀个表中所有的VARCHAR字段最⼤长度是65535个字节，且存储字符个数由所选字符集决定。
如UTF8存储⼀个字符最⼤要3个字节，那么varchar在存放占⽤3个字节长度的字符时不应超过21845个字符。同时，在进⾏排序和创建临时表⼀类的内存操作时，会使⽤N的长度申请内存。(如⽆特殊需要，原则上单个varchar型字段不允许超过255个字符)
4、TEXT：仅仅当字符数量可能超过20000个的时候，才可以使⽤TEXT类型来存放字符类数据，因为所有MySQL数据库都会使⽤UTF8字符集。
所有使⽤TEXT类型的字段必须和原表进⾏分拆，与原表主键单独组成另外⼀个表进⾏存放，与⼤⽂本字段的隔离，⽬的是。如⽆特殊需要，不使⽤MEDIUMTEXT、TEXT、LONGTEXT类型
5、对于精确浮点型数据存储，需要使⽤DECIMAL，严禁使⽤FLOAT和DOUBLE。
6、如⽆特殊需要，尽量不使⽤BLOB类型
7、如⽆特殊需要，字段建议使⽤NOT NULL属性，可⽤默认值代替NULL
8、⾃增字段类型必须是整型且必须为UNSIGNED，推荐类型为INT或BIGINT，并且⾃增字段必须是主键或者主键的⼀部分。
索引设计规范
1、索引区分度
索引必须创建在索引选择性（区分度）较⾼的列上，选择性的计算⽅式为:  selecttivity = count(distinct c_name)/count(*)  ; 如果区分度结果⼩于0.2，则不建议在此列上创建索引，否则⼤概率会拖慢SQL执⾏
2、遵循最左前缀
对于确定需要组成组合索引的多个字段，设计时建议将选择性⾼的字段靠前放。使⽤时，组合索引的⾸字段，必须在where条件中，且需要按照最左前缀规则去匹配。
3、禁⽌使⽤外键，可以在程序级别来约束完整性
4、Text类型字段如果需要创建索引，必须使⽤前缀索引
5、单张表的索引数量理论上应控制在5个以内。经常有⼤批量插⼊、更新操作表，应尽量少建索引，索引建⽴的原则理论上是多读少写的场景。
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)的索引，那么只需要修改原来的索引即可。举例⼦：⽐如⼀个品牌表，建⽴的的索引如下，⼀个主键索引，⼀个唯⼀索引
1 PRIMARY KEY (`id`),
2 UNIQUE KEY `uni_brand_define` (`app_id`,`define_id`)
varchar2最大长度当你同事业务代码中的检索语句如下的时候，应该⽴即警告了，即没有覆盖索引，也没按照最左前缀原则：
1 select brand_id,brand_name from  ds_brand_system where status=?  and define_id=?  and app_id=?
建议改成如下：
1 select brand_id,brand_name from  ds_brand_system where app_id=? and define_id=?  and  status=?
约束设计规范
1、PK应该是有序并且⽆意义的，由开发⼈员⾃定义，尽可能简短，并且是⾃增序列。
2、表中除PK以外，还存在唯⼀性约束的，可以在数据库中创建以“uk_”作为前缀的唯⼀约束索引。
3、PK字段不允许更新。
4、禁⽌创建外键约束，外键约束由程序控制。
5、如⽆特殊需要，所有字段必须添加⾮空约束，即not null。
6、如⽆特殊需要，所有字段必须有默认值。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。