MySQL データ行オーバーフローについての深い理解

この記事は、MySQL のデータ行オーバーフローについて詳しく説明しています。必要な方は参考にしていただければ幸いです。

1. 一般的なエラーから始めましょう

一般的な SQL エラー メッセージから始めましょう:

特に OMG のようなコンテンツ制作を主な仕事としている BG では、コンテンツ行の保存に大きなデータが必要になるため、この種のエラーに何度も遭遇したことがあると思います。避けられない話題。ここでのデータの「サイズ」には、多くのストレージ領域を占有するだけではなく、単一 (テーブル) フィールドの大きなストレージ領域、長いデータ保持時間、大規模なデータの冗長性、ホット データの不整合によって引き起こされる大量のデータも含まれます。ホットスポットによりピーク値が大幅に変化し、複雑な論理処理によりデータストレージの負荷が増大するなど。エラーの問題に戻り、まずこのテーブルの構造を見てみましょう。

これを見ると、人それぞれ異なる意見があると思います。ここでは、さまざまな処理方法の長所と短所を比較しません。より頻繁に使用される 2 つの処理方法についてのみ説明します。

• エラー ガイダンスに従って、2 つの大きな varchar (22288) を text と blob に変更します。

• ビジネスの特性に従って、 varchar のサイズ ストレージの長さ、またはルールに従って複数の小さな vachar と char

に分割する これら 2 つの処理方法にも、フィールドをテキストまたは BLOB に変更するという利点と欠点があります。データ ストレージ容量の増加により、このフィールドのインデックス ページにはプレフィックスまたはフルテキスト インデックスのみが使用できるようになります。ビジネス側がデータを json 形式で保存する場合、5.7 で json データをサポートすることは良い選択です。タイプ。単一のサブカテゴリに対してクエリを実行して出力できます。同様に、削減および分割する場合は、ビジネス シナリオと論理要件にさらに依存し、ビジネスで使用されるロジックを変更する必要があり、エンジニアリングの量も評価する必要があります。

2. 詳細な検討

次に、制限サイズ「65535」に関するいくつかのわかりにくい概念を詳しく分析してみましょう。

1. 「65535」は、単一の varchar(N) の N の最大値ではなく、テーブル全体の非ラージ フィールド タイプのフィールドの合計バイト数です。

---------------------------------------- - ------------------------------------------------- - -

すべてのテーブル (ストレージ エンジンに関係なく) の最大行サイズは 65,535 バイトです。ストレージ エンジンでは、この制限に追加の制約が設定され、実質的な最大行サイズが減少する場合があります。

-- ------------------------------------------------- - --------------------------------------------------

2. 異なる文字セットは、フィールドの最大記憶容量に影響します。たとえば、UTF8 文字は、VARCHAR (255) CHARACTER SET UTF8 列の場合、255 バイトを占有します。 3 = 765 バイト。したがって、テーブルには 65,535/765=85 を超える列を含めることはできません。 GBK は全角などです。

3. 可変長列の場合、フィールド サイズを評価するときは、列の実際の長さを格納するバイト数も考慮する必要があります。たとえば、VARCHAR (255) CHARACTER SET UTF8 列には、値の長さ情報を格納するためにさらに 2 バイトが必要であるため、実際には、この列には最大 65533 バイトを格納でき、残りの 2 バイトを格納できます。バイトには長さの情報が格納されます。

4. BLOB、TEXT、および JSON 列は、varchar や char などのフィールドとは異なり、列の長さの情報は行の長さとは無関係に格納され、

5. NULL 列を定義すると、許可される列の最大数が減ります。

• InnoDB テーブル、NULL 列と NOT NULL 列のストレージ サイズは同じです

• MyISAM テーブル、NULL 列には、その値がどうかを記録するための追加スペースが必要です無効である。各 NULL には追加ビットが 1 つ必要です (最も近いバイトに丸められます)。最大行長は次のように計算されます:

行の長さ = 1 (列の長さの合計) (NULL 列の数 delete_flag 7)/8 (可変長列の数)

