MySQL 数据库铁律

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS MySQL,高可用系列 2核4GB
简介: MySQL 数据库铁律好的数据库规范有助于减少软件实现的复杂度,降低沟通成本,本铁律主要涵盖了建库建表、建索引、写 SQL、ORM 映射等方面的处理约定。1.建库铁律铁律 Level 备注字符集 使用 utf-8。

MySQL 数据库铁律
好的数据库规范有助于减少软件实现的复杂度,降低沟通成本,本铁律主要涵盖了建库建表、建索引、写 SQL、ORM 映射等方面的处理约定。

1.建库铁律

  • 铁律 Level 备注
    字符集 使用 utf-8。如果存储的是表情则选用 utf8mb4 进行存储。 强制

排序规则 使用 utf8_general_ci 强制
2.建表铁律

  • 铁律 Level 备注
    注释 一定要有字段注释。 强制

编码 使用 utf-8。如果存储的是表情则选用 utf8mb4 进行存储。 强制
是否概念的字段 必须用 is_xx 命名,数据类型是 unsigned tinyint(1是0否)例如 is_deleted(1删除0未删除)。 强制 任何字段如果非负数必须unsigned
表名、字段名 只能使用小写字母、下划线或者数字;禁止以下划线或者数字开头;禁止两个下划线之间只出现数字;禁用保留字;表名禁止使用复数名词。 强制
库名、表名的命名 库名尽量与应用名称一致,表名最好用 业务名称_表的作用 命名。 强制
索引命名 主键索引用 pk_字段名;唯一索引用 uk_字段名;普通索引用 idx_字段名。 强制 pk_ 即 primary key;uk_即 unique key;idx_即 index
小数类型 数据类型是 decimal,禁止使用 float 和 double,float 和 double 存在精度损失,如果存储的数据范围超过 decimal 的范围,建议将数据拆成整数和小数并分开存储。 强制
varchar类型 varchar是可变长字符串,不预先分配存储空间,长度不要超过5000个字符,如果长度大于5000应用text(独立出一张表来,用主键来对应,避免影响其他字段的索引效率)。 强制
表名必备三字段 id(数据类型是 unsigned bigint,单表递增,步长为1),gmt_create、gmt_modified(主动创建时间、被动更新时间,数据类型都是 datetime)。 强制
字段冗余 字段允许适当冗余,但必须考虑数据一致,冗余字段应具备1)不频繁修改;2)不是varchar超长字段,更不能是text字段。 推荐
分库分表 单表行数超过500万行或者单表容量超过2GB时,才推荐分库分表。 推荐
设置合适的字符存储长度,不但可以节约数据库表空间和索引存储,更重要的是能够提升检索速度。

3.建索引铁律

  • 铁律 Level 备注
    唯一索引 业务上具有唯一特性的字段,即使是多个字段的组合,也必须建成唯一索引。虽然唯一索引影响了 insert 速度,这个损耗可以忽略,但是明显提高了查询速度;另外,即使在应用层做了非常完善的校验控制,只要没有唯一索引,根据墨菲定律,必然有脏数据产生。 强制

join 超过三个表禁止 join,需要 join 的字段,数据类型必须一致;当多表关联查询时,保证被关联的字段需要有索引;即使双表 join 也要注意表索引、SQL 性能。 强制
varchar字段上建立索引 必须指定索引长度,没必要对全字段建立索引,根据实际文本区分度决定索引长度即可。索引长度与区分度是一对矛盾体,一般对字符串类型数据,长度为 20 的索引,区分度会高达 90% 以上,可以使用 count(distinct left(列名, 索引长度))/count(*) 的区分度来确定。 强制
页面搜索禁止模糊 页面搜索禁止左模糊或者全模糊,如果有需要请走搜索引擎来解决。禁止原因:索引文件具有 B-Tree 的最左前缀匹配特性,如果左边的值未确定,那么无法使用此索引。 强制
order by 如果有 order by 的场景,请注意索引的有序性。order by 最后的字段是组合索引的一部分,并且放在索引组合顺序的最后,避免出现 file_sort 的情况,影响查询性能。正例:where a=? and b=? order by c; 索引应建为 a_b_c;反例:索引中有范围查找,那么索引有序性无法利用,如 where a>10 order by b; 索引 a_b 无法排序。 推荐
4.写SQL铁律

  • 铁律 Level 备注
    count() 不要使用 count(列名) 或 count(常量) 来替代 count(),count() 是 SQL92 定义的标准统计行数的语法,跟数据库无关,跟 NULL 和非 NULL 无关。count() 会统计值为 NULL 的行,而 count(列名) 不会统计此列为 NULL 的行。 强制

