MySQL表设计优化与索引 (十)

Bit-Packed Data Types

MySQL有一些存储类型使用一个值中的一些单个的比特位来紧凑的存储数据。纯技术上将，不管是底层的存储格式还是操作，所有这些类型都是字符串类型。

BIT

MySQL5.0以前， BIT只是TINYINT的同义词而已。但是在MySQL5.0以及之后的版本，BIT是一个完全不同的数据类型了， 有着自己的一些特点， 这里讨论一些新的行为和属性:

可以用BIT字段在单列里面来存储一个或多个true/false值， BIT(1)定义一个了只包含单个比特位的字段， BIT(2)是存储2个比特位的字段， 如此类推;BIT字段的最大长度可以是64个比特。

BIT类型的行为与存储引擎有关。MyISAM把一些列为了存储的目的打包到一起， 所以17个单独的BIT列需要17个比特来存储(假定这些列都不允许NULL)，MyISAM会近似算成3个字节来存储。其他的一些存储引擎， 比如Memory和InnoDB, 把每个列都用有足够长度存储这些比特位的最小整数来存储，所以无法节省存储空间。

* TIMESTAMP类型的一些行为的规则比较复杂并随着不同的MySQL版本而变化，所以在使用时应当确认是自己所期望的行为。通常， 在对TIMESTAMP的列做了改变后通过查看SHOW CREATE TABALE的结果来确认是一个的主意。

MySQL把BIT当做字符串类型， 而不是数据类型。当检索BIT(1)列的值， 结果是一个字符串而内容是二进制位0或1， 而不是ASCII值”0″或”1″.然而， 如果在一个数值上下文检索的话， 结果是比特串转化而成的数字。 当需要与另一个值进行比较时， 记住这一点。比如， 如果存储值’00111001′(是57的二进制表示)到一个BIT(8)的字段中然后检索出来，得到的是字符编码值为57字符串， 而这值就是“9”的ASCII编码。但是在数值环境中， 得到的是值57:s

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类型。对于大多数应用程序来说，避免使用这个类型比较的好。

如果想在单个比特位的存储空间中存储true/false值的另一个选择是使用可以为NULL的CHAR(0)列。这个列能够存储NULL和长度为0的空串。

SET

如果需要存储多个true/false的值， 可以考虑把多个列放到一个MySQL所支持的SET数据类型，而MySQL内部通过一些比特位来表示的。这种类型有效的使用存储空间， MySQL也有一些函数如FIND_IN_SET( )和FIELD( )来方便查询。 最主要的缺点是改变列定义的开销： 需要ALTER TABLE, 而这个操作在一个大表上则是开销非常大的(参考后面有关于替换方法的讨论)。一般来说， 也无法在SET列上使用索引。

Bitwise operations on integer columns

整型列上的位操作

SET类型的一个替代办法是把一个整数当做一些比特位的集合。 比如，可以把一个TINYINT数当做8个比特位， 用位运算来操作其中的比特位， 可以通过在应用程序中位每一个比特位定义命名常数来简化理解。

这种方法相对于SET的主要的好处是可以不需要ALTER TABLE就能改变枚举值。缺点是查询写起来麻烦且不容易理解(当第5个比特位为1是什么意思?)。有些人喜欢使用位操作而有些人不喜欢，因而是否使用这个技巧很大程度上是个人口味的问题。

把比特位打成包的一个例子应用程序是存储权限的访问控制列表(ACL).每个比特或SET元素表示一个CAN_READ, CAN_WRITE或者CAN_DELETE之类的值。 如果使用SET列， 需要在MySQL的列定义中存储比特到值得映射关系; 如果使用整数列， 则需要在应用中存储这个映射关系。下面是一些使用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 |

+——-+

使用了变量来存储值， 不过在代码中可以使用常数来代替。