MySQL数据库schema的设计

举报
阿柠 发表于 2022/09/27 15:44:25 2022/09/27
【摘要】 MySQL schema设计中的缺陷太多的列MySQL的存储引擎API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据,然后在服务器层将缓冲内容解码成各个列。从行缓冲中将编码过的列转换成行数据结构的代价是十分大的。而转换的代价依赖与列的数量。当我们研究一个CPU占用非常高的案例时,发现客户使用了非常宽的表,然而只有一小部分的列会实际用到,这时候转换的代价就非常高了。MySQL限制...

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文件

快速创建索引

【版权声明】本文为华为云社区用户原创内容,转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息, 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。