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

This article introduces the relevant knowledge of "what is the maintenance operation of optimize, Analyze and check in Mysql". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Optimize

Data in the insert, update, delete when some data migration, paging, and then there are some fragments, over time fragments accumulated to affect performance, which requires DBA to regularly optimize the database to reduce fragments, this is through the optimize command. Optimize table table name.

Fragments can be viewed according to show table status, and the ratio of actual data data_length to hollow fragment data data_free can be judged.

Note: optimize table generates locks, which are recommended to be executed at the bottom and peak of the business, and can only be used for MyISAM

If InnoDB needs to use alter table tabname engine=innodb

Analyze

It is used to analyze and store the distribution of keywords in the table, so that the system can obtain accurate statistical information and affect the generation of the execution plan of SQL. For tables whose data has basically not changed, there is no need for frequent table analysis. However, if the amount of data in the table changes significantly, and the user feels that the actual execution plan is different from the expected execution plan, performing a table analysis may help to produce the expected execution plan.

Analyze table table name

Check

Checking tables or views for errors is useful for tables in MyISAM and InnoDB storage engines. Table checks are performed on the tables of the MyISAM storage engine, and keyword statistics are also updated

Repair optimize needs to have enough hard disk space, otherwise it may destroy the table and make it impossible to operate. It is necessary to use repair. Note that INNODB does not support repair operation.

This is the end of the content of "what are the maintenance operations of optimize, Analyze, and check in Mysql". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.