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

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

各位同学，大家好！今天我们来深入探讨 MySQL 中两个特殊的枚举类型：SET 和 ENUM。它们在数据类型设计中扮演着重要的角色，尤其是在处理有限且预定义值的字段时。然而，如何正确地使用它们，以及它们对性能的影响，是我们需要重点关注的问题。

1. ENUM 类型：单选枚举

ENUM 类型允许你定义一个字符串列表，字段的值只能是列表中的某一个。这非常适合存储状态、类别等信息。

1.1 语法与示例

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    status ENUM('active', 'inactive', 'pending') NOT NULL DEFAULT 'pending'
);

INSERT INTO products (name, status) VALUES
('Product A', 'active'),
('Product B', 'inactive'),
('Product C', 'pending');

-- 尝试插入非法值
-- INSERT INTO products (name, status) VALUES ('Product D', 'deleted'); -- 会报错

SELECT * FROM products;

优点：

• 数据一致性： 强制字段只能存储预定义的值，防止脏数据。
• 存储空间： ENUM 类型内部以整数存储，根据枚举值的数量，可能只需要 1 或 2 个字节。例如，最多 255 个成员的枚举使用 1 字节，最多 65535 个成员的枚举使用 2 字节。
• 可读性： 虽然内部存储是整数，但查询结果会显示字符串值，提高了可读性。

缺点：

• 灵活性差： 修改枚举列表需要修改表结构，这可能会导致锁表和数据迁移，影响线上服务。
• 查询效率： 虽然内部存储是整数，但在进行字符串比较时，性能不如直接比较整数类型。

1.2 内部存储机制

ENUM 类型的值实际上存储的是索引，从 1 开始。 你可以通过如下方式查询：

SELECT name, status, status+0 AS status_index FROM products;

这个查询会将 ENUM 类型的值转换为整数索引。

1.3 使用场景

• 表示商品状态（上架、下架、待审核）。
• 表示订单状态（待支付、已支付、已发货、已完成、已取消）。
• 表示用户性别（男、女）。

1.4 性能考量

虽然 ENUM 类型节省存储空间，但在某些情况下，性能可能不如直接使用 TINYINT 或 SMALLINT 类型，特别是当需要频繁进行字符串比较时。

示例：性能对比

-- 创建使用 ENUM 的表
CREATE TABLE enum_table (
    id INT PRIMARY KEY AUTO_INCREMENT,
    status ENUM('active', 'inactive', 'pending') NOT NULL
);

-- 创建使用 TINYINT 的表
CREATE TABLE tinyint_table (
    id INT PRIMARY KEY AUTO_INCREMENT,
    status TINYINT NOT NULL
);

-- 插入大量数据
DELIMITER //
CREATE PROCEDURE insert_data(IN table_name VARCHAR(255), IN num_rows INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    WHILE i < num_rows DO
        IF table_name = 'enum_table' THEN
            INSERT INTO enum_table (status) VALUES (ELT(FLOOR(1 + RAND() * 3), 'active', 'inactive', 'pending'));
        ELSE
            INSERT INTO tinyint_table (status) VALUES (FLOOR(RAND() * 3));
        END IF;
        SET i = i + 1;
    END WHILE;
END //
DELIMITER ;

CALL insert_data('enum_table', 100000);
CALL insert_data('tinyint_table', 100000);

-- 查询性能对比
-- ENUM
SELECT COUNT(*) FROM enum_table WHERE status = 'active';
-- TINYINT (假设 0: active, 1: inactive, 2: pending)
SELECT COUNT(*) FROM tinyint_table WHERE status = 0;

-- 清理测试数据
DROP PROCEDURE insert_data;
DROP TABLE enum_table;
DROP TABLE tinyint_table;

通过以上测试，你可以观察到，当使用整数类型的 status 字段进行查询时，性能通常优于使用 ENUM 类型。 这是因为数据库可以直接进行整数比较，而不需要进行字符串查找和转换。

2. SET 类型：多选枚举

SET 类型允许你定义一个字符串列表，字段的值可以是列表中一个或多个值的组合。这非常适合存储权限、标签等信息。

2.1 语法与示例

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    permissions SET('read', 'write', 'execute')
);

INSERT INTO users (name, permissions) VALUES
('User A', 'read,write'),
('User B', 'write,execute'),
('User C', 'read,execute'),
('User D', 'read');

SELECT * FROM users;

优点：

• 数据紧凑： SET 类型内部以位向量存储，可以高效地存储多个值的组合。
• 方便的查找： 可以使用 FIND_IN_SET() 函数来查找包含特定值的记录。

