跳到主要内容

范例-生成数据库表大文件脚本

范例-生成数据库表大文件脚本-testlog.sql​

代码​

testlog.sql

create table testlog (id int auto_increment primary key,name char(10),age int default 20);

delimiter $$

create procedure  pro_testlog() 
begin  
declare i int;
set i = 1; 
while i < 100000 
do  insert into testlog(name,age) values (concat('wang',i),i); 
set i = i +1; 
end while; 
end$$

delimiter ;

使用​

如下是一个生成大文件表的sql脚本：

testlog.sql

create table testlog (id int auto_increment primary key,name char(10),age int default 20);

delimiter $$

create procedure  pro_testlog() 
begin  
declare i int;
set i = 1; 
while i < 100000 
do  insert into testlog(name,age) values (concat('wang',i),i); 
set i = i +1; 
end while; 
end$$

delimiter ;

将这个代码拷贝到自己数据库里：

[root@linux-test ~]#mysql -uroot -pxyy520 test

mysql> create table testlog (id int auto_increment primary key,name char(10),age int default 20);
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> delimiter $$
mysql> 
mysql> create procedure  pro_testlog() 
    -> begin  
    -> declare i int;
    -> set i = 1; 
    -> while i < 100000 
    -> do  insert into testlog(name,age) values (concat('wang',i),i); 
    -> set i = i +1; 
    -> end while; 
    -> end$$
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> delimiter ;
mysql>

查看当前表信息：

mysql> select *from testlog;
Empty set (0.00 sec)

[root@linux-test ~]#ll -h /data/mysql/test
total 1.1M
……
-rw-rw---- 1 mysql mysql 8.5K Apr 28 07:19 testlog.frm
-rw-rw---- 1 mysql mysql  96K Apr 28 07:19 testlog.ibd #这个文件是96K

触发脚本：

mysql> call pro_testlog;
Query OK, 1 row affected (16.77 sec)

[root@linux-test ~]#ll -h /data/mysql/test/testlog.ibd
-rw-rw---- 1 mysql mysql 11M Apr 28 07:21 /data/mysql/test/testlog.ibd
[root@linux-test ~]#ll -h /data/mysql/test/testlog.ibd
-rw-rw---- 1 mysql mysql 12M Apr 28 07:21 /data/mysql/test/testlog.ibd
#可以看到这个testlog.ibd文件已经增大到12M了。

此时直接使用delete from testlog; 命令删除表后，那么这个/data/mysql/test/testlog.ibd文件大小是否会变小呢？

mysql> delete from testlog;
Query OK, 99999 rows affected (0.20 sec)

mysql> select *from testlog;
Empty set (0.00 sec)

mysql> 


[root@linux-test ~]#ll -h /data/mysql/test/testlog.ibd
-rw-rw---- 1 mysql mysql 12M Apr 28 07:24 /data/mysql/test/testlog.ibd
#可以发现这个文件大小依然没有变化。

那么我们可以利用如下命令来清理这种空洞文件：

mysql> OPTIMIZE TABLE testlog;
+--------------+----------+----------+-------------------------------------------------------------------+
| Table        | Op       | Msg_type | Msg_text                                                          |
+--------------+----------+----------+-------------------------------------------------------------------+
| test.testlog | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.testlog | optimize | status   | OK                                                                |
+--------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.01 sec)

##再次验证(符合预期)
[root@linux-test ~]#ll -h /data/mysql/test/testlog.ibd
-rw-rw---- 1 mysql mysql 96K Apr 28 07:25 /data/mysql/test/testlog.ibd

当然，truncate命令直接是可以会自动缩减数据文件的大小的。

| 199997 | wang99998 | 99998 |
| 199998 | wang99999 | 99999 |
+--------+-----------+-------+
99999 rows in set (0.04 sec)

mysql> truncate table testlog;
Query OK, 0 rows affected (0.01 sec)

[root@linux-test ~]#ll -h /data/mysql/test/testlog.ibd
-rw-rw---- 1 mysql mysql 96K Apr 28 07:26 /data/mysql/test/testlog.ibd

位置​

链接：https://pan.baidu.com/s/1-c4nXQki7jMJYl2sCrer7Q?pwd=zt1p 提取码：zt1p testlog.sql

image-20240428074239032

FAQ​

  • 直接调用
MariaDB [hellodb]> call pro_testlog;
Query OK, 99999 rows affected (12.308 sec) #耗费12s


[root@mysql hellodb]# ll -h
-rw-rw----. 1 mysql mysql  999 Jun 13 06:52 testlog.frm
-rw-rw----. 1 mysql mysql  12M Jun 13 07:07 testlog.ibd
  • 启用事务后，执行会更快些
MariaDB [hellodb]> begin;
Query OK, 0 rows affected (0.000 sec)

MariaDB [hellodb]> call pro_testlog;
Query OK, 99999 rows affected (1.086 sec) #只要1s

MariaDB [hellodb]> commit;
Query OK, 0 rows affected (0.001 sec)


[root@mysql hellodb]# ll -h testlog*
-rw-rw----. 1 mysql mysql 999 Jun 13 07:27 testlog.frm
-rw-rw----. 1 mysql mysql 12M Jun 13 07:28 testlog.ibd
0%