• 静的テーブル、delete_flag = 1、静的テーブルは行が削除されたかどうかを識別するために行にビットを記録します。

• #動的テーブル、delete_flag = 0、このフラグは動的行の先頭に格納されます。動的テーブルは

# に従って指定できます。 #6. InnoDB テーブルの場合、NULL 列と NOT NULL 列のストレージ サイズは同じです。

7. InnoDB では、1 つのテーブルに最大 1,000 個の列を含めることができます。

8. 767 バイト、または 768/2=384 ダブル バイト、または 767/3=255 3 バイト フィールドを超える場合、GBK は 2 バイト、UTF8 は 3 バイトです。

9 エンジンごとに異なるインデックス制限があります。

• innodb の各列の長さは 767 バイトを超えることはできず、すべてのインデックス列の長さの合計は 3072 バイトを超えることはできません。 myisam 各列の長さは 1000 バイトを超えることはできません。すべてのインデックス列の長さの合計は 1000 バイトを超えることはできません

3. 本当の障害

オンライン業界で次のようなエラーが多数発生し、プログラムの書き込みができなくなった問題について話しましょう。データ:

プロンプトと通常の考え方によれば、私たちの最初の反応は、ビジネスには次の問題があるということです:

• 設定されたテーブル構造のフィールドが制限を超えています

• 特定のフィールドに挿入されたデータ長が、変更されたフィールドに設定されている最大値を超えています

##次を確認してください ビジネスのデータベース テーブル構造は次のとおりです:

最初の理由はすぐに解消されました。テーブルの作成時にエラーが報告されなかった場合、テーブル内の大きくないフィールドの合計が 65535 の場合、テーブルの作成時にエラーが発生し、ビジネスは書き込み時およびライブラリ テーブルを通じてのみエラーを報告します。この構造では、多数の中ブロブ タイプのフィールドが存在し、大きくないフィールドの合計が 65535 よりもはるかに小さいこともわかります。

その後、企業が提供する特定の SQL によれば、appversion、datadata、elt_stamp、および id のそれほど大きくないフィールドは、mediumblob タイプのフィールドに最大 16M を格納できる制限を超えません。ビジネスデータはこの規模には程遠いのです。エラーメッセージに従って、appversion、datadata、elt_stamp、idなどの大きくないフィールドをblob型に変更しましたが、依然として解決できません。 (以前の分析によれば、それが問題の原因ではないはずです)。

冷静になった後、実際には無視されている別の詳細があることがわかりました。これは、成功したリクエストと失敗した SQL を比較すると、まだ成功したリクエストが存在することを意味します。確かにデータ量に違いがあることがわかりました。そこで最初に気になるのは、max_allowed_pa​​cket パラメータが調整されているかどうかです。はい、1 つのリクエストがサイズを超えているため、設定されたサイズが 1G であることを確認しました。 SQL のデータ長はそれほど大きくないため、この理由は除外されます。

これを確認した後、基本的にいくつかの一般的な問題を解決し、別のパラメータの制限を確認します:

innodb_page_size、これデフォルト値は 16K で、1 ページあたり 2 行のデータがあるため、各行には最大 8K データが含まれます。

データ テーブル Row_format がコンパクトであることを確認した後、問題の原因は次のとおりであると推測できます。

innodb のデフォルトのアプローチの保存形式では、各 BLOB フィールドの最初の 864 バイトが保存されます。ページにあるため、BLOB が特定の数を超えると、1 行のサイズが 8k を超えるため、エラーが報告されます。ビジネス ライティングの成功した SQL と失敗した SQL を比較することで、この推論も適用されました。では、この問題をどのように解決すればよいでしょうか。

• ビジネスではテーブルが分割され、大きなフィールドは別のテーブルに格納されます

• Row_format の格納方法を解決して問題を解決します
  単一のビジネステーブルに格納されるアイテムの数は多くなく、ビジネスロジックが分割に適していないため、この問題を Row_format で解決する必要があります。

