MySQL数据库开发的36条原则(小结)

前言

这些原则都是经历过实战总结而成

每一条原则背后都是血淋淋的教训

这些原则主要是针对数据库开发人员，在开发过程中务必注意

一、核心原则

1.尽量不在数据库做运算

俗话说：别让脚趾头想事情，那是脑瓜子的职责

作为数据库开发人员，我们应该让数据库多做她所擅长的事情：

• 尽量不在数据库做运算
• 复杂运算移到程序端CPU
• 尽可能简单应用MYSQL

举例：

在mysql中尽量不要使用如：md5()、Order by Rand()等这类运算函数

2.尽量控制单表数据量

大家都知道单表数据量过大后会影响数据查询效率，严重情况下会导致整个库都卡住

一般情况下，按照一年内单表数据量预估：

• 纯INT不超过1000W
• 含CHAR不超过500W

同时要尽量做好合理的分表，使单表数据量不超载，常见的分表策略有：

• 通过USERID来分表（根据ID区间分表）：在金融行业应用较多，用户量大、用户特征明显
• 按DATE分表（按天、周、月分表）：在电信行业应用非常多，如用户上网记录表、用户短信表、话单表等
• 按AREA分表（省、市、区分表）
• 其他

分区表的适用场景主要有：

① 表非常大，无法全部存在内存，或者只在表的最后有热点数据，其他都是历史数据；

② 分区表的数据更易维护，可以对独立的分区进行独立的操作；

③ 分区表的数据可以分布在不同的机器上，从而高效使用资源；

④ 可以使用分区表来避免某些特殊的瓶颈；

⑤ 可以备份和恢复独立的分区。

但是使用分区表同样有一些限制，在使用的时候需要注意：

① 一个表最多只能有 1024 个分区；

② 5.1版本中，分区表表达式必须是整数， 5.5可以使用列分区；

③ 分区字段中如果有主键和唯一索引列，那么主键列和唯一列都必须包含进来；

④ 分区表中无法使用外键约束；

⑤ 需要对现有表的结构进行修改；

⑥ 所有分区都必须使用相同的存储引擎；

⑦ 分区函数中可以使用的函数和表达式会有一些限制；

⑧ 某些存储引擎不支持分区；

⑨ 对于 MyISAM 的分区表，不能使用 load index into cache；

⑩ 对于 MyISAM 表，使用分区表时需要打开更多的文件描述符。

3.尽量控制表字段数量

单表的字段数量也不能太多，根据业务场景进行优化调整，尽量调整表字段数少而精，这样有以下好处：

• IO高效
• 全表遍历
• 表修复快
• 提高并发
• alter table更快

那究竟单表多少字段合适呢？

按照单表1G体积，500W行数据量进行评估：

• 顺序读1G文件需N秒
• 单行不超过200Byte
• 单表不超50个纯INT字段
• 单表不超20个CHAR(10)字段

==>建议单表字段数上限控制在20~50个

4.平衡范式与冗余

数据库表结构的设计也讲究平衡，以往我们经常说要严格遵循三大范式，所以先来说说什么是范式：

第一范式：单个字段不可再分。唯一性。

第二范式：不存在非主属性只依赖部分主键。消除不完全依赖。

第三范式：消除传递依赖。

用一句话来总结范式和冗余：

冗余是以存储换取性能，

范式是以性能换取存储。

所以，一般在实际工作中冗余更受欢迎一些。

模型设计时，这两方面的具体的权衡，首先要以企业提供的计算能力和存储资源为基础。

其次，一般互联网行业中都根据Kimball模式实施数据仓库，建模也是以任务驱动的，因此冗余和范式的权衡符合任务需要。

例如，一份指标数据，必须在早上8点之前处理完成，但计算的时间窗口又很小，要尽可能减少指标的计算耗时，这时在计算过程中要尽可能减少多表关联，模型设计时需要做更多的冗余。

5.拒绝3B

数据库的并发就像城市交通，呈非线性增长

这就要求我们在做数据库开发的时候一定要注意高并发下的瓶颈，防止因高并发造成数据库瘫痪。

这里的拒绝3B是指：

• 大SQL（BIG SQL）：要减少
• 大事务（BIG Transaction）
• 大批量（BIG Batch）

二、字段类原则

1.用好数值字段类型

三类数值类型：

• 整型：TINYINT(1Byte)、TINYINT(1Byte)、SMALLINT(2B)、MEDIUMINT(3B)、INT(4B)、BIGINT(8B)
• 浮点型：FLOAT(4B)、DOUBLE(8B)
• DECIMAL(M,D)

以几个常见的例子来进行说明：

1）INT(1) VS INT(11)

很多人都分不清INT(1)和INT(11)的区别，想必大家也很好奇吧，其实1和11其实只是显示长度的却别而已，也就是不管int（x）x的值是什么值，存储数字的取值范围还是int本身数据类型的取值范围，x只是数据显示的长度而已。

2）BIGINT AUTO_INCREMENT

大家都知道，有符号int最大可以支持到约22亿，远远大于我们的需求和MySQL单表所能支持的性能上限。对于OLTP应用来说，单表的规模一般要保持在千万级别，不会达到22亿上限。如果要加大预留量，可以把主键改为改为无符号int，上限为42亿，这个预留量已经是非常的充足了。

使用bigint，会占用更大的磁盘和内存空间，内存空间毕竟有限，无效的占用会导致更多的数据换入换出，额外增加了IO的压力，对性能是不利的。

因此推荐自增主键使用int unsigned类型，但不建议使用bigint。

3）DECIMAL(N,0)

当采用DECIMAL数据类型的时候，一般小数位数不会是0，如果小数位数设置为0，那建议使用INT类型

2.将字符转化为数字

数字型VS字符串型索引有更多优势：

• 更高效
• 查询更快
• 占用空间更小

举例：用无符号INT存储IP，而非CHAR(15)

INT UNSIGNED

可以用INET_ATON()和INET_NTOA()来实现IP字符串和数值之间的转换

3.优先使用ENUM或SET

对于一些枚举型数据，我们推荐优先使用ENUM或SET，这样的场景适合：

1）字符串型

2）可能值已知且有限

存储方面：

1）ENUM占用1字节，转为数值运算

2）SET视节点定，最多占用8字节

3）比较时需要加‘单引号（即使是数值）

举例：

`sex` enum('F','M') COMMENT '性别'；

`c1` enum('0','1','2','3') COMMENT '审核'；

4.避免使用NULL字段

为什么在数据库表字段设计的时候尽量都加上NOT NULL DEFAULT ''，这里面不得不说用NULL字段的弊端：

很难进行查询优化

NULL列加索引，需要额外空间

含NULL复合索引无效

举例：

1）`a` char(32) DEFAULT NULL 【不推荐】

2）`b` int(10) NOT NULL 【不推荐】

3）`c` int(10) NOT NULL DEFAULT 0 【推荐】

5.少用并拆分TEXT/BLOB

TEXT类型处理性能远低于VARCHAR

• 强制生成硬盘临时表
• 浪费更多空间
• VARCHAR(65535)==>64K(注意UTF-8)

尽量不用TEXT/BLOB数据类型

如果业务需要必须用，建议拆分到单独的表

举例：

CREATE TABLE t1 (
  id INT NOT NULL AUTO_INCREMENT,
  data TEXT NOT NULL,
  PRIMARY KEY(id)
) ENGINE=InnoDB;