高性能MySQL阅读笔记（1）--- 数据类型

整数类型

MySQL 包含以下几种整数类型：TINYINT SMALLINT MEDIUMINT INT BIGINT，分别使用8，16，24，32，64位存储空间。他们可以存储的范围为-2^(n-1)到2^(n-1)-1，其中 n 是存储空间位数。
整数类型有可选的 UNSIGNED 属性，表示不允许负数，这大致可以使正数的上限提升一倍，2^n - 1。有符号和无符号的使用相同的存储空间，并且具有相同的性能。
MySQL 可以为正数指定宽度，例如INT(11),对大多数应用没有意义，它不会限制值得合法范围，只是规定了 MySQL 的一些交互工具用来展示字符的个数而已。对于存储和计算来说 INT(1) 和 INT(20) 是一样的。
不同类型的选择决定了数据存储方式，但是系统的整数计算是基于64位整数的，即使在32位系统中。

实数类型

MySQL 包含3中实数类型：FLOAT DOUBLE DECIMAL，FLOAT 使用4个字节，DOUBLE 使用8个字节，DECIMAL 根据具体精度定。
FLOAT 和 DOUBLE 用于使用标准的浮点数进行近似计算。DECIMAL 用于精确计算，通常可以用来存储比 BiGINT 还要大的整数。
MySQL 使用 DOUBLE 做为内部浮点数计算类型。

字符串类型

VARCHAR和CHAR类型

VARCHAR 和 CHAR 是两种最基本的数据类型。

VARCHAR

VARCHAR 类型用于存储可变长字符串，它比定长字符串更节省空间，因为它仅使用必要的空间。有一种情况例外，如果 MySQL 表使用 ROW_FORMAT=FIXED 创建的话，每一行会使用定长存储，这会很浪费空间。
VARCHAR 需要额外的字节存储字符串的长度，如果列的最大长度小于或等于255字节，则只需要一个字节表示，否则需要两个字节。
VARCHAR 节省空间，会带来性能提升。但是行是边长的，在 UPDATE 的时候可能使行变得比原来长，并且页内没有足够的空间存储，这种情况下不同的存储引擎会有不同的处理方案。比如 MyISAM 会将行拆为不同的片段处理，InnoDB 则需要分裂页来使行可以放入页内。
以下情况适合使用 VARCHAR：字符串列的最大长度比平均长度大很多；列的更新很少，所以碎片不是问题；使用了像UTF8这样的字符集，每个字符都使用不同的字节数存储。

CHAR

CHAR 类型是定长的，MySQL 根据定义分配固定大小的空间。当存储CHAR类型的时候系统会删除所有末尾的空格。CHAR 会根据需要使用空格进行填充方便比较。
CHAR 适合存储很短的字符串，或则所有值长度都接近一个长度。例如 CHAR 非常适合存储 MD5 的值，因为他的长度是固定的。对于经常变更的数据，CHAR 比 VARCHAR 更合适，因为不会产生碎片。对于非常短的列，CHAR 也比 VARCHAR合适，因为 VARCHAR 需要一个额外的空间存储长度。

与 CHAR 和 VARCHAR 类似的类型还有 BINARY 和 VARBINARY，他们用于存储二进制字符串，二进制字符串存储的是字节码而不是字符。填充也不一样：MySQL 使用 \0 （零字节）而不是空额，在检索时也不会去掉填充值。
当需要存储二进制数据，并且希望 MySQL 使用字节码而非字符进行比较时，这些类型非常有用。二进制优势不仅仅体现在大小写敏感上。MySQL 比较 BINARY 时，每次按一个字节，并且根据该字节的数值比较。因此二进制比较比字符比较简单很多，所以更快。

BLOB 和 TEXT 类型

BLOB 和 TEXT 是为了存储很大的数据而设计的字符串数据类型，分别采用二进制和字符串方式存储。BLOB 类型包含：TINYBLOB、SMALLBLOB、BLOB、MEDIMUBLOB、LONGBLOB，BLOB 是 SMALLBLOB 同义词。TEXT 类型包含：TINYTEXT、SMALLTEXT、TEXT、MEDIUMTEXT、LONGTEXT，TEXT 是 SMALLTEXT 的同义词。
与其他类型不同，MySQL 把 BLOB 和 TEXT 当做一个独立对象处理。存储引擎在存储时通常会做特殊处理。当 BLOB 和 TEXT 过大时，InnoDB 会使用专门的“外部”区域存储实际的值，此时每个值在行内只需要1-4个字节存储一个指针。
BLOB 和 TEXT 不同：BLOB 是二进制数据，而且没有字符集和排序规则而 TEXT 有。
MySQL 对 BLOB 和 TEXT 做排序和其他类型不同：它只对每个列的前 max_sort_length 字节而不是整个字符串排序。如果只需要排序前面一小部分字符，可以修改 max_sort_length，或则使用 order by sustring(column, length)。
MySQL 无法将 BLOB 和 TEXT 列全部长度字符串进行索引，也没有办法使用索引来消除排序。

枚举类型

枚举列可以把一些不重复的字符串存储为一个预定义的集合。MySQL 存储枚举类型非常紧凑，会根据列表值得数量压缩到一个或两个字节中。MySQL 会在内部每个列表值得位置表示为整数，并且在表的定义文件.frm中定义数字-字符串的映射关系。
下面有个例子：

