insert into person(id, person_id, person_name, gmt_create, gmt_modified) select @i:=@i+1, left(rand()*10,10) as person_id, concat('user_',@i%2048), date_add(gmt_create,interval + @i*cast(rand()*100 as signed) SECOND), date_add(date_add(gmt_modified,interval +@i*cast(rand()*100 as signed) SECOND), interval + cast(rand()*1000000 as signed) SECOND) from person;
此处需要注意的是，也许你在执行到近 800w 或者 1000w 数据的时候，会报错：The total number of locks exceeds the lock table size，这是由于你的临时表内存设置的不够大，只需要扩大一下设置参数即可。
SET GLOBAL tmp_table_size =512*1024*1024; #（512M） SET global innodb_buffer_pool_size= 1*1024*1024*1024; # (1G);
验证
select count(1) from person; select count(1) from person where person_id =6; show profiles;
优化测试
MySQL函数
ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)
-- 随机产生字符串 drop function if exists rand_string; -- 先判断是否已存在同名函数，如果已存在则先删除 DELIMITER $$ -- 两个 $$ 表示结束 create function rand_string(n int) returns varchar(255) begin declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'; declare return_str varchar(255) default ''; declare i int default 0; while i set return_str = concat(return_str, substring(chars_str, floor(1+rand()*52), 1)); set i=i+1; end while; return return_str; end $$ DELIMITER ;
-- 随机生成编号 drop function if exists rand_num; DELIMITER $$ create function rand_num() returns int(5) begin declare i int default 0; set i=floor(100+rand()*10); return i; end $$ DELIMITER ;
自定义函数的调用和其他普通函数的调用一样，示例如下：
select rand_string(5); select rand_num();
一键测试
drop database if exists tmp; create database tmp; use tmp; CREATE TABLE person( id int NOT NULL AUTO_INCREMENT PRIMARY KEY comment '主键', person_id tinyint not null comment '用户id', person_name VARCHAR(200) comment '用户名称', gmt_create datetime comment '创建时间', gmt_modified datetime comment '修改时间' ) comment 'user info';
SET @@profiling = 0; SET @@profiling_history_size = 0; SET @@profiling_history_size = 100; SET @@profiling = 1; insert into person values(1,1,'user_1', NOW(), now()); show profiles;
set @i=1; drop function if exists test_performance; DELIMITER $$ #设置结束符 create function test_performance(num int) returns varchar(255) begin declare return_str varchar(255) default ''; if(num > 0) then insert into person(id, person_id, person_name, gmt_create, gmt_modified) select @i:=@i+1, left(rand()*10,10) as person_id, concat('user_',@i%2048), date_add(gmt_create,interval + @i*cast(rand()*100 as signed) SECOND), date_add(date_add(gmt_modified,interval +@i*cast(rand()*100 as signed) SECOND), interval + cast(rand()*1000000 as signed) SECOND) from person limit num; else insert into person(id, person_id, person_name, gmt_create, gmt_modified) select @i:=@i+1, left(rand()*10,10) as person_id, concat('user_',@i%2048), date_add(gmt_create,interval + @i*cast(rand()*100 as signed) SECOND), date_add(date_add(gmt_modified,interval +@i*cast(rand()*100 as signed) SECOND), interval + cast(rand()*1000000 as signed) SECOND) from person; end if;
select count(1) into return_str from person where person_id = "9"; select count(1) into return_str from person;
在页的 7 个组成部分中，我们自己存储的记录会按照我们指定的行格式存储到 User Records 部分。
但是在一开始生成页的时候，其实并没有 User Records 这个部分，每当我们插入一条记录，都会从 Free Space 部分，也就是尚未使用的存储空间中申请一个记录大小的空间划分到 User Records 部分，当 Free Space 部分的空间全部被 User Records 部分替代掉之后，也就意味着这个页使用完了，如果还有新的记录插入的话，就需要去申请新的页了。这个过程的图示如下。
