-- 创建一个临时内存表

set global log_bin_trust_function_creators=1;

DROP TABLE IF EXISTS `vote_recordss_memory`;

CREATE TABLE `vote_recordss_memory` (

    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

    `user_id` varchar(20) NOT NULL DEFAULT '',

    `vote_num` int(10) unsigned NOT NULL DEFAULT '',

    `group_id` int(10) unsigned NOT NULL DEFAULT '',

    `status` tinyint(2) unsigned NOT NULL DEFAULT '',

    `create_time` datetime NOT NULL DEFAULT '1971-01-01 01:01:01',

    PRIMARY KEY (`id`),

    KEY `index_user_id` (`user_id`) USING HASH

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

-- 创建一个普通表，用作模拟大数据的测试用例

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 '' COMMENT '投票数',

    `group_id` int(10) unsigned NOT NULL DEFAULT '' COMMENT '用户组id 0-未激活用户 1-普通用户 2-vip用户 3-管理员用户',

    `status` tinyint(2) unsigned NOT NULL DEFAULT '' 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='投票记录表';

-- 为了数据的随机性和真实性，我们需要创建一个可生成长度为n的随机字符串的函数。

-- 创建生成长度为n的随机字符串的函数

DELIMITER // -- 修改MySQL 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 //

-- 为了操作方便，我们再创建一个插入数据的存储过程

-- 创建插入数据的存储过程

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 ;  -- 改回默认的 MySQL delimiter：';'

-- 开始执行存储过程，等待生成数据(100W条)

-- 调用存储过程 生成100W条数据

CALL insert_vote_recordss_memory(10000000);

-- 查询内存表已生成记录(为了下步测试，目前仅生成了x条)

SELECT count(*) FROM `vote_recordss_memory`;

-- 把数据从内存表插入到普通表中(100w条数据ys就插入完了)

INSERT INTO vote_records SELECT * FROM `vote_recordss_memory`;

-- 查询普通表已的生成记录

SELECT count(*) FROM `vote_records`;

# 实时监控mysql的工具

https://blog.csdn.net/u013820054/article/details/54022456

---------+-------mysql-status-------+-----threads-----+-----slow-----+---bytes---+---------locks----------

time     |   QPS  TPS  ins  upd  del| run  con cre cac| sql  tmp Dtmp| recv  send| lockI lockW openT openF

---------+--------------------------+-----------------+--------------+-----------+------------------------

:: |               |             |           |   0K    8K|

:: |               |             |           |   0K    8K|

:: |               |             |           |   0K    8K|

:: |               |             |           |   0K    8K|

:: |               |             |           |   0K    8K|

:: |               |             |           |   0K    8K|

:: |               |             |           |   0K    8K|

:: |               |             |           |   0K    8K|

:: |               |             |           |   0K    8K|

:: |               |             |           |   0K    8K|

:: |               |             |           |   0K    8K|

:: |               |             |           |   0K    8K|

:: |               |             |           |   0K    8K|

:: |               |             |           |   0K    8K|

:: |               |             |           |   0K    8K|

:: |               |             |           |   0K    8K|

:: |               |             |           |   0K    8K|

:: |               |             |           |   0K    8K|

:: |               |             |           |   0K    8K|

:: |               |             |           |   0K    8K|