• MySQL性能优化(二):选择优化的数据类型

良好的设计是高性能的基石,应该根据系统的实际业务需求、使用场景进行设计、优化、再调整,在这其中往往需要权衡各种因素,例如,数据库表究竟如何划分、字段如何选择合适的数据类型等等问题。

blog-thumb

良好的设计是高性能的基石,应该根据系统的实际业务需求、使用场景进行设计、优化、再调整,在这其中往往需要权衡各种因素,例如,数据库表究竟如何划分、字段如何选择合适的数据类型等等问题。

MySQL 支持的数据类型非常之多,对于选择恐惧症的小伙伴而言,苦不可言。大部分人在创建数据库表时,基本一股脑的使用 INTVARCHAR 这两种类型最多,至于长度,则会选择足够大即可,避免日后不够用咋办。只顾当时一时爽,之后坑谁谁难受。

如果你是一个追求极致、高效的开发者,对于上面的情况肯定是不愿让其发生的。在众多的数据类型面前,如何选择正确的数据类型,对于高性能是至关重要的。本文将介绍如何选择优化的数据类型,来提高 MySQL 的性能,将会选取最为常用的类型进行说明,便于在实际开发中创建表、优化表字段类型时提供帮助。

一、选择原则

不管存储哪种类型的数据,下面几个简单的原则将有助于你做出更好的选择。

1.更小的通常更好

一般情况下,应该尽可能选择正确存储数据的最小数据类型。更小的数据类型通常更快,因为它们占用更少的磁盘空间、内存,并且处理时需要的CPU周期更少。

但是,在选择更小数据类型时,一定不要低估存储值的范围,因为后期修改数据类型及长度是一件非常痛苦、耗时的操作。如果无法确定哪个数据类型是最好的,就选择你认为不会超过范围的最小类型。

2.简单就好

简单的数据类型操作通常需要更少的 CPU 周期。例如,整型比字符操作代价更低,因为字符集和校队规则(如:排序规则)使得字符比较比整型比较更复杂。

3.尽量避免用 NULL

NULL 是在常见不过的值了,通常都习惯对某些字段设置默认值为 NULL,这其实是一种非常不好的习惯。如果查询中的字段值恰巧是设置的 NULL 值,对 MySQl 来说更难优化,因为可为 NULL 的字段使得索引、值比较都更复杂。

NULL 值不能进行索引,影响索引的统计信息,影响优化器的判断。复合索引中只要有一列含有 NULL 值,那么这一列对于此复合索引就是无效的。

二、字符串类型

字符串类型是数据库中使用频率最高的数据类型,VARCHARCHAR 是两种最主要的字符串类型,都可以用来存储字符串,但它们保存和检索的方式不同。VARCHAR 属于可变长度的字符类型,而 CHAR 属于固定长度的字符类型。下面是关于这两种类型的说明、比较。

1.VARCHAR

VARCHAR 类型用于存储可变长字符串,它比定长类型更节省空间,因为它仅使用必要的空间(例如,越短的字符串使用最少的空间)。

VARCHAR 需要使用 1 或 2 个额外的字节来记录字符串的长度(如果字段的最大长度小于或等于 255 字节,则只使用 1 个字节表示长度,否则使用 2 个字节来表示长度)。例如,一个 VARCHAR(10) 的字段需要 11 个字节的存储空间,VARCHAR(1000) 则需要 1002 个字节的存储空间,其中需要 2 个字节来存储长度。

2.CHAR

CHAR 类型是定长的。当数据类型为 CHAR 时,MySQL 会删除所有的末尾空格。

CHAR 类型适合存储很短的字符串,或者所有值都接近同一个长度。例如,CHAR 类型非常适合存储密码的 MD5 值,因为这是一个定长的值。对于经常变更的数据,CHAR 类型也比 VARCHAR 类型更好,因为定长的 CHAR 类型不容易产生碎片。对于存储非常短的列,CHAR 类型比 VARCHAR 在存储空间上更有效率。例如,用 CHAR(1) 来存储只有Y和N的值,如果采用 VARCHAR(1) 却需要 2 个字节,因为还会有一个记录长度的额外字节。

通过下面具体例子来对 CHAR 进行说明,有助于更好的理解。这里创建一张只有一个 CHAR(10) 字段的表 char_test,并往里面插入三个字符串 xcbeyond,注意前后有空格的区别:

mysql> create table char_test(ch char(10));
Query OK, 0 rows affected

mysql> insert into char_test(ch) values('xcbeyond'),('  xcbeyond'),('xcbeyond  ');
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

