履歴テーブルを考えていたら、PKにDate型を使いたくなった

よくPKには Char や Number を使い、Varchar 型や Date 型を忌避する傾向がありますが、なぜなんでしょう？
別に機能的には問題ないように感じますが、パフォーマンス面での問題があるのでしょうか？

結論から書きます

むやみに Char にするより、Date のほうがパフォーマンス高いことが多い
< > の範囲検索では、VARCHAR が思いのほか早いけど、そんなに差があるわけじゃない
結論、キーに日時型を使うことを躊躇する必要はそれほどない。

調べてみました。

ググる

Varchar 型

可変長と固定長の問題から、いたずらに Varchar を使うのでなく、Char にしたほうがいいという意見が多い様子
もちろん、数値ならば Number を使うべき

Date 型

型毎のデータサイズを理由として、パフォーマンスに影響するという説
Char よりも Date のほうがデータサイズが少ないので、Date のほうが優秀なのか？

よく、時間情報がいらない（邪魔）だから Char にするという手法があるけど、そもそも 13月とかわけわからない情報を排除できないデメリットもあるし、RDBMSによっては、時間情報を持たない日付型（MySQLのDATE型）もあるので、この辺はメリデメ含め、RDBMSに依存する気もする。

予想

データサイズによるところ、というのは理論的で信頼したくなった
いずれにせよ、「なんかパフォーマンス悪そう」というイメージは都市伝説であるのは濃厚だ

検証方法を考える

