开发相关数据库规范

开发相关数据库规范

		解读
1、基础规范	必须使用InnoDB存储引擎	解读：支持事务、行级锁、并发性能更好、CPU及内存缓存页优化使得资源利用率更高
	必须使用UTF 8mb4字符集	解读：统一字符集，万国码，无需转码，无乱码风险，节省空间，创建库和表字符集和排序规则定义为DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
	必须为数据表、数据字段加入中文注释	除主键外的其它字段都有需要增加注释。请使用英文标点，避免出现乱码
	禁止使用存储过程、试图、触发器、Event	高并发大数据的互联网业务，架构设计思路是“解放数据库CPU，将计算转移到服务层”，并发量大的情况下，这些功能可能将数据库拖死，业务逻辑放到服务层具备更好的扩展性，能够轻易实现“增机器就能加性能”。数据库擅长存储和索引，CPU计算还是上移吧
	禁止存储大文件或大照片	为何要让数据库做它不擅长的事情？大文件和照片存储在文件系统，数据库存url多好
	禁止使用分区表	分区表在物理上表现多个文件，在逻辑上表现为一个表，跨分区查询效率可能很低，建议采用物理分表的方式管理大数据。
	禁止在数据库中存储明文密码	解读: 防止被拖库 包括不能明文存储手机号、银行卡号、QQ、邮箱等会员个人信息
二、命名规范	库名、表名、字段名：小写，下划线风格，不超过32个字符；必须见名知意，禁止拼音英文混用	另外： 库名、表名、字段名禁止使用MySQL保留关键字（如 desc、range、match、delayed 等，请参考 【MySQL官方保留字】）
	非唯一索引名idx_xxx，唯一索引名uniq_xxx
三、表设计规范	单实例表数目必须小于500
	单表列数目必须小于50	超过50个字段的表考虑拆表
	必须有一个与业务无关的自增主键(5.7版本) 8.0版本中开发可以使用id生成器生成全局唯一主键或者使用自增主键.	解读： a）主键递增，数据行写入可以提高插入性能，可以避免page分裂，减少表碎片提升空间和内存的使用 b）主键要选择较短的数据类型， Innodb引擎普通索引都会保存主键的值，较短的数据类型可以有效的减少索引的磁盘空间，提高索引的缓存效率 c） 无主键的表删除，在row模式的主从架构，会导致备库夯住 d)  不是自增id，会导致原本可以顺序写入的请求变成随机写入，效率更低。 示例: 主键使用 `id` bigint unsigned NOT NULL AUTO_INCREMENT
	禁止在表中建立预留字段	解读： 预留字段的命名很难做到见名识义 预留字段无法确认存储的数据类型，所以无法选择合适的类型 对预留字段类型的修改，会对表进行锁定 (除非有非常明确的目的)
	禁止使用外键，如果有外键完整性约束，需要应用程序控制	解读：外键会导致表与表之间耦合，update与delete操作都会涉及相关联的表，十分影响sql 的性能，甚至会造成死锁。高并发情况下容易造成数据库性能，大数据高并发业务场景数据库使用以性能优先
