大型互联网公司使用的数据库设计规范
- 2019 年 10 月 4 日
- 筆記
DDL设计标准
1、 所有表的DDL，都不回退
2、 数据库命名规范，统一：hs_xxxx；表名不超过40个字符（即最大只能40个字符）
3、 表一旦设计好，字段只允许增加，不允许减少（drop column），不允许改名称（change column）
4、 统一使用INNODB存储引擎,UTF8编码（整个数据库的编码统一为utf8_general_ci，为此不需要建立表的DDL加上特别CHARACTER SET utf8 COLLATE utf8_general_ci）；
5、 禁用Stored procedure （包括存储过程，函数，触发器）；
6、 表必须有主键，建议统一由Auto-Increment字段生成整型，不建议使用组合主键， 自增id只作为虚拟主键，不建议与业务数据处理有关联关系，如果把控不好，会有问题（案例：AUTO_INCREMENT主键字段不要与业务有关联关系）
索引使用标准
1、非唯一索引建议使用“idx_表缩写名称_字段缩写名称”进行命名。
2、唯一索引建议使用“uniq_表缩写名称_字段缩写名称”进行命名。
3、索引名称必须使用小写。
4、唯一键不和主键重复。每个业务实体表和关系表都应该至少有一个业务主键对应的唯一索引。
5、索引字段的顺序需要考虑字段值去重之后的个数，个数多的放在前面，就是数据分布。
6、使用EXPLAIN判断SQL语句是否合理使用索引，尽量避免extra列出现：Using File Sort，Using Temporary。
7、UPDATE、DELETE语句需要根据WHERE条件添加索引。
8、合理创建联合索引（避免冗余），(a,b,c) 相当于 (a) 、(a,b) 、(a,b,c)。
9、合理利用覆盖索引。比如SELECT email,uid FROM user_email WHERE uid=xx，如果uid不是主键，适当时候可以将索引添加为index(uid,email)，以获得性能提升。
约束设计标准
1、 主键的内容不能被修改。
2、外键约束一般不在数据库上创建，只表达一个逻辑的概念，由程序控制。
3、 禁用数据库外键
4、命名
a) 主键约束：默认PRIMARY；
b) unique约束：UK_<column_name>
c) check约束：CK_<column_name>
d) 外键约束：业务禁用
事务的处理标准
1、一个事务，处理的行数不能超过1000 rows/s
2、操作完后请及时commit，释放锁；
3、并发下，避免死锁，如果存在并发对相同对数据做DML，请按顺序操作，举例
# 有3条数据，主键是1，2，3，那么
Session 1:
update 数据的顺序是 1，2，3
Session 2:
update 数据的顺序是 3，2，1 或 2，1，3
在并发下，存在死锁的可能性，如购物车曾经出现过
Session 1，更新了 1，持有锁，将要更新2
Session 2，更新了 2，持有锁，将要更新1
#### Oops，死锁了。
分区表使用规范
原则上：禁止使用分区表！禁止使用分区表！禁止使用分区表！
1、分区表也是一个db特性，少一个特性，少一个功能bug的风险
2、其实分区表解决的是，单表大数据量，然后这些数据不太重要，需要定期drop partition清理，方便清理而已，真正带来查询效率的，是索引和数据访问方式
3、DBA无法做Online DDL，这个才是重点中的重点
如果一定要用遵循
1、单表大数据量且有一定的字段冗余以后都不会做DDL了
2、然后这些数据生命周期很短，不太重要，不需要归档，可以直接清理的，定期drop partition可以方便清理，如监控数据，告警数据，一些日志数据等
SQL语句标准
0、请避免 多于2表的join。
1、使用prepared statement，可以提供性能并且避免SQL注入。
2、SELECT语句只获取需要的字段，禁止使用SELECT * FROM语句，这是有效防止新增字段对应用逻辑的影响，还能减少对性能的影响；
3、INSERT语句必须显式的指明字段名称，不使用INSERT INTO table value()。
4、禁止在where子句中对字段施加函数，如to_date（add_time)>xxxxx,应改为:add_time >= unix_timestamp(date_add(str_to_date('20130227','%Y%m%d'),interval – 29 day))
5、UPDATE、DELETE语句不使用LIMIT 。以前我们使用的是MySQL 5.0，使用statment模式，所以有此规范，目前5.5，row和mixed模式不会出现，此规则去掉。
6、写到应用程序里的SQL语句，禁止一切DDL操作，如对这些权限有要求，必需与DBA协商同意方可使用
7、WHERE条件中必须使用合适的类型，避免MySQL进行隐式类型转化，如ISENDED=1，字段类型是tinyint，那么不能是ISENDED=‘1’。
8、避免在SQL语句进行数学运算或者函数运算，容易将业务逻辑和DB耦合在一起。
9、INSERT语句使用batch提交。
10、避免使用存储过程、触发器、函数等，容易将业务逻辑和DB耦合在一起，并且MySQL的存储过程、触发器、函数中存在一定的bug。
11、使用合理的SQL语句减少与数据库的交互次数。
12、不使用ORDER BY RAND()，使用其他方法替换。
13、建议使用合理的分页方式以提高分页的效率。
14、InnoDB表避免使用COUNT(*)操作，计数统计实时要求较强可以使用memcache或者redis，非实时统计可以使用单独统计表，定时更新。
15、不建议使用%前缀模糊查询，例如LIKE “%weibo”。
16、避免多余的排序。使用GROUP BY 时，默认会进行排序，当你不需要排序时，可以使用order by null,例如Select a.OwnerUserID,count(*) cnt from DP_MessageList a group by a.OwnerUserID order by null;
17、 新增排序要求：不鼓励在DB里排序，特别是只有1000行一下的，请在app server上排序，app server有上百台，而db仅仅个位数的服务器数量，排序都在db，会把db压垮的，特别是禁止上千行的排序在db这边。
18、 禁止使用 REPLACE INTO ；
19、禁止使用子查询，select col、col from table where id in （select col from table）这是禁止的；
20、batch size 大小不能超过1000，同时请根据业务QPS和记录长度来评估1000以内什么值合适，如where col in （）的值不能超过1000。参考：batch size标准
21、禁止使用 UUID()，USER()这样的MYSQL INSIDE函数对于复制来说是很危险的，会导致主备数据不一致，重要的是会严重影响mysql性能。
22、 如果应用使用的是长连接，应用必须具有自动重连的机制。但请避免每执行一个SQL去检查一次DB可用性；
使用replace带来的问题
1、Replace into 操作在唯一键重复情况下，是先尝试写入，检测到冲突则删除原记录，再写入新记录。该操作在二进制日志中只有一条 update 操作，将导致同一张表的 auto_increment 值主从不一致，主从切换后，极易致业务写入失败；
2、据 1 描述， 一次 Replace into 的写入操作最坏情况下有两次 DB 操作，操作负担重；
3、并发情况下，Replace into 因不同 session 获取锁的顺序问题，产生死锁的概率更高；
