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

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

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

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

Q&A

4回答

279閲覧

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

ar3qna

総合スコア14

MySQL

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

0グッド

0クリップ

投稿2018/06/21 07:04

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

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

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

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

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

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

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

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

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

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

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

guest

回答4

0

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

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

投稿2018/06/21 12:00

yambejp

総合スコア114505

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

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

ar3qna

2018/06/22 06:31

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

0

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

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

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

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

投稿2018/06/21 08:18

szk.

総合スコア1400

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

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

ar3qna

2018/06/21 09:12

ご回答ありがとうございます! データの登録部分はご指摘のとおり、今使ってる関数などとは別に作る必要があるなと考えていますが、ライブラリに依存はしていないので問題はないです。 JSON関連の関数はちょっと調べたいような部分では使おうかと思いますが、一般に使われる部分では、SQLではデータを取得してPHP側でテキストデータとみなしてJSONのデコードをして使おうかと考えています。SQLの処理自体は極力シンプルにしておいたいと思っています。 そうなるとJSON型のメリットがどの程度あるかというとそれほどないような気もしてきました。 考慮すべき点が明確で素晴らしいですね! システム設計側としては現状、JSON形式ではないのですが、カンマ区切りで管理しているデータもあり、それに関しては取得してPHP側で分解して使っていて、使い勝手はいいのですが、シンプルなデータでない限りカンマ区切りではわかりにくすぎるので、シンプルでないデータについてはJSON形式のテキストでの管理でいいかなと考えています。
szk.

2018/06/22 06:27

カンマ区切りがシンプルでjsonが万能というわけでもありませんよ。 DB側の関数の問題でしたが、postgresでタブ文字が入っているとtextからjsonに変換できないなど、 特殊なケースで使えない場合もあります。 その時はtextのタブを置換してjsonにしたのですが、あまり好ましい対応ではありませんよね。 カラム追加によるデータ移行の実時間は削減できると思いますが、 プログラムの改修コストや障害時の調査などのコストがあがってしまうのではないかと懸念されます。 トータルしてどちらのほうがよいか。。。
guest

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 08:15

sysjojo

総合スコア325

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

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

ar3qna

2018/06/21 08: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型は少し使ってみて困ったことがなさそうだったら実運用に入れてみます!ありがとうございます
guest

0

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

投稿2018/06/21 07:12

Orlofsky

総合スコア16415

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

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

ar3qna

2018/06/21 07:21

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

2018/06/21 08:21

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

2018/06/21 08:33

ご回答ありがとうございます。 データが600万レコードありますが、AWSのRDSでlargeインスタンスなのでそれほど非力ではないと思いますので何か他に問題ありそうな気もしてきました。追加したカラムは、int型のカラムを4つとtext型のカラムを1つで、これらにはインデックスは貼っていません。 データの中身はユーザー投稿のコメントのようなものです外部キーは貼っていません。インデックスを張ってるカラムが10個くらいあるのでこの再生成に時間がかかってるのかもです。 データのコピーは、 insert into 新しいテーブル(新しいテーブルのカラム) select 古いテーブルのカラム,追加したカラム from 古いテーブル という感じのクエリを書いて入れ直しています。
ar3qna

2018/06/21 08:47 編集

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

2018/06/21 08:59

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

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

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

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

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

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

ただいまの回答率
85.51%

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

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

質問する

関連した質問