-- 建表
DROP TABLE IF EXISTS person;
CREATE TABLE person (
PID int(11) AUTO_INCREMENT COMMENT '编号',
PNAME varchar(50) COMMENT '姓名',
PSEX varchar(10) COMMENT '性别',
PAGE int(11) COMMENT '年龄',
SAL decimal(7, 2) COMMENT '工资',
PRIMARY KEY (PID)
);
-- 创建存储过程
create procedure insert_person(in max_num int(10))
begin
declare i int default 0;
set autocommit = 0;
repeat
set i = i +1;
insert into person (PID,PNAME,PSEX,PAGE,SAL) values (i,concat('test',floor(rand()*10000000)),IF(RAND()>0.5,'男','女'),FLOOR((RAND()*100)+10),FLOOR((RAND()*19000)+1000));
until i = max_num
end repeat;
commit;
end;
-- 调用存储过程
call insert_person(30000000);
-- 不使用索引,根据pName进行查询
select * from person where PNAME = "test1209325"; #10.813S
-- 给PNAME建立索引
alter table person add index idx_pname(PNAME);
select * from person where PNAME = "test1209325"; # 0.032S
select * from person where PID = 2800000; #0.021