MySQL数据insert测试

原创

胖五斤

修改于 2022-12-25 20:49:54

1.4K0

修改于 2022-12-25 20:49:54

文章被收录于专栏：一涨知识胖五斤

测试环境

本地Mac安装的MySQL（8.0.30）服务，性能数据仅作为参考，但对于不同索引情况下的结果，还是能看出有区别。

测试方式

通过存储过程来进行数据insert：

delimiter ;;

create procedure idata()

begin

  declare i int;

  set i=1;

  while(i<=500000)do

    insert into t values(i, i, i);

    set i=i+1;

  end while;

end;;

delimiter ;



call idata();

测试情况

1、没主键、没索引

drop table if exists t;

create table t (a int, b int, c int);



mysql> call idata();

Query OK, 1 row affected (2 min 58.78 sec)



mysql> select count(\*) from t;

+----------+

| count(\*) |

+----------+

|   500000 |

+----------+

1 row in set (0.02 sec)



mysql> create index ta on t(a);

Query OK, 0 rows affected (1.07 sec)

Records: 0  Duplicates: 0  Warnings: 0

2、没主键、有普通索引

drop table if exists t;

create table t (a int, b int, c int);

create index ta on t(a);



mysql> call idata();

Query OK, 1 row affected (3 min 31.30 sec)

3、没主键、有唯一索引

drop table if exists t;

create table t (a int, b int, c int);

create unique index ta on t(a);



mysql> call idata();

Query OK, 1 row affected (3 min 49.46 sec)

4、有主键、没索引

drop table if exists t;

create table t (a int, b int, c int, primary key(a));



mysql> call idata();

Query OK, 1 row affected (3 min 56.93 sec)

5、有主键、有普通索引

drop table if exists t;

create table t (a int, b int, c int, primary key(a));

create index tb on t(b);



mysql> call idata();

Query OK, 1 row affected (4 min 14.70 sec)

6、有主键，有唯一索引

drop table if exists t;

create table t (a int, b int, c int, primary key(a));

create unique index tb on t(b);



mysql> call idata();

Query OK, 1 row affected (4 min 30.83 sec)

7、日志刷盘也有影响

set global sync\_binlog=0;

set global innodb\_flush\_log\_at\_trx\_commit=0;

设置后提升明显。

有主键，有唯一索引情况下：

mysql> call idata();

Query OK, 1 row affected (43.26 sec)

8、手动commit

set global sync\_binlog=1;

set global innodb\_flush\_log\_at\_trx\_commit=1;

set autocommit = 0;后再手动触发commit；

mysql> call idata();

Query OK, 1 row affected (20.82 sec)



mysql> commit;

Query OK, 0 rows affected (0.28 sec)

drop table if exists t;

create table t (a int, b int, c int, primary key(a));

create unique index tb on t(b);

set global sync\_binlog=0;

set global innodb\_flush\_log\_at\_trx\_commit=0;

set autocommit = 0;



mysql> call idata();

Query OK, 1 row affected (21.78 sec)



mysql> commit;

Query OK, 0 rows affected (0.31 sec)

9、alter table t disable keys;

mysql> call idata();

Query OK, 1 row affected (4 min 8.54 sec)

测试小结

场景	idata耗时
没主键、没索引	2 min 58.78 sec；idata后创建索引耗时1.07 sec
没主键、有普通索引	3 min 31.30 sec
没主键、有唯一索引	3 min 49.46 sec
有主键、没索引	3 min 56.93 sec
有主键、有普通索引	4 min 14.70 sec
有主键，有唯一索引	4 min 30.83 sec
日志刷盘：不要同步刷	43.26 sec
手动commit	20.82 sec
disable keys	4 min 8.54 sec