Mysql - PHP inserts 100,000 pieces of content in batches, causing the memory to burst to 128MB. How to deal with it?

Question

Because there is a business design that generates records in batches and generates 100,000 pieces of content at one time (most of the content is the same, and some fields need to generate random codes), what needs to be done?

Answer 1

Those who talk about using asynchronous and looping, I personally think it is unreasonable.
With 100,000 pieces of data, even if your memory is large enough, it will take a lot of time to store the data in the database (IO flow), and even if the transaction submission is not processed well, the database will Will occupy memory.

The fastest way is to generate data directly in the database.
Generate an id table yourself (only one field of id is stored) and record 100,000 entries (0-10w)
Mysql method:

insert into table t
select i.id, concat('名字', i.id) name, 
    concat('随机生成码7-12:',FLOOR(7 + (RAND() * 6))) rand,
    ifnull(a.nickname, 'No nickname') nickname,
    uuid() descript, --随机字符串
    from_unixtime(unix_timestamp("20170101000000")+FLOOR((RAND()*60*60*24*365)))  --2017年中随机日期
from table_id i
left join table_account a on a.id=FLOOR((RAND()*12)) --如果数据来源另外一些表
where i.id < 1000  --如果只要生成1000条

Answer 2

I really don’t want to process it in batches, ini_set(''memory_limit', '256M');? Or if your server memory is not tight, set it to 512M

Answer 3

1. If the generated data is stored in a chain structure, it is recommended to optimize the data structure. If most of the data are the same, you can use key values ​​to save the same data, and then use sub-key values ​​to save different data (i.e. seek common ground while reserving differences)
Such as
array (0=>array('data'=>array('name'=>'t','age'=>'14),'key'=>array('1','2' ,'3','4')));
foreach($array as $val) {

foreach($val['key']=>$kval) {
    var_dump($kval);
    var_dump($val);
}

}

2. Saving 10 pieces of data at one time will put more pressure on the MySQL server. It is recommended to optimize the insert code, such as
insert into table values('t','a',1),('t','a',2)

3. It is recommended to open transactions and submit data quantitatively