MySQL ENUM vs VARCHAR性能实测：百万级数据对比实验

大家好！上期文章发布后，很多读者对ENUM和VARCHAR的性能差异很感兴趣。今天我就带大家完整还原这个百万级数据的性能测试过程，用数据说话！

测试环境配置

• 硬件配置：
  • 处理器：第13代英特尔酷睿i5-13500H
  • 内存：16GB DDR4
  • 存储：高性能SSD
• 软件环境：
  • MySQL版本：5.7.26（InnoDB引擎）
  • 字符集：utf8

📊 测试表结构设计

我们设计了两张结构相同但字段类型不同的表：

-- ENUM版本表
CREATE TABLE user_enum (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50),
    gender ENUM('Male','Female','Other','Unknown'),
    reg_time DATETIME,
    INDEX idx_gender (gender)
);

-- VARCHAR版本表
CREATE TABLE user_varchar (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50),
    gender VARCHAR(10),
    reg_time DATETIME,
    INDEX idx_gender (gender)
);

🧑‍💻 测试数据生成

使用存储过程生成100万条测试数据：

CREATE DEFINER=`root`@`localhost` PROCEDURE `generate_test_data2`()
BEGIN
  DECLARE i INT DEFAULT 0;
    DECLARE gender_val VARCHAR(10);
    
    WHILE i < 1000000 DO
        -- 随机生成性别（确保两组数据一致）
        SET gender_val = CASE FLOOR(RAND()*4)
            WHEN 0 THEN 'Male'
            WHEN 1 THEN 'Female'
            WHEN 2 THEN 'Other'
            ELSE 'Unknown'
        END;
        
        -- 插入ENUM表
        INSERT INTO user_enum (username, gender, reg_time)
        VALUES (CONCAT('user', i), gender_val, NOW());
        
        -- 插入VARCHAR表
        INSERT INTO user_varchar (username, gender, reg_time)
        VALUES (CONCAT('user', i), gender_val, NOW());
        
        SET i = i + 1;
    END WHILE;
END

⚙️ 测试指标与方法

我们测试了五个关键指标：

1. 存储空间占用

   -- 查看表大小
   SELECT 
       table_name, 
       data_length/1024/1024 AS data_mb,
       index_length/1024/1024 AS index_mb
   FROM information_schema.tables 
   WHERE table_schema = DATABASE();
   

2. 精确查询性能

   -- 启用性能分析
   SET profiling = 1;
   -- 各执行100次取平均值
   SELECT SQL_NO_CACHE * FROM user_enum WHERE gender = 'Female' LIMIT 1000;
   SELECT SQL_NO_CACHE * FROM user_varchar WHERE gender = 'Female' LIMIT 1000;
   -- 查看耗时
   SHOW PROFILES;
   

3. 范围查询性能

   -- 启用性能分析
   SET profiling = 1;
   -- 测试排序查询
   SELECT SQL_NO_CACHE * FROM user_enum ORDER BY gender LIMIT 100000;
   SELECT SQL_NO_CACHE * FROM user_varchar ORDER BY gender LIMIT 100000;
   -- 查看耗时
   SHOW PROFILES;
   

4. 插入性能

   -- 启用性能分析
   SET profiling = 1;
   -- 测试单条插入
   INSERT INTO user_enum (username, gender) VALUES ('test', 'Male');
   INSERT INTO user_varchar (username, gender) VALUES ('test', 'Male');
   -- 查看耗时
   SHOW PROFILES;
   

5. 索引大小比较

   SHOW INDEX FROM user_enum;
   SHOW INDEX FROM user_varchar;
   

🔍 结果深度分析

1. 存储优势：

   • ENUM内部存储为整数（1-4），而VARCHAR存储实际字符串
   • 性别字段：ENUM固定1字节，VARCHAR平均4-6字节

2. 查询优势：

   -- ENUM实际执行的是数字比较
   WHERE gender = 2  -- 'Female'的索引值
   
   -- VARCHAR执行的是字符串比较
   WHERE gender = 'Female'
   

3. 索引效率：

   • ENUM索引的基数(Cardinality)更低
   • B+树索引的层级更少