Schema的优化和索引 - 选择最佳的数据类型

前言

　　越小也就是越好的

　　一般来说，尽量选择小的数据类型足以符合你的存储和展现数据。越小的数据类型也常常是越快的，因为它们使用了较少的硬盘空间，内存，CPU缓存。它们也需要更少的CPU处理周期。

　　要确定的是不要低估你要存储数值的范围，因为在你的Schema的多个位置中，增长的数据类型范围会让操作很费力费时。如果你对不知道选择什么样的数据类型，那么就选择一个最小的，当然要确保数据不会越界。

　　简单的就是好的

　　对于简单数据类型的操作，不需要太多的CPU周期。比如，整型之间的比较要比字符之间的比较消耗更低。因为字符集和排序规则使字符的比较过于的复杂。这有两个例子，存储日期和时间要使用MySQL内置类型而不是用字符串，以及对于IP地址你应该使用整型。我们会将在以后的章节详细讨论。

　　如果可能，要避免使用NULL

　　当可能的时候，你都应该定义字段为NOT NULL.有许多表包括了许多NULL的列，即使这些应用并不存储NULL。只不过因为这是默认的。除非你打算存储NULL，不然的话你就要小心指定列为NOT NULL.

　　对于MySQL，优化一些涉及到NULL列的语句是非常困难的，因为它们加索引，索引统计，以及值的比较都很复杂。一个NULL列需要更多的存储空间以及在MySQL内部需要特殊处理。当一个NULL列被索引了，它的每个实体都需要额外的字节，以及设置导致了在MyISAM中，定长的索引转为可变大小（variable-sized）的索引。

　　即使你需要在字段中不存储值，你也不要用NULL.考虑使用0，特殊的数值，或者空字符串来取代NULL.

　　把NULL列变为NOT NULL列性能的提高是很小的。因此不要改变已有的Schema,除非你能知道这样做会引起的问题。然而，如果你计划索引列，如果可能的话，请避免使用NULL列。

　　第一步要决定指定列的适用类型，要大概知道属于哪一类的：数值，字符串，时间等等。这是比较直接的，但是我们提到有些特殊的情况下，选择数据类型并不是很直接的。

　　第二步选择具体的类型。许多MySQL数据类型保存相同的类型数据，但是它们存储的范围不同，允许的精度，或者它们需要的物理空间（或者硬盘和内存）。一些数据类型有特殊的行为或者属性。

　　举个例子，DATETIME和TIMESTAMP列能存储相同类型的数据：日期和时间，精度为秒。然而，TIMESTAMP只使用了一半的存储空间，以及有个自动更新的特性。在另一方面，它的值范围较小，以及有的时候这个特性成了一个障碍了。

　　我们讨论的都是基本类型。MySQL也兼容别名，比如INTEGER,BOOL以及NUMERIC.这些仅仅是别名。它们可能造成一些困惑，但是不会影响性能。

整数和实数

整数

　　总共有两种数字类型：整数和实数（有小数部分的）。如果你保存的是整数，使用下列的整型：TINYINT,SMALLINT,MEDIUMINT,INT或者BIGINT。它们分别需要8,16,24,32,以及64位的存储空间。它们存储的值范围为N就是它们使用的存储空间所需的位数。

　　整型有个一个可选择的参数，就是UNSIGNED，它不允许存储负数，而正数的最大上限变为之前的大约两倍左右。举个例子，一个TINYINT UNSIGNED存储的范围是0-255，而不是之前的-128-127.

　　有符号和无符号类型使用了相同大小的存储空间，它们的性能是一样的，因此使用哪种类型取决存储的数据范围。

　　你的选择决定了MySQL在内存和硬盘上存储数据的方式。然而，即使在32位的架构上，整型的计算常常使用64位的BIGINT.（例外的情况一般发生在使用DECIMAL或者DOUBLE计算的聚合函数上）。

