解读阿里巴巴开发规范之MySQL

devopsec 等级 381 0 0

前言

从7月份毕业开始算起,也是将近有5个月的工作经验了吧。在工作上,经历了一段时间的适应,现在接触到不同大神写出来的代码,发现各有优劣,于是就在思考一个问题,什么样的代码才是最正常最规范的呢?我的同事甩给我了一本《阿里巴巴Java开发手册》,从头到尾也算是过了一遍。今天趁着双休的假期,我就来讲一下这本书里面的MySQL规范制约吧~

阿里巴巴开发规范之MySQL

建表规约

1、【强制】每张表必须设置一个主键ID,并且这个主键ID要自增(在满足需要的情况下尽量短),除非是分库分表

理解:由于InnoDB存储引擎决定了需要有一个主键,而且这个主键ID是自增的话可以有效提高插入的性能,避免过多的页分裂,减少表碎片提高空间的利用率。

但是在分库分表下,会有分片规则,这个时候需要统一分配各个表中的主键值,从而避免整个逻辑表中主键重复,一般我们会使用雪花ID来实现。

2、【强制】必须使用utf8mb4字符集

理解:在MySQL中的UTF-8并非是真正的"UTF-8",而utf8mb4才是真正的"UTF-8"。

3、【强制】数据库表、表字段必须加入中文注释

理解:程序员都不要给我懒,这样可以让后来者更好的上手和理解。

4、【强制】库名、表名、字段名必须小写,下划线风格,不超过32个字符,必须见名知意,禁止拼音英文混用

理解:在linux环境下MySQL是区分大小写的,而在windows是不区分大小写的,所以统一使用小写+下划线可以避免不必要的混淆。

5、【强制】单表列数目必须小于30,若超过则应该考虑将表拆分

理解:单表列数太多会使得MySQL处理InnoDB返回数据之间的映射成本太高。

6、【强制】禁止使用外键,如果有外键完整性约束,需要应用程序控制

理解:外键会导致表与表之间耦合,UPDATE与DELETE操作都会涉及相关联的表,十分影响SQL的性能,甚至会造成死锁。

7、【强制】必须把字段定义为NOT NULL并且提供默认值

理解:NULL需要更多的存储空,无论是表还是索引中每行中的NULL的列都需要额外的空间来标识。NULL这种类型需要MySQL内部进行特殊处理,增加了数据库处理记录的复杂性,同等条件下,表中较多NULL字段会导致数据库处理性能下降。

8、【强制】禁止使用保留字,如DESC、RANGE、MAX等

理解:请参考MySQL官方保留字。

9、【强制】如果存储的字符串长度几乎相等,使用CHAR定长字符串类型

理解:能够减少空间碎片,节省存储空间。

10、【强制】小数类型为 decimal,禁止使用 float 和 double

理解:float 和 double 在存储的时候,存在精度损失的问题,很可能在值的比较时,得到正确的结果。如果存储的数据范围超过 decimal 的范围,建议将数据拆成整数和小数分开存储。

11、【强制】表必备三字段:id, gmt_create, gmt_modified。

理解:其中 id 必为主键,类型为 bigint unsigned、单表时自增、步长为 1。gmt_create,gmt_modified 的类型均为 datetime 类型,前者现在时表示主动创建,后者过去分词表示被动更新。

12、【推荐】在一些场景下,考虑使用TIMESTAMP代替DATETIME

理解:TIMESTAMP可以表达1970-2038年,而且TIMESTAMP需要4字节存储空间,而DATETIME需要8字节,存储1001-9999年

13、【推荐】对于自动生成的schema不要太过信任,最好自己手动写

理解:对于一些数据库客户端不要过分信任。

索引规约

1、【强制】业务上具有唯一特性的字段,即使是多个字段的组合,也必须建成唯一索引

理解:不要以为唯一索引影响了 insert 速度,这个速度损耗可以忽略,但提高查找速度是明显的;另外,即使在应用层做了非常完善的校验控制,只要没有唯一索引,根据墨菲定律,必然有脏数据产生。

2、【强制】超过三个表禁止 join。需要 join 的字段,数据类型必须绝对一致;多表关联查询时,保证被关联的字段需要有索引

理解:多表的join操作会影响SQL的性能

3、【强制】在 varchar 字段上建立索引时,必须指定索引长度,没必要对全字段建立索引,根据实际文本区分度决定索引长度即可

