《高性能MySQL》必读章节 之 建表优化

在数据库的性能调优的过程中会涉及到很多的知识，包括字段的属性设置是否合适，索引的建立是否恰当，表结构涉及是否合理，数据库/操作系统 的设置是否正确…..其中每个topic可能都是一个领域。

在我看来，在数据库性能提升关键技术中，对字段的优化难度相对较低且对性能的影响也非常的大。由于MySQL支持的数据类型比较多，且每个类型都有其独特的特性，但是有时候在选择一个具体的数据类型时，往往都是随意的选择一个能用的类型，而不会考虑到这个类型是否是最优的。在具体的类型描述之前，先来看一些针对数据类型选择的主要原则：

a) 尽量选择占用空间小的类型

因为小的类型无论是在磁盘，还是在内存中占用的空间都是小的,在进行查询或者排序时临时表要求的空间也会相对较少。在数据量比较小的时候可能感觉不到，但是当数据量比较大时，这个原则的重要性可能就会得到显现。

例如,有一张“商品信息”表，记录为2000万条，这张表有个 “剩余商品数量”(COUNT)的字段，一般而言 SMALLINT (len:16 range:0-65535)已经足够表达这个字段，可是如果你在设计的过程中用了BIGINT(len:64 range:0-18446744073709551615)来表达，虽然说程序可能正确的运行，但是这一个字段将会额外的增加大概95M的磁盘存储空间(64-16)/8*20,000,000 Bytes)，另外在做数据选择和排序时仅仅这一个字段就会增加你95M的内存消耗，基于以上行为的影响，数据库的Performance必然是会被影响的。

这里说的尽量小的前提是确保你将要选择的类型可以满足日后业务发展的需求，因为在数据量比较大的时候做表结构的更新是个非常缓慢而且麻烦的事情。

在企业项目中体现：

mysql中的tinyint：从 0 到 255 的整型数据。存储大小为 1 字节。

mysql中的Smallint： 存储大小为 2 字节。

mysql中的int：存储大小为 4 字节。

我看了下我们现有的系统，表数量有400张。

表中的“类型”字段，不会少吧，比如订单有订单类型、取消原因类型等等10来个类型字段，（这些类型，最多有10多个值，再加也多不到哪去），其他的虽然少，但是一个表最少有1个吧，算这400多个表“类型”字段一共500个吧，我们来算一下数据空间的差别(假设数据都是500万条)：

使用tinyint:5000000*1*500/1024/1024 = 2384MB

使用int:5000000*4*500/1024/1024 = 9537MB

光数据存储，相差就是几个G,还有数据库日志呢，绝对不会比数据少（现在生产环境数据已经差不多到达1000多万了）

字段大，对于数据库查询、数据传输等方面都会有很大影响的，所以不要小看 int和tinyint类型的选择！我们系统很多都是开发者自己建立的数据库表，int在和程序中方便使用，直接用Int了，根本不会考虑效率问题，这是需要重视的。

b) 尽量选择简单/恰当的类型

在对表进行选择以及排序的时候，对于简单的类型往往只需要消耗较少的CPU时钟周期。例如，对于mysql server而言，整数类型值的比较往往会比字符串类型值的Compare简单且快，所以当你需要对特定的表进行排序时应该尽量选择整数类型作为排序的依据。字符集和比较规则使得字符排序与比较更复杂。

打个比方：全世界的人都知道 1~10这10个整数怎么比较和排序。因为整数可以直接比较。但是你知道α β γ δ ε ζ η θ ι κ λ μ ν ξ ο π ρ σ τ υ φχ ψ ω 怎么排序吗？ 我们不得不借助字符校对集进行排序。字符校对集

c) 尽量将字段设置为NOTNULL

一般情况下，如果你没有显示的制定一个字段为NULL，那么这个字段将会被数据库系统认为是NULL, 系统的这种默认行为将会导致以下三个问题

(1) Mysql服务器自身的 查询优化功能将会受影响

(2) Mysql针对null值的字段需要额外的存储空间以及处理

(3) 如果一个null值是索引的一部分，那么索引的效果也会收到影响

由于这个原则对于数据库性能提升的作用不是很大，所以对于已经存在的DB schema，其存在NULLABLE字段或者是索引为NULLABLE的，也不用专门的去修改它，但是对于新设计的DB或者索引需要尽量遵守这个原则。

介绍完了数据类型选择的原则后，接下来将会介绍Mysql中常见的数据类型以及在性能优化方面需要注意的地方。

· 整数

在Mysql 的整数家族成员中主要包括TINYINT(8bit), SMALLINT(16bit), MEDIUMINT(24bit), INT(32bit), or BIGINT(64bit)。

