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

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

ただいまの
回答率

90.32%

  • MySQL

    6191questions

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

MySQLでのテーブル設計について

受付中

回答 4

投稿

  • 評価
  • クリップ 0
  • VIEW 222

ar3qna

score 2

MySQLでのテーブル設計について質問させてください。

現在、MySQLで運用中のシステムがありますが、そこでデータ量が数百万レコードがあるテーブルがあります。

ここに機能追加で保存したい変数を追加することが度々あるのですが、データ量の大きなテーブルへのカラムの追加は処理コストが大きいので避けたいと考えています。
※現在は、カラムを追加したいときは、新しいテーブルを作ってデータをコピーしてからテーブル名の変更をしているので、システムを止めるほどではないですが、コピーに数時間がかかるので差分を後で追加するなど細々とした手間がかかります。

そこで、インデックスを貼らない、検索対象にはならない、他のテーブルとジョインしたりしないようなデータに関しては、なんでも入れることができるようなテキスト型のカラムを一つ作っておいて、そこにjson形式で保存しておこうかと考えています。

それであれば、機能追加で保存しておきたい変数が増えても、jsonデータの中に追加しておくだけで住みますので、テーブルの変更は必要ありません。

こういった使い方はあまり良くない、こんなデメリットがあるなどありましたら教えてください。

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

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

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

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 4

0

今時数百万レコードのテーブルなんてざらです。数千万件、数億件のレコード件数のテーブルも珍しくありません。ハードウェアの性能にもよりますが、開発環境用のサーバーでカラム追加して処理時間を測定しては?

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/06/21 16:21

    開発環境でカラム追加をしたことがありますが、数時間のロックが掛かって現実的ではないことはわかっていますので、気軽にカラムの追加はしません。

    質問してるのは、カラムの追加の処理時間ではなく、設計に関してです。

    キャンセル

  • 2018/06/21 17:21

    余程非力なハードウェアを使っているとか、大きなLOBがいくつもあるとかでない限り、
    >コピーに数時間がかかる
    というのは不可解です。
    差支えない範囲で、具体的にどんな用途のテーブルでどんなカラムを追加したいのかを書かれた方が適切なコメントが付くのでは?

    キャンセル

  • 2018/06/21 17:33

    ご回答ありがとうございます。

    データが600万レコードありますが、AWSのRDSでlargeインスタンスなのでそれほど非力ではないと思いますので何か他に問題ありそうな気もしてきました。追加したカラムは、int型のカラムを4つとtext型のカラムを1つで、これらにはインデックスは貼っていません。

    データの中身はユーザー投稿のコメントのようなものです外部キーは貼っていません。インデックスを張ってるカラムが10個くらいあるのでこの再生成に時間がかかってるのかもです。

    データのコピーは、
    insert into 新しいテーブル(新しいテーブルのカラム) select 古いテーブルのカラム,追加したカラム from 古いテーブル
    という感じのクエリを書いて入れ直しています。

    キャンセル

  • 2018/06/21 17:46 編集

    データ量を確認した所、4Gくらいでした。FULLTEXTインデックスを貼っているカラムが一つありました。

    キャンセル

  • 2018/06/21 17:59

    AWS RDS largeであろうとクラウドのI/O周りは遅いのが泣き所です。インデックスの数は少ない方がINSERTの負荷は少ないです。
    わたしは使ったことがありませんが、パラレルSQLスレッドが使えるか調べてみては?
    http://nippondanji.blogspot.com/2011/10/mysqlmysql-563-m6.html

    キャンセル

0

私だったら試してみる、というレベルの話で、使ったことはないですがテキスト型ではなく、JSON型、というのはどうでしょうか?

http://gihyo.jp/dev/serial/01/oss-db-various-news/0016
https://www.s-style.co.jp/blog/2017/06/420/

