数据库设计和研发规范

MLXG

一.  基础规范
所有表都需要添加注释
使用comment从句添加表和列的备注，从一开始就做好数据字典维护。

单表数据量建议控制在500万以内
2 f2 O( b, l4 t, z; t过大会给备份和恢复带来困难，可用历史数据归档（用于日志数据），分库分表（用于业务数据）等手段来控制数据量大小。
- b% C  X! l8 b
5 _1 z! o) t+ E" \' I8 ]2 m不在数据库中存储图、文件等二进制数据
图或文件IO操作耗时，通常存储于文件服务器，数据库只存储文件地址信息。

尽量冷热数据分离，减小表的宽度
: o! S6 H$ x# ?2 y0 @) d把不常用且占宽大的字段从热表中分离可独立存储，每一行数据大小不超过65535字节。
5 F5 ]. S+ F/ }
2 C2 V7 k! i! H5 B, ]禁止测试、开发环境直连线上数据库

二、命名规范

库名、表名、字段名必须使用小写字母，并采用下划线分割
) U2 |8 C$ G. O) Z, t, m: Z6 `3 |MySQL配置参数lower_case_table_names=1，即库表名以小写存储，大小写不敏感。如果是0，则库表名以实际情况存储，大小写敏感；如果是2，以实际情况存储，但以小写比较。如果大小写混合使用，可能存在abc，Abc，ABC等多个表共存，容易导致混乱。
# s" f; p+ J3 r0 y1 g1 U6 g
库名、表名、字段名不要超过32个字符，需见名知意易于辨识
) e0 J6 K2 ^! k* g! M库名、表名、字段名支持最多64个字符，但为了减少传输量，禁止超过32个字符。

库名、表名、字段名禁止使用MySQL保留字
- H+ Q' F7 ~* j" Q! P* A5 J" Q! P当库名、表名、字段名等含有保留字时，SQL语句必须用反引号引用属性名称，这将使SQL语句书写、SHELL脚本中变量的转义变得非常复杂。
( K$ u! ?7 g/ q; {
备份库、表必须以bak为前缀，并以日期为后缀
形如：bak_user_account_20190313
& o2 c* r6 T8 b( f7 i4 k
索引的命名
0 Z  {2 C# [& w. l6 j$ k' D" F主键索引名为 pk_字段名；唯一索引名为 uk _字段名；普通索引名则为 idx_字段名。
9 o1 W: J$ x/ }9 f7 Y

三、字段设计规范
" D+ H* w% |7 C! ~) Z- N# ]7 b5 O5 |
0 g0 b% c- P& l' s优先选择符合存储需要的最小的数据类型
列的字段类型越大，建立索引占据的空间就越大，使得一个页中的索引越少，IO次数增加，影响性能，能用int的就不用char或者varchar，能用tinyint的就不用int。状态status、类型type等字段推荐使用tinytint或者smallint类型节省存储空间；使用 UNSIGNED 存储非负数值；使用tinyint来代替 enum和boolean；存储 ip 最好用 int存储而非 char(15)。

( ?! ^. Y, \7 t3 o1 ^2 P; r$ W不推荐使用blob，text等类型
2 b  d1 S. M3 K5 r0 jblob，text是为了存储极大的字符串而设计的数据类型，采用二进制与字符串方式存储，该数据类型不能设置默认值、不便于排序、不便于建立索引，varchar的性能会比text高很多，如果非要使用，建议将这种数据分离到单独的拓展表中。

; \8 g7 ]; p. ?3 j2 C禁止使用字符串来存储日期型数据
一是无法使用日期函数计算比较，二是因为字符串存储要占更多的内存空间，datetime占8字节，timestamp是以int存储，占4字节。

; \8 [& o$ s' ~" w, [用DECIMAL代替FLOAT和DOUBLE存储精确浮点数
9 K) `- @: M# e4 o: J3 k$ m# D0 w$ [4 xDecimal类型为精准浮点数，float 和 double 都有精度损失的问题，很可能在值的比较时，得到不正确的结果。如果存储的数据范围超过 decimal 的范围，建议将数据拆成整数和小数分开存储。
) b( e1 f, w* R
. ]# {; `1 m7 o+ Y2 O1 q0 L必须把字段定义为NOT NULL并设默认值
- y: X, T- C% q一是NULL需要更多的存储空间，二是NULL的列可能索引实效，三是NULL只能采用IS NULL或者IS NOT NULL，而在=/!=/in/not in时有大坑。

