阿里-Java开发手册手记(mysql数据库)

基于Java开发手册 2020泰山版

文章目录

建表规约

表达是与否概念的字段,必须使用 is_xxx 的方式命名,数据类型是 unsigned tinyint

  • 任何字段如果为非负数,必须是 unsigned

表名、字段名必须使用小写字母或数字,禁止出现数字开头,禁止两个下划线中间只出现数字

  • MySQL 在 Windows 下不区分大小写,但在 Linux 下默认是区分大小写。因此,数据库名、表名、字段名,都不允许出现任何大写字母,避免节外生枝

小数类型为 decimal,禁止使用 float 和 double

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

如果存储的字符串长度几乎相等,使用 char 定长字符串类型

varchar 是可变长字符串,不预先分配存储空间,长度不要超过 5000,如果存储长度大于此值,定义字段类型为 text,独立出来一张表,用主键来对应,避免影响其它字段索引效率

表必备三字段:id, gmt_create, gmt_modified

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

单表行数超过 500 万行或者单表容量超过 2GB,才推荐进行分库分表

合适的字符存储长度,不但节约数据库表空间、节约索引存储,更重要的是提升检索速度

  • 无符号值可以避免误存负数,且扩大了表示范围
类型字节表示范围
tinyint unsigned1无符号值: 0-255
smallint unsigned2无符号值: 0-65535
int unsigned4无符号值: 0-43亿
bigint unsigned8无符号值: 0-10的19次方

索引规约

业务上具有唯一特性的字段,即使是组合字段,也必须建成唯一索引

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

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

  • 即使双表 join 也要注意表索引、SQL 性能

在 varchar 字段上建立索引时,必须指定索引长度,没必要对全字段建立索引,根据实际文本区分度决定索引长度

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

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

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

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

  • where a=? and b=? order by c; 索引:a_b_c

建组合索引的时候,区分度最高的在最左边

  • 存在非等号和等号混合判断条件时,在建索引时,请把等号条件的列前置。如:where c>? and d=? 那么即使 c 的区分度更高,也必须把 d 放在索引的最前列,即建立组合索引 idx_d_c

防止因字段类型不同造成的隐式转换,导致索引失效

SQL 语句

不要使用 count(列名)或 count(常量)来替代 count(),count()跟数据库无关,跟 NULL 和非 NULL 无关

  • count(*)会统计值为 NULL 的行,而 count(列名)不会统计此列为 NULL 值的行

count(distinct col) 计算该列除 NULL 之外的不重复行数,注意 count(distinct col1, col2) 如果其中一列全为 NULL,那么即使另一列有不同的值,也返回为 0

当某一列的值全是 NULL 时,count(col)的返回结果为 0,但 sum(col)的返回结果为NULL,因此使用 sum()时需注意 NPE 问题

  • 避免 sum 的 NPE 问题SELECT IFNULL(SUM(column), 0) FROM table;

使用 ISNULL()来判断是否为 NULL 值

  • NULL 与任何值的直接比较都为 NULL

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

不得使用外键与级联,一切外键概念必须在应用层解决

  • 学生表中的 student_id 是主键,那么成绩表中的 student_id 则为外键。如果更新学生表中的 student_id,同时触发成绩表中的 student_id 更新,即为级联更新。外键与级联更新适用于单机低并发,不适合分布式、高并发集群;级联更新是强阻塞,存在数据库更新风暴的风险;外键影响数据库的插入速度

禁止使用存储过程,存储过程难以调试和扩展,更没有移植性

SQL 语句中表的别名前加 as,并且以 t1、t2、t3、…的顺序依次命名

  • 别名可以是表的简称,或者是根据表出现的顺序,以 t1、t2、t3 的方式命名。2)别名前加 as 使别名更容易识别

因国际化需要,所有的字符存储与表示,均采用 utf8 字符集,那么字符计数方法需 要注意

SELECT LENGTH("轻松工作"); 返回为 12
SELECT CHARACTER_LENGTH("轻松工作"); 返回为 4

ORM 映射

POJO 类的布尔属性不能加 is,而数据库字段必须加 is_,要求在 resultMap 中进行字段与属性之间的映射

sql.xml 配置参数使用:#{},#param# 不要使用${} 此种方式容易出现 SQL 注入

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值