数据库开发规范

库表设计规范
Common
【强制】只能使用小写字母、下划线及数字，但不能以数字和下划线开头，禁止两个下划线中间只出现数字，使用见名知意的单词
【强制】禁止使用拼音！
【强制】禁用保留字，如desc、range、match、delayed等，参考本文的“MYSQL保留字”
【强制】建表必须增加注释，包括：表级注释、字段级注释并保持更新
【推荐】库名与应用名称尽量一致。
Database
【强制】数据库名以db_开头
Table
【强制】表名称以t_开头，规范同数据库命名
【强制】表引擎使用InnoDB
【强制】所有表字符编码设置为UTF-8，如果需要存储emoj表情，需要使用UTF8mb4(MySQL 5.5.3以后支持)
【强制】禁止使用外键。说明：外键与级联更新适用于单机低并发，不适合分布式、高并发集群；级联更新是强阻塞，存在数据库更新风暴的风险；外键影响数据库的插入速度。
【强制】表名不使用复数名词。 说明：表名应该仅仅表示表里面的实体内容，不应该表示实体数量，对应于DO类名也是单数形式，符合表达习惯
【强制】varchar是可变长字符串，不预先分配存储空间，长度不要超过5000，如果存储长度大于此值，定义字段类型为text，独立出来一张表，用主键来对应，避免影响其它字段索引效率
【强制】如果存储的字符串长度几乎相等，使用char定长字符串类型
【推荐】表的命名最好是加上“业务名称_表的作用”。 正例：tiger_task / tiger_reader / mpp_config
【推荐】单表行数超过500万行或者单表容量超过2GB，才推荐进行分库分表。 说明：如果预计三年后的数据量根本达不到这个级别，请不要在创建表时就分库分表。
【推荐】表级注释增加创建者的姓名，便于当表过期时，通知负责人更新文档
Column
【强制】字段名以f_开头
【强制】建表必须存在的四个字段：
f_id：自增主键，单表自增，步长为1。说明：auto_increment类型字段会被MySQL默认作为Primary Key。自增主键会提升数据插入性能。
f_create_time：创建时间，datetime类型；
f_update_time：更新时间，datetime类型。
f_state：unsigned tinyint类型，1表示有效；0表示无效（逻辑删除）。会分期原则上不允许任何数据被物理删除（delete操作）
【强制】任何字段如果为非负数，必须是unsigned
【强制】表达是与否概念的字段，必须使用is_xxx的方式命名，数据类型是unsigned tinyint（ 1表示是，0表示否）
【强制】金额禁止使用浮点数，一律使用unsigned int
【强制】小数类型为decimal，禁止使用float和double。 说明：float和double在存储的时候，存在精度损失的问题，很可能在值的比较时，得到不正确的结果。如果存储的数据范围超过decimal的范围，建议将数据拆成整数和小数分开存储。
【强制】时间类型字段，不要使用timestamp，建议使用datetime。说明：timestamp存在时区转化的问题，并且支持的时间范围比datetime窄
【推荐】所有字段均定义为NOT NULL ，除非你真的想存NULL；
【推荐】如果修改字段含义或对字段表示的状态追加时，需要及时更新字段注释
【推荐】字段允许适当冗余，以提高性能，但是必须考虑数据同步的情况。冗余字段应遵循：
1）不是频繁修改的字段。
2）不是varchar超长字段，更不能是text字段。 正例：商品类目名称使用频率高，字段长度短，名称基本一成不变，可在相关联的表中冗余存储类目名称，避免关联查询。
【推荐】使用varchar存储变长字符串 ，当然要注意varchar(M)里的M指的是字符数不是字节数；使用UNSIGNED INT存储IPv4 地址而不是CHAR(15) ，这种方式只能存储IPv4，存储不了IPv6
【参考】合适的字符存储长度，不但节约数据库表空间、节约索引存储，更重要的是提升检索速度。 正例：无符号值可以避免误存负数，且扩大了表示范围。
研发规范&工作流程 > 数据库开发规范 > image2017-4-20 18:54:37.png
Index
【强制】主键索引名为pk_字段名；唯一索引名为uk_字段名；普通索引名则为idx_字段名。 说明：uk_ 即 unique key；idx_ 即index的简称。
【强制】单个索引字段数不超过5，单表索引数量不超过5，索引设计遵循B+ Tree索引最左前缀匹配原则
【强制】业务表必须创建唯一索引（注意不是primary key，而是unique key）。即使是组合字段，也必须建成唯一索引。 说明：不要以为唯一索引影响了insert速度，这个速度损耗可以忽略，但提高查找速度是明显的；另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生
【强制】超过三个表禁止join。需要join的字段，数据类型必须绝对一致；多表关联查询时，保证被关联的字段需要有索引。 说明：即使双表join也要注意表索引、SQL性能。
【强制】在varchar字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度即可。 说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为20的索引，区分度会高达90%以上，可以使用count(distinct left(列名, 索引长度))/count(*)的区分度来确定。
【强制】避免冗余索引。冗余索引例子：
idx_abc(a,b,c)
idx_a(a) 冗余
idx_ab(a,b) 冗余
【推荐】创建索引时避免有如下极端误解：
1）误认为一个查询就需要建一个索引。
2）误认为索引会消耗空间、严重拖慢更新和新增速度。
3）误认为唯一索引一律需要在应用层通过“先查后插”方式解决。
【推荐】建立的索引能覆盖80%主要的查询，不求全，解决问题的主要矛盾
SQL编写规范
【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。 说明：索引文件具有B-Tree的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。
【强制】不要使用count(列名)或count(常量)来替代count()，count()是SQL92定义的标准统计行数的语法，跟数据库无关，跟NULL和非NULL无关。 说明：count(*)会统计值为NULL的行，而count(列名)不会统计此列为NULL值的行
【强制】禁止使用存储过程，存储过程难以调试和扩展，更没有移植性。
【强制】UPDATE、DELETE语句不使用LIMIT ，容易造成主从不一致
【推荐】in操作能避免则避免，若实在避免不了，需要仔细评估in后边的集合元素数量，控制在1000个之内。
【推荐】如果有order by的场景，请注意利用索引的有序性。order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现file_sort的情况，影响查询性能。 正例：where a=? and b=? order by c; 索引：a_b_c 反例：索引中有范围查找，那么索引有序性无法利用，如：WHERE a>10 ORDER BY b; 索引a_b无法排序。
【推荐】利用覆盖索引来进行查询操作，避免回表。 说明：如果一本书需要知道第11章是什么标题，会翻开第11章对应的那一页吗？目录浏览一下就好，这个目录就是起到覆盖索引的作用。 正例：能够建立索引的种类：主键索引、唯一索引、普通索引，而覆盖索引是一种查询的一种效果，用explain的结果，extra列会出现：using index。
【推荐】利用延迟关联或者子查询优化超多分页场景。 说明：MySQL并不是跳过offset行，而是取offset+N行，然后返回放弃前offset行，返回N行，那当offset特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行SQL改写。 正例：先快速定位需要获取的id段，然后再关联： SELECT a.* FROM 表1 a, (select id from 表1 where 条件 LIMIT 100000,20 ) b where a.id=b.id
【推荐】 SQL性能优化的目标：至少要达到 range 级别，要求是ref级别，如果可以是consts最好。 说明： 1）consts 单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数据。 2）ref 指的是使用普通的索引（normal index）。 3）range 对索引进行范围检索。 反例：explain表的结果，type=index，索引物理文件全扫描，速度非常慢，这个index级别比较range还低，与全表扫描是小巫见大巫。
【推荐】建组合索引的时候，区分度最高的在最左边。 正例：如果where a=? and b=? ，a列的几乎接近于唯一值，那么只需要单建idx_a索引即可。 说明：存在非等号和等号混合判断条件时，在建索引时，请把等号条件的列前置。如：where a>? and b=? 那么即使a的区分度更高，也必须把b放在索引的最前列。
【推荐】避免在数据库中进行数学运算和其他大量计算任务
【推荐】避免使用 select *，尽可能给出查询列名。select f_user_name,f_user_age from xxx
数据修复规范
【强制】数据订正时，删除和修改记录时，要先select，避免出现误删除，确认无误才能执行更新语句。
MYSQL保留字
ADD ALL ALTER
ANALYZE AND AS
ASC ASENSITIVE BEFORE
BETWEEN BIGINT BINARY
BLOB BOTH BY
CALL CASCADE CASE
CHANGE CHAR CHARACTER
CHECK COLLATE COLUMN
CONDITION CONNECTION CONSTRAINT
CONTINUE CONVERT CREATE
CROSS CURRENT_DATE CURRENT_TIME
CURRENT_TIMESTAMP CURRENT_USER CURSOR
DATABASE DATABASES DAY_HOUR
DAY_MICROSECOND DAY_MINUTE DAY_SECOND
DEC DECIMAL DECLARE
DEFAULT DELAYED DELETE
DESC DESCRIBE DETERMINISTIC
DISTINCT DISTINCTROW DIV
DOUBLE DROP DUAL
EACH ELSE ELSEIF
ENCLOSED ESCAPED EXISTS
EXIT EXPLAIN FALSE
FETCH FLOAT FLOAT4
FLOAT8 FOR FORCE
FOREIGN FROM FULLTEXT
GOTO GRANT GROUP
HAVING HIGH_PRIORITY HOUR_MICROSECOND
HOUR_MINUTE HOUR_SECOND IF
IGNORE IN INDEX
INFILE INNER INOUT
INSENSITIVE INSERT INT
INT1 INT2 INT3
INT4 INT8 INTEGER
INTERVAL INTO IS
ITERATE JOIN KEY
KEYS KILL LABEL
LEADING LEAVE LEFT
LIKE LIMIT LINEAR
LINES LOAD LOCALTIME
LOCALTIMESTAMP LOCK LONG
LONGBLOB LONGTEXT LOOP
LOW_PRIORITY MATCH MEDIUMBLOB
MEDIUMINT MEDIUMTEXT MIDDLEINT
MINUTE_MICROSECOND MINUTE_SECOND MOD
MODIFIES NATURAL NOT
NO_WRITE_TO_BINLOG NULL NUMERIC
ON OPTIMIZE OPTION
OPTIONALLY OR ORDER
OUT OUTER OUTFILE
PRECISION PRIMARY PROCEDURE
PURGE RAID0 RANGE
READ READS REAL
REFERENCES REGEXP RELEASE
RENAME REPEAT REPLACE
REQUIRE RESTRICT RETURN
REVOKE RIGHT RLIKE
SCHEMA SCHEMAS SECOND_MICROSECOND
SELECT SENSITIVE SEPARATOR
SET SHOW SMALLINT
SPATIAL SPECIFIC SQL
SQLEXCEPTION SQLSTATE SQLWARNING
SQL_BIG_RESULT SQL_CALC_FOUND_ROWS SQL_SMALL_RESULT
SSL STARTING STRAIGHT_JOIN
TABLE TERMINATED THEN
TINYBLOB TINYINT TINYTEXT
TO TRAILING TRIGGER
TRUE UNDO UNION
UNIQUE UNLOCK UNSIGNED
UPDATE USAGE USE
USING UTC_DATE UTC_TIME
UTC_TIMESTAMP VALUES VARBINARY
VARCHAR VARCHARACTER VARYING
WHEN WHERE WHILE
WITH WRITE X509
XOR YEAR_MONTH ZEROFILL