共计 9551 个字符，预计需要花费 24 分钟才能阅读完成。
在咱们对数据库技术方案设计的时候，咱们是否有本人的设计理念或者准则，还是更多的根据本人的直觉去设计，是否已经后悔线上产生过的一次低级故障，可能略微留神点就能够防止，是否想过怎么能力很好的防止，上面标准的价值正是咱们工作的查看清单，须要咱们一直从谬误中积攒无效教训来领导将来的工作。以下标准在大型互联网公司通过了充沛的验证，尤其实用于并发量大、数据量大的业务场景。先介绍的是平安标准，因为平安无小事，很多公司都已经因为本人的数据泄露导致用户的惨痛损失，所以将平安标准放到了第一位。
一、平安标准
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，确认无误能力执行更新语句，避免出现误删除
作者|修冶
点击立刻收费试用云产品 开启云上实际之旅！
原文链接
本文为阿里云原创内容，未经容许不得转载。