0


MySQL进阶实战1,数据类型与三范式

目录

一、选择优化的数据类型

MySQL支持的数据类型非常多,选择正确的数据类型对于获得高性能至关重要。

1、更小的

一般情况下,应该尽量使用较小的数据类型,更小的数据类型通常更快,因为占用更少的磁盘、内存和CPU缓存,处理时需要的CPU周期更短。

2、更简单的

简单的数据类型通常需要更少的CPU周期,整形比字符串类型代价更低,因为字符集和校验规则使字符比较比整形比较更复杂。

3、尽量避免NULL

很多表都包含可为NULL的列,即使应用程序并不需要保存NULL也是如此,因为可为NULL是列的默认属性,通常情况下,最好指定列为NOT NULL。

如果查询中包含可为NULL的列,对MySQL来说更难优化,因为可为NULL的列使索引、索引统计和值的比较都更复杂。可为NULL的列会使用更多的存储空间,在MySQL里也需要特殊处理,可为NULL的列被索引时,每个索引记录需要一个额外的字节,在MyISAM里甚至还可能导致固定大小的索引变成可变大小的索引。

4、datetime和timestamp

datetime

timestamp

都可以存储时间和日期,都精确到秒,但是

timestamp

只是用

datetime

一半的存储空间,并且

timestamp

会根据时区变化,具有特殊的自动更新能力。

二、整形类型

1、整形

如果存储整形,可以使用

tinyint、smallint、mediumint、int、bigint

。分别使用8、16、24、32、64位存储空间。它们可以存储的值的范围从

-2^(n-1)^

2^(n-1)^-1

,其中N是存储空间的位数。

整形类型有可选的

unsigned

属性,表示不允许负值,这大致可以使正数的上限提升一倍。例如

tinyint unsigned

可以存储的范围是

0~255

,而

tinyint

的存储范围是

-128 ~ 127

有符号和无符号类型使用的是相同的存储空间,具有相同的性能,因此可以根据实际情况选择合适的类型。

2、实数

实数是带有小数部分的数字,然而,它们不只是为了存储小数部分,还可以使用

decimal

存储比

bigint

还大的数字。

float

double

类型支持使用标准的浮点计算进行近似计算。

float

使用4个字节存储,

double

使用8个字节存储,相比

float

有更高的精度和更大的范围。

因为需要额外的空间和计算开销,应该尽量只在对小数进行精确计算时才使用

decimal

,例如存储财务数据,但在数据量比较大的时候,可以使用

bigint

代替

decimal

,将需要存储的货币单位根据小数的位数乘以相应的倍数即可,这样可以避免浮点数存储计算不精确和

decimal

精确计算代价高的问题。

三、字符串类型

1、varchar

varchar是可变长的字符串,是最常见的字符串数据类型。它比定长类型char更省空间,因为它仅适用必要的空间。varchar需要使用1~2个额外字节记录字符串的长度,如果列的最大长度小于等于255字节,使用1个字节表示,否则使用2个字节。

2、char

char类型是定长的,MySQL总是根据定义的字符串长度分配足够的空间,char值会根据需要采用空格进行长度填充。

  1. char适合存储很短的字符串或者都是一个长度的字符串,比如MD5值;
  2. 对于经常变更的数据,char也比varchar更友好,定长的char类型不容易产生碎片;
  3. 对于较短的列,char也比varchar更有效率,因为不用额外的1字节记录长度。

3、blob和text类型

  • blob采用二进制存储,没有排序规则和字符集;
  • text采用字符方式存储,有排序规则和字符集;

MySQL会把每个blob和text值当做一个独立的对象处理。存储引擎在存储是通常会做特殊处理。当blob或text的值太大时,InnoDB会使用专门的外部存储空间进行存储,此时每个值在行内需要1~4个字节存储一个指针,然后在外部存储区域存储实际的值。

尽量避免对blob或text字段进行排序,可以通过substring()方法截取一小部分字符进行排序,也不能将blob或text列全部长度的字符串进行索引。

四、三范式

第一范式:每个表的每一列都要保持它的原子性,也就是表的每一列是不可分割的;
第二范式:在满足第一范式的基础上,每个表都要保持唯一性,也就是表的非主键字段完全依赖于主键字段;
第三范式:在满足第一范式和第二范式的基础上,表中不能产生传递关系,要消除表中的冗余性;

五、范式的优缺点

1、优点

  1. 范式化的更新操作通常比反范式化要快;
  2. 当数据较好地范式化后,就只有很少或者没有重复数据,所以只需要修改更少的数据;
  3. 范式化的表通常更小,可以更好地存放在内存中,执行操作更快;
  4. 很少有冗余的数据,意味着检索数据时更少使用distinct或group by语句;

2、缺点

  1. 查询时,由于分表的原因,常常需要表关联;
  2. 可能无法触发索引;

六、反范式的优缺点

1、优点

  1. 由于都在一个表里,可以减少表关联查询;
  2. 可能同属于一个索引的字段,不用分表,触发索引更方便;

2、缺点

  1. 存储过多的冗余数据
  2. 查询时可能需要distinct或group by的情况更多;

在实际的开发中,都是混用范式化和反范式化,有的时候必要的冗余,有益而无害。

MySQL进阶实战系列文章

SQL性能优化的21个小技巧

mysql索引详解

MySql基础知识总结(SQL优化篇)

哪吒精品系列文章

Java学习路线总结,搬砖工逆袭Java架构师

10万字208道Java经典面试题总结(附答案)

SQL性能优化的21个小技巧

Java基础教程系列

Spring Boot 进阶实战
在这里插入图片描述

标签: 数据库 mysql sql

本文转载自: https://blog.csdn.net/guorui_java/article/details/127182496
版权归原作者 哪 吒 所有, 如有侵权,请联系我们删除。

“MySQL进阶实战1,数据类型与三范式”的评论:

还没有评论