写点什么

数据库的 schema 与数据类型优化

作者:阿柠xn
  • 2022 年 9 月 15 日
    陕西
  • 本文字数:4540 字

    阅读完需:约 15 分钟

schema 与数据类型优化

首先我们介绍一下这个 schema:


schema(发音 “skee-muh” 或者“skee-mah”,中文叫模式)是数据库的组织和结构

选择优化的数据类型

mysql 支持的数据类型非常多,选择正确的数据类型对于获得高性能至关重要。不管存储那种类型的数据,下面几个简单的原则你需要记住。


  • 更小的通常更好

  • 简单就好

  • 尽量避免 null

整数类型

我们有以下几种整数类型:



他们的存储空间范围从-2(N-1)次方到 2 的(N-1)次方减一。N 是位数


整数类型有可选的 unsigned 属性,表示不允许负值,这大致可以使正数的上限提高一倍。

实数类型

实数嘛,就是带有小数部分的数字,然而,它不只是为了存储小数部分;也可以使用 decimal 存储比 bigint 还大的整数。


decimal(18,9)小数点两边各存储 9 个数字,一个使用 9 个字节;小数点前的数字使用 4 个字节,小数点后的数字使用 4 个字节,小数点本身占一个字节。


因为需要额外的空间和计算开销,所以应该尽量只在对小数进行精确计算时才使用 decimal--例如存储财务数据,但是在数据量比较大的时候们可以使用 bigint 代替 decimal,将存储的数据根据小数的位数乘以相应的倍数即可。

字符串类型

varchar


用于存储可变长的字符串,如果 MySQL 使用 ROW_FORMAT=FIXED 创建的话,每一行都会使用定长存储,这会很浪费空间。


varchar 需要使用 1 个或者 2 个额外的字节记录字符串的长度。例如:varchar(10)的列需要 11 个字节的存储空间。


但是由于行是变长的在 update 的时候可能使行变得比原来更长,这就导致需要额外的工作。例如 myisam 会将行拆成不同的片段存储,innodb 则需要分裂页来使行可以放进页内。


char


char 类型是定长的,当存储 char 值时。mysql 会删除所有的末尾空格。


char 适合存储很短的字符串,或者所有值都接近同一个长度。例:char 十分适合存储密码的 md5 值。


对于经常变更的数据,char 也比 varchar 好,因为定长的 char 类型不容易产生碎片。


BLOB 和 TEXT


都是为了存储很大的数据而设计的字符串数据类型,分别采用二进制和字符方式存储。


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


BLOB 和 TEXT 家族之间仅有的不同是 BLOB 类型存储的是二进制数据,没有排序规则或字符集,而 text 类型有字符集和排序规则。


使用枚举 enum 代替字符串类型


有时候可以使用枚举列来代替常用的字符串类型。枚举列可以把一些不重复的字符串存储成一个预定义的集合。mysql 在存储枚举时非常紧凑,会根据列表值的数量压缩到一个或者两个字节中。mysql 内部会将每个值在列表中的位置保存为整数,并且在表的.frm 文件中保存“数字-字符串‘映射关系的查找表。


我们看看下面的例子:


create TABLE  enum_test(        e ENUM('fish','apple','dog') NOT NULL);
INSERT INTO enum_test(e) VALUES ('fish'),('dog'),('apple');
复制代码


SELECT e + 0 FROM enum_test;
复制代码



SELECT e FROM enum_test;
复制代码



所以使用数字作为 enum 枚举常量,这种双重性很容易导致混乱,例如 enum(’1‘,’2‘,’3‘)。所以尽量别这么用。


另外一个让人大吃一惊的事情是:



枚举字段是按照内部存储的整数而不是定义的字符串进行排序的。


枚举最不好的地方是,字符串列表是固定的,添加或删除字符串必须使用 ALTER TABLE 。因此对于一系列未来可能改变的字符串,枚举并不是一个好主意。

日期和时间类型

DATETIME


这个类型可以保存大范围的值,从 1001 年到 9999 年,精度为秒,他把日期和时间封装到格式为 YYYYMMDDHHMMSS 的整数中,与时区无关。使用 8 个字节的存储空间。


TIMRSTAMP


