Optimal use of optimize table in MySQL

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