缺点：

• 复杂性： SET 类型的操作比 ENUM 类型更复杂，需要使用位运算或字符串函数。
• 灵活性差： 类似于 ENUM 类型，修改 SET 列表需要修改表结构。
• 可读性差： 存储的是位向量，直接查看数据不容易理解。

2.2 内部存储机制

SET 类型的值实际上存储的是一个二进制数，每一位代表一个枚举值是否被选中。 例如，如果 SET 定义为 'read', 'write', 'execute'，那么：

• 'read' 对应 1 (二进制 001)
• 'write' 对应 2 (二进制 010)
• 'execute' 对应 4 (二进制 100)
• 'read,write' 对应 3 (二进制 011)
• 'read,execute' 对应 5 (二进制 101)
• 'write,execute' 对应 6 (二进制 110)
• 'read,write,execute' 对应 7 (二进制 111)

你可以使用 field+0 强制转换为数字查看。

2.3 使用场景

• 表示用户权限（读、写、执行、删除）。
• 表示商品标签（新品、热销、促销）。
• 表示兴趣爱好（音乐、电影、体育）。

2.4 查询技巧

• FIND_IN_SET() 函数： 查找包含特定值的记录。

  SELECT * FROM users WHERE FIND_IN_SET('read', permissions) > 0;

• 位运算： 可以使用位运算进行更复杂的查询，但可读性较差。 例如，假设 read 对应 1, write 对应 2, execute 对应 4，查找拥有 read 和 write 权限的用户：

  SELECT * FROM users WHERE (permissions+0 & (1+2)) = (1+2);  --  不推荐，可读性差

2.5 性能考量

SET 类型的性能瓶颈主要在于字符串操作和位运算。 当数据量较大时，FIND_IN_SET() 函数的性能会下降。

示例：性能对比

-- 创建使用 SET 的表
CREATE TABLE set_table (
    id INT PRIMARY KEY AUTO_INCREMENT,
    permissions SET('read', 'write', 'execute')
);

-- 创建使用 INT 的表
CREATE TABLE int_table (
    id INT PRIMARY KEY AUTO_INCREMENT,
    permissions INT
);

