MySQL 开发规范

核心原则

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

SQL 不要使用 md5(), Order by Rand() 等这类运算函数，将复杂运算转移到程序端 CPU。

2. 控制单表数据量

单表数据量过大后会影响数据查询效率，严重情况下会导致整个库都卡住。一般情况下，按照一年内单表数据量预估：单表中纯 INT 不超过 10M 条元组，含 Char 不超过 5M 条元组。

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

• 通过 UserId 的 id 区间进行分表，常用于用户量大、用户特征明显的场景，例如：金融行业等；
• 通过 Date 字段的天、周、月分表，常用于时间计算频繁场景，例如：用户上网记录表、用户短信表、话费表等；
• 通过区域的省、市、区分表；
• 其他策略；

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

1. 表非常大，无法全部存在内存，或者只在表的最后有热点数据，其他都是历史数据；
2. 分区表的数据更易维护，可以对独立的分区进行独立的操作；
3. 分区表的数据可以分布在不同的机器上，从而高效使用资源；
4. 可以使用分区表来避免某些特殊的瓶颈；
5. 可以备份和恢复独立的分区；

使用分区表时，需要注意一些相关的限制条件：

1. 一个表最多只能有 1024 个分区；
2. 5.1 版本中，分区表表达式必须时整数，5.5 以上版本可以使用列分区；
3. 分区字段中如果有主键和唯一索引列，那么主键列和唯一列都必须包含进来；
4. 分区表中无法使用外键约束；
5. 需要对现有表的结构进行修改；
6. 所有分区都必须使用相同的存储引擎；
7. 分区函数中可以使用的函数和表达式会有一些限制，todo：啥限制；
8. 某些存储引擎不支持分区，todo 啥引擎；
9. 对于 MyISAM 的分区表，不能使用 load index into cache；
10. 对于 MyISAM 的分区表，使用分区表时需要打开更多的文件描述符。

3. 尽量控制表字段数量

单表的字段数量应该根据业务场景进行优化调整，使单表的字段数少而精，这样有以下好处：

1. IO 高效；
2. 全表遍历；
3. 表修复快；
4. 提高并发；
5. alter table 更快；

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

• 看顺序读 1G 文件所需的时间不超过 N 秒；
• 单行不超过 200 Byte，
• 不超过 50 个纯 int 字段；
• 不超过 20 个 char(10) 字段；

建议单表字段数上限控制在 20-50个。

4. 平衡范式与冗余

数据库表结构的设计要遵守三大范式：

1. 第一范式（确保每列保持原子性）：单个字段不可再分；
2. 第二范式（确保表中每列都和主键相关）：不存在非主属性，只依赖部分主键，消除不完全依赖；
3. 第三范式（确保每列都和主键直接相关）：消除传递依赖。

范式是以性能换取存储，冗余是以存储换取性能。因此，一般情况下工作中冗余更受欢迎。

模型设计时，这两方面的权衡首先应以企业提供的计算能力和存储资源为基础。其次，二者的权衡需要符合任务需要，因为一般互联网行业中都根据 Kimball 模式实施数据仓库，建模也是以任务驱动的。

5. 拒绝 3B

• Big SQL：应该将大 SQL 拆解成多条简单 SQL；
• Big Transaction：事务/连接使用原则是 即开即用，用完即关；
  • 与事务无关的操作应该放在事务外面，减少锁资源的占用；
  • 不破坏一致性前提下，使用多个短事务代替一个长事务；
• Big Batch；

在做数据库开发的时候要注意高并发下的瓶颈，防止因高并发造成数据库瘫痪 。

字段类原则

1. 用好数值字段类型

MySQL 的三类数值类型有：

• 整型：TINYINT(1Byte)、TINYINT(1Byte)、SMALLINT(2B)、MEDIUMINT(3B)、INT(4B)、BIGINT(8B)
• 浮点型：FLOAT(4B)、DOUBLE(8B)
• 十进制数字：DECIMAL（M, D）

