Schema 与数据类型优化

参考内容: 《高性能 MySQL(第三版))》

选择优化的数据类型

世面上常见的数据库大多支持了多种多样的数据类型,选择正确的数据类型对于获得高性能至关重要,一般都需要遵循如下的几个原则:

1、更小的通常更好:更小的通常更快,因为占用着更少的磁盘、内存和 CPU,并且处理时需要的 CPU 周期也更少;
2、简单就好:简单数据类型的操作通常需要更少的 CPU 周期;
3、尽量避免 NULL:如果查询中包含可为 NULL 的列,就会使得索引、索引统计和值比较变得复杂,因此在设计表是最好指定列为 NOT NULL。

整数类型

在 MYSQL 中可以为整数类型指定宽度,例如INT(11),但是这对大多数应用是没有意义的,它不会限制值的合法范围,只是规定了 MySQL 的一些交互工具(如 MySQL 命令行客户端)用来显示字符的个数。对于存储和计算来说INT(1)INT(20)是相同的。

字符串类型

需要注意的是当 MySQL 存储 CHAR 值时,它会删掉所有的末尾空格,因为 CHAR 值会根据需要采用空格进行填充以方便比较,这导致的问题就是你使用 CHAR 存储的'string '会变成'string'。CHAR 的好处在于它是定长的,很适合存储像 MD5 值一样的定长值,定长值的 CHAR 类型不易产生碎片,而且对于非常短的列 CHAR 也会比 VERCHAR 好,比如CHAR(1)只需要一个字节,而VERCHAR(1)则需要两个字节,因为它还需要一个字节来存长度。

VERCHAR 类型在存储可变长字符串时,会比 CHAR 更节省空间,它需要使用 1 或者 2 个额外的字节记录字符串的长度。但由于行是变长的,当一个行占用的空间增长,并且在页内没有更多的可用空间可以存储,就容易产生碎片。

使用枚举代替字符串

有时候可以使用枚举列代替常用的字符串类型,枚举列可以把一些不重复的字符串存储成一个预定义的集合,而且 MySQL 在存储枚举时非常紧凑,会根据列的数量压缩到一个或两个字节。比如下面的例子:

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;

# result
+-----+
| e+0 |
+-----+
|   1 |
|   2 |
|   3 |
+-----+

可以看到使用枚举类型后,上面三行数据实际上存储为了整数,而不是字符串,而且还有一个让人吃惊的地方:枚举字段是按照内部存储的整数而不是定义的字符串进行排序的,这一点需要特别注意,不然在写程序时容易中犯错。当然你也可以在查询时使用FIELD()函数显式地指定排序顺序。

可以看到上面

范式和反范式

关系型数据库有设计范式的概念,这一点在大学的数据库课程中肯定都会提及。因为有比较高的范式,那么就只有很少或者没有重复的数据,因此在 UPDATE 时只需要修改更少的数据;高范式的表通常也更小,因此占用的内存也会更小,操作起来也会更快......

但是高范式也带来了另一个缺点,比较好的范式通常意味着需要关联,稍微复杂一点的查询就需要使用 JOIN,关联的代价是昂贵的,甚至让一些索引策略失效;而如果不需要关联,即使某个查询需要全表扫描,当数据比内存大时可能会比关联查询快的多。所以一般都会根据实际情况将范式与反范式混用,完全的范式化和完全的反范式化都是实验室才有的东西。

缓存表和汇总表

这里的「缓存表」和「汇总表」并没有什么标准的含义。我们用「缓存表」来存储那些可以从其他表获取,但是获取的速度很慢的数据;而「汇总表」则用来保存那些使用 GROUP BY 语句聚合数据的表。毫无疑问,我们存这些冗余数据也是为了性能。

比如近两年各种应用流行的年终报告,每次网易云音乐的年终报告都会把朋友圈撑满,其它类似于缓存一个用户的朋友数、一个文件的下载次数等等。这些数据实时计算的开销是很大的,而且多数情况下用户也等不起实时计算的时间,一般的解决方案都是通过增加计数器表(缓存表)来解决这个问题。

计算机科学中总是伴随着双面性,上面的计数器表带来性能提升的同时也带来了并发问题。网站的每一次点击都会导致对计数器的更新,对于任何想要更新这一行的事务来说,这条记录都有一个全局的互斥锁,这会使得这些事务只能串行的进行。每一次点击都会触发下面的语句,但大量的点击伴随着该行数据的互斥锁,想想性能也不会提升到哪里去吧。

UPDATE hit_counter SET cnt = cnt + 1;

大多数应用都是读查询居多,为了提升读查询的速度,经常会需要增加一些额外的索引,增加冗余列、汇总表、缓存表等等。但是不要忘了这些技巧也会增加写查询的负担,还会增加开发难度,因此应该根据实际应用场景来做权衡。

加快 ALTER TABLE 表的速度

MySQL 执行大部分修改表结构的方法都是用新的结构创建一个空表,然后从旧表中查出所有数据插入到新表,然后删除旧表。在内存不足、表很大、索引多的情况下会花费很长的时间。一个很严重的缺点是大部分 ALTER TABLE 操作将导致 MySQL 服务中断。

对于常见的场景我们有两种技巧避免服务中断。一种是先在一台不提供服务的机器上执行 ALTER TABLE 操作,然后和提供服务的主库进行切换;另一种技巧是「影子拷贝」,即用要求的表结构创建一张和源表无关的新表,然后通过重命名和删除表的操作交换两张表。

数据库MySQL