Jovry's blog
0%

高性能MySQL-Schema与数据类型优化

Posted on Edited on In , ,

1 选择优化的数据类型

  • ①、更小的通常更好：更小的数据类型通常更快，因为它们占用更少的磁盘、内存和CPU缓存，且处理时需要的CPU周期也更少。选择不会超过范围的最小类型。

  • ②、简单就好：简单数据类型的操作通常需要更少的CPU周期，例如：

    • a、整型比字符操作代价更低，因为字符集和校对规则（排序规则）使字符比较比整型比较更复杂。
    • b、应使用mysql内建的类型来存储时间和日期（date,time,datetime），而不是字符串来存储日期和时间。
    • c、应使用整型存储IP地址。

  • ③、避免使用null：最好使用列作为not null，除非真的需要存储null值，原因如下：

    • a、若查询中包含可为null的列，对MySQL来说很难优化，因为可为null的列使得索引、索引统计和值都更复杂。
    • b、可为null的列可能会使用更多的存储空间，在MySQL里需要特殊处理。
    • c、当可为null的列被索引时，每个索引记录需要一个额外的字节

（例外：InnoDB使用单独位（bit）存储null值，对于叙述数据有很好的空间效率，但不适用与MyISAM。）

为列选择数据类型的步骤？

  • ①、确定适合的大类型，例如：数字、字符串、时间等。
  • ②、选择具体的类型。（注：很多MySQL的数据类型可以存储相同类型的数据，只是存储的长度和范围不同、允许的精度不同，需要的物理空间（内存和磁盘）不同）。

例如：datetime和timestamp列都可以存储相同类型的数据，时间和日期，精确到秒。但timestamp只使用datetime一半的存储空间，并且会根据时区变化，具有特殊自动更新能力。另一方面，timestamp允许的时间范围要小很多，有时候它的特殊能力会成为障碍。

关于别名：Mysql支持很多别名，例如INTEGER,BOOL,以及NUMERIC。若建表使用的数据类型的别名，使用show create table检查时，会发现mysql报告的是基本类型，而不是别名。

1.1 整数类型

MySQL支持两种类型的数字：整数和实数。

其中,MySql整数类型有TINYINT，SMALLINT，MEDEIUMINT，INT，BIGINT。分别使用8，16，24，32，64位存储空间，其存储值得范围从-2^(N-1)到2^(N-1)-1，其中N是存储空间的位数。

关于UNSIGNED属性，表示不允许为负值，加上此属性，可以使正数的上限提高一倍。

（注：有符号和无符号类型使用相同的存储空间，并且具有相同的性能。）

Mysql可以为整数类型指定宽度。但它不会限制值的合法范围，只是规定Mysql的一些交互工具用来显示字符的个数。例如，对于存储和运算INT(1)和INT(20)是相同的。
（注：不限制合法范围只是针对整数类型，对于字符串类型等并不适用）

1.2 实数类型

实数是带有小数部分的数字。它不仅可以存储小数，还可以使用DECIMAL存储比BIGINT还大的整数类型。

Mysql既支持精确类型（指的decimal），也支持不精确类型（float及double类型）。

  • FLOAT和DOUBLE类型支持使用标准的浮点运算进行近似计算，其计算与所使用的平台浮点数计算有关。

  • DECIMAL类型用于存储精确的小数（但仅限于MySQL5.0及更高版本，其实现是由MYSQL服务器自身实现的。），MYSQL4.1之前的版本中，DECEIMAL只是一个“存储类型”。

浮点和decimal类型都可以指定精度。对于decimal列，可以指定小数点前后所允许的最大位数。

（Mysql5.0和更高的版本将数字打包保存到一个二进制字符串中，每4个字节存9个数字，实现参考 https://zhidao.baidu.com/question/565903719525546284.html

​ 32位的2进制能表示最大的十进制数字是4294967295，一共有10位；除了最高的十亿位，其他九个位都能够表示0-9；也就是说MySQL的decimal类型在保存大数时并不是将单个十进制数字映射成二进制数保存。

）

浮点类型在存储同样范围的值时，通常比DECIMAL使用更少的空间。float使用4个字节存储，double使用8个字节。MySQL使用double作为内部浮点计算的类型。

