MySQL 5.6 Online DDL.md
12794 ワード
一.Fast index Creation
MySQL 5.5以降、MySQL 5.1 innodb pluginはFast index Creationをサポートしています.以前のバージョンではインデックスの追加や削除などのDDL操作について、MySQLデータベースの操作手順は次のとおりです.
(1)まず新しいテンポラリテーブルを作成し,命令ALTAR TABLEによって新しく定義された構造をテーブル構造にする.
(2)元のテーブルのデータをテンポラリテーブルにインポートする
(3)元のテーブルを削除する
(4)最後にテンポラリ・テーブルを元のテーブル名に変更する
上記の手順では、大きなテーブルにインデックスを追加または削除するには、長い時間がかかります.致命的なのは、大量のアクセス要求がある場合、サービスを提供できないことを意味します.
innodbストレージエンジンは1.0から.xバージョンではFast index Creation(クイックインデックス作成)がサポートされ始めました.FICと略称する.セカンダリインデックスの作成では、インデックスを作成したテーブルにSロックが追加されます.作成中にテーブルを再構築する必要がないため、速度が大幅に向上します.セカンダリインデックスinnodbストレージエンジンを削除するには、内部ビューを更新し、セカンダリインデックスのスペースを使用可能にマークするとともに、MySQLデータベースの内部ビューでテーブルのインデックス定義を削除します.特に注意が必要な場合、テンポラリ・テーブルの作成パスはパラメータtmpdirによって設定されます.tmpdirに十分なスペースがあることを確認する必要があります.そうしないと、セカンダリインデックスの作成に失敗します.セカンダリインデックスを作成するときにSロックが追加されるため、このプロセスではテーブルの読み取りのみが実行され、トランザクションがテーブルの書き込みを必要とする場合は、データベース・サービスも使用できません.FIC方式は補助インデックスに限定され、プライマリ・キーの作成と削除についても同じようにテーブルを再構築する必要があることに注意してください.
二.Oline Schema Change
Online Schema Change(オンラインアーキテクチャの変更、OSCと略称)は、Facebookによって最初に実現されたオンラインDDLの方式である.「オンライン」とは、フィールドを追加したり、インデックスを追加したりするDDL操作で、トランザクションによるテーブルの読み書き操作がブロックされないことを意味します.
三.Online DDL
FICはinnodbストレージエンジンに一時テーブルの作成を回避させ、インデックスの作成効率を向上させることができます.FICはリード動作をブロックしないが、DML動作は依然としてブロックされている.MySQL 5.6ではオンラインDDL(オンラインデータ定義)操作がサポートされ始め、セカンダリインデックスの作成とINSERT、UPDATE、DELETEなどのDML操作も許可されています.また、補助インデックスだけでなく、以下のDDL操作は「オンライン」で行うことができます.
(1)削除による補助インデックスの作成
(2)自己成長値の変更
(3)外部キーコンストレイントの追加または削除
(4)列の名前変更
新しいALTER TABLEでインデックスの作成方法を選択できます
ALGORITHMはインデックスの作成または削除のアルゴリズムを指定し、COPYはMySQL 5.1のバージョン以前の方法でテンポラリ・テーブルを作成することを示します.INPLACEは、インデックスの作成または削除操作でテンポラリ・テーブルを作成する必要がないことを示します.DEFAULTはパラメータold_によるalter_tableでINPLACEかCOPYかのアルゴリズムを判断し、パラメータをデフォルトでOFFに変更し、INPLACE方式を採用することを示す
LOCKセクションでは、インデックス作成または削除時にテーブルにロックを追加する場合、(1)NONE、インデックス作成または削除操作を実行する場合、ターゲットテーブルにロックを追加しません.つまり、トランザクションは読み書き操作が可能で、ブロックが受信されず、このモードで最大の同時実行が可能です.
(2)SHAREは,Fast index Creationと同様に,インデックス作成または削除操作を行うと,ターゲットテーブルにSロックを付加する.同時読み込みトランザクションの場合も実行できます.しかし、書き込みトランザクションが発生すると、ストレージエンジンがSHAREモードをサポートしていない場合は、エラーメッセージが返されます.
(3)EXCLUSIVE,インデックス作成または削除を行う場合,ターゲットテーブルにXロックを付加する.読み書きトランザクションは実行できません.すべてのスレッドがブロックされます.これはCOPY方式と似ていますが、COPY方式のようにテンポラリ・テーブルを作成する必要はありません.
(4)DEFAULTは、まず現在の動作でNONEモードが使用可能か否かを判断し、できない場合はSHAREモードが使用可能か否かを判断し、最後にEXCLUSIVEモードが使用可能か否かを判断する.つまりDEFAULTは,トランザクションの最大同時性を判断することでDDLを実行するパターンを判断する.
innodbストレージエンジンがOnline DDLを実現する原理は、作成または削除操作を実行すると同時に、INSERT、UPDATE、DELETEのようなDML操作ログをキャッシュに書き込み、インデックスの作成が完了した後にテーブルに再適用し、データの一貫性を達成することである.このキャッシュのサイズはパラメータinnodb_online_alter_log_max_size制御、デフォルトサイズは128 MBです.
更新するテーブルが比較的大きく、作成中に大量の書き込みトランザクションがある場合はinnodb_online_alter_log_max_sizeのスペースにログを保存できない場合は、対応するエラーが投げ出されます.これは後でテストします.変更エラーが発生した場合、パラメータを大きくして、より大きなログキャッシュ領域を得ることができます.さらに、実行中に書き込みトランザクションが発生しないように、ALTER TABLEのモードをSHAREに設定できます.そのためDMLログの記録は不要です.
上の簡単な説明を通じて、みんなの心の中に譜面があると信じています.では、実際にテストしてみましょう.ここでsysbenchを使用して1000 w行のテストデータを生成します.
1.最初に補助インデックスを追加するテスト
セッション1でインデックスの追加操作を実行し、セッション2でDML操作を実行する.
session 1(alter tableはデフォルトの実行方法を選択し、innodbストレージエンジンにどのロックを追加すべきかを自分で判断させる)
session 2(時計がロックされていないことがわかり、すべて正常です)
2.テストフィールドsession 1を追加
session 2
フィールドを追加してもDML操作には影響しないことがわかります.爽やかじゃない?爽やかならアップグレードしましょう.mysql 5.5にフィールドを追加したらどうなりますか?mysql 5.5にフィールドを追加すると、テーブルがロックされ、読み書きがブロックされます(増加し、インデックスを削除するとSロックされ、書き込みがブロックされます).まだmysql 5.6を使用していない学生も心配しないでください.oak-online-alter-tableとpt-online-schema-changeの2つのツールが非常に使いやすいからです.mysql 5.5にフィールドを追加する場合を見てみましょう.
別のセッションの表示
ロックテーブルが表示され、一時テーブルが作成されています.
ただし、MySQL 5.6は必ずしもテーブルをロックしないわけではありません.大きな結果のクエリがテーブルをクエリーしている場合、ALTER TABLEを実行するとテーブルがロックされます.簡単なテストをします.
session 1
session 2
session 3
ロックテーブルが発生しているのが見えますね.したがって、オンラインになるときは、スローSQLまたは比較的大きな結果セットのSQLが実行されているかどうかを確認する必要があります.そうしないと、ALTER TABLEを実行するときにロックテーブルが発生します.もちろんoak-online-alter-tableとpt-online-schema-changeにこの制限があるかどうかは分かりません.時間を割くにはテストが必要です.
参考資料:http://www.cnblogs.com/gomysql/p/3776192.html http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html
http://www.mysqlperformanceblog.com/2014/02/26/monitor-alter-table-progress-innodb_file_per_table/
『MySQL技術内幕--innodbストレージエンジン第2版』
MySQL 5.5以降、MySQL 5.1 innodb pluginはFast index Creationをサポートしています.以前のバージョンではインデックスの追加や削除などのDDL操作について、MySQLデータベースの操作手順は次のとおりです.
(1)まず新しいテンポラリテーブルを作成し,命令ALTAR TABLEによって新しく定義された構造をテーブル構造にする.
(2)元のテーブルのデータをテンポラリテーブルにインポートする
(3)元のテーブルを削除する
(4)最後にテンポラリ・テーブルを元のテーブル名に変更する
上記の手順では、大きなテーブルにインデックスを追加または削除するには、長い時間がかかります.致命的なのは、大量のアクセス要求がある場合、サービスを提供できないことを意味します.
innodbストレージエンジンは1.0から.xバージョンではFast index Creation(クイックインデックス作成)がサポートされ始めました.FICと略称する.セカンダリインデックスの作成では、インデックスを作成したテーブルにSロックが追加されます.作成中にテーブルを再構築する必要がないため、速度が大幅に向上します.セカンダリインデックスinnodbストレージエンジンを削除するには、内部ビューを更新し、セカンダリインデックスのスペースを使用可能にマークするとともに、MySQLデータベースの内部ビューでテーブルのインデックス定義を削除します.特に注意が必要な場合、テンポラリ・テーブルの作成パスはパラメータtmpdirによって設定されます.tmpdirに十分なスペースがあることを確認する必要があります.そうしないと、セカンダリインデックスの作成に失敗します.セカンダリインデックスを作成するときにSロックが追加されるため、このプロセスではテーブルの読み取りのみが実行され、トランザクションがテーブルの書き込みを必要とする場合は、データベース・サービスも使用できません.FIC方式は補助インデックスに限定され、プライマリ・キーの作成と削除についても同じようにテーブルを再構築する必要があることに注意してください.
二.Oline Schema Change
Online Schema Change(オンラインアーキテクチャの変更、OSCと略称)は、Facebookによって最初に実現されたオンラインDDLの方式である.「オンライン」とは、フィールドを追加したり、インデックスを追加したりするDDL操作で、トランザクションによるテーブルの読み書き操作がブロックされないことを意味します.
三.Online DDL
FICはinnodbストレージエンジンに一時テーブルの作成を回避させ、インデックスの作成効率を向上させることができます.FICはリード動作をブロックしないが、DML動作は依然としてブロックされている.MySQL 5.6ではオンラインDDL(オンラインデータ定義)操作がサポートされ始め、セカンダリインデックスの作成とINSERT、UPDATE、DELETEなどのDML操作も許可されています.また、補助インデックスだけでなく、以下のDDL操作は「オンライン」で行うことができます.
(1)削除による補助インデックスの作成
(2)自己成長値の変更
(3)外部キーコンストレイントの追加または削除
(4)列の名前変更
新しいALTER TABLEでインデックスの作成方法を選択できます
mysql [localhost] {msandbox} ((none)) > select version();
+-----------+
| version() |
+-----------+
| 5.6.19 |
+-----------+
1 row in set (0.00 sec)
mysql [localhost] {msandbox} ((none)) >
? alter table
| ALGORITHM [=] {DEFAULT|INPLACE|COPY}
| ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
| CHANGE [COLUMN] old_col_name new_col_name column_definition
[FIRST|AFTER col_name]
| LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}
| MODIFY [COLUMN] col_name column_definition
[FIRST | AFTER col_name]
ALGORITHMはインデックスの作成または削除のアルゴリズムを指定し、COPYはMySQL 5.1のバージョン以前の方法でテンポラリ・テーブルを作成することを示します.INPLACEは、インデックスの作成または削除操作でテンポラリ・テーブルを作成する必要がないことを示します.DEFAULTはパラメータold_によるalter_tableでINPLACEかCOPYかのアルゴリズムを判断し、パラメータをデフォルトでOFFに変更し、INPLACE方式を採用することを示す
mysql [localhost] {msandbox} ((none)) > show variables like '%old_alter%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| old_alter_table | OFF |
+-----------------+-------+
1 row in set (0.00 sec)
mysql [localhost] {msandbox} ((none)) >
LOCKセクションでは、インデックス作成または削除時にテーブルにロックを追加する場合、(1)NONE、インデックス作成または削除操作を実行する場合、ターゲットテーブルにロックを追加しません.つまり、トランザクションは読み書き操作が可能で、ブロックが受信されず、このモードで最大の同時実行が可能です.
(2)SHAREは,Fast index Creationと同様に,インデックス作成または削除操作を行うと,ターゲットテーブルにSロックを付加する.同時読み込みトランザクションの場合も実行できます.しかし、書き込みトランザクションが発生すると、ストレージエンジンがSHAREモードをサポートしていない場合は、エラーメッセージが返されます.
(3)EXCLUSIVE,インデックス作成または削除を行う場合,ターゲットテーブルにXロックを付加する.読み書きトランザクションは実行できません.すべてのスレッドがブロックされます.これはCOPY方式と似ていますが、COPY方式のようにテンポラリ・テーブルを作成する必要はありません.
(4)DEFAULTは、まず現在の動作でNONEモードが使用可能か否かを判断し、できない場合はSHAREモードが使用可能か否かを判断し、最後にEXCLUSIVEモードが使用可能か否かを判断する.つまりDEFAULTは,トランザクションの最大同時性を判断することでDDLを実行するパターンを判断する.
innodbストレージエンジンがOnline DDLを実現する原理は、作成または削除操作を実行すると同時に、INSERT、UPDATE、DELETEのようなDML操作ログをキャッシュに書き込み、インデックスの作成が完了した後にテーブルに再適用し、データの一貫性を達成することである.このキャッシュのサイズはパラメータinnodb_online_alter_log_max_size制御、デフォルトサイズは128 MBです.
mysql [localhost] {msandbox} ((none)) > show variables like '%online%';
+----------------------------------+-----------+
| Variable_name | Value |
+----------------------------------+-----------+
| innodb_online_alter_log_max_size | 134217728 |
+----------------------------------+-----------+
1 row in set (0.00 sec)
mysql [localhost] {msandbox} ((none)) > select 134217728 / 1024 / 1024;
+-------------------------+
| 134217728 / 1024 / 1024 |
+-------------------------+
| 128.00000000 |
+-------------------------+
1 row in set (0.00 sec)
mysql [localhost] {msandbox} ((none)) >
更新するテーブルが比較的大きく、作成中に大量の書き込みトランザクションがある場合はinnodb_online_alter_log_max_sizeのスペースにログを保存できない場合は、対応するエラーが投げ出されます.これは後でテストします.変更エラーが発生した場合、パラメータを大きくして、より大きなログキャッシュ領域を得ることができます.さらに、実行中に書き込みトランザクションが発生しないように、ALTER TABLEのモードをSHAREに設定できます.そのためDMLログの記録は不要です.
上の簡単な説明を通じて、みんなの心の中に譜面があると信じています.では、実際にテストしてみましょう.ここでsysbenchを使用して1000 w行のテストデータを生成します.
[root@mysql-server ~]# sysbench --test=oltp --oltp-table-size=10000000 --oltp-read-only=off --init-rng=on --num-threads=16 --max-requests=0 --oltp-dist-type=uniform --max-time=1800 --mysql-user=msandbox --mysql-socket=/tmp/mysql_sandbox5619.sock --mysql-password=msandbox --db-driver=mysql --mysql-table-engine=innodb --oltp-test-mode=complex prepare
1.最初に補助インデックスを追加するテスト
セッション1でインデックスの追加操作を実行し、セッション2でDML操作を実行する.
session 1(alter tableはデフォルトの実行方法を選択し、innodbストレージエンジンにどのロックを追加すべきかを自分で判断させる)
mysql [localhost] {msandbox} (sbtest) > select count(*) from sbtest;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (2.28 sec)
mysql [localhost] {msandbox} (sbtest) > show create table sbtest\G
*************************** 1. row ***************************
Table: sbtest
Create Table: CREATE TABLE `sbtest` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`k` int(10) unsigned NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=10000001 DEFAULT CHARSET=latin1
1 row in set (0.04 sec)
mysql [localhost] {msandbox} (sbtest) >
mysql [localhost] {msandbox} (sbtest) > alter table sbtest add key idx_pad ( pad );
session 2(時計がロックされていないことがわかり、すべて正常です)
mysql [localhost] {msandbox} (sbtest) > delete from sbtest where id=10;
Query OK, 1 row affected (0.16 sec)
mysql [localhost] {msandbox} (sbtest) > show processlist;
+----+----------+-----------+--------+---------+------+----------------+--------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+----------+-----------+--------+---------+------+----------------+--------------------------------------------+
| 17 | msandbox | localhost | sbtest | Query | 4 | altering table | alter table sbtest add key idx_pad ( pad ) |
| 18 | msandbox | localhost | sbtest | Query | 0 | init | show processlist |
+----+----------+-----------+--------+---------+------+----------------+--------------------------------------------+
2 rows in set (0.00 sec)
mysql [localhost] {msandbox} (sbtest) > update sbtest set k=11 where id=100;
Query OK, 1 row affected (1.20 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql [localhost] {msandbox} (sbtest) > show processlist;
+----+----------+-----------+--------+---------+------+----------------+--------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+----------+-----------+--------+---------+------+----------------+--------------------------------------------+
| 17 | msandbox | localhost | sbtest | Query | 53 | altering table | alter table sbtest add key idx_pad ( pad ) |
| 18 | msandbox | localhost | sbtest | Query | 0 | init | show processlist |
+----+----------+-----------+--------+---------+------+----------------+--------------------------------------------+
2 rows in set (0.26 sec)
mysql [localhost] {msandbox} (sbtest) >
2.テストフィールドsession 1を追加
mysql [localhost] {msandbox} (sbtest) > alter table sbtest add age int after pad;
session 2
mysql [localhost] {msandbox} (sbtest) > delete from sbtest where id=20;
Query OK, 1 row affected (1.02 sec)
mysql [localhost] {msandbox} (sbtest) > update sbtest set k=101 where id=1111;
Query OK, 1 row affected (1.10 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql [localhost] {msandbox} (sbtest) > show processlist;
+----+----------+-----------+--------+---------+------+----------------+------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+----------+-----------+--------+---------+------+----------------+------------------------------------------+
| 23 | msandbox | localhost | sbtest | Query | 120 | altering table | alter table sbtest add age int after pad |
| 24 | msandbox | localhost | sbtest | Query | 1 | init | show processlist |
+----+----------+-----------+--------+---------+------+----------------+------------------------------------------+
2 rows in set (0.38 sec)
mysql [localhost] {msandbox} (sbtest) >
フィールドを追加してもDML操作には影響しないことがわかります.爽やかじゃない?爽やかならアップグレードしましょう.mysql 5.5にフィールドを追加したらどうなりますか?mysql 5.5にフィールドを追加すると、テーブルがロックされ、読み書きがブロックされます(増加し、インデックスを削除するとSロックされ、書き込みがブロックされます).まだmysql 5.6を使用していない学生も心配しないでください.oak-online-alter-tableとpt-online-schema-changeの2つのツールが非常に使いやすいからです.mysql 5.5にフィールドを追加する場合を見てみましょう.
mysql> select version();
+------------+
| version() |
+------------+
| 5.5.37-log |
+------------+
1 row in set (0.03 sec)
mysql>
mysql> alter table sbtest add address char(30) after pad;
別のセッションの表示
mysql> show processlist;
+----+------+-----------+--------+---------+------+---------------------------------+---------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+--------+---------+------+---------------------------------+---------------------------------------------------+
| 9 | root | localhost | sbtest | Query | 6 | copy to tmp table | alter table sbtest add address char(30) after pad |
| 10 | root | localhost | sbtest | Query | 4 | Waiting for table metadata lock | delete from sbtest where id=100 |
| 11 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+----+------+-----------+--------+---------+------+---------------------------------+---------------------------------------------------+
3 rows in set (0.00 sec)
mysql>
ロックテーブルが表示され、一時テーブルが作成されています.
ただし、MySQL 5.6は必ずしもテーブルをロックしないわけではありません.大きな結果のクエリがテーブルをクエリーしている場合、ALTER TABLEを実行するとテーブルがロックされます.簡単なテストをします.
session 1
mysql [localhost] {msandbox} (sbtest) > select * from sbtest;
session 2
mysql [localhost] {msandbox} (sbtest) > alter table sbtest add age int after pad;
session 3
mysql [localhost] {msandbox} (sbtest) > show processlist;
+----+----------+-----------+--------+---------+------+---------------------------------+------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+----------+-----------+--------+---------+------+---------------------------------+------------------------------------------+
| 27 | msandbox | localhost | sbtest | Query | 5 | Sending data | select * from sbtest |
| 28 | msandbox | localhost | sbtest | Query | 3 | Waiting for table metadata lock | alter table sbtest add age int after pad |
| 29 | msandbox | localhost | sbtest | Query | 0 | init | show processlist |
+----+----------+-----------+--------+---------+------+---------------------------------+------------------------------------------+
3 rows in set (0.22 sec)
mysql [localhost] {msandbox} (sbtest) >
ロックテーブルが発生しているのが見えますね.したがって、オンラインになるときは、スローSQLまたは比較的大きな結果セットのSQLが実行されているかどうかを確認する必要があります.そうしないと、ALTER TABLEを実行するときにロックテーブルが発生します.もちろんoak-online-alter-tableとpt-online-schema-changeにこの制限があるかどうかは分かりません.時間を割くにはテストが必要です.
参考資料:http://www.cnblogs.com/gomysql/p/3776192.html http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html
http://www.mysqlperformanceblog.com/2014/02/26/monitor-alter-table-progress-innodb_file_per_table/
『MySQL技術内幕--innodbストレージエンジン第2版』