奇怪的事情发生了,当我们查询时,会发现第三个字符串末尾的空格被自动截断了。为了更好的显示出是否有空格,对 ch 字段前后拼接 ' 字符便于查看对比。

mysql> select concat("'",ch,"'") from char_test;
+--------------------+
| concat("'",ch,"'") |
+--------------------+
| 'xcbeyond'         |
| '  xcbeyond'       |
| 'xcbeyond'         |
+--------------------+
3 rows in set

如果用 VARCHAR(10) 字段存储相同的值,则字符串末尾的空格是不会被截断的。

三、日期类型

MySQL 提供了两种相似的日期类型:DATETIMETIMESTAMP,使用起来傻傻分不清,看完本节后不要再说不知道如何选择了。

对于应用程序而言,他们都能很好的表示日期,但是再某些场景下,各有不同。接下来让我们一起看看吧。

1.DATETIME

DATETIME 类型能够保持很大范围的日期,从 1001 年到 9999 年,精度为秒。它把日期和时间封装到格式为 YYYYMMDDHHMMSS 的整数中,与时区无关,使用8个字节的存储空间。

默认情况下,MySQL 是以一种可排序、无歧义的格式显示 DATETIME 值,例如 2020-03-05 22:38:40

2.TIMESTAMP

TIMESTAMP,从它的名字不难看出,它和UNIX时间戳相同,保存了从 1970 年 1 月 1 日 0 时 0 分 0 秒以来的秒数TIMESTAMP 只使用 4 个字节的存储空间,因此它的范围比DATETIME小得多,只能表示从 1970 年到 2038 年

TIMESTAMP 显示的值依赖于时区,MySQL 服务器、操作系统,以及客户端连接都有时区设置。因此,存储值为0的TIMESTAMP 在美国东部时区显示为 1969-12-31 19:00:00,与格林尼治时差5个小时。

通常应该尽量使用 TIMESTAMP,因为它比 DATETIME 更节省存储空间,而且对于跨时区的业务,TIMESTAMP 更为合适。

如果需要存储比秒更小粒度的日期和时间值该怎么办?MySQL 目前没有提供合适的数据类型,但可以采用其他变通的方式,如可以使用自己的存储格式:可以使用 BIGINT 类型存储微妙级别的时间戳,或者使用 DOUBLE 存储秒之后的小数部分。或者也可以使用 MariaDB 数据库替代 MySQL

四、TEXT 和 BLOB 类型

一般在保存少了字符串的时候,我们会选择 CHARVARCHAR 类型,而在保存较大文本等数据时,通常会选择使用 TEXTBLOB

TEXTBLOB 类型都是存储很大的数据而设计的字符串数据类型,分别采用字符串和二进制方式存储。例如,TEXT 通常用来保存文章内容、日志等字符串内容,而BLOB通常用来保存图片、视频等二进制数据内容。有如下特点:

  • TEXT 类型有字符集和排序规则。
  • BLOB 类型存储的是二进制数据,没有排序规则或字符集。
  • MySQL 中不能将 TEXT 和 BLOB 类型的列进行索引,也不能使用这些索引消除排序。

与其他数据类型不同,MySQL 把每个 TEXTBLOB 类型的值当作一个独立的对象处理。存储引擎在存储时通常会做特殊处理,当它们的值太大时,InnoDB 会使用专门的“外部”存储区域来进行存储,此时每个值在行内需要 1~4 个字节来存储一个指针,然后在外部存储区域存储实际的值。

在面对 TEXT、BLOB 之间的选择时,应该根据实际情况选择能够满足需求的最小存储类型,接下来主要针对 TEXT、BLOB 类型存在的一些常见问题进行介绍。

1.在执行了大量的删除操作时,TEXTBLOB 会引起一些性能问题

删除操作会在数据库表中留下很大的“空洞”,以后填入这些“空洞”的记录在插入的性能上会有影响。为了提高性能,建议定期使用 OPTIMZE TABLE 功能对这类表进行碎片整理,避免因为“空洞”导致性能问题。

实战演示验证说明如下:

1)创建测试表 text_test,字段 idcontext 的类型分别为 int(11)text:

mysql> create table text_test(id int(11),context text);
Query OK, 0 rows affected

2)往表 text_test 中插入大量的数据,这里使用 repeat 函数插入字符串:

repeat 函数用于字符串的复制

mysql> insert into text_test(id,context) values(1,repeat('xcbeyond',1000));
Query OK, 1 row affected

mysql> insert into text_test(id,context) values(2,repeat('xcbeyond',1000));
Query OK, 1 row affected

mysql> insert into text_test(id,context) values(3,repeat('xcbeyond',1000));
Query OK, 1 row affected

