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 the table, and the results of the analysis will enable 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. Example:

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 one or more tables for errors and ease of troubleshooting

2.3. Insufficient:

During the checklist, you need to give more permissions or priority to the execution of the current operator

2.4. Example:

third, optimize the table space

3.1. Command

OPTIMIZE [No_write_to_binlog | Local]table

Tbl_name [, Tbl_name] ...

3.2. function

If you have deleted a large part of a table, or have made a lot of changes to a table with variable lengths, you need to do a regular optimization. This command merges the space fragments in the table and recycles them.

3.3. Insufficient:

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

3.4. Example:

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 check for a unique index of the MyISAM table, and when it is 0 o'clock, it is off and 1 o'clock, then on.) As a result, you can turn it off when you import your data. When the import is successful, reschedule and 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;

Perform setautocommit=0 before importing data, turn off autocommit transactions, perform set autocommit=1 after import, and resume auto-commit to improve import efficiency.

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 is not allowed to delete, insert, modify and other operations

5.1.1B, Example:

A, test table:

B, read lock

C, the current terminal to delete, modify, insert operation

D, another terminal to perform the delete operation

( waiting status appears)

E, unlock operation

(current terminal unlocked)

(The new terminal does not complete the delete operation)

5.1.2, MyISAM write lock

5.1.2A, function: Once the write lock, then only allow the current terminal to perform additions and deletions, the new terminal is not allowed, must wait for the first terminal to finish, before you can

5.1.2B, Example:

First Terminal

A second terminal

Unlock:

First Terminal:

A second terminal:

5.1.3, whether table locking affects views

5.1.3A, example

Read lock on table T

(terminal to perform read lock)

(New terminal)

(Old terminal unlocked)

(New terminal)

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

b, in general, rarely go explicitly to read and write locks on the table. The MyISAM will automatically lock

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

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

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Small Shell _mysql Table optimization