MySQLカーネル月報2015.01-MySQL・虫取り動態・InnoDB自増列繰返し値問題
3670 ワード
問題の再現
まず問題から始めて、このバグを再現します.
ここでmysqlを閉じてmysqlを起動し、データを挿入します.
挿入(2,2)が見えますが、再起動しなければ、同じデータを挿入して得られるはずです(4,2).上記のテストではmysqldが再起動した後、InnoDBストレージエンジンのテーブル自増idが再利用される可能性があることを反映しています.
自己増加idの再利用は、いくつかのシーンで問題が発生します.なお、上記の例を用いて、t 1に履歴テーブルt 1_があるとするhistoryはt 1テーブルの履歴データを格納するために使用され、mysqldが再起動する前にti_historyにはすでに(2,2)というデータがあるかもしれませんが、再起動後に(2,2)を挿入し、新しく挿入した(2,2)が履歴テーブルに移行すると、プライマリ・キー制約に違反します.
原因分析
InnoDB自増列に重複値が発生した理由
表作成時にAUTO_を指定できますINCREMENT値は、指定しない場合はデフォルトで1です.この値は現在の自増列の開始値サイズを表し、新しく挿入したデータが自増列の値を指定していない場合は、自増列の値がこの開始値になります.InnoDBテーブルの場合、この値はファイルに永続しません.メモリに存在します(dict_table_struct.autoinc).では、この値が持続していない以上、なぜ私たちは新しい値を挿入するたびに、show create table t 1がAUTO_を見ますか?INCREMENT値は変化に従う.実はshow create table t 1は直接dict_からtable_struct.Autoincが取得した(ha_innobase::update_create_info).
分かったAUTO_INCREMENTは、リアルタイムメモリに格納されます.さて、mysqld再起動後、どこからAUTO_を入手INCREMENTは?メモリ値が失われたに違いない.実際mysqlはselect max(id)+1 from t 1のような実行を採用する.AUTO_を入手する方法INCREMENT.この方法が自己増加idの繰返しをもたらす原因である.
MyISAM自己付加価値
MyISAMにもこの問題はありますか?MyISAMにはこの問題はありません.myisamはこの値をリアルタイムで保存します.MYIファイル(mi_state_info_write).mysqldが再開するとMYIでAUTO_を読み込むINCREMENT値(mi_state_info_read).したがって,MyISAMテーブルの再起動は自己増加idの重複の問題は生じない.
問題の修正
MyISAM選択AUTO_INCREMENTはリアルタイムで記憶する.MYIファイルヘッダにあります.実はMYIヘッダには他の情報もリアルタイムで保存されます.つまりAUTO_と書きます.INCREMENTはシーケンシャルな動作であり、その性能損失は無視できる.InnoDBテーブルでこの問題を解決するには、2つの方法があります.1)AUTO_INCREMENTの最大値はfrmファイルに永続します.2)AUTO_INCREMENT最大値は、集約インデックスルートページtrx_に永続します.idが存在する位置.1つ目の方法は、ファイルを直接書くのにパフォーマンスが消費されます.これは、シーケンシャルな操作ではなく、追加の操作です.私たちは2つ目の案を採用します.集計インデックス・ルート・ページのヘッダーtrx_に格納する理由id,ヘッダにtrx_を格納するidは、2次インデックスページとinsert bufヘッダーのみ有効(MVCC).集計インデックスルートページヘッダtrx_idという値は使用されず、常に初期値0を維持します.ちょうどこの位置の8バイトは付加価値の値から格納できます.AUTOを更新するたびにINCREMENT値の場合、この値を集計インデックスルートページヘッダーtrx_に変更します.idの位置.この書き込み操作は本物のデータ書き込み操作と同様にwrite-ahead logの原則を守っているが,ここではundo logではなくredo logしか必要としない.AUTOをロールバックする必要がないのでINCREMENTの変化(つまり、ロールバック後の自増列値は保持され、insertロールバックしてもAUTO_INCREMENT値はロールバックされません).
よって、AUTO_INCREMENT値は集計インデックスルートページtrx_に格納されます.idが存在する場所は,実際にはメモリルートページの修正と1つのredo log(量が小さい)が多くなっているが,このredo logの書き込みも非同期であり,既存のトランザクションlogのシーケンシャルな操作といえる.よってAUTO_INCREMENT値は、集約インデックス・ルート・ページに格納されます.このパフォーマンス損失は極めて小さいです.
修復後のパフォーマンス比較では、グローバルパラメータinnodb_を追加しました.autoinc_persistent取値on/off;onはAUTO_INCREMENT値は、集約インデックス・ルート・ページにリアルタイムで格納されます.offは従来方式でメモリのみに格納される.
パフォーマンスの損失が%1以下であることがわかります.
改善
新規パラメータinnodb_autoinc_persistent_interval制御持続化AUTO_INCREMENT値の頻度.例:innodb_autoinc_persistent_interval=100,auto_incrememt_increment=1の場合、すなわちinsertは100回ごとにAUTO_の持続化を制御するINCREMENT値.各永続値は、現在の値+innodb_です.autoinc_persistent_interval.
テストの結論
制限
1 innodb_autoinc_persistent=on, innodb_autoinc_persistent_interval=N>1の場合、N回増分後に集約インデックスルートページに永続化され、各永続値は現在のAUTO_INCREMENT+(N-1)*innodb_autoextend_increment.再起動後に永続化されたAUTOを読み込むINCREMENTの値は大きくなり、無駄になりますが、繰り返しません.innodb_autoinc_persistent_interval=1は毎回持続化しているのでこの問題はありません.
2 innodb_の場合autoinc_persistent=on、頻繁にauto_を設定increment_incrementは、集約インデックス・ルート・ページに永続化する値が不正確になる可能性があります.なぜならinnodb_autoinc_persistent_interval計算はauto_を考慮していませんincrement_increment変化の場合はdict_を参照table_autoinc_update_if_greater.auto_を設定increment_incrementの場合は極めて少なく、無視できます.
注意:innodbを開く必要がある場合はautoinc_persistent、パラメータファイルで指定する必要があります
このようにset global innodb_を指定するとautoinc_persistent=on;再起動後は集計インデックスルートページからAUTO_は読み込まれませんINCREMENT最大値.
質問:InnoDBテーブルについて、select max(id)+1 from t 1でAUTO_を再起動INCREMENT値は、idにインデックスがある場合、この文はインデックスで検索されます.では、これはmysqlがインデックスに含まなければならない理由を説明することができます.インデックスが指定されていない場合は、次のエラーが表示されます.
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a keyそしてmyisamテーブルにもこの要求があり、余計な感じがします.
まず問題から始めて、このバグを再現します.
ここでmysqlを閉じてmysqlを起動し、データを挿入します.
挿入(2,2)が見えますが、再起動しなければ、同じデータを挿入して得られるはずです(4,2).上記のテストではmysqldが再起動した後、InnoDBストレージエンジンのテーブル自増idが再利用される可能性があることを反映しています.
自己増加idの再利用は、いくつかのシーンで問題が発生します.なお、上記の例を用いて、t 1に履歴テーブルt 1_があるとするhistoryはt 1テーブルの履歴データを格納するために使用され、mysqldが再起動する前にti_historyにはすでに(2,2)というデータがあるかもしれませんが、再起動後に(2,2)を挿入し、新しく挿入した(2,2)が履歴テーブルに移行すると、プライマリ・キー制約に違反します.
原因分析
InnoDB自増列に重複値が発生した理由
表作成時にAUTO_を指定できますINCREMENT値は、指定しない場合はデフォルトで1です.この値は現在の自増列の開始値サイズを表し、新しく挿入したデータが自増列の値を指定していない場合は、自増列の値がこの開始値になります.InnoDBテーブルの場合、この値はファイルに永続しません.メモリに存在します(dict_table_struct.autoinc).では、この値が持続していない以上、なぜ私たちは新しい値を挿入するたびに、show create table t 1がAUTO_を見ますか?INCREMENT値は変化に従う.実はshow create table t 1は直接dict_からtable_struct.Autoincが取得した(ha_innobase::update_create_info).
分かったAUTO_INCREMENTは、リアルタイムメモリに格納されます.さて、mysqld再起動後、どこからAUTO_を入手INCREMENTは?メモリ値が失われたに違いない.実際mysqlはselect max(id)+1 from t 1のような実行を採用する.AUTO_を入手する方法INCREMENT.この方法が自己増加idの繰返しをもたらす原因である.
MyISAM自己付加価値
MyISAMにもこの問題はありますか?MyISAMにはこの問題はありません.myisamはこの値をリアルタイムで保存します.MYIファイル(mi_state_info_write).mysqldが再開するとMYIでAUTO_を読み込むINCREMENT値(mi_state_info_read).したがって,MyISAMテーブルの再起動は自己増加idの重複の問題は生じない.
問題の修正
MyISAM選択AUTO_INCREMENTはリアルタイムで記憶する.MYIファイルヘッダにあります.実はMYIヘッダには他の情報もリアルタイムで保存されます.つまりAUTO_と書きます.INCREMENTはシーケンシャルな動作であり、その性能損失は無視できる.InnoDBテーブルでこの問題を解決するには、2つの方法があります.1)AUTO_INCREMENTの最大値はfrmファイルに永続します.2)AUTO_INCREMENT最大値は、集約インデックスルートページtrx_に永続します.idが存在する位置.1つ目の方法は、ファイルを直接書くのにパフォーマンスが消費されます.これは、シーケンシャルな操作ではなく、追加の操作です.私たちは2つ目の案を採用します.集計インデックス・ルート・ページのヘッダーtrx_に格納する理由id,ヘッダにtrx_を格納するidは、2次インデックスページとinsert bufヘッダーのみ有効(MVCC).集計インデックスルートページヘッダtrx_idという値は使用されず、常に初期値0を維持します.ちょうどこの位置の8バイトは付加価値の値から格納できます.AUTOを更新するたびにINCREMENT値の場合、この値を集計インデックスルートページヘッダーtrx_に変更します.idの位置.この書き込み操作は本物のデータ書き込み操作と同様にwrite-ahead logの原則を守っているが,ここではundo logではなくredo logしか必要としない.AUTOをロールバックする必要がないのでINCREMENTの変化(つまり、ロールバック後の自増列値は保持され、insertロールバックしてもAUTO_INCREMENT値はロールバックされません).
よって、AUTO_INCREMENT値は集計インデックスルートページtrx_に格納されます.idが存在する場所は,実際にはメモリルートページの修正と1つのredo log(量が小さい)が多くなっているが,このredo logの書き込みも非同期であり,既存のトランザクションlogのシーケンシャルな操作といえる.よってAUTO_INCREMENT値は、集約インデックス・ルート・ページに格納されます.このパフォーマンス損失は極めて小さいです.
修復後のパフォーマンス比較では、グローバルパラメータinnodb_を追加しました.autoinc_persistent取値on/off;onはAUTO_INCREMENT値は、集約インデックス・ルート・ページにリアルタイムで格納されます.offは従来方式でメモリのみに格納される.
パフォーマンスの損失が%1以下であることがわかります.
改善
新規パラメータinnodb_autoinc_persistent_interval制御持続化AUTO_INCREMENT値の頻度.例:innodb_autoinc_persistent_interval=100,auto_incrememt_increment=1の場合、すなわちinsertは100回ごとにAUTO_の持続化を制御するINCREMENT値.各永続値は、現在の値+innodb_です.autoinc_persistent_interval.
テストの結論
制限
1 innodb_autoinc_persistent=on, innodb_autoinc_persistent_interval=N>1の場合、N回増分後に集約インデックスルートページに永続化され、各永続値は現在のAUTO_INCREMENT+(N-1)*innodb_autoextend_increment.再起動後に永続化されたAUTOを読み込むINCREMENTの値は大きくなり、無駄になりますが、繰り返しません.innodb_autoinc_persistent_interval=1は毎回持続化しているのでこの問題はありません.
2 innodb_の場合autoinc_persistent=on、頻繁にauto_を設定increment_incrementは、集約インデックス・ルート・ページに永続化する値が不正確になる可能性があります.なぜならinnodb_autoinc_persistent_interval計算はauto_を考慮していませんincrement_increment変化の場合はdict_を参照table_autoinc_update_if_greater.auto_を設定increment_incrementの場合は極めて少なく、無視できます.
注意:innodbを開く必要がある場合はautoinc_persistent、パラメータファイルで指定する必要があります
このようにset global innodb_を指定するとautoinc_persistent=on;再起動後は集計インデックスルートページからAUTO_は読み込まれませんINCREMENT最大値.
質問:InnoDBテーブルについて、select max(id)+1 from t 1でAUTO_を再起動INCREMENT値は、idにインデックスがある場合、この文はインデックスで検索されます.では、これはmysqlがインデックスに含まなければならない理由を説明することができます.インデックスが指定されていない場合は、次のエラーが表示されます.
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a keyそしてmyisamテーブルにもこの要求があり、余計な感じがします.