制限つきですが、インデックスを付けたりもできそうですので、テキスト型で自前で用意するよりは楽できそう。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/06/21 17:43

    ありがとうございます!

    JSON型なんて初めて知りました!
    AWSのRDSのAuroraを使っていますが、AuroraでもJSON型に対応しているようです。
    https://aws.amazon.com/jp/blogs/news/using-json-with-mysql-5-7-compatible-amazon-aurora/

    インデックスを貼ることは考えていませんでしたが、ちょっとしたクエリを書いて調査したいときなんかでも、JSON用の関数も用意されているようなのでちょっとしたことならSQLだけで済みそうですし、良さげですね。

    上記のページの上の方にありましたが、JSON型を使うことで柔軟性が出るというのは、(テキスト型にJSONを入れるのも同じと考える)方向性として間違っていなそうな気がしますので、設計としてはOKとしようと思います。

    JSON型は少し使ってみて困ったことがなさそうだったら実運用に入れてみます!ありがとうございます

    キャンセル

0

json形式でデータを保持するのであれば、
テーブル設計よりもシステム設計から考え直さないといけないはずです。
例えば、
そのデータ(カラム)をどのようにして登録してどのようにして更新するか、
消込の更新は考慮してあるか、初期値(デフォルト値)の設定があるか。
SQLで運用しているのであれば、SQL組めるか?既存のSQLは対応しているか?
システムが利用しているのであれば、json形式が扱えるライブラリを利用しているか?

MySQLのバージョンが何かでも対応方法が変わってくるかと。
json使えてもDBのjson関数は癖があったと思うのであまり使うべきではないかと思います。

考えは悪くないと思いますが、考慮が足りていますか?
上記に書いたものも、考慮すべきことのごく一部だと考えてください。

機能追加でカラムを増やす運用をしてきたのであれば、
そのシステムではそのやり方を踏襲しておくのが無難です。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/06/21 18:12

    ご回答ありがとうございます!

    データの登録部分はご指摘のとおり、今使ってる関数などとは別に作る必要があるなと考えていますが、ライブラリに依存はしていないので問題はないです。

    JSON関連の関数はちょっと調べたいような部分では使おうかと思いますが、一般に使われる部分では、SQLではデータを取得してPHP側でテキストデータとみなしてJSONのデコードをして使おうかと考えています。SQLの処理自体は極力シンプルにしておいたいと思っています。

    そうなるとJSON型のメリットがどの程度あるかというとそれほどないような気もしてきました。

    考慮すべき点が明確で素晴らしいですね!

    システム設計側としては現状、JSON形式ではないのですが、カンマ区切りで管理しているデータもあり、それに関しては取得してPHP側で分解して使っていて、使い勝手はいいのですが、シンプルなデータでない限りカンマ区切りではわかりにくすぎるので、シンプルでないデータについてはJSON形式のテキストでの管理でいいかなと考えています。

    キャンセル

  • 2018/06/22 15:27

    カンマ区切りがシンプルでjsonが万能というわけでもありませんよ。
    DB側の関数の問題でしたが、postgresでタブ文字が入っているとtextからjsonに変換できないなど、
    特殊なケースで使えない場合もあります。
    その時はtextのタブを置換してjsonにしたのですが、あまり好ましい対応ではありませんよね。

    カラム追加によるデータ移行の実時間は削減できると思いますが、
    プログラムの改修コストや障害時の調査などのコストがあがってしまうのではないかと懸念されます。
    トータルしてどちらのほうがよいか。。。

    キャンセル

0

なんとも言えませんが適当な単位でパーティショニングするだけで
効果があると思います
そもそもテーブルの設計段階で拡張の必要性は考慮すべきなので
あとからテーブル構造を頻繁に変えるのはちょっとデータ管理の
基本ができていないとしか言いようがありませんね。

逆にレコードに対して動的に不規則な属性が追加されていくような
仕組みであれば、カスタマイズデータを別テーブルで管理すればよい
ような気がします。
たとえば600万件の登録積みデータにたいして、カラムを追加しても
600万件のnullデータが発生するだけですよね?
あまり効率的だとは言えないでしょう

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/06/22 15:31

    ありがとうございます。確かに別テーブルに個別データを管理するという手はありますね。とても参考になります。

    キャンセル

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

  • ただいまの回答率 90.32%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

同じタグがついた質問を見る

  • MySQL

    6191questions

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