Shulou(Shulou.com)06/01 Report--

Take a look at the description of OPTIMIZE in the manual:

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name]... You should use OPTIMIZE TABLE if you have deleted a large portion of the table, or if you have made many changes to tables with variable-length rows (tables with VARCHAR, BLOB, or TEXT columns). Deleted records are kept in the linked list, and subsequent INSERT operations reuse the old record location. You can use OPTIMIZE TABLE to reuse unused space and defragment data files. In most settings, you don't need to run OPTIMIZE TABLE at all. Even if you make a large number of updates to variable-length rows, you don't need to run them often, once a week or once a month, only on specific tables. OPTIMIZE TABLE only works on MyISAM, BDB, and InnoDB tables. Note that MySQL locks the table while OPTIMIZE TABLE is running.

raw data

1, amount of data

Mysql > select count (*) as total from ad_visit_history

+-+

| | total |

+-+

| | 1187096 | / / there are more than 1.18 million pieces of data in total |

+-+

1 row in set (0.04 sec)

2, the size of the table file stored on the hard disk

[root@ www.linuxidc.com test1] # ls | grep visit | xargs-I du {}

382020 ad_visit_history.MYD / / data files account for 380m

127116 ad_visit_history.MYI / / index files account for 127m

12 ad_visit_history.frm / / structure file occupies 12K

3. Check the index information.

Mysql > show index from ad_visit_history from test1; / / check the index information of 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 name of the table.

Non_unique: 0 if the index cannot include duplicate words. 1, if possible.

Key_name: the name of the index.

Seq_in_index: the column sequence number in the index, starting with 1.

Column_name: column name.

Collation: how columns are stored in the index. In MySQLSHOW INDEX syntax, there is a value of'A'(ascending order) or NULL (no classification).

Cardinality: an estimate of the number of unique values in the index. It can be updated by running ANALYZE TABLE or myisamchk-a. The cardinality is counted based on statistics stored as integers, so even for small tables, the value does not need to be accurate. The higher the cardinality, the greater the chance that MySQL will use the index when federating.

Sub_part: if the column is only partially indexed, the number of characters indexed. NULL if the entire column is indexed.

Packed: indicates how keywords are compressed. NULL if it is not compressed.

Null: if the column contains NULL, it contains YES. If not, it is empty.

Index_type: methods for storing index data structures (BTREE, FULLTEXT, HASH, RTREE)

Second, 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)

[root@ www.linuxidc.com test1] # ls | grep visit | xargs-I du {} / / the corresponding MYD,MYI file size remains unchanged

382020 ad_visit_history.MYD

127116 ad_visit_history.MYI

12 ad_visit_history.frm

According to conventional thinking, if half of the data is deleted in the database, the corresponding .MYD, .MYI files should also become half of what they were before. But after deleting half of the data, .MYD.MYI did not reduce 1KB at all, which is so terrible.

Let's take 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)

By comparison, it is reasonable to see that the data information in this index query and the last index query is basically the last one.

Third, optimize it with optimize table.

?? mysql > optimize table ad_visit_history; / / Optimization after data deletion

+-- +

| | Table | Op | Msg_type | Msg_text | |

+-- +

| | test1.ad_visit_history | optimize | status | OK | |

+-- +

1 row in set (1 min 21.05 sec)

1, check the size of the .MYD, .MYI file

?? [root@ www.linuxidc.com test1] # ls | grep visit | xargs-I du {}

182080 ad_visit_history.MYD / / data files are almost half of what they were before optimization

66024 ad_visit_history.MYI / / index files are the same, about half of what they were before optimization

12 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 see that indexing opportunities such as ad_code,ad_code_ind,from_page_url_ind have increased by almost 85%, so the efficiency has been greatly improved.

Fourth, summary

Combined with the information on the official mysql website, the individual understands it in this way. When you delete data, mysql does not reclaim the storage space occupied by the deleted data, as well as index bits. It is empty there, but waiting for new data to fill the gap, so that there is a lack, if for a while, there is no data to fill the gap, it will be a waste of resources. So for tables that are annoying to write frequently, optimize should be carried out regularly, once a month, depending on the actual situation.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.