使用varchar(20)存储手机号，不要使用整数
' h9 @# U7 J! \. f) _0 b一是牵扯到国家代号，可能出现+/-/()等字符，例如+86，二是varchar可以模糊查询，例如like ‘138%’
& p, F) R1 [/ F
根据业务区分使用char/varchar
字段长度固定，或者长度近似的业务场景，适合使用char，能够减少碎片，查询性能高
% v  n3 t8 y% J# b5 ~/ o
+ X7 o1 @+ q8 k8 D' z字段长度相差较大，或者更新较少的业务场景，适合使用varchar。

% L7 ~7 _: l8 c$ ?1 y% K禁止在数据库中存储明文密码，把密码加密后存储
% H3 k  B! N6 X# w1 d尽量不使用外键
) l7 q% s" i4 s2 E" {# M建议在应用层实现外键的逻辑， 外键与级联更新不适合高并发场景，降低插入性能，大并发下容易产生死锁。
  F" v( q( ?; p. @
2 j% ]9 K( B1 H0 [# v. D( G9 D- N核心表必须有行数据的创建时间和最后更新时间
  }8 j. B9 r! c. m. s, A$ d) O核心表（如用户表，金钱相关的表）必须有行数据的创建时间字段create_time和最后更新时间字段update_time，便于查问题。

9 s: f3 v# g3 Z+ i- M
四、索引设计规范
' B# s, F9 d3 }7 ]
, |5 E6 L1 q2 s; x# e$ fMySQL的查询速度依赖良好的索引设计，因此索引对于高性能至关重要，合理的索引会加快查询速度，不合理的索引会降低速度。

4 n9 Z/ _! ?5 x/ z索引其实就是一种数据结构（哈希表、树等），不同类型的索引有着不同的数据结构和功能。

4 B* W, T& i$ r8 s; O6 F# D索引的作用是加速查询速度，维护数据的约束性（完整性、一致性）。对于加速查询，使用索引不一定是最好的选择，小表就直接全表扫描，中到大表就建索引，超大表就分区分表，要做好索引带来的好处和维护索引的成本之间的权衡。

- Q2 _/ B. x$ y单表的索引数建议不超过 5 个，单个索引中的字段数建议不超过 5 个
# V" \; j7 C5 B0 f1 E7 `, Z太多就起不到过滤作用了，索引也占空间，管理也耗资源。
# X, p4 ?! e1 ?0 r2 @0 w
$ A, }) v- v: |- z" ^对字符串使用前缀索引，前缀索引长度不超过8个字符
0 \# u" c; ?7 R; U不要索引blob/text等字段，不要索引大型字段，这样做会让索引占用太多的存储空间。

