建表规范
-
数据库使用InnoDB存储引擎，字符编码utf8mb4
-
库名、表名、字段名必须使用小写字母，“_”分割。禁止出现数字开头，禁止两个下划线中间只出现数字。
-
表的命名最好是加上“业务名称_表的作用”。
正例:pay_task / force_project / trade_config -
库名与应用名称尽量一致。
-
表的每个字段必须有comment 说明。
-
库名、表名、字段名见名知意,建议使用名词而不是动词。不要使用复数名词。product 不是 products.
-
不得使用外键与级联，一切外键概念必须在应用层解决。
-
禁用保留字，如 desc、partition、 range、match、delayed、name，time ，datetime，password 等，请参考 MySQL 官方保留字。
-
表必备三字段:id, gmt_create, gmt_modified。其中id必为主键，类型为bigint unsigned、单表时自增、步长为1,分表分库的话建议用SnowFlake生成算法生成。gmt_create, gmt_modified 的类型均为 datetime 类型。
-
表达是与否概念的字段，必须使用 is_xxx 的方式命名，数据类型是 unsigned tinyint (1 表示是，0 表示否)。
说明:任何字段如果为非负数，必须是 unsigned。
注意:POJO 类中的任何布尔类型的变量，都不要加 is 前缀，所以，需要在设置 从 is_xxx 到 Xxx 的映射关系。数据库表示是与否的值，使用 tinyint 类型，坚持 is_xxx 的 命名方式是为了明确其取值含义与取值范围。
正例:表达逻辑删除的字段名 is_deleted，1 表示删除，0 表示未删除。 -
合适的字符存储长度，不但节约数据库表空间、节约索引存储，更重要的是提升检 索速度。
如下表，其中无符号(unsigned)值可以避免误存负数，且扩大了表示范围。
| 对象 | 年龄区间 | 类型 | 字节 | 表示范围 |
|---|---|---|---|---|
| 人 | 150岁之内 | tinyint unsigned | 1 | 无符号值:0到 255 龟 |
| 恐龙化石 | 千万年 | int unsigned | 4 | 无符号值:0 到约 42.9 亿 |
| 太阳 | 约50亿年 | bigint unsigned | 8 | 无符号值:0 到约 10 的 19 次方 |
- VARCHAR(N)，N表示的是字符数不是字节数而是字符数（英文一个字母一个字符，中文一个汉字一个字符），比如VARCHAR(255)，可以最大可存储255个汉字，需要根据实际的宽度来选择N。
VARCHAR 是可变长字符串，不预先分配存储空间，长度不要超过 5000，如果存储长 度大于此值，定义字段类型为 text，独立出来一张表，用主键来对应，避免影响其它字段索引效率。
- 不建议使用ENUM、SET类型，使用TINYINT来代替。
a)ENUM，有三个问题：添加新的值要做DDL，默认值问题(将一个非法值插入ENUM(也就是说，允许的值列之外的字符串)，将插入空字符串以作为特殊错误值)，索引值问题 - 除了接口表和临时表，在线数据一律不允许硬删除（归档除外）。如果业务上需要有数据删除的动作，通过软删除标识(is_deleted=1表示已删除)来操作。
索引规范
-
非唯一索引必须按照“idx_字段名称字段名称[字段名]”进行命名。
-
唯一索引必须按照“uniq_字段名称字段名称[字段名]”进行命名。
-
索引名称必须使用小写。
索引中的字段数建议不超过5个。
单张表的索引数量控制在5个以内。 -
建组合索引的时候，区分度最高的在最左边。
-
对长度过长的VARCHAR字段建立索引时，添加crc32或者MD5 Hash字段，对Hash字段建立索引；或者根据识别度指定索引长度。
- 合理创建联合索引（避免冗余），(a,b,c) 相当于 (a) 、(a,b) 、(a,b,c)。
- 使用EXPLAIN判断SQL语句是否合理使用索引，SQL 性能优化的目标:至少要达到 range 级别，要求是 ref 级别，如果可以是 consts 最好。
说明:
1)consts 单表中最多只有一个匹配行(主键或者唯一索引)，在优化阶段即可读取到数据。 2)ref 指的是使用普通的索引(normal index)。
3)range 对索引进行范围检索。 - 禁止使用%前缀模糊查询，例如LIKE “%weibo”。
- 禁止三个表以上 join。需要 join 的字段，数据类型必须绝对一致;多表关联查询时， 保证被关联的字段需要有索引。多表查询建议拆解成单表查询在程序里组合成对象。禁止写复杂的SQL,程序服务可以方便集群水平扩展，复杂的SQL造成数据库计算压力，数据库不方便水平扩展。
- 防止因字段类型不同造成的隐式转换，导致索引失效。
SQL语句规范
-
禁止写复杂的sql，禁止在SQL语句进行数学运算或者函数运算，容易将业务逻辑和DB耦合在一起。复杂的SQL造成数据库计算压力，数据库不方便水平扩展。尽量做到单表查询，在程序里组合成对象。
-
不要使用 count(列名)或 count(常量)来替代 count(*)，count(*)是 SQL92 定义的 标准统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 无关。 说明:count(*)会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行。
-
当某一列的值全是 NULL 时，count(col)的返回结果为 0，但 sum(col)的返回结果为 NULL，因此使用 sum()时需注意 NPE 问题。 正例:可以使用如下方式来避免sum的NPE问题:SELECT IF(ISNULL(SUM(g)),0,SUM(g)) FROM table;
-
SELECT语句只获取需要的字段。不要select *。增加很多不必要的消耗（cpu、io、内存、网络带宽）。
-
禁止使用存储过程，存储过程难以调试和扩展，更没有移植性。
-
使用合理的SQL语句减少与数据库的交互次数。比如多个ID查询可以用in(1,2…)；INSERT … ON DUPLICATE KEY UPDATE
REPLACE
INSERT IGNORE
INSERT INTO values(),() -
MySQL分页要先快速定位需要获取的 id 段，然后再关联:
SELECT a.* FROM 表 1 a, (select id from 表 1 where 条件 LIMIT 100000,20 ) b where a.id=b.id
- 当两张表的数据量比较大，又需要连接查询时，应该使用 FROM table1 JOIN table2 ON xxx的语法，避免使用 FROM table1,table2 WHERE xxx 的语法，因为后者会在内存中先生成一张数据量比较大的笛卡尔积表，增加了内存的开销。
常见数据字典命名约定
| 业务名称 | 字段 |
|---|---|
| 主键 | id |
| 用户名称 | user_name |
| 商户id | partner_id |
| 门店id | store_id |
| 商品ID | product_id |
| 交易流水号 | trade_no |
| 创建时间，修改时间 | gmt_create, gmt_modified |
| 逻辑删除 | is_deleted |
| 后续根据实际业务添加 | … … |
