mysql 快速模拟千万级别数据量

太阳上的雨天

1235人浏览 · 2022-05-28 14:47:43

太阳上的雨天 · 2022-05-28 14:47:43 发布

使用存储过程模拟千万级别的数据量。两个案列

一案列一

创建test数据库和数据表

CREATE DATABASE test;



-- 创建一个临时内存表
DROP TABLE IF EXISTS `vote_record_memory`;
CREATE TABLE `vote_record_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 CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `index_user_id` (`user_id`) USING HASH
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `vote_record`;
CREATE TABLE `vote_record` (
    `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 CURRENT_TIMESTAMP COMMENT '创建时间',
    PRIMARY KEY (`id`),
    KEY `index_user_id` (`user_id`) USING HASH COMMENT '用户ID哈希索引'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='投票记录表';


-- 创建生成长度为n的随机字符串的函数
DELIMITER // -- 修改MySQL delimiter：'//'
DROP FUNCTION IF EXISTS `rand_string` //
SET NAMES utf8 //
CREATE FUNCTION `rand_string` (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 `add_vote_record_memory` 
 
DELIMITER //
CREATE PROCEDURE `add_vote_record_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; 
    DECLARE id INT 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);
        SET id = i;
        INSERT INTO `vote_record_memory` VALUES (id, rand_string(20), vote_num, group_id, status, NOW());
        SET i = i + 1;
    END WHILE;
END //
DELIMITER ;  -- 改回默认的 MySQL delimiter：';'


CALL add_vote_record_memory(10000000);

二案例二

CREATE TABLE `e_a_order` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `merchant_id` bigint NOT NULL COMMENT '商户id',
  `account_id` bigint NOT NULL COMMENT '账户id',
  `origin_order_no` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '商户外部订单号',
  `order_time` timestamp NOT NULL COMMENT '订单创建时间',
  `account` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '客户账号',
  `channel_id` bigint NOT NULL COMMENT '通道id',
  `amount` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '订单金额',
  `notify_url` varchar(512) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '回调地址',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `version` bigint NOT NULL,
  `fail_reason` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '失效原因',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `origin_order_no_idx` (`origin_order_no`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2470599 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;


CREATE TABLE `e_b_order` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `merchant_id` bigint NOT NULL COMMENT '商户id',
  `account_id` bigint NOT NULL COMMENT '账户id',
  `order_no` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '平台内部订单号',
  `origin_order_no` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '商户外部订单号',
  `channel_id` bigint NOT NULL COMMENT '通道id',
  `match_method` tinyint NOT NULL COMMENT '匹配方式',
  `c_payment_method` tinyint DEFAULT NULL COMMENT 'c支付方式',
  `money_status` tinyint NOT NULL COMMENT '资金状态',
  `order_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '订单创建时间',
  `order_status` tinyint NOT NULL COMMENT '订单状态',
  `account` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '充值账号',
  `extend_info` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '扩展信息',
  `amount` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '订单金额',
  `fee_rate` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '手续费率',
  `deal_amount` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '划账金额',
  `payment_url` varchar(2048) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '支付链接',
  `receive_payment_time` timestamp NULL DEFAULT NULL COMMENT '接收支付链接时间',
  `examine_url` varchar(4096) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '检测链接',
  `agent_info` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '代理信息',
  `script_info` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '脚本信息',
  `url_ext` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT 'url扩展信息',
  `payment_status` tinyint NOT NULL COMMENT '支付状态',
  `payment_id` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '成功凭证(网厅订单号)',
  `payment_time` timestamp NULL DEFAULT NULL COMMENT '支付时间',
  `error_code` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '订单失败代码',
  `error_message` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '订单失败原因',
  `delivery_status` tinyint NOT NULL COMMENT '发货状态',
  `delivery_time` timestamp NULL DEFAULT NULL COMMENT '发货时间',
  `notify_status` tinyint NOT NULL COMMENT '回调状态',
  `notify_params` varchar(512) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '回调参数',
  `notify_result` varchar(512) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '回调返回结果',
  `notify_time` timestamp NULL DEFAULT NULL COMMENT '回调时间',
  `notify_url` varchar(512) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '回调地址',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `version` bigint NOT NULL,
  `pay_amount` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '支付金额',
  `province` varchar(255) DEFAULT NULL COMMENT '省份',
  `good_type` tinyint DEFAULT NULL COMMENT '商品类型',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uk_order_no` (`order_no`) USING BTREE,
  KEY `account_id_idx` (`account_id`) USING BTREE,
  KEY `origin_order_no_idx` (`origin_order_no`) USING BTREE,
  KEY `channel_id_idx` (`channel_id`) USING BTREE,
  KEY `order_time_idx` (`order_time`) USING BTREE,
  KEY `customer_account_idx` (`account`) USING BTREE,
  KEY `amount_idx` (`amount`) USING BTREE,
  KEY `payment_id_idx` (`payment_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1687004 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;


CREATE TABLE `e_c_order` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `merchant_id` bigint NOT NULL COMMENT '商户id',
  `account_id` bigint NOT NULL COMMENT '账户id',
  `order_no` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '平台内部订单号',
  `origin_order_no` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '商户外部订单号',
  `product_name` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '商品名称',
  `order_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '订单创建时间',
  `order_status` tinyint NOT NULL COMMENT '订单状态',
  `channel_id` bigint NOT NULL COMMENT '通道id',
  `payment_method` tinyint DEFAULT NULL COMMENT '支付方式',
  `amount` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '订单金额',
  `fee_rate` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '手续费率',
  `deal_amount` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '划账金额',
  `last_match_time` timestamp NULL DEFAULT NULL COMMENT '最后匹配时间',
  `match_ip` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '匹配ip',
  `match_terminal` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '匹配的终端信息',
  `b_payment_status` tinyint DEFAULT NULL COMMENT '支付状态',
  `b_payment_id` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '成功凭证(网厅订单号',
  `b_payment_time` timestamp NULL DEFAULT NULL COMMENT '支付时间',
  `notify_status` tinyint NOT NULL COMMENT '回调状态',
  `notify_params` varchar(512) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '回调参数',
  `notify_result` varchar(512) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '回调返回结果',
  `notify_time` timestamp NULL DEFAULT NULL COMMENT '回调时间',
  `notify_url` varchar(512) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '回调地址',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `version` bigint NOT NULL,
  `paying_start_time` timestamp NULL DEFAULT NULL COMMENT '开始支付时间',
  `paying_limit_millis` int DEFAULT NULL COMMENT '支付时间上限',
  `cashier_domain` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '' COMMENT '收银台地址',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `order_no_idx` (`order_no`) USING BTREE,
  KEY `origin_order_no_idx` (`origin_order_no`) USING BTREE,
  KEY `order_time_idx` (`order_time`) USING BTREE,
  KEY `amount_idx` (`amount`) USING BTREE,
  KEY `b_payment_id_idx` (`b_payment_id`) USING BTREE,
  KEY `account_id_idx` (`account_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1718842 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

-- ======================================================================================================================================================
-- e_a_order

DROP PROCEDURE IF EXISTS `add_e_a_order` 
 
DELIMITER //
CREATE PROCEDURE `add_e_a_order`(IN n INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE id INT DEFAULT 1;
    WHILE i < n DO
        SET id = i;
        INSERT INTO `e_a_order` VALUES (id, 1, 1, "1234567", now(), "1", 1, "10000", "http://192.168.0.103:9527/notifya", now(), now(), 1, "无");
        SET i = i + 1;
    END WHILE;
END //
DELIMITER ;  -- 改回默认的 MySQL delimiter：';'


CALL add_e_a_order(10000000); 



-- ======================================================================================================================================================
-- e_b_order


-- 创建生成长度为n的随机字符串的函数
DELIMITER // -- 修改MySQL delimiter：'//'
DROP FUNCTION IF EXISTS `rand_string` //
SET NAMES utf8 //
CREATE FUNCTION `rand_string` (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 `add_e_b_order`

DELIMITER //
CREATE PROCEDURE `add_e_b_order`(IN n INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	DECLARE id INT DEFAULT 1;
	WHILE i < n DO
		SET id = i;
			INSERT INTO `e_b_order` VALUES (id, 1, 1, rand_string(30), rand_string(10), 1, 1, 1, 1, NOW(), 1, "110", "扩展信息", "10000", "0.0300", "10000", "http:192.168.0.0/notifyUrl", NOW(), "检测链接", "代理信息", "脚本信息", "url_ext", 1, rand_string(5), NOW(), "1000", "测试失败", 1, NOW(), 1, "a,b,c", "回调结果", NOW(), "http//192.168.0./notify", NOW(), NOW(), 1, "10000", "杭州", 1);
		SET i = i + 1;
	END WHILE;
END //
DELIMITER ; -- 改回默认的 MySQL delimiter：';'

CALL add_e_b_order(10000000);



-- ======================================================================================================================================================
-- e_c_order

DROP PROCEDURE IF EXISTS `add_e_c_order`

DELIMITER //
CREATE PROCEDURE `add_e_c_order`(IN n INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	DECLARE id INT DEFAULT 1;
	WHILE i < n DO
		SET id = i;
			INSERT INTO `e_c_order` VALUES (id, 1, 1, rand_string(30), rand_string(10), "奥特曼", NOW(), 1, 1, 1, "10000", "0.0300", "10000", NOW(), "127.0.0.1", "匹配的终端信息", 1, rand_string(5), NOW(), 1, "a,b,c", "回调的结果", NOW(), "http://192.68.0.0/notify", NOW(), NOW(), 1, NOW(), 1, "http://192.168.0.0/cashier/domain");
		SET i = i + 1;
	END WHILE;
END


CALL add_e_c_order(10000000);


-- ======================================================================================================================================================
-- e_match_order

DROP PROCEDURE IF EXISTS `add_e_match_order`

DELIMITER //
CREATE PROCEDURE `add_e_match_order`(IN n INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	DECLARE id INT DEFAULT 1;
	WHILE i < n DO
		SET id = i;
			INSERT INTO `e_match_order` VALUES (id, rand_string(30), NOW(), rand_string(30), "http://192.168.0.0/pay/url", 1, rand_string(30), NOW(), NOW(), 1);
		SET i = i + 1;
	END WHILE;
END


CALL add_e_match_order(10000000);

mysql创建function 报错误1418 - This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in

原因分析

我们创建函数时必须指定我们的函数是否是

DETERMINISTIC 不确定的
NO SQL 没有SQl语句，当然也不会修改数据
READS SQL DATA 只是读取数据，当然也不会修改数据
MODIFIES SQL DATA 要修改数据
CONTAINS SQL 包含了SQL语句
在mysql数据库中执行以下语句（临时生效，重启后失效）
set global log_bin_trust_function_creators=TRUE;
在配置文件/etc/my.cnf的[mysqld]配置log_bin_trust_function_creators=1

魔乐社区

魔乐社区（Modelers.cn) 是一个中立、公益的人工智能社区，提供人工智能工具、模型、数据的托管、展示与应用协同服务，为人工智能开发及爱好者搭建开放的学习交流平台。社区通过理事会方式运作，由全产业链共同建设、共同运营、共同享有，推动国产AI生态繁荣发展。

更多推荐

小参数・大码力・易部署 | Qwen3.6-27B上线魔乐社区，基于昇腾的部署教程来了

继一周前模型开源发布后，千问再度开源Qwen3.6-27B —— 一个拥有270亿参数的稠密多模态模型，也是社区呼声最高的模型规格。Qwen3.6-27B 依然支持多模态思考与非思考模式，在智能体编程方面达到了旗舰级表现，全面超越前代开源旗舰 Qwen3.5-397B-A17B（总参数397B / 激活参数17B的MoE模型）。作为稠密架构，它无需MoE路由即可部署，是开发者在实用、可广泛部署规模