Java开发手册之MySQL数据库
创始人
2024-05-30 23:53:15
0

MySQL数据库

  • 建表规约
  • 索引规约
  • SQL语句
  • ORM映射

建表规约

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

说明:任何字段如果为非负数,必须是unsigned。

正例:表达逻辑删除的字段名 is_deleted,1 表示删除,0 表示未删除。
2. 表名、字段名必须使用小写字母或数字,禁止出现数字开头,禁止两个下划线中间只出现数字。
数据库字段名的修改代价很大,因为无法进行预发布,所以字段名称需要慎重考虑。

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

正例:aliyun_admin,rdc_config,level3_name
反例:AliyunAdmin,rdcConfig,level_3_name
4. 表名不使用复数名词。

说明:表名应该仅仅表示表里面的实体内容,不应该表示实体数量,对应于 DO 类名也是单数形式,符合表达习惯。

  1. **禁用保留字,**如 desc、range、match、delayed 等,请参考 MySQL 官方保留字。
  2. 主键索引名为 pk_字段名;唯一索引名为 uk_字段名;普通索引名则为 idx_字段名。
    说明:pk_ 即 primary key;uk_ 即 unique key;idx_ 即 index 的简称。
  3. 小数类型为 decimal,禁止使用 float 和 double。

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

  1. 如果存储的字符串长度几乎相等,使用 char 定长字符串类型
  2. varchar 是可变长字符串,不预先分配存储空间,长度不要超过 5000,如果存储长度大于此值,定义字段类型为 text,独立出来一张表,用主键来对应,避免影响其它字段索引效率。
  3. 表必备三字段:id, gmt_create, gmt_modified

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

索引规约

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

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

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

说明:即使双表 join 也要注意表索引、SQL 性能。

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

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

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

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

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

正例:where a=? and b=? order by c; 索引:a_b_c
反例:索引中有范围查找,那么索引有序性无法利用,如:WHERE a>10 ORDER BY b; 索引a_b 无法排序。

SQL语句

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

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

  1. count(distinct col) 计算该列除 NULL 之外的不重复行数,注意 count(distinct col1, col2) 如果其中一列全为 NULL,那么即使另一列有不同的值,也返回为 0。
  2. 当某一列的值全是 NULL 时,count(col)的返回结果为 0,但 sum(col)的返回结果为NULL,因此使用 sum()时需注意 NPE 问题。
    正例:可以使用如下方式来避免 sum 的 NPE 问题:SELECT IF(ISNULL(SUM(g)),0,SUM(g)) FROM table;
  3. 使用 ISNULL()来判断是否为 NULL 值。

说明:NULL 与任何值的直接比较都为 NULL。
1) NULL<>NULL 的返回结果是 NULL,而不是 false。
2) NULL=NULL 的返回结果是 NULL,而不是 true。
3) NULL<>1 的返回结果是 NULL,而不是 true。

  1. 在代码中写分页查询逻辑时,若 count 为 0 应直接返回,避免执行后面的分页语句。
  2. 不得使用外键与级联,一切外键概念必须在应用层解决

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

  1. 禁止使用存储过程,存储过程难以调试和扩展,更没有移植性。
  2. 数据订正时,删除和修改记录时,要先 select,避免出现误删除,确认无误才能执行更新语句。

ORM映射

  1. 在表查询中,一律不要使用 * 作为查询的字段列表,需要哪些字段必须明确写明。

说明:
1)增加查询分析器解析成本。
2)增减字段容易与 resultMap 配置不一致。

  1. POJO 类的布尔属性不能加 is,而数据库字段必须加 is_,要求在 resultMap 中进行字段与属性之间的映射。
    说明:参见定义 POJO 类以及数据库字段定义规定,在中增加映射,是必须的。
    在 MyBatis Generator 生成的代码中,需要进行对应的修改。
  2. 不要用 resultClass 当返回参数,即使所有类属性名与数据库字段一一对应,也需要定义;反过来,每一个表也必然有一个与之对应。
    说明:配置映射关系,使字段与 DO 类解耦,方便维护。
  3. sql.xml 配置参数使用:#{},#param# 不要使用${} 此种方式容易出现 SQL 注入。
  4. BATIS 自带的 queryForList(String statementName,int start,int size)不推荐使用。

说明:其实现方式是在数据库取到statementName对应的SQL语句的所有记录,再通过subList 取 start,size的子集合。

正例:
Map map = new HashMap(); 
map.put("start", start); 
map.put("size", size); 
  1. 不允许直接拿 HashMap 与 Hashtable 作为查询结果集的输出。
    说明:resultClass=”Hashtable”,会置入字段名和属性值,但是值的类型不可控。

  2. 更新数据表记录时,必须同时更新记录对应的 gmt_modified 字段值为当前时间。

相关内容

热门资讯

官方通报疑医生暴力接生致婴儿残... 转自:北京时间 【#官方通报疑医生暴力接生致婴儿残疾#】...
华泰人寿高层大“换血” 记者丨林汉垚编辑丨肖嘉7月10日,华泰人寿保险股份有限公司(以下简称“华泰人寿”)任命牛增亮为公司总...
三地三代科研人接力种出新滋味   7月的河北沽源,千亩生菜迎来采收高峰,从田间直供全国肯德基餐厅。与此同时,一筐筐新鲜蔬菜在鲜切菜...
巨轮智能涨2.41%,成交额5... 7月11日,巨轮智能盘中上涨2.41%,截至13:46,报8.08元/股,成交5.26亿元,换手率3...
井松智能涨2.14%,成交额2... 7月11日,井松智能盘中上涨2.14%,截至13:46,报18.58元/股,成交2674.65万元,...
灿瑞科技涨2.02%,成交额2... 7月11日,灿瑞科技盘中上涨2.02%,截至13:47,报32.36元/股,成交2404.54万元,...
寻蓟城觅燕都   ▌谭烈飞 著  北京什么时候从中国北方的军事重镇发展成为都城的呢?公元前的周武王时期,北京地区的...
以太坊基金会发文阐述以太坊全面... 吴说获悉,以太坊基金会博客发布文章,阐述以太坊全面采用零知识证明(ZK),首步为部署 L1 zkEV...
佰维存储涨2.04%,成交额3... 7月11日,佰维存储盘中上涨2.04%,截至13:49,报67.05元/股,成交3.90亿元,换手率...
“杨少华剪彩饭店暂停营业”?平... 来源:中国青年报微信公号7月9日,相声演员杨少华去世,享年94岁。噩耗传出后,多位网友都在社交平台发...