　　MySQL允许你指定整型的“宽度”，比如INT(11).对大多数应用来说没有什么意义：它并不会约束值的范围，但是可以简单的指定MySQL交互工具的字符数。只是限制显示而已。对于存储和计算，INT(1)和INT(20)是等同的。

　　实数

　　实数的数字带有小数部分。然而它们并不仅仅是小数。它也能保存一些大到连 BIGINT都保存不下的整数。MySQL同时支持精确和不精确两种类型。

　　FLOAT和DOUBLE支持近似的标准浮点运算。如果你需要知道准确的浮点运算结果，你需要研究你的平台的浮点实现方式。

　　DECIMAL类型存储了准确的浮点数。在MySQL5.0以上版本，都支持准确的运算。MySQL4.1较早版本，一般浮点运算会得到比较奇怪的值，因为丢掉了精度。在这些MySQL版本中，DECIMAL只不过是存储类型而已。

　　MySQL5.0以上版本的服务器自身去运行DECIMAL运算，是因为CPU并不直接支持这种计算。浮点运算稍微快些，因为CPU本地执行了这些运算。

　　浮点和DECIMAL类型都允许你指定精度。对于DECIMAL，你可以指定小数点之前和之后的数字最大的数量。这个决定了列的消耗空间的大小。MySQL5.0以上版本把数字打包进了一个二进制字符串（每四个字节包含了九个数字）。比如DECIMAL(18,9)会在小数点两边各存储9个数字，使用的空间为9字节：4个字节存储了小数点之前的数字，1个字节存储了小数点，另外4个字节存储了小数点之后的数字。

　　一个DECIMAL数在MySQL5.0以上版本中最高上限的数字个数为65。早期的MySQL版本中限制为254，并且存储数值并没有压缩（一个字节一个数字）。然而，这些版本中，并不能使用这些大数值进行计算，因为DECIMAL就是个存储类型而已。要计算的话，只能把DECIMAL转换为DOUBLE了。

　　指定浮点数的精度有两种方法，不同的方法导致了MySQL选择不同的类型以及近似的存储数值。精度的指定并不是标准的，因此我们建议指定你希望的类型而不是精确度。

　　保存相同的数值范围的情况下，浮点类型要比DECIMAL使用更少的空间。一个FLOAT列使用了4个字节。DOUBLE消耗了8个字节以及有更好的精度和更大的值的范围。像Integer一样，你仅仅选择的是存储类型。MySQL在浮点运算中，使用DOUBLE进行运算。

　　因为需要额外的存储空间和计算的消耗。你使用DECIMAL的情况为你需要使用精确的结果。比如，存储的是财政信息。

字符串类型

　　MySQL支持很多字符串类型，以及它们的许多变化类型。这些数据类型在4.1和5.0版本变化都比较大。可以说变得更复杂了。早在4.1版本中，每个字符串列都有自己的字符集和对于那些字符集的排序规则，或者叫做collation（校对）。

　　VARCHAR和CHAR

　　有两个主要的字符串类型就是VARCHAR和CHAR。它们都存储了字符值。不幸的是，很难准确解释这些值怎样存储在硬盘和内存中，因为这些都是有存储引擎实现的。我们假设你使用的是MyISAM和/或者InnoDB.如果不是，你最好查阅你使用存储引擎的相关文档。

　　让我们先看看VARCHAR和CHAR是怎样存储在硬盘上的。要注意的是存储引擎可能存储的CHAR或者VARCHAR在存入内存和硬盘上有所不同的，以及当服务器从存储引擎获得了这个值的时候，会把这个值转为另一个存储引擎的格式。下面就是两种类型的比较

　　VARCHAR：

　　VARCHAR存储了变长的字符串以及它是最常见的字符串数据类型。它占用的空间要少于定长的类型，因为它根据所需来决定需要的空间大小。特例就是MyISAM参数设为ROW_FORMAT=FIXED.这个参数使表的每一行使用固定大小的空间以及浪费大量的空间。

