数据库设计规范和设计工具

1、数据库设计规范和设计工具Mysql设计、使用及数据字典工具规范化设计规范行业规范3大范式第一范式（1NF）无重复的列 - 一张表不能够有相同的字段，这是所有范式的基础第二范式（2NF）属性完全依赖于主键 消除部分子函数依赖 - 一张表必须要有唯一标示第三范式（3NF）属性不依赖于其它非主属性 消除传递依赖 反范式设计（冗余结构设计） - 提升性能的手段从数据库的设计角度遵循范式没有什么问题，但是在实际的项目设计中会出现很大的问题，需要额外查询更多字表。反范式设计的目的：用空间来换取时间，把数据冗余在多个表中，当查询时可以减少或者是避免表之间的关联。数据冗余可以带来很好的读取性能，比如 NOSQ

2、L模型大量使用反范式设计。参考第三方规范sharding-jdbc Sql书写规范分片键必须放在where条件第一位（有可能是新版本bug）；查询分片必须携带分片键，例如分片键是CREATE_DATE，分片前是SELECT ID FROM TABLE WHERE ID=?，分片后查询需要SELECT ID FROM TABLE WHERE CREATE_DATE=? AND ID=?。不携带分片键仍然可以获得正确结果，但是会进行全表查询，性能低下；sharding-jdbc 功能及限制我们的规范说明： 本规范适用于新的设计，已存在的历史设计暂不做调整。 本规范适用于wdzj

3、 oltp事务处理型应用，不适用于olap分析型应用。· DB设计 ER图创建和维护ER图和数据字典设计ER图有助于思考实体与实体关系,ER图能明确表达实体关系 (1:1 , 1:N , M:N)在DB设计阶段要考虑业务场景、及相应的查询语句和索引创建和维护数据字典： 必须含有表定义说明，字段含义说明 字段枚举值和含义 需要完整罗列，填写到 comment中分库分表设计 设计阶段要考虑业务的数据量，进行扩展性、hash算法设计，分片技术选型设计事务型应用 跟钱有关的应用必须使用事务 需要考虑锁的设计 选择 innodb 乐观锁、行锁、避免死锁，跨DB事务 最终一致性善用冗余设计 如评

4、论量、浏览量、· 表定义表名命名 统一小写、 有意义的名字前缀：表名不加前缀（不同业务应该分开存储到不同的库中，如果实际中单个库涉及到多个业务，使用业务类型作为前缀，如 "hongbao_" 红包业务的表,"openplat_" 开放平台业务的表）、索引"idx_"、视图前缀"v_"、存储过程前缀"sp_"、触发器前缀"tr_"表定义 必须含有 comment 说明注释；表定义 必须有明确的字符集 UTF-8 ;表定义 必须有明确的存储

5、引擎 （MyISAM、InnoDB等）;必须有明确的主键字段定义、约束定义、外键定义· 字段定义字段命名 统一小写、用有意义的英文或拼音命名不允许非null字段，确定default取值;必须有含义说明、完整的字段枚举值和含义 在comment 说明注释；必须有明确的字符集UTF-8 ;· 类型选择能使用字符串类型（string）就不用文本类型（text）能使用数值型（int）的就不用字符串类型（string） int(10) - 不能限定长度 能使用时间类型(date,time,datetime)的就不用字符串类型能使用小值类型(tinyint

6、,smallint,mediumint,int,bigint)的就不用大值类型（tinyint,smallint,mediumint,int,bigint）善用数值类型，unsigned 、decimal用于精度要求非常高的类型，如decimal(12,2)善用二进制类型 binary慎用timestamp、存储过程、触发器、like、正则、· 避免复杂的SQL o 禁止用子查询o 表连接最多两层o 分页计数 count (子查询 - 只需要where部分)o 禁止使用mysql函数o 性能不佳的情况将join拆分为多个单表查询后台新闻分页查询 - SELECT REPLY

7、COUNT, TYPENAME, SHORTLINK, TYPEID, NEWSID, NEWSIDX, RESOURCEFROM, DISPLAYFLG, TOPFLG, PUBLISHDATE, TITLE, AUTHOR, SUMMARY, ORIGINALFLG, WDZJONLY, SUBJECTTYPE, HITS, PICURL, OUTHTMLFILEURL, VERIFYFLG, VERIFYUSERNAME, VERIFYUSERID, VERIFYDATE, KEYWORDS, DESCRIPTION, STAYLONG, SEOTITLE, INSUSERNAME, W

8、DZJUSERLEVELLABEL FROM ( SELECT T.REPLYCOUNT AS REPLYCOUNT, T1.TYPENAME, T1.SHORTLINK, T.TYPEID, T.NEWSID, T.NEWSIDX, T.RESOURCEFROM, T.DISPLAYFLG, T.TOPFLG, T.PUBLISHDATE, T.TITLE, T.SHORTTITLE, T.AUTHOR, T.SUMMARY, T.ORIGINALFLG, T.WDZJONLY, T.SUBJECTTYPE, T.HITS, T.RECOMMEND, T.PICURL, T.OUTHTMLF

9、ILEURL, T.VERIFYFLG, T.VERIFYUSERNAME, T.VERIFYUSERID, T.VERIFYDATE, T.KEYWORDS, T.DESCRIPTION, T.STAYLONG, T.SEOTITLE, T.INSUSERNAME, T.WDZJUSERLEVELLABEL FROM WDZJNEWSTYPE T1, WDZJNEWS T WHERE T.TYPEID = T1.TYPEID AND T.APPNEWSFLG = '0' AND T.NEWSFLG = '0' AND T.TYPEID = 47 AND ( T

10、1. LEVEL = '0,22,47' OR T1. LEVEL LIKE '0,22,47,%' ) AA ORDER BY AA.NEWSIDX DESC, AA.PUBLISH_DATE DESCselect title from WDZJNEWS where typeid regexp '4$' #查询类型id为4的新闻· 慎用全表扫描档案信批报告 取数没有分页 ， 现在是取出所有数据在内存分页 - 取合适大小的分页数量 （ 全量静态化 合适大小 1000条，常规业务应该按照实际恩也大小查询 如 20条）· 善用 预

11、处理查询 ， 禁用拼sql方式规避sql注入 、 sql语句缓存提升性能· SQL性能查询计划分析explain 慢查询分析 mysql记录慢查询（ 运维周期提供慢查询记录邮件，每2周解决top10的慢查询 ），应用记录慢查询索引使用· 索引使用生产环境db更新运维更新  不符合以下规范的打回修改· 含有null字段；· 没有 default value缺省取值；· 表定义 无comment 说明注释；· 表定义 缺少明确的字符集；· 表定义 缺少存储引擎 （MyISAM、InnoDB等）