数据库最佳实践

一、数据库设计原则
1.考虑存储成本: 

• ·不在数据库中存储图片、文件、视频等大数据。
• ·在某些历史数据可清理的场景，建议使用分区表，通过删除分区，可以很容易的删除数据

2.考虑读写性能: 

• ·使用执行高效的sq1语句。不建议多表联合查询
• ·拒绝大sq1语句、大事务、大批量操作。--大量数据更新，进入DB时，需要批量操作，一个事务不能高于5000条

3.考虑技术一致性、可迁移性: 

• ·数据库及表统一使用UTF8字符集
• ·禁止使用存储过程和触发器、视图、自定义函数、可移植性差的函数等

4.考虎虑数据安全性: 

• ·不允许物理删除数据行，建议统-使用is_deleted字段实现逻辑删除。
• ·物理删除恢复数据非常困难，误删代价非常大。
• ·物理删除会让数据的主键值变的不连续，不连续的主键值会让分页查询的速度变慢。
• ·物理删除既不能料放磁盘空间，而目会产生大量的碎片，导致索引频繁断裂，影响SOL执行计划的稳定性，同时，在碎片回收时，会耗用大量的CPU，磁盘空间，影响表的正常DML操作。
• ·物理删除会导致大数据抽数或其他数据同步的场景数据异常，对于数据的分析处理会有错误，修数重跑会很麻烦。

二、命名规范

• ·表和列禁止使用关键字和保留字命名。
• ·数据库、表、列、索了命名统使用小写。
• ·表名全小写字母，使用单数名词，用连接多个单词，建议加上业务前缀，比如:user_log。
• ·唯一索引前缀为uk_，普通索引前缀为idx_，索引名格式:前缀_表名或表名首字母_列名。

三、设计规范
表设计规范

• ·线上表列数建议不要超过50 表越宽字段越多，dm操作的效率也会越差
• ·除临时表以外，其他表必须有两个日期字段，created date(创建日期)，updated date(修改日期)，且非空；还必须有created by(创健人)，updated by(修改人)，更新时必须同时更新updated date和updated by。--created_date, updated_date不能用于业务功能，需要新建字段使用
• ·表必须有is_deleted字段，实现逻辑删除。
• ·表必须有物理主键。设置为ID，必须为主键索引(用ID做物理主键)，长度建议18位以上。

字段设计规范

• ·不允许使用FLOAT和DOUBLE存储精确浮点数，用numeric 
• ·字符串类型，使用VARCHAR，不使用CHAR。
• ·使用DATETIME存储日期时间。
• ·新表所有字段均定义为NOT NULL, null可用0或''代替。否则容易出现索失效问题
• ·尽量不使用BLOB和TEXT等长字段
• ·禁止删除线上表字段，或修改线上表及字段名。
• ·根据更新的频繁程度决定字段的顺序(例如，把updated_by字段放到所有业务字段前)。

--MySQL关键字，保留字: 
https://dev.mysq1l.com/doc/refman/5.6/en/keywords.html 
--TiDB关键字，保留字: 
https://docs.pingcap.com/zh/tidb/v3.0/keywords-and-reserved-words 
--PG关键字，保留字: 
https://www.postgresql.org/doCs/12/sql-keywords-appendix.html 
-oracle关键字，保留字: 
https://docs.oracle.com/en/database/oracle/oracle-database/21/lnpls/plsgl-reserved-words-keywords.html

索引设计规范

• ·只允许使用普通索引或者唯一索引I。
• ·不要创建冗余或者无效的索引。
• ·组合索引中，范围查询的字段放到组合索引最后(一般是时间字段)。
• ·已上线的索引，不允许修改/删除，特殊情况由dba走变更实施。
• ·索引上字段长度大大会影响性能，请根据业务选择合适的字段长度。
• ·单表索引不要超过5个，单索引字段数不要超过5个。
• ·建议updated_date字段创建索引(记录数大于100w则必须创健)。
• ·设计有逻辑删除时唯一索引可以加一个字段，删除的时候填id，新增的时候写一个常量

注意: 

• ·索引占磁盘空间，不要重复的索引，尽量短
• ·过滤性高的列建索引，取值范围固定的列不建索引
• ·不要对索引列运算
• ·多列组合索引，过滤性高的字段最前
• ·order by字段建立索引，避免filesort 
• ·组合索引，不同的排序顺序不能使用索引！
• ·>!=无法使用索引

分区表设计规范

• ·分区键统使用PARTITION_KEY(BIGINT default '0' NOT NULL),分区键的值为日期值，例如201802
• ·分区表的主键PRIMARY KEY定义为(ID,PARTITION_KEY)。
• ·分区表规范推荐按月range分区，并提前预留18个月的分区数(分区数量不能超过20个)，要求可定期清理归档后的历史数据。
• ·分表的UK约束(索引)，必须带上分区键，例妆如UK_LSL_ACTIVITY (ACTIVITY,PARTITION_KEY)。
• ·对于分区表的select，update, insert,必须带上partition_key。

四、SQL编写规范

• ·禁止使用全表查询，配置表和小表(数据总量小于1万条)例外
• ·使用正确类型的变量，杜绝DB层做隐式类型转换的情况(例子)。不好的示例::product usergroupmapping where is valided =1, products where id='1'
• ·避免直接的全模糊查询，会走不上索引。不好的示例:select id from table where name like '%jacky%' 
• ·select需要写明具体字段，不允许直接用select*,不建议选取过多不需要的列。
• ·语句中使用到的所有column，要带上表名，例如:select a.columnA,b.columB from table a join table b on a.id=b.id 
• ·批量查询不要使用or条件，用in的方式进行查询，一次不要超过200个value. 
• ·禁止使用到select*from table for update操作，用乐观锁保障。
• ·禁止使用not in 
• ·尽量减少子查询的使用。
• ·并发SQL需要控制单次获取数据的量，并发越高的sQL，获取的数据量应越低。--可选的方案是分多次获取，削峰填谷
• ·where条件中禁止使用<>、!=进行字段关联。
• ·如使用多表连接，禁止表个数超过3个
• ·禁止使用正则表达式。

五、分库分表

• 考虑到机房双活的机制，一般建议按照2的次方数进行分库分表，比如:2,4,8分片等，schema的数据量和表的数量-致。
• schema的命名由schema名+两位序号，例如:fnddata_01。
• table的命名由表名+三位序号，例如:fnd_records_001。
• index的命名由索引名+表序号，例如:pk_fnd_records_id_001 

数据库参数最佳实践

dba 建议 查询最大连接数根据查询的量来调整最大值，建议 maxActive=15，设置成一样可以防止回收。initialSize=minIdle=5。queryTimeout 不用设置，一般用的是 CONNECT TIMEOUT 和 SOCKET TIMEOUT。

maxIdle 在 druid 中已经弃用了，不用设置