MySQL関連について

tool

// チューニングツール(perl)
https://github.com/major/MySQLTuner-perl

// MySQLBench

mysql tuner の説明

日付について

MySQLの日付型の扱い方や機能をまとめてみました

結論

5.6.4以降であれば、5bytになるので、datetimeでOK
5.6.4以前であれば、timestampで運用しつつ、2038年に近づいたら、datatimeに変更もあり
※しかし、データが多いと困難。。。

mysqlは、後方互換性を担保しているので、5.6.4以降になることを見据えて、はじめからdatetimeでやっておくのが、一番いいかも。

カラムのストレージ容量とかは、サーバの増強とかスケールでなんとかなるし。

DATETIME

日付 + 時刻

表示形式 : YYYY-MM-DD HH:MM:SS

範囲 : 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59

DATE

日付

表示形式 : YYYY-MM-DD

範囲 : 1000-01-01 ~ 9999-12-31

TIMESTAMP

• MySQLバージョン
• SQLモード

によって異なる

日付 + 時刻

表示形式 : YYYY-MM-DD HH:MM:SS

範囲 : 1970-01-01 00:00:00 ~ 2038-12-31 23:59:59

注意点

不正な日付が入力された場合は 指定フォーマットに全て0が代入された値が入力される。

フォーマットは、変更可能だが、全ての日付に適応される。
例) YY-MM-DDなど

日付関数

NOW()
CURRENT_DATE

検索について

MySQLでDATETIME型のデータを高速に検索する方法

int型で保存して、変換して使用した方が3~5倍高速になる。

datetime型など日付け用の型の検索の場合は、
BETWEENを使用することで、INDEXを使用した検索が可能。

mysql5.5以上ならdatetimeでも速い？

ストレージ容量

引用元

timestamp => datetimeに型を変更

-- 作成したテーブル
CREATE TABLE `date_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created_at` timestamp,
  `updated_at` timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

-- 確認
desc date_test;
+------------+-----------+------+-----+---------------------+-----------------------------+
| Field      | Type      | Null | Key | Default             | Extra                       |
+------------+-----------+------+-----+---------------------+-----------------------------+
| id         | int(11)   | NO   | PRI | NULL                | auto_increment              |
| created_at | timestamp | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
| updated_at | timestamp | NO   |     | 0000-00-00 00:00:00 |                             |
+------------+-----------+------+-----+---------------------+-----------------------------+
3 rows in set (0.00 sec)

-- 適当にinsert

select * from date_test;
+----+---------------------+---------------------+
| id | created_at          | updated_at          |
+----+---------------------+---------------------+
|  1 | 2014-05-18 06:09:53 | 2014-05-18 06:09:53 |
|  2 | 2014-05-18 06:10:03 | 2014-05-18 06:10:03 |
|  3 | 2014-05-18 06:10:04 | 2014-05-18 06:10:04 |
|  4 | 2014-05-18 06:10:05 | 2014-05-18 06:10:05 |
|  5 | 2014-05-18 06:10:05 | 2014-05-18 06:10:05 |
+----+---------------------+---------------------+
5 rows in set (0.00 sec)

-- timestamp => datetimeに型を変更

alter table date_test change created_at created_at datetime;

-- 変更後確認

select * from date_test;
+----+---------------------+---------------------+
| id | created_at          | updated_at          |
+----+---------------------+---------------------+
|  1 | 2014-05-18 06:09:53 | 2014-05-18 06:09:53 |
|  2 | 2014-05-18 06:10:03 | 2014-05-18 06:10:03 |
|  3 | 2014-05-18 06:10:04 | 2014-05-18 06:10:04 |
|  4 | 2014-05-18 06:10:05 | 2014-05-18 06:10:05 |
|  5 | 2014-05-18 06:10:05 | 2014-05-18 06:10:05 |
+----+---------------------+---------------------+
5 rows in set (0.00 sec)

desc date_test;
+------------+-----------+------+-----+---------------------+----------------+
| Field      | Type      | Null | Key | Default             | Extra          |
+------------+-----------+------+-----+---------------------+----------------+
| id         | int(11)   | NO   | PRI | NULL                | auto_increment |
| created_at | datetime  | YES  |     | NULL                |                |
| updated_at | timestamp | NO   |     | 0000-00-00 00:00:00 |                |
+------------+-----------+------+-----+---------------------+----------------+
3 rows in set (0.00 sec)