首页
MySQL数据库设计规范2
蓝鲸开发
库表字段设计规范
- 【必须】库/表/字段的字符集必须保持一致
- 【必须】所有表必须要有主键，主键不能使用更新频繁的列，以定长类型作为主键或者联合主键的第一字段
- 【必须】不使用 UUID MD5 HASH 这些值作为主键(一般使用连续递增的值作为主键，比如自增 ID)
- 【必须】存在自增列的表，自增列上必须存在一个单独的索引，若在复合索引中，自增列必须置于第一位
- 【建议】建表时应建立好合适的索引，并考虑未来的需求建立保留索引，避免上线后数据量增大再来创建新索引
- 【建议】建表时可以适当考虑未来功能需求，设计一些保留字段，避免频繁的 DB 增加字段
- 【建议】避免使用临时表
- 【建议】表中避免使用外键
- 【必须】库名、表名、字段名避免使用 MySQL 保留字(如: BACKUP/CACHE/CODE 等) 详细参看官方文档 8.0 版本
- 【必须】所有字段均定义为 NOT NULL ，并设置 default 值 (NULL 字段很难优化查询，NULL 字段的索引需要额外空间，NULL 字段的复合索引无效)
- 【建议】避免单表太多字段, 最好不超过 32 个，尤其是版本不断迭代的情况下，如果字段过多，建议适当考虑分表而不是加字段
- 【建议】加字段尽量在表的末尾增加，避免使用 after/before 类 alter 操作
- 【建议】尽可能避免单个 blob 过大，实在不行请分表
- 【建议】如果使用到 text/blob 类型，单表避免使用超过 8 个 blob 字段；如果 blob 字段内容较多，程序考虑做压缩 (blob 超过 8 个建议使用 row_format=dynamic 行格式来存储)
- 【建议】整型字段, 如果数据量会很大,请使用 BIGINT,避免字段溢出(流水 ID/道具 ID 等)
- 【建议】日期字段建议用 DATETIME
- 【建议】强烈建议使用 TINYINT 来代替 ENUM 类型
- 【建议】遵循精确原则，用 DECIMAL 代替 FLOAT 和 DOUBLE 存储精确浮点数
- 【建议】建议使用 INT UNSIGNED 存储 IPV4
- 【建议】使用 UNSIGNED 存储非负整数
- 【必须】所有表/字段均应使用 comment 来描述此表/字段所代表的真正含义
- 【必须】禁止在数据库中存储明文密码，把密码在应用层做加密后存储(非对称算法最佳)
- 【必须】数据库中不允许存储视频或者照片等大文件，可以将大对象放到磁盘或者其他对象存储上
索引规范
- 【必须】单表中索引数量不超过 5 个，过多的索引会影响 UPDATE/INSERT/DELETE 操作
- 【必须】单个索引中的字段数不超过 5 个
- 【必须】单表中的索引数禁止超过字段数
- 【必须】重要的 SQL 必须被索引，核心 SQL 优先考虑覆盖索引，避免回表 (说明：索引包含所有满足查询需要的数据的索引就是覆盖索引)
- 【必须】禁止重复索引(一个字段上建立多个索引) 例如表含有 primary key a uniq index a index a，则字段 a 被建了 3 个重复索引
- 【必须】禁止冗余索引(多个索引的前缀列相同，或在联合索引中包含了主键的索引) 例如表有索引 idx1(a,b,c)、idx2(a,b)，则 idx2 为冗余索引，因为 idx1 已经包含 idx2
- 【必须】UPDATE/DELETE 语句的 WHERE 条件列；ORDER BY、GROUP BY、DISTINCT的字段；多表关联的 JOIN 字段都应该要有索引
- 【必须】区分度最大的字段放在前面，符合最左前缀的特点建立索引
- 【必须】不在索引列进行数学运算和函数运算，否则无法使用索引/导致全表扫描。例子: SELECT name FROM class WHERE ABS(score) > 80;
- 【必须】不在低基数列上建立索引，例如“性别”、布尔值的列
- 【必须】索引字段的默认值不能为 NULL ，要改为其他的默认值，因 NULL 值对查询效率影响很大
- 【知会】对字符串使用前缀索引，前缀索引长度不超过 8 个字符，但在 ORDER BY 或 GROUP BY 中使用不到前缀索引。建立前缀索引的语法：ALTER TABLE table_name ADD KEY(column_name(prefix_length));
- 【知会】尽量不在 BLOB/TEXT 等字段上建立索引，且 BLOB 和 TEXT 类型的列只能创建前缀索引
- 【知会】合理创建联合索引(避免冗余)，减少维护索引的 IO 开销
- 【知会】使用不等于 (!=、<>、not in 、not like 等) 的时候，MYSQL 无法使用索引
- 【知会】使用 LIKE 操作的时候 where 条件以%开始 (如‘%abc’)时，MYSQL 无法使用索引，使用通配符尽量不要放在开头
- 【知会】多表关联中 join 条件字段类型不一致的时候无法使用索引
SQL 语句设计规范
- 【必须】MySQL 不擅长数学计算，不要把大计算量的 SQL 放在 MySQL 中执行。 如：统计/计算类操作避免从 DB 中直接计算
- 【必须】单次查询的结果集行数不要过多(控制在几百几千行)，如果结果集确实很多，注意控制查询频率(避免对 DB 机器的流量/IO/CPU 等产生压力)
- 【必须】避免使用 CREATE table AS SELECT * FROM …的操作
- 【必须】程序层避免使用 INSERT INTO … SELECT *
- 【必须】不要用 SELECT * FROM，查询哪几个字段就 SELECT 几个字段
- 【必须】业务代码中事务及时提交，避免产生没必要的锁等待
- 【必须】SQL 中使用到 OR 的改写为用 IN()，且 in 的值最好不超过 1000 个
- 【必须】SQL 中避免隐式转换，会导致索引失效(这是一个大坑)，如：数值类型禁止加引号；字符串类型必须加引号
- 【必须】充分利用前缀索引，而且要符合最左前缀原则
- 【必须】不使用%前导的查询，如like “%abc”，会导致索引失效
- 【必须】不使用负向查询，如 NOT IN、NOT LIKE、!=、<>会导致无法使用索引，引起全表扫描，并且会把 NULL 查出来
- 【必须】UPDATE、DELETE 语句必须使用 WHERE 条件
- 【必须】清理历史数据时，不要一次清理过多，建议使用DELETE … LIMIT N分批清理
- 【必须】统计行数使用COUNT(*)，避免使用 COUNT(app)这样的操作
- 【必须】INSERT 语句必须指定字段列表，禁止使用INSERT INTO TABLE()
- 【必须】禁止使用ORDER BY RAND()
- 【建议】避免使用 JOIN 联表操作
- 【建议】避免使用触发器、函数、存储过程、event 等
- 【建议】如果确认合并的两个结果集中不包含重复的数据，那么请使用 UNION ALL 而不是 UNION
- 【建议】减少与数据库交互次数，尽量采用批处理 SQL 语句
- 【必须】防止 SQL 注入，程序应对传入的参数进行严格校验，正确过滤字符，而且避免使用动态拼装 SQL