在咱们对数据库技术方案设计的时候，咱们是否有本人的设计理念或者准则，还是更多的根据本人的直觉去设计，是否已经后悔线上产生过的一次低级故障，可能略微留神点就能够防止，是否想过怎么能力很好的防止，上面标准的价值正是咱们工作的查看清单，须要咱们一直从谬误中积攒无效教训来领导将来的工作。以下标准在大型互联网公司通过了充沛的验证，尤其实用于并发量大、数据量大的业务场景。先介绍的是平安标准，因为平安无小事，很多公司都已经因为本人的数据泄露导致用户的惨痛损失，所以将平安标准放到了第一位。

一、平安标准

1.【强制】禁止在数据库中存储明文明码，需把明码加密后存储阐明：对于加密操作倡议由公司的中间件团队基于如mybatis的扩大，提供对立的加密算法及密钥治理，防止每个业务线独自开发一套，同时也与具体的业务进行理解耦

2.【强制】禁止在数据库中明文存储用户敏感信息，如手机号等阐明：对于手机号倡议公司搭建对立的手机号查问服务，防止在每个业务线独自存储

3.【强制】禁止开发间接给业务同学导出或者查问波及到用户敏感信息的数据，如须要需上级领导审批

4.【强制】波及到导出数据性能的操作，如蕴含敏感字段都需加密或脱敏

5.【强制】跟数据库交互波及的敏感数据操作都需有审计日志，必要时要做告警

6.【强制】对连贯数据库的IP需设置白名单性能，杜绝非法IP接入

7.【强制】对重要sql（如订单信息的查问）的拜访频率或次数要做历史趋势监控，及时发现异常行为

8.【举荐】线上连贯数据库的用户名、明码倡议定期进行更换

二、根底标准

1.【举荐】尽量不在数据库做运算，简单运算需移到业务利用里实现

2.【举荐】回绝大sql语句、回绝大事务、回绝大批量，可转化到业务端实现

阐明：大批量操作可能会造成重大的主从提早，binlog日志为row格局会产生大量的日志

3.【举荐】防止应用存储过程、触发器、函数等，容易造成业务逻辑与DB耦合

阐明：数据库善于存储与索引、要解放数据库CPU，将计算转移到服务层、也具备更好的扩展性4.【强制】数据表、数据字段必须退出中文正文

阐明：后续保护的同学看到后才分明表是干什么用的

5.【强制】不在数据库中存储图片、文件等大数据

阐明：大文件和图片须要储在文件系统

6.【举荐】对于程序连贯数据库账号，遵循权限最小准则

7.【举荐】数据库设计时，须要问下本人是否对当前的扩展性进行了思考

8.【举荐】利用 pt-query-digest 定期剖析slow query log并进行优化

9.【举荐】应用内网域名而不是ip连贯数据库

10.【举荐】如果数据量或数据增长在后期布局时就较大，那么在设计评审时就应退出分表策略

11.【举荐】要求所有研发SQL关键字全副是小写，每个词只容许有一个空格

三、命名标准

1.【强制】库名、表名、字段名要小写，下划线格调，不超过32个字符，必须见名知意，倡议应用名词而不是动词，词义与业务、产品线等相关联，禁止拼音英文混用

2.【强制】一般索引命名格局：idx_表名_索引字段名（如果以首个字段名为索引有多个，能够加上第二个字段名，太长能够思考缩写），惟一索引命名格局：uk_表名_索引字段名（索引名必须全副小写，长度太长能够利用缩写），主键索引命名：pk_ 字段名

3.【强制】库名、表名、字段名禁止应用MySQL保留字

4.【强制】长期库表名必须以tmp为前缀，并以日期为后缀

5.【强制】备份库表必须以bak为前缀，并以日期为后缀

6.【举荐】用HASH进行散表，表名后缀应用16进制数，下标从0开始

7.【举荐】按日期工夫分表需合乎YYYYMM[HH]格局

8.【举荐】散表如果应用md5（或者相似的hash算法）进行散表，表名后缀应用16进制，比方user_ff

9.【举荐】应用CRC32求余（或者相似的算术算法）进行散表，表名后缀应用数字，数字必须从0开始并等宽，比方散100张表，后缀从00-99

10.【举荐】应用工夫散表，表名后缀必须应用特定格局，比方按日散表user_20110209、按月散表user_201102

11.【强制】表白是与否概念的字段，应用 is _ xxx 的形式进行命名

四、库设计规范

1.【举荐】数据库应用InnoDB存储引擎

