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

This article mainly tells you how to maintain and optimize the mysql database. You can look up the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here, so let's go straight to the topic. I hope that this article on how to maintain and optimize the mysql database can bring you some practical help.

● Recycling tablespace

When a data table deletes a large number of data, use OPTIMIZE TABLE to reclaim the space. In principle, create a new data table, and then copy the contents to the new table:

OPTIMIZE TABLE "TABLENAME"

InnoDB will rebuild with the following command:

ALTER TABLE table.name ENGINE='InnoDB'

Otherwise, an error will occur:

Table does not support optimize, doing recreate + analyze instead

● checks the status of the data table

ANALYZE TABLE, which is used to check whether the table key is correct.

ANALYZE TABLE mmb:

● CHECK TABLE is used to examine tables against many problems:

As shown below (the following figure is taken from mysql must know), CHECKTABLE discovers and fixes problems:

Check table orders, orderitems

The index is also checked on the MyISAM table. CHECK TABLE supports a range of ways to use MyISAM tables.

Parameters:

CHANGED checks tables that have changed since the last check

EXTENDED performs the most thorough check, FAST only checks tables that are not properly closed, and MEDIUM checks all deleted links and performs key verification

QUICK only performs fast scans

If MyISAM table access produces incorrect and inconsistent results, you may need to use REPAIR TABLE to fix the corresponding table. This statement should not be used frequently, and if it needs to be used frequently, there may be bigger problems to solve.

● modifies memory allocation and buffer size, etc.:

View the current status of MYSQL:

SHOW STATUS

View the current MYSQL settings:

SHOW VARIABLES

Modify the current MYSQL settings:

Most of the parameters can be modified directly with set, but will expire after the service restart, so you still have to write the parameters to the my.cnf file.

● displays MYSQL thread information SHOW PROCESSLIST

The command,state and info columns should be the most important to show what state the statement is in and to facilitate analysis. the number of command states should not be large, which will occupy connection pooling and seriously waste system resources.

● KILL process:

Kill 33;-- end the process with id 33 shown above

How to maintain and optimize the mysql database will first tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

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.