mysql> insert into text_test(id,context) values(4,repeat('xcbeyond',1000));
Query OK, 1 row affected

mysql> insert into text_test(id,context) values(5,repeat('xcbeyond',1000));
Query OK, 1 row affected

mysql> insert into text_test(id,context) values(6,repeat('xcbeyond',1000));
Query OK, 1 row affected
……

3)此时看看表 text_test 的物理文件大小:

2020/03/07 周六  15:58           540,672 text_test.ibd

这里显示数据文件大小为 540Kb

4)从表 text_test 中删除一大部分数据,这些数据占总数据量的2/3:

mysql> delete from text_test where id < 10;
Query OK, 9 rows affected

5)在此查看 text_test 的物理文件大小:

2020/03/07 周六  16:05           573,440 text_test.ibd

奇怪的是,数据文件大小并没有因为删除数据而减少,反而还增加了一点。

6)接下来对表 text_test 进行OPTIMIZE 优化操作:

mysql> optimize table text_test;
+----------------+----------+----------+-------------------------------------------------------------------+
| Table          | Op       | Msg_type | Msg_text                                                          |
+----------------+----------+----------+-------------------------------------------------------------------+
| test.text_test | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.text_test | optimize | status   | OK                                                                |
+----------------+----------+----------+-------------------------------------------------------------------+
2 rows in set

7)再次查看表 text_test 的物理文件大小:

2020/03/07 周六  16:08           458,752 text_test.ibd

可以发现,表的数据文件大小减少了,则说明“空洞”空间已经被回收了。

2.使用合成索引来提高大文本字段(TEXTBLOB 类型)的查询性能

合成索引,就是根据大文本字段的内容建立一个散列值,并把这个值存储在单独的数据列中,接下来就可以通过检索散列值找到数据行了。

但是,要注意这种技术只能用于精确匹配的查询(散列值对于类似 <>= 等范围搜索操作符是没有用处的)。可以使用 MD5() 函数生成散列值,也可以使用 SHA1()CRC32(),或者使用自己的应用程序逻辑来计算散列值。请记住数值型散列值可以很高效率地存储。同样,如果散列算法生成的字符串带有尾部空格,就不要把它们存储在 CHARVARCHAR 列中,它们会受到尾部空格去除的影响。合成的散列索引对于那些 BLOBTEXT 数据列特别有用。用散列标识符值查找的速度比搜索 BLOB 列本身的速度快很多。

实战演示验证说明如下:

1)创建测试表 text_test2,字段 idcontexthashValue 字段类型分别为 int(11)textvarchar(40):

mysql> create table text_test2(id int(11),context text,hashValue varchar(40));
Query OK, 0 rows affected

2)往表 text_test2 中插入数据,其中 hashValue 用来存入 context 列内容的 MD5 值:

mysql> insert into text_test2 values(1,repeat('xcbeyond',10),md5(context));
Query OK, 1 row affected

mysql> insert into text_test2 values(2,repeat('xcbeyond',10),md5(context));
Query OK, 1 row affected

mysql> select * from text_test2;
+----+----------------------------------------------------------------------------------+----------------------------------+
| id | context                                                                          | hashValue                        |
+----+----------------------------------------------------------------------------------+----------------------------------+
|  1 | xcbeyondxcbeyondxcbeyondxcbeyondxcbeyondxcbeyondxcbeyondxcbeyondxcbeyondxcbeyond | 537f6020f5b2b59456a61271a2b3f285 |
|  2 | xcbeyondxcbeyondxcbeyondxcbeyondxcbeyondxcbeyondxcbeyondxcbeyondxcbeyondxcbeyond | 537f6020f5b2b59456a61271a2b3f285 |
+----+----------------------------------------------------------------------------------+----------------------------------+
2 rows in set

3)如果需要查询 context 列的值,则通过散列值 hashValue 来查询:

mysql> select * from text_test2 where hashValue = md5(repeat('xcbeyond',10));
+----+----------------------------------------------------------------------------------+----------------------------------+
| id | context                                                                          | hashValue                        |
+----+----------------------------------------------------------------------------------+----------------------------------+
|  1 | xcbeyondxcbeyondxcbeyondxcbeyondxcbeyondxcbeyondxcbeyondxcbeyondxcbeyondxcbeyond | 537f6020f5b2b59456a61271a2b3f285 |
|  2 | xcbeyondxcbeyondxcbeyondxcbeyondxcbeyondxcbeyondxcbeyondxcbeyondxcbeyondxcbeyond | 537f6020f5b2b59456a61271a2b3f285 |
+----+----------------------------------------------------------------------------------+----------------------------------+
2 rows in set

