LoginSignup
0
0

More than 1 year has passed since last update.

@TechnoKuRo(TechnoKuRo)

プライマリキーのデータ型に日時型を使うのは避けたほうがいい？

Last updated at Posted at 2022-09-30

履歴テーブルを考えていたら、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 でもやってみたい

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0