Q&A
14.1. EXPLAINの利用 https://www.postgresql.jp/document/12/html/using-explain.html 評価してる？
質問内容
単純な疑問で恐縮なのですが、SQL(SELECT)が重くなるのは「レコード数」なのでしょうか。それとも「データザイズ」なのでしょうか。
質問背景
zekterraさんの質問であったり、他でも見かける中で「nnn万件のデータのSELECTに時間がかかります」という内容があるかと思います。
しかし、対象テーブルが「1カラム」の場合と「10カラム」の場合では違うのでは？という単純な疑問が沸いてきました。
例えば、
「1カラム」しか保持してなく、1レコード辺りのデータサイズも小さい場合、SELECT文のレスポンスも速い印象で、
「10カラム」を保持しており、1レコード辺りのデータサイズも約10倍ある場合、SELECT文のレスポンスも併せて遅くなりそうな印象です。
しかし、
どこを調べても「レコード数」に関するチューニングの記述しか見当たらず、「データサイズ」に関する記述が見つかりません。
これは、
「『レコード数が原因であり、データサイズは関係ないのである』という事であり、あえて言及していないのか･･･?」という純粋な疑問が沸いてきました。
そこで、
SQLやデータベースに詳しい方にご教示いただきたいと思い、投稿させていただいた次第です。
補足情報
・PostgreSQLを採用しており、テーブルAに約400万レコード格納されている
・テーブルＡには、暗号化されたデータA(JSON)を格納していて、取得時に復号する
・データＡは、処理Aや処理Bなど複数処理に用いられるが、処理Aについては暗号化しなくていい情報しか用いられていない
→「暗号化すべきものとそうでないものを区別してテーブルを分ければ、レコード辺りのデータサイズも削減されて、処理Aが軽くなるのでは？」という仮説を立ててみた
（他人のシステムなのであまり手出しできないが、そもそもJSONを加工･正規化もせずに使っているので、説得材料として加工･正規化をさせたい意図。仮説が誤ってたら他を考える予定。）
回答6件
0
説得材料として「レコード辺りのデータが多いから、正規化してね」と言えたらいいなという
エンティティの視点から、分割される事はあっても、「レコード辺りのデータが多い」理由が正規化されていない事とは言い切れません。
以前、親子関係にある情報を正規化すると低速であったため、正規化を緩めて配列で保持するようにしたことがあります。
※８千万件のデータを正規化すると、2億件の子テーブルとなったので、結合する事の負荷が高かった。
「暗号化すべきものとそうでないものを区別してテーブルを分ければ、レコード辺りのデータサイズも削減されて、グラフ描画が軽くなるのでは？」という仮説を立ててみた
復号するのは、項目毎ですから、関係ないですね。
「『レコード数が原因であり、データサイズは関係ないのである』という事であり、あえて言及していないのか･･･?」
1行に大量の項目があったとしても、最終的に取得する件数が小さくなるなら読み取りに関する性能は悪くありません。
これは、最終的に取り出すデータの総和の量に関係するものです。
目的のデータに辿り着くまでを如何にコストを下げるかが大事なのです。
投稿2021/02/14 16:30
総合スコア25327
0
SQLの文法上間違っていなくて正しくとも、遅いSQLは存在します。
例えば、TIMESTAMP型のカラムを相手に今月のデータを絞り込もうとしたとき、
都度カラムに関数を適用して今月である、かつ今年である、などと条件を与えるよりも、
BETWEENで月初と月末の日付で挟んだほうが速い、などです。
カラムの値を演算子や関数などで処理する場合、
演算加工した値がメモリ上にキャッシュとして載っているうちはまだ気になりませんが、
レコード数が多くなってきたり他にもトランザクションが多数動いていたりすると、
重くなってしまう要因になります。
演算したり関数を使ったりしないで絞り込めるような書き方に改めるのも、
チューニングではないかと。
また、インデックスが効くようなデータ構造なのかどうか。
11.2. インデックスの種類
アルゴリズムも色々選べますし、400万件を効率よく絞り込むためにも配慮するべきかと。
それと、SQLのチューニングだけでなく、PostgreSQL自体のチューニングもありますよね。
19.4. 資源の消費
shared_buffers や temp_buffers が効いてくるのかな、あまり細かく突っ込んだことがないのでなんとも。
絞り込んでる最中の情報を保持する量としては temp_buffers かなぁ。
投稿2021/02/08 04:39
編集2021/02/08 04:49退会済みユーザー
総合スコア0
0
どこを調べても「レコード数」に関するチューニングの記述しか見当たらず、「データサイズ」に関する記述が見つかりません。
そもそも論として、データは必要だからデータベースに入れるわけで、「重いから1レコードあたりのデータ量を削減する」という選択を取る余地は、通常はないと考えます。
投稿2021/02/08 02:41
編集2021/02/08 02:42
総合スコア145963
> しかし、対象テーブルが「1カラム」の場合と「10カラム」の場合では違うのでは？という単純な疑問が沸いてきました。
SELECT *で漫然とデータを取得する場合、全カラムのデータを取得しますので、ディスクアクセスや通信量は取るデータ数に比例しますが、それは「投げたクエリが悪い」という話です。
> そもそも論として、データは必要だからデータベースに入れるわけで、「重いから1レコードあたりのデータ量を削減する」という選択を取る余地は、通常はないと考えます。
私も同意見です。
ただ、他人が作ったシステムで正規化が綺麗になされていないため、説得材料として「レコード辺りのデータが多いから、正規化してね」と言えたらいいなという･･･。
ちなみに、状況としては以下のような感じです。
・PostgreSQLを採用しており、テーブルAに約400万レコード格納されている
・テーブルＡには、暗号化されたデータA(JSON)を格納していて、取得時に復号する
・データＡは、"グラフ描画"や"データダウンロード"などに用いられるが、"グラフ描画"については暗号化しなくていい情報しか用いられていない
→「暗号化すべきものとそうでないものを区別してテーブルを分ければ、レコード辺りのデータサイズも削減されて、グラフ描画が軽くなるのでは？」という仮説を立ててみた、、、という感じです。
> tyaunuakkia さん
会話がかみ合ってないですよ。
もう一度回答を読むと良いです。
私としては、「レコード数」と「データサイズ」が比較対象として登場するこの質問は非常に違和感があります。
で、この回答と追加のコメントはその理由が端的に説明されています。
0
そもそもの話になりますが、説得材料にするならネットで聞いた知識を語るのではなく、実際に非効率なデータベースをあなたの理論で効率的なものに変え、それを計測した数字を出すのが良いと思います。
理論はあくまで理論であり、その通りになる保証はありません。具体的なコードが一切無い状態での理論は特に。
投稿2021/02/08 04:57
編集2021/02/08 07:21
総合スコア28669
明確な根拠がなければ、やってみても何も変わらなかったという状況もありますしね。
> →「暗号化すべきものとそうでないものを区別してテーブルを分ければ、レコード辺りのデータサイズも削減されて、グラフ描画が軽くなるのでは？」という仮説を立ててみた、、、という感じです。
こんなことを書いているので、テーブルを分けなくても select 文で余計なカラムを取得しているのをやめるだけで軽くなる可能性もあるのかもしれませんし、データや作業者次第で正しい理論が本来の力を発揮できなかったりすることもあるんですよね。
> 説得材料として加工･正規化をさせたい意図。
これがこの質問の意図だそうですが、このそもそもの目的を果たすには数字を出すのが早いし、遅かれ早かれ数字は出さなくてはいけないので、面倒な段階を踏まなくても実際にデータをコピーしてやってみればいいのにと思いました。
0
「非効率なSQL」「適切にインデックスが設定されていない」可能性のほうが高いと思います。
それらも含めてパスォーマンスチューニングには実行計画の取得及び解析は必須です。
投稿2021/02/08 02:33
編集2021/02/08 02:38
総合スコア80875
> 「非効率なSQL」「適切にインデックスが設定されていない」可能性のほうが高いと思います。
> それらも含めてパスォーマンスチューニングには実行計画の取得及び解析は必須です。
そうですね、私も同意見です。
ちなみに、「SQLも正しく、インデックスも適切で、尚も遅い場合」はいかがでしょうか。
SQLは正しくてインデックスも適切なので検索は速いものの、レコード辺りのサイズが大きいからレスポンスも遅くなる、、、という事はないのでしょうか？
（DBではないですが、WebAPIコール時もファイルサイズが大きいデータ(PDFなど)を取得しようとすると重くなる事があるため）
「正しい」の基準は要件次第ですし、利用するDBにもよります。
質問に書いてないのでお応えしかねます。
> 「正しい」の基準は要件次第ですし、利用するDBにもよります。
> 質問に書いてないのでお応えしかねます。
出せない情報は質問に書けませんので汲んでいただければと思います。申し訳ないです。
ひとまず出せる情報だけでも質問本文に追記されては。
個別のコメントでは情報がバラけます。
そうですね、ありがとうございます。
ひとまず他者さんへコメントした補足情報を追記いたしましたのでご確認くださいませ。
>「SQLも正しく、インデックスも適切で、尚も遅い場合」
そのSQLをサブクエリーなどが無いプライマリーを直接指定するだけの単純なものにした場合の結果も同様に遅いなら、DBMSのパラメータチューニング等が必要で、そうでないなら、SQLやインデックスがまだ適切では無いという事です。
saziさん
フォローありがとうございます。
「適切」の基準はそれぞれだし「十分対策されたかどうか」は観点にもよりますしね。
質問者がデータのせいにしたいだけにも見えました。
0
ベストアンサー
結論いえば、データ量でパフォーマンスは違います。
物理的なディスクアクセスの時間、ネットワークの転送量が増えるので当然です。
テーブルを分ければよいかは、JOINするのであれば、効果は低いです。
というか、パフォーマンスは悪くなるはずです。
正規化は、更新箇所が減るのでupdateについては効果がありますが
selectでいうと、同一ブロックに配置されていた情報が、異なるブロックに配置されるため
ディスクIOが増えるのであれば、むしろ遅くなるはずです。
身も蓋もない話ですが、実測した効果を示した方がよいのではないでしょうか。
投稿2021/02/08 04:44
総合スコア4826
あなたの回答
tips
プレビュー
