、创建表:
CREATE TABLE IF NOT EXISTS `blog` (
`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`title` char(120) NOT NULL,
`content` text NOT NULL,
`date` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;
2、创建存储过程
delimiter $$
create procedure batch_insert()
begin
DECLARE max int;
DECLARE rc int;
set max =10000000;
set rc =1;
loop: while rc<max do
insert into blog (title,content,date) values(CONCAT('title hre',rc),concat('content here',rc),now());
set rc=rc+1;
end while loop;
end$$
delimiter ;
3、调用存储过程:
CREATE TABLE IF NOT EXISTS `blog` (
`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`title` char(120) NOT NULL,
`content` text NOT NULL,
`date` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;
2、创建存储过程
delimiter $$
create procedure batch_insert()
begin
DECLARE max int;
DECLARE rc int;
set max =10000000;
set rc =1;
loop: while rc<max do
insert into blog (title,content,date) values(CONCAT('title hre',rc),concat('content here',rc),now());
set rc=rc+1;
end while loop;
end$$
delimiter ;
3、调用存储过程:
call batch_insert();
一张表数据超过千万级别,mysql查询性能严重下降!
set @@profiling=1;
select * from blog where title='title hre27' ;
show profiles;
+----------+------------+----------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+----------------------------------------------+
| 1 | 5.75001775 | select * from blog where title='title hre27' |
|
+----------+------------+----------------------------------------------+