count(distinct col) 计算该列除 NULL 外的不重复行数。注意,count(distinct col1, col2),如果其中一列全为 NULL,那么即使另一列有不同的值,也返回为 0。 强制
sum(col) 当一列的值全为 NULL 时,count(col) 的返回结果为 0,但 sum(col) 的返回结果为 NULL,因此使用 sum() 时需要注意 NPE 问题。可用如下方式避免 NPE 问题:select if(isnull(sum(g)), 0, sum(g)) from table; 强制
isnull 使用 isnull() 来判断是否为 NULL 值。NULL 与任何值的比较都为 NULL。 强制
分页查询逻辑 若 count 为 0 应直接返回,避免执行后面的分页语句。 强制
外键与级联 禁止使用外键与级联,一切外键概念必须在应用层解决。原因:外键与级联不适合分布式、高并发集群,级联更新是强阻塞,存在数据库更新风暴的风险,外键影响数据库的插入速度。 强制
存储过程 禁止使用存储过程,存储过程难以调试和扩展,更没有移植性。 强制
数据订正 数据订正(特别是删除、修改记录操作)时要先 select,避免出现误删除,确认无误后才能执行更新语句。 强制
in in 操作能避免就避免,如果实在避免不了,in 后面的集合元素数量要控制在 1000 个以内。 推荐
truncate table 禁止使用 truncate table,truncate table 比 delete 速度快,且使用的系统和日志资源少,但是 truncate 无事务且不触发 trigger,有可能造成事故,故不要在开发代码中使用此语句。 参考
5.ORM映射铁律

  • 铁律 Level 备注
    表查询 禁止使用 * 作为查询的字段列表,需要哪些字段必须明确。 强制