四、字段设计规范	必须把字段定义为NOT NULL并且提供默认值(text类型除外)	解读： a）null的列使索引/索引统计/值比较都更加复杂，对MySQL来说更难优化 b）null 这种类型MySQL内部需要进行特殊处理，增加数据库处理记录的复杂性；同等条件下，表中有较多空字段的时候，数据库的处理性能会降低很多 c）null值需要更多的存储空，无论是表还是索引中每行中的null的列都需要额外的空间来标识 d）对null 的处理时候，只能采用is null或is not null，而不能采用=、in、<、<>、!=、not in这些操作符号。如：where name!='shenjian'，如果存在name为null值的记录，查询结果就不会包含name为null值的记录 示例: NOT NULL DEFAULT XXX( '' 或 0 或 特殊值)
	禁止使用BLOB类型	解读： 会浪费更多的磁盘和内存空间，非必要的大量的大字段查询会淘汰掉热数据，导致内存命中率急剧降低，影响数据库性能 禁止给超过十万行的大表建立TEXT类型.  大表如果要建立TEXT类型必须要拆表,然后用主键和主表关联
	金额类数据必须使用 decimal 类型	解读：浮点类型容易导致钱对不上 （Decimal类型为精准浮点数，在计算时不会丢失精度）
	必须使用varchar(20)存储手机号	解读： a）涉及到区号或者国家代号，可能出现+-() b）手机号会去做数学运算么？ c）varchar可以支持模糊查询，例如：like '138%' 示例: `phone` varchar(20) NOT NULL DEFAULT '' COMMENT '联系电话'
	时间字段设计规范	a)  禁止往mysql时间类型的字段中传 '0000-00-00 00:00:00' ，'0000-00-00' 这种值 b)  date类型默认值设为 '1970-01-01'，datetime类型默认值设为'1970-01-01 00:00:00'  c)  '创建时间'，强制使用:   `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间' d)  '更新时间'，强制使用:   `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
	必须使用尽可能小的VARCHAR字段，能用varchar(20)的就不用varchar(255)	解读： 合适的字符存储长度，不但节约数据库表空间、节约索引存储，更重要的是提升检索速度。
	禁止使用ENUM，可使用TINYINT代替	解读： a）增加新的ENUM值要做DDL操作 b）ENUM的内部实际存储就是整数，你以为自己定义的是字符串？
五、索引设计规范	单表索引控制在5个以内	解读： 更新会变更B+树，索引过多会降低数据库性能
	单表唯一索引的数量必须小于3个
	组合索引字段数不允许超过5个	解读：字段超过5个时，实际已经起不到有效过滤数据的作用了
	禁止创建冗余索引	解读： a) 主键也是一个索引(效率最高)， 本身还有唯一性约束 b) 联合索引  inx_a_b_c(a,b,c) ;   相当于创建了 idx(a), idx(a,b), idx(abc)
	禁止对过长的varchar字段创建索引	解读： 对长字符串应该考虑使用前缀索引，前缀索引长度不超过8个字符。  不要对过长的VARCHAR字段建立索引。优先考虑前缀索引。或添加CRC32 CRC64或MD5伪列并建立索引。例：SELECT * FROM urls WHERE url = 'http://wwww.shopperplus.com'; 建立crc_url伪列，程序使用时对url取其crc32值，然后select * from urls where crc_url=1215054814
	禁止在更新十分频繁、区分度不高的属性上建立索引	解读： a）更新会变更B+树，更新频繁的字段建立索引会大大降低数据库性能 b）'性别' 这种区分度不大的属性，建立索引是没有什么意义的，不能有效过滤数据，性能与全表扫描类似
	建立组合索引，必须把区分度高的字段放在前面	解读：能够更加有效的过滤数据 在定义联合索引时，如果a列要用到范围查找的话，就要把a列放到联合索引的右侧 区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0
	双活数据校验需求	如果updated_at没有索引需要添加 KEY `idx_updated_at` (`updated_at`) ,有联合索引第一位可以不加