就像它的名字一样 TIMESTAMP 类型保存了从 1970 年 1 月 1 日午夜(格林尼治标准时间)以来的秒数。它和 unix 时间戳相同。timestamp 值使用 4 个字节的存储空间,因此他的范围比 datetime 小的多。只能表示从 1970 到 2038 年 MySQL 提供了 FROM_UNIXTIME()函数把 Unix 时间戳转换为日期,并且提供 Unix_TIMESTAMP()函数把日期转换为 Unix 时间戳。

位数据类型

BIT


mysql 把 bit 当做字符串类型,而不是数字类型,当检索 bit(1)的值时,结果是一个包含二进制 0 或 1 的字符串,而不是 ascii 码的 0,1。


SET


如果需要保存很多 true/false 值,可以考虑合并这些列到一个 set 数据类型,他在 mysql 内部是以一系列打包的位的集合来表示的。这样可以有效的利用空间,并且 MySQL 有像 FIND_IN_SET()和 FIELD()这样的函数,方便地在查询中使用。


它的主要缺点是改变列的定义的代价较高:需要 alter TABLE,这对大表来说是非常昂贵的操作。

选择标识符

特殊类型数据

MySQL schema 设计中的缺陷

太多的列


MySQL 的存储引擎 API 工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据,然后在服务器层将缓冲内容解码成各个列。从行缓冲中将编码过的列转换成行数据结构的代价是十分大的。而转换的代价依赖与列的数量。当我们研究一个 CPU 占用非常高的案例时,发现客户使用了非常宽的表,然而只有一小部分的列会实际用到,这时候转换的代价就非常高了。


MySQL 限制了每个关联操作最多只能有 61 个表,一个粗略的经验,如果希望查询执行的快速且并发性好,单个查询最好在 12 个表以内做关联。


全能的枚举


注意放置过度使用枚举


你别一个枚举,举了个数字全集出来,那就不礼貌了。


变相的枚举


枚举列允许在列中存储一组定义值中的单个值,集合 set 列则允许在列中存储一组定义值中的一个或多个值。


比如


