目录
- 一、数据库命名规范
- 二、数据库基本设计规范
- 三、索引设计规范
- 四、数据库字段设计规范
- 五、数据库SQL 开发规范
- 六、数据库操作行为规范
一、数据库命名规范
- 所有数据对象名称必须小写 :
db_user
- 禁止使用MySQL 保留关键字，若是则引用 ``
- 临时表以
tmp_
bak_
- 所有存储相同数据的列名和列类型必须一致
二、数据库基本设计规范
-
MySQL 5.6
Innodb
- 数据库和表的字符集统一使用
UTF-8
（统一字符集可以避免由于字符集转换产生的乱码）
MySQL 中 UTF-8 字符集 汉字 占 3 个字节，ASCII 码占用 1 个字节。
- 所有表和字段都需要添加注释。 使用
comment
-
尽可能控制单表数据量的大小，建议控制在500万以内 （这种限制取决于存储设置和文件系统）
可以用历史数据归档，分库分表等手段来控制数据量大小
-
谨慎使用MySQL分区表
（分区表在物理上表现为多个文件，在逻辑上表现为一个表）
谨慎选择分区键，跨分区查询效率可能更低
建议采用物理分表的方式管理大数据
-
尽量做到冷热数据分类，减小表的宽度（即：列）
减少磁盘IO，保证热数据的缓存命中率
利用有效的缓存，避免读入无用的冷数据（不建议使用
SELECT *
）
垂直拆分：经常一起使用的列放到一个表中
-
禁止在表中建立预留字段
预留字段的命名很难做到见名识义
预留字段无法确认存储的数据类型，所有无法选择合适的类型
对预留字段类型的修改，会对表进行锁定
- 禁止在数据库中存储图片，文件等二进制数据
三、索引设计规范
-
限制每张表上的索引数量，建议单张表索引不超过 5 个
（索引可以提高效率同样可以降低效率）
-
Innodb
主键
Innodb
表必须有一个主键
（不适用更新频繁的列作为主键，不使用多列主键）
（不使用 UUID , MD5, HASH，字符串列作为主键）
（主键建议选择使用自增 ID 值）
- 常见索引列 建议：
- SELECT 、UPDAT、DELETE语句的 WHERE 从句中的列
- 包含在 ORDER BY、GROUP BY 、DISTINCT中的字段
- 多表 JOIN 的关联列
-
如何选择索引列的顺序
（区分度最高的列放在联合索引的最左侧 ，比如 主键）
（尽量把字段长度小的列放在联合索引的最左侧）
（使用最频繁的列放到联合索引的左侧）
-
避免建立冗余索引和重复索引
重复索引：primary key(id), index(id), unique index(id)
冗余索引：index(a，b，c）、index（a，b）、index（a）
-
对于频繁的查询优先考虑使用覆盖索引
覆盖索引：就是包含了所有查询字段的索引
（避免Innodb表进行索引的二次查询）
（可以把随机IO变为顺序IO加快速度）
-
尽量避免使用外键
不建议使用外键约束，但一定在表与表之间的关联键上建立索引
外键可用于保证数据的参照完整性，但建议在业务端实现
外键会影响父表和子表的写操作从而降低性能
四、数据库字段设计规范
-
优先选择符合存储需要的最小的数据类型
将字符串转化为数字类型存储
比如：将IP转为数字。（15字节 -》4字节）
INET_ATON('255.255.255.255') = 4294967295
INET_NTOA(4294967295)= ''255.255.255.255' -
对于非负数据采用无符号整型进行存储
SIGNED INT : -2147483648 ~ 2147483647
UNSIGNED INT : 0 ~ 4294967295
- VARCHAR(N) 中的N代表的字符数，而不是字节数
- 使用UTF-8 存储汉字 VARCHAR(255) = 765 字节
- 过大的长度会消耗更多的内存
- 避免使用
TEXT
BLOB
数据类型
建议把
BLOB
TEXT
列分离到单独的扩展表中
TEXT
BLOB
- 避免使用
ENUM
数据类型
修改ENUM值需要使用
ALTER
语句
ENUM类型的 ORDER BY 操作效率低， 需要额外操作
禁止使用数值作为ENUM 的枚举值
- 尽可能把所有列定义为
NOT NULL
索引NULL 列需要额外的空间来保存，所以要占用更多的空间
进行比较和计算时要对NULL 值做特别的处理
-
字符串存储日期型的数据（不正确的做法）
缺点1：无法用日期函数进行计算和比较
缺点2：用字符串存储日期要占用更多的空间
使用 TIMESTAMP 或 DATETIME 类型存储时间
TIMESTAMP : 1970-01-01 00:00:01 ~ 2038-01-19 03:14:07 （占用 4 个字节）
超出TIMESTAMP取值范围的使用 DATETIME 类型
- 同财务相关的金额类数据，必须使用
decimal
类型
Decimal 类型为精确浮点数，在计算时不会丢失精度
占用空间由定义的宽度决定（
.
占一个字节）
可用于存储必 bigint 更大的整数数据
五、数据库SQL 开发规范
- 建议使用预编译语句进行数据库操作
-
避免数据类型的隐式转换
隐式转换会导致索引失效
-
充分利用表上已经存在的索引
避免使用双%号的查询条件。如
a like '%123%'
一个SQL只能利用到复合索引中的一列进行范围查询
使用
left join
not exists
not in
-
程序连接不同的数据库使用不同的账号，禁止跨库查询
为 数据库 迁移和分库分表留出余地
降低业务耦合度
避免权限过大而产生的安全风险
-
禁止使用 SELECT * ，必须使用SELECT <字段列表> 查询
消耗更多的 CPU 和 IO 以及网络带宽资源
无法使用覆盖索引
可减少表结构变更带来的影响
- 禁止使用不含字段列表的INSERT 语句
- 避免使用子查询，可以把子查询优化为
join
操作
子查询的结果集无法使用索引
子查询会产生临时表操作，如果子查询数据量大则严重影响效率
消耗过多的CPU 及 IO资源
- 避免使用
JOIN
关联太多的表
每 join 一个表会多占用一部分内存（join_buffer_size）
会产生临时表操作，影响查询效率
MySQL 最多允许关联61个表，建议不超过5个
-
减少同数据库的交互次数
数据库更适合处理批量操作。
合并多个相同的操作到一个，可以提高处理效率
- 使用 in 代替 or
-
禁止使用 order by rand() 进行随机排序
会把表中所有符合条件的数据装载到内存中进行排序
会消耗大量的CPU 和 IO及内存资源
-
WHERE 从句中禁止对列进行函数转换和计算
对列进行函数转换或计算会导致无法使用索引
# 如：
where date(createtime) = '20160901'
# 改为：
where createtime >= '20160901' and createtime < '20160i902' - 在明显不会有重复值时使用
UNION ALL
UNION
UNION 会把所有数据放到临时表中后再进行去重操作
UNION ALL 不会再对结果集进行去重操作
-
拆分复杂的大SQL 为多个小SQL
MySQL 一个 SQL 只能使用一个 CPU 进行计算
SQL 拆分后可以通过并行执行来提高处理效率
六、数据库操作行为规范
-
超过100万行的批量写操作，要分批多次进行操作
大批量操作可能会造成严重的主从延迟
- 避免产生大事务操作
- 对于大表使用 pt-online-schema-change修改表结构
- 禁止为程序使用的账号赋予super权限
- 对于程序数据库账号只能在一个DB下使用，不准跨库
![【MySQL数据库】数据库索引事务1.索引2.事务[图]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)