（因为decimal需要额外的开销，所以应该尽量只在对小数进行精确计算时才使用decimal，例如财务数据。在数据量比较大的时候，可以考虑使用bigint代替decimal，然后将需要存储的货币单位根据小数的尾数乘以相应的倍数。）

1.3 字符串类型

MySQL支持多种字符串类型，varchar和char类型是最主要字符串类型。其存储方式与存储引擎的具体实现有关。与varchar和char类似的类型还有binary和varbinary，他们存储的是二进制字符串。

1.3.1 varchar

varchar类型用于存储可变长字符串，是最常见的字符串数据类型。（它比定长类型更节省空间，因为它仅使用必要的空间。例外的情况，若MySQL表使用ROW_FORMAT=FIXED创建，则每一行都会使用定长存储。）

varchar需要使用1或2个额外的字节记录字符串的长度：若列的最大长度小于或等于255字节，则只使用1个字节表示，否则使用2个字节。

使用varchar(5)和varchar(200)存储‘hello‘的空间开销是一样的，那么使用短的列有什么优势吗？

MySQL通常会分配固定大小的内存块来保存内部值，因此更长的列会消耗更多的内存。尤其是使用内存临时表进行排序或操作时会特别糟糕，在利用磁盘临时表进行排序也同样糟糕。因此最好的策略是只分配真正需要的空间。

varchar类型的优缺点？

优点：节省存储空间，对性能有所帮助。

缺点：由于行时变长的，在update时可能使行变得比原来更长，这就需要做额外的工作。例如，若行占用的空间增长，在页内没有更多的空间可以存储，MyISAM会拆成不同的片段存储，InnoDB则需要分裂页来使行可以放进页内。

那些场景下使用varchar类型是合适的？

  • ①、字符串列的最大长度比平均长度大很多。
  • ②、列的更新很少，所以碎片不是问题。
1.3.2 char

char类型是定长的，MySQL总是根据定义字符串长度分配足够的空间。

那些场景下使用char类型是合适的？

  • ①、char适合存储很短的字符串；（例如：用char（1）来存储只有Y和N的值，采用单字节字符集只需要一个字节，但是varchar却需要两个字节，因为还需要另一个用于记录长度的额外的字节）
  • ②、存储所有值都接近同一个长度的字符串；（例如，存储密码的MD5值，因为这是一个定长的值。）
  • ③、存储经常变更的数据。
1.3.3 BLOB和TEXT类型

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

  • BLOB类型采用二进制方式存储，没有排序规则或者字符集，其家族包含：TINYBLOB、SMALLBLOB、BLOB、MEDIUMBLOB、LONGBLOB。
  • TEXT类型采用字符串方式存储，有字符集合排序规则，其家族包含：TINYTEXT、SMALLTEXT、TEXT、MEDIEMTEXT、LONGTEXT。

相对于MySQL的其他类型，BLOB和TEXT有什么不同点？

  • ①、BLOB和TEXT只对每个列的最前max_sort_length字节而不是整个字符串做排序，若只排序前面一小部分字符，可以减小max_sort_length的配置，或者使用ORDER BY SUSTRING(column,length)。
  • ②、不能将BLOB和TEXT列全部长度的字符串进行索引，也不能使用这些索引消除排序。
1.3.4 使用枚举（ENUM）代替字符串类型

有时候可以使用枚举列代替常用的字符串类型。枚举列可以把一些不重复的字符串存储成一个预定义的集合。MySQL在存储枚举时非常紧凑，会根据列值的数量压缩到一个或者两个字节中。

MySQL在内部会将每个值在列表中的位置保存为整数，并且在表的.frm文件中保存“数字-字符串”映射关系的“查找表”。

示例:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
# 建表
create table enum_test (
    e enum('fish', 'apple', 'dog') not null
);

# 写数据
insert into enum_test(e) values ('fish'),('dog'),('apple');

# 枚举值实际存储的是整数,而不是字符串.
mysql> select e+0 from enum_test;
+-----+
| e+0 |
+-----+
|   1 |
|   3 |
|   2 |
+-----+
3 rows in set (0.03 sec)

# 枚举字段排序时按照内部存储的整数,而不是定义的字符串进行排序的.
mysql> select e from enum_test order by e;
+-------+
| e     |
+-------+
| fish  |
| apple |
| dog   |
+-------+
3 rows in set (0.07 sec)