-- 插入大量数据
DELIMITER //
CREATE PROCEDURE insert_data(IN table_name VARCHAR(255), IN num_rows INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE perm INT;
    WHILE i < num_rows DO
        SET perm = FLOOR(RAND() * 8); -- 0-7 的随机数，对应 SET 的各种组合
        IF table_name = 'set_table' THEN
            INSERT INTO set_table (permissions) VALUES (ELT(perm+1, '', 'read', 'write', 'read,write', 'execute', 'read,execute', 'write,execute', 'read,write,execute'));
        ELSE
            INSERT INTO int_table (permissions) VALUES (perm);
        END IF;
        SET i = i + 1;
    END WHILE;
END //
DELIMITER ;

CALL insert_data('set_table', 100000);
CALL insert_data('int_table', 100000);

-- 查询性能对比
-- SET
SELECT COUNT(*) FROM set_table WHERE FIND_IN_SET('read', permissions) > 0;
-- INT (假设 1: read, 2: write, 4: execute)
SELECT COUNT(*) FROM int_table WHERE (permissions & 1) = 1;

-- 清理测试数据
DROP PROCEDURE insert_data;
DROP TABLE set_table;
DROP TABLE int_table;

在以上示例中，使用 INT 类型存储权限，并使用位运算进行查询，通常比使用 SET 类型和 FIND_IN_SET() 函数性能更高。

3. ENUM 和 SET 的替代方案

由于 ENUM 和 SET 类型存在灵活性差、查询性能可能不佳等问题，在实际应用中，我们可以考虑以下替代方案：

• 使用 TINYINT、SMALLINT 等整数类型： 对于 ENUM 类型，可以使用整数类型来代替，并使用常量或枚举类来定义值的含义。 对于 SET 类型，可以使用 INT 或 BIGINT 类型来存储位向量，并使用位运算进行查询。

• 使用关联表： 对于多对多关系，可以使用关联表来存储关系。 例如，可以使用一个 user_permissions 表来存储用户和权限之间的关系。

• 使用 JSON 类型： MySQL 5.7 及以上版本支持 JSON 类型，可以使用 JSON 数组来存储多个值的集合。 JSON 类型具有更好的灵活性，但查询性能可能不如整数类型。

表格对比：ENUM、SET 与替代方案

特性	ENUM	SET	TINYINT/INT + 常量	关联表	JSON
数据一致性	强制	强制	代码层面保证	数据库层面保证	代码层面保证
存储空间	较小	较小	较大 (取决于类型)	较大 (需要额外索引)	较大 (存储字符串)
查询性能	可能较差	较差	较好	较好 (取决于索引)	可能较差
灵活性	差	差	较好	好	好
可读性	好	差	较好 (取决于常量定义)	较好	较好
适用场景	有限的、固定的单选值	有限的、固定的多选值	需要灵活扩展的单/多选值	多对多关系	需要灵活扩展的多值集合
修改成本	高 (修改表结构)	高 (修改表结构)	低 (修改常量定义)	中 (添加/删除记录)	低 (修改 JSON 内容)

4. 最佳实践

• 谨慎使用 ENUM 和 SET 类型： 在确定字段的值是有限的、固定的，并且不会频繁修改的情况下，可以考虑使用 ENUM 和 SET 类型。 否则，建议使用替代方案。

• 选择合适的替代方案： 根据实际需求选择合适的替代方案。 如果需要高性能，可以使用整数类型和位运算。 如果需要灵活性，可以使用关联表或 JSON 类型。

• 注意性能测试： 在生产环境中使用任何数据类型之前，都应该进行充分的性能测试，以确保其满足性能要求。

• 考虑数据迁移： 如果需要将现有的 ENUM 或 SET 字段迁移到其他类型，需要仔细考虑数据迁移方案，以避免数据丢失或损坏。

5. 案例分析

案例 1：商品状态

假设我们需要存储商品的状态，状态包括：上架、下架、待审核、已删除。

• 方案一：使用 ENUM 类型

  CREATE TABLE products (
      id INT PRIMARY KEY AUTO_INCREMENT,
      name VARCHAR(255) NOT NULL,
      status ENUM('上架', '下架', '待审核', '已删除') NOT NULL DEFAULT '待审核'
  );

  缺点： 如果后续需要添加新的状态，需要修改表结构。

• 方案二：使用 TINYINT 类型 + 常量

  CREATE TABLE products (
      id INT PRIMARY KEY AUTO_INCREMENT,
      name VARCHAR(255) NOT NULL,
      status TINYINT NOT NULL DEFAULT 2 -- 0: 上架, 1: 下架, 2: 待审核, 3: 已删除
  );
  
  -- 常量定义 (例如在 PHP 代码中):
  define('PRODUCT_STATUS_ACTIVE', 0);
  define('PRODUCT_STATUS_INACTIVE', 1);
  define('PRODUCT_STATUS_PENDING', 2);
  define('PRODUCT_STATUS_DELETED', 3);

  优点： 添加新的状态只需要修改常量定义，不需要修改表结构。

案例 2：用户权限

假设我们需要存储用户的权限，权限包括：读、写、执行、删除。

• 方案一：使用 SET 类型

  CREATE TABLE users (
      id INT PRIMARY KEY AUTO_INCREMENT,
      name VARCHAR(255) NOT NULL,
      permissions SET('读', '写', '执行', '删除')
  );

  缺点： 查询性能可能较差，修改权限列表需要修改表结构。

• 方案二：使用 INT 类型 + 位运算

  CREATE TABLE users (
      id INT PRIMARY KEY AUTO_INCREMENT,
      name VARCHAR(255) NOT NULL,
      permissions INT -- 1: 读, 2: 写, 4: 执行, 8: 删除
  );
  
  -- 常量定义:
  define('PERMISSION_READ', 1);
  define('PERMISSION_WRITE', 2);
  define('PERMISSION_EXECUTE', 4);
  define('PERMISSION_DELETE', 8);
  
  -- 例如，授予用户读写权限:
  UPDATE users SET permissions = PERMISSION_READ | PERMISSION_WRITE WHERE id = 1;
  
  -- 查询拥有读权限的用户:
  SELECT * FROM users WHERE (permissions & PERMISSION_READ) = PERMISSION_READ;

  优点： 查询性能更好，添加新的权限只需要修改常量定义，不需要修改表结构。

总结

ENUM 和 SET 是 MySQL 中有用的数据类型，但需要谨慎使用。 在选择数据类型时，需要综合考虑数据一致性、存储空间、查询性能、灵活性和可读性等因素。 在实际应用中，建议优先考虑使用 TINYINT/INT + 常量、关联表或 JSON 类型等替代方案，以获得更好的性能和灵活性。理解内部存储机制，可以帮助你更好地进行性能优化和问题排查。