创建表

create table banbies(
id int primary key auto_increment,
name varchar(20)
);
create table users(id bigint(20),name varchar(16),password varchar(41),sex int(1),banbie_id int(4));

创建存储过程

DELIMITER $$$ ;
Create Procedure createusers()
begin
declare i bigint default 0;
declare a int;
declare b int;
set i=0;
start transaction;
while i<10000000 do
set a=FLOOR(1+RAND() *9 )%2;
set b=FLOOR(1+RAND() *1000 );
insert into users (id,name,password,sex,banbie_id) values(i,concat('user_',i),password(i),a,b);
set i=i+1;
end while;
commit;
end$$$
DELIMITER ;

执行存储过程 createusers()

set innodb_flush_log_at_trx_commit=2; #优化插入数据时性能
call createusers();

创建索引

ALTER TABLE users ADD PRIMARY KEY(id);
ALTER TABLE users ADD INDEX banbieid (banbie_id);

查看索引

show index from users;