共计 5827 个字符，预计需要花费 15 分钟才能阅读完成。
本标准旨在帮忙或领导 RD、QA、OP 等技术人员做出适宜线上业务的数据库设计。在数据库变更和解决流程、数据库表设计、SQL 编写等方面予以标准，从而为公司业务零碎稳固、衰弱地运行提供保障
设计规范
以下所有标准会依照【高危】、【强制】、【倡议】三个级别进行标注，恪守优先级从高到低
对于不满足【高危】和【强制】两个级别的设计，DBA 有权力强制打回要求批改
库名
1.【强制】库的名称必须管制在 32 个字符以内，相干模块的表名与表名之间尽量体现 join 的关系，如 user 表和 user_login 表
2.【强制】库的名称格局：业务零碎名称_子系统名，同一模块应用的库名尽量应用对立前缀
3.【强制】个别分库名称命名格局是库通配名_编号，编号从 0 开始递增，比方 wenda_001 以工夫进行分库的名称格局是“库通配名_工夫”
4.【强制】创立数据库时必须显式指定字符集，并且字符集只能是 utf8 或者 utf8mb4。创立数据库 SQL 举例：create database db1 default character set utf8;
表构造
1.【强制】表必须有主键，且设置 id 为自增主键
2.【强制】表禁止应用外键，如果要保障残缺下，应由程序端实现，外键使表之间互相耦合，影响 update、delete 等性能，有可能造成死锁，高并发环境下容易导致数据库性能瓶颈
3.【强制】表和列的名称必须管制在 32 个字符以内，表名只能应用字母、数字和下划线，一律小写。如表名过长能够采纳缩写等形式
4.【强制】创立表时必须显式指定字符集为 utf8 或 utf8mb4
5.【强制】创立表时必须显式指定表存储引擎类型，如无非凡需要，一律为 InnoDB。当须要应用除 InnoDB/MyISAM/Memory 以外的存储引擎时，必须通过 DBA 审核能力在生产环境中应用。因为 Innodb 表反对事务、行锁、宕机复原、MVCC 等关系型数据库重要个性，为业界应用最多的 MySQL 存储引擎。而这是其余大多数存储引擎不具备的，因而首推 InnoDB
6.【强制】建表必须有 comment，表级别和字段级别都要有 comment
7.【倡议】建表时对于主键：(1)强制要求主键为 id，类型为 int 或 bigint（为了当前延展性，这里要求新建表对立为 bigint），且为 auto_increment(2)标识表里每一行主体的字段不要设为主键，倡议设为其余字段如 user_id，order_id 等，并建设 unique key 索引。因为如果设为主键且主键值为随机插入，则会导致 innodb 外部 page 决裂和大量随机 I /O，性能降落
8.【倡议】外围表（如用户表，金钱相干的表）必须有行数据的创立工夫字段 create_time 和最初更新工夫字段 update_time，便于查问题
9.【倡议】表中所有字段必须都是 NOT NULL default 默认值 属性，业务能够依据须要定义 DEFAULT 值。因为应用 NULL 值会存在每一行都会占用额定存储空间、数据迁徙容易出错、聚合函数计算结果偏差以及索引生效等问题
10.【倡议】倡议对表里的 blob、text 等大字段，垂直拆分到其余表里，仅在须要读这些对象的时候才去 select
11.【倡议】反范式设计：把常常须要 join 查问的字段，在其余表里冗余一份。如 user_name 属性在 user_account，user_login_log 等表里冗余一份，缩小 join 查问
12.【强制】两头表用于保留两头后果集，名称必须以 tmp_结尾。备份表用于备份或抓取源表快照，名称必须以 bak_结尾。两头表和备份表定期清理
13.【强制】对于线上执行 DDL 变更，必须通过 DBA 审核，并由 DBA 在业务低峰期执行
列数据类型优化
1.【倡议】表中的自增列（auto_increment 属性），举荐应用 bigint 类型。因为无符号 int 存储范畴为 -2147483648~2147483647（大概 21 亿左右），溢出后会导致报错
2.【倡议】业务中选择性很少的状态 status、类型 type 等字段举荐应用 tinytint 或者 smallint 类型节俭存储空
3.【倡议】业务中 IP 地址字段举荐应用 int 类型，不举荐用 char(15)。因为 int 只占 4 字节，能够用如下函数互相转换，而 char(15)占用至多 15 字节。一旦表数据行数到了 1 亿，那么要多用 1.1G 存储空间。SQL：select inet_aton(‘192.168.2.12’); select inet_ntoa(3232236044); PHP: ip2long(‘192.168.2.12’); long2ip(3530427185);
4.【倡议】不举荐应用 enum，set。因为它们节约空间，且枚举值写死了，变更不不便。举荐应用 tinyint 或 smallint
5.【倡议】不举荐应用 blob，text 等类型。它们都比拟节约硬盘和内存空间。在加载表数据时，会读取大字段到内存里从而节约内存空间，影响零碎性能。倡议和 PM、RD 沟通，是否真的须要这么大字段
6.【倡议】存储金钱的字段，倡议用 int，程序端乘以 100 和除以 100 进行存取。或者用 decimal 类型，而不要用 double
7.【倡议】文本数据尽量用 varchar 存储。因为 varchar 是变长存储，比 char 更省空间。MySQL server 层规定一行所有文本最多存 65535 字节
8.【倡议】工夫类型尽量选取 datetime。而 timestamp 尽管占用空间少，然而有工夫范畴为 1970-01-01 00:00:01 到 2038-01-01 00:00:00 的问题
索引设计
1.【强制】InnoDB 表必须主键为 id int/bigint auto_increment, 且主键值禁止被更新
2.【倡议】惟一键以“uk_”或“uq_”结尾，一般索引以“idx_”结尾，一律应用小写格局，以字段的名称或缩写作为后缀
3.【强制】InnoDB 和 MyISAM 存储引擎表，索引类型必须为 BTREE；MEMORY 表能够依据须要抉择 HASH 或者 BTREE 类型索引
4.【强制】单个索引中每个索引记录的长度不能超过 64KB
5.【倡议】单个表上的索引个数不能超过 5 个
6.【倡议】在建设索引时，多思考建设联结索引，并把区分度最高的字段放在最后面。如列 userid 的区分度可由 select count(distinct userid)计算出来
7.【倡议】在多表 join 的 SQL 里，保障被驱动表的连贯列上有索引，这样 join 执行效率最高
8.【倡议】建表或加索引时，保障表里相互不存在冗余索引。对于 MySQL 来说，如果表里曾经存在 key(a,b)，则 key(a)为冗余索引，须要删除
分库分表、分区表
1.【强制】分区表的分区字段（partition-key）必须有索引，或者是组合索引的首列
2.【强制】单个分区表中的分区（包含子分区）个数不能超过 1024
3.【强制】上线前 RD 或者 DBA 必须指定分区表的创立、清理策略
4.【强制】拜访分区表的 SQL 必须蕴含分区键
5.【倡议】单个分区文件不超过 2G，总大小不超过 50G。倡议总分区数不超过 20 个
6.【强制】对于分区表执行 alter table 操作，必须在业务低峰期执行
7.【强制】采纳分库策略的，库的数量不能超过 1024
8.【强制】采纳分表策略的，表的数量不能超过 4096
9.【倡议】单个分表倡议不超过 500W 行，这样能力保证数据查问性能更佳
10.【倡议】程度分表尽量用取模形式，并预留出足够的 buffer，免得日后须要从新拆分和迁徙，日志、报表类数据倡议采纳日期进行分表
字符集
1.【强制】数据库自身库、表、列所有字符集必须保持一致，为 utf8 或 utf8mb4
2.【强制】前端程序字符集或者环境变量中的字符集，与数据库、表的字符集必须统一，对立为 utf8
SQL 编写
DML 语句
1.【强制】SELECT 语句必须指定具体字段名称，禁止写成 。因为 select 会将不该读的数据也从 MySQL 里读出来，造成网卡压力。且表字段一旦更新，但程序端没有来得及更新的话，零碎会报错
2.【强制】insert 语句指定具体字段名称，不要写成 insert into t1 values(…)，情理同上
3.【倡议】insert into…values(XX),(XX),(XX)…。这里 XX 的值不要超过 500 个。值过多尽管上线很很快，但会引起主从同步提早
4.【倡议】SELECT 语句不要应用 UNION，举荐应用 UNION ALL，并且 UNION 子句个数限度在 3 个以内。因为 union all 不须要去重，节俭数据库资源，进步性能
5.【倡议】in 值列表限度在 500 以内。例如 select… where userid in(….500 个以内…)，这么做是为了缩小底层扫描，加重数据库压力从而减速查问
6.【倡议】事务里批量更新数据须要管制数量，进行必要的 sleep，做到少量多次
7.【强制】事务波及的表必须全副是 innodb 表。否则一旦失败不会全副回滚，且易造成主从库同步中断
8.【强制】写入和事务发往主库，只读 SQL 发往从库，即程序端实现读写拆散
9.【强制】DML 语句必须有 where 条件，且应用索引查找
10.【强制】生产环境禁止应用 hint，如 sql_no_cache，force index，ignore key，straight join 等。因为 hint 是用来强制 SQL 依照某个执行打算来执行，但随着数据量变动咱们无奈保障本人当初的预判是正确的，咱们要尽量让 MySQL 优化器本人抉择执行打算
11.【强制】where 条件里等号左右字段类型必须统一，否则无奈利用索引
12.【倡议】SELECT|UPDATE|DELETE|REPLACE 要有 WHERE 子句，且 WHERE 子句的条件必须应用索引查找
13.【强制】生产数据库中强烈不举荐大表上产生全表扫描，但对于 100 行以下的动态表能够全表扫描。查问数据量不要超过表行数的 25%，否则不会利用索引
14.【强制】WHERE 子句中禁止只应用全含糊的 LIKE 条件进行查找，如果要应用 like，请应用 like‘xxxx%’的形式，必须有其余等值或范畴查问条件，否则无奈利用索引
15.【倡议】索引列不要应用函数或表达式，否则无奈利用索引。如 where length(name)=’Admin’ 或 where user_id+2=10023
16.【倡议】缩小应用 or 语句，可将 or 语句优化为 union，而后在各个 where 条件上建设索引。如 where a=1 or b= 2 优化为 where a=1… union …where b=2, key(a),key(b)
17.【倡议】分页查问，当 limit 终点较高时，可先用过滤条件进行过滤。如 select a,b,c from t1 limit 10000,20; 优化为: select a,b,c from t1 where id>10000 limit 20;
多表连贯
1.【强制】禁止跨 db 的 join 语句。因为这样能够缩小模块间耦合，为数据库拆分奠定坚实基础
2.【强制】禁止在业务的更新类 SQL 语句中应用 join，比方 update t1 join t2…
3.【倡议】不倡议应用子查问，倡议将子查问 SQL 拆开联合程序屡次查问，或应用 join 来代替子查问
4.【倡议】线上环境，多表 join 不要超过 3 个表
5.【倡议】多表连贯查问举荐应用别名，且 SELECT 列表中要用别名援用字段，数据库. 表格局，如 select a from db1.table1 alias1 where …
6.【倡议】在多表 join 中，尽量选取后果集较小的表作为驱动表，来 join 其余表
事务
1.【倡议】事务中 INSERT|UPDATE|DELETE|REPLACE 语句操作的行数管制在 1000 以内，以及 WHERE 子句中 IN 列表的传参个数管制在 500 以内
2.【倡议】批量操作数据时，须要管制事务处理间隔时间，进行必要的 sleep，个别倡议值 1 - 2 秒
3.【倡议】对于有 auto_increment 属性字段的表的插入操作，并发须要管制在 200 以内
4.【强制】程序设计必须思考“数据库事务隔离级别”带来的影响，包含脏读、不可反复读和幻读。线上倡议事务隔离级别为 repeatable-read
5.【倡议】事务里蕴含 SQL 不超过 5 个（领取业务除外）。因为过长的事务会导致锁数据较久，MySQL 外部缓存、连贯耗费过多等雪崩问题
6.【倡议】事务里更新语句尽量基于主键或 unique key，如 update … where id=XX; 否则会产生间隙锁，外部扩充锁定范畴，导致系统性能降落，产生死锁
7.【倡议】尽量把一些典型内部调用移出事务，如调用 webservice，拜访文件存储等，从而防止事务过长
8.【倡议】对于 MySQL 主从提早严格敏感的 select 语句，请开启事务强制拜访主库
排序和分组
1.【倡议】缩小应用 order by，和业务沟通能不排序就不排序，或将排序放到程序端去做。order by、group by、distinct 这些语句较为消耗 CPU，数据库的 CPU 资源是极其贵重的
2.【倡议】order by、group by、distinct 这些 SQL 尽量利用索引间接检索出排序好的数据。如 where a=1 order by 能够利用 key(a,b)
3.【倡议】蕴含了 order by、group by、distinct 这些查问的语句，where 条件过滤出来的后果集请放弃在 1000 行以内，否则 SQL 会很慢
线上禁止应用的 SQL 语句
1.【高危】禁用 update|delete t1 … where a=XX limit XX; 这种带 limit 的更新语句。如果是非 row 格局的 binlog 格局，会导致主从不统一，导致数据错乱。倡议加上 order by PK
2.【高危】禁止应用关联子查问，如 update t1 set … where name in(select name from user where…); 效率极其低下
3.【强制】禁用 procedure、function、trigger、views、event、外键束缚。因为他们耗费数据库资源，升高数据库实例可扩展性。举荐都在程序端实现
4.【倡议】禁用 insert into …on duplicate key update…、replace into 等语句，在高并发环境下，极容易导致死锁
5.【强制】禁止联表更新语句，如 update t1,t2 where t1.id=t2.id…
