実現したいこと

時間のかかるクエリを可能なかぎり高速で取得したいのですが
知識が乏しいため質問させて頂きました

教えて欲しいこと

販売テーブルと仕入れテーブルいずれも３５万件ほどのレコードがあり
商品別、取引展別などで集計したいのですが、データ量が多く
１回のSQLが非常に長いため、ビューにした方が良いのかと考えました

上記の３５万件のテーブルはいずれも過去のバックアップテーブルとして存在しているため
日常的に更新されることはありません、

ビューの場合、初回のビュー作成には非常に時間がかかるものと思われますが
更新がないとすると基本的にビューの更新は初回のみでその後は更新されない？という理解であっていますでしょうか。

また、select * from ビュー where 条件で抽出されるSQLの実行時間は、
通常のSQLと比較して格段に早くなるよいう認識であっていますでしょうか

ご存知の方、どうぞよろしくお願いいたします。

m.ts10806

2023/09/22 20:36

テーブル定義にもよると思います　。仮に組んでみて実行計画とってみましたか？

行動規範の内容に同意します

回答2件

ベストアンサー

MySQLのビュー

パフォーマンス改善を目的としたビューということは、マテリアライズドビューを想定されているのかもしれませんが
MySQLにマテリアライズドビューはありません。
https://dev.mysql.com/doc/refman/8.0/ja/faqs-views.html#faq-mysql-have-materialized-views

１回のSQLが非常に長いため、ビューにした方が良いのかと考えました

何度も同じようなクエリを書くのが大変……ということであれば、普通のビューは役立ちます。
(※性能改善ではなく、手間の削減や再利用性向上など目的で)

ビューの場合、初回のビュー作成には非常に時間がかかるものと思われますが

ビューは、定義するだけでは特に負荷はかかりません。

また、select * from ビュー where 条件で抽出されるSQLの実行時間は、
通常のSQLと比較して格段に早くなるよいう認識であっていますでしょうか

ビューに対するクエリは、実際にはビューが参照する実テーブルへのクエリとして実行されるので
実行時性能が改善することは基本的にありません。

むしろ、集約処理(GROUP BYとか)を含むビューを作成し、後からWHEREを加えて抽出する場合などは
本来の元テーブルへのクエリ(WHEREしてからGROUP BY)より性能劣化が起こりえます。

一般的なマテリアライズドビュー

MySQLには存在しませんが、他DBMSでのマテリアライズドビューについて一般的な回答をしますと
マテリアライズドビュー初回作成時および追加更新削除時の負荷は増えますが
その分複雑なJOINやGROUP BYやORDER BYを含むクエリであっても性能改善が期待できます。

MySQLでの性能改善方法

MySQLで性能改善が必要な場合は

インデックスや既存クエリ自体の見直し
※基本的なやつ
サーバ物理性能のスケールアップ、MySQLサーバのパラメータ見直し
※ごく普通のDBチューニング
マテリアライズドビューの自力実装
水平分割(パーティショニング)

などがあります

クエリやインデックス、サーバ本体やDB設定での改善がこれ以上見込めないのであれば、以降の対策をせざるを得ないかもしれません

マテリアライズドビュー自力実装は、普通の実テーブルをビュー代わりに使用します。
(マテリアライズド)ビュー定義と同じような構造になるようにCREATEし、ビュー同等のクエリでそのままINSERT-SELECTして初期化します。
元テーブルへの更新追随は、全ての元テーブルに対する各種トリガーで常時追随する方法や、定期的なテーブル丸ごと初期化(中身総入れ替え)処理などで実現します。
SELECTクエリの性能改善を期待はできますが、整合性を保つためにかなり面倒になるデメリットもあります。

水平分割とは、たとえば「年をまたいだ集計処理はしない」前提であればデータを年ごとに分割してDB内部で管理させることで性能改善が見込めたりします。
ただし管理がやたら複雑になったり前提を満たさないとろくに性能改善できなかったりするので、事前にしっかりと検討や検証が必要です。
(※理屈としては知っていますが実際やったことはないのであまり正確な情報提供はできません)

投稿2023/09/23 04:45