mysql recreate_does the optimize table of mysql really rebuild(recreate) table？

最新推荐文章于 2022-03-03 22:51:03 发布

果呀哎呀妈呀哦呀

最新推荐文章于 2022-03-03 22:51:03 发布

阅读量225

点赞数

文章标签： mysql recreate

本文链接：https://blog.csdn.net/weixin_30724201/article/details/113324273

版权

Demo:

I created a table on MySQL 5.6.37 and filled it with about 1GB of random data.

mysql> select version();

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

| version() |

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

| 5.6.37-82.2-log |

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

mysql> show table statusG

*************************** 1. row ***************************

Name: mytable

Engine: InnoDB

Version: 10

Row_format: Compact

Rows: 61914

Avg_row_length: 20926

Data_length: 1295613952

Max_data_length: 0

Index_length: 1589248

Data_free: 4194304

Auto_increment: 131056

Create_time: 2019-02-20 15:54:23

Update_time: NULL

Check_time: NULL

Collation: utf8mb4_general_ci

Checksum: NULL

Create_options:

Comment:

I can observe the file in my datadir and note the inode number (the leftmost number).

/usr/local/var/mysql/test ls -li

total 2590752

8625784850 -rw-rw---- 1 bkarwin admin 67 Feb 20 07:54 db.opt

8625784853 -rw-rw---- 1 bkarwin admin 8580 Feb 20 07:54 mytable.frm

8625784854 -rw-rw---- 1 bkarwin admin 1321205760 Feb 20 07:56 mytable.ibd

I run optimize table.

mysql> optimize table mytable;

While it's running, I check progress by listing files in the datadir again. I see it has created a new temp file and it's filling it with a copy of the data.

Note the inode number of the temp file.

/usr/local/var/mysql/test ls -li

total 4395064

8625785082 -rw-rw---- 1 bkarwin admin 8580 Feb 20 07:57 #sql-337_16.frm

8625785083 -rw-rw---- 1 bkarwin admin 914358272 Feb 20 07:57 #sql-ib2021-3689447301.ibd

8625784850 -rw-rw---- 1 bkarwin admin 67 Feb 20 07:54 db.opt

8625784853 -rw-rw---- 1 bkarwin admin 8580 Feb 20 07:54 mytable.frm

8625784854 -rw-rw---- 1 bkarwin admin 1321205760 Feb 20 07:56 mytable.ibd

Finally my optimize table finishes. It took almost 2 minutes. If it wasn't doing a real table copy, why would it have taken so long?

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

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

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

2 rows in set (1 min 5.52 sec)

Looking at the datadir again, I see the temp file has been renamed to be the new tablespace, and the old file has been removed. The new file has the inode number of the temp file, indicating it's the copy.

/usr/local/var/mysql/test ls -li

total 2557984

8625784850 -rw-rw---- 1 bkarwin admin 67 Feb 20 07:54 db.opt

8625785082 -rw-rw---- 1 bkarwin admin 8580 Feb 20 07:57 mytable.frm

8625785083 -rw-rw---- 1 bkarwin admin 1304428544 Feb 20 07:58 mytable.ibd