MySQL Database maintenance, optimization

Reclaim Table Space

When a data table deletes data in large quantities, it uses the optimize table for space reclamation, creating a new data table and then copying the contents to the new table:

OPTIMIZE TABLE "TABLENAME";

InnoDB to be rebuilt with the following command:

ALTER TABLE table.name engine= ' InnoDB ';

Otherwise, an error will occur:

Table does not support optimize, doing recreate + analyze instead

Check the data table status

ANALYZE table, which is used to check the correct form keys.

ANALYZE TABLE MMB:

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M02/08/8E/wKiom1nkJTWRTeJGAAAPgOuRA3s166.png "title=" QQ picture 20171016111854.png "alt=" Wkiom1nkjtwrtejgaaapgoura3s166.png "/>

Check table is used to examine tables for a number of issues:

As shown below (excerpt from MySQL must know), CheckTable discovers and fixes the problem:

Check table orders, OrderItems;

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M00/A7/3E/wKioL1nkJpPBtSrPAABt2uytVz8513.png "title=" QQ picture 20171016112857.png "alt=" Wkiol1nkjppbtsrpaabt2uytvz8513.png "/>

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

Parameters:

CHANGED Check the tables that have changed since the last check;

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

Quick Scan only;

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

Modify memory allocations and buffer sizes, and so on:

To view the current status of MySQL:

SHOW STATUS;

View MySQL current settings:

SHOW VARIABLES;

To modify the current MySQL settings:

Most parameters can modify the current parameter directly with set, but it will fail after the service restarts, so you still have to write the parameters to the my.cnf file.

Show MySQL thread info show processlist

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M00/A7/44/wKioL1nkXsSgUrvbAAAb3eTreLA663.png "title=" QQ picture 20171016153555.png "alt=" Wkiol1nkxssgurvbaaab3etrela663.png "/>

This command, state,and info columns should be the most important, showing what the statement is in, convenient for analysis. The number of command states should not be many, which consumes connection pooling and severely wastes system resources.

KILL Process:

Kill 33; --end process with ID 33

MySQL Database maintenance, optimization