3
Help us understand the problem. What are the problem?

More than 1 year has passed since last update.

posted at

updated at

【Mysql】日付カラムのデータ型はdate型?varchar型? 問題を議論してみたい

こんにちは。
データエンジニアのKotaです。
初投稿ですが、お手柔らかにお願いします。

普段僕は自社プロダクトのデータ分析チームで、
ビッグデータ分析基盤の要件定義~運用まで一気通貫で作らせて頂いてます。

そこで今回議論したいのが、表題の件。

現場によっても、下手すれば現場内のDBによってもバラバラの型をしている日付カラム。
皆さんお持ちのDBでは、どちらの型になっているでしょうか??

議論したいと思ったきっかけ

かくいう僕も、この問題で先日上司と軽くバトりました。
まずはその話から簡単に触れます。ちなみにエンジンはMysqlです。

ある日、別の分析基盤を作っている後輩のDB設計書をレビューする機会がありました。メンバーは後輩・僕・上司の3人。
そこで後輩が、分析用一時テーブルの日付列のデータ型を以下の様にしていました。

カラム名 データ型
CREATE_TIME(作成日) varchar(14)
UPDATE_TIME(更新日) varchar(14)

恐らく一部マスタ系のテーブル以外はほぼ持っているであろうこのカラム。

これに対し、語気を強めてツッコミを入れたのが先輩です。

「ここの2つさ、datetime型にしてよ!!!」

お?そんな強く言うこと?
いや、てか別にvarcharで良くね?
むしろvarcharが良くね?と思ったのが僕。

その旨上司に伝えてみると、

「いやいや絶対datetime型!!」の一点張り。

その場で議論が始まるも、平行線。
ちなみに既存の分析基盤はdatetimeだったので、
しょうがなく僕が折れる形でdatetimeに決着しました。

僕の言い分

事実だけ伝えられても「そっか...」って感じだと思うので、
お互いの主張の理由を見てみましょう。

まずは僕から。僕の主張の根拠は以下3つです。

①チューニングのしやすさ
②業務用DBとのフォーマット合わせ


僕が重視したのはこれです。
検索の際に関数を指定しなくて良くなるので非常に楽。
特に既存の分析用SQLなんかは、
件数が億に迫るテーブルから、1日分のデータを以下のように算出していました。

SELECT ...
FROM ...
WHERE DATE(RPAD(UPDATE_TIME,14,0)) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) AND CURRENT_DATE();

ちなみにこれ以外に条件はありません。中にはこれに加え、結合を行っているものも複数あります。
WHERE条件で使用しているUPDATE_TIMEをdate型に変換+謎のRPAD関数と、
関数を2ネストかましています。(恐らく不良データ対策でしょう)
当然関数を使ってるのでインデックスは効きません。
それどころかこのテーブル、UPDATE_TIMEを第一項目としたインデックスすら付与していない始末。
既に件数が億近いテーブルからこの条件でデータ抽出。よく今までやってたな...って感じです。
UPDATE_TIMEがvarcharになれば、不必要にdate変換なんてしなくて良いので、
インデックス効くようになるし。RPADは知らん。作った人辞めてて理由は誰も知らん。地獄って感じ。

②こちらの理由もあります。うちの現場、UPDATE_TIMEの扱いがDBによって違います。

★業務用DB

カラム名 データ型
CREATE_TIME(作成日) varchar(14)
UPDATE_TIME(更新日) varchar(14)

★分析用DB

カラム名 データ型
CREATE_TIME(作成日) datetime
UPDATE_TIME(更新日) datetime

★分析結果可視化用DB

カラム名 データ型
CREATE_TIME(作成日) datetime
UPDATE_TIME(更新日) datetime

何で違うんでしょうね。作った人辞めてて理由は誰も知らん。地獄って感じ。
恐らく可視化の際に、BIツールでのデータソース読み込みで都合が悪そうだからdatetimeにしてるのかな...って感じはします。

今回後輩君はこの理由でvarcharにしたそうです。不良データなら可視化DBに移送する際にチェックできるし。だそうです。その通りだと思います。

上司の言い分

一方、上司の言い分です。

①現行踏襲
②不良データへのリスク
③設計書見て、「日付入れてるんだな」って分かりやすい

①、③は聴き流してました。

②が彼のメイン主張。
僕のチューニングに対する言い分を聞いた上で、
「運用乗ってるし、何か変わったときに変なデータ入って処理が落ちるリスクがある」んだそう。
その辺の検証をする時間的余裕はない!って言われてしまったので、
それ以上何も言い返せず、この場は終わりました。

モヤモヤする...。

あまりにモヤっとなのでここに書かせて頂きました。
またハードウェアに優しくない激重SQLが1つ出来上がってしまうのかと思うと...。

僕が経験してきた現場でも、ここの型にはバラツキがあったので気になります。どっちがより良いんでしょうか。

皆さんの現場ではここのデータ型、何にしていますか?
どんな理由があってその型にしていますか?

ぜひご教授頂けたら、とても嬉しいです。

最後まで見て頂いて、ありがとうございました。

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Sign upLogin
3
Help us understand the problem. What are the problem?