schema与数据类型

选择优化的数据类型

MySQL支持的数据类型非常多，选择正确的数据类型对于获得系统的的良好性能至关重要。不管存储哪种类型的数据，下面几个简单的原则都有助于做出更好的选择

更小的通常更好

一般情况下，应该选择能够正确存储数据的最小的数据类型。更小的数据类型通常更快，因为他们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期也更少。
但是要确保没有低估需要存储数据值的存储范围，在schema中要增加数据类型的存储范围是一件很耗时的工作。

简单就好

简单的数据类型通常占用更少的CPU周期。例如，整数比字符操作代价更低，因为字符集和排序规则使字符比整数更复杂。

尽量不使用null

很多表可能包含可为null的列，即使应用程序不需要null值，这是因为可能默认值就是null。通常情况下最好指定为not null，除非真的需要存储null值。
如果查询中有包含null值的列，对MySQL来说更难优化，应为null值会使得索引、索引统计和值比较变的更加复杂。可为null值的列需要更多的存储空间，对MySQL来说需要特殊处理。当可为null的值被索引时，每个索引记录需要一个额外的字节。
通常把可为null值的列改为not null带来的性能提升比较小，所以没必要在现有的schema中查找并修改，除非确定这回导致问题。但是如果计划在列上建立索引时，应该尽量避免值可为null。

下一步是选择具体的数据类型。很多MySQL的数据类型可以存储相同类型的数据，只是在存储长度和范围、允许的精度不同，或者物理存储空间不同。相同大类型的不同子类型数据有时候也有一些特殊的行为和属性。
例如，DATETIME和TIMESTAMP都可以存储相同的时间类型：时间和日期，精确到秒。然而，TIMESTMAP只使用DATETIME一半的存储空间，并且会根据时区变化，具有特殊的自动更新能力。另一方面，TIMESTMAP允许的时间范围也小的多。

默认时间戳(Timestamp)类型的取值范围为’1970-01-01 00:00:01’ UTC 至’2038-01-19 03:14:07’ UTC，数据精确到秒级别。MySQL内部使用4个字节的INT类型来表示，约22亿个数值来表示从’1970-01-01 00:00:01’ UTC之后的每一秒。

整数类型

如果需要存储整数，可以选用这几种整数类型：TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT。分别使用4，12，24，32，64为存储空间。可以存储的范围是$-2^{(n-1)}$–$2^{(n-1)}-1$，n表示空间的位数。
整数类型可有UNSIGNED属性，表示不允许负值。可以是整数的上限提高一倍，存储范围是$2^{(n)}-1$。

整数在存储时，如果要表示负数，需要用1位来表示符号位，即整数的正负。有符号和无符号占用相同的存储空间并具有相同的性能。

MySQL可以为整数指定显示宽度，如：INT(11)，对大多数应用没有意义，它不会限制值的合法性，只是规定了MySQL的一些交互工具用来显示这个字符的个数。对于存储来说，INT(1)和INT(20)没有差别。

实数类型

实数是带有小数部分的数字。它也不只是用来存储小数，也可以使用DECIMAL存储比BIGINT还大的整数。
FLOAT和DOUBLE支持使用标准的浮点运算进行近似计算。FLOAT占用4个字节，DOUBLE占用8个字节；MySQL选择DOUBLE作为内部浮点计算的类型。

对于DECIMAL，可以指定小数点前后允许的最大位数。例如，DECIMAL(18,9)小数点两边各存9个数字，一共使用9个字节；小数点前的数字用4个字节，小数点后的数字用4个字节，小数点本身占1个字节。
因为CPU不支持对DECIMAL的直接计算，所以MySQL服务器自身实现了对DECIMAL类型的高精度计算。相对CPU直接支持原生浮点计算的类型，DECIAML的计算相对较慢一些。

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

字符串类型

VARCHAR和CHAR类型

VARCHAR

VARCHAR类型可用于存储变长的字符串，它比定长的字符串类型更节省空间。
VARCHAR需要额外的1或者2个字节记录字符长度（最大长度小于或者等于255则需要1个字节表示，否则需要2个字节表示）。由于行是变长的，在UPDATE的时候，可能是原来的行更长，这就需要额外的工作，不同的引擎处理方式不同；MyISAM会将行拆成不通的片段存储，InnoDB会分裂页来是行可以存储页中。
比较适合使用VARCHAR类型：

• 字符串列的最大长度比平均长度大很多
• 列的更新很少，不容易造成碎片
• 使用了像UTF-8这样复杂的字符集，每个字符都使用不同的字节数进行存储

CHAR

