"Go" use optimize, stored procedures, and system tables to bulk-defragment the MySQL database tables to free up tablespace

This article collects in my notebook, because cannot find the original source. Omitted in this, as if who knows can contact me plus.

The core is to use the MySQL system table and the "optimize table name " command, the MySQL data table for the space release. Because both the Delete and drop table do not release the tablespace (thetruncate command frees the tablespace "delete all data"), it needs to be freed with the optimize command.

The purpose of this stored procedure is to defragment all the tables of a library. A table can accumulate a lot of fragments as it is inserted very often, or is constantly updated. If you tidy up in time, the query efficiency will be much higher.

DELIMITER $$DROP PROCEDURE IF EXISTS' MySQL '. ' Sp_optimize_tables ' $$CREATE PROCEDURE' MySQL '. ' Sp_optimize_tables ' (inch db_name varchar(255))BEGIN  --Created by David Yeung 20080128.  --To optimize all the tables in exact database.  DeclareCntint default 0; DeclareIint default 0; Select Count(*) asTotal fromInformation_schema.tableswhereTable_schema= db_name  intoCNT;  whileI<CNT do--Get The table ' s exact name.    Set @stmt =Concat'Select table_name from information_schema.tables where Table_schema =" ",db_name," "ORDER BY table_name ASC limit'I', 1 into @tb_name'); PrepareS1 from @stmt; ExecuteS1; Drop PrepareS1; Set @stmt = "'; Set @stmt =Concat'Optimize table',db_name,'.',@tb_name); PrepareS1 from @stmt; ExecuteS1; Drop PrepareS1; Set @stmt = "'; SetI=I+ 1; End  while; --Refresh tables.flush Tables;END$ $DELIMITER;

Invocation Example:

mysql> use MySQL
Database changed
mysql> call Sp_optimize_tables (' david_test ');
+------------------------------+----------+----------+----------+
| table                         | op       | Msg_type | Msg_text |
+------------------------------+----------+----------+----------+
| david_test.test1 | optimize | status    | ok       |
+------------------------------+----------+----------+----------+
1 row in Set (0.26 sec)

+--------------------------+----------+----------+----------+
| table                     | op       | Msg_type | Msg_text |
+--------------------------+----------+----------+----------+
| david_test.test2| Optimize | status    | ok       |
+--------------------------+----------+----------+----------+
1 row in Set (0.35 sec)

+---------------------------------------+----------+----------+----------+
| table                                  | op       | Msg_type | Msg_text |
+---------------------------------------+----------+----------+----------+
| david_test.test3 | optimize | status   | ok       |
+---------------------------------------+----------+----------+----------+
1 row in Set (0.45 sec)

+--------------------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------------------+----------+----------+----------+
| david_test.test_article | Optimize | Status | OK |
+--------------------------+----------+----------+----------+
1 row in Set (4.13 sec)

...

+----------------------------------+----------+----------+----------+
| table                             | op       | Msg_type | Msg_text |
+----------------------------------+----------+----------+----------+
| david_test.test_article_content | Optimize | status   | ok       |
+----------------------------------+----------+----------+----------+
1 row in Set (37.81 sec)

+-----------------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-----------------------+----------+----------+----------+
| David_test.members | Optimize | Status | OK |
+-----------------------+----------+----------+----------+
1 row in Set (40.02 sec)

+--------------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------------+----------+----------+----------+
| David_test.test_site | Optimize | Status | OK |
+--------------------+----------+----------+----------+
1 row in Set (40.31 sec)

+--------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------+----------+----------+----------+
| david_test.t | Optimize | Status | OK |
+--------------+----------+----------+----------+
1 row in Set (41.10 sec)

Query OK, 0 rows affected (41.13 sec)

"Go" use optimize, stored procedures, and system tables to bulk-defragment the MySQL database tables to free up tablespace