　　varchar使用了1或者2额外的字节记录值的长度：如果长度大约为255字节的话，大概使用1个字节，如果更多的话，那么就是2个字节。假如是latin1字符节，一个varchar(10)将会占用11字节的存储空间。一个varchar(1000)使用上限为1002字节。因为需要2字节存储长度信息。

　　VARCHAR会对性能有所帮助，因为它节约了空间。然而，因为行是变长，当你更新它们的时候，它们就会增长。这样会导致需要额外的一些工作。如果一行增长以及并不在原来的地址了。这些行为依赖于存储引擎。举个例子，MyISAM可能把行分为碎片。以及InnoDB可能切开页，把行填充到它的内部。其他的存储引擎可能在适当的地方不会更新数据。

日期和时间类型

　　MySQL对于不同种类的日期和时间有很多的数据类型，比如YEAR和DATE。MySQL可保存时间颗粒度是一秒。然而，时间的计算可以精确到毫秒。我们将会教你如何解决存储引擎的局限性。

　　大部分时间类型都没有什么可选择的。所以那个是最佳的选择不是一个问题。只有一个问题那就是当你即要保存日期也要保存时间的时候，将要做什么。MySQL提供了两个类型来满足这个需求:DATETIME和TIMESTAMP.对许多应用而言，任意一个都可以，但是在一些案例中，一个要好于另一个。让我们来看看：

　　DATETIME

　　这个类型可以保存大范围的值。从1001到9999.精度精确到了秒。它保存日期和时间压缩到为YYYYMMDDHHMMSS格式的整型，独立的时间区域。这会占用8字节的存储空间。

　　默认的MySQL显示DATETIME为排序的，清晰地格式，比如2008-01-16　22:37:08。这是符合ANSI标准的日期和时间。

　　TIMESTAMP

　　就像名字所提示的一样，TIMESTAMP类型存储了从1970 1.1 开始所经过秒的数量-这个UNIX的TIMESTAMP是一样的。因此它的范围要比DATATIME要小一些：从1970年到2038年。MySQL提供了FROM_UNIXTIME和UNIX_TIMESTAMP函数，用来把Unix的timestamp转为data，反之亦然。

　　新版的MySQL的TIMESTAMP值像DATETIME.但是旧版MySQL显示它们没有任何的标点。仅仅是显示格式的不同。TIMESTAMP存储在MySQL的方式在所有MySQL版本中都是一样的。

　　TIMSTAMP的现实也依赖于时区。MySQL服务器，操作系统，客户端连接所有的都有时区的设置。因此，一个TIMESTAMP存值为0，实际显示的是东部夏令时间，1969-12-31 19:00:00。相对GMT有5个时差。

　　TIMESTAMP也有一个DATETIME不具备的属性。默认的情况下，当你插入一条记录并没有指定这个值，MySQL会把TIMESTAMP列设为当前的时间。当你更新这一行的时候，MySQL也会更新TIMESTAMP列。除非你给TIMESTAMP指定一个值。对于任意的TIMESTAMP，你可以配置插入和更新的行为。最终，TIMESTAMP列默认不能为NULL.这点和其他的类型有所不同。

　　先撇特殊行为不谈，一般来说，如果能用TIMESTAMP就应该用，因为它空间上比DATETIME更有效用。有的时候人们用整型来存储UNIX的TIMESTAMP，但是这么做不能带来什么好处。这么做也不太容易去处理，我们不建议这么做。

　　怎样保存比秒更精确的时间?MySQL还没有这些数据类型，但你可以使用你自己的存储类型：你可以使用BIGINT类型并且存储这个值作为在微妙级别的TIMESTAMP，或者你可以使用DOUBLE并且存储小数点之后的秒的小数部分。

BIT数据类型

　　MySQL有很多使用单独的bit去存储数据的类型。不管底层的存储格式以及操作，从技术上来看所有的这些类型都是字符串类型。

