MySQL高级特性之：`MySQL`的`SET`和`ENUM`：其在`SQL`类型设计中的性能考量。 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

MySQL高级特性之：SET和ENUM在SQL类型设计中的性能考量

大家好，今天我们来深入探讨MySQL中两种特殊的数据类型：SET和ENUM。它们都属于枚举类型，用于限制字段的取值范围，但它们的设计和使用方式有着显著的区别，并且会对数据库的性能产生不同的影响。在SQL类型设计中，理解和选择合适的枚举类型至关重要。

1. ENUM类型：明确的单值选择

ENUM类型允许你为一个字段定义一个预定义的字符串列表，该字段只能取列表中的一个值。这种类型的优点在于它能够强制数据完整性，并且在存储上相对高效。

1.1 ENUM的定义和使用

ENUM的定义方式如下：

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    category ENUM('Electronics', 'Clothing', 'Books', 'Home Goods') NOT NULL
);

在这个例子中，category字段只能取’Electronics’, ‘Clothing’, ‘Books’, ‘Home Goods’这四个值之一。

插入数据时，你可以直接使用字符串值：

INSERT INTO products (name, category) VALUES ('Laptop', 'Electronics');
INSERT INTO products (name, category) VALUES ('T-Shirt', 'Clothing');

或者，你也可以使用索引值（从1开始）：

INSERT INTO products (name, category) VALUES ('Novel', 3); -- 相当于 'Books'

1.2 ENUM的存储机制

MySQL内部使用数字来存储ENUM值，而不是直接存储字符串。 ENUM列表中的每个值都会被分配一个整数索引，从1开始。这样做的好处是节省存储空间，特别是当ENUM列表很长时。例如，如果ENUM列表包含最多255个值，则ENUM字段只需要1个字节存储。如果列表包含256到65535个值，则需要2个字节。

1.3 ENUM的性能考量

存储空间： ENUM使用整数存储，因此比直接存储字符串更节省空间，尤其是在需要存储大量数据时，这种优势会更加明显。
查询效率： 在进行查询时，MySQL可以直接比较整数值，而不需要进行字符串比较，这可以提高查询效率。
索引： 可以像其他数据类型一样，为ENUM字段创建索引，进一步提高查询效率。
修改成本： 修改ENUM的定义（例如，添加或删除值）需要重建表，这是一个耗时的操作，尤其是在大型表中。因此，在定义ENUM时，应该仔细考虑所有可能的取值，并预留一定的扩展空间。
排序： ENUM默认按照其在定义中的顺序排序，而不是按照字母顺序。可以通过FIELD()函数自定义排序规则，但这会增加查询的复杂度。

1.4 ENUM的适用场景

ENUM适用于以下场景：

字段的取值范围是固定的，并且很少变化。
需要强制数据完整性，确保字段只能取预定义的值。
需要节省存储空间，并提高查询效率。

2. SET类型：多值选择的灵活方案

SET类型与ENUM类似，也允许你为一个字段定义一个预定义的字符串列表。不同之处在于，SET字段可以同时取列表中的多个值。

2.1 SET的定义和使用

SET的定义方式如下：

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    hobbies SET('Reading', 'Sports', 'Music', 'Travel')
);

在这个例子中，hobbies字段可以同时包含’Reading’, ‘Sports’, ‘Music’, ‘Travel’中的多个值，或者一个值都不包含。

插入数据时，你可以使用逗号分隔的字符串值：

INSERT INTO users (name, hobbies) VALUES ('Alice', 'Reading,Sports');
INSERT INTO users (name, hobbies) VALUES ('Bob', 'Music');
INSERT INTO users (name, hobbies) VALUES ('Charlie', ''); -- 表示没有爱好

或者，也可以使用数字值，数字表示每个选项在集合中的位置，然后将这些位置的2的幂加起来。例如：

‘Reading’ 是第一个选项，对应 2^0 = 1
‘Sports’ 是第二个选项，对应 2^1 = 2
‘Music’ 是第三个选项，对应 2^2 = 4
‘Travel’ 是第四个选项，对应 2^3 = 8

因此，’Reading,Sports’ 可以表示为 1 + 2 = 3。

INSERT INTO users (name, hobbies) VALUES ('David', 3); -- 相当于 'Reading,Sports'

2.2 SET的存储机制

与ENUM类似，MySQL内部也使用数字来存储SET值。SET列表中的每个值都会被分配一个二进制位，如果该值存在于SET字段中，则对应的二进制位为1，否则为0。例如，如果SET列表包含8个值，则SET字段需要1个字节存储。如果列表包含9到16个值，则需要2个字节。列表包含17到24个值，需要3个字节，以此类推。

