MySQL online ddl

標籤：mysql online ddl

前言：

5.1 和 5.5 innodb plugin 支援Fast index create：

Fast index create 如何?的？ 只是對於 secondary index ，不需要copy table data。

執行過程：

1.判斷當前表是否有未結束的transaction(commit or rollback)

2.對原表加 shared lock

2.把secondary index 用的column 排序放到memory或temp file，建立B-Tree 索引

3.unlock table

注意每一次create index 或 alter table add index 都會掃描一次clustered index，所以盡量把多個添加索引語句放在一起。

alter table xxx drop index xx_idx1,add index xx_idx2,add index xx_idx3;

Fast index create的限制：

1.新的索引，臨時檔案寫入tempdir。

2.alter table drop index name_idx,add index name_idx;使用的是同一個索引名，使用copy table data。

3.TEMPORARY TABLE建立index 使用copy table data。

4.ALTER TABLE ... RENAME COLUMN 為了保證innodb的資料字典和mysql的資料字典資訊保持一致， 使用copy table data

5.The statement ALTER IGNORE TABLE t ADD UNIQUE INDEX does not delete duplicate rows. This has been reported as MySQL Bug #40344. The IGNORE keyword 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 更新secondary index 不用使用Fast index create。

DDL發展曆程：

1.copy table

  MySQL最早的DDL操作方式，DDL通過Copy Table方式實現：

 -建立temp table

 -鎖住原表，原表可讀不可寫

 -copy原表的資料到tempbiao

 -刪除原表，rename temp表

2.inplace

 -在原表上直接進行ddl，鎖表進行

 缺點：並發度低

3.online

ddl過程不長期鎖表，並發讀寫

    1.inplace online ddl

    2.copy table online ddl

二.從5.6 開始，支援Online DDL（inplace online ddl、copy table online ddl）

1.inplace online ddl

實現過程：

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" />

解決問題：

1.online ddl 過程中，add index，因缺乏新的索引，索引字典上新增一個標識：trx_id(代表索引建立過程最大的事務id)，小於此trx_id的事務都不使用新索引。

2.最佳化（加速）Online DDL的效能：

a可通過增加innodb_sort_buffer_size參數，最佳化Online (Add Index/Column)操作效能；

b建立索引，排序過程，使用記憶體大小為innodb_sort_buffer_size的3倍；

cRow Log Block大小，等於innodb_sort_buffer_size ；

1.copy table online ddl

實現過程：

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" />

本文出自 “My DBA life” 部落格，請務必保留此出處http://huanghualiang.blog.51cto.com/6782683/1596174

MySQL online ddl