高性能MySqlの進化(1)：データ型の最適化_前編

データベースのパフォーマンスチューニングのプロセスには、フィールド属性の設定が適切かどうか、インデックスの確立が適切かどうか、テーブル構造が適切かどうか、データベース/オペレーティングシステムの設定が正しいかどうかなど、多くの知識が必要になります。それぞれのトピック 各トピックはフィールドである場合があります。

私の意見では、データベースのパフォーマンスを向上させるための主要なテクノロジーの中で、フィールドの最適化は比較的難しく、パフォーマンスに非常に大きな影響を与えます。 MySQL は多くのデータ型をサポートしており、それぞれの型に独自の特性があるため、特定のデータ型を選択するときに、その型が最適であるかどうかを考慮せずに、使用可能な型を選択することがよくあります。具体的な型について説明する前に、まずデータ型を選択するための主な原則をいくつか見てみましょう:

a) 占有スペースが小さい型を選択するようにしてください
小さい型は、ディスク上とメモリ内で占有するスペースが少ないためです。クエリや並べ替えを行う場合、一時テーブルに必要なスペースは比較的小さくなります。データ量が比較的少ない場合には感じられないかもしれませんが、データ量が比較的多い場合には、この原則の重要性が明らかになることがあります。

たとえば、2,000 万件のレコードがある「製品情報」テーブルがあります。このテーブルには、「残りの製品数量」(COUNT) フィールドがあります。一般的には、SMALLINT (len: 16、範囲: 0-65535) です。このフィールドを表現するにはこれで十分ですが、設計プロセス中に BIGINT (len: 64 range: 0-18446744073709551615) を使用して表現すると、プログラムは正しく実行されるかもしれませんが、このフィールドは約 95M のディスク ストレージ容量 (64-. 16)/8*20,000,000 バイト) さらに、データの選択と並べ替えを行うと、このフィールドだけでメモリ消費量が 95M 増加し、データベースのパフォーマンスに必然的に影響します。

ここでできる限り小さくするという前提は、選択しようとしているタイプが将来のビジネス開発のニーズを確実に満たせるようにするためです。データ量が比較的少ない場合、テーブル構造の更新は非常に遅くて面倒な作業であるためです。大きい。

b) 単純/適切なタイプを選択するようにしてください

テーブルを選択して並べ替える場合、単純なタイプの方が消費する CPU クロック サイクルが少なくなることがよくあります。たとえば、MySql サーバーの場合、整数型の値の比較は文字列型の値の比較よりも簡単で高速であることが多いため、特定のテーブルを並べ替える必要がある場合は、並べ替えの基準として整数型を選択するようにしてください

c) フィールドを NOTNULL に設定してみてください

一般に、フィールドを明示的に NULL として指定しない場合、このフィールドはデータベース システムによって NULLABLE とみなされます。このシステムのデフォルト動作により、次の 3 つの問題が発生します。問題点
(1) Mysql サーバー自体のクエリ最適化機能が影響を受けます
(2) Mysql は追加のストレージ領域と null 値を持つフィールドの処理を必要とします
(3) null 値がインデックスの一部である場合、インデックスも影響を受けます

この原則はデータベースのパフォーマンスの向上に大きな役割を果たさないため、NULLABLE フィールドまたはインデックスが NULLABLE である場合、特に既存の DB スキーマを変更する必要はありません。ただし、新しく設計された DB やインデックスは可能な限りこの原則に従う必要があります。

データ型選択の原則を紹介した後、Mysql の一般的なデータ型と、パフォーマンスの最適化の観点から何に注意する必要があるかを紹介します。

・整数

Mysql の整数ファミリーのメンバーには、主に TINYINT(8 ビット)、SMALLINT(16 ビット)、MEDIUMINT(24 ビット)、INT(32 ビット)、または BIGINT(64 ビット) が含まれます。

符号付き整数の場合、これらの型の格納範囲は (-2(n-1), 2(n-1)-1) で、符号なし数値の場合、式の範囲は (0,2n-1) です。データベース、符号付き数値と符号なし数値は同じ記憶領域を占有するため、型を選択するときは、符号付きか符号なしかを考慮せず、数値の範囲のみを考慮することができます

MySQL では、整数を定義するときにそれを指定できますtype INT(10) などの幅。クライアント/CMD ラインの INT(10) の出力には違いがありますが、Mysql サーバーの観点から見ると、実際のストレージ容量/計算消費量の点では INT(10) と INT(32) の間に違いはありません。数値の範囲。 m

y y

