《高性能MySQL》读书笔记表的设计与管理
dd
  2023-07-01 23:30:33 2023-07-01 23:30   2023-08-05 18:57:16      2.6k 字  9 分钟  

选择优化的数据类型

MySQL支持的数据类型非常多，选择正确的数据类型对于获得高性能至关重要

选择原则：

  • 更小的通常更好：尽量使用能够正确存储和表示数据的最小数据类型，占用的空间更少，处理需要的CPU周期更少
  • 简单为好：简单的数据类型处理所需的CPU周期比较少
    • 整型数据比字符型数据的比较操作代价更低，因为字符集和排序规则(collation)使字符型数据的比较更复杂
    • 将日期和时间存储为MySQL的内置类型而不是字符串类型
    • 用整型数据存储IP地址
  • 尽量避免存储****NULL：NULL的列使索引、索引统计和值比较都更复杂，且NULL的列会占用更多的存储空间
    • 但是在调优时将NULL改为NOT NULL带来的性能提升比较小

选择步骤：

  1. 确定合适的大类型：数字、字符串、时间等
  2. 选择具体类型：MySQL数据类型很多可以存储相同类型的数据，但是在存储的值范围、精度、占据空间上有着不同

整数类型

MySQL中整数有以下几种类型：

  • TINYINT：8位，存储值范围：-2$$^7 $$~ 2$$^7$$-1
  • SMALLINT：16位，存储值范围：-2$$^{15} $$~ 2$$^{15} $$-1
  • MEDIUMINT：24位，存储值范围：-2$$^{23} $$~ 2$$^{23} $$-1
  • INT：32位，存储值范围：-2$$^{31} $$~ 2$$^{31} $$-1
  • BIGINT：64位，存储值范围：-2$$^{63} $$~ 2$$^{63} $$-1

整数类型有可选的UNSIGNED属性，表示不允许负值，这大致可以使正数的上限提高一倍。例如，TINYINT UNSIGNED可以存储的值的范围是0～255，而TINYINT的值的存储范围是-128～127

不同的整数类型占据的空间是不同的，但是整数计算通常使用BIGINT，除了一些聚合函数

同时MySQL可以为整数类型指定宽度，比如INT(11)，但是不会限制值的范围，依旧是32位，例如INT(11)和INT(20)是相同的，只是规定了MySQL的一些交互工具中用于显示字符的个数

实数类型

MySQL中实数有以下几种类型：

  • DECIMAL：提供精确类型
  • FLOAT & DOUBLE：不精确，是近似计算，前者占据4字节，后者占据8字节

MySQL中我们可以选择不同的实数类型，但是浮点类型的内部计算是使用DOUBLE

注意：DECIMAL由于精确计算需要额外的空间和计算成本，性能要求比较高。建议只在需要精确计算的时候才使用。同时对于一些大容量场景，可以考虑用BIGINT代替DECIMAL，将需要存储的货币单位根据小数的位数乘以相应的倍数即可。假设要存储财务数据并精确到万分之一分，则可以把所有金额乘以一百万，然后将结果存储在BIGINT里，这样可以同时避免浮点存储计算不精确和DECIMAL精确计算代价高的问题

字符串类型

MySQL支持多种字符串数据类型，每种类型还有许多变体。每个字符串列可以有自己的字符集和该字符集的排序规则集

VARCHAR & CHAR

在磁盘和内存中的存储方式在不同的存储引擎下是不同的，下面以InnoDB为准。同时同种存储引擎在内存中存储二者的方式可能和磁盘上存储方式不同

VARCHAR

用于存储可变长度的字符串

会额外使用字节记录字符串的长度：

  • 列的长度<=255字节：用1字节表示
  • 列的长度>255字节：用2字节表示

优点：只使用必要的空间，所以比固定长度的类型更加节省空间

缺点：行是可变长度，在更新时可能会增长，会导致额外的工作。如果行的增长使原位置无法容纳更多内容，不同存储引擎处理方式不同。InnoDB可能需要分割页面来容纳行，其他存储引擎可能不在原数据位置更新数据

使用场景：

  • 字符串列的最大长度远大于平均长度
  • 列的更新少
  • 使用类似UTF-8的复杂字符集，每个字符都用不同的字节数存储

varchar分配是否越多越好？比如使用varchar(5)和varchar(200)存储’hello’的空间开销是一样的，那么使用更短的列有什么优势？

较大的列会使用更多的内存，MySQL会在内部分配固定大小的内存块保存值，也就是按照最长的方式在内存中分配空间。比如排序是varchar(200)是按照200这个长度来进行的，不合理的长度会浪费空间。

所以最合理的策略是按需分配

CHAR

是固定长度的，MySQL会为定义的字符串长度分配足够的空间。当存储CHAR值时，MySQL删除所有尾随空格。如果需要进行比较，值会用空格填充

使用场景：

  • 所有值的长度几乎都相同的情况（比如用户密码的MD5值）
  • 经常修改的数据（不易出现碎片）
  • 非常短的列（varchar还需要额外的字节记录长度）

BLOB & TEXT

用于存储很大的数据，分别用于二进制存储与字符方式存储

当BLOG & TEXT的值太大是，InnoDB会使用外部存储区域进行存储，此时每个值在行内只需要记录外部存储区域的地址即可

排序方式：只对列的最前max_sort_length字节排序，而不是整个字符串

MySQL不能将BLOB和TEXT数据类型的完整字符串放入索引，也不能使用索引进行排序

一般不建议使用BLOB或TEXT存储类似图像等数据，会导致表过大同时修改查询表这些操作变慢。一般是使用单独的对象数据存储，然后在表中跟踪对应的位置或文件名（类似文件存储在fastDFS中，然后在MySQL表中记录文件在FastDFS中的位置）

