mysql存储过程批量插入数据，构造 15w,150w,600w表

#构造15万(customer表)，150万(orders表)，600万(LINEITEM表)数据差异较大的三张表，
#只有数据量最大的表包含主键索引，对上述表进行三表联合查询，对比之前版本查询所耗时间	


#####################################################################
##初始化数据库表
#####################################################################

DROP DATABASE IF EXISTS test_01;

CREATE DATABASE test_01;

USE test_01;

DROP TABLE IF EXISTS customer, orders, lineitem;

CREATE TABLE CUSTOMER (
	C_CUSTKEY INTEGER NOT NULL,
	C_NAME VARCHAR(25) NOT NULL
) ENGINE = greatdb
PARTITION BY HASH (C_CUSTKEY) PARTITIONS 4;

INSERT INTO CUSTOMER
VALUES (1, 'Customer#000000001');

CREATE TABLE ORDERS (
	O_ORDERKEY INTEGER NOT NULL,
	O_CUSTKEY INTEGER NOT NULL,
	O_ORDERDATE DATE NOT NULL
) ENGINE = greatdb
PARTITION BY HASH (O_ORDERKEY) PARTITIONS 4;

INSERT INTO ORDERS
VALUES (1, 36901, '1996-01-02');

CREATE TABLE LINEITEM (
	L_ORDERKEY INTEGER NOT NULL,
	L_LINENUMBER INTEGER NOT NULL,
	L_EXTENDEDPRICE DECIMAL(15, 2) NOT NULL,
	L_DISCOUNT DECIMAL(15, 2) NOT NULL,
	L_RETURNFLAG CHAR(1) NOT NULL
) ENGINE = greatdb
PARTITION BY HASH (L_ORDERKEY) PARTITIONS 4;

INSERT INTO LINEITEM
VALUES (1, 1, 21168.23, 0.04, 'N');

ALTER TABLE CUSTOMER
	ADD PRIMARY KEY (C_CUSTKEY);

ALTER TABLE ORDERS
	ADD PRIMARY KEY (O_ORDERKEY);

ALTER TABLE LINEITEM
	ADD PRIMARY KEY (L_ORDERKEY, L_LINENUMBER);



#####################################################################
##插入数据
#####################################################################

delimiter $$

CREATE PROCEDURE insert_customer(IN tablename varchar(200),IN counts int)
begin
DECLARE i INT DEFAULT 1;
DECLARE v_C_CUSTKEY int;
SELECT MAX(C_CUSTKEY)+1 into v_C_CUSTKEY from customer;
SET @valuesSql = " ";
SET @tablename=tablename;
WHILE i< counts DO
SET @C_NAME=concat('Customer#00000000',floor(rand() * 100000000));
SET @sqlString = CONCAT("(",v_C_CUSTKEY,",'",@C_NAME,"')");
IF(i < counts-1) THEN
SET @valuesSql = CONCAT(@valuesSql,@sqlString,",");
ELSE
SET @valuesSql = CONCAT(@valuesSql,@sqlString);
END IF;
SET i = i+1;
SET v_C_CUSTKEY=v_C_CUSTKEY+1;
END WHILE;
SET @exesql = CONCAT("insert into ",@tablename," (C_CUSTKEY,C_NAME) VALUES ",@valuesSql);
PREPARE stmt FROM @exesql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
end$$

CREATE PROCEDURE insert_orders(IN tablename varchar(200),IN counts int)
begin
DECLARE i INT DEFAULT 1;
DECLARE v_O_ORDERKEY int;
SELECT MAX(O_ORDERKEY)+1 into v_O_ORDERKEY from orders;
SET @valuesSql = " ";
SET @tablename=tablename;
WHILE i< counts DO
SET @O_CUSTKEY=floor(rand()*9000000 + 10000);
SET @O_ORDERDATE=date(from_unixtime(unix_timestamp('1900-01-01') + floor(rand() * (unix_timestamp('2022-12-31') - unix_timestamp('2017-01-01') + 1))));
SET @sqlString = CONCAT("(",v_O_ORDERKEY,",",@O_CUSTKEY,",'",@O_ORDERDATE,"')");
IF(i < counts-1) THEN
SET @valuesSql = CONCAT(@valuesSql,@sqlString,",");
ELSE
SET @valuesSql = CONCAT(@valuesSql,@sqlString);
END IF;
SET i = i+1;
SET v_O_ORDERKEY=v_O_ORDERKEY+1;
END WHILE;
SET @exesql = CONCAT("insert into ",@tablename," (O_ORDERKEY,O_CUSTKEY,O_ORDERDATE) VALUES ",@valuesSql);
PREPARE stmt FROM @exesql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
end$$



CREATE PROCEDURE insert_lineitem(IN tablename varchar(200),IN counts int)
begin
DECLARE i INT DEFAULT 1;
DECLARE v_L_ORDERKEY int;
SELECT MAX(L_ORDERKEY)+1 into v_L_ORDERKEY from lineitem;
SET @valuesSql = " ";
SET @tablename=tablename;
WHILE i< counts DO
SET @L_LINENUMBER=FLOOR(RAND()*7000000);
SET @L_EXTENDEDPRICE=round(RAND()*100000,2);
SET @L_DISCOUNT=round(RAND(),2);
SET @L_RETURNFLAG=substring('ABCDEFGHIJKLMNOPQRSTUVWSYZ',floor(RAND()*26)+1,1);
SET @sqlString = CONCAT("(",v_L_ORDERKEY,",",@L_LINENUMBER,",",@L_EXTENDEDPRICE,",",@L_DISCOUNT,",'",@L_RETURNFLAG,"')");
IF(i < counts-1) THEN
SET @valuesSql = CONCAT(@valuesSql,@sqlString,",");
ELSE
SET @valuesSql = CONCAT(@valuesSql,@sqlString);
END IF;
SET i = i+1;
SET v_L_ORDERKEY=v_L_ORDERKEY+1;
END WHILE;
SET @exesql = CONCAT("insert into ",@tablename," (L_ORDERKEY,L_LINENUMBER,L_EXTENDEDPRICE,L_DISCOUNT,L_RETURNFLAG) VALUES ",@valuesSql);
PREPARE stmt FROM @exesql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
end$$

create procedure  loop_insert(IN tablename varchar(200),IN counts int)
begin
DECLARE i INT DEFAULT 1;
IF  (tablename ='customer') THEN
WHILE i <= counts DO
call insert_customer('customer',1001);
SET i = i + 1;
END WHILE;
ELSEIF (tablename ='orders') THEN
WHILE i <= counts DO
call insert_orders('orders',1001);
SET i = i + 1;
END WHILE;
ELSEIF (tablename='lineitem') THEN
WHILE i <= counts DO
call insert_lineitem('lineitem',1001);
SET i = i + 1;
END WHILE;
END IF;
END$$

delimiter ;

#15万(customer表)
call loop_insert('customer',150);

#150万(orders表)
call loop_insert('orders',1500);

#600万(LINEITEM表)
call loop_insert('lineitem',6000);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173