The function and use of optimize table in MySQL

Take a look at the description of 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.

Raw 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] www.linuxidc.com 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.

Three, use optimize table to optimize

?? mysql> optimize table ad_visit_history; Optimizations after deleting data
+------------------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+------------------------+----------+----------+----------+
| Test1.ad_visit_history | Optimize | Status | OK |
+------------------------+----------+----------+----------+
1 row in Set (1 min 21.05 sec)

1, take a look. MYD,. Size of the Myi file

?? [Email protected] www.linuxidc.com test1]# ls |grep visit |xargs-i du {}
182080 ad_visit_history. MYD//Data files are almost half the size of the optimization
66024 ad_visit_history. MYI//index files are the same, almost half before optimization
Ad_visit_history.frm

2, check 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 | 42 | 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 | 42 | NULL | NULL | YES | BTREE | |
| Ad_visit_history | 1 | From_page_url_ind | 1 | From_page_url | A | 24916 | NULL | NULL | YES | BTREE | |
| Ad_visit_history | 1 | Ip_ind | 1 | IP | A | 598000 | NULL | NULL | YES | BTREE | |
| Ad_visit_history | 1 | Port_ind | 1 | Port | A | 59800 | 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)

From the above data, we can conclude that index opportunities such as ad_code,ad_code_ind,from_page_url_ind have increased by almost 85%, so that the efficiency has improved a lot.

Four, summary

In conjunction with the information on the MySQL official website, this is understood by the individual. When you delete data, MySQL does not reclaim the storage space that is occupied by the deleted data, as well as the index bit. Instead of waiting for new data to compensate for the vacancy, there is a lack of it, and if there is no data to fill the vacancy at 1:30, it would be a waste of resources. So for the comparison of the frequency of the table, to regularly carry out optimize, one months once, see the actual situation and decided.

The function and use of optimize table in MySQL