質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

Q&A

1回答

2029閲覧

datatimeをuniq制約のindexに追加するのはやめた方がいいでしょうか?(次のようなランキングテーブルを設計するにあたりご相談です。)

qaz3330

総合スコア113

MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

0グッド

0クリップ

投稿2016/08/06 13:16

記事に対するランキングを集計するSQL文を記述しようと思います。

categoryには「すべて」「スポース」「趣味」「ビジネス」「金融」

等があり、記事ごとに、一つのカテゴリに属します。

ただし、ランキングを算出する場合は次のカラムにランキングの結果を入れるような設計で考えております。

「所属カテゴリ」のランキング => ranking 「すべて」のカテゴリのランキング => ranking_all

また、ランキングは3時間毎に集計し
created_atには"2016/08/06 11:00"
といった値が入ります。

今回、rankingとranking_allにランキング結果を集計したいため、
同じレコードに2回アクセスすることになります。

そのため、DUPLICATE KEY UPDATEを使いレコードが存在すれば更新するというSQL文にしようと思ってます。

その際に、
uniq制約の設計をしなくてはいけないのですが、
現状は、article_idとcreated_at でしか、uniqかどうかの判断ができません。

ランキングのため、created_atのような時間軸のカラムは必須だと思っております。

質問

そこで質問したい点は

このような状況下の中、uniq制約をかけるindexはarticle_idとcreated_atでよろしいでしょうか?
created_atのようなdatetimeをindexの対象にしてしまうことは、後々問題になったりしないかなと心配です。

テーブル設計やカラムを追加した方がいい等、ありましたら、ご指摘頂けますと幸いです。

宜しくお願いします。

------------------------------------------------------------------------------+ | Table | Create Table | ------------------------------------------------------------------------------+ | rankings | CREATE TABLE `rankings` ( `article_id` char(255) NOT NULL, `category_id` int(11) NOT NULL, `ranking` int(11) DEFAULT NULL, # 「所属カテゴリ」のランキングがこのカラムに入る `ranking_all` int(11) DEFAULT NULL, # 「すべて」のカテゴリのランキングがこのカラムに入る `created_at` datetime NOT NULL, `updated_at` datetime NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | ------------------------------------------------------------------------------+

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答1

0

記事IDと日付で一意ですよと明示する意味では、
UNIQUE制約に作成日付を含めるのはなしではないですが、
更新の多さから考えるとパフォーマンス面でどうなのかという懸念がありそうですね。

僕個人としては、
リソースが許すのであればもう一段階テーブル分割するというのも一つの手かなと考えてます。
イメージとしてはスーパタイプ・サブタイプっぽい考え方にはなりますが・・・

  • ランキング管理(スーパタイプっぽい何か)

SQL

1CREATE TABLE `ランキング管理` ( 2 `rank_id` INT(10) UNSIGNED NOT NULL, # 管理用ID(カテゴリ別、全カテゴリで同一IDで) 3 `category_id` INT(10) NOT NULL, # カテゴリID 4 `created_at` datetime NOT NULL, # ランキングが一括更新であるなら作成日、更新日はこっちだけでよい希ガス 5 `updated_at` datetime NOT NULL, 6 PRIMARY KEY(rank_id, category_id), 7 UNIQUE(category_id, created_at) # 検索条件で使われそうだから念のためINDEXを張る 8)
  • カテゴリ別ランキング(「すべて」を除外したランキング)(サブタイプ)

SQL

1CREATE TABLE `カテゴリ別ランキング` ( 2 `rank_id` INT(10) UNSIGNED NOT NULL, 3 `article_id` char(255) NOT NULL, 4 `category_id` INT(10) NOT NULL, 5 `ranking` INT(10) DEFAULT NULL, 6 PRIMARY KEY(rank_id, article_id) 7)
  • 全カテゴリランキング(「すべて」のみのランキングを格納)(サブタイプ)

SQL

1CREATE TABLE `全カテゴリランキング` ( 2 `rank_id` INT(10) UNSIGNED NOT NULL, 3 `article_id` char(255) NOT NULL, 4 `ranking` INT(10) DEFAULT NULL, 5 PRIMARY KEY(rank_id, article_id) 6)

上記なような構成が考え付きました。

この構成のメリットとしては
0. 作成日含んだUNIQUE制約を張った管理テーブルは更新頻度が少ないのでINDEXを張った際のパフォーマンス劣化を抑止できる
0. カテゴリ別とすべての場合でテーブルを分けたので原則INSERTのみで対応可(重複UPDATEの考慮がいらない)
0. 障害発生時の調査が行いやすい
・管理情報とランキングを同一トランザクションで作成するようにすれば管理情報がない時点で障害と判断可
・カテゴリ別のランキングとすべてのランキングは普通なら件数が一致するはずなので件数相違があった時点でおかしい

逆にデメリットとしては
0. テーブルを割ったことによるリソース消費
0. ランキング情報が2つあるため単純レコード数が2倍に増加
0. 一定期間ごとに古いランキングを削除する仕組みがないと無事死亡

つまる所、旧期間のランキングを長く保持したい仕組みにするならば、
この構成は逆に危険となります。

後カテゴリ別ランキングからカテゴリIDを除いた理由ですが、
記事ごとにカテゴリは1つという仕様があるため外してます。

ただカテゴリ別ランキングのくせにどのカテゴリに属すのか分かり辛いので、
後々のためには入れたほうがいいかもです(管理テーブルとのカテゴリIDの二重持ちにはなりますが)
※むしろ管理情報の主キーと結合して主キーに対する索引一意スキャンにした方が速度が出そうなので直しときます。

あくまでもご参考程度までに。

###補足
下記のリンク先にもありますが、
NOT FILLオプションなどを指定しない限りはカッコ内に11を指定する意味はなさそうです。
mysqlのint(11)のカッコ内の数値の意味

###追記1
UNIQUEインデックス更新のパフォーマンス悪化のみ防ぎたい場合は、
管理テーブル、ランキングテーブルの二段階構成だけでも効果は見込めるかも

###追記2
今さら気づいたけど記事IDの精度は255桁なんですね。
IDで255桁ってあんまり聞いたことないかな・・・。
このIDの桁数を短くすることが可能でれば、
そうした方がパフォーマンス的にも良い気がします。

投稿2016/08/06 16:12

編集2016/08/06 18:17
Panzer_vor

総合スコア1636

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

まだベストアンサーが選ばれていません

会員登録して回答してみよう

アカウントをお持ちの方は

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問