Q&A
SQLサーバへの負荷を考えるのなら
オリジナルで分散処理を書くより、シャーディングまたはマスタースレーブ構成あたりを検索した方が良いです。
初めて質問いたします。下記につき悩んでおりまして先輩方のお考えを拝借できればと思います。どうぞよろしくお願いします。
概要
laravelにて作成中の業務系システムのデータベースの設計につき、システムを利用するユーザ毎にデータベースを分けるべきか、或いは同じデータベースを利用するべきかで悩んでいます。
何も調べずに考えた段階ではユーザ毎にデータベースを分けるつもりでした。
ですが調べてみたところ、過去に同じような趣旨の質問(https://teratail.com/questions/53161)があったのでそこを閲覧したりStackOverflowなども見てみましたが、いずれもインスタンスを分ける考え方には否定的な回答が多い印象でした。
いずれも４年以上前のもので少々時間が経っている事から、最近の事情はどうなのであろうと思いましての質問です。
現在においてもやはりデータベースをユーザ毎に分けるという考え方はアンチパターンなので考え直すべきなのでしょうか？
今回の前提条件
・ログイン管理には別のインスタンスを利用している。
・各ユーザは店舗を複数（１～２００程度）持っており、データは店舗毎に、或いは任意の複数店舗を纏めて集計する必要がある。また店舗数は任意のタイミングで増加していく。減る事は基本的に無い。
・店舗番号と店舗名称を紐づけるテーブルが必要。
・各店舗はユーザが独自に設定できる２つのグループの両方に属している。その２つのグループを管理するテーブルが必要。
・各店舗から上がってくるデータは複数種ありそれぞれにテーブルが必要。
・各テーブルには最小で１店舗ごとに１日１レコード、最大で１０００レコードほど追加される。登録した後に削除するレコードは基本的に無い。
・各ユーザが利用するテーブルの構造は基本的に同じ。ただしユーザが利用する機能によっては不必要なテーブルや追加で必要なテーブルもある。
・ログインしたユーザは自分のデータのみ閲覧し、他のユーザのデータを直接的、間接的にも利用する事はない。
・ユーザには親ユーザと子ユーザがあり、子ユーザは親ユーザの閲覧できる範囲を上限に閲覧できる範囲を設定できる。
・アプリケーションの仕様の変更により、必要なデータが増加する可能性がある。
何故当初の考えに疑問を持ち質問をしたのか。
色々調べている過程でデータベースを分けるデメリットとして下記のような事かと自分なりに解釈しました。
1.)テーブルの追加、変更などの際の管理が煩雑になる。
2.)ユーザ間を跨ったクエリが不可能になる。
3.)速度という意味では適切なインデックスが貼ってあれば分ける事にメリットは別にない。
その為に色々調べている過程で、そもそもデータベースインスタンスは分けるべきではないという回答が多かったのですが、インスタンスを纏めるとなると今度は下記の点が気になります。
1.)テーブルによってはサイズが大きくなりすぎるのではないか。
2.)アプリケーションに何らかのミスがあった際、他ユーザのデータを見れてしまう可能性が高くなる。
3.)ユーザが住み分けられている方が有事の際に分かりやすい、対応しやすい。特定ユーザへのサービス終了の際などもわかりやすい。(という点が失われる)
今回の私のケースではどうするのがベストなのか、色々悩んでも考えが纏まらず困っております。どうぞよろしくお願いします。
ご回答ありがとうございました。
今回は大きなシステムでは無いので負荷よりもそもそもこういったシステムではどうあるべきか、の方が主眼ではありましたがご教示頂いたシャーディング、マスタースレーブ構成についても調べなおし検討したいと思います。
回答5件
0
各「ユーザー」がバラバラな「企業」（あるいはそれに類する事業体）で、それぞれごとに分散したシステムを構築するのに近いスタイルなのであれば、データベースは分けるほうが第一選択となります。
- 万が一にも、データが他人に見えると大問題となる
- ユーザーごとにカスタマイズを施すことも考えられ、その場合には分けておかないとやりづらい
- ユーザーの壁を超えての検索・集計も、ほぼ発生し得ない
一般的な「1つのサービスを構成する」ユーザーが多数いる状況とは、全く話が違います。
投稿2020/09/02 07:29
総合スコア145183
0
実装上の都合を考えた場合
「特殊な客が来たり、数が増えたらプロジェクトを分けたり、EC2インスタンスやMySQL鯖レベルで分離させれば良くね？」が現実的なラインかなと思います。
社外秘だと思いますので下記の質問に答える事はしないほうが良いでしょうけど、
心の中で回答したり、紙に書いて確かめてみてください。
- ユーザーって誰？
- 1ユーザー200店舗持っててという話だけど、それサービス開始と同時に超負荷のトラフィックが来る大人気コンテンツになるの？
- なんで報告様式が違うの？システムの中で吸収して、GUI(HTML)で出し分ける程度じゃ駄目なの？
まずはこれらがYESかNOかで決まります。
膨大なレコードに対応する為にDBを分けるという戦略は
YAGNI原則に沿って考えると、大げさに考えすぎでは？
それだけの客から利用料金をもらって速度が担保出来なくなったやべえ
→じゃあデータベースを分割するアプローチを採用してみようか
こういう泥縄で良いんです。
実際Facebook、Twitter等の大手サービスも、
最初や普通にPHPやRoRという小さく実装しやすい形でのサービススタートでした。
トラフィクが増えて困ったからC系やJava系へ以降するという方針でやっています。
なので一つでも冒頭の質問でNOがあるならば、
YAGNI原則が当てはまるのではないか？という視点で見たほうが良さそうです。
以下気になった箇所へのコメント
各ユーザは店舗を複数（１～２００程度）持っており
仮にDBを分けたとしましょう。
その場合、全てのDBで客のシステムは正常動作するのかを担保しなければなりません。
例えばユーザーが100人、平均店舗数が100店舗だったとすると1万店舗になりますよね。
この1万の店舗の内、実装ミスやDBのアップデートが遅れた事が原因で
はい100店舗規模が一斉に動作しませんになるわけですよ。
100ユーザー全てでテストを通さなければなりません。
誰がテストするねん。
手動ではらちがあかないでしょう。
そしてユーザー毎にDB構造が変わるがあるという要件があり、
自動テストを組むだけでも一苦労です。
であれば、DB的なところは一定のフォーマットを強制させて、PHP・HTML・JSなんかで見せ方を工夫して何とかするアプローチを模索する事になるでしょう。
情報漏えいや速度の視点でDBを分けるという提案をされていますが、
そもそも可用性という視点ではかなり怖い決断になるでしょう。
ログインしたユーザは自分のデータのみ閲覧
これって例えばMySQLのユーザーアカウントを発行して、
SQL発行しますレベルで切り分けるんですか？
もしこれがYESならばユーザーにとって非常に嬉しいシステムになるでしょう。
実現するならDBを分ける選択肢しか用意されていないと言って良いでしょう。
速度という意味では適切なインデックスが貼ってあれば分ける事にメリットは別にない。
サバンナライオン「それ数十億レコードのテーブルでも同じこと言えんの？」
例えばソシャゲなんかでは週一でメンテやってますね。
インデックスがあろうがなかろうが、
億レベルのレコードのせいであらゆる操作が重くなってゲームとしてストレスを与えるような状況に陥ります。
なのでメンテ時間を利用して不要なレコードを整理しているはずです。
インデックスがあるから楽勝！……ではないので、
本当に速度要件を満たせるのか、実際に実務に近いようなテーブルやレコード作って確かめてみてください。
自分の目で確かめるのもITエンジニアの重要な業務の一つで
こういう研究を高速で完了させるのも優れたITエンジニアの要件です。
ユーザ間を跨ったクエリが不可能になる。
MySQLならFROM句にdb_name.table_nameと書けば、
異なるDBのテーブルも指定できますよ。
もちろんユーザー間を跨いだ集計を始めると、
まともな速度は出なくなると思いますが、
そもそもユーザー間は要件では跨がないんですよね？
これはDB分けたほうが良い理由の一つになるでしょう。
ユーザが住み分けられている方が有事の際に分かりやすい、対応しやすい。特定ユーザへのサービス終了の際などもわかりやすい。(という点が失われる)
これは本当ですか？
100データベースのログテーブルを見張る事になるので、
本当にわかりやすいか懐疑的です。
1テーブル管理側でも有利な環境を構築すれば、
十分わかりやすいシステムを作れるのではないでしょうか？
この辺は色々設計してみないと見えてこないですね。
一旦は保留で考えた方が良いと思います。
どちらにせよ良いアイデアが出来て上手く行くシステム作れるはずです。
各テーブルには最小で１店舗ごとに１日１レコード、最大で１０００レコードほど追加される。登録した後に削除するレコードは基本的に無い。
えっ、しょぼくね？
まぁ私が断定しちゃ駄目ですね。
想定状況2つ作ってパフォーマンス比較してみてはどうでしょうか。
投稿2020/09/02 07:45
総合スコア21158
ご回答ありがとうございます。
YAGNI原則は初耳でしたのでとても参考になりました。あまり先のことばかり考えすぎずないよう気を付けます。
今回は
ログインしたユーザは自分のデータのみ閲覧
これって例えばMySQLのユーザーアカウントを発行して、
SQL発行しますレベルで切り分けるんですか？
もしこれがYESならばユーザーにとって非常に嬉しいシステムになるでしょう。
実現するならDBを分ける選択肢しか用意されていないと言って良いでしょう。
これで行くことを考えておりました。運用面での苦労はおっしゃる通りありそうですが万が一にも他者のデータを見られる状況は避けるべき案件なのでこの方針色々実験しつつ進めていこうと思います。
0
ベストアンサー
ユーザーの定義が曖昧な気がします。
サービスを提供する（契約等）の単位で考えると、そのユーザー同士の連携がある場合（ゲームなど）は分けない方が良いですし、ユーザー同士の連携が無いなら分けた方が良いと思います。
質問の「業務系システム」という事なら、保守・運用面を考えると分けないとヤバイと思います。
リカバリー時を考えると、分けられていないと非常に面倒ですし、事故も発生しやすくなります。
規模が小さいなら、物理的に同じサーバーにいてもいいわけですし、スケールアウトするにしても移植の手順は少なくて済みますから。
投稿2020/09/02 06:53
総合スコア25173
ご回答ありがとうございます。
ユーザの定義が曖昧とのご指摘、申し訳ありません。おっしゃる通りです。
今回はご返答での予想通り契約単位でユーザが作成されてユーザ間の連携は全くありません(親ユーザ⇔子ユーザ間は除く)。また規模は大きくないのでデータベースは分けるにしても物理的には同じサーバでの運用を考えておりました。
調べ方が悪かったのか同じテーブルスキーマならデータベースは分けるべきでは無いとの考え方ばかりがヒットしてきてデータベースを分ける事に不安を感じていたのですが、「分けないとヤバイ」とまでおっしゃって頂けて分けて設計する事に自信を持てました。
0
ベストというのは無いと思うので、個人的な考えですが…
用語を定義してから始めます。
インスタンス: MySQLサーバーのインスタンス。
データベース: create database で作られるもの
スキーマ: create schema で作られるもの
個人的に選択する順番の順で並べます。
同一データベース
メリット
- アプリ側の作り的に慣れている（普通に作るだけですからね）
- 条件を間違えると他ユーザーのデータが見れるというのはごもっともなので、そこは阻止できる仕掛けを作り込むべきだとは思います
デメリット
- 運用面で、このユーザーのデータをバックアップから戻して。みたいなリクエストには答えにくい（別にこれも仕掛けを作り込めば可能ですが、手動対応だと事故が怖いです）
- 将来、負荷が上がったときの対応が面倒になる（が、先行事例は多い）
データベースをユーザーごとに作る
メリット
- ユーザーごとのデータのバックアップ、リストアがやりやすくなるので運用面では利便性が高い
- 将来、DB負荷分散する際も、インスタンスをわけるだけで済んでしまう
デメリット
- デメリットは複数コネクションを使い分ける的な実装になるのでそのあたりで面倒が起きそう
- DBへのコネクションが増えるのでサーバー側の設定を見ておかないと事故りそう
- アプリのユーザーが増えた時に、データベースの作成とユーザー作成をやらなければいけない（これは自動化できそうですが）その情報をアプリ側にどう繋ぐの？というあたりで苦労しそう
選ばない案
- 同一データベース、別スキーマ→それをするなら別データベースでの方がよい
- ユーザーごとにインスタンスを分ける→メンテナンスが大変すぎます。
投稿2020/09/02 07:29
総合スコア2027
0
ユーザーの使用権限が、DB/テーブル/レコード単位によります。
更新可能ユーザーはログインさせるなど参照ユーザーと分けた方がベター。
ゆるっとした社内向けならすべてrootでアクセスさせてもさほど問題はありません
投稿2020/09/02 06:21
総合スコア114779
ご回答ありがとうございます。
ただ私の質問の不備や理解不足のせいかせっかくのご回答があまり理解できませんでした。
ユーザに見せたいデータの範囲は決まっている(そのユーザ用のデータのみ)ので、その上でDBの設計により使用権原がDB単位/テーブル単位/レコード単位になるかが決まると思うのですが。
更新可能ユーザと参照ユーザは分けた方がベターという事は理解できました。その通りかと思います。
単純な話、システム側ではDBまたはテーブル単位でログインユーザーごとにアクセス制御ができます。
レコード単位の制限は、アクセス可能グループやユーザーを各レコードに埋め込むことで
同じDB、同じテーブル内でもアクセスできるレコードを絞ることができます。
WEBの場合、セッションでユーザー管理をすることが多いので、セッションユーザーごとに
アクセスを振り分ければ、インスタンスに指定するユーザー(mysqlのユーザー)は
そんなにバラバラにわける必要はないということです
再度のご回答ありがとうございます。
セッションユーザーごとにアクセスは分けるのですが、まずは万が一の為にもユーザ毎にデータベースは分け個別にmysqlのユーザも作成する方向でやってみようと思いますが、mysqlユーザの作成は省略する事も視野に入れつつ進めていこうと思います。
ユーザーに関しては分かりづらいですよね・・・
・サーバーへのログインユーザー
・mysqlのログインユーザー
・apacheのユーザー
・セッションのユーザー
があって使い分けが必要です、またそれらのユーザーグルーピングして同じ
アクセス単位にするなど運用時には拡張していくことになります
あなたの回答
tips
プレビュー