INT(1) 和 INT(11)：1 与 11 是显示长度的区别，无论 INT(x) 的 x 是什么值，存储长度都是 4 Bytes。

BIGINT AUTO_INCREMENT: 有符号 int 最大可以支持到约 22 亿，远远大于我们的需求和 MySQL 单表所能支持的性能上限。OLTP（On-Line Transaction Processing）应用中，单表规模一般保持在千万级别，不会达到 22 亿上限。如果加大预留量，可以把主键改为无符号整型，上限为 42 亿，预留量就会更加充足。

使用 bigint 会占用更大的磁盘和内存空间，无效占用会导致更多的数据换如换出，额外增加了 IO 的压力。因此，推荐主键使用 int unsigned 类型，不建议使用 bigint。

DECIMAL(N, 0): 如果采用 DECIMAL 类型，一般小数位不会默认是 0；如果要设置小数位数为0，建议直接使用整型。

2. 将字符转化为数字

相比字符串型索引，数字型索引更高效、查询更快、占用空间更小，例如，将 IP 存储为 INT，而不是 CHAT(15)，然后用 INET_ATON() 和 INET_NTOA() 实现 IP 字符串和数值之间的转换。

3. 优先使用 ENUM 或 SET

对于枚举型数据，推荐优先使用 ENUM 或 SET，主要适用于以下场景：

1. 字符串型枚举；
2. 枚举值已知且有限

ENUM 占用 1 Byte，转为数值运算；SET 视节点而定，最多占用 8 Byte。

注：比较时需要加单引号。

`sex` enum('F', 'M') COMMENT '性别',
`c1` enum('0','1','2','3') COMMENT '列'

4. 避免使用 NULL 字段

NULL 字段的弊端：

1. 很难进行查询优化；
2. NULL 列加索引需要额外空间；
3. 含 NULL 复合索引无效；

因此，建议在数据库表字段设计的时候尽量加上 NOT NULL DEFAULT '' 或者NOT NULL DEFAULT 0。

5. 少用并拆分 TEXT/BLOB

TEXT数据不存储在数据库服务器的内存中，因此，每当查询TEXT数据时，MySQL都必须从磁盘读取它，这与CHAR和VARCHAR相比要慢得多。

TEXT 最多可以存储 64KB 数据量，相当于 VARCHAR(65535)。

如果业务必须要用到 TEXT 数据类型，建议将 TEXT 列拆分到单独的表，在该表中只存储 text 列的主键。

6. 不在数据库里存图片

这不是显而易见吗？图片的大小比其路径大的多得多，保存图片路径即可。

将图片按照固定规则分配到多个目录中去，进行保存和查找。

索引类原则

1. 谨慎添加索引

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

• 添加索引会降低更新效率，因为不仅要更新主表、还要保存索引文件；
• 能不加的索引尽量不加：综合评估数据密度和数据分布，索引数量最好不超过字段数的 20%；
• 结合核心 sql 有限考虑覆盖索引。

2. 字符字段必须建前缀索引

• 单字母区分度 26
• 4 字母区分度 = 26^4 = 456,976;
• 6 字母区分度 = 26^6 = 308,915,776;

`str` varchar(100) DEFAULT NULL COMMENT 'string',
KEY `idx_str` (`str`(8)),
) ENGINE=InnoDB

3. 不在索引列做运算

• 在索引列上做任何操作，都会导致索引失效；
• 为了避免出现各种意想不到的问题，一定要保证索引字段的类型和条件中参数的类型一致。

4. 自增列或全局 ID 做 INNODB 主键

• 对主键建立聚簇索引；
• 二级索引存储主键值；
• 主键不应更新修改；
• 按自增顺序插入值；
• 忌用字符串做主键；
• 聚簇索引分裂；
• 推荐用独立于业务的 AUTO_INCREMENT 列或全局 id 生成器做代理主键；
• 若不指定主键，InnoDB 会用唯一且非空索引代替。

