一. 7亿数据的测试表
MySQL 测试数据准备:
-- 建表语句
CREATE TABLE fact_sale (
id bigint(8) NOT NULL AUTO_INCREMENT,
sale_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
prod_name varchar(50) DEFAULT NULL,
sale_nums int(11) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 批量加载数据的存储过程
-- 可以考虑先关闭binlog
delimiter //
CREATE PROCEDURE p_ins_fact_sal()
begin
declare l_sal_date timestamp;
declare l_prod_name varchar(50);
declare l_sal_num int;
declare l_n int default 1;
while l_n < power(10,9) DO
set l_sal_date = adddate('2010-01-01',CEILING(RAND()*900+100));
set l_prod_name = concat('PROD',CEILING(RAND()*9+1));
set l_sal_num = ceiling(rand()*9)+ceiling(rand()*90);
insert into fact_sale(sale_date,prod_name,sale_nums) values (l_sal_date,l_prod_name,l_sal_num);
if mod(l_n,1000) = 0 then
commit;
end if;
set l_n = l_n + 1;
end while;
end;
//
delimiter ;
-- 运行存储过程
-- 我的运行到一半停了,就7亿多数据进行测试
set autocommit = 0;
call p_ins_fact_sal;
mysql> select count(*) from fact_sale;
+-----------+
| count(*) |
+-----------+
| 767830000 |
+-----------+
1 row in set (2 min 32.75 sec)
mysql> desc fact_sale;
+-----------+-------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+-------------------+-----------------------------+
| id | bigint(8) | NO | PRI | NULL | auto_increment |
| sale_date | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| prod_name | varchar(50) | YES | | NULL | |
| sale_nums | int(11) | YES | | NULL | |
+-----------+-------------+------+-----+-------------------+-----------------------------+
4 rows in set (0.01 sec)