阐明：反对事务、行级锁、并发性能更好、CPU及内存缓存页优化使得资源利用率更高

2.【举荐】数据库和表的字符集对立应用UTF8

阐明：utf8号称万国码，其无需转码、无乱码危险且节俭空间。若是有字段须要存储emoji表情之类的，则将表或字段设置成utf8mb4，utf8mb4向下兼容utf8。

3.【举荐】不同业务，应用不同的数据库，防止相互影响

4.【强制】所有线上业务库均必须搭建MHA高可用架构，防止单点问题

五、表设计规范

1.【举荐】建表标准示例

CREATE TABLE `student_info` (    `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',    `stu_name` varchar(10) NOT NULL DEFAULT '' COMMENT '姓名',    `stu_score` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '总分',    `stu_num` int(11) NOT NULL COMMENT '学号',    `gmt_create` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创立工夫',    `gmt_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新工夫',    `status` tinyint(4) DEFAULT '1' COMMENT '1代表记录无效，0代表记录有效',      PRIMARY KEY (`id`),      UNIQUE KEY `uk_student_info_stu_num` (`stu_num`) USING BTREE,    KEY `idx_student_info_stu_name` (`stu_name`) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生信息表';

2.【强制】禁止应用外键，如果有外键完整性束缚，须要利用程序控制

3.【强制】每个Innodb 表必须有一个主键

阐明：Innodb 是一种索引组织表，其数据存储的逻辑程序和索引的程序是雷同的。每张表能够有多个索引，但表的存储程序只能有一种，Innodb 是依照主键索引的程序来组织表的，因而不要应用更新频繁的列如UUID、MD5、HASH和字符串列作为主键，这些列无奈保证数据的程序增长，主键倡议应用自增ID 值。

4.【举荐】单表列数目最好小于50

5.【强制】禁止应用分区表

阐明：分区表在物理上体现为多个文件，在逻辑上体现为一个表，审慎抉择分区键，跨分区查问效率可能更低，倡议采纳物理分表的形式治理大数据

6.【举荐】拆分大字段和拜访频率低的字段，拆散冷热数据

7.【举荐】采纳适合的分库分表策略，例如千库十表、十库百表等（倡议表大小管制在2G）

8.【举荐】单表不超过50个int字段；不超过20个char字段，不超过2个text字段

9.【举荐】表默认设置创立工夫戳和更改工夫戳字段

10.【举荐】日志类型的表能够思考按创立工夫程度切割，定期归档历史数据

11.【强制】禁止应用order by rand()

阐明：order by rand()会为表减少一个伪列，而后用rand()函数为每一行数据计算出rand()值，基于该行排序，这通常都会生成磁盘上的长期表，因而效率非常低。

12.【参考】能够联合应用hash、range、lookup table进行散表

13.【举荐】每张表数据量倡议管制在500w以下，超过500w能够应用历史数据归档或分库分表来实现（500万行并不是MySQL数据库的限度。过大对于批改表构造，备份，复原都会有很大问题。MySQL没有对存储有限度，取决于存储设置和文件系统）

14.【强制】禁止在表中建设预留字段

阐明：预留字段的命名很难做到见名识义，预留字段无奈确认存储的数据类型，所以无奈抉择适合的类型；对预留字段类型的批改，会对表进行锁定

六、字段设计规范

1.【强制】必须把字段定义为NOT NULL并且提供默认值

阐明：NULL字段很难查问优化，NULL字段的索引须要额定空间，NULL字段的复合索引有效

2.【强制】禁止应用ENUM，可应用TINYINT代替

3.【强制】禁止应用TEXT、BLOB类型(如果表的记录数在万级以下能够思考)

4.【强制】必须应用varchar(20)存储手机号

5.【强制】禁止应用小数存储国币、应用“分”作为单位，这样数据库里就是整数了

6.【强制】用DECIMAL代替FLOAT和DOUBLE存储准确浮点数

7.【举荐】应用UNSIGNED存储非负整数

阐明：同样的字节数，存储的数值范畴更大

8.【举荐】倡议应用INT UNSIGNED存储IPV4

阐明：用UNSINGED INT存储IP地址占用4字节，CHAR(15)则占用15字节。另外，计算机解决整数类型比字符串类型快。应用INT UNSIGNED而不是CHAR(15)来存储IPV4地址，通过MySQL函数inet_ntoa和inet_aton来进行转化。IPv6地址目前没有转化函数，须要应用DECIMAL或两个BIGINT来存储。例如:SELECT INET_ATON('192.168.172.3'); 3232279555 SELECT INET_NTOA(3232279555); 192.168.172.3

9.【举荐】字段长度尽量按理论须要进行调配，不要随便调配一个很大的容量

10.【举荐】外围表字段数量尽可能地少，有大字段要思考拆分

11.【举荐】适当思考一些反范式的表设计，减少冗余字段，缩小JOIN

12.【举荐】资金字段思考对立*100解决成整型，防止应用decimal浮点类型存储

13.【举荐】应用VARBINARY存储大小写敏感的变长字符串或二进制内容阐明：VARBINARY默认辨别大小写，没有字符集概念，速度快

14.【参考】INT类型固定占用4字节存储阐明：INT(4)仅代表显示字符宽度为4位，不代表存储长度。数值类型括号前面的数字只是示意宽度而跟存储范畴没有关系，比方INT(3)默认显示3位，空格补齐，超出时失常显示，Python、Java客户端等不具备这个性能

15.【参考】辨别应用DATETIME和TIMESTAMP

阐明：存储年应用YEAR类型、存储日期应用DATE类型、存储工夫(准确到秒)倡议应用TIMESTAMP类型。DATETIME和TIMESTAMP都是准确到秒，优先选择TIMESTAMP，因为TIMESTAMP只有4个字节，而DATETIME8个字节，同时TIMESTAMP具备主动赋值以及⾃自动更新的个性。补充：如何应用TIMESTAMP的主动赋值属性?主动初始化，而且自动更新：column1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATECURRENT_TIMESTAMP 只是主动初始化：column1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP 自动更新，初始化的值为0：column1 TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP 初始化的值为0：column1 TIMESTAMP DEFAULT 0

16.【举荐】将大字段、拜访频率低的字段拆分到独自的表中存储，拆散冷热数据

阐明：有利于无效利用缓存，防⽌读入无用的冷数据，较少磁盘IO，同时保障热数据常驻内存提⾼高缓存命中率

17.【参考】VARCHAR(N)，N示意的是字符数不是字节数，比方VARCHAR(255)，能够最大可存储255个汉字，须要依据理论的宽度来抉择N

18.【参考】VARCHAR(N)，N尽可能小，因为MySQL一个表中所有的VARCHAR字段最大长度是65535个字节，进行排序和创立长期表一类的内存操作时，会应用N的长度申请内存

19.【举荐】VARCHAR(N)，N>5000时，应用BLOB类型

20.【举荐】应用短数据类型，比方取值范畴为0~80时，应用TINYINT UNSIGNED

21.【强制】存储状态，性别等，用TINYINT

22.【强制】所有存储雷同数据的列名和列类型必须统一（在多个表中的字段如user_id，它们类型必须统一）

23.【举荐】优先选择合乎存储须要的最小数据类型

24.【举荐】如果存储的字符串长度简直相等，应用 char 定长字符串类型

七、索引设计规范

1.【举荐】单表索引倡议管制在5个以内

阐明：索引能够减少查问效率，但同样也会升高插入和更新的效率，甚至有些状况下会升高查问效率，所以不是越多越好

2.【强制】禁止在更新非常频繁，区分度不高的属性上建设索引

3.【强制】建设组合索引必须把区分度高的字段放在后面

4.【举荐】对字符串应用索引，如果字符串定义长度超过128的，能够思考前缀索引

5.【强制】表必须有主键，并且是auto_increment及not null的，依据表的理论状况定义无符号的tinyint,smallint,int,bigint

6.【强制】禁止更新频繁的列作为主键

7.【强制】禁止字符串列作为主键

8.【强制】禁止UUID MD5 HASH这些作为主键(数值太离散了)

9.【举荐】默认应用非空的惟一键作为主键

10.【举荐】主键倡议抉择自增或发号器

11.【举荐】外围SQL优先思考笼罩索引

12.【参考】防止冗余和反复索引

13.【参考】索引要综合评估数据密度和散布以及思考查问和更新比例

14.【强制】不在索引列进行数学运算和函数运算

15.【举荐】研发要常常应用explain，如果发现索引选择性差，必须要学会应用hint

16.【举荐】能应用惟一索引就要应用惟一索引，进步查问效率

17.【举荐】多条字段反复的语句，要批改语句条件字段的程序，为其建设一条联结索引，缩小索引数量

18.【强制】索引字段要保障不为NULL，思考default value进去。NULL也是占空间，而且NULL十分影响索引的查问效率

19.【强制】新建的惟一索引不能和主键反复

20.【举荐】尽量不应用外键、外键用来爱护参照完整性，可在业务端实现阐明：防止对父表和子表的操作会相互影响，升高可用性

21.【强制】字符串不应做主键

22.【强制】表必须有无符号int型自增主键，对应表中id字段

阐明：必须得有主键的起因：采纳RBR模式复制，无主键的表删除，会导致备库夯住 ；应用自增的起因：数据写入能够进步插入性能，防止page决裂，缩小表碎片

23.【举荐】对长度过长的VARCHAR字段建设索引时，增加crc32或者MD5 Hash字段，对Hash字段建设索引

阐明：上面的表减少一列url_crc32，而后对url_crc32建设索引，缩小索引字段的长度，提高效率CREATE TABLE url( ... url VARCHAR(255) NOT NULL DEFAULT 0, url_crc32 INT UNSIGNED NOT NULL DEFAULT 0, ... index idx_url(url_crc32) ）

24.【举荐】WHERE条件中的非等值条件（IN、BETWEEN、<、<=、>、>=）会导致前面的条件应用不了索引

25.【举荐】索引字段的程序须要思考字段值去重之后的个数，个数多的放在后面

26.【举荐】ORDER BY，GROUP BY，DISTINCT的字段须要增加在索引的前面

27.【参考】正当创立联结索引（防止冗余），如(a,b,c) 相当于 (a) 、(a,b) 、(a,b,c)

28.【举荐】复合索引中的字段数倡议不超过5个

29.【强制】不在选择性低的列上建设索引，例如"性别", "状态"， "类型"

30.【举荐】对于独自条件如果走不了索引，能够应用force –index强制指定索引

31.【强制】禁止给表中的每一列都建设独自的索引

32.【举荐】在varchar字段上建设索引时，必须指定索引长度，没必要对全字段建设索引，依据理论文本区分度决定索引长度即可

八、SQL应用标准

1.【强制】禁止应用SELECT *，只获取必要的字段，须要显示阐明列属性

阐明：按需获取能够缩小网络带宽耗费，能无效利用笼罩索引，表构造变更对程序根本无影响。

2.【强制】禁止应用INSERT INTO t_xxx VALUES(xxx)，必须显示指定插入的列属性

3.【强制】WHERE条件中必须应用适合的类型，防止MySQL进行隐式类型转化

阐明：因为MySQL进行隐式类型转化之后，可能会将索引字段类型转化成=号左边值的类型，导致应用不到索引，起因和防止在索引字段中应用函数是相似的，例子 select uid from t_user where phone=15855550101（phone为 varchat 类型，此时查问中应用数字查问，会导致索引生效）

4.【强制】禁止在WHERE条件的属性上应用函数或者表达式

5.【强制】禁止负向查问，以及%结尾的含糊查问

6.【强制】应用程序必须捕捉SQL异样，并有相应解决

7.【举荐】sql语句尽可能简略、大的sql想方法拆成小的sql语句

阐明：简略的SQL容易应用到MySQL的querycache、缩小锁表工夫特地是MyISAM、能够应用多核cpu

8.【举荐】事务要简略，整个事务的工夫长度不要太长

9.【强制】防止在数据库中进行数学运算或者函数运算(MySQL不善于数学运算和逻辑判断，也容易将业务逻辑和DB耦合在一起)

10.【举荐】sql中应用到OR的改写为用IN() (or的效率没有in的效率高)

11.【参考】SQL语句中IN蕴含的值不应过多，外面数字的个数倡议管制在1000个以内

12.【举荐】limit分页留神效率。Limit越大，效率越低。能够改写limit

阐明：改写例子：1）改写办法一提早回表写法 select xx,xx from t t1, (select id from t where .... limit 10000,10) t2 where t1.id = t2.id2）改写办法二select id from t limit 10000, 10; 应该改为 => select id from t where id > 10000 limit 10;

13.【举荐】尽量应用union all代替union

14.【参考】防止应用大表JOIN

15.【举荐】对数据的更新要打散后批量更新，不要一次更新太多数据

16.【举荐】应用正当的SQL语句缩小与数据库的交互次数

17.【参考】留神使用性能剖析工具 Sql explain / showprofile / mysqlsla

18.【举荐】能不必NOT IN就不必NOT IN，坑太多了，会把空和NULL给查出来

19.【举荐】对于分页查问，程序里倡议正当应用分页来提高效率，limit、offset较大要配合子查问应用

20.【强制】禁止在数据库中跑大查问

21.【强制】禁止单条SQL语句同时更新多个表

22.【举荐】统计表中记录数时应用COUNT(*)，而不是COUNT(primary_key)和COUNT(1)

阐明：count( * ) 会统计值为 NULL 的行，而 count( 列名 ) 不会统计此列为 NULL 值的行

23.【举荐】INSERT语句应用batch提交（INSERT INTO tableVALUES(),(),()……），values的个数不应过多

24.【举荐】获取大量数据时，倡议分批次获取数据，每次获取数据少于2000条，后果集应小于1M

25.【举荐】在做开发时倡议应用数据库框架(如mybatis)或prepared statement，能够晋升性能并防止SQL注入

26.【强制】禁止跨库查问（为数据迁徙和分库分表留出余地，升高耦合度，升高危险）

27.【举荐】尽量避免应用子查问，能够把子查问优化为join操作（子查问的后果集无奈应用索引，子查问会产生长期表操作，如果子查问数据量大会影响效率，耗费过多的CPU及IO资源）

28.【强制】超过三个表禁止 join。（须要 join 的字段，数据类型必须相对统一；多表关联查问时，保障被关联的字段须要有索引。即便双表 join 也要留神表索引、SQL 性能。）

29.【举荐】SQL 性能优化的指标：至多要达到 range 级别，要求是 ref 级别，如果能够是 consts最好

30.【举荐】尽量不要应用物理删除（即间接删除，如果要删除的话提前做好备份），而是应用逻辑删除，应用字段delete_flag做逻辑删除，类型为tinyint，0示意未删除，1示意已删除

31.【强制】在代码中写分页查问逻辑时，若 count 为 0 应间接返回，防止执行前面的分页语句

32.【强制】程序连贯不同的数据库要应用不同的账号

33.【举荐】应用 ISNULL()来判断是否为 NULL 值

九、行为规范

1.【强制】禁止应用应用程序配置文件内的帐号手工拜访线上数据库

2.【强制】禁止非DBA对线上数据库进行写操作，批改线上数据须要提交工单，由DBA执行，提交的SQL语句必须通过测试

3.【强制】禁止在线上做数据库压力测试

4.【强制】禁止从测试、开发环境直连线上数据库

5.【强制】禁止在主库进行后盾统计操作，防止影响业务，能够在离线从库上执行后盾统计

十、流程标准

1.【强制】所有的建表操作须要提前告知该表波及的查问sql

2.【强制】所有的建表须要确定建设哪些索引后才能够建表上线

3.【强制】所有的改表构造、加索引操作都须要将波及到所改表的查问sql收回来告知DBA等相干人员

4.【强制】在建新表加字段之前，要求至多要提前3天邮件进去，给dba们评估、优化和审核的工夫

5.【强制】批量导入、导出数据须要DBA进行审查，并在执行过程中察看服务

6.【强制】禁止有super权限的应用程序账号存在

7.【强制】推广流动或上线新性能必须提前告诉DBA进行流量评估

8.【强制】不在业务高峰期批量更新、查询数据库

9.【强制】隔离线上线下环境（开发测试程序禁止拜访线上数据库）

10.【强制】在对大表做表构造变更时，如批改字段属性会造成锁表，并会造成从库提早，从而影响线上业务，必须在凌晨后业务低峰期执行，另对立用工具pt-online-schema-change防止锁表且升高提早执行工夫

11.【强制】外围业务数据库变更需在凌晨执行

12.【举荐】汇总库开启Audit审计日志性能，呈现问题时方可追溯

13.【强制】给业务方开权限时，明码要用MD5加密，至多16位。权限如没有特殊要求，均为select查问权限，并做库表级限度

14.【举荐】如果呈现业务部门人为误操作导致数据失落，须要复原数据，请在第一工夫告诉DBA，并提供精确工夫，误操作语句等重要线索。

15.【强制】批量更新数据，如update,delete 操作，须要DBA进行审查，并在执行过程中察看服务

16.【强制】业务部门程序呈现bug等影响数据库服务的问题，请及时告诉DBA便于保护服务稳固

17.【强制】线上数据库的变更操作必须提供对应的回滚计划

18.【强制】批量荡涤数据，须要开发和DBA独特进行审查，应避开业务高峰期时段执行，并在执行过程中察看服务状态

19.【强制】数据勘误如删除和批改记录时，要先 select ，确认无误能力执行更新语句，避免出现误删除

作者｜修冶

点击立刻收费试用云产品 开启云上实际之旅！

原文链接

本文为阿里云原创内容，未经容许不得转载。