ホームページ > 記事 > データベース > MySQL でのデータ型とスキーマの最適化

MySQL でのデータ型とスキーマの最適化

coldplay.xixi転載: 2020-10-26 17:58:092484ブラウズ

最近Mysql 教程この項目では、データ型とスキーマの側面の知識を紹介します。

1.強化されたデータ型の選択

MySQL でサポートされているデータ型は多数ありますが、適切なデータ型をどのように選択するかがパフォーマンスにとって重要です。タイプ:

# 通常はより小さい
# データを正しく保存できる最小のデータタイプを使用することをお勧めします。これにより、占有される磁場、内部記憶、および記憶がより少なくなり、処理時間もより短くなります。整形型と文字列の両方がいずれかのフィールドの記憶動作に対応できる場合、整形型は文字列よりも演算コストが小さいため、通常、どちらも最適な選択となります。その間を選択する場合は、通常、整形型を使用することでより優れたパフォーマンスが得られます。 MySQL の場合、インデックスや値比などの点でさらに多くの処理が必要ですが、パフォーマンスへの影響は大きくありませんが、NULL を可能な限り回避するように設計されています。 #上記の原則に加えて、データの種類を選択するときは、次の手順に従う必要があります。
ステップ
: 最初に、データ、文字列、時間などの適切な大種類を決定し、次に特定の種類を再度選択します。以下の具体的なタイプは、まず数字で、整数と数値の 2 つのタイプがあります。
空间大小（ビット）
8
#SMALLINT
16

MEDIUMINT

INT 3264

BIGINT

整数型が格納できる範囲は、スペースサイズに関連しています: -2^(N-1) ～ 2^(N-1)-1 (N はスペースサイズの桁数)。

整数型にはオプションの属性 UNSIGNED があります。これを宣言すると、負の数値は許可されず、格納範囲は 0 ～ 2^(N)-1 (2 倍) になります。

MySQL では、INT(1) などの整数型の幅を指定することもできますが、これはあまり重要ではなく、値の正当な範囲を制限するものではありません。 31 ～ 2 ^31-1 の値は、MySQL との対話型ツールによって表示される文字数に影響します。

1.2 実数型

実数型の比較は以下のとおりです。

#精度に関する#上記のことからわかるように、FLOAT と DOUBLE は両方とも固定スペースサイズを持ちますが、同時に標準の浮動小数点演算を使用するため、近似的にしか計算できません。 DECIMAL は正確な計算を実現できますが、同時により多くのスペースを消費し、より多くの計算オーバーヘッドを消費します。

実数型	スペースサイズ (バイト)	値の範囲	計算精度
FLOAT	4	負の数値: -3.4E 38~-1.17E-38; 非負の数値: 0、1.17E-38〜3.4E 38	概算計算
DOUBLE	8	負の数値: -1.79E 308~-2.22E-308; 負でない数値: 0、2.22E-308~1.79E 308	近似計算
DECIMAL	#DOUBLEと同じ	#正確な計算

DECIMAL が占めるスペースは、指定された精度に関連します。たとえば、DECIMAL(M,D):

M は数値全体の最大長であり、値は範囲は [1, 65 ]、デフォルト値は 10、

MySQL が DECIMAL 型を保存する場合、それはバイナリ文字列として保存されます。4 バイトごとに 9 つの数値が保存されます。9 桁未満の場合、数値が占めるスペースは次のとおりです。

桁数占有スペース (バイト)1、2 123 4小数点は別に保存され、小数点はも1バイトを占有します。以下に 2 つの計算例を示します。


	##3、4
	5、6
	7, 8

DECIMAL(18, 9): 整数部分の長さは 9 で、4 バイトを占めます。小数部の長さは 9 で、4 バイトを占めます。同時に小数点用の 1 バイトを追加し、合計 9 バイトを占有します。

DECIMAL(20, 9): 整数部分の長さは 14 で、7 (4 3) バイトを占めます。小数部の長さは 9 で、4 バイトを占めます。同時に小数点用の 1 バイトを追加し、合計 12 バイトを占有します。

DECIMAL は依然として多くのスペースを占有するため、DECIMAL は小数の正確な計算が必要な場合にのみ必要であることがわかります。さらに、DECIMAL の代わりに BIGINT を使用することもできます。たとえば、小数点以下 5 桁の計算を保証する必要がある場合、値に 10 の 5 乗を乗算して BIGINT として保存できます。これにより、不正確な浮動小数点ストレージ計算と DECIMAL を同時に実行する、まさに計算コストのかかる問題です。
1.3 文字列型

最も一般的に使用される文字列型は、VARCHAR と CHAR です。

