MySQL Online DDL

Objective:

5.1 and 5.5 InnoDB Plugin support for fast index create:

How is Fast index create implemented? Only for secondary index, no copy table data is required.

Execution process:

1. Determine if the current table has an transaction (commit or rollback)

2. Add shared lock to the original table

2. Put the column sort of secondary index in memory or temp file, build B-tree index

3.unlock table

Note that each time the CREATE INDEX or ALTER TABLE ADD index is scanned for clustered index once, so try to put multiple add-in statements together.

ALTER TABLE XXX DROP INDEX Xx_idx1,add index xx_idx2,add index xx_idx3;

Limitations of Fast index create:

1. New index, temporary file written to TempDir.

2.alter Table Drop index Name_idx,add index name_idx; Using the same index name, use copy table data.

3. TEMPORARY TABLE 创建index  use Copy table data.

4.ALTER TABLE ... RENAME COLUMN to ensure consistency between the InnoDB data dictionary and the MySQL data dictionary information,   use the Copy table data

5.The statementALTER IGNORE TABLE t ADD UNIQUE INDEXdoes not delete duplicate rows. This has been reported as MySQL Bug #40344. theIGNOREkeyword is ignored. If any duplicate rows exist, the operation fails with the following error message:

• ERROR 23000:duplicate entry ' 347 ' for key ' pl '

6.optimize Table Update Secondary index does not use fast index create.

DDL Development History:

1.copy table

MySQL the earliest DDL mode of operation, DDL through Copy Table Way to achieve:

-Create a new temp table

-Lock the original table, the original table can be read and not writable

-copy data from the original table to Tempbiao

-Delete the original table, rename temp table

2.inplace

-Direct DDL on the original table, lock table for

Cons: Low concurrency

3.online

DDL process not long-term lock table, concurrent read and write

1.inplace Online DDL

2.copy Table Online DDL

Two. Starting from 5.6, support for online DDL (inplace online DDL,copy table online DDL)

1.inplace Online DDL

Implementation process:

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>650 "this.width=650;" src= "http ://s3.51cto.com/wyfs02/m02/57/85/wkiol1sc2ffhpkehaapjeqkumbo106.jpg "title=" Ddl1.png "alt=" Wkiol1sc2ffhpkehaapjeqkumbo106.jpg "/>

Solve the problem:

1.online DDL process, add index, because of the lack of a new index, the index dictionary has a new identity: trx_id (the transaction ID that represents the largest index creation process), and transactions less than this trx_id do not use the newly indexed .

2. Optimize (accelerate) the performance of the Online DDL:

A can optimize Online (add index/column) operation performance by adding innodb_sort_buffer_size parameters ;

b CREATE INDEX, sort process, use memory size of innodb_sort_buffer_size 3 times;

CRow Log Block size, equal to innodb_sort_buffer_size ;

1.Copy table online DDL

Implementation process:

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>650 "this.width=650;" src= "http ://s3.51cto.com/wyfs02/m02/57/88/wkiom1sc2vtq1vpqaah9t7b777u195.jpg "title=" Ddl2.png "alt=" Wkiom1sc2vtq1vpqaah9t7b777u195.jpg "/>

This article is from the My DBA life blog, so be sure to keep this source http://huanghualiang.blog.51cto.com/6782683/1596174

MySQL Online DDL