理解:索引的长度与区分度是一对矛盾体,一般对字符串类型数据,长度为 20 的索引,区分度会高达 90%以上,可以使用 count(distinct left(列名, 索引长度))/count(*)的区分度来确定。

4、【强制】页面搜索严禁左模糊或者全模糊,如果需要请走搜索引擎来解决

理解:索引文件具有 B-Tree 的最左前缀匹配特性,如果左边的值未确定,那么无法使用此索引。

5、【推荐】如果有 order by 的场景,请注意利用索引的有序性。order by 最后的字段是组合索引的一部分,并且放在索引组合顺序的最后,避免出现 file_sort 的情况,影响查询性能

理解:组合索引的顺序会影响到SQL的查询性能

6、【推荐】利用覆盖索引来进行查询操作,避免回表

理解:覆盖索引只需要通过索引即可拿到所需的数据,而不再需要再次回表查询,提高了SQL的查询效率。

7、【推荐】利用延迟关联或者子查询优化超多分页场景

理解:MySQL 并不是跳过 offset 行,而是取 offset+N 行,然后返回放弃前 offset 行,返回N 行,那当 offset 特别大的时候,效率就非常的低下,要么控制返回的总页数,要么对超过特定阈值的页数进行 SQL 改写。

8、【推荐】建组合索引的时候,区分度最高的在最左边

理解:区分度最高的放左边,能够在一开始过滤掉很多无用数据,提高索引的效率。需要注意的是各个条件的顺序尽量和索引的顺序一致。

9、【推荐】防止因字段类型不同造成的隐式转换,导致索引失效

理解:详细细节可以参考这篇文章哦~MySQL索引(三)索引不生效的情况

SQL规约

1、【强制】不要使用 count(列名)或 count(常量)来替代 count(*),count(*)是 SQL92 定义的标准统计行数的语法,跟数据库无关,跟 NULL 和非 NULL 无关

理解:count(*)会统计值为 NULL 的行,而 count(列名)不会统计此列为 NULL 值的行。

2、【强制】使用 ISNULL()来判断是否为 NULL 值

理解:NULL与任何值直接比较都为NULL。

  1. NULL<>NULL返回结果是NULL,而不是false。
  2. NULL=NULL返回结果是NULL,而不是false。
  3. NULL&1返回结果是NULL,而不是true。

3、【强制】在代码中写分页查询逻辑时,若 count 为 0 应直接返回,避免执行后面的分页语句

理解:提高SQL的效率,避免不必要的无用查询。

4、【强制】不得使用外键与级联,一切外键概念必须在应用层解决

理解:外键会导致表与表之间耦合,UPDATE与DELETE操作都会涉及相关联的表,十分影响SQL的性能,甚至会造成死锁。

5、【强制】禁止使用存储过程,存储过程难以调试和扩展,更没有移植性

理解:避免不必要的维护。

6、【推荐】TRUNCATE TABLE 比 DELETE 速度快,且使用的系统和事务日志资源少,但 TRUNCATE无事务且不触发 trigger,有可能造成事故,故不建议在开发代码中使用此语句。

理解:TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同。

参考

  • 《阿里巴巴Java开发手册》
  • 实际工作结合《阿里巴巴Java开发手册》得出的理解

本文转自 https://blog.csdn.net/weixin_37686415/article/details/110276789?utm_medium=distribute.pc_relevant.none-task-blog-OPENSEARCH-6.control&dist_request_id=&depth_1-utm_source=distribute.pc_relevant.none-task-blog-OPENSEARCH-6.control,如有侵权,请联系删除。

收藏
评论区

相关推荐