六、SQL使用规范	禁止使用SELECT *，只获取必要的字段，需要显示说明列属性	解读： a）读取不需要的列会增加CPU、IO、NET消耗 b）不能有效的利用覆盖索引 c）使用SELECT * 容易在增加或者删除字段后出现程序BUG
	禁止使用INSERT INTO t_xxx VALUES(xxx)，必须显式指定插入的列属性	解读：容易在增加或者删除字段后出现程序BUG 正确写法：INSERT INTO t_xxx(col1, col2, col3) VALUES('a','b','c') 错误写法：INSERT INTO t_xxx VALUES('a','b','c')
	禁止使用属性隐式转换	解读：对索引字段做函数操作或者计算时,优化器会放弃使用索引. 例如: SELECT uid FROM t_user WHERE phone=13812345678 会导致全表扫描，而不能命中phone索引.（这个线上问题不止出现过一次） 不走索引的原因     phone 字段类型是 varchar(20),而语句中 phone 字段条件值没加单引号,导致 MySQL 内部会先把a转换成int型,再去做判断,相当于实际执行的 SQL 语句如下:     mysql> select uid from t_user where cast(phone as signed int) =13812345678 ;
	禁止在WHERE条件的属性上使用函数或者表达式	解读：索引树中存储的是列的实际值和主键值 例如: SELECT uid FROM t_user WHERE from_unixtime(day)>='2017-02-15' 会导致全表扫描 如果拿 '2017-02-15' 去匹配,将无法定位到索引树中的值。因此放弃走索引,而选择全表扫描。 正确的写法是：SELECT uid FROM t_user WHERE day>= unix_timestamp('2017-02-15 00:00:00')
	减少负向查询，以及%开头的模糊查询	解读： a）负向查询条件：NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等，会导致全表扫描    《类比字典》 b）%开头的模糊查询，会导致全表扫描    《类比组合索引》
	禁止使用子查询	解读：会产生临时表，消耗较多内存与CPU，极大影响数据库性能 可以把子查询优化为join操作, 禁止超过3个表以上的join查询
	禁止在 update 时将逗号 ”,” 写成 ”and”，非常非常非常危险	正确示例：update table set uid=uid+1000,gid=gid+100 where id<=2;         错误示例：update table set uid=uid+1000 and gid=gid+1000 where id<=2;  此时uid=uid+1000 and gid=gid+1000将作为值(1或0)赋值给uid，并且无warning!
	建议拆分复杂的大SQL为多个小SQL	1、大SQL:逻辑上比较复杂，需要占用大量CPU进行计算的SQL       MySQL 一个SQL只能使用一个CPU进行计算，SQL拆分后可以通过多核CPU并行执行来提高处理效率 2、禁止在主库上执行后台管理和统计类的功能查询，这种复杂类的SQL会造成CPU的升高，进而会影响业务。
	禁止使用order by rand()	解读 会把表中所有符合条件的数据装载到内存中，然后在内存中对所有数据根据随机生成的值进行排序，并且可能会对每一行都生成一个随机值，如果满足条件的数据集非常大， 就会消耗大量的CPU和IO及内存资源  推荐在程序中获取一个随机值，然后从数据库中获取数据的方式
七、行为规范	流程工单SQL禁止使用变量	例如: select @parentId ：= LAST_INSERT_ID() 解读: 执行后无法生成回滚语句，请提供确切得数值
	禁止有super权限的应用账号存在	安全第一，super权限会导致很多诡异问题且难以进行追踪
	禁止有DDL、DCL权限的应用账号存在
	重大项目的数据库方案选型和设计必须提前通知DBA
	批量导入、导出数据必须通过DBA审核，并在执行过程中观注监控
	批量更新数据，如DELETE、UPDATE操作，必须DBA审核，并在执行过程中观注监控
	产品出现非数据库导致故障时，如被攻击，必须及时通知DBA，便于维护服务稳定
	业务部门程序出现bug等影响数据库服务的问题，必须及时通知DBA，便于维护服务稳定
	业务部门推广活动或上线新功能，必须提前通知DBA进行服务和访问量评估，并留出必要时间以便DBA完成扩容
	出现业务部门人为误操作导致数据丢失，需要恢复数据的，必须第一时间通知DBA，并提供准确时间点、误操作语句等重要线索
	不要在数据库中存放业务逻辑	数据库是有状态的服务，变更复杂而且速度慢，如果把业务逻辑放到数据库中，将会限制业务的快速发展。建议把业务逻辑提前放到前端或者中间逻辑层，数据库仅仅作为存储层，实现逻辑与存储分离