The role of optimize table in MySQL

description of the OPTIMIZE in the manual:
OPTIMIZE [LOCAL | No_write_to_binlog] TABLE tbl_name [, Tbl_name] ...

If you have deleted a large part of the table, or if you have made many changes to a table with variable-length rows (a table with a varchar, blob, or text column), you should use the
OPTIMIZE TABLE. The deleted records are kept in the linked list, and subsequent insert operations re-use the old record location. You can use optimize table to re-
Use unused space and defragment data files.

In most settings, you do not need to run optimize TABLE at all. Even if you have a large number of updates to variable-length lines, you do not need to run them frequently, once a week or once a month
, you can run only on a specific table.

OPTIMIZE table works only on MyISAM, BDB, and InnoDB tables.

Note that MySQL locks the table during the Optimize table run.

Example description optimize table is important in optimizing MySQL

First, the original data
1, data volume
Mysql> Select COUNT (*) as total from ad_visit_history;
+---------+
| Total |
+---------+
| 1187096 | A total of more than 1.18 million data
+---------+
1 row in Set (0.04 sec)


2. The size of the table file stored on the hard disk

[[email protected] test1]# ls |grep visit |xargs-i du {}
382020 ad_visit_history. MYD//Data files accounted for 380M
127116 ad_visit_history. MYI//Index file accounted for 127M
AD_VISIT_HISTORY.FRM//Structural files accounted for 12K


3, check the index information


Mysql> Show index from ad_visit_history from Test1; Look at the index information for the table
+------------------+------------+-------------------+--------------+---------------+-----------+-------------+- ---------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------------+------------+-------------------+--------------+---------------+-----------+-------------+- ---------+--------+------+------------+---------+
| Ad_visit_history | 0 | PRIMARY | 1 | ID | A | 1187096 | NULL | NULL | | BTREE | |
| Ad_visit_history | 1 | Ad_code | 1 | Ad_code | A | 46 | NULL | NULL | YES | BTREE | |
| Ad_visit_history | 1 | unique_id | 1 | unique_id | A | 1187096 | NULL | NULL | YES | BTREE | |
| Ad_visit_history | 1 | Ad_code_ind | 1 | Ad_code | A | 46 | NULL | NULL | YES | BTREE | |
| Ad_visit_history | 1 | From_page_url_ind | 1 | From_page_url | A | 30438 | NULL | NULL | YES | BTREE | |
| Ad_visit_history | 1 | Ip_ind | 1 | IP | A | 593548 | NULL | NULL | YES | BTREE | |
| Ad_visit_history | 1 | Port_ind | 1 | Port | A | 65949 | NULL | NULL | YES | BTREE | |
| Ad_visit_history | 1 | Session_id_ind | 1 | session_id | A | 1187096 | NULL | NULL | YES | BTREE | |
+------------------+------------+-------------------+--------------+---------------+-----------+-------------+- ---------+--------+------+------------+---------+
8 rows in Set (0.28 sec)


The information description of the column in the index information.


Table: The names of the tables.
Non_unique: 0 If the index cannot include a repeating word. 1 if it is possible.
Key_name: The name of the index.
Seq_in_index: The column sequence number in the index, starting at 1.
COLUMN_NAME: Column name.
Collation: How the columns are stored in the index. In the Mysqlshow index syntax, there is a value of ' A ' (ascending) or null (no classification).
Cardinality: An estimate of the number of unique values in the index. You can update by running analyze table or myisamchk-a. The cardinality is counted according to the statistics stored as integers, so even for small tables, this value is not necessarily accurate. The larger the cardinality, the greater the chance that MySQL will use the index when it is federated.
Sub_part: The number of characters that are indexed if the column is only partially indexed. Null if the entire column is indexed.
Packed: Indicates how the keyword is compressed. Null if it is not compressed.
Null: Contains Yes if the column contains null. If not, it is empty.
Index_type: Storage Index data structure method (BTREE, Fulltext, HASH, RTREE)


Two, delete half of the data


Mysql> Delete from ad_visit_history where id>598000; Delete half of the data
Query OK, 589096 rows affected (4 min 28.06 sec)


[[email protected] www.linuxidc.com test1]# ls |grep visit |xargs-i du {}//the corresponding myd,myi file size does not change
382020 ad_visit_history. MYD
127116 ad_visit_history. MYI
Ad_visit_history.frm


As a general rule, if half the data is deleted in the database, the corresponding. MYD,. The myi file should also become half the previous. But after deleting half the data,. MYD. Myi altogether even 1KB is not reduced, this is how terrible ah.


We're having a look at the index information
Mysql> Show index from Ad_visit_history;
+------------------+------------+-------------------+--------------+---------------+-----------+-------------+- ---------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------------+------------+-------------------+--------------+---------------+-----------+-------------+- ---------+--------+------+------------+---------+
| Ad_visit_history | 0 | PRIMARY | 1 | ID | A | 598000 | NULL | NULL | | BTREE | |
| Ad_visit_history | 1 | Ad_code | 1 | Ad_code | A | 23 | NULL | NULL | YES | BTREE | |
| Ad_visit_history | 1 | unique_id | 1 | unique_id | A | 598000 | NULL | NULL | YES | BTREE | |
| Ad_visit_history | 1 | Ad_code_ind | 1 | Ad_code | A | 23 | NULL | NULL | YES | BTREE | |
| Ad_visit_history | 1 | From_page_url_ind | 1 | From_page_url | A | 15333 | NULL | NULL | YES | BTREE | |
| Ad_visit_history | 1 | Ip_ind | 1 | IP | A | 299000 | NULL | NULL | YES | BTREE | |
| Ad_visit_history | 1 | Port_ind | 1 | Port | A | 33222 | NULL | NULL | YES | BTREE | |
| Ad_visit_history | 1 | Session_id_ind | 1 | session_id | A | 598000 | NULL | NULL | YES | BTREE | |
+------------------+------------+-------------------+--------------+---------------+-----------+-------------+- ---------+--------+------+------------+---------+
8 rows in Set (0.00 sec)


In contrast, the index query and the last index query, the data information is basically the last one, this is still reasonable.

The role of optimize table in MySQL