postgresql和mysql哪个好
postgresql和mysql都是免费且功能强大的开源数据库,很多用户面对这两个库都会有一个问题,那就是哪一个才是最好的开源数据库,MySQL还是PostgreSQL呢?该选择哪一个开源数据库呢? postgresql和mysql哪个好 一.PostgreSQL相对于MySQL的优势 1、在SQL的标准实现上要比MySQL完善,而且功能实现比较严谨;
[DB]PostgreSQL 与 MySQL 相比,优势何在?
PostgreSQL 与 MySQL 相比,优势何在? 数据库 知乎 Pg 没有 MySQL 的各种坑 MySQL 的各种 text 字段有不同的限制, 要手动区分 small text, middle text, large text... Pg 没有这个限制, text 能支持各种大小. 按照 SQL 标准, 做 null 判断不能用
MySQL的语句执行顺序
今天遇到一个问题就是mysql中insert into 和update以及delete语句中能使用as别名吗?目前还在查看,但是在查阅资料时发现了一些有益的知识,给大家分享一下,就是关于sql以及MySQL语句执行顺序: sql和mysql执行顺序,发现内部机制是一样的。最大区别是在别名的引用上。  一、sql执行顺序 
解读阿里巴巴开发规范之MySQL
(https://blog.csdn.net/weixin_37686415/article/details/110276789?utm_mediumdistribute.pc_relevant.nonetaskblogOPENSEARCH6.control&dist_request_id&depth_1utm_sourcedistrib
golang实现MySQL数据库事物的提交与回滚
MySQL 事务主要用于处理操作量大,复杂度高的数据。在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。事务用来管理 insert,update,delete 语句,事务处理可以用来维护数据库的完整性,保证成批的 SQL 语句要么全部执行,要么全部不执行。 一般来说,事务是必须满足4个条件(ACID)::原子性(Atomicit
MySQL 8.0 创建 user 及允许远程连接
进入 mysql 命令行模式mysql h127.0.0.1 uroot p 查看当前 mysql 版本select version(); 查看当前 mysql 用户列表信息select host, user, authentication_string, plugin from user; 执行完上面的命令后会显示一个表格 查看表格
MySQL 5.7新特性:并行复制原理
众所周知,MySQL的复制延迟是一直被诟病的问题之一,在MySQL 5.7版本已经支持“真正”的并行复制功能,官方称为为enhanced multithreaded slave(简称MTS),因此复制延迟问题已经得到了极大的改进。总之,MySQL 5.7版本后,复制延迟问题永不存在。一、MySQL 5.6并行复制架构从MySQL 5.6.3版本开始
47 张图带你 MySQL 进阶!
我们在 MySQL 入门篇主要介绍了基本的 SQL 命令、数据类型和函数,在局部以上知识后,你就可以进行 MySQL 的开发工作了,但是如果要成为一个合格的开发人员,你还要具备一些更高级的技能,下面我们就来探讨一下 MySQL 都需要哪些高级的技能
21分钟 MySQL 入门教程
21分钟 MySQL 入门教程 目录 一、MySQL的相关概念介绍(c1) 二、Windows下MySQL的配置(c2) 配置步骤(d1) MySQL服务的启动、停止与卸载(d2) 三、
MySQL基础(非常全)
MySQL基础一、MySQL概述1、什么是数据库 ? 答:数据的仓库,如:在ATM的示例中我们创建了一个 db 目录,称其为数据库2、什么是 MySQL、Oracle、SQLite、Access、MS SQL Server等 ? 答:他们均是一个软件,都有两个主要的功能: a. 将数据保存到文件或内存 b. 接收特定的命令,然后对文件进行相应
MySQL(一)MySQL基础介绍
最近的学习内容是数据库相关的一些知识,主要以MySQL为主,参考书籍——《MySQL必知必会》MySQL学习及下载地址:https://dev.mysql.com/MySQL学习使用注意事项:1、必须访问一个已有的MySQL服务器,需要一个服务器账号(一个登录名和一个口令)2、MySQL运行在所有主要平台上,包括Windows、Linux、Solaris、M
Mysql Workbench使用教程
<1 MySQL WorkbenchMySQL Workbench 为数据库管理员、程序开发者和系统规划师提供可视化的Sql开发、数据库建模、以及数据库管理功能。 <2.MySQL Workbench 的下载和安装 (1)安装最新MySql时,有是否安装MySql Workbench的选项,可选择安装。 (2)可以独立安装MySql Workbench。
什么是索引?Mysql目前主要的几种索引类型
一、索引MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。打个比方,如果合理的设计且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是一个人力三轮车。索引分单列索引和组合索引。单列索引,即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引。组合索引,即一个索引包含多个列。创
一文读懂一条 SQL 查询语句是如何执行的
2001 年 MySQL 发布 3.23 版本,自此便开始获得广泛应用,随着不断地升级迭代,至今 MySQL 已经走过了 20 个年头。为了充分发挥 MySQL 的性能并顺利地使用,就必须正确理解其设计思想,因此,了解 MySQL 的逻辑架构是必要的。本文将通过一条 SQL 查询语句的具体执行过程来详细介绍 MySQL 架构中的各个组件。MySQL 逻辑架构
Python中如何判断表的存在
MySQL如何判断表是否存在MySQL判断表是否存在可以使用下面语句:select from informationschema.tables where tablename 'student';select from informationschema.tables where tablename 'teacher';SQL语句说明:informati