上面的例子则是展示了合成索引的用法,由于这种技术只能用于精确匹配,在一定程度上减少 I/O,从而提高查询效率。

3.在不必要的情况下避免检索 TEXTBLOB 类型的值

例如,SELECT * 查询就不是很好的操作,除非能够确定作为约束条件的 WHERE 子句只会找到所需要的数据行。否则,很可能毫无目的地在网络上传输大量的值。这也是 BLOB 或 TEXT标识符信息存储在合成的索引列中对用户有所帮助的例子。用户可以搜索索引列,决定需要的哪些数据行,然后从符合条件的数据行中检索 BLOB 或 TEXT 值。

4.把 BLOB 或 TEXT 列分离到单独的表中

在某些环境中,如果把这些数据列移动到第二张数据表中,可以把原数据表中的数据列转换为固定长度的数据行格式,那么它就是有意义的。这会减少主表中的碎片,可以得到固定长度数据行的性能优势。它还可以使主数据表在运行 SELECT * 查询的时候不会通过网络传输大量的 BLOB 或 TEXT 值。

五、选择唯一标识符

唯一标识符,也就是我们常常所说的主键,用于充当表记录的唯一判断依据。唯一标识符,选择合适的数据类型是非常重要的。

通常唯一标识符更多的是用来与其它值或者其它表的值进行比较(如,关联查询中),标识列也可能在其它表中作为外键使用,所以为标识列选择数据类型时,应该选择根关联表中对应列一样的类型。

当选择唯一标识符的类型时,不仅仅需要考虑存储类型,还需要考虑 MySQL 对这种类型怎么执行计算和比较的,因为比较在SQL查询中使用最多,而且也是制约性能的最大因素。

一旦选定了一种类型,就一定要确保所有关联表中都使用相同的类型。因为类型直接往往都是需要精确匹配,混用不同数据类型可能导致性能问题,即使没有性能影响,在比较操作时隐式类型转换也可能导致很难发现的错误问题。

在可以满足值的范围的需求,并且预留未来增长空间的前提下,应该选择最小的数据类型。

下面是一些小技巧:

1.整数类型

整数通常是标识列最好的选择,因为它很快,并且可以使用 AUTO_INCREMENT

2.字符串类型

如果可以避免,尽可能的避免使用字符串类型作为标识列的类型,因为它很消耗空间,并且通常比数字类型慢。尤其是在 MyISAM 存储引擎的表里使用字符串作为标识列时,要特别的小心,MyISAM 默认对字符串使用压缩索引,这会导致查询慢很多。

对于完全“随机”的字符串也需多加注意,例如 MD5()SHA1() 或者 UUID() 产生的字符串。这些函数生成的新值会任意分布在很大的空间内,会导致 insert 以及一些 select 操作变得很慢:

  • 因为插入值会随机地写到索引的不同位置,所以使得 insert 语句更慢。这会导致页分裂、磁盘随机访问。
  • select 语句会变得更慢,是因为逻辑上不相邻的数据会分布在磁盘和内存的不同地方。
  • 随机值会导致缓存对所有类型的查询语句效果很差,因为会使得缓存赖以工作的访问局部性原理失效。

六、总结

在实际开发中,有很多工具会自动生成建表脚本等等,自动生成前期给开发带来了很大的便利,但与此同时却导致严重的性能问题。有些工具生成的东西,在存储任何数据都会使用很大的 VARCHAR 类型,这往往是不正确的。如果是自动生成的,一定要反复检查确认是否合理。

例如,一些 ORM 框架(如,MyBatisHibernate),会存储任意类型的数据列到任意类型的后端数据,这通常意味着没有设计使用更优的数据类型来存储,后期安全隐患很大,出现问题也很难排查。总之,一定要反复检查确认是否合理。这也是我个人不太喜欢用这类类似的工具,来生成代码的原因,检查真的很浪费我的时间。

在这里已经介绍了大部分常用的数据类型,各自都有哪些特点,哪些地方会严重影响性能等等。在选择数据类型时,把握好“选择原则”,你就成功了一半,其余细节在日常开发接触中慢慢琢磨、留意,选择类型时不要随意、盲目选择就好。

简单归纳如下:

  • 对于字符串类型,最好的策略是只分配真正需要的空间。

  • 日期类型,要根据实际需要选择能够满足应用的最小存储的日期类型。

  • 对含有 TEXTBLOB 字段的表,如果经常做删除和修改记录的操作要定时执行 OPTIMIZE TABLE 功能对表进行碎片整理。