MySQL篇-Schema和数据类型优化

选择更优的数据类型

  1. 尽量选择存储空间更小的数据类型

空间更小占用的磁盘空间会更小,更加节省空间

  1. 数据类型尽量简单

可以选择简单的数据类型就不要选择复杂的,例如可以用INT存储数据就不要用DOUBLE或者FLOAT存储,这没有任何意义

  1. 尽量不要使用NULL值列

因为NULL会给索引带来额外的存储空间、在SQL查询中更难优化

整数类型

类型 占用空间(BIT)
TINYINT 8
SMALLINT 16
MEDIUMINT 24
INT 32
BIGINT 64

一般会选择BIGINT作为主键列的列类型,注意:即使在创建列时指定数据长度,也无法改变占用空间。例如INT(11)INT(20)都是占用32位存储空间,指定数据长度只能够在显示结果的时候起作用

在运算操作中,一般会使用BIGINT数据类型,即使在32位计算机中也如此。

实数类型

实数类型包括了小数部分,在MySQL中主要有三种数据类型:FLOAT、DOUBLE、DECIMAL

数据类型 占用空间(Byte)
FLOAT 4
DOUBLE 8
DECIMAL 动态指定

FLOAT和DOUBLE都是采用近似浮点运算,有可能出现精度损失问题;DECIMAL是精确存储数字的类型,在运算中可以不损失精度,一般用于精确数值运算场景-例如财务数据;DECIMAL类型每9个数字占用4个字节。

DECIMAL可以动态指定数据长度,例如:DECIMAL(18, 9)代表保留小数点前9位、后9位,存储空间为9个字节,小数点占1个字节。

注意:CPU本身不支持浮点数的精确运算,DECIMAL的精确运算在MySQL服务器中实现,而运算速度比CPU的浮点运算慢,即FLOAT和DOUBLE的运算比DECIMAL快

字符串类型

数据类型 特点
CHAR 定长、MySQL5.0开始去除结尾空格
VARCHAR 变长、MySQL5.0开始保留结尾空格、额外占用1~2个字节标识数据长度

选用VARCHAR的标准:

  1. 数据的长度差异较大
  2. 不确定数据的长度
  3. 每个字符占用的字节数不一致,例如UTF-8编码下的字符可能占用1~2个字节

选用CHAR的标准:

  1. 数据的长度较一致,例如长度相同的MD5散列码值
  2. 数据长度很短的列,例如存储’Y’或’N’,因为VARCHAR需要额外1个字节存储数据长度

VARCHAR类型在更新时可能因为数据长度变得比原来更长,磁盘没有足够的存储空间存储该值,导致页分裂;而CHAR类型相对稳定,因为长度一致。

VARCHAR类型有可能导致内存碎片,它在存储值时如果数据长度未满,则会浪费一些存储空间,所以定义VARCHAR的数据长度需要谨慎考虑,以免产生大量内存碎片。例如VARCHAR(20)VARCHAR(255)存储最大长度为15个字节的字符串,那么VARCHAR(255)就非常奢侈了。

日期和时间类型

数据类型 特性
TIMESTAMP 与时区有关,日期范围为1970~2038年,占用4个字节,默认为NOT NULL
DATETIME 与时区无关,保存日期时间,格式YYYYMMHHMMSS,日期范围1000-9999年,占用8个字节

建议使用TIMESTAMP而不是DATETIME,因为在存储空间上占有优势,TIMESTAMP可以利用FROM_UNIXTIME()转换为日期,UNIX_TIMESTAMP()转换为时间戳,也可以在应用程序级别上进行两者的转换输出。

由于TIMESTAMP默认是NOT NULL,如果在插入时没有指定时间戳,会采用当前的时间戳存储

位数据类型

数据类型 特性
BIT 最大占用64位,动态指定长度,保存二进制串
SET 一系列打包的位数据类型的集合,无法使用索引

在开发中应尽量少或者非常谨慎的使用BIT,原因如下:

  1. 字符串上下文中,BIT查询到的结果是ASCII码对应的字符,而在数字上下文中,显示的是ASCII码本身的值

例如:

1
2
3
CREATE TABLE bittest(a bit(8));
INSERT INTO bittest (a) VALUES (b'00111001');
SELECT a, a + 0 FROM bittest;
a a + 0
9 57

SET可以用于存储多个BIT的值,例如(‘dog’, ‘fish’, ‘chicken’),但是带来的代价是修改表开销非常大,所以有另外一种更好的方式替代SET-利用整数的每一位代表不同的含义。

例如:使用TINYINT,占用8位,’dog’采用最低位,’fish’采用次低位,’chicken’采用次次低位

含义 整数值
dog 1
fish 2
chicken 4
dog、chicken 5

这样做的好处:

  1. 如果要表示新的含义,不用频繁地修改表
  2. 可以在应用程序级别上表示各个值的含义

类似的Linux操作文件的权限:

  1. READ:4
  2. WRITE:2
  3. EXECUTE:1

那么也可以使用TINYINT标识某个用户的执行权限

