数据库设计规范
数据库设计规范:
一 命名规范:
1、所有数据库对象名称必须使用小写字母并用下划线分割
2、所有数据库对象名称禁止使用MySQL保留关键字(如果表名中包含关键字查询时,需要将其用单引号括起来)
3、数据库对象的命名要做到见名识意,并且最好不超过32个字符
4、临时表必须以tmp_为前缀并以日期为后缀,备份表必须以bak_为前缀并以日期(时间戳)为后缀
5、所有存储相同数据的列名和列类型必须一致
二 数据库基本设计规范:
1、所有表必须使用Innodb存储引擎(MySQL5.5之前默认使用MyIsam,5.6以后默认的为Innodb)
2、数据库和表的字符集统一使用utf8mb4的utf8mb4_0900_ai_ci字符集(数据库版本mysql8以上)
3、所有表和字段都需要添加注释(使用 comment 从句添加表和列的备注)
4、尽量控制单表的数据量大小,建议控制在500万以为。
5、谨慎使用MySQL分区表
6、尽量做到冷热数据分离,减小表的宽度
MySQL 的sql_mode不能设置为only_full_group_by模式
7、禁止在表中建立预留字段
8、禁止在数据库中存储图片,文件等大的二进制数据
9、禁止在线上环境做压力测试
10、禁止从开发环境,测试环境直接连接生产环境数据库
11、外键关联表时必须设置级联删除和级联更新(ON DELETE cascade ON UPDATE cascade)
三 数据库字段设计规范:
1、优先选择符合存储需要的最小的数据类型
原因: 列的字段越大,建立索引时所需要的空间也就越大,这样一页中所能存储的索引节点的数量也就越少,遍历时所需要的IO次数也就越多。
2、避免使用TEXT,BLOB数据类型,最常见的TEXT类型可以存储64K的数据
a. 建议把 BLOB 或是 TEXT 列分离到单独的扩展表中
b.TEXT 或 BLOB 类型只能使用前缀索引**
3、避免使用枚举ENUM类型
ENUM 类型的 ORDER BY 操作效率低
4.尽可能把所有列定义为NOT NULL
原因:
索引 NULL 列需要额外的空间来保存,所以要占用更多的空间
进行比较和计算时要对 NULL 值做特别的处理
- 使用 TIMESTAMP(4 个字节) 或 DATETIME 类型 (8 个字节) 存储时间,
经常会有人用字符串存储日期型的数据(不正确的做法)
缺点 1:无法用日期函数进行计算和比较 缺点 2:用字符串存储日期要占用更多的空间
6.同财务相关的金额类数据必须使用 decimal 类型 非精准浮点:float,double,精准浮点:decimal。
Decimal 类型为精准浮点数,在计算时不会丢失精度
占用空间由定义的宽度决定,每 4 个字节可以存储 9 位数字,并且小数点要占用一个字节
四 索引设计规范:
1、每个 Innodb 表必须有个主键
Innodb 是一种索引组织表:数据的存储的逻辑顺序和索引的顺序是相同的。每个表都可以有多个索引,但是表的存储顺序只能有一种。
Innodb 是按照主键索引的顺序来组织表的
•不要使用更新频繁的列作为主键,不适用多列主键(相当于联合索引)
•不要使用 UUID,MD5,HASH,字符串列作为主键(无法保证数据的顺序增长)
•主键建议使用自增 ID 值
2、限制每张表上的索引数量,建议单张表索引不超过 5 个
索引可以增加查询效率,但同样也会降低插入和更新的效率,甚至有些情况下会降低查询效率。
因为 MySQL 优化器在选择如何优化查询时,会根据统一信息,对每一个可以用到的索引来进行评估,以生成出一个最好的执行计划,如果同时有很多个索引都可以用于查询,就会增加 MySQL 优化器生成执行计划的时间,同样会降低查询性能
3、禁止给表中的每一列都建立单独的索引
4、常见的索引列建议:
出现在SELECT 、UPDATE 、DELETE语句中的WHERE从句中的列
包含在ORDER BY 、GROUP BY 、DISTINCT中的字段
多表join 的关联列
5.如何选择索引列的顺序
建立索引的目的是:希望通过索引进行数据查找,减少随机 IO,增加查询性能 ,索引能过滤出越少的数据,则从磁盘中读入的数据也就越少。
•区分度最高的放在联合索引的最左侧(区分度=列中不同值的数量/列的总行数)
•尽量把字段长度小的列放在联合索引的最左侧(因为字段长度越小,一页能存储的数据量越大,IO 性能也就越好)
•使用最频繁的列放到联合索引的左侧(这样可以比较少的建立一些索引)
- 避免建立冗余索引和重复索引(增加了查询优化器生成执行计划的时间)
•重复索引示例:primary key(id)、index(id)、unique index(id)
•冗余索引示例:index(a,b,c)、index(a,b)、index(a)
- 对于频繁的查询优先考虑使用覆盖索引
覆盖索引:就是包含了所有查询字段 (where,select,ordery by,group by 包含的字段) 的索引
覆盖索引的好处:
避免Innodb 表进行索引的二次查询:Innodb是以聚集索引的顺序来存储的,对于Innodb来说,
二级索引在叶子结点中所保存的是行的主键信息,如果使用二级索引查询数据的话,在查找到相应的键值后,还要通过主键进行二次查询才能获取我们真实所需要的数据。而在覆盖索引中,二级索引的键值中可以获取所有的数据,避免了对主键的二次查询,减少了IO 操作,提升了查询效率
可以把随机IO 变成顺序IO加快查询效率:由于覆盖索引是按照 键值的顺序存储的,对于IO密集型的范围查找来说,对比随机从磁盘读取每一行的数据IO要少的多,因此利用覆盖索引在访问时,可以把磁盘随机读取的IO转变成索引查找的顺序IO
8、索引SET 规范
尽量避免使用外键约束
不建议使用外键约束,但一定要在表与表之间的关联键上建立索引
外键可用于保证数据的参照完整性,但建议在业务端实现•
外键会影响父表和子表的写操作从而降低性能
五 数据库操作行为规范:
- 超 100 万行的批量写 (UPDATE,DELETE,INSERT) 操作,要分批多次进行操作
大批量操作可能会造成严重的主从延迟
主从环境中,大批量操作可能会造成严重的主从延迟,大批量的写操作一般都需要执行一定长的时间, 而只有当主库上执行完成后,才会在其他从库上执行,所以会造成主库与从库长时间的延迟情况
binlog 日志为 row 格式时会产生大量的日志
大批量写操作会产生大量日志,特别是对于 row 格式二进制数据而言,由于在 row 格式中会记录每一行数据的修改,我们一次修改的数据越多,产生的日志量也就会越多,日志的传输和恢复所需要的时间也就越长,这也是造成主从延迟的一个原因
避免产生大事务操作
大批量修改数据,一定是在一个事务中进行的,这就会造成表中大批量数据进行锁定,从而导致大量的阻塞,阻塞会对 MySQL 的性能产生非常大的影响。
特别是长时间的阻塞会占满所有数据库的可用连接,这会使生产环境中的其他应用无法连接到数据库,因此一定要注意大批量写操作要进行分批
- 对于大表使用 pt-online-schema-change 修改表结构
避免大表修改产生的主从延迟•避免在对表字段进行修改时进行锁表
对大表数据结构的修改一定要谨慎,会造成严重的锁表操作,尤其是生产环境,是不能容忍的。
pt-online-schema-change 它会首先建立一个与原表结构相同的新表,并且在新表上进行表结构的修改,然后再把原表中的数据复制到新表中,并在原表中增加一些触发器。把原表中新增的数据也复制到新表中,在行所有数据复制完成之后,把新表命名成原表,并把原来的表删除掉。把原来一个 DDL 操作,分解成多个小的批次进行
- 禁止为程序使用的账号赋予 super 权限
当达到最大连接数限制时,还运行 1 个有 super 权限的用户连接•super 权限只能留给 DBA 处理问题的账号使用
- 对于程序连接数据库账号,遵循权限最小原则
程序使用数据库账号只能在一个 DB 下使用,不准跨库•程序使用的账号原则上不准有 drop 权限
- 禁止使用不含字段列表的 INSERT 语句
如:
insert into values (‘a’,‘b’,‘c’);
应使用:
insert into t(c1,c2,c3) values (‘a’,‘b’,‘c’);
六 SQL优化:
1、避免数据类型的隐式转换,隐式转换会导致索引失效如:
select name,phone from customer where id = ‘111’;
2、充分利用表上已经存在的索引
3、禁止使用 SELECT * 必须使用 SELECT <字段列表> 查询
4、 避免使用子查询,可以把子查询优化为 join 操作
通常子查询在 in 子句中,且子查询中为简单 SQL(不包含 union、group by、order by、limit 从句) 时,才可以把子查询转化为关联查询进行优化。
子查询性能差的原因:
子查询的结果集无法使用索引,通常子查询的结果集会被存储到临时表中,不论是内存临时表还是磁盘临时表都不会存在索引,所以查询性能会受到一定的影响。特别是对于返回结果集比较大的子查询,其对查询性能的影响也就越大。
由于子查询会产生大量的临时表也没有索引,所以会消耗过多的 CPU 和 IO 资源,产生大量的慢查询。
5、避免使用 JOIN 关联太多的表
6、减少同数据库的交互次数(就是大批量的操作分组进行比如update语句是尽量批量分批操作)
7、对应同一列进行 or 判断时,使用 in 代替 or
in 的值不要超过 500 个,in 操作可以更有效的利用索引,or 大多数情况下很少能利用到索引
8、在明显不会有重复值时使用 UNION ALL 而不是 UNION
UNION 会把两个结果集的所有数据放到临时表中后再进行去重操作•UNION ALL 不会再对结果集进行去重操作
9、拆分复杂的大 SQL 为多个小 SQL
大 SQL 逻辑上比较复杂,需要占用大量 CPU 进行计算的 SQL•MySQL 中,一个 SQL 只能使用一个 CPU 进行计算•SQL 拆分后可以通过并行执行来提高处理效率
10.优化groupBy 尽量在此之前把无用数据过滤掉,having效率较低
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· 百万级群聊的设计实践
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
· 永远不要相信用户的输入:从 SQL 注入攻防看输入验证的重要性