mysql> create table enum_test(
    -> e enum('fish','apple','dog') not null
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> insert into enum_test(e) values ('fish'),('dog'),('apple');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

这三行数据实际存储的是数字而不是字符串。可以通过数字上下文环境监测看到这个双重属性：

mysql> select e+0 from enum_test;
+-----+
| e+0 |
+-----+
|   1 |
|   3 |
|   2 |
+-----+
3 rows in set (0.01 sec)

如果使用数字作为 ENUM 的枚举常量，这种双重数据很容易混乱，例如 ENUM（‘1’，‘2’，‘3’）。建议避免这样做。
另外枚举字段是按照内部的整数而不是字符串进行排序的：

mysql> select e from enum_test order by e;
+-------+
| e     |
+-------+
| fish  |
| apple |
| dog   |
+-------+
3 rows in set (0.01 sec)

绕过这种限制一种方式是定义的时候按照实际的顺序定义枚举。另外在查询中使用 FIELD() 指定排序顺序，但是这将无法使用索引消除排序。

mysql> select e from enum_test order by field(e,'apple','dog','fish');
+-------+
| e     |
+-------+
| apple |
| dog   |
| fish  |
+-------+
3 rows in set (0.01 sec)

枚举最不好的地方，字符串列表是固定的，添加或删除字符串需要使用 alter table，而这将会导致锁表。

日期和时间类型

MySQL 可以使用许多类型来保存日期和时间，例如 YEAR 和 DATE。MySQL 能存储最小时间粒度是秒。

DATETIME

DATETIME 能保存的范围为 1001 年到9999年，精度为秒。它日期和时间封装到YYYYMMDDHHMMSS的整数中，与时区无关。使用8个字节的存储空间。
默认情况下MySQL使用一种可排序的、无歧义的格式显示DATETIME值，例如：“2018-10-26 03:24:00”。

TIMESTAMP

TIMESTAMP 保存的是从 1970 年 1 月 1 日午夜以来的秒数，和 UNIX 时间戳相同。TIMESTAMP 使用4个字节的存储空间，可表示 1970年到2038年。MySQL 提供了两个函数：FROM_UNIXTIME()函数把UNIX转化为日期，UNIX_TIMESTAMP()函数把日期转化为 UNIX 时间戳。
TIMESTAMP 的值依赖于时间戳。
我们需要注意 TIMESTAMP 类型默认是 NOT NULL 的，新建表的时候如果不指定第一个 timestamp 类型字段的默认值，那么MySQL 会自动设置默认值 是 CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP，并且 第二个 TIMESTAMP 类型的字段必须手动指定默认值或指定可为 NULL 或则建表将会失败。例如：

mysql> create table timestamp_test( a int not null auto_increment, b timestamp, c timestamp ,primary key(a));                  ERROR 1067 (42000): Invalid default value for 'c'

我们指定第二个 TIMESTAMP 字段可为 NULL

mysql> create table timestamp_test( a int not null auto_increment, b timestamp, c timestamp null,primary key(a));
Query OK, 0 rows affected (0.04 sec)
mysql> desc timestamp_test;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type      | Null | Key | Default           | Extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| a     | int(11)   | NO   | PRI | NULL              | auto_increment              |
| b     | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| c     | timestamp | YES  |     | NULL              |                             |
+-------+-----------+------+-----+-------------------+-----------------------------+
3 rows in set (0.01 sec)

TIMESTAMP 比 DATETIME 有更高的空间效率，所以尽量使用 TIMESTAMP。如果需要保存比秒更加精切的时间可以使用 BIGINT 保存毫秒级别的时间戳，或则用 DOUBLE 保存秒之后的小数部分。

位数据类型

MySQL有几种位存储数据类型，比如：BIT SET。所有这些位类型，不管底层格式和处理方式如何，从技术上讲都是字符串类型。

BIT

在 MySQL5.0 之前，BIT 是 TINYINT 的同义词，但是之后的版本中 BIT 是一个单独的数据类型。
BIT可以用来存储一个或多个 ture/false 值。BIT(2) 包含两个位，BIT 列最大长度是64位。
BIT 的存储因存储引擎而不同，MyISAM 会打包所有 BIT 位，所以 17 个 BIT 列只需要 17 个位存储即3个字节就可以了。其他存储引擎如 Memory 和 InnoDB 为每个 BIT 列使用一个足够存储的最小整数类型来存储，所以不能节省空间。
MySQL 把 BIT 当做字符串类型而不是数字类型。但检索 BIT 类型时返回的是字符串0、1组成的字符串。而在数值上下文中结果又将转化为数字。例如：

mysql> insert into bit_test values(b'00111001');
Query OK, 1 row affected (0.01 sec)

mysql> select a, a+0 from bit_test;
+------+------+
| a    | a+0  |
+------+------+
| 9    |   57 |
+------+------+
1 row in set (0.00 sec)

所以我们需要谨慎使用 BIT 类型，其实我们应该尽量避免使用。

SET

如果需要保存很多 ture/false 值，我们可以合并这些到一个 SET 数据类型中，它在数据库内部是以一些列打包的位的集合表示的。节省空间，并且 FIND_IN_SET() 和 FIELD()这样的函数也方便我们查询。缺点就是改变列的定义代价比较高，需要用 alter table。通常 SET 列也无法使用索引。
SET 类型演示

mysql> create table acl(
    -> perms set('can_read','can_write','can_delete') not null
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> insert into acl(perms) values('can_read,can_write');
Query OK, 1 row affected (0.00 sec)

mysql> select perms from acl where find_in_set('can_read',perms);
+--------------------+
| perms              |
+--------------------+
| can_read,can_write |
+--------------------+
1 row in set (0.01 sec)

整数列表示位

我们通常可以使用整数列来表示位，比如 TINYINT 可以表示8位列，具体的逻辑可以写在业务代码中。缺点就是比较难理解。