使用枚举代替字符串类型

ENUM列可以存储一组预定义的不同字符串值，MySQL在存储枚举时用会整数进行表示，而不是字符串，使得占用空间压缩到1~2字节

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
CREATE TABLE enum_test(
    e ENUM('fish','apple','dog') NOT NULL
);
INSERT INTO enum_test(e) VALUES('fish'),('dog'),('apple');

mysql> select * from enum_test;                                                                                                                
+-------+                                                                                                                                      
| e     |                                                                                                                                      
+-------+                                                                                                                                      
| fish  |                                                                                                                                      
| dog   |                                                                                                                                      
| apple |                                                                                                                                      
+-------+                                                                                                                                      
3 rows in set (0.00 sec)                                                                                                                       
                                                                                                                                               
mysql> select e + 0 from enum_test;                                                                                                            
+-------+                                                                                                                                      
| e + 0 |                                                                                                                                      
+-------+                                                                                                                                      
|     1 |                                                                                                                                      
|     3 |                                                                                                                                      
|     2 |                                                                                                                                      
+-------+                                                                                                                                      
3 rows in set (0.00 sec)

enum字段是根据内部整数值排序的，而不是根据字符串

1
2
3
4
5
6
7
8
9
mysql> select e from enum_test order by e;                                                                                                     
+-------+                                                                                                                                      
| e     |                                                                                                                                      
+-------+                                                                                                                                      
| fish  |                                                                                                                                      
| apple |                                                                                                                                      
| dog   |                                                                                                                                      
+-------+                                                                                                                                      
3 rows in set (0.00 sec)

可以通过按照需要的顺序指定ENUM成员来解决这个问题。也可以在查询中使用FIELD()函数显式地指定排序顺序，但这会导致MySQL无法利用索引消除排序

1
2
3
4
5
6
7
8
mysql> select e from enum_test order by FIELD(e,'apple','dog','fish');                                                                         
+-------+                                                                                                                                      
| e     |                                                                                                                                      
+-------+                                                                                                                                      
| apple |                                                                                                                                      
| dog   |                                                                                                                                      
| fish  |                                                                                                                                      
+-------+

MySQL将枚举值存储为整数，在查找时需要根据整数去获取对应的字符串。这一部分的开销通常可以被enum列的小尺寸所抵消

使用时需要考虑的地方：

  • enum值是否会过多
  • enum值是否需要经常修改（会导致表经常变更）

日期和时间类型

MySQL提供了两种类型用于实现同时存储日期和时间

DATETIME

可以保存大范围的数值，从1000年到9999年，精度为1微秒。它以YYYYMMDDHHMMSS格式存储压缩成整数的日期和时间，且与时区无关。这需要8字节的存储空间

可排序、无歧义

TIMESTAMP

存储自1970年1月1日格林尼治标准时间(GMT)午夜以来经过的秒数——与UNIX时间戳相同。TIMESTAMP只使用4字节的存储空间，所以它的范围比DATETIME小得多：只能表示从1970年到2038年1月19日。时间戳显示的值依赖于时区

默认情况下，当插入/更新一行记录时没有指定第一个TIMESTAMP列的值，MySQL会将该列的值设置为当前时间

使用时的考虑因素

  • 都需要处理服务器和客户端上的时区问题
  • TIMESTAMP会有2038年限制
  • 存储空间
  • 时间精度

通过将日期和时间存储为UNIX纪元（即自1970年1月1日以来的秒数），以协调世界时(UTC)的形式，可避免MySQL处理的复杂性，这一做法越来越流行。使用带符号的32位INT，可以表达直到2038年的时间。使用无符号的32位INT，可以表达直到2106年的时间。如果使用64位，还可以超出这些范围

特殊数据类型

某些类型的数据并不直接对应于可用的内置类型。IPv4地址就是一个很好的例子。人们通常使用VARCHAR(15)列来存储IP地址。然而，它们实际上是32位无符号整数，而不是字符串。用小数点将地址分成四段的表示方法只是为了让人们阅读容易，所以应该将I P地址存储为无符号整数。MySQL提供了INET_ATON()和INET_NTOA()函数来在这两种表示形式之间进行转换。使用的空间从VARCHAR(15)的约16字节缩减到无符号32位整数的4字节。如果你担心数据库的可读性，不想继续使用函数查看行数据，请记住MySQL有视图，可以使用视图来简化数据查看的复杂性。

Schema设计陷阱

  • 太多的列：服务器在转换行格式时会消耗过大的CPU
  • 太多的联接
  • 过度使用枚举
  • 警惕NULL值
    • 上面已经讲过避免使用NULL
    • 当需要表示未知值时，不要太害怕使用NULL。在某些情况下，使用NULL比使用某个虚拟常数更好。避免使代码复杂化，引入bug。例如DATETIME设置为0000-00-00 00:00:00可能会导致一些问题
  • 本文标题：《高性能MySQL》读书笔记表的设计与管理
  • 本文作者：dd
  • 创建时间：2023-07-01 23:30:33
  • 本文链接：2023/07/01/表的设计与管理/
  • 版权声明：本博客所有文章除特别声明外，均采用 BY-NC-SA 许可协议。转载请注明出处！
  1. 选择优化的数据类型
    1. 整数类型
    2. 实数类型
    3. 字符串类型
      1. VARCHAR & CHAR
        1. VARCHAR
        2. CHAR
      2. BLOB & TEXT
      3. 使用枚举代替字符串类型
    4. 日期和时间类型
      1. DATETIME
      2. TIMESTAMP
      3. 使用时的考虑因素
    5. 特殊数据类型
  2. Schema设计陷阱