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

This article mainly introduces "what is the role of MyISAM's OPTIMIZE". In daily operation, I believe many people have doubts about the role of MyISAM's OPTIMIZE. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the questions of "what is the role of MyISAM's OPTIMIZE?" Next, please follow the editor to study!

Some introductions

Use the OPTIMIZE command to organize the files of the MyISAM table. It's like we use the Windows operating system every once in a while.

After that, we will do a disk defragmentation to make the files in the system use contiguous space as much as possible to improve the access speed of files. MyISAM in

When optimizing and sorting through OPTIMIZE, the main thing is to clean up the debris space caused by data deletion and update, so that the whole file

Keep it together. Generally speaking, you need to do an OPTIMIZE operation after each large data deletion operation. And every

There should be an OPTIMIZE maintenance operation every quarter.

Create a table and insert a large amount of data

(localhost@testdb) [root] > select * from test5

+-+ +

| | id | name |

+-+ +

| | 1 | aa |

(localhost@testdb) [root] > insert into test5 select * from test5

Query OK, 12288 rows affected (0.07 sec)

(localhost@testdb) [root] >

(localhost@testdb) [root] >

(localhost@testdb) [root] > show index from test5

+- -+

| | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+- -+

| | test5 | 1 | id | 1 | id | A | NULL | NULL | NULL | | BTREE |

+- -+

1 row in set (0.00 sec)

(localhost@testdb) [root] >

(localhost@testdb) [root] >

(localhost@testdb) [root] >

[root@node1 testdb] # ls-trl

Total 2096

-rw-r- 1 mysql mysql 65 Feb 23 09:54 db.opt

-rw-r- 1 mysql mysql 8586 Feb 26 16:18 test5.frm

-rw-r- 1 mysql mysql 293888 Feb 29 15:52 test5.MYI

-rw-r- 1 mysql mysql 491520 Feb 29 15:52 test5.MYD

Delete some data of the table

(localhost@testdb) [root] > delete from test5 where id=1

Query OK, 8192 rows affected (0.16 sec)

(localhost@testdb) [root] >

(localhost@testdb) [root] >

(localhost@testdb) [root] >

(localhost@testdb) [root] >

(localhost@testdb) [root] > commit

Query OK, 0 rows affected (0.00 sec)

(localhost@testdb) [root] > delete from test5 where id=2

Query OK, 8192 rows affected (0.14 sec)

(localhost@testdb) [root] > commit

Query OK, 0 rows affected (0.00 sec)

Third, do optimize operation

(localhost@testdb) [root] > optimize table test5

+-+

| | Table | Op | Msg_type | Msg_text | |

+-+

| | testdb.test5 | optimize | status | OK | |

+-+

1 row in set (0.02 sec)

(localhost@testdb) [root] > show index from test5

+- -+

| | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+- -+

| | test5 | 1 | id | 1 | id | A | 1 | NULL | NULL | | BTREE |

+- -+

1 row in set (0.00 sec)

Fourth, after the completion of the operation, the index and data are reduced

[root@node1 testdb] # ls-trl

Total 1576

-rw-r- 1 mysql mysql 65 Feb 23 09:54 db.opt

-rw-r- 1 mysql mysql 8586 Feb 26 16:18 test5.frm

-rw-r- 1 mysql mysql 87040 Feb 29 15:53 test5.MYI

-rw-r- 1 mysql mysql 163840 Feb 29 15:53 test5.MYD

At this point, the study on "what is the role of OPTIMIZE of MyISAM" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.