Beckham _ mysql table Optimization _ MySQL

Mysql table optimization: 1. Analysis Table 2. Check Table 3. Optimization Table 4. table data import 5. lock table operation 1. Analysis Table 11. Command: ANALYZE [NO_WRITE_TO_BIN Mysql table optimization Brief:
I. Analysis Table
II. Checklist
III. Optimization table
IV. table data import
5. table lock operations I. Analysis Table

1.1. command:

ANALYZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE tbl_name [, tbl_name]...

1.2. role:

This statement is used to analyze and store the table's keyword distribution. the analysis result will allow the system to obtain accurate statistical information, so that the SQL statement can generate the correct row plan.

1.3. limitations:

During the analysis, the read lock operation is performed on the table.

1.4. example:

II. Checklist

2.1. commands

Check table tbl_name [, tbl_name]... [option]...

Option = {for upgrade | QUICK | FAST | MEDIUM | EXTENDED | CHANGED}

2.2. role:

Check whether one or more tables have errors to facilitate troubleshooting

2.3. limitations:

During the checklist, more permissions or priorities must be granted to the current operator.

2.4. example:

III. optimize tablespaces

3.1. commands

OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE

Tbl_name [, tbl_name]...

3.2 Role

If you have deleted a large part of the table, or you have made many changes to the table with a variable length, you need to perform regular optimization. This command can merge and recycle space fragments in the table.

3.3. limitations:

The storage engine that depends on the table. It can be used only for the innodb and myisam storage engines.

3.4. example:

3.4.1 taking myisam as an example

Create Table: create table 'T '( 'Id' int (10) unsigned default null, 'Name' char (3) default null, 'Age' char (5) DEFAULT NULL ) ENGINE = MyISAM default charset = utf8

3.4.2 view table t size

3.4.3 delete table data t

3.4.4. Optimization table

IV. table data import

4.1 Non-unique index for myisam

Format: alter table name disable keys;

Loading data

Alter table name enable keys

(Note: disable keys and enable keys are used to enable or disable the update of non-unique indexes in the myisam table to increase the import speed .)

4.1.1. example:

Create table t

4.1.2 insert test data

4.1.3 export data and clear data

4.1.4. index disabled

4.1.5 disable the index

4.2 unique index for myisam

Format: set unique_checks = 0

Loading data

Setunique_checks = 1

(Note: unique_checks is the unique index validation of the myisam table. if it is 0, it is disabled. if it is 1, it is enabled. Therefore, you can disable it when importing data. After the import is successful, change the import time to improve the import speed .)

4.2.1. example:

4.2.2 insert test data

4.2.3 export and clear data

4.2.4 unique index verification not disabled

4.2.5 disable unique index verification

4.3 for innodb tables

Format: set autocommit = 0;

Loadingdata

Setautocommit = 1;

Execute setautocommit = 0 before importing data, disable automatic commit transactions, and execute set autocommit = 1 after import to restore automatic commit, which improves the import efficiency.

5. lock operation

5.1 myisam table read and write locks

5.1.1 read lock

5.1.1A. Role: once read is locked, the current table cannot be deleted, inserted, or modified.

5.1.1B. example:

A. Test table:

B. read lock

C. delete, modify, and insert operations on the current terminal

D. delete another terminal

(Wait status appears)

E. unlock operations

(Unlock the current terminal)

(Delete a new terminal)

5.1.2. myisam write lock

5.1.2A: once the write lock is performed, only the current terminal is allowed to perform addition, deletion, modification, and query operations. The new terminal is not allowed. you must wait until the first terminal completes execution.

5.1.2B. example:

First terminal

Second terminal

Unlock:

First terminal:

The second terminal:

5.1.3. will table locking affect the view?

5.1.3A. example

Read TABLE t

(Terminal for executing read lock)

(New Terminal)

(Unlock the old terminal)

(New Terminal)

Conclusion: write locks in tables a and myisam are stricter than read locks.

B. generally, read and write locks are rarely explicitly performed on the table. Myisam will be locked automatically

C. the read/write locks of the original table will affect the view.