POJO POJO 类的布尔属性不能加 is,而数据库字段必须加 is,要求在 resultMap 中进行字段与属性之间的映射。 强制
返回参数 禁止用 resultClass 作为返回参数,即使所有类属性名与数据库字段一一对应,也需要定义;反过来,每一个表也必然有一个属性与之对应。原因:配置映射关系,使字段与 DO 类结耦,方便维护。 强制
返回参数 禁止直接使用 HashMap、HashTable 作为查询结果集的输出。原因:属性值的类型不可控。 强制
sql.xml 配置参数 sql.xml 配置参数使用 #{}, #param#,不要使用 ${},${} 容易出现SQL注入。 强制
queryForList 禁止使用 Mybatis 自带的 queryForList(String statementName, int start, int size)。原因:其实现方式是在数据库取到 statementName 对应的 SQL 语句的所有记录,再通过 subList 取 start, size 的子集合。 强制
更新时间 更新数据库表记录时,必须同时更新记录对应的修改时间。 强制
更新数据库表记录 不要写一个大而全的数据更新接口(传入为 POJO 类)。执行 SQL 时,不要更新无改动的字段,原因:容易出错、效率低、增加 binlog 存储。 推荐
@Transactional @Transactional 事务不要滥用。事务会影响数据库的 QPS。另外,使用事务的地方需要考虑各方面的回滚方案,包括缓存回滚、搜索引擎回滚、消息补偿、统计修正等。 参考
Mybatis 动态sql标签 < isEqual> 中的 compareValue 是与属性值对比的常量,一般是数字,表示相等时执行相应的 SQL 语句;< isNotEmpty> 表示不为空且不为 null 时执行;< isNotNull> 表示不为 null 时执行。 参考
————————————————
版权声明:本文为CSDN博主「郭朝」的原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/smartbetter/article/details/100160069

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
2天前
|
SQL 关系型数据库 MySQL
MySQL导入.sql文件后数据库乱码问题
本文分析了导入.sql文件后数据库备注出现乱码的原因,包括字符集不匹配、备注内容编码问题及MySQL版本或配置问题,并提供了详细的解决步骤,如检查和统一字符集设置、修改客户端连接方式、检查MySQL配置等,确保导入过程顺利。
|
22天前
|
SQL 关系型数据库 MySQL
12 PHP配置数据库MySQL
路老师分享了PHP操作MySQL数据库的方法,包括安装并连接MySQL服务器、选择数据库、执行SQL语句(如插入、更新、删除和查询),以及将结果集返回到数组。通过具体示例代码,详细介绍了每一步的操作流程,帮助读者快速入门PHP与MySQL的交互。
34 1
|
24天前
|
SQL 关系型数据库 MySQL
go语言数据库中mysql驱动安装
【11月更文挑战第2天】
37 4
|
1月前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第27天】本文深入探讨了MySQL的索引策略和查询性能调优技巧。通过介绍B-Tree索引、哈希索引和全文索引等不同类型,以及如何创建和维护索引,结合实战案例分析查询执行计划,帮助读者掌握提升查询性能的方法。定期优化索引和调整查询语句是提高数据库性能的关键。
176 1
|
1月前
|
关系型数据库 MySQL Linux
在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤,包括准备工作、下载源码、编译安装、配置 MySQL 服务、登录设置等。
本文介绍了在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤,包括准备工作、下载源码、编译安装、配置 MySQL 服务、登录设置等。同时,文章还对比了编译源码安装与使用 RPM 包安装的优缺点,帮助读者根据需求选择最合适的方法。通过具体案例,展示了编译源码安装的灵活性和定制性。
89 2
|
1月前
|
存储 关系型数据库 MySQL
MySQL vs. PostgreSQL:选择适合你的开源数据库
在众多开源数据库中,MySQL和PostgreSQL无疑是最受欢迎的两个。它们都有着强大的功能、广泛的社区支持和丰富的生态系统。然而,它们在设计理念、性能特点、功能特性等方面存在着显著的差异。本文将从这三个方面对MySQL和PostgreSQL进行比较,以帮助您选择更适合您需求的开源数据库。
127 4
|
19天前
|
运维 关系型数据库 MySQL
安装MySQL8数据库
本文介绍了MySQL的不同版本及其特点,并详细描述了如何通过Yum源安装MySQL 8.4社区版,包括配置Yum源、安装MySQL、启动服务、设置开机自启动、修改root用户密码以及设置远程登录等步骤。最后还提供了测试连接的方法。适用于初学者和运维人员。
135 0
|
2月前
|
存储 关系型数据库 MySQL
如何在MySQL中创建数据库?
【10月更文挑战第16天】如何在MySQL中创建数据库?
|
1月前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第26天】数据库作为现代应用系统的核心组件,其性能优化至关重要。本文主要探讨MySQL的索引策略与查询性能调优。通过合理创建索引(如B-Tree、复合索引)和优化查询语句(如使用EXPLAIN、优化分页查询),可以显著提升数据库的响应速度和稳定性。实践中还需定期审查慢查询日志,持续优化性能。
76 0
|
2月前
|
存储 监控 关系型数据库
MySQL并发控制与管理:优化数据库性能的关键
【10月更文挑战第17天】MySQL并发控制与管理:优化数据库性能的关键
202 0