2.3 SET的性能考量

存储空间： SET的存储空间取决于SET列表的大小。每个值占用一个二进制位，因此SET列表越大，需要的存储空间就越多。
查询效率： 使用FIND_IN_SET()函数可以查询包含特定值的SET字段。但是，FIND_IN_SET()函数不能使用索引，因此在大型表中查询效率较低。
索引： 虽然不能直接为SET字段创建索引，但是可以通过其他方式来提高查询效率。例如，可以创建一个辅助表，将SET字段中的每个值都存储为单独的一行，然后为辅助表创建索引。
修改成本： 与ENUM类似，修改SET的定义也需要重建表，这是一个耗时的操作。
位运算： MySQL实际上使用位运算来处理SET类型。这使得某些操作（例如检查是否包含某个值）非常高效。但是，也使得调试和理解查询变得更加困难。

2.4 SET的适用场景

SET适用于以下场景：

字段的取值范围是固定的，并且很少变化。
需要允许字段同时包含多个值。
对存储空间的要求不高，并且可以接受较低的查询效率。

3. ENUM vs SET：选择的权衡

特性	`ENUM`	`SET`
取值数量	单个值	多个值
存储空间	较小，取决于`ENUM`列表的大小	较大，取决于`SET`列表的大小
查询效率	较高，可以使用索引	较低，`FIND_IN_SET()`函数不能使用索引
修改成本	高，需要重建表	高，需要重建表
适用场景	单值选择，数据完整性要求高，性能敏感	多值选择，对性能要求不高，灵活性要求高

4. 性能优化策略

避免过度使用SET： 如果SET列表很大，或者需要频繁查询SET字段，则应该考虑使用其他数据类型，例如关联表。
合理选择ENUM和SET： 根据实际需求选择合适的枚举类型。如果只需要单值选择，则应该使用ENUM。如果需要多值选择，则可以使用SET。
使用关联表代替SET： 如果需要频繁查询SET字段，并且对性能要求很高，则可以考虑使用关联表。关联表可以将SET字段中的每个值都存储为单独的一行，然后为关联表创建索引，从而提高查询效率。例如，可以将上面的users表修改为以下结构：
```
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL
);

CREATE TABLE user_hobbies (
    user_id INT NOT NULL,
    hobby ENUM('Reading', 'Sports', 'Music', 'Travel') NOT NULL,
    PRIMARY KEY (user_id, hobby),
    FOREIGN KEY (user_id) REFERENCES users(id)
);
```
这样，就可以使用索引来查询用户的爱好，从而提高查询效率。
谨慎修改定义： 修改ENUM或SET的定义都需要重建表，所以应该在设计阶段仔细考虑所有可能的取值，并预留一定的扩展空间。
对于SET类型的查询优化： 如果必须使用SET类型，并且需要查询包含特定值的记录，可以考虑使用全文索引（FULLTEXT index）。但是，全文索引也有其局限性，例如只适用于MyISAM和InnoDB存储引擎，并且对中文支持不好。
考虑使用位图： 对于非常大的SET类型，可以考虑使用位图（bitmap）来存储和查询。位图是一种非常高效的数据结构，可以用来表示一个集合。但是，位图的实现比较复杂，需要一定的编程技巧。

5. 实际案例分析

假设我们正在设计一个在线购物网站的数据库。我们需要存储商品的属性，例如颜色、尺寸、材质等。

颜色： 颜色是一个典型的ENUM类型。因为颜色是有限的，并且很少变化。例如，可以定义一个ENUM('Red', 'Green', 'Blue', 'Black', 'White')。
尺寸： 尺寸也可以使用ENUM类型，例如ENUM('S', 'M', 'L', 'XL', 'XXL')。但是，如果尺寸的种类很多，或者经常需要添加新的尺寸，则应该考虑使用关联表。
材质： 材质可以使用SET类型。因为一件商品可能由多种材质组成。例如，可以定义一个SET('Cotton', 'Polyester', 'Silk', 'Wool')。但是，如果需要频繁查询具有特定材质的商品，则应该考虑使用关联表。
商品状态： 商品状态可以使用ENUM类型，例如ENUM('On Sale', 'Out of Stock', 'Pre-order', 'Normal')。

代码案例：使用ENUM和SET的完整示例

-- 创建数据库
CREATE DATABASE IF NOT EXISTS ecommerce;
USE ecommerce;

-- 创建商品表
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    color ENUM('Red', 'Green', 'Blue', 'Black', 'White') NOT NULL,
    sizes ENUM('S', 'M', 'L', 'XL', 'XXL') NOT NULL,
    materials SET('Cotton', 'Polyester', 'Silk', 'Wool'),
    status ENUM('On Sale', 'Out of Stock', 'Pre-order', 'Normal') NOT NULL
);

