高性能MySQL - 数据类型优化

数据类型选择原则

1. 小

尽可能使用可以正确存储数据的最小数据类型，占用更小的磁盘，内存和CPU缓存，处理周期也小。

2. 简单

整型比字符操作代价低，字符集的较对规则使字符比整型比较更复杂，所以应该使用MySQL内建的类型而不是字符串来存储日期和时间，另外应该用整型存储IP地址，而不是字符串。

3.尽量避免NULL

NULL对于MySQL来说很难优化，NULL使得索引，索引统计和值比较都更为复杂，而且会使用更多的存储空间。

基本数据类型

MySQL为了兼容性支持很多别名，例如INTEGER，BOOL，以及NUMERIC，但它们只是别名，如果用SHOW CREATE TABLE检查，你会发现MySQL报告的是基本类型，而不是别名。

一、整型

TINYINT	SMALLINT	MEDIUMINT	INT	BIGINT
8	16	24	32	64

存储范围： -2^(N-1)到2^(N-1)-1,其中N是存储空间的位数。
UNSIGN属性，可以使得上限提高一倍，
-128 - 127 ——> 0 - 255
MySQL 可以为整数指定宽度，例如INT(11),对大多数应用这是没有意义的，它不会限制值的合法范围，只是规定了MySQL一些交互工具用来显示字符的个数，对于存储和计算来说，INT(1)和INT(20)是相同的。

二、实数类型

实数是带有小数部分的数字。然而，它们不只是为了存储小数部分；也可以使用DECIMAL存储比BIGINT大的整数，MySQL既支持精确类型，也支持不精确类型。

FLOAT 和 DOUBLE支持使用标准的浮点运算进行相近似计算，DECIMAL类型用于存储精确的小数，5.0 之后的版本支持精确计算。CPU不支持对它的直接计算，所以在MySQL5.0 之后的版本，MySQL服务器自身实现了DECIMAL的高精度计算，但相对于高精度的这个运算，浮点运算明显会快一些。

浮点和DECIMAL类型都可以指定精度。对于DECIMAL 列，可以指定小数点前后所允许的最大位数。例如： DECIMAL(18,9)小数点两边各存储9个数字，一共使用9个字节；小数点本身占1个字节。

DECIMAL类型最多65个数字

因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精度计算时才使用DECIMAL。

三、字符串类型

VARCHAR和CHAR是两种最主要的字符串类型，具体怎么存储在磁盘和内存中的，跟存储引擎的具体实现有关。下面的描述假设使用的是InnoDB和MyISAM。

VARCHAR
VARCHAR类型用于存储可变长字符串，比定长类型更节省空间。所以他需要使用1或2个额外字节记录字符串的长度：如果列的最大长度小于或等于255字节，则使用1个字节表示，否则2字节。假设采用latin1字符集，一个VARCHAR(10)的列需要11个字节的存储空间。VARCHAR(1000)需要1002个字节。

Update的时候可能使行变得比原来更长，如果页内没有更多的空间，不同的存储引擎会采取不同的处理方式。MyISAM会将行拆成不同的片段存储，InnoDB则需要分裂页来使行可以放进页内。

使用VARCHAR(5)和VARCHAR(200)存储‘hello’的空间开销是一样的，但是更长的列会消耗更多的内存，因为MySQL通常会分配固定大小的内存块来保存内部值。尤其是使用内存临时表进行排序或操作时会特别糟糕。所以最好还是只分配真正需要的空间。

下面情况下用VARCHAR是合适的：字符串列的最大长度比平均长度大很多；列的更新很少，所以碎片不是问题；使用了UTF-8这样复杂的字符集，每个字符都使用不同的字节数进行存储。

InnoDB 可以把过长的VARCHAR存储为BLOB。MySQL在存储和检索的时候会保留末尾空格，在4.1之后的版本。

CHAR
· CHAR类型是定长的。MySQL会删除所有的末尾空格。CHAR适合存储很短的字符串，或者所有值都接近同一个长度，例如，CHAR非常适合存放密码的MD5值。

· 并且对于经常变更的数据，CHAR也比VARCHAR好，不容易产生碎片。

· 对于非常短的列，CHAR比VAERCHAR在存储空间上也更有效率。例如CHAR(1)来存储只有Y和N的值，只需要一个字节，VARCHAR要2个字节。

BINARY和VARBINARY
当需要存储二进制数据，并且希望MySQL使用字节码而不是字符进行比较时使用。二进制比字符比较简单很多，所以也很快。

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

两组不同的数据类型家族：

TINYTEXT	SMALLTEXT	TEXT	MEDIUMTEXT	LONGTEXT
TINYBLOB	SMALLBLOB	BLOB	MEDIUMBLOB	LONGBLOB

当BLOB和TEXT值太大时，InnoDB会使用专门的“外部”存储区间进行存储，此时每个值在行内需要1-4个字节存储一个指针，然后在外部存储区域存储实际的值。

枚举ENUM
有时候可以使用枚举列代替常用的字符串类型。MySQL在内部会将每个值在列表中的位置保存为整数，并且在表的.frm文件中保存“数字 - 字符串”映射关系的“查找表”

举例：我们运行下面两个语句：

CREATE TABLE enum_test(
    e ENUM('fish','apple','dog') NOT NULL
);
INSERT INTO enum_test(e) VALUES('fish'),('dog'),('apple');

这三行数据实际存储为整数，而不是字符串。可以通过在数字上下文环境检索看到双重属性。

另外枚举字段是按照内部存储的整数而不是定义的字符串进行排序的。
枚举最不好的地方，字符串列表是固定的，添加和删除字符串必须使用ALTER TABLE，因此，对于一系列未来可能会改变的字符串，使用枚举不是一个好主意，除非能接受在列表末尾添加元素。

日期和时间类型
DataTime
这个类型能保存大范围的值，从1001年到9999年，精确为秒。它把日期和时间封装到格式YYYYMMDDHHMMSS的整数中，与时区无关，使用8个字节的空间。
TIMESTAMP
从1970年1月1日午夜以来的秒数，它和UNIX时间戳相同。只使用4个字节的存储空间，范围比DATETIME小，只能表示从1970到2038年。MySQL提供FROM_UNIXTIME()函数把UNIX时间戳转换成日期，UNIX_TIMESTAMP()转换回去。跟时区有关。

除了特殊行为之外，通常应该尽量使用TIMESTAMP，因为它空间效率更高。

以上两种都只保存到秒，之前在七牛碰到过这个问题，要想保存更小的，只能使用自己的存储格式，比如可以使用BIGINT类型存储微妙级别的时间戳。

位数据类型
BIT
可以使用BIT列在一列中存储一个或多个true/false值。BIT(2)存储2位。最大长度64. MySQL把BIT当作字符串使用，所以不要随意加减运算，不然会出现问题。
SET
如果需要保存很多true／false值，可以考虑合并这些列到一个SET，主要的缺点是改变列的定义的代价较高。

IPV4地址，实际上是32位无符号整数，不是字符串。所以应该用无符号整数存储IP地址，MySQL提供INET_ATON() 和INET_NTOA()函数在这两种方法之间转换。