注:

  • a. 应该避免使用数字作为enum枚举常量.

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

枚举类型的缺点?

  • 字符串列表是固定的,添加或删除字符串必须使用alter table.因此,对于一系列未来可能会改变的字符串,枚举并不是一个好主意.

  • 每个枚举值保存为整数,并且必须进行查找才能转换为字符串,所以枚举列有一些开销.

枚举类型的优点？

  • 转换后让表的大小及主键大小缩小了。

1.4 日期和时间类型

MySQL可以使用许多类型来保存日期和时间值，例如，YEAR和DATE。MySQL能存储的是最小时间粒度为秒。

MySQL提供两种相似的日期类型：DATETIME和TIMESTAMP。

1.4.1 DATETIME

DATETIME将日期和时间封装到格式为YYYYMMDDHHMMSS的整数中，与时区无关。使用8个字节的存储空间。默认情况下，MySQL以一种可排序的、无歧义的格式显示DATETIME值，例如：“2018-01-16 22:37:08”。

1.4.2 TIMESTAMP（推荐）

TIMESTAMP类型保存了1970年1月1日以来的秒数，它与Unix时间戳相同。使用4个字节的存储空间。只能表示从1970到2038年。

MySQL提供了以下两个函数，对时间戳和日期进行转化。

  • FROM_UNIXTIME()：把Unix时间戳转换为日期。
  • UNIX_TIMESTAMP()：把日期转换为Unix时间戳。

TIMESTAMP显示的值依赖于时区，MySQL服务器、客户端连接都有时区设置。

MySQL存储的时间都是以秒为粒度，若需要存储以秒为更小粒度的日期和时间值应该怎么处理？

  • ①、可以使用BIGINT类型存储微秒级别的时间戳。

  • ②、可以使用DOUBLE存储秒之后的小数部分。

  • ③、可以使用MariaDB替代MySQL。

1.5 位数据类型

MySQL有少数几种存储类型使用紧凑的位存储数据，所有这些位类型，不管底层存储格式和处理方式如何，从技术上来说都是字符串类型。

1.5.1 BIT

可以使用Bit列在一列中存储一个或多个true/false值，BIT列最大长度为64个位。例如，BIT(1)定义一个包含单个位的字段，BIT(2)存储2个位。

注：MySQL将BIT当做字符串类型，而不是数字类型。当检索BIT(1)的值，结果是一个包含二进制0或1的字符串，而不是ASCII码的“0”或者“1”。在和数字上下文的场景中检索时，结果将是位字符串转换成的数字。

例如：若存储一个值b‘00111001’到BIT(8)的列并检索它，得到的内容是字符码57的字符串（即ASCII码的字符“9”），在数字上下文场景中，得到的是数字57.

1
2
3
4
5
6
7
8
9
10
11
12
13
# 创建测试表。
create table bittest(a bit(8));
# 写入数据。
insert bittest values (b'00111001');
# 查询字符串场景和数字场景的结果。
mysql> select a, a + 0 from bittest;
+------+-------+
| a    | a + 0 |
+------+-------+
| 9    |    57 |
+------+-------+
1 row in set (0.03 sec)

注：应该避免使用BIT类型，若需要一个bit的存储空间存储一个true/false的值，可以创建一个可以为空的char(0)的列，该列可以保存空值（null）或者长度为0的字符串（空字符串）。

1.5.2 SET

若需要保存很多true/false的值，可以考虑合并这些列的到一个SET数据类型，它在MySql内部是以一系列打包的位的集合来表示的。MySQL提供了FIND_IN_SET()和FIELD()函数，方便地查询。

缺点：改变列的定义代价较高，需要alter table，这对大表来说是非常昂贵的操作。并且，也无法在SET列上通过索引查询。

SET的改变操作代价较高，有什么替代的解决方案吗？

一种替代SET的方式是使用一个整数包装一系列的位。

例如：可以把8个位包装到一个TINYINT中，并且按位操作来使用。可以在应用中为每个位定义名称常量来简化这个操作。

示例：实现保存权限的访问控制列表（ACL）,每个没或者SET元素代表一个值，列入CAN_READ、CAN_WRITE或CAN_DELETE。

  • ①、使用SET列来存储。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 建表
