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

Source: Internet
Author: User

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

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.