MySQL构建百万级数据

在进行环境测试时，往往会用到可能很庞大的数据量，这个数据量就需要我们自己生成，借助内存表我们可以用以下方式生成。
以下操作基于MySQL8.0.15

一、内存表：

1.介绍：

  内存表的表结构建立在磁盘里面，数据放在内存里面，当mysql重启之后，内存表的数据会丢失，表结构依旧存在会执行一次truncate操作。
内存表的特点：

• 内存表使用hash索引把数据保存在内存中，具有更快的速度，可以用来缓存。
• 内存表对所有的用户连接都是可用的。这就意味着，多个会话连接的内存表名字不能重复，具有唯一性。
• 内存表如果复制数据进去的话，所有的原有格式都不会存在，需要重新设置
• 重启造成数据丢失，可以drop表之后重新复制数据等。这是最傻瓜的方法了。
• 支持简单的操作符>=<这三个，我认为内存表用来缓存的话，应该不会涉及很复杂的操作。
• 内存表的默认引擎是memory

针对于内存表的这种特性我们可以先在内存表中生成数据然后插入到测试表这样速度相对快一些。而且在重启后会自动释放内存表数据节省空间。

2.创建内存表：

mysql> CREATE TABLE `vote_records_memory` (
    ->     `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->     `user_id` varchar(20) NOT NULL DEFAULT '',
    ->     `vote_num` int(10) unsigned NOT NULL DEFAULT '0',
    ->     `group_id` int(10) unsigned NOT NULL DEFAULT '0',
    ->     `status` tinyint(2) unsigned NOT NULL DEFAULT '1',
    ->     `create_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
    ->     PRIMARY KEY (`id`),
    ->     KEY `index_user_id` (`user_id`) USING HASH
    -> ) ENGINE=MEMORY AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
##复制
CREATE TABLE `vote_records_memory` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `user_id` varchar(20) NOT NULL DEFAULT '',
    `vote_num` int(10) unsigned NOT NULL DEFAULT '0',
    `group_id` int(10) unsigned NOT NULL DEFAULT '0',
    `status` tinyint(2) unsigned NOT NULL DEFAULT '1',
    `create_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (`id`), KEY `index_user_id` (`user_id`) USING HASH
) ENGINE=MEMORY AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

二、构建过程

1.创建测试表

创建完成内存表后继续创建测试表

DROP TABLE IF EXISTS `vote_records`;
CREATE TABLE `vote_records` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `user_id` varchar(20) NOT NULL DEFAULT '' COMMENT '用户Id',
    `vote_num` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '投票数',
    `group_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '用户组id 0-未激活用户 1-普通用户 2-vip用户 3-管理员用户',
    `status` tinyint(2) unsigned NOT NULL DEFAULT '1' COMMENT '状态 1-正常 2-已删除',
    `create_time` datetime  NOT NULL DEFAULT '1971-01-01 01:01:01' COMMENT '创建时间',
    PRIMARY KEY (`id`),
    KEY `index_user_id` (`user_id`) USING HASH COMMENT '用户ID哈希索引'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='投票记录表';

2.创建随机字符串函数

为了保证数据的随机性创建一个可生成长度为n的随机字符串函数。定义过程如下：

set global log_bin_trust_function_creators=1;#默认不允许创建function。
DELIMITER //
DROP FUNCTION IF EXISTS `rand_strings` //
SET NAMES utf8 //
CREATE FUNCTION `rand_strings` (n INT) RETURNS VARCHAR(255) CHARSET 'utf8'
BEGIN 
    DECLARE char_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
    DECLARE return_str varchar(255) DEFAULT '';
    DECLARE i INT DEFAULT 0;
    WHILE i < n DO
        SET return_str = concat(return_str, substring(char_str, FLOOR(1 + RAND()*62), 1));
        SET i = i+1;
    END WHILE;
    RETURN return_str;
END //

3.创建存储过程

接下来是数据插入部分建立一个自动插入的存储过程。

DROP PROCEDURE IF EXISTS `insert_vote_recordss_memory`
CREATE PROCEDURE `insert_vote_recordss_memory`(IN n INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE vote_num INT DEFAULT 0;
    DECLARE group_id INT DEFAULT 0;
    DECLARE status TINYINT DEFAULT 1;
    WHILE i < n DO
        SET vote_num = FLOOR(1 + RAND() * 10000);
        SET group_id = FLOOR(0 + RAND()*3);
        SET status = FLOOR(1 + RAND()*2);
        INSERT INTO `vote_recordss_memory` VALUES (NULL, rand_strings(20), vote_num, group_id, status, NOW());
        SET i = i + 1;
    END WHILE;
END //
DELIMITER ; #改回默认的';'

4.生成数据

准备工作完成开始生成
调用存储过程 生成1000条数据

CALL insert_vote_recordss_memory(1000);#按需要进行修改。

查询生成的数据量

mysql> select count(*) from vote_records_memory;
+----------+
| count(*) |
+----------+
|      999 |
+----------+
1 row in set (0.00 sec)

把数据从内存表插入到普通表中

INSERT INTO vote_records SELECT * FROM `vote_recordss_memory`;
mysql> select count(*) from vote_records;
+----------+
| count(*) |
+----------+
|     999  |
+----------+
1 row in set (0.00 sec)