　　BIT

　　在5.0之前的版本，BIT仅仅等同于TINYINT。但是在5.0之后的版本，它已经是个具有一些特性的，和以前完全不同的数据类型了。我们在这讨论的是新增加的特性。

　　你可以使用BIT列存储一个或多个true/false值。BIT(1)定义了一个包含1个bit的字段，BIT(2)存储了2个bits.如此类推。。

　　BIT最大长度是64bits.

　　不同存储引擎,BIT的特性是不同的。MyISAM为了存储它们把这些列打包为一个整体。因此17个单独的BIT列仅仅要存储17bits(假设每一列都没有NULL值)。MyISAM大约需要3字节存储它们。其他的存储引擎，如Memory和InnoDB。以足够存储这些bits的最小整数类型来存储每一列。因此你就不能节约使用的存储空间了。

　　MySQL把BIT看做字符串类型。并不是数字类型。当你获取一个BIT(1)的值，这个结果是个字符串，但是这字符串是二进制的0或1，要记住并不是ASCII的0或1.然而如果你获取的是数字，这个结果会被转换。如果你要作比较就一定要记住这一点。举个例子，如果你把b'00111001'（这个等同于57）存储到BIT(8)再获取它。你会获得字符串包含了字符码为57.ASCII字符码为9，但是在数字的环境下，你的获得值是57.

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

mysql> INSERT INTO bittest VALUES(b'00111001');

mysql> SELECT a, a + 0 FROM bittest;

+------+-------+

| a | a + 0 |

+------+-------+

| 9 | 57 |

+------+-------+

　　这很令人迷惑，所以我们建议要小心的使用BIT类型。对于大部分应用，要尽可能避免使用这个类型。

　　如果你想用一个BIT存储true/false。另一个选择是使用一个可以为NULL的CHAR(0)列。这个列可以存储NULL也可以存储一个空字符串。

　　SET

　　如果你要存储非常多的true/false。考虑使用SET数据类型把许多列整合为一个。它把一系列的bit打包了。并且十分有效的进行存储。还有就是MySQL有一些如FIND_IN_SET()和FIELD()函数可以很容易使用SET类型。主要的缺点就是需要改变表的定义。需要使用ALTER TABLE，这对比较大的表来说消耗还是很大的。一般来讲，你也不能使用索引来查找SET列。

　　在整数列上进行位运算

　　对于SET的另一个原则是使用整型。一个例子，你可以把8bits打包一个TINYINT然后用位运算来操作它们。在应用代码中对每一位定义常量可以使这些操作变得更为简单。

　　这种方式最主要的优势是，你可以改变字段中的枚举值，而不必使用ALTER TABLE。缺点就是写出的语句很难理解。一些人很习惯位运算而另一些人确不是。所以是否使用这个技术完全是个人的喜好了。

　　下面的一个例子是关于权限控制的。每个bit或set元素表现的值都是CAN_READ, CAN_WRITE或者CAN_DELETE。如果使用的SET类型，那么MySQL存储的就是bit和值的映射。如果存储的是整形，你就要把映射存储到应用程序代码中了。

　　下面是SET的例子

mysql> CREATE TABLE acl (
-> perms SET('CAN_READ', 'CAN_WRITE', 'CAN_DELETE') NOT NULL
-> );
mysql> 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 |
+---------------------+

　　如果使用的整形，你可能写出如下的代码

mysql> SET @CAN_READ := 1 << 0,
-> @CAN_WRITE := 1 << 1,
-> @CAN_DELETE := 1 << 2;
mysql> CREATE TABLE acl (
-> perms TINYINT UNSIGNED NOT NULL DEFAULT 0
-> );
mysql> INSERT INTO acl(perms) VALUES(@CAN_READ + @CAN_DELETE);
mysql> SELECT perms FROM acl WHERE perms & @CAN_READ;
+-------+
| perms |
+-------+
| 5 |
+-------+

