MySQL测试数据准备

一. 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)