Optimal use of optimize table in MySQL

Source: Internet
Author: User
Tags echo date

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.