含义 整数值
READ、WRITE、EXECUTE 7
READ 4
READ、WRITE 6
EXECUTE 1
······ ······

选择标识列

通常有两种选择:整数类型、字符串类型

整数类型:

  1. 最好采用AUTO_INCREMENT,这有利于提高索引查找的效率,因为相邻的数据在磁盘上相隔不远,大概率在同一个磁盘页内;

字符串类型:

  1. 由于字符串类型是无序的,在索引分布上不均匀,根据主键查找数据时效率非常低,如果使用字符串做主键,最好采用UUID()生成主键,因为在某种程度上UUID生成的字符串也是有序的,只不过程度很低
  2. 插入时,查找插入的位置也要比整数类型效率低下,原因也是因为索引分布不均匀

范式和反范式的使用

先看看什么是三范式:

  1. 第一范式满足每一列都是不可再分的、最小的原子列
  2. 第二范式满足非主属性完全依赖于主属性
  3. 第三范式满足所有非主属性都直接依赖于主属性,没有传递依赖

数据库三大范式实例

经典的“雇员-部门-领导”表:

EMPLOYEE_ID EMPLOYEE DEPARTMENT HEAD
1 Jones Accounting Jones
2 Smith Engineering Smith
3 Brown Accounting Jones
4 Green Engineering Smith

该表存在如下问题:

  1. 如果员工不存在,部门和领导的信息也没法显示
  2. 如果员工JonesBrown的部门领导显示不一致,就不知道哪个是正确的了

所以上面的表结构符合第一范式,第一范式有可能出现的问题就是:删除异常、修改异常

范式化

将上面的表“升级”,得到的结果如下

“员工-部门”表:

EMPLOYEE_ID EMPLOYEE DEPARTMENT_ID
1 Jones 1
2 Smith 2
3 Brown 1
4 Green 2

“部门-领导”表:

DEPARTMENT_ID DEPARTMENT HEAD
1 Accounting Jones
2 Engineering Smith

解决了上面的两个问题了:

  1. 这里的DEPARTMENT_ID作为外键关联列,只要DEPARTMENT或者HEAD作修改,都不会产生一致性问题
  2. 即使删除所有的EMPLOYEE也不会丢失部门信息

上面的表结构符合第二范式(实际上也满足第三范式),在很多情况下符合第二范式已经很不错了,范式化的缺点有:

  1. 范式越高,查询的代价越大,造成过多的表关联,第三范式即是如此
  2. 关联查询在一些情况下使索引失效,全表扫描

范式化的优点有:

  1. 每张表的内存较小,数据操作更快
  2. 更新操作更容易、代价更小
  3. 很少出现冗余数据,例如第一范式存在DEPARTMENTHEAD冗余

反范式化

“USER”用户表:

USER_ID USER_NAME ACCOUNT_TYPE
1 Jhon premiumv
2 Jack guest
3 Sam guest
4 Mike premiumv

“MESSAGE”消息表:索引列(PUBLISHED)

MESSAGE_ID MESSAGE_TEXT PUBLISHED USER_ID
1 HELLO, I AM JHON! 2020-01-01 12:00:00 1
2 HELLO, IT’S SAD TODAY! 2020-01-02 23:11:12 1
3 I AM SORRY TO HEAR THAT 2020-01-03 09:00:00 2
4 BYE BYE! 2020-01-03 10:00:00 3
5 I AM PREMIUMV! 2020-01-04 9:33:33 4

要查看付费用户的最近的10条消息,可以用关联查询:

1
2
3
4
5
6
7
SELECT MESSAGE_TEXT, USER_NAME
FROM USER
INNER JOIN MESSAGE
ON USER.USER_ID = MESSAGE.USER_ID
WHERE ACCOUNT_TYPE = 'preminmv'
ORDER BY PUBLISHED DESC
LIMIT 10;

但是这会扫描USER表中的所有用户,判断是不是付费用户preminmv,效率极低,即使调换查询顺序也是一样的道理,此时可以在MESSAGE表中增加两个冗余字段ACCOUNT_TYPEUSER_NAME,建立索引(PUBLISHED, ACCOUNT_TYPE),只需要查询MESSAGE表,且可以利用组合索引扫描,效率提高很多。

1
2
3
4
5
SELECT MESSAGE_TEXT, USER_NAME
FROM MESSAGE
WHERE ACCOUNT_TYPE='preminmv'
ORDER BY PUBLISHED DESC
LIMIT 10;

反范式化大部分都是通过添加冗余字段达到目的,优点是:

  1. 避免关联查询,可以使用组合索引提高查询效率

缺点是:

  1. 字段冗余(这看起来很搞笑)

混用范式化和反范式化

完全的范式化导致查询的昂贵代价、完全的反范式化导致插入异常和删除异常,折中的办法就是两者混用:

  1. 如果在某个查询中代价过大,加入冗余字段可以减轻查询的代价(参考反范式化)
  2. 如果存在插入异常和删除异常,果断需要范式化(参考范式化)
0%