对于有符号整数而言这些类型的存储范围为(-2(n-1) ,2(n-1)-1)，对于无符号数而言表达的范围是(0,2n-1)，对于数据库而言有符号数和无符号数占用相同的存储空间，所以在选择类型的时候可以只考虑数的区间，而不用考虑是signed还是unsigned。

Mysql允许你在定义整数类型时指定他的宽度，例如 INT(10)。INT(10) 对于Client/CMD Line的输出是有区别的，但在Mysql Server看来实际的存储空间/计算消耗/数字范围 INT(10)与INT(32)没有任何的区别。

注意：指定整数的显示宽度 特性已经被移除，请避免使用。–《高性能MySQL》（第三版MySQL5.6以上版本）

· 小数

在Mysql中小数家族的数据类型主要包括FLOAT(4字节),DOUBLE(8字节),从这两种类型的存储空间可以看出小数的存取比整数需要消耗更多的空间，所以除非必须，否则应该尽量避免使用小数的类型

注意： 指定浮点数的精度特性已经被移除，请避免使用。–《高性能MySQL》（第三版MySQL5.6以上版本） 如Float(4,2) 。

规范：对小数的计算请使用DECIMAL进行精确计算，避免使用浮点数。—《阿里巴巴JAVA技术手册–建表公约》

· 字符串

不管对于哪门语言而言，字符串都是一个比较重要且复杂的类型，这个规律对于MYSQL同样适用

在MYSQL中主要包括VARCHAR以及CHAR两种字符串类型，对于这两种字符串类型在磁盘以及内存中存储方式是由Storage engine决定的，且不同的storage engine可能会有不同的存储方式。一般情况下对于一种storage engine 而言，在磁盘以及内存中的存储方式也是不同的，当数据在磁盘与内存之间转移时，storage engine将会负责把数据进行转换

VARCHAR

首先需要指出的是Mysql是用variable length的方式来来存储VARCHAR，相对于fixed length，这种方式对存储空间采取的策略是“用多少，要多少”，是一种比较节省空间的存储方案，在没有特殊需求的情况下可以作为默认的类型

VARCHAR之所以可以实现定长，是因为每个VARCHAR值都会附加一个 长度为1-2byte 的长度指示器，例如当需要存储“I Love Java”时，底层的存储内容为 “11I Love Java”,其中11(1 Byte)代表长度。当需要存储内容的长度为1000时长度指示器就需要两个字节。因为2bytes的最大值为216，所以当存储的字符串超过这个长度时，会出现不可预料的异常，这时就需要使用CLOB来存储这种超长的字符串。

在MYSQL的不同版本中，针对VARCHAR字段的结尾空格处理也有所不同

Version>=5.0 保留结尾的空格

Version<=4.1 截取空格

以MYSQL 5.6 为例：

▪ 使用VARCHAR(5) 和VARCHAR(200) 存储’hello’的空间开销是一样的。那么使用更短的列有什么优势吗？

事实证明有很大的优势。更大的列会消耗更多的内存，因为MySQL 通常会分配固定大小的内存块来保存内部值。尤其是使用内存临时表进行排序或操作时会特别糟糕。在利用磁盘临时表进行排序时也同样糟糕。

所以最好的策略是只分配真正需要的空间。

CHAR

CHAR类型与VARCHAR类型最大的区别在于它是定长的。同时相比于VARCHAR它主要有以下特点

1)在所有的MYSQL版本中，末尾的空格都会被截取

2)对于 一些短的且是长度基本相同的字段是个不错的选择例如MD5,ID Number

3)对于经常需要变更的字段，CHAR类型会更高效

4)对于一些超短的字段，也非常的节约空间。例如你保存“Y”或者是“N”，用CHAR只需要一个字节，而用VARCHAR 的话需要两个字节（1byte length+1 byte value）

对于定长的CHAR，Mysql server会根据其定义的长度采用补空格的方式来分配足够大的存储空间。有一点需要注意的是 VARCHAR/CHAR在进行“补空格”以及“去结尾空格”的操作是由Mysql server来实现的，与Storage engine 无关

·BLOB/TEXT

在实际的应用程序中往往需要存储两种体积较大的数据，一种是较大的Binary数据，e.g. 一张10M的图片，另外一种是 较大的文本 e.g.一篇几万字的文章。在Oracle中有BOLB和CLOB来应对这两种数据，而在MySQL中对应的是BLOB以及TEXT.

鉴于这两种数据类型的特殊性，在MySQL中对BLOB以及TEXT的存储和操作做了特殊的处理:

1) BLOB/TEXT 的值往往是作为对象来处理，这些对象有自己的ID，以及独立的存储空间