　　我们使用了变量，但是你可以用应用程序中的代码来替换它们。

主键的选择

　　对于主键，选择一个好的数据类型尤为关键。你可能经常需要用这些列和其他做比较以及用这些列查找其他的列。你可能也把它们作为另一些表的外键。因此当你选择主键的数据类型时，应该保持相关表主键类型一致。

　　当选择主键的数据类型，你不仅要考虑存储类型，也要考虑MySQL操作和比较这些类型的表现情况。比如，MySQL内部存储ENUM和SET是作为整型的，但是当在字符串环境下作比较的时候，MySQL会把它们转为字符串。

　　一旦你选择了一个类型，要确定相关表都要使用这个类型。这个类型一定要精确，包括了它的属性，如UNSIGNED。混合不同的数据类型，会引起性能问题。即使不会，在类型比较的时候，也会出现很难发现的错误。在忘记比较不同的数据类型之后，这些错误往往会发生。

　　根据你需要值的范围，选择最小的数据类型的范围。并且要为以后留一些主键增长的空间。一个例子，你使用state_id来存储美国州的名称，你不需要上千或百万的值。因此你不需要使用INT。一个TINYINT就足够了并且它大小为3字节。如果你使用它作为其他表的外键。三字节就能发挥大的作用了。

　　Integer类型

　　Integer通常来说是主键类型的最佳选择。因为它很快并且可以自增。

　　ENUM 以及SET

　　通常来说，虽然它们对于表中包含状态或者类型值比较有用，但对于主键并不是一个好的选择。ENUM和SET比较适合存储一些如订单的状态，产品的类型，或者人的性别。

　　如果你使用了ENUM定义了一个产品的类型，你可能要根据唯一的ENUM字段来查找（你可能在这表中添加了产品的描述以及等等产品类型的相关信息）。这种情况下你可能使用ENUM作为主键，但是大多数情况要避免使用。

　　String类型

　　如果可以，要尽可能避免使用String做为主键。它会浪费许多空间以及处理起来要慢于Integer。当使用MyISAM的表时，使用String要尤为小心。默认的情况下，MyISAM会对String类型的索引进行压缩，这样会使查找变慢。

　　你也要小心使用随机的String.如MD5(),SHA1(),UUID().随机生成的String散列在大的空间中，这会降低插入和一些查询语句的速度。

　　降低了INSERT语句，因为在索引中这插入的值会存入随机的位置。这回造成页的分割，随机硬盘访问，造成聚集索引碎片。

　　降低SELECT速度，因为相邻的行被分散在硬盘和内存中。

　　随机值会导致缓存能力的下降。因为它们消除了本地的引用。本地的引用是缓存的工作方式。如果所有的数据都已经“预热”了，把任意数据放到缓存中没有任何的优势并且，如果工作数据集合没有在内存中，缓存就会有很多刷新和查找丢失的现象。

　　如果使用的UUID值，要去掉破折号或者使用UNHEX( )把UUID转为16字节的数并且，把它们存在BINARY(16)的列。你可使用HEX来获得16进制的值。

　　UUID生成的值和其他哈希函数如SHA1()生成的值有所不同。UUID的值分布不均并且稍微有点连续。但是它还是没有整型好。

　　特殊类型的数据

　　一些特殊类型的数据可能并不直接的和MySQL内置类型相吻合。一个例子就是用STAMPTIME存储更精确的时间。

　　另一个例子是IP地址。人们常常使用VARCHAR(15)来存储IP地址。然而，一个IP地址其实是一个无符号的32bit的整型，并不是一个字符串。IP地址的点儿仅仅是为了方便人们去读取IP地址而已。你应该用无符号整型去存储IP地址。MySQL提供了INET_ATON()和INET_NTOA()函数来解决这两种类型的转换。以后的MySQL版本。它会提供一个专门来存储IP的地址的数据类型。