VARCHAR

可変長文字列

として、文字列の長さを記録するために追加の 1 バイトまたは 2 バイトが使用されます。最大長が 255 を超えない場合、1 バイトのみが記録されます。長さが255を超える場合は2バイト必要です。 VARCHAR に適用可能なシナリオ:最大長は平均長よりも大幅に長くなります;

断片化を避けるために列の更新頻度が低くなります;

UTF-8 などの文字セットを複雑に使用し、各文字を異なるバイトを使用して格納できます。
CHAR

固定長文字列

です。定義された文字列長に従って、十分なスペースが割り当てられます。 適用可能なシナリオ : は長さが短く、

# は同様の長さ (MD5 など)、

# は頻繁に更新されます。
大きな文字列を格納するには、VARCHAR と CHAR に加えて、BLOB と TEXT タイプを使用できます。 BLOB と TEXT の違いは、
BLOB

binary

形式で保存されるのに対し、TEXT は character 形式で保存されることです。このことは、BLOB 型のデータには文字セットの概念がないため、文字によるソートができませんが、TEXT 型のデータには文字セットの概念があり、文字によるソートが可能であるという事実にもつながります。両者の利用シーンは保存形式によっても決まり、写真などのバイナリデータを保存する場合はBLOB型、記事などのテキストを保存する場合はTEXT型を使用します。 1.4 日付と時刻のタイプMySQL に保存できる最小時間粒度は秒です。一般的に使用される日付タイプには、DATETIME と TIMESTAMP があります。

タイプストレージコンテンツDATETIMEYYYYMMDDHHMMSS1970 年 1 月 1 日の午前 0 時からの秒数

スペースサイズ(バイト)

タイムゾーンの概念

None

形式の整数#TIMESTAMP

TIMESTAMP によって表示される値はタイムゾーンによって異なります。つまり、タイムゾーンが異なるとクエリされる値も異なります。上記の違いに加えて、TIMESTAMP には特別な属性もあり、挿入および更新中に最初の TIMESTAMP 列の値が指定されていない場合、この列の値は現在時刻に設定されます。

開発プロセスでは TIMESTAMP を使用するようにしてください。主な理由は、スペースサイズが DATETIME の半分に過ぎず、スペース効率が高いためです。

秒単位で正確な日付と時刻を保存したい場合はどうすればよいでしょうか? MySQL にはそれが提供されていないため、BIGINT を使用してマイクロレベルのタイムスタンプを保存するか、DOUBLE を使用して秒後の小数部分を保存できます。

1.5 識別子の選択

一般に、整数は単純で計算が速く、AUTO_INCREMENT を使用できるため、識別子としては最適な選択です。

2. パラダイムとアンチパラダイム

