MySQL性能测试

-- 创建表
CREATE TABLE test_count_performance (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),        -- 高区别度字段
    status TINYINT,            -- 低区别度字段
    category VARCHAR(50),      -- 中等区别度字段
    created_at DATETIME,       -- 创建时间字段
    updated_at DATETIME,       -- 更新时间字段
    INDEX idx_name (name),     -- 为 name 字段创建索引
    INDEX idx_created_at (created_at)  -- 为 created_at 字段创建索引
);

-- 初始化基础数据 (10 条记录)
INSERT INTO test_count_performance (name, status, category, created_at, updated_at)
VALUES
    (CONCAT('Name_', FLOOR(RAND() * 100000)), FLOOR(RAND() * 3), CONCAT('Category_', FLOOR(RAND() * 100)),
     DATE_ADD('2023-01-01', INTERVAL FLOOR(RAND() * 365) DAY), DATE_ADD('2023-01-01', INTERVAL FLOOR(RAND() * 365) DAY)),
    (CONCAT('Name_', FLOOR(RAND() * 100000)), FLOOR(RAND() * 3), CONCAT('Category_', FLOOR(RAND() * 100)),
     DATE_ADD('2023-01-01', INTERVAL FLOOR(RAND() * 365) DAY), DATE_ADD('2023-01-01', INTERVAL FLOOR(RAND() * 365) DAY)),
    (CONCAT('Name_', FLOOR(RAND() * 100000)), FLOOR(RAND() * 3), CONCAT('Category_', FLOOR(RAND() * 100)),
     DATE_ADD('2023-01-01', INTERVAL FLOOR(RAND() * 365) DAY), DATE_ADD('2023-01-01', INTERVAL FLOOR(RAND() * 365) DAY)),
    (CONCAT('Name_', FLOOR(RAND() * 100000)), FLOOR(RAND() * 3), CONCAT('Category_', FLOOR(RAND() * 100)),
     DATE_ADD('2023-01-01', INTERVAL FLOOR(RAND() * 365) DAY), DATE_ADD('2023-01-01', INTERVAL FLOOR(RAND() * 365) DAY)),
    (CONCAT('Name_', FLOOR(RAND() * 100000)), FLOOR(RAND() * 3), CONCAT('Category_', FLOOR(RAND() * 100)),
     DATE_ADD('2023-01-01', INTERVAL FLOOR(RAND() * 365) DAY), DATE_ADD('2023-01-01', INTERVAL FLOOR(RAND() * 365) DAY)),
    (CONCAT('Name_', FLOOR(RAND() * 100000)), FLOOR(RAND() * 3), CONCAT('Category_', FLOOR(RAND() * 100)),
     DATE_ADD('2023-01-01', INTERVAL FLOOR(RAND() * 365) DAY), DATE_ADD('2023-01-01', INTERVAL FLOOR(RAND() * 365) DAY)),
    (CONCAT('Name_', FLOOR(RAND() * 100000)), FLOOR(RAND() * 3), CONCAT('Category_', FLOOR(RAND() * 100)),
     DATE_ADD('2023-01-01', INTERVAL FLOOR(RAND() * 365) DAY), DATE_ADD('2023-01-01', INTERVAL FLOOR(RAND() * 365) DAY)),
    (CONCAT('Name_', FLOOR(RAND() * 100000)), FLOOR(RAND() * 3), CONCAT('Category_', FLOOR(RAND() * 100)),
     DATE_ADD('2023-01-01', INTERVAL FLOOR(RAND() * 365) DAY), DATE_ADD('2023-01-01', INTERVAL FLOOR(RAND() * 365) DAY)),
    (CONCAT('Name_', FLOOR(RAND() * 100000)), FLOOR(RAND() * 3), CONCAT('Category_', FLOOR(RAND() * 100)),
     DATE_ADD('2023-01-01', INTERVAL FLOOR(RAND() * 365) DAY), DATE_ADD('2023-01-01', INTERVAL FLOOR(RAND() * 365) DAY)),
    (CONCAT('Name_', FLOOR(RAND() * 100000)), FLOOR(RAND() * 3), CONCAT('Category_', FLOOR(RAND() * 100)),
     DATE_ADD('2023-01-01', INTERVAL FLOOR(RAND() * 365) DAY), DATE_ADD('2023-01-01', INTERVAL FLOOR(RAND() * 365) DAY));

-- 倍增插入数据，直到达到 300 万条记录
-- 每次将已有的数据插入新数据
INSERT INTO test_count_performance (name, status, category, created_at, updated_at)
SELECT 
    CONCAT('Name_', FLOOR(RAND() * 100000)), 
    FLOOR(RAND() * 3), 
    CONCAT('Category_', FLOOR(RAND() * 100)),
    DATE_ADD('2023-01-01', INTERVAL FLOOR(RAND() * 365) DAY), 
    DATE_ADD('2023-01-01', INTERVAL FLOOR(RAND() * 365) DAY)
FROM test_count_performance;

-- 重复执行上面的 INSERT ... SELECT FROM 语句，直到表中的记录数达到 300 万

select  count(*) from test_count_performance ;


SELECT COUNT(*) FROM test_count_performance WHERE status = 1;

SELECT COUNT(*) FROM test_count_performance WHERE name = 'Name_12345';

SELECT COUNT(*) FROM test_count_performance WHERE created_at BETWEEN '2023-01-01' AND '2023-06-30';

EXPLAIN SELECT COUNT(*) FROM test_count_performance WHERE name = 'Name_12345';