-- 插入数据
INSERT INTO products (name, price, color, sizes, materials, status) VALUES
('T-Shirt', 25.99, 'Red', 'M', 'Cotton', 'On Sale'),
('Jeans', 59.99, 'Blue', 'L', 'Cotton,Polyester', 'Normal'),
('Sweater', 49.99, 'Green', 'XL', 'Wool', 'Out of Stock'),
('Dress', 79.99, 'Black', 'S', 'Silk', 'Normal'),
('Shirt', 39.99, 'White', 'L', 'Cotton', 'Pre-order');

-- 查询红色商品
SELECT * FROM products WHERE color = 'Red';

-- 查询包含棉材质的商品
SELECT * FROM products WHERE FIND_IN_SET('Cotton', materials);

-- 查询所有在售商品
SELECT * FROM products WHERE status = 'On Sale';

-- 查询所有包含棉和涤纶材质的商品，可以使用位运算的特性进行查询
SELECT * FROM products WHERE (materials & (1+2)) = (1+2);
-- 棉: 'Cotton' 对应 1
-- 涤纶: 'Polyester' 对应 2
-- 棉和涤纶: 对应 3
-- 但是这种写法可读性太差，所以不推荐使用

代码案例：使用关联表代替SET

-- 创建商品表
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    color ENUM('Red', 'Green', 'Blue', 'Black', 'White') NOT NULL,
    sizes ENUM('S', 'M', 'L', 'XL', 'XXL') NOT NULL,
    status ENUM('On Sale', 'Out of Stock', 'Pre-order', 'Normal') NOT NULL
);

-- 创建商品材质表
CREATE TABLE product_materials (
    product_id INT NOT NULL,
    material ENUM('Cotton', 'Polyester', 'Silk', 'Wool') NOT NULL,
    PRIMARY KEY (product_id, material),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- 插入数据
INSERT INTO products (name, price, color, sizes, status) VALUES
('T-Shirt', 25.99, 'Red', 'M', 'On Sale'),
('Jeans', 59.99, 'Blue', 'L', 'Normal'),
('Sweater', 49.99, 'Green', 'XL', 'Out of Stock'),
('Dress', 79.99, 'Black', 'S', 'Normal'),
('Shirt', 39.99, 'White', 'L', 'Pre-order');

INSERT INTO product_materials (product_id, material) VALUES
(1, 'Cotton'),
(2, 'Cotton'),
(2, 'Polyester'),
(3, 'Wool'),
(4, 'Silk'),
(5, 'Cotton');

-- 查询包含棉材质的商品
SELECT p.*
FROM products p
JOIN product_materials pm ON p.id = pm.product_id
WHERE pm.material = 'Cotton';

6. 注意事项

ENUM和SET的定义长度有限制，最大为65535个不同的元素。
ENUM和SET的值不区分大小写。
在进行查询时，应该使用字符串值，而不是索引值。虽然可以使用索引值，但是会降低代码的可读性。

7. 避免误用：JSON类型的替代方案

在某些情况下，开发者可能会考虑使用SET类型来存储动态的、数量不定的选项。然而，对于高度灵活和动态的数据结构，JSON数据类型通常是更好的选择。 JSON允许你存储任意结构的数据，无需预先定义模式。

例如，如果商品的特性非常多样化且经常变化，使用JSON来存储这些特性会更加灵活。

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    details JSON
);

INSERT INTO products (name, price, details) VALUES (
    'Fancy Gadget',
    99.99,
    '{"color": "Silver", "features": ["Wireless", "Bluetooth", "Noise Cancelling"]}'
);

-- 查询包含 "Bluetooth" 特性的商品 (MySQL 5.7+):
SELECT * FROM products WHERE JSON_CONTAINS(details, '"Bluetooth"', '$.features');

虽然 JSON 提供了极大的灵活性，但查询效率可能不如 ENUM 或 SET（如果能有效利用索引）。选择 JSON 通常是在灵活性和性能之间进行权衡的结果。

总结

ENUM和SET是MySQL中两种特殊的枚举类型，它们可以用于限制字段的取值范围，并提高数据完整性和存储效率。但是，它们也有一些缺点，例如修改成本高，查询效率较低。在选择ENUM和SET时，应该根据实际需求进行权衡。对于需要频繁查询的字段，应该考虑使用关联表或其他数据类型。了解它们的存储机制、性能特点，能够帮助我们在SQL类型设计中做出更明智的选择。

MySQL高级特性之：SET和ENUM在SQL类型设计中的性能考量

发表回复 取消回复

发表回复取消回复