Barracuda ファイル形式には、圧縮形式と動的形式という 2 つの新しい行レコード形式があります。この 2 つの新しい形式では、BLOB データの格納に完全な行オーバーフロー方式が使用されます。データ ページであり、実際のデータは BLOB ページに格納されます。圧縮行レコード形式のもう 1 つの特徴は、そこに格納されるデータが zlib アルゴリズムを使用して圧縮されることです。

関連する変更操作は比較的単純です:

1、 MySQL グローバル変数の変更:

SET GLOBAL innodb_file_format='Barracuda';

2. 元のテーブルの属性をスムーズに変更します:

ROW_FORMAT=COMPRESSED

4. 学習を続けます

このケースを通じて、詳細な研究に値する 2 つの点を抽出できます:

1。 # #MySQL 5.6 以降、innodb_page_size は Innodb データ ページを 8K、4K に設定でき、デフォルトは 16K です。このパラメータは初期化中に my.cnf に追加する必要があります。テーブルが作成されてから変更されている場合、MySQL の起動時にエラーが報告されます。 では、バージョン 5.6 より前にこの値を変更する必要がある場合はどうすればよいでしょうか?唯一の方法は、ソース コードに何らかの作業を行ってから、MySQL を再構築することです。

UNIV_PAGE_SIZE はデータ ページ サイズです。デフォルトは 16K です。この値は 2 の累乗に設定できます。この値は 4k、8k、16k、32K、64K に設定できます。 UNIV_PAGE_SIZE を同時に変更した後、UNIV_PAGE_SIZE_SHIFT の値は UNIV_PAGE_SIZE の 2 乗となるため、データ ページの設定は次のようになります。

innodb_page_size をさまざまな値に設定した場合の mysql パフォーマンスへの影響について説明します。テストされたテーブルには 1 億レコードが含まれており、ファイル サイズは 30G です。

①読み取りおよび書き込みシナリオ (読み取り 50%、書き込み 50%)

16K、CPU への負荷は小さく、平均は 20%

8K、CPU圧力は 30% ～ 40% ですが、選択スループットは 16K を超えています

②シナリオの読み取り (100% 読み取り)

16K と 8K の違いは明らかではありません

InnoDB バッファ プール管理ページ自体にもコストがかかります。ページ数が増えると、同じサイズの管理リストも長くなります。したがって、データ行自体が比較的長い場合 (大きなブロックの挿入)、より多くの行を 1 ページに入れることができ、各 IO 書き込みのサイズが大きくなり、IOPS が低いほど多くのデータが書き込まれるため、ページが大きいほど速度の向上に役立ちます。 。 行の長さが 8K を超えると、それが 16K ページの場合、一部の文字列型は強制的に TEXT に変換され、文字列の本体が拡張ページに転送されます。これにより、拡張ページを読み取るためにさらに 1 回の IO が必要になります。より大きな行長がサポートされており、64K ページは拡張ページを使用せずに約 32K の行長をサポートできます。 ただし、短いライン長のランダムな読み書きの場合、そのような大きなページの使用は適しておらず、IO 効率が低下し、大きな IO はごく一部しか読み取れません。

2. Row_format について

Innodb ストレージ エンジンはレコードを行の形式で保存します。 InnoDB バージョン 1.0.x より前では、InnoDB ストレージ エンジンは行レコード データを格納するためにコンパクトと冗長の 2 つの形式を提供していました。 MySQL 5.1 の innodb_plugin では、新しいファイル形式 Barracuda が導入されています。これには、圧縮形式と動的形式という 2 つの新しい行形式があります。そしてコンパクトなものと冗長なものを総称してアンテロープと呼びます。コマンド SHOW TABLE STATUS LIKE 'table_name'; を使用すると、現在のテーブルで使用されている行フォーマットを表示できます。row_format 列は、現在使用されている行レコード構造のタイプを示します。