前缀索引就是对文本的前几个字符建立索引，前缀索引能有效减小索引文件的大小，提高索引的速度。但是前缀索引也有它的坏处：MySQL 不能在 ORDER BY 或 GROUP BY 中使用前缀索引，也不能把它们用作覆盖索引(Covering Index)
! E; a9 K, U: P5 f1 r# A) ?. L5 v7 i
2 Q7 u' T% H( a  Y( i) S) l重要的SQL必须被索引，核心SQL优先考虑覆盖索引
) \  o$ ~6 {# z3 a8 `  T: @UPDATE、DELETE 语句的 WHERE 条件列；ORDER BY、GROUP BY、DISTINCT 的字段；多表 JOIN 的字段。覆盖索引可以避免Innodb表进行索引的二次查询，把随机IO变成顺序IO，加快查询效率。

联合索引区分度最大的字段放在前面
. y2 {+ E7 G, @选择筛选性更优的字段放在最前面，比如userid，carid等，type，status等筛选性一般不建议放在最前面。索引根据左前缀原则，当建立一个联合索引(a,b,c)，则查询条件里面只有包含(a)或(a,b)或(a,b,c)的时候才能走索引，(a,c)作为条件的时候只能使用到a列索引，所以这个时候要确定a的返回列一定不能太多，不然语句设计就不合理，(b,c)则不能走索引。
  N1 `* w. o0 L& R
业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引
不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明
7 A& i) k2 [0 \& C" }
显的； 另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生。

InnoDB 和 MyISAM 存储引擎表，索引类型必须为BTREE
$ A9 [3 F- p4 `0 `MEMORY表可以根据需要选择 HASH 或者 BTREE 类型索引。

# j2 l8 p0 c: V1 F% r. u( LMYSQL 中索引的限制
2 Y4 p2 g3 p& E# |/ d1 dMYISAM 存储引擎索引长度的总和不能超过 1000 字节

BLOB 和 TEXT 类型的列只能创建前缀索引
- }+ a7 y2 c" p' E& O
4 a! u- L" G9 b9 n7 h) ?使用不等于 (!= 或者 <>) 的时候, MYSQL 无法使用索引

过滤字段使用函数运算 (如 abs (column)) 后, MYSQL无法使用索引

join语句中join条件字段类型不一致的时候MYSQL无法使用索引
9 F% U7 ?5 A2 z9 H3 V" D5 ?/ W  Q
4 [# |  }7 ^- M" e/ Z使用 LIKE 操作的时候如果条件以通配符开始 (如 ‘%abc…’)时, MYSQL无法使用索引

! Y! _2 r  W6 s( c使用非等值查询的时候, MYSQL无法使用 Hash 索引。

9 ]& L2 Q' L# ]5 [
五、SQL查询规范
5 Y. ?  F  J- S! e1 w
$ [2 Y( G0 l$ @) x" L按需索取拒绝 select *
无法索引覆盖，回表操作，IO增加；额外的内存负担，大量冷数据灌入innodb_buffer_pool_size，降低查询命中率；额外的网络传输开销。
0 q( c" S4 |# l0 Q1 |6 H
超过三个表禁止 join
1 A5 |8 H0 j! R7 ?/ V$ C: d8 ~需要 join 的字段，数据类型必须绝对一致；多表关联查询时，保证被关联的字段有索引。

涉及到复杂sql时，须用explain检验索引设计
对应同一列进行 or 判断时，使用 in 代替 or
in 的值不要超过 500 个， in 操作可以更有效的利用索引，or 大多数情况下很少能利用到索引。
% N* o% G0 X9 P8 r2 O  {
( `- ]1 \* |- nin 操作能避免则避免
若实在避免不了，需要仔细评估 in 后边的集合元素数量，控制在 1000 个之内。

* J6 M$ h, ?4 Q5 ~& mWHERE从句中禁止对列进行函数转换和计算
2 T1 S8 ^1 h  O% t8 ^对列进行函数转换或计算时会导致无法使用索引。

: `/ p3 t2 \8 L+ _7 e8 M# Q9 tcount(列名)或 count(常量) 替代不了 count(*)
count(*)是 SQL92 定义的标准统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 无，
5 M8 P* |! |8 V* ^- c
count(*)会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行。
+ h: H8 K* R0 v6 T2 w; h
6 s& t4 s. `) t不要使用UNION，推荐使用UNION ALL
因为union all不需要去重，节省数据库资源，提高性能，另外子句个数限制在5个以内。

' K# @0 t. p' U: b  K( g禁止使用 order by rand() 进行随机排序
3 U; ?: ^9 r4 K/ w会把表中所有符合条件的数据装载到内存中，然后在内存中对所有数据根据随机生成的值进行排序，并且可能会对每一行都生成一个随机值，如果满足条件的数据集非常大，就会消耗大量的 CPU 和 IO 及内存资源，推荐在程序中获取一个随机值，然后从数据库中获取数据的方式。
2 J/ w9 _0 u9 Z# J, [+ p" ^
' t& s/ B: k: ^, Q! u5 _" a不得使用外键与级联，一切外键概念必须在应用层解决
以学生和成绩的关系为例，学生表中的 student_id是主键，那么成绩表中的 student_id则为外键。如果更新学生表中的 student_id，同时触发成绩表中的 student_id 更新， 即为级联更新。外键与级联更新适用于单机低并发，不适合分布式、高并发集群； 级联更新是强阻塞，存在数据库更新风暴的风险；而且外键影响数据库的插入速度。

使用合理的分页方式以提高分页效率
不推荐 SELECT * FROM table ORDER BY TIME DESC LIMIT 10000，10;

原因：会导致大量的io，因为MySQL使用的是提前读取策略
9 m) J$ M7 j  @0 w6 L7 x& F3 \
推荐：SELECT * FROM table WHERE TIME < last_TIME ORDER BY TIME DESC LIMIT 10.
, Q9 a4 Y5 ]  `* A0 {) C8 Z
SELECT * FROM table inner JOIN (SELECT id FROM table ORDER BY TIME LIMIT 10000，10) as t USING(id)

/ e) S& D: H/ }5 b0 Z- L$ I
0 h( J5 Q2 |7 n" I! f

hunterccc

不同类型的索引有着不同的数据结构和功能

ssdgh

要优先选择符合存储需要的最小的数据类型

nuiga

学习了  感谢分享