Q&A
質問文を読む限りだと基本的には動作しているが、条件を変更していないのにある日突然データが取れなくなる、という事？
であれば、そのクエリをどのように発行しているのかわからないけど、発行元でエラー発生の有無（有る場合はその内容）を調べたり、RDBMSの側でログを確認したりという事が最初の調査ステップになるんで、それが提示されないと第三者には判断するのが非常に困難だと思うけども。
SQLで、viewを元にして作ったviewの値がnullになるときがあります。
何が原因か、ご教授頂きたく、宜しくお願い致します。
(実際につかっているDBはMriaDBで、件数は25000件ほどです）
＜例＞
概要
A:「Inputテーブル(t1)」と「属性テーブル(t2)」からview「結合テーブル(V1)」を作成。
B:上記view「結合テーブル(V1)」を元に view「最終テーブル(V99)」を作成。
通常は、view「最終テーブル(V99)」は正常に機能していますが、原因不明で値がnullになってしまうことがあり、気が付いた都度、view「最終テーブル(V99)」をDROP して再度CREATEしています。
※view「結合テーブル(V1)」は、常に正常に機能しています。
①view「結合テーブル(V1)」
SQL
1CREATE VIEW V1 2AS 3SELECT 4t1.DB_id, t1.DB名, t1.id, t2.属性_id, t1.属性名, t1.値, 5FROM `inputテーブル` as t1 6inner join 7属性テーブル as t2 8on t1.属性_id=t2.属性_id 9
②view「最終テーブル(V99)」
SQL
1CREATE VIEW V99 2AS 3SELECT 4 5id, 6MAX(CASE 属性名 WHEN '名前0' THEN 値 else null END) AS 名前, 7MAX(CASE 属性名 WHEN '住所0' THEN 値 else null END) AS 住所, 8MAX(CASE 属性名 WHEN '年齢0' THEN 値 else null END) AS 年齢 9FROM 10V1 11GROUP BY 12id 13HAVING 14MAX(CASE WHEN DB_id='1' THEN DB名 END) = 'sample' 15
■お尋ねしたいこと
①
MySQLでviewを元にして作ったviewの値がnullになるときがあります。
何が原因か、また、その対応策について、ご教授頂きたく、宜しくお願い致します。
②
viewをもとに、viewをつくることは避けた方がいいのでしょうか
（複雑になるからとかの理由で、、、）
それとも、通常、現場では普通に使う方法なのでしょうか。
回答2件
0
ベストアンサー
V1の結果がキャッシュされていれば、そのような事は起きるかもしれません。
なので、V1経由のビューではなく、V1を展開した形でのV99を作成した方が良いでしょうね。
投稿2021/06/29 03:56
総合スコア25327
sazi様
ご回答ありがとうございます。
私的には、V99は、めまいがするほど、複雑です。
V1を展開した形でのV99を作成、となると、一体どうしたらいいことやら、、、
ヒントをご教示いただけますと、嬉しいです。
MariaDBが10.2.1以降なら、with式にV1の内容を展開すればいいので、そこまで手は掛からないと思います。
もう一つの方法は、一時テーブルにV1の内容を出力して、それを参照するようにする。
あくまで、v1の結果を出力するのに掛かる時間の影響が無いならですが。
そもそも、キャッシュが原因かどうかは推測なので、have_query_cacheの内容を確認して下さい。
https://dev.mysql.com/doc/refman/5.6/ja/query-cache-configuration.html
もし、有効になっている様なら、query_cache_typeを変更し、SQL_NO_CACHEオプションを付けてみてください。
https://dev.mysql.com/doc/refman/5.6/ja/query-cache-in-select.html
※当然キャッシュの恩恵は受けれなくなるので、遅くなるという事にはなりますが。
sazi様
ご回答ありがとうございます。
・MariaDBが10.3.29でした。
・with式にV1の内容を展開してviewを作成する具体的情報がなかなか見つかりません。
CREATE VIEW V99
AS
SELECT
id,
MAX(CASE 属性名 WHEN '名前0' THEN 値 else null END) AS 名前,
MAX(CASE 属性名 WHEN '住所0' THEN 値 else null END) AS 住所,
MAX(CASE 属性名 WHEN '年齢0' THEN 値 else null END) AS 年齢
FROM
※以降viewV1の展開？
with v1
AS
(SELECT
t1.DB_id, t1.DB名, t1.id, t2.属性_id, t1.属性名, t1.値,
FROM `inputテーブル` as t1
inner join
属性テーブル as t2
on t1.属性_id=t2.属性_id)
※ここまでviewV1の展開？
GROUP BY
id
HAVING
MAX(CASE WHEN DB_id='1' THEN DB名 END) = 'sample'
とかでも、無いようだし、、、
・have_query_cacheがyesだったので、V1とV99のselectにSQL_NO_CACHEオプションを付けてみました。
若干遅くなった感じがしますが、許容範囲の感じがしてます。
with式にV1の内容を展開してviewを作成する具体的方法のアドバイスをご教授いただければ、嬉しいです。
SQL_NO_CACHEオプションで解決するなら、WITH式での展開は不要だと思います。
withを使用した記述は以下の様なものです。
CREATE VIEW V99 AS
with v1 as (v1の中身)
select ～ -- V99の中身
sazi様
誠に、ご回答ありがとうございました。
本当に、助かりました。
SQL_NO_CACHEオプション、with句、別々に試してみました。体感的には同程度の速さでした。
一応、SQL_NO_CACHEオプションのほうで、運用していこうと思っています。
※
V1:
SQLアンチパターンとされるEAV形式（縦表示）の複数テーブルを結合して最終形のテーブル作成
V99:
VIを横形式のテーブルとして作成
このV1➡V99は、実務現場では多い作業だと思いますが、この過程を、キャッシュ等の不安定克服も含めて説明した情報があまりないのが、不思議な感じでした。
SQLアンチパターンを敢えて使う場合の方法なんて、情報が少ないのは当然という気がします。
今回はSQLアンチパターンというところまで踏み込んだ所では無く、Viewを使った場合の不備という事ですから、DBMSの仕様を確認すべきでしょうね。
因みに、with式(CTE)は可読性を高めるというのもあるんですが、SQL内ではキャッシュされるので性能向上にも貢献します。
sazi様
ありがとうございました。
アドバイスいただきましたwith式(CTE)のSQL内でのキャッシュも考えて、こちらを採用してしばらく様子をみてみようと思います。
sazi様のおはなしすべてから、息を呑むほどの実務現場のプロの熱気が伝わってくるかのようでした。
このたびは、本当にありがとうございました。
ご回答ありがとうございます。
すいません、再度確認しましたら、FROMの前の , は本番の記載では元々つけていなかったです。
申し訳ございません。
今後、ご指摘して頂いた通り、現象を再現できるようにテーブル定義はCREATE TABLE文で、テーブル中のデータはINSERT文で Markdown の [コード] に記載するようにいたします。
今後、サーバー側・DB側でのエラーログを調べてみようと思います。
ちなみに、viewAを作成し、そのviewAをもとに、viewBを作成することは、実務上、通常の作業なのでしょうか。
質問は修正できます。
「今後」って書く人は今後でも改めない事が多いです。
CREATE TABLEやINSERTを提示すると動作確認してくれる人が多くなります。
Orlofsky様
ご回答ありがとうございます。
当方、恥ずかしながら、CREATE TABLEやINSERTなど、SQL文を作成したことがほとんどなく、今しばらく時間がかかりそうですが、書き上げて、質問を必ず訂正させていただきます。
このたびは、お忙しい中、誠にありがとうございました。
あなたの回答
tips
プレビュー