5. 尽量不用外键

外键约束保证了数据主从关系和产生的先后顺序。虽然外键可以节省开发量，但有额外开销：

1. 数据库需要维护外键的内部管理；
2. 外键等于把数据的一致性交给数据库实现，但这应该是程序保证的，不要让脚思考；
3. 有外键后，任何增、删、更新操作都需要触发相应操作去检查；
4. 可到达其他表，因此，对其他表内部加锁可能出现死锁情况。

SQL 类原则

1. 尽可能避免使用存储过程/触发器/函数

mysql 自带了 Store Procedure / Triggers / Function，但他们本身都是一组为了完成特定功能的 SQL 语句集。

这些事情应该交给客户端程序负责。

2. 尽量不用 SELECT *

在写查询语句时，应当尽量不用SELECT * ，只取需要的数据列。

• 相比只查询需要的数据列，select * 会消耗更多的 CPU、内存、IO、网络带宽。
• sql 语句更安全，可以一定程度上减少表变化带来的影响；

3. 将 OR 改写为 IN()

OR效率：O(n)

IN效率：O(Log n）

4. 将 OR 改写为 UNION

不同字段筛选条件时，将 OR 改为 UNION。

Select * from opp 
WHERE phone='010-88886666' 
union 
Select * from opp 
WHERE cellPhone='13800138000';

5. 避免负向查询和 % 前缀的模糊查询

• NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE等判断可能会造成索引失效；
• % 前缀会导致索引失效和全表扫描。

6. 减少 COUNT(*)

COUNT(*)对表中行的数目进行计数，不管表列中包含的是空 值(NULL)还是非空值。

COUNT(column) 对特定列中具有值的行进行计数，忽略 NULL值。

COUNT(*) 开销大，能不用尽量不用。

7. LIMIT 高效分页

SELECT _column,_column FROM _table [WHERE Clause] [LIMIT N][OFFSET M]

• limit N: 返回 N 条记录；
• offset M：跳过 M 条记录，默认 M=0，单独使用不起作用；
• LIMIT N,M == LIMIT M OFFSET N: 从第N条记录开始，返回 M 条记录；

N 越大越慢，推荐分页方式：

SELECT * FROM table WHERE id>=123 LIMIT 11;

8. 用 UNION ALL 而非 UNION

UNION 有去重开销。

9. 分解联结保证高并发

高并发数据库不建议进行两个表以上的 JOIN 查询，适当分解联结保证高并发。

10. 同数据类型的列值比较

数值列与字符类型比较：同时转换为双精度进行对比；

字符列与数值类型比较：字符列整列转数值，不会使用索引查询。

Strings are automatically converted to numbers and numbers to strings as necessary.

eg. ‘abc’ 转数值是 0，’1abc’转数值是1。

11. Load data 批量导入数据

批量数据用 Load data 命令导入更快，相比单行 insert，不需要每次刷新缓存。

12. 打散大批量更新

• 大批量更新尽量凌晨操作，避开高峰；
• 白天上线默认为 100条/秒；

13. Know Every SQL

作为 DBA 和数据库开发人员，必须对数据库的每条 SQL 都非常了解。

约定类原则

1. 隔离线上、线下环境

构建数据库的生态环境，确保不同角色的数据库环境不同。

2. 永远不在程序端显式加锁

• 外部锁对数据库不可控；
• 高并发时是灾难；
• 极难调试和排查；

对于类似并发扣款等一致性问题，采用事务处理，提交前进行二次校验冲突。

3. 统一字符集为 UTF8

避免乱码

4. 统一命名规范

• 库、表名称统一用小写；
• 库名用缩写，长度尽量在 2-7 个字母之间；
• 避免保留字命名：例如 GROUP、DROP、LIMIT 等。

References:

1. MySQL数据库开发的36条原则
2. MySQL Text 类型
3. Mysql 索引上做计算为什么会导致索引失效