数据库开发设计规范及表结构设计原则
⽂章⽬录
⼀. 命名规范
1.库名、表名、字段名必须使⽤⼩写字母，并采⽤下划线分割
(1)MySQL有配置参数lower_case_table_names=1，即库表名以⼩写存储，⼤⼩写不敏感。如果是0，则库表名以实际情况存储，⼤⼩写敏感；如果是2，以实际情况存储，但以⼩写⽐较。
(2)如果⼤⼩写混合使⽤，可能存在abc，Abc，ABC等多个表共存，容易导致混乱。
(3)字段名显⽰区分⼤⼩写，但实际使⽤时不区分，即不可以建⽴两个名字⼀样但⼤⼩写不⼀样的字段。
(4)为了统⼀规范， 库名、表名、字段名使⽤⼩写字母。
2.库名以 d 开头，表名以 t 开头，字段名以 f_ 开头
(1)⽐如表 t_crm_relation，中间的 crm 代表业务模块名
(2)视图以view_开头，事件以event_开头，触发器以trig_开头，存储过程以proc_开头，函数以func_开头
(3)普通索引以idx_col1_col2命名，唯⼀索引以uk_col1_col2命名（可去掉f_公共部分）。如
idx_companyid_corpid_contacttime(f_company_id,f_corp_id,f_contact_time)
3.库名、表名、字段名禁⽌超过32个字符，需见名知意
库名、表名、字段名⽀持最多64个字符，但为了统⼀规范、易于辨识以及减少传输量，禁⽌超过32个字符
4.临时库、表名须以tmp加⽇期为后缀
如 t_crm_relation_tmp0425。备份表也类似，形如 _bak20160425 。mysql视图和存储过程
5.按⽇期时间分表须符合_YYYY[MM][DD]格式
这也是为将来有可能分表做准备的，⽐如t_crm_ec_record_201403，但像 t_crm_contact_at201506就打破了这种规范。
不具有时间特性的，直接以 t_tbname_001 这样的⽅式命名。
⼆. 库表基础规范
1.使⽤Innodb存储引擎
5.5版本开始mysql默认存储引擎就是InnoDB，5.7版本开始，系统表都放弃MyISAM了。
2.表字符集统⼀使⽤UTF8
(1)UTF8字符集存储汉字占⽤3个字节，存储英⽂字符占⽤⼀个字节
(2)校对字符集使⽤默认的 utf8_general_ci
(3)连接的客户端也使⽤utf8，建⽴连接时指定charset或SET NAMES UTF8;。（对于已经在项⽬中长期使⽤latin1的，救不了了）
(4)如果遇到EMOJ等表情符号的存储需求，可申请使⽤UTF8MB4字符集
3.所有表都要添加注释
(1)尽量给字段也添加注释
(2)类status型需指明主要值的含义，如”0-离线，1-在线”
4.控制单表字段数量
(1)单表字段数上限30左右，再多的话考虑垂直分表，⼀是冷热数据分离，⼆是⼤字段分离，三是常在⼀起做条件和返回列的不分离。
(2)表字段控制少⽽精，可以提⾼IO效率，内存缓存更多有效数据，从⽽提⾼响应速度和并发能⼒，后续 alter table 也更快。
5.所有表都必须要显式指定主键
(1)主键尽量采⽤⾃增⽅式，InnoDB表实际是⼀棵索引组织表，顺序存储可以提⾼存取效率，充分利⽤磁盘空间。还有对⼀些复杂查询可能需要⾃连接来优化时需要⽤到。
(2)需要全局唯⼀主键时，使⽤外部发号器ticket server（建设中）
(3)如果没有主键或唯⼀索引，update/delete是通过所有字段来定位操作的⾏，相当于每⾏就是⼀次全表扫描
(4)少数情况可以使⽤联合唯⼀主键，需与DBA协商
6.不强制使⽤外键参考
即使2个表的字段有明确的外键参考关系，也不使⽤ FOREIGN KEY ，因为新纪录会去主键表做校验，影响性能。
7.适度使⽤存储过程、视图，禁⽌使⽤触发器、事件
(1)存储过程（procedure）虽然可以简化业务端代码，在传统企业写复杂逻辑时可能会⽤到，⽽在互联⽹企业变更是很频繁的，在分库分表的情况下要升级⼀个存储过程相当⿇烦。⼜因为它是不记录log的，所以也不⽅便debug性能问题。如果使⽤过程，⼀定考虑如果执⾏失败的情况。
(2)使⽤视图⼀定程度上也是为了降低代码⾥SQL的复杂度，但有时候为了视图的通⽤性会损失性能（⽐如返回不必要的字段）。
(3)触发器（trigger）也是同样，但也不应该通过它去约束数据的强⼀致性，mysql只⽀持“基于⾏的触发”，也就是说，触发器始终是针对⼀条记录的，⽽不是针对整个sql语句的，如果变更的数据集⾮常⼤的话，效率会很低。掩盖⼀条sql背后的⼯作，⼀旦出现问题将是灾难性的，但⼜很难快速分析和定位。再者需要ddl时⽆法使⽤pt-osc⼯具。放在transaction执⾏。
(4)事件（event）也是⼀种偷懒的表现，⽬前已经遇到数次由于定时任务执⾏失败影响业务的情况，⽽且mysql⽆法对它做失败预警。建⽴专门的 job scheduler 平台。
a.单表数据量控制在5000w以内
b.数据库中不允许存储明⽂密码
三. 字段规范
1.char、varchar、text等字符串类型定义
(1)对于长度基本固定的列，如果该列恰好更新⼜特别频繁，适合char
(2)varchar虽然存储变长字符串，但不可太⼩也不可太⼤。UTF8最多能存21844个汉字，或65532个英⽂
(3)varbinary(M)保存的是⼆进制字符串，它保存的是字节⽽不是字符，所以没有字符集的概念，M长度0-255（字节）。只⽤于排序或⽐较时⼤⼩写敏感的类型，不包括密码存储
(4)TEXT类型与VARCHAR都类似，存储可变长度，最⼤限制也是2^16，但是它20bytes以后的内容是在数据页以外的空间存储
（row_format=dynamic），对它的使⽤需要多⼀次寻址，没有默认值。
⼀般⽤于存放容量平均都很⼤、操作没有其它字段那样频繁的值。
⽹上部分⽂章说要避免使⽤text和blob，要知道如果纯⽤varchar可能会导致⾏溢出，效果差不多，但因为每⾏占⽤字节数过多，会导致buffer_pool能缓存的数据⾏、页下降。另外text和blob上⾯⼀般不会去建索引，⽽是利⽤sphinx之类的第三⽅全⽂搜索引擎，如果确实要创建（前缀）索引，那就会影响性能。凡事看具体场景。
另外尽可能把text/blob拆到另⼀个表中
(5)BLOB可以看出varbinary的扩展版本，内容以⼆进制字符串存储，⽆字符集，区分⼤⼩写，有⼀种经常提但不⽤的场景：不要在数据库⾥存储图⽚。
2.int、tinyint、decimal等数字类型定义
(1)使⽤tinyint来代替 enum和boolean
ENUM类型在需要修改或增加枚举值时，需要在线DDL，成本较⾼；ENUM列值如果含有数字类型，可能会引起默认值混淆
tinyint使⽤1个字节，⼀般⽤于status,type,flag的列
(2)建议使⽤ UNSIGNED 存储⾮负数值
相⽐不使⽤ unsigned，可以扩⼤⼀倍使⽤数值范围
(3)int使⽤固定4个字节存储，int(11)与int(4)只是显⽰宽度的区别
(4)使⽤Decimal 代替float/double存储精确浮点数
对于货币、⾦额这样的类型，使⽤decimal，如 decimal(9,2)。float默认只能能精确到6位有效数字
3.timestamp与datetime选择
(1)datetime 和 timestamp类型所占的存储空间不同，前者8个字节，后者4个字节，这样造成的后果是两者能表⽰的时间范围不同。前者范围为1000-01-01 00:00:00 ~ 9999-12-31 23:59:59，后者范围为 1970-01-01 08:00:01 到 2038-01-19 11:14:07 。所以TIMESTAMP ⽀持的范围⽐ DATATIME 要⼩。
(2)timestamp可以在insert/update⾏时，⾃动更新时间字段（如 f_set_time timestamp NOT NULL DEFAULT
CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP），但⼀个表只能有⼀个这样的定义。
(3)timestamp显⽰与时区有关，内部总是以 UTC 毫秒 来存的。还受到严格模式的限制
(4)优先使⽤timestamp，datetime也没问题
(5)where条件⾥不要对时间列上使⽤时间函数
4.建议字段都定义为NOT NULL
(1)如果是索引字段，⼀定要定义为not null 。因为null值会影响cordinate统计，影响优化器对索引的选择
(2)如果不能保证insert时⼀定有值过来，定义时使⽤default ‘’ ，或 0
5.同⼀意义的字段定义必须相同
⽐如不同表中都有 f_user_id 字段，那么它的类型、字段长度要设计成⼀样
四. 索引规范
1.任何新的select,update,delete上线，都要先explain，看索引使⽤情况
尽量避免extra列出现：Using File Sort，Using Temporary，rows超过1000的要谨慎上线。
explain解读
(1)type：ALL, index, range, ref, eq_ref, const, system, NULL（从左到右，性能从差到好）
(2)possible_keys：指出MySQL能使⽤哪个索引在表中到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不⼀定被查询使⽤
(3)key：表⽰MySQL实际决定使⽤的键（索引）
如果没有选择索引，键是NULL。要想强制MySQL使⽤或忽视possible_keys列中的索引，在查询中使⽤FORCE INDEX、USE INDEX或者IGNORE INDEX
(4)ref：表⽰选择 key 列上的索引，哪些列或常量被⽤于查索引列上的值
(5)rows：根据表统计信息及索引选⽤情况，估算的到所需的记录所需要读取的⾏数
(6)Extra
a.Using temporary：表⽰MySQL需要使⽤临时表来存储结果集，常见于排序和分组查询
b.Using filesort：MySQL中⽆法利⽤索引完成的排序操作称为“⽂件排序”
1.索引个数限制
(1)索引是双刃剑，会增加维护负担，增⼤IO压⼒，索引占⽤空间是成倍增加的
(2)单张表的索引数量控制在5个以内，或不超过表字段个数的20%。若单张表多个字段在查询需求上都要单独⽤到索引，需要经过DBA评估。
2.避免冗余索引
(1.)InnoDB表是⼀棵索引组织表，主键是和数据放在⼀起的聚集索引，普通索引最终指向的是主键地址，所以把主键做最后⼀列是多余的。如f_crm_id作为主键，联合索引(f_user_id,f_crm_id)上的f_crm_id就完全多余
(2)(a,b,c)、(a,b)，后者为冗余索引。可以利⽤前缀索引来达到加速⽬的，减轻维护负担
3.没有特殊要求，使⽤⾃增id作为主键
(1.)主键是⼀种聚集索引，顺序写⼊。组合唯⼀索引作为主键的话，是随机写⼊，适合写少读多的表
(2)主键不允许更新
4.索引尽量建在选择性⾼的列上
(1)不在低基数列上建⽴索引，例如性别、类型。但有⼀种情况，idx_feedbackid_type (f_feedback_id,f_type)，如果经常⽤ f_type=1⽐较，⽽且能过滤掉90%⾏，那这个组合索引就值得创建。有时候同样的查询语句，由于条件取值不同导致使⽤不同的索引，也是这个道理。
(2)索引选择性计算⽅法（基数 ÷ 数据⾏数）
Selectivity = Cardinality / Total Rows = select count(distinct col1)/count(*) from tbname，越接近1说明col1上使⽤索引的过滤效果越好
(3)⾛索引扫描⾏数超过30%时，改全表扫描
5.最左前缀原则
(1)mysql使⽤联合索引时，从左向右匹配，遇到断开或者范围查询时，⽆法⽤到后续的索引列
⽐如索引idx_c1_c2_c3 (c1,c2,c3)，相当于创建了(c1)、(c1,c2)、(c1,c2,c3)三个索引，where条件包含上⾯三种情况的字段⽐较则可以⽤到索引，但像 where c1=a and c3=c 只能⽤到c1列的索引，像 c2=b and c3=c等情况就完全⽤不到这个索引
(2)遇到范围查询(>、<、between、like)也会停⽌索引匹配，⽐如 c1=a and c2 > 2 and c3=c，只有c1,c2列上的⽐较能⽤到索引，
(c1,c2,c3)排列的索引才可能会都⽤上
(3)where条件⾥⾯字段的顺序与索引顺序⽆关，mysql优化器会⾃动调整顺序
6.前缀索引
(1)对超过30个字符长度的列创建索引时，考虑使⽤前缀索引，如 idx_cs_guid2 (f_cs_guid(26))表⽰截取前26个字符做索引，既可以提⾼查效率，也可以节省空间
(2)前缀索引也有它的缺点是，如果在该列上 ORDER BY 或 GROUP BY 时⽆法使⽤索引，也不能把它们⽤作覆盖索引(Covering Index)
(3)如果在varbinary或blob这种以⼆进制存储的列上建⽴前缀索引，要考虑字符集，括号⾥表⽰的是字节数
7.合理使⽤覆盖索引减少IO
INNODB存储引擎中，secondary index(⾮主键索引，⼜称为辅助索引、⼆级索引)没有直接存储⾏地址，⽽是存储主键值。
如果⽤户需要查询secondary index中所不包含的数据列，则需要先通过secondary index查到主键值，然后再通过主键查询到其他数据列，因此需要查询两次。覆盖索引则可以在⼀个索引中获取所有需要的数据列，从⽽避免回表进⾏⼆次查，节省IO因此效率较⾼。
例如SELECT email，uid FROM user_email WHERE uid=xx，如果uid不是主键，适当时候可以将索引添加为index(uid，email)，以获得性能提升。
8.尽量不要在频繁更新的列上创建索引
如不在定义了 ON UPDATE CURRENT_STAMP 的列上创建索引，维护成本太⾼（好在mysql有insert buffer，会合并索引的插⼊）五. SQL设计
①.正规化表设计原则
零状态形式：所需要的所有字段组成的表，未经过任何处理。
第⼀级正规化形式处理原则：1.消除每个表格中重复的组2.为每套相关的数据建⽴⼀个独⽴的表格3.使
⽤⼀个主键来标识每套相关的数据。第⼆级正规化形式处理原则：1.为应⽤在多条记录的字段建⽴独⽴的表格；2.通过⼀个foreign key来关联这些表格的值。
第三级正规化形式处理原则：1.消除不依赖于该键的字段。
第四个正规化形式处理原则：1.在⼀个多对多的关系中，独⽴的实体不能存放在同⼀个表格中。由于它仅应⽤于多对多的关系，因此⼤多数的开发者可以忽略这条规定。不过在某些情况下，它是⾮常实⽤的。
第五级正规化形式处理原则：1.原来的表格必须可以通过由它分离出去的表格重新构建，使⽤这个规定的好处是，你可以确保不会在分离的表格中引⼊多余的列，所有你创建的表格结构都与它们的实际需要⼀样⼤。应⽤这条规定是⼀个好习惯，不过除⾮你要处理⼀个⾮常⼤型的数据，否则你将不需要⽤到它。
②.SQL设计
1.杜绝直接 SELECT * 读取全部字段
即使需要所有字段，减少⽹络带宽消耗，能有效利⽤覆盖索引，表结构变更对程序基本⽆影响
2.能确定返回结果只有⼀条时，使⽤ limit 1
在保证数据不会有误的前提下，能确定结果集数量时，多使⽤limit，尽快的返回结果。
3.⼩⼼隐式类型转换
(1)转换规则
a. 两个参数⾄少有⼀个是 NULL 时，⽐较的结果也是 NULL，例外是使⽤ <=> 对两个 NULL 做⽐较时会返回 1，这两种情况都不需要做类型转换
b. 两个参数都是字符串，会按照字符串来⽐较，不做类型转换
c. 两个参数都是整数，按照整数来⽐较，不做类型转换
d. ⼗六进制的值和⾮数字做⽐较时，会被当做⼆进制串
e. 有⼀个参数是 TIMESTAMP 或 DATETIME，并且另外⼀个参数是常量，常量会被转换为 timestamp
f. 有⼀个参数是 decimal 类型，如果另外⼀个参数是 decimal 或者整数，会将整数转换为 decimal 后进⾏⽐较，如果另外⼀个参数是浮点数，则会把 decimal 转换为浮点数进⾏⽐较
g. 所有其他情况下，两个参数都会被转换为浮点数再进⾏⽐较。
(2)如果⼀个索引建⽴在string类型上，如果这个字段和⼀个int类型的值⽐较，符合第 g 条。如f_phone定义的类型是varchar，但where 使⽤f_phone in (098890)，两个参数都会被当成成浮点型。发⽣这个隐式转换并不是最糟的，最糟的是string转换后的float，mysql⽆法使⽤索引，这才导致了性能问题。如果是 f_user_id = ‘1234567’ 的情况，符合第 b 条，直接把数字当字符串⽐较。
4.禁⽌在where条件列上使⽤函数
(1)会导致索引失效，如lower(email)，f_qq % 4。可放到右边的常量上计算
(2)返回⼩结果集不是很⼤的情况下，可以对返回列使⽤函数，简化程序开发
5.使⽤like模糊匹配，%不要放⾸位
会导致索引失效，有这种搜索需求是，考虑其它⽅案，如sphinx全⽂搜索
6.涉及到复杂sql时，务必先参考已有索引设计，先explain
(1)简单SQL拆分，不以代码处理复杂为由。
(2)⽐如 OR 条件： f_phone=‘10000’ or f_mobile=‘10000’，两个字段各⾃有索引，但只能⽤到其中⼀个。可以拆分成2个sql，或者union all。
(3)先explain的好处是可以为了利⽤索引，增加更多查询限制条件
7.使⽤join时，where条件尽量使⽤充分利⽤同⼀表上的索引
(1)如 select t1.a,t2.b * from t1,t2 and t1.a=t2.a and t1.b=123 and t2.c= 4 ，如果t1.c与t2.c字段相同，那么t1上的索引(b,c)就只⽤到b了。此时如果把where条件中的t2.c=4改成t1.c=4，那么可以⽤到完整的索引
(2)这种情况可能会在字段冗余设计（反范式）时出现
(3)正确选取inner join和left join
8.少⽤⼦查询，改⽤join
⼩于5.6版本时，⼦查询效率很低，不像Oracle那样先计算⼦查询后外层查询。5.6版本开始得到优化
9.考虑使⽤union all，少使⽤union，注意考虑去重
(1)union all不去重，⽽少了排序操作，速度相对⽐union要快，如果没有去重的需求，优先使⽤union all
(2)如果UNION结果中有使⽤limit，在2个⼦SQL可能有许多返回值的情况下，各⾃加上limit。如果还有or
der by，请DBA。
10.IN的内容尽量不超过200个
超过500个值使⽤批量的⽅式，否则⼀次执⾏会影响数据库的并发能⼒，因为单SQL只能且⼀直占⽤单CPU，⽽且可能导致主从复制延迟11.拒绝⼤事务
⽐如在⼀个事务⾥进⾏多个select，多个update，如果是⾼频事务，会严重影响MySQL并发能⼒，因为事务持有的锁等资源只在事务

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