create table acl (
  perms set('CAN_READ','CAN_WRITE','CAN_DELETE') not null  
);
# 写数据
insert into acl(perms) values ('CAN_READ,CAN_DELETE');
# 查询
mysql> select perms from acl where find_in_set('CAN_READ',perms);
+---------------------+
| perms               |
+---------------------+
| CAN_READ,CAN_DELETE |
+---------------------+
1 rows in set (0.00 sec)
  • ②、使用整数列来存储。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# 定义字段值。
set @CAN_READ := 1<<0,
    @CAN_WRITE := 1<<1,
    @CAN_DELETE := 1<<2;
# 建表
create table acl_int (
    perms tinyint unsigned not null default 0
);
# 写数据
insert into acl_int(perms) values (@CAN_READ + @CAN_DELETE);
# 查询
mysql> select perms from acl_int where perms & @CAN_READ;
+-------+
| perms |
+-------+
|     5 |
+-------+
1 row in set (0.07 sec)

1.6 选择标识符（identifier）

选择标识符的小技巧：

  • ①、整数类型：整数通常是标识列最好的选择，因为他们很快并且可以使用auto_increment.

  • ②、尽量不要选择enum和set类型：enum和set列适合存储固定信息，例如有序的状态、产品类型、人的性别。

  • ③、尽量避免使用字符串类型作为标识符：因为字符串类型很消耗空间，并且通常比数字类型慢。（对于MyISAM

默认对字符串使用压缩索引，会使查询变得更慢。）

2 范式和反范式

2.1 范式

MySQL的三大范式：

  • 第一范式：确保数据表中每列（字段）的原子性，没有冗余属性；
  • 第二范式：在第一范式的基础上，目标是确保表中的每列和主键相关。
  • 第三范式：在第二范式的基础上，确保表中和列和主键直接相关，而不是间接相关。

示例：下表为“雇员、部门、部门领导”的示例。

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

缺点：若此时Say Brown接管Accounting部门的领导，需要修改多行来反应这个变化。

范式化方式为拆分雇员和部门项，以两张表存储。

1
2
3
4
5
6
7
8
9
10
11
12
13
# 雇员表
EMPLOYEE_NAME DEPARTMENT
Jones Accounting 
Smith Engineering 
Brown Accounting 
Green Engineering 

# 部门表
DEPARTMENT HEAD 
Accounting Jones 
Engineering Smith 
Accounting Jones 
Engineering Smith
2.1.1 范式的优缺点

优点：

  • 范式化的更新操作通常比反范式化要快；
  • 当数据较好的范式化时，就只有很少或者没有重复数据，所以只需要修改更少的数据。
  • 范式化的表通常更小，可以更好地放在内存里，所以执行操作会更快。
  • 很少有多余的数据，意味着检索列表数据时更少需要DISTINCT或者GROUP BY语句。

缺点：

  • 通常需要关联。稍微复杂一些的查询语句在符合范式的shcema上都可能需要至少一次关联，或者更多。（该问题可以通过在业务上实现关联）

2.2 反范式

反范式化指的是通过增加冗余或重复的数据来提高数据库的读性能。

2.2.1 反范式的优缺点

优点：

  • 反范式化的schema因为所有数据都在一张表中，可以更好的避免关联。
  • 若不需要关联，则对于大部分查询最差的情况（全表扫描），可以避免随机I/O，会比关联的情况快很多。
  • 一张表可以更有效的使用索引策略。

缺点：

​ 修改一个数据，可能需要修改多行来反映。

3 加快alter table操作的速度

MySQL执行大部分修改表结构的操作的方法是用新的结构创建一个空表，从旧表中查出所有数据插入新表，然后删除旧表。在数据量大的情况下，很耗时。

大部分alter table操作将导致MySQL服务中断。

对于常见的场景，技巧有：

  • ① 先在一台不提供服务的机器上执行alter table操作，然后和提供服务的主库进行切换。

  • ② “影子拷贝”，影子拷贝用要求的表结构创建一张和源表无关的新表，然后通过重命名和删表操作交换两张。或这是用工具完成拷贝工作。

其余内容见参考资料1，懒得写了。

参考资料

  1. 高性能MySQL_第三版

  2. MySQL三大范式和反范式