Small Shell _mysql Table optimization

MySQL table optimizer Brief:
first, the analysis table
second, the inspection table
third, optimize the table
iv. Table Data Import
Five, lock table operation first, the analysis table

1.1. Command:

ANALYZE [No_write_to_binlog | Local]table tbl_name [, Tbl_name] ...

1.2. Function:

This statement is used to analyze and store the keyword distribution of tables. The results of the analysis will allow the system to get accurate statistics so that SQL can generate the correct line plan

1.3. Insufficient:

A read-lock operation is performed on the table during parsing

1.4. Examples:

second, the inspection table

2.1. Command

CHECK TABLE tbl_name [, Tbl_name] ... [option] ...

option = {for UPGRADE | QUICK | FAST | MEDIUM | EXTENDED | CHANGED}

2.2. Function:

Check if one or more tables have errors. Ease of troubleshooting

2.3. Insufficient:

A lot of other permissions or priorities are required to run the current operator during the checklist

2.4. Examples:

third, optimize the table space

3.1. Command

OPTIMIZE [No_write_to_binlog | Local]table

Tbl_name [, Tbl_name] ...

3.2. function

Assume that a large portion of the table has been deleted, or that a table with variable lengths has been modified very much. You need to do regular optimization. This command merges the space fragments in the table. For recycling.

3.3. Insufficient:

The storage engine that relies on tables. Can only be used for INNODB and MyISAM storage engines

3.4. Examples:

3.4.1, table structure with MyISAM as an example

Create table:create Table ' t ' ( ' id ' int (ten) 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, deleting table data t

3.4.4, Optimization table

iv. table Data import

4.1, for MyISAM non-unique index

Format: ALTER TABLE name disable keys;

Loading data

ALTER TABLE table name enable keys

(Note: Disable keys and enable keys are used to turn on or off updates to non-unique indexes in the MyISAM table, increasing the import speed.) )

4.1.1, Examples:

CREATE TABLE T

4.1.2, inserting test data

4.1.3, export data and erase data

4.1.4, index not closed

4.1.5, close Index

4.2. Unique index for MyISAM

Format: Set unique_checks=0

Loading data

Setunique_checks=1

(Note: Unique_checks is a checksum of the MyISAM table for a unique index.) When it is 0 o'clock, it is closed, and 1 o'clock, then it is turned on.

Therefore, you can import the data. Let it shut down. The import is successful and then rescheduled. Increase the import speed. )

4.2.1, Examples:

4.2.2, inserting test data

4.2.3, export data and erase data

4.2.4, unique index check not turned off

4.2.5, turn off unique index checksum

4.3, for InnoDB table

Format: Set autocommit=0;

Loadingdata

Setautocommit=1;

Run setautocommit=0 before importing the data, and turn off the commit transaction yourself. Run set autocommit=1 at the end of the import to restore your own unsolicited commits, which increases the efficiency of the import.

Five, lock operation

5.1, MyISAM table read, write lock

5.1.1, read lock

5.1.1A, function: Once the read lock. The current table does not agree to the deletion, insertion, alteration, etc.

5.1.1B, Sample:

A, measured frequently:

B, read lock

C, current terminal run Delete, change, insert operation

D, another terminal to run the delete operation

( waiting status appears)

E, unlock operation

(current terminal unlocked)

(The new terminal is finished before the delete operation)

5.1.2, MyISAM write lock

5.1.2A, function: Once the write lock. will only agree to the current terminal to run additions and deletions to check operation. The new terminal is different from the idea. You must wait for the first terminal to finish running. Talent Enough

5.1.2B, Sample:

First Terminal

A second terminal

Unlock:

First Terminal:

A second terminal:

5.1.3, whether table locking affects views

5.1.3A, sample

Read lock on table T

(terminal with read lock running)

(New terminal)

(Old terminal unlocked)

(New terminal)

Summary: A, MyISAM table write lock more stringent than read lock

b, under normal circumstances very little to explicitly go to the table read and write lock. MyISAM will take the initiative to lock itself.

C, the original table read-write lock affects the view

The quieter become,the more you is able to hear!

Small Shell _mysql Table optimization