2) BLOB/TEXT的值被用来排序的时候，只有前N个字节会被使用，N 对应的是数据库中的一个常量值 (max_sort_length), 如果你想指定更多的字节被用来排序，那么你可以增加max_sort_length的值或者是使用ORDER BY SUBSTRING(column, length)函数来处理

3) 当BLOB/TEXT 被用作索引或者排序的时候，不能使用整个字段的值.

在万不得已的情况下要避免把BOLB/TEXT用作索引或是排序

因为MySQL 的Memory 引擎不支持BLOB 和TEXT 类型，所以，如果查询的过程中涉及到BLOB /TEXT,则需要使用MyISAM 磁盘临时表，即使只有几行数据也是如此（在最新的Percona Server 的Memory 引擎支持BLOB 和TEXT 类型）。

Memory引擎频繁的访问磁盘临时表会产生严重的性能开销，最好的解决方案是尽量避免使用BLOB 和TEXT 类型。如果实在无法避免，有一个技巧是在所有用到BLOB 字段的地方都使用SUBSTRING(column, length) 将列值转换为字符串（在ORDER BY 子句中也适用），这样就可以使用内存临时表了。但是要确保截取的子字符串足够短，不会使临时表的大小超过max_heap_table_size 或tmp_table_size，超过以后MySQL 会将内存临时表转换为MyISAM 磁盘临时表。

最坏情况下的长度分配对于排序的时候也是一样的，所以这一招对于内存中创建大临时表和文件排序，以及在磁盘上创建大临时表和文件排序这两种情况都很有帮助。例如，假设有一个1 000 万行的表，占用几个GB 的磁盘空间。其中有一个utf8字符集的VARCHAR(1000) 列。每个字符最多使用3 个字节，最坏情况下需要3 000字节的空间。如果在ORDER BY 中用到这个列，并且查询扫描整个表，为了排序就需要超过30GB 的临时表

· DATETIME/TIMESTAMP

在MySQL中包含两种时间格式 DATETIME，TIMESTAMP， 通常在使用的过程中这两种类型区别不是很大，但是在细节上还是存在差别DATETIME TIMESTAMP占用空间8Bytes

4Bytes可表示区间(年)1001-9999 1970-2038是否与时区有关否是存储内容日期和时间封装到格式为YYYYMMDDHHMMSS 的整数中

保存了从1970 年1 月1 日午夜（格林尼治标准时间）以来的秒数，它和UNIX 时间戳相同

显示方式是否与时区有关

否（ANSI 标准定义的日期和时间表示方法）

是

特殊属性

(1)TIMESTAMP 列默认为NOT NULL,默认值为当前时间

因为TMESSTAMP会占用更小的存储空间，所以可以使用它作为默认的时间格式

· ENUM

这种类型的字段主要是通过枚举的方式来保存列的值，因为在使用的过程中会涉及到枚举位置与实际值的转换，所以对于整体的性能可能会有一定的影响，而且枚举的值是存储在.frm(数据表结构定义文件)中，所以当建立完ENUM的列后，如果你想对EMUM的内容进行更新，也就相当于做了表结构的更新。

下面是个简单建立ENUM列的例子：

mysql> CREATE TABLEenum_test(

-> e ENUM(‘fish’, ‘apple’, ‘dog’) NOT NULL

-> );

mysql> INSERT INTOenum_test(e) VALUES(‘fish’), (‘dog’), (‘apple’);

·BIT

如果需要让你设计一个表示布尔值的字段要求占用的空间最少，你会如何去设计？用INT,还是用CHAR(1)?相比INT以及CHAR(1)而言BIT(1)或许是个更好的选择，因为它占用的空间只是一个BIT。它可以通过BIT(N)的方式来表达多个BIT的值，这种方式最大支持到BIT(64)。

在MySQL5.0之前的版本中，BIT被认为是和TINYINT等同的，在新的版本中被作为两种完全不同的类型来对待。

当你把一个BIT字段从数据库中检索出来显示在控制台上时，值会被显示成ASCII编码，当字段的值出在一个数字运算的上下文时，它会被当成是BIT的十进制的值，下面的一个例子可以很清楚的说明这两种情况

mysql>CREATE TABLE bittest(a bit(8));

mysql> INSERT INTObittest VALUES(b’00111001’);

mysql> SELECT a, a+ 0 FROM bittest;

+——+——-+

| a | a + 0 |

+——+——-+

| 9 | 57 |

+——+——-+

上面的这个例子或许会让你感到困惑，很有可能让你不再想使用这种机制来存储单个的位，作为一种替代方案可以把相关字段设置成CHAR(0)，NULL用来表示False,””(Empty String)表示True