🎜🎜 🎜🎜 のデータ型には主に Float (4BYTES)、Double (8bytes) があり、これら 2 つの型の記憶領域から必要な場合を除いて、避けるようにする必要があります。 10 進数タイプの使用🎜🎜10 進数タイプのフィールドを作成する場合、FLOAT(10,3) を使用して小数点の精度を指定できます。>=Mysql 5.0 でサポートされる最大精度は、小数点以下 65 桁までです。 🎜🎜データベースはバイナリ配列文字列を使用して小数点以下の数値を保存するため、必要な精度が高くなるほど、ストレージ容量/計算CPUクロックの消費量が増加する可能性があります。 🎜

小数を使用すると、より多くのストレージ領域と CPU リソースが消費される可能性があり、初期の Mysql バージョンでは、計算に小数 2 桁が含まれると精度が失われますが、金融分野での金額の保存など、多くの場合に必要です。多くの場合、ストレージのオーバーヘッドを削減し、精度を確保するために、小数点は整数に拡張されてデータベースに保存され、アプリケーション内で小数点が変換されて計算されます。たとえば、ユーザーのアカウント残高が 999.35 元のままである場合、その小数点は 999.35 元のままです。データに保存されている金額は 99935 ポイントです。銀行の処理プログラムは、99935 ポイントを取得した後、まずそれを 999.35 元に変換し、その後、対応する処理を実行します。一般に、この規則は比較的重要で複雑なタイプです。 MYSQL: MYSQL には主に VARCHAR と CHAR という 2 つの文字列タイプがあり、これらはストレージ エンジンによって決定され、ストレージ エンジンごとに異なる格納方法が使用されます。一般に、ストレージ エンジンの場合、ディスクとメモリの保存方法も異なります。ディスクとメモリ間でデータが転送される場合、ストレージ エンジンはデータの変換を担当します

VARCHAR

まず、それを指定する必要があります。 out MySQL は可変長を使用して VARCHAR を格納します。この方法は、固定長と比較して、「必要なだけ使用する」ストレージ スペース戦略を採用しています。この場合、特別なニーズがない場合は比較的スペースを節約できます。 、デフォルトの型として使用できます

VARCHAR が固定長を実現できる理由は、各 VARCHAR 値に 1 ～ 2 バイトの長さのインジケーターが追加されるためです。たとえば、「I Love Java」を保存する必要がある場合、基礎となる格納コンテンツは「11I Love Java」です。11 (1 バイト) は長さを表します。格納されるコンテンツの長さが 1000 の場合、長さインジケーターには 2 バイトが必要です。 2 バイトの最大値は 216 であるため、格納される文字列がこの長さを超えると、予期しない例外が発生します。この場合、このような超長い文字列を格納するには CLOB を使用する必要があります。

MYSQL のバージョンが異なると、VARCHAR フィールドの末尾のスペースの処理も異なります
バージョン>=5.0 末尾のスペースを保持します
バージョン
MYSQL 5.6 を例に挙げます:

VARCHAR(5) と VARCHAR(200) を使用して「hello」を格納する場合のスペース オーバーヘッドは同じです。では、短い列を使用することには何か利点があるのでしょうか?

それは大きな利点があることがわかりました。 MySQL は通常、内部値を保持するために固定サイズのメモリ ブロックを割り当てるため、カラムが大きくなると、より多くのメモリが消費されます。これは、並べ替えや操作にメモリ内の一時テーブルを使用する場合に特に問題になります。ディスク一時テーブルを使用して並べ替える場合も同様に問題が発生します。

したがって、最善の戦略は、本当に必要なスペースのみを割り当てることです。

CHAR

CHAR 型と VARCHAR 型の最大の違いは、固定長であることです。同時に、VARCHAR と比較すると、主に次の特徴があります

1) すべての MYSQL バージョンで、末尾のスペースは切り捨てられます

2) 基本的に同じ長さのいくつかの短いフィールドに適した選択肢です、MD5 、ID 番号など
3) 頻繁に変更が必要なフィールドの場合、CHAR 型の方が効率的です
4) 一部の超短いフィールドの場合は、非常にスペースも節約されます。たとえば、「Y」または「N」を保存する場合、CHAR の使用には 1 バイトのみが必要ですが、VARCHAR の使用には 2 バイト (1 バイトの長さ + 1 バイトの値) が必要です

固定長 CHAR の場合、Mysql サーバーは次に従ってそれを使用します。十分な記憶領域を割り当てるために、定義された長さにはスペースが埋め込まれます。注意すべき点は、VARCHAR/CHAR の「スペースの充填」と「末尾のスペースの削除」の操作は MySQL サーバーによって実装されており、ストレージ エンジンとは何の関係もないことです

上記は、高パフォーマンスの MySql の進化です ( 1): データ型 _ のコンテンツを最適化します。その他の関連コンテンツについては、PHP 中国語 Web サイト (www.php.cn) に注目してください。