create TABLE 。。。 (is_default set('Y','N') NOT NULL default 'N'
复制代码


这里我们需要注意到这个真假的情况是不会同时出现的,那么我们就应该毫无疑问的使用枚举而不是这个 set。


非此发明的 null


我们之前写了避免使用 null 的好处,并且建议尽可能的考虑替代方案。比如我们可以用 0,或者一些特殊字符去代替 null。


但是遵循这一原则也不要走极端。当确实需要表示未知值时也不要害怕使用 null。

范式和反范式

​ 范式:


范式是符合某一种级别的关系模式的集合。关系数据库中的关系必须满足一定的要求,满足不同程度要求的为不同范式。

第一范式(1NF)

在任何一个关系数据库中,第一范式(1NF) [2] 是对关系模式的基本要求,不满足第一范式(1NF)的数据库就不是关系数据库。

第二范式(2NF)

是在第一范式(1NF)的基础上建立起来的,即满足第二范式(2NF)必须先满足第一范式(1NF)。第二范式(2NF)要求数据库表中的每个[实例]或行必须可以被唯一地区分。为实现区分通常需要为表加上一个列,以存储各个实例的唯一标识。这个唯一属性列被称为[主关键字]或主键、主码。

范式的优点和缺点

优点:


  • 范式化的更新操作比反范式化的更新要快

  • 当数据较好的范式化,就只有很少或者较少的重复数据,所以只需要修改更是少的数据。

  • 范式化的表通常更小,可以更好的放在内存里,所以执行的操作会更快。

  • 很少的重复数据也就意味着在 select 时我们会更少的使用 distinct 或者 group by 语句。


缺点:


  • 需要关联

反范式化的优点和缺点

反范式化的 schema 因为所有的数据都在一张表中,所以很好的避免了关联。

混用范式化和反范式化

最常见的反范式化数据的方法就是复制或者缓存,在不同的表里存储相同的特定列。我们还可以使用触发器更新缓存值,这使得实现这样的方案变得更简单。

缓存表和汇总表

有时候提升性能的最好方法是在同一张表中保存衍生的冗余数据。然而,有时也需要创建一张完全独立的汇总表或缓存表。


我们用术语缓存表来表示存储那些可以比较简单的从 schema 其他表获得的数据的表。而术语汇总表,则保存的是使用 group by 语句聚合数据的表。


我们使用汇总表,要远比我们扫描表的全部行要有效的多。


缓存表则相反,其对优化搜索和检索查询语句很有效。这些查询语句经常需要特殊的表和索引结构。例如:可能会需要很多不同的索引组合来加速各种类型的查询。这些矛盾的需求有时候要创建一张只包含主表中部分列的缓存表。一个有用的技巧是我吗可以使用不同的存储引擎。比如说,主表使用 innodb,我吗可以把 myisam 作为缓存表的引擎,这样会得到更小的索引占用空间,并且可以做全文搜索。


在使用缓存表和汇总表的时候,我吗必须决定到底是实时维护数据还是定期重建。那个更好依赖于应用程序,但是定期重建并不只是节省资源,也可以保持表不会有那么多的碎片,以及有完全顺序组织的索引。


当然为了安全 ,我们还会在重建这些表的时候使用一个影子表,来保证数据在操作过程也是可以使用的。

物化视图

计数器表

计数器表是一个经常会用到的东西,我们使用单独的表可以帮助避免查询缓存失效。


下面我们要展示呢一些更高级的技巧:


你比如说,我们有一个计数器表,是记录这个网站的点击次数的这样一个表,但我们每次修改的时候都会有一个全局的互斥锁,这也就导致了这些事务只能串行执行。我们要是想获得更好的性能,就可以将计数器保存在多行,每次随机选择一行进行更新。我们对这个计数表这样更新:


CREATE TABLE hit_counter(slot tinyint unsigned not null primary key ,cnt int unsigned not null)ENGINE = InnoDB
复制代码


我们预先在表中增加 100 行数据,选择一个随机的槽进行更新:


UPDATE hit_counter SET cnt = cnt +1 WHERE slot = RAND()*100;
复制代码


要统计结果,我们就使用下面这样的聚合查询:


SELECT SUM(cnt) FROM hit_counter; 
复制代码


:umbrella:我们一个常见的需求是每隔一段时间开始一个新的计数器,我们这样修改表:


CREATE TABLE daily_hit_counter(day date not null,slot tinyint unsigned not null,cnt int unsigned not null,primary key (day,slot))ENGINE = InnoDB;
复制代码


这样的话我们就不要去预先生成行,而用 on duplicate key update 语句(存在就更新,不存在那就插入)


INSERT INTO daily_hit_counter(day,slot,cnt)VALUES (CURRENT_DATE,RAND()*100,1)ON DUPLICATE KEY UPDATE cnt = cnt + 1;
复制代码


如果希望减少表的行数,避免表变得太大,可以写一个周期执行的任务,合并所有结果到 0 号槽,并且删除所有其他的槽:


UPDATE daily_hit_counter as c    INNER JOIN (    SELECT day,SUM(cnt)AS cnt,MIN(slot)AS mslot    FROM daily_hit_counter    GROUP BY day    )AS x USING(day)SET  c.cnt = IF(c.slot = x.mslot,x.slot,0),    c.slot = IF (c.slot = x.mslot,0,c.slot);
复制代码


DELETE FROM daily_hit_counter WHERE slot <>0 AND cnt = 0;
复制代码

加快 alter TABLE 操作的速度

MySQL 对于大表的 alter TABLE 一直是一个大问题。mysql 执行大部分的修改表的结构操作的方法是用新的结构创建一个空表,然后把旧表里的数据插入到新表。


对于常见的场景,能使用的场景只有两种:


  • 先在一台不提供服务的机器上执行 ALTER TABLE 操作,然后和提供服务的主库进行切换

  • 影子拷贝:用要求的表结构创建一张和源表无关的新表,然后通过重命名和删表操作交换两张表。


不是所有的 alter TABLE 操作都会引起表重建。例如,有两个方法可以改变或者删除一个列的默认值(一种方法很快,一种很慢)。


慢的方式:


ALTER TABLE sakila.film MODIFY COLUMN rental_duration TINYINT(3) NOT NULL DEFAULT 5;
复制代码


这种方式是比较慢的,因为 modify 这种方式是要导致表的重建的。


ALTER TABLE sakila.film ALTER  COLUMN rental_duration  SET DEFAULT 5;
复制代码


这种 alter 的方式就很快,因为他是直接修改.firm 文件而不涉及表数据。所以这个操作是特别快的。

只修改.frm 文件

快速创建索引

发布于: 1 小时前阅读数: 4
用户头像

阿柠xn

关注

还未添加个人签名 2022.08.29 加入

还未添加个人简介

评论

发布
暂无评论
数据库的schema与数据类型优化_数据库_阿柠xn_InfoQ写作社区