簡単に言えば、パラダイムとは、データテーブルのテーブル構造が準拠する特定の設計標準のレベルです。第 1 正規形では属性は分離不可能であり、現在の RDBMS システムで構築されるテーブルはすべて第 1 正規形に準拠しています。第 2 正規形は、コード (主キーとして理解できる) に対する非主属性の部分的な依存を排除します。第 3 正規形は、コードに対する非主属性の推移的な依存を排除します。具体的な紹介については、Zhihu でこの回答を読むことができます (https://www.zhihu.com/question/24696366/answer/29189700)

StrictNormalized データベース、各ファクトデータ#データの冗長性はありません

これにより、次のような利点が得られます:

更新操作の高速化、
変更するデータの削減。
テーブルが小さくなり、メモリに収まりやすくなり、操作が高速に実行されます。
DISTINCT または GROUP BY の必要性が少なくなります。

ただし、データはさまざまなテーブルに分散しているため、クエリを実行するときにテーブルを関連付ける必要があります。 アンチパラダイムの利点は、を関連付ける必要がなく、データが冗長的に保存されることです。

実際のアプリケーションでは、完全な正規化や完全な非正規化は発生しません。多くの場合、パラダイムと非正規化を混合する必要があります。。多くの場合、部分的に正規化されたスキーマを使用することが最善です。 ■選択。データベース設計に関しては、インターネットでこの文章を目にしましたが、それを実感できます。

データベース設計は 3 つの領域に分割する必要があります:

最初の領域: データベース設計を始めたばかりで、パラダイムの重要性はまだ深く理解されていません。このときに現れるアンチパラダイムのデザインは、一般的に問題を引き起こします。

2 番目の領域: 問題に遭遇して解決すると、パラダイムの本当の利点が徐々に理解できるようになり、冗長性が低く、効率の高いデータベースを迅速に設計できるようになります。

第 3 の領域: N 年間のトレーニングを経ると、パラダイムの限界が必ずわかります。このとき、パラダイムを壊し、より合理的なアンチパラダイム部分を設計します。

パラダイムは武道の動きのようなもので、初心者がその動きに従わないと恥をかいて死ぬだけです。結局のところ、トリックはマスターによって要約されたエッセンスです。武道が上達し、動きに習熟すると、必ずその動きの限界に気づき、それを忘れるか、独自の動きを生み出すことになります。

一生懸命働いてあと数年耐えさえすれば、いつでも 2 番目の状態に到達でき、そのパラダイムが古典であると常に感じるでしょう。このとき、パラダイムに頼りすぎず、パラダイムの限界を素早く打ち破ることができる人が、当然エキスパートとなります。

4. キャッシュテーブルとサマリーテーブル

上記のアンチパラダイムと冗長データをテーブルに保存することに加えて、完全に独立したサマリーテーブルまたはキャッシュを作成することもできます。検索のニーズを満たすテーブル。

キャッシュテーブルは、スキーマ内の他のテーブルから取得できるデータ、つまり論理的に冗長なデータを格納するテーブルを指します。 サマリーテーブルは、GROUP BY およびその他のステートメントを使用してデータを集計することによって計算された非冗長データのストレージを指します。

キャッシュテーブルは、 検索および取得クエリステートメントを最適化するために使用できます。ここで使用できるテクニックには、キャッシュテーブルにさまざまなストレージエンジンを使用することが含まれます。たとえば、メインテーブルは InnoDB を使用します。一方、キャッシュテーブルは MyISAM を使用してインデックスフットプリントを小さくできます。キャッシュテーブルを Lucene などの特殊な検索システムに配置することもできます。

サマリーテーブルは、統計値をリアルタイムで計算するための高コストを回避するためのものです。コストは 2 つの側面から発生します。1 つは、テーブル内のほとんどのデータが必要であることです。もう 1 つは、特定のインデックスを作成することです。インデックスは UPDATE 操作に影響します。たとえば、過去 24 時間の WeChat モーメントの数をクエリするには、テーブル全体を 1 時間ごとにスキャンし、統計の後にレコードを概要テーブルに書き込むことができます。クエリを実行する場合は、概要の最新の 24 レコードをクエリするだけで済みます。すべてではなくテーブル各クエリ中に、テーブル全体が統計のためにスキャンされます。

キャッシュテーブルとサマリーテーブルを使用する場合、ニーズに応じて データをリアルタイムで維持するか それとも 定期的に再構築する かを決定する必要があります。リアルタイムのメンテナンスと比較して、定期的な再構築により、より多くのリソースが節約され、テーブルの断片化が少なくなります。再構築中も、操作中にデータが利用可能であることを確認する必要があり、これは「shadow table」を通じて実現する必要があります。実テーブルの背後にシャドウテーブルを作成し、データを入力した後、アトミックな名前変更操作によってシャドウテーブルと元のテーブルを切り替えます。

5. ALTER TABLE オペレーションの高速化

MySQL が ALTER TABLE オペレーションを実行するとき、多くの場合、新しいテーブルを作成し、古いテーブルからデータを取得して新しいテーブルに挿入します。古いテーブル: テーブルが大きい場合、これには長い時間がかかり、MySQL サービスの中断が発生します。サービスの中断を避けるために、通常は 2 つの手法 :

サービスを提供しないマシンで ALTER TABLE 操作を実行し、メインライブラリと通信します。サービスを提供する Switch;
「シャドウコピー」では、元のテーブルとは関係のない新しいテーブルを作成し、データ移行の完了後に名前変更操作を実行します。

ただし、すべての ALTER TABLE 操作でテーブルが再構築されるわけではありません。たとえば、フィールドのデフォルト値を変更する場合、MODIFY COLUMN を使用するとテーブルが再構築され、ALTER COLUMN を使用するとテーブルが再構築されます。 will テーブルの再構築は実行されず、操作は非常に高速です。これは、ALTER COLUMN がデフォルト値を変更する場合、テーブルを再構築せずに、既存のテーブル (フィールドのデフォルト値を格納する) の .frm ファイルを直接変更するためです。

#その他の関連する無料学習の推奨事項: mysql チュートリアル(ビデオ)

以上がMySQL でのデータ型とスキーマの最適化の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。

声明：

この記事はjuejin.imで複製されています。侵害がある場合は、admin@php.cn までご連絡ください。

前の記事：Mysqlはインデックスに頼れるけど、バイトに頼るしかない…。次の記事：Mysqlはインデックスに頼れるけど、バイトに頼るしかない…。

続きを見る