MySQLストレージ時間タイプの選択に関する問題の説明

MySQL では、datetime 型は通常、時間を保存するために使用されますが、現在では多くのシステムで、Unix タイムスタンプを保存するために int も使用されています。これらの違いは何でしょうか?私の要約は次のとおりです。

整数

（1）4バイトのストレージ。INTの長さは4バイトで、データタイムよりもストレージスペースが少なくて済みます。intインデックスのストレージスペースも比較的小さく、ソートとクエリの効率も比較的高いです。

（２）可読性が極めて悪く、データを直感的に見ることができない

タイムスタンプ

（１）４バイトストレージ

（２）値はUTC形式で保存される

（３）タイムゾーン変換：保存時に現在のタイムゾーンに変換し、取得時に現在のタイムゾーンに戻します。

（4）TIMESTAMP値は1970年より前または2037年より後にすることはできません

日時

（１）８バイトのストレージ

（２）時間帯とは関係ない

（３）DATETIME値を「YYYY-MM-DD HH:MM:SS」形式で取得し表示する。サポートされる範囲は「1000-01-01 00:00:00」から「9999-12-31 23:59:59」です。

MySQL のパフォーマンスがどんどん高くなるにつれて、時間の保存方法は個人の習慣やプロジェクトの要件に依存すると思います。

int と timestamp と datetime のパフォーマンス テストに関する 2 つの記事を共有します。

MySQL DATETIME vs TIMESTAMP vs INT テスター

テーブル `test_datetime` を作成します (
`id` int(10) 符号なし NOT NULL AUTO_INCREMENT,
`datetime` フィールドタイプがNULLではありません。
主キー (`id`)
)ENGINE=MyISAM;

モデル構成

• キップロック
• キーバッファ = 128M
• 最大許容パケット = 1M
• テーブルキャッシュ = 512
• ソートバッファサイズ = 2M
• 読み取りバッファサイズ = 2M
• 読み取りバッファサイズ = 8M
• myisam_sort_buffer_size = 8M
• スレッドキャッシュサイズ = 8
• クエリキャッシュタイプ = 0
• クエリキャッシュサイズ = 0
• スレッド同時実行性 = 4

テスト

日付時刻 14111 14010 14369 130000000
タイムスタンプ 13888 13887 14122 90000000
13270 12970 13496 90000000

mysqlを実行する

mysql> test_datetime から * を選択して、出力ファイル '/tmp/test_datetime.sql' に移動します。
クエリは正常、10000000 行が影響を受けました (6.19 秒)

mysql> test_timestamp から * を選択して、出力ファイル '/tmp/test_timestamp.sql' に移動します。
クエリは正常、10000000 行が影響を受けました (8.75 秒)

mysql> test_int から * を選択して、出力ファイル '/tmp/test_int.sql' に移動します。
クエリは正常、10000000 行が影響を受けました (4.29 秒)

test_datetime テーブルを変更し、test_int の名前を変更します。
test_int テーブルを変更し、datetimeint 列を追加します (INT NOT NULL)。
test_int を更新し、datetimeint を UNIX_TIMESTAMP(datetime) に設定します。
テーブル test_int を変更し、列 datetime を削除します。
テーブル test_int を変更し、列 datetimeint を変更します。datetime int は null ではありません。
test_int から * を選択して、出力ファイル '/tmp/test_int2.sql' に格納します。
テーブル test_int を削除します。

これで、DATETIME テストからまったく同じタイムスタンプが得られ、TIMESTAMP テストでも元のタイムスタンプを再利用できるようになります。

mysql> ファイル '/export/home/ntavares/test_datetime.sql' のデータをテーブル test_datetime にロードします。
クエリは正常、10000000 行が影響を受けました (41.52 秒)
レコード: 10000000 削除: 0 スキップ: 0 警告: 0

mysql> ファイル '/export/home/ntavares/test_datetime.sql' のデータをテーブル test_timestamp にロードします。
クエリは正常、10000000 行が影響を受け、44 件の警告 (48.32 秒)
レコード: 10000000 削除: 0 スキップ: 0 警告: 44

mysql> ファイル '/export/home/ntavares/test_int2.sql' のデータをテーブル test_int にロードします。
クエリは正常、10000000 行が影響を受けました (37.73 秒)
レコード: 10000000 削除: 0 スキップ: 0 警告: 0

予想どおり、INT はそのまま保存されますが、他のものは再計算する必要があります。TIMESTAMP は DATETIME のストレージ サイズの半分を使用しているにもかかわらず、パフォーマンスが依然として低下していることに注意してください。

フルテーブルスキャンのパフォーマンスを確認してみましょう。

mysql> SELECT SQL_NO_CACHE count(id) FROM test_datetime WHERE datetime > '1970-01-01 01:30:00′ AND datetime < '1970-01-01 01:35:00′;
++————–+
| カウント(ID) |
++————–+
|211991|
++————–+
セット1列目（3.93秒）

mysql> SELECT SQL_NO_CACHE count(id) FROM test_timestamp WHERE datetime > '1970-01-01 01:30:00′ AND datetime < '1970-01-01 01:35:00′;
++————–+
| カウント(ID) |
++————–+
|211991|
++————–+
セット1列目（9.87秒）

mysql> SELECT SQL_NO_CACHE count(id) FROM test_int WHERE datetime > UNIX_TIMESTAMP('1970-01-01 01:30:00′) AND datetime < UNIX_TIMESTAMP('1970-01-01 01:35:00′);
++ ...
| カウント(ID) |
++ ...
|211991|
++————–+
セット1列目（15.12秒）

しかし、TIMESTAMP のパフォーマンスは低下し、再計算が影響しているように思われたので、次にテストするのに適した方法は、再計算を行わないことであるように思われました。つまり、UNIX_TIMESTAMP() 値に相当する値を見つけて、代わりにそれを使用します。

mysql> UNIX_TIMESTAMP('1970-01-01 01:30:00′) AS lower、UNIX_TIMESTAMP('1970-01-01 01:35:00′) AS bigger を選択します。
+——-+——–+
| 低く | 大きく |
+——-+——–+
| 1800 | 2100 |
+——-+——–+
セット内の 1 行 (0.00 秒)

mysql> SELECT SQL_NO_CACHE count(id) FROM test_int WHERE datetime > 1800 AND datetime < 2100;
++————–+
| カウント(ID) |
++————–+
|211991|
++————–+
セット1列目（1.94秒）

MySQL DATETIME vs TIMESTAMP vs INT パフォーマンスと InnoDB を使用したベンチマーク

要約する

以上がこの記事の全内容です。この記事の内容が皆様の勉強や仕事に何らかの参考学習価値をもたらすことを願います。123WORDPRESS.COM をご愛顧いただき、誠にありがとうございます。これについてもっと知りたい場合は、次のリンクをご覧ください。