CHAR类型是定长的；MySQL根据字符串定义分配存储空间。字符长处不够分配长度时，内部会在末尾以空格填充，这种方式会导致如果字符串末尾真的包含空格，在存储时空格会丢失。
例如：

mysql> CREATE TABLE char_test( char_col CHAR(10));
mysql> INSERT INTO char_test (char_col) VALUES 
	-> ('test1'),('   test2'),('test3   ');

mysql> SELECT CONCAT("'",char_col,"'") FROM char_test;
+--------------------------+
| CONCAT("'",char_col,"'") |
+--------------------------+
|'test1'			       |
|'   test2'				   |
|'test3'				   |
+--------------------------+

比较适合选择CHAR类型：

• 存储很短的字符串（不需要像VARCHAR一样需要额外自己存储长度）
• 所有值长度相同或者接近（例如：MD5加密后的值）

枚举类型代替字符串类型

有时候可以使用枚举类型来代替常用的字符串。枚举类型可以把一些不重复的字符串存储成一个预定义的字符串集。MySQL存储枚举类型非常紧凑，通常会根据表值的列数压缩到一个或两个字节中。
MySQL在内部以整数的形式保存枚举类型，并会保存一个“数字–字符串”的映射关系的查找表。
例如：

mysql>CREATE TABLE em_test(
mysql> e EMUM('b','a','c') NOT NULL
mysql> );

这三个数据实际存储为整数，而不是字符串：

mysql>select e + 0 from em_test;
+------+
|e + 0 |
+------+
|	  1|
|	  2|
|	  3|
+------+

排序也是通过存储的整数来排序的：

mysql>select e from em_test ORDER BY e;
+------+
|e + 0 |
+------+
|	  b|
|	  a|
|	  c|
+------+

因为这种双重性的存在，不要使用数字来作为枚举的常量，很容易搞混乱。
枚举的字符串列是固定的。新增或删除枚举类型都需要使用ALTER TABLE，因此，对于一些未来可能有变化的字符串，最好避免使用枚举类型。

时间和日期类型

MySQL提供两种日期日期类型：DATETIME和TIMESTMAP，两种类型都是秒级的时间粒度（MariaDB支持微妙级的时间粒度）。

DATETIME

这个类型能保存大范围的时间值，1001年到9999年，精度为秒。它把时间值封装成格式为YYYYMMDDHHMMSS的整数中。使用8个字节的空间存储。

TIMESTMAP

这个类型保存了从1970年1月1日午夜（格林尼治时间）以来的所有秒数，和UNIX时间戳一致。TIMESTMAP使用4个字节存储空间。所以能保存的时间范围要小的多。TIMESTMAP显示的值依赖时区。
除了特殊情况下，应该尽量选择使用TIMESTMAP，它比DATETIME的空间效率要高很多。有时候，人们会采用Unix时间戳存储为整数值，但这不会带来太大是收益，很不直观。

如果要存储比秒粒度更小的时间格式，MySQL目前没有提供合适的数据类型，但可以由其他方案代替。如：使用BIGINT类型存储微妙级别的时间戳，或使用DOUBLE存储秒之后的小数部分。

MySQL各数据类型大小及长度

数字型

类型	大小	范围（有符号）	范围（无符号）
TINYINT	1 字节	(-128，127)	(0，255)
SMALLINT	2 字节	(-32 768，32 767)	(0，65 535)
MEDIUMINT	3 字节	(-8 388 608，8 388 607)	(0，16 777 215)
INT	4 字节	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)
BIGINT	8 字节	(-9 233 372 036 854 775 808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)
FLOAT	4 字节	(-3.402 823 466 E+38，1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)）	0，(1.175 494 351 E-38，3.402 823 466 E+38)）
DOUBLE	8 字节	(1.797 693 134 862 315 7 E+308，2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)）	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值

字符型

类型	长度	作用
CHAR	0-255字节	定长字符串
VARCHAR	0-255字节	变长字符串
TINYBLOB	0-255字节	不超过 255 个字符的二进制字符串
TINYTEXT	0-255字节	短文本字符串
BLOB	0-65 535字节	二进制形式的长文本数据
TEXT	0-65 535字节	长文本数据
MEDIUMBLOB	0-16 777 215字节	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215字节	中等长度文本数据
LOGNGBLOB	0-4 294 967 295字节	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295字节	极大文本数据

时间型

类型	大小 （字节）	范围	格式
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD
TIME	3	‘-838:59:59’/‘838:59:59’	HH:MM:SS
YEAR	1	1901/2155	YYYY
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYYMMDDHHMMSS
TIMESTAMP	4	1970-01-01 00:00:01/2038-01-19 03:14:07	YYYY-MM-DD HH:MM:SS