OPTIMIZE TABLE is used to reclaim unused database space.
When rows of data on a table are deleted, the disk space occupied is not immediately reclaimed, the space is reclaimed after the Optimize table command is used, and the rows of data on the disk are queued (note: disk, not database).
Most of the time, you do not need to run optimize table, you can only run it for those specific tables after you have deleted the data rows in bulk or periodically (once a week or once a month) to perform a data table optimization operation.
Take a look at the Optimize table for InnoDB and MyISAM related knowledge
1. InnoDB and MyISAM
Currently, the engine that supports the optimize command is MyISAM, InnoDB, and ARCHIVE, and for InnoDB, the optimize command is mapped to alter TABLE command, which rebuilds the datasheet, updates the index statistics, and reclaims space in the primary key index.
2. InnoDB and MyISAM
If your MySQL has a repository, if you only want to execute on the main library, then you can add the keyword no_write_to_binlog (or local, meaning exactly the same).
OPTIMIZE [No_write_to_binlog | Local] TABLE
This is especially important for a MySQL database with a mm structure, because most of the time you just want to do it on the standby, rather than having to influence the main library.
Use in MySQL command mode
The code is as follows |
Copy Code |
#ls-lah users_0.ibd-rwxr-xr-x 1 MySQL dba 736M May 6 09:50 users_0.ibd root@test 10:10:53>optimize table Users_0 |
Test instance
1. Take a look at the table index after you delete the insert operation multiple times
The code is as follows |
Copy Code |
Mysql> show INDEX from ' Tbl_name '; +----------+------------+------------+--------------+-------------+-----------+-------------+----------+------- -+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +----------+------------+------------+--------------+-------------+-----------+-------------+----------+------- -+------+------------+---------+ | tbl_name | 0 | primary | 1 | stepid | a | 1 | NULL | null | | btree | | | Tbl_name | 1 | Flowid | 1 | Flowid | A | 1 | NULL | NULL | | Btree | | | Tbl_name | 1 | Wagercount | 1 | Wagercount | A | 1 | NULL | NULL | | Btree | | | Tbl_name | 1 | Wagerid_3 | 1 | Wagerid | A | 1 | NULL | NULL | | Btree | | | Tbl_name | 1 | Wagerid_3 | 2 | StepType | A | 1 | NULL | NULL | | Btree | | | Tbl_name | 1 | Wagerid_3 | 3 | Paramresult | A | 1 | 255 | NULL | | Btree | | | Tbl_name | 1 | steptype_2 | 1 | StepType | A | 1 | NULL | NULL | | Btree | | | Tbl_name | 1 | steptype_2 | 2 | Paramresult | A | 1 | 255 | NULL | | Btree | | | Tbl_name | 1 | Wagerid_2 | 1 | Wagerid | A | 1 | NULL | NULL | | Btree | | | Tbl_name | 1 | Wagerid_2 | 2 | StepType | A | 1 | NULL | NULL | | Btree | | | Tbl_name | 1 | Wagerid_2 | 3 | Paramresult | A | 1 | 255 | NULL | | Btree | | +----------+------------+------------+--------------+-------------+-----------+-------------+----------+------- -+------+------------+---------+ Rows in Set (0.01 sec) |
2. Optimization table
The code is as follows |
Copy Code |
mysql> optimize table tbl_name; +---------------+----------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------------+----------+----------+----------+ | Test.tbl_name | Optimize | Status | OK | +---------------+----------+----------+----------+ 1 row in Set (40.60 sec) |
3, again to see the effect of optimization
The code is as follows |
Copy Code |
Mysql> show INDEX from ' Tbl_name '; +----------+------------+------------+--------------+-------------+-----------+-------------+----------+------- -+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +----------+------------+------------+--------------+-------------+-----------+-------------+----------+------- -+------+------------+---------+ | Tbl_name | 0 | PRIMARY | 1 | StepID | A | 172462 | NULL | NULL | | Btree | | | Tbl_name | 1 | Flowid | 1 | Flowid | A | 86231 | NULL | NULL | | Btree | | | Tbl_name | 1 | Wagercount | 1 | Wagercount | A | 4311 | NULL | NULL | | Btree | | | Tbl_name | 1 | Wagerid_3 | 1 | Wagerid | A | 86231 | NULL | NULL | | Btree | | | Tbl_name | 1 | Wagerid_3 | 2 | StepType | A | 172462 | NULL | NULL | | Btree | | | Tbl_name | 1 | Wagerid_3 | 3 | Paramresult | A | 172462 | 255 | NULL | | Btree | | | Tbl_name | 1 | steptype_2 | 1 | StepType | A | 9 | NULL | NULL | | Btree | | | Tbl_name | 1 | steptype_2 | 2 | Paramresult | A | 86231 | 255 | NULL | | Btree | | | Tbl_name | 1 | Wagerid_2 | 1 | Wagerid | A | 86231 | NULL | NULL | | Btree | | | Tbl_name | 1 | Wagerid_2 | 2 | StepType | A | 172462 | NULL | NULL | | Btree | | | Tbl_name | 1 | Wagerid_2 | 3 | Paramresult | A | 172462 | 255 | NULL | | Btree | | +----------+------------+------------+--------------+-------------+-----------+-------------+--- |
If you are using in PHP
PHP Program:
The code is as follows |
Copy Code |
Header ("content-type:text/html; Charset=utf-8 "); Set_time_limit (0); echo Date (' y-m-d h:i:s '). ' Begin<br/> '; /* * Use optimize table to optimize tablespace * Reclaim space, reduce fragmentation */ mysql_connect (' localhost ', ' root ', ' 123456 ') or Die (' Database connection failed '. Mysql_error ()); mysql_query (' SET NAME UTF8 '); $database = ' db3 '; mysql_select_db ($database); $res = mysql_query (' Show TABLES from '. $database); while ($row = Mysql_fetch_row ($res)) { $table = $row [0]; $sql = sprintf (' OPTIMIZE TABLE%s.%s ', $database, $table); if (mysql_query ($sql)) { echo ' optimize table '. $table. ' Complete. <br> '; } Else { echo ' Tuning table '. $table. ' Failed. '. Mysql_error (); Exit } } echo Date (' y-m-d h:i:s '). ' End<br/> '; |
after reading the article understand
Function: Reclaim space, reduce fragmentation
Method: OPTIMIZE TABLE tablename
Scenario: Disk depletion, InnoDB tablespaces run out. First use the Optimize TABLE command to optimize, and then consider the expansion.
Note: The OPTIMIZE command supports the engine MyISAM, InnoDB, archve
For InnoDB, it rebuilds the data table, updates the index statistics, and reclaims the primary key index space