设计性能更优MySQL数据库schema

MySQL
395
0
0
2023-03-10
MySQL正则表达式regexp_replace函数的用法实例
上一篇
MySQL正则表达式匹配查询(含实例)
下一篇
标签   MySQL优化
目录
  • 数据类型优化
  • 选择优化的数据类型
  • 整数类型
  • 实数类型
  • 字符串类型
  • 日期和时间类型
  • 位数据类型
  • MySQL schema设计中的缺陷
  • 范式和反范式
  • 范式的优点和缺点
  • 反范式化的优点和缺点
  • 混用范式化和反范式化
  • 缓存表和汇总表
  • 物化视图
  • 计数器表
  • 加快alter TABLE操作的速度

数据类型优化

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

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

选择优化的数据类型

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

  • 更小的通常更好
  • 简单就好
  • 尽量避免null

整数类型

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

TINYINT

SMALLINT

MEDIUMINT

INT

BIGINT

8位

16位

24位

32位

64位

他们的存储空间范围从-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;

☔️我们一个常见的需求是每隔一段时间开始一个新的计数器，我们这样修改表：

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文件而不涉及表数据。所以这个操作是特别快的。

MySQL正则表达式regexp_replace函数的用法实例
上一篇
MySQL正则表达式匹配查询(含实例)
下一篇