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

使用ENUM代替字符串类型

有时候， 可以通过使用ENUM来代理常规的字符串类型。一个ENUM列能够存储65535个不同的字符串值，MySQL非常紧凑的存储这些值，会根据值列表把这些值存储到1到2个字节中。通过在表的.frm文件中保存一个数字到字符串的对应关系来保存一个”查询表”， 它把每个值存储成一个表示值在字段定义列表中的某个位置的整数。 下面是几个例子;

mysql> CREATE TABLE enum_test(

-> e ENUM(‘fish’, ‘apple’, ‘dog’) NOT NULL

-> );

mysql> INSERT INTO enum_test(e) VALUES(‘fish’), (‘dog’), (‘apple’);

这3列实际上存储的是整数， 而不是字符串。 能够通过在数字检索上下文中查看到这些值的两面属性：

mysql> SELECT e + 0 FROM enum_test;

+——-+

| e + 0 |

+——-+

| 1 |

| 3 |

| 2 |

+——-+

如果指定数字当做ENUM常量的话， 这个两面性太容易引起混淆了。建议不要这么做。

另外一个令人奇怪的是ENUM字段通过整数值来排序， 而不是字符串本省：

mysql> SELECT e FROM enum_test ORDER BY e;

+——-+

| e |

+——-+

| fish |

| apple |

| dog |

+——-+

通过指定期望的ENUM数字的排序顺序来解决这个问题。也可以通过在查询中显式的使用FIELD()来指定排序顺序， 但是这回使得MySQL在排序中无法使用索引:

mysql> SELECT e FROM enum_test ORDER BY FIELD(e, ‘apple’, ‘dog’, ‘fish’);

+——-+

| e |

+——-+

| apple |

| dog |

| fish |

+——-+

ENUM最大的缺点是字符串列表是固定的，增加或者删除一个字符串需要使用ALTER TABLE。因而， 如果列表中允许的字符串值可能会变化的话， 使用ENUM可能不是一个好的主意。MySQL在它自己的权限表中使用了ENUM来表示Y和N.

因为MySQL把每个值存储整数， 不得不在需要的时候做一些查询工作来转化成字符串表示，ENUM有一些开销的。这些有可能会通过他们相对较小的大小来缓冲， 但并不总是这样。特别是， 把CHAR或者VARCHAR列并成一个ENUM列比并成CHAR或者VARCHAR列要慢。

为了演示一下，我们对MySQL在一个应用中的一个表上做join做性能测试. 这个表有一个很宽的主键：

CREATE TABLE webservicecalls (

day date NOT NULL,

account smallint NOT NULL,

service varchar(10) NOT NULL,

method varchar(50) NOT NULL,

calls int NOT NULL,

items int NOT NULL,

time float NOT NULL,

cost decimal(9,5) NOT NULL,

updated datetime,

PRIMARY KEY (day, account, service, method)

) ENGINE=InnoDB;

这个表包含了大概110,000条记录，大小只有10M, 所以能够全部放在内存中。service列包含有5个不同的值， 平均长度是4个字符， method列包含有71个值， 平均长度20个字符。

我们对这个表做了一个拷贝， 把service和method字段转化成ENUM类型，如下：

CREATE TABLE webservicecalls_enum (

… 省略 …

service ENUM(…values omitted…) NOT NULL,

method ENUM(…values omitted…) NOT NULL,

… 省略 …

) ENGINE=InnoDB;

然后测试联合两个表的主键列来测试性能，使用的查询如下：

mysql> SELECT SQL_NO_CACHE COUNT(*)

-> FROM webservicecalls

-> JOIN webservicecalls USING(day, account, service, method);

然后改变一下查询， 以不同的组合来联合VARCHAR和ENUM字段，表3-1中列出了结果。

表3-1. 联合VARCHAR和ENUM列的

Test Queries per second

VARCHAR joined to VARCHAR 2.6

VARCHAR joined to ENUM 1.7

ENUM joined to VARCHAR 1.8

ENUM joined to ENUM 3.5

把列转化成ENUM后， 联合速度变快了。但是联合ENUM和VARCHAR列慢一些。在这个例子中， 只要不需要与VARCHAR列进行联合，看起来转化成ENUM是一个不错的注意。

而且， 做类型转换有另外一个好处: 从SHOW TABLE STATUS结果着那个的Data_length列的值来看，把这两列换成ENUM后， 表只有原来大小的1/3了。 在某些情况下， 即使需要做与VARCHAR列的联合， 也是有好处的。 在转换后，主键的大小也只有原来的的一半。 因为这是一个InnoDB表， 如果还有其他索引的话，减少主键的大小可以使得索引大大减小。