第1PKにNUMBER、第2PKに Number, Char, Varchar, Datetime(日付、時間を持つ型）の4パターンテーブルを作って、それぞれ同等情報を40万件くらい登録して、検索パフォーマンスを調べる

※100万行で検証しようと思ったけど、時間要素を持たない日付型をキーとして扱う現実的な値として、 1900年～3000年をPKとして扱いたいので、各行を一意に扱うため、40万1763件　とした。
※各行は、1日と1秒毎に加算してテストデータとした

検証

テストテーブル

みんな大好き(?) MySQL でテストします。
対象型は

CHAR
DATE（時間要素を持たない日付）
DATETIME（時間要素を持つ日時）
NUMBER (yyyyMMdd hhmmss を入れるため BIGINT）
VARCHAR
とします。

createTestTables.sql


-- テーブルの構造 `test_char`
--

CREATE TABLE `test_char` (
  `no` int UNSIGNED NOT NULL,
  `second` char(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- テーブルの構造 `test_date`
--

CREATE TABLE `test_date` (
  `no` int UNSIGNED NOT NULL,
  `second` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- テーブルの構造 `test_datetime`
--

CREATE TABLE `test_datetime` (
  `no` int UNSIGNED NOT NULL,
  `second` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- テーブルの構造 `test_number`
--

CREATE TABLE `test_number` (
  `no` int UNSIGNED NOT NULL,
  `second` bigint UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- テーブルの構造 `test_varchar`
--

CREATE TABLE `test_varchar` (
  `no` int UNSIGNED NOT NULL,
  `second` varchar(16) COLLATE utf8mb4_general_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- テーブルのインデックス `test_char`
--
ALTER TABLE `test_char`
  ADD PRIMARY KEY (`no`,`second`);

--
-- テーブルのインデックス `test_date`
--
ALTER TABLE `test_date`
  ADD PRIMARY KEY (`no`,`second`);

--
-- テーブルのインデックス `test_datetime`
--
ALTER TABLE `test_datetime`
  ADD PRIMARY KEY (`no`,`second`);

--
-- テーブルのインデックス `test_number`
--
ALTER TABLE `test_number`
  ADD PRIMARY KEY (`no`,`second`);

--
-- テーブルのインデックス `test_varchar`
--
ALTER TABLE `test_varchar`
  ADD PRIMARY KEY (`no`,`second`);

テストデータ

前述のとおり、1900年から 1日と1秒を加算して、3000年の直前までの 40万1763件（≒日）をテストデータとしました。

insertTestData.sql

insert into test_char values(1, '19000101121201');
insert into test_char values(2, '19000102121202');
insert into test_char values(3, '19000103121203');
insert into test_char values(4, '19000104121204');
insert into test_char values(5, '19000105121205');
insert into test_char values(6, '19000106121206');
insert into test_char values(7, '19000107121207');
insert into test_char values(8, '19000108121208');
insert into test_char values(9, '19000109121209');
insert into test_char values(10, '19000110121210');
insert into test_char values(11, '19000111121211');
......(これを延々と char date datetime number varchar に突っ込む)

検証方法

概ねの中央値のSELECT を　固定値（＝）、複数値（IN句）、範囲値（< and >）で検索して、その秒数を取ります。

実施

eq

一致判定の場合、文字列より日付や数値がパフォーマンスが高そう
日付と数値の差はないと思った

selectEq.sql

-- 1個目
SELECT * FROM `test_char` WHERE second = '25321118042434';
-- 0.1010 sec

SELECT * FROM `test_date` WHERE second = '2532/11/18';
-- 0.0804 sec

SELECT * FROM `test_datetime` WHERE second = '2532/11/18 04:24:34';
-- 0.0818 sec

SELECT * FROM `test_number` WHERE second = 25321118042434;
-- 0.0788 sec

SELECT * FROM `test_varchar` WHERE second = '25321118042434';
-- 0.1001 sec
------------------------------------------------
-- 2個目
SELECT * FROM `test_char` WHERE no = 231154 and second = '27560405030420';
-- 0.1022 sec

SELECT * FROM `test_datetime` WHERE no = 231154 and second = '2756/04/05 03:04:20';
-- 0.0868 sec

SELECT * FROM `test_date` WHERE no = 231154 and second = '2756/04/05';
-- 0.0829 sec

SELECT * FROM `test_number` WHERE second = 27560405030420;
-- 0.0833 sec

SELECT * FROM `test_varchar` WHERE no = 231154 and second = '27560405030420';
-- 0.1099 sec

範囲

範囲判定の場合、文字列型が思いのほか早い。
特に varchar が成績がいい！　けどそんなに違いがあるわけじゃない

selectLtGt.sql

-- 1個目
SELECT * FROM `test_char` WHERE second > '20300101000000' AND second < '20350101000000';
-- 0.0206 sec

SELECT * FROM `test_datetime` WHERE second > '2030-01-01' AND second < '2035-01-01';
-- 0.0176 sec

SELECT * FROM `test_date` WHERE second > '2030-01-01' AND second < '2035-01-01';
-- 0.0175 sec

SELECT * FROM `test_number` WHERE second > 20300101000000 AND second < 20350101000000;
-- 0.0179 sec

SELECT * FROM `test_varchar` WHERE second > '20300101000000' AND second < '20350101000000';
-- 0.0091 sec
------------------------
-- 2個目
SELECT * FROM `test_char` WHERE second > '21300101000000' AND second < '21350101000000';
-- 0.0297sec

SELECT * FROM `test_datetime` WHERE second > '2130-01-01' AND second < '2135-01-01';
-- 0.0250 sec

SELECT * FROM `test_date` WHERE second > '2130-01-01' AND second < '2135-01-01';
-- 0.0248 sec

SELECT * FROM `test_number` WHERE second > 21300101000000 AND second < 21350101000000;
-- 0.0243 sec

SELECT * FROM `test_varchar` WHERE second > '21300101000000' AND second < '21350101000000';
-- 0.0159 sec

複数値(IN句)

IN句の場合、文字列型よりも日付・数値が早い。
日付・数値はやっぱり大差がない。

selectIn.sql

-- 1個目
SELECT * FROM `test_char` WHERE second in ('19610724182646', '21460330131059', '25600525071204', '27780816052028', '29960526032609');
-- 0.1112 sec

SELECT * FROM `test_datetime` WHERE second in ('1961/7/24 18:26:46', '2146/3/30 13:10:59', '2560/5/25 07:12:04', '2778/8/16 05:20:28', '2996/5/26 03:26:09');
-- 0.0844 sec
                                           
SELECT * FROM `test_date` WHERE second in ('1961/07/24', '2146/03/30', '2560/05/25', '2778/08/16', '2996/05/26');
-- 0.0929 sec

SELECT * FROM `test_number` WHERE second in (19610724182646, 21460330131059, 25600525071204, 27780816052028, 29960526032609);
-- 0.0822 sec

SELECT * FROM `test_varchar` WHERE second in ('19610724182646', '21460330131059', '25600525071204', '27780816052028', '29960526032609');
-- 0.1146 sec
-------------------
-- 2個目
SELECT * FROM `test_char` WHERE second in ('19780506200856', '21231118105451', '23750917122749', '26660922175919', '28790520153350');
-- 0.1127 sec

SELECT * FROM `test_datetime` WHERE second in ('1978/5/6 20:08:56', '2123/11/18 10:54:51', '2375/9/17 12:27:49', '2666/9/22 17:59:19', '2879/5/20 15:33:50');
-- 0.0834 sec
                                           
SELECT * FROM `test_date` WHERE second in ('1978/5/6', '2123/11/18', '2375/9/17', '2666/9/22', '2879/5/20');
-- 0.0840 sec

SELECT * FROM `test_number` WHERE second in (19780506200856, 21231118105451, 23750917122749, 26660922175919, 28790520153350);
-- 0.0874 sec

SELECT * FROM `test_varchar` WHERE second in ('19780506200856', '21231118105451', '23750917122749', '26660922175919', '28790520153350');
-- 0.1157 sec

以上何度やってもさほど変わりはないので、サンプリングはこれくらい。

検証結果

範囲検索でまさかの varchar が良い成績でしたが、気に留めるほどの差はありませんでした。
日時型を忌避する理由はないかも。
少なくとも、頑張って文字列型にする必要性はなく、自信をもって日時型をPKにしてよい。

NextAction

Postgres や Oracle でもやってみたい