Shulou(Shulou.com)05/31 Report--

MySQL data fragmentation and analysis, in view of this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

MySQL has quite a number of different storage engines to implement the data storage function in the list. Every time MySQL removes a line from your list, the space is left blank. A large number of deletions over a period of time will make this empty space more than the space used to store the contents of the list. When MySQL scans data, the object it scans is actually the upper limit of the capacity requirement of the list, that is, the part of the area where the data is written at its peak. If a new insert is made, MySQL will try to take advantage of these empty areas, but will still not be able to fully occupy them. This additional broken storage space is much less efficient in terms of read efficiency than normally occupied.

The following experimental examples are given:

C:\ Users\ duansf > mysql-uroot-p123456

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 4

Server version: 5.5.13 MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

Affiliates. Other names may be trademarks of their respective

Owners.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

Mysql >

Create a test library:

Mysql > create database frag_test

Query OK, 1 row affected (0.01sec)

Mysql > use frag_test

Database changed

Mysql > create table frag_test (C1 varchar (64))

Query OK, 0 rows affected (0.27 sec)

Insert several rows of data:

Mysql > insert into frag_test values ('this is row 1')

Query OK, 1 row affected (0.21 sec)

Mysql > insert into frag_test values ('this is row 2')

Query OK, 1 row affected (0.05sec)

Mysql > insert into frag_test values ('this is row 3')

Query OK, 1 row affected (0.03 sec)

Now let's look at the fragments:

Mysql > show table status from frag_test\ G

* * 1. Row *

Name: frag_test

Engine: InnoDB

Version: 10

Row_format: Compact

Rows: 3

Avg_row_length: 5461

Data_length: 16384

Max_data_length: 0

Index_length: 0

Data_free: 10485760

Auto_increment: NULL

Create_time: 2016-03-17 16:36:04

Update_time: NULL

Check_time: NULL

Collation: utf8_general_ci

Checksum: NULL

Create_options:

Comment:

1 row in set (0.00 sec)

ERROR:

No query specified

Delete a row and detect again:

Mysql > delete from frag_test where C1 = 'this is row 2'

Query OK, 1 row affected (0.07 sec)

Mysql > show table status from frag_test\ G

* * 1. Row *

Name: frag_test

Engine: InnoDB

Version: 10

Row_format: Compact

Rows: 2

Avg_row_length: 8192

Data_length: 16384

Max_data_length: 0

Index_length: 0

Data_free: 10485760

Auto_increment: NULL

Create_time: 2016-03-17 16:36:04

Update_time: NULL

Check_time: NULL

Collation: utf8_general_ci

Checksum: NULL

Create_options:

Comment:

1 row in set (0.00 sec)

Data_free: 10485760 We see that the value of Data_free has not decreased.

Try cleaning up the debris:

Mysql > optimize table frag_test

+--

-- +

| | Table | Op | Msg_type | Msg_text |

| |

+--

-- +

| | frag_test.frag_test | optimize | note | Table does not support optimize, d |

Oing recreate + analyze instead |

| | frag_test.frag_test | optimize | status | OK |

| |

+--

-- +

2 rows in set (0.66 sec)

Mysql > show table status from frag_test\ G

* * 1. Row *

Name: frag_test

Engine: InnoDB

Version: 10

Row_format: Compact

Rows: 2

Avg_row_length: 8192

Data_length: 16384

Max_data_length: 0

Index_length: 0

Data_free: 9437184

Auto_increment: NULL

Create_time: 2016-03-17 16:36:04

Update_time: NULL

Check_time: NULL

Collation: utf8_general_ci

Checksum: NULL

Create_options:

Comment:

1 row in set (0.00 sec)

Data_free: 9437184 after the debris is cleared, the Data_free is reduced.

The "data_free" column shows the space left after we delete the second line. Imagine what the result would be if you had 20,000 lines of instructions.

It is estimated that they will consume 400000 bytes of storage space. Now if you delete 20,000 command lines to only one line, there will be only 20 bytes of useful content in the list.

But MySQL still treats it as a list with a capacity of 400000 bytes, and all but 20 bytes of space is wasted.

Note:

1.MySQL officials recommend that defragmentation should not be done frequently (hourly or daily), but only once a week or once a month, depending on the actual situation.

2.OPTIMIZE TABLE only works on MyISAM,BDB and InnoDB tables, especially on MyISAM tables. In addition, not all tables need to be defragmented

Generally, you only need to sort out the tables that contain variable-length text data types.

3. While OPTIMIZE TABLE is running, MySQL locks the table.

4. By default, using OPTIMIZE TABLE directly on the data table of the InnoDB engine may display a prompt for "Table does not support optimize, doing recreate + analyze instead".

At this point, we can restart MySQL with the mysqld-- skip-new or mysqld-- safe-mode command so that other engines can support OPTIMIZE TABLE.

This is the answer to the question about the defragmentation and analysis of MySQL data. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

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.