MySQL 5.6 バージョン (デフォルトの Compact)、msyql 5.7.9 以降のバージョンでは、デフォルトの行フォーマットは innodb_default_row_format 変数によって決定され、デフォルト値は DYNAMIC です。テーブルの作成時に ROW_FORMAT=DYNAMIC を指定することもできます。 (これはテーブルの保存形式を動的に調整するために使用できます)。既存のテーブルの行モードを圧縮または動的に変更する場合は、最初にファイル形式を Barracuda に設定する必要があります (set global innodb_file_format=Barracuda;)。次に、ALTER TABLE tablename ROW_FORMAT=COMPRESSED; を使用して変更を有効にします。そうしないと、変更は無効になり、プロンプトは表示されません。

①compact

BLOB 列値の長さ > の場合、最初の 768 バイトはまだデータ ページにあり、残りのバイトはオーバーフロー ページに配置されます。

上記の blob または可変長フィールドの種類には、blob、text、varchar が含まれます。 varchar 列値の長さも特定の数 N より大きくなります。latin1 文字セットでは、N 値は次のように計算できます。 innodb ストレージ エンジンのデフォルトのブロック サイズは 16kb です。テーブルはインデックス構成テーブルであり、ツリーの下部のリーフ ノードは双方向リンク リストであるため、各ページには少なくとも 2 行のレコードが必要です。これにより、innodb は 8,000 行を超えるデータを保存できないことが決まりますから、他の列値が占めるバイト数を引いた値 (N にほぼ等しくなります)。

②圧縮または動的

BLOB に対して完全な行オーバーフローを使用します。つまり、クラスター化インデックス レコード (データ ページ) は、オーバーフロー セグメント アドレスを指す 20 バイトのポインターのみを保持します。実際に格納される場所:

動的行フォーマット。列ストレージがオフページページに配置されるかどうかは、主に行によって異なります。データ ページに次の 2 行を格納できるようになるまで、長い列はオフページに配置されます。 TEXT/BLOB 列は、40 バイト以下の場合、常にデータ ページに格納されます。動的フォーマットでは、大きな列の値の一部がオフページに配置されている限り、全体を配置する方がより効果的であると考えられるため、コンパクトのように B ツリー ノードに大きな列の値を配置しすぎることを回避できます。値をオフページに追加します。

compressed は物理構造においては Dynamic に似ていますが、テーブルのデータ行は zlib アルゴリズムを使用して圧縮され、保存されます。長い BLOB カラムタイプが多い場合に使用すると、オフページの使用を減らし、ストレージ容量を削減できます (約 50%)。前回の「【データベース評価レポート】第 3 回: InnoDB、TokuDB の圧縮パフォーマンス」レポート テストを参照してください。ただし、バッファ プールには圧縮バージョンと非圧縮バージョンの両方のデータが格納されるため、より多くのメモリが必要になります。

最後に、「ハイパフォーマンス MySQL」を参照し、BLOB などの可変長フィールド型の使用についていくつかの提案を行いました。

① 大きなフィールドは InnoDB の多くのスペースを無駄にする可能性があります。たとえば、格納されたフィールド値が行に必要な値より 1 バイトだけ多い場合、残りのバイトを格納するためにページ全体が使用され、ページのスペースの大部分が無駄になります。同様に、32 ページ サイズをわずかに超える値がある場合、実際には 96 ページが使用されます。

②値が長すぎると、インデックスがクエリの WHERE 条件として使用できなくなり、実行が非常に遅くなる可能性があります。 MySQL は WHERE 条件を適用する前にすべてのカラムを読み取る必要があるため、MySQL が InnoDB に大量の拡張ストレージの読み取りを要求し、その後 WHERE 条件をチェックして不要なデータをすべて破棄する可能性があります。

③テーブルには大きなフィールドが多数あり、それらを結合して 1 つの列に格納するのが最善です。各フィールドに独自のページを持たせるよりも、すべての大きなフィールドで拡張記憶域を共有する方が良いでしょう。

④ COMPRESS() を使用して大きなフィールドを圧縮して BLOB として保存するか、MySQL に送信する前にアプリケーションで圧縮すると、スペースを大幅に節約でき、パフォーマンスが向上します。

⑤ 拡張ストレージでは、データが正しいかどうかを確認するために列全体の長さを完全に比較する必要があるため、アダプティブ ハッシュが無効になります。

以上がMySQL データ行オーバーフローについての深い理解の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。