はじめに
次のインタビューで、EAV（Entity Attribute Value）データモデルの欠点について質問されましたが、何を言うかわかりませんでした。私の意見では、これは任意のデータを保存する理想的な方法です。 少し考えた後、私は唯一の問題はサンプルのインデックスを構築できないことだと言いました。
インタビューの後、私は数日間この質問に戸惑い、結論に至りましたが、私の良心を解くために少しググってみました。 私は自分の考えの確認をグーグルで調べましたが、これは私には十分ではありませんでした-数字による確認で実装したかったです。
私がどのような結論に至り、最適化からどのような利益が得られるかに興味がある場合は、catにようこそ。
一連の記事「The Ideal Catalog」の目次
- 完璧なカタログ、建築スケッチ
- 理想的なカタログ、実装オプション
- 完全なカタログ、データサンプリングの最適化
簡単な結論
疲れていて多くの文字を処理できない場合、結論は次のとおりです。データを選択するには、すべての列にインデックスが構築されているマテリアライズドビューを使用する必要があります。
実体化された表現に基づくサンプルは、同様のインデックスを持つ同様のテーブルのサンプルよりも5〜15％高速に動作します。
これで要約が終わり、順次プレゼンテーションが開始されます。
EAVモデルの使用方法
データは直接クエリで取得でき、エンティティの種類ごとにプレゼンテーションを作成できます（カタログの見出し）。同様のマテリアライズドビューを作成できます。 または、テーブルを作成することもできますが、属性が変更されるたびに、DROP COLUMNおよびADD COLUMNを使用してALTER TABLEを実行する必要があります。通常のオプション。
EAVをGoogleに最適化する方法は他にもありますが、私はこれらを調査しただけです。
テスト方法
「見出し」の内容を生成するスクリプト（PHP）と、テーブルの作成、ビューの作成、マテリアライズドビュー（PostrgeSql）の作成のためのDDLコードを生成するスクリプト。
これらのスクリプトを使用して、対応するデータソースが作成されました。
ここで、エンティティ（カタログセクション）を選択します。エンティティには、多くのポジションがあり、特性（属性）はほとんどありません。
SELECT r.code, (SELECT COUNT(*) FROM rubric_property rp WHERE rp.rubric_id = r.id) property_count, (SELECT COUNT(*) FROM rubric_item ri WHERE ri.rubric_id = r.id) item_count, (SELECT COUNT(*) FROM rubric_property rp WHERE rp.rubric_id = r.id) + ( SELECT COUNT(*) FROM rubric_item ri WHERE ri.rubric_id = r.id) summary FROM rubric r GROUP BY r.code, r.id ORDER BY summary DESC, property_count DESC, item_count DESC;
私の意見では属性の数はそれほど重要ではありませんが、属性の数の影響を評価したい場合、すべてのスクリプト
はリポジトリにあります 。
2種類のサンプルを作成します。最初の種類の選択では1行、2番目の選択では複数の行が返されます。
もちろん、完全なテストには、より多くのサンプルタイプが必要です。
選択の種類ごとに、各データソースに対してクエリを実行します。クエリ、ビュー、テーブル、マテリアライズドビュー、さらにクエリなどから円で選択します。 理論上のそのような周期性は、クエリキャッシングの影響を軽減します。 同じクエリを連続して1000回実行すると、テストするのに悪い方法のように思えます。
したがって、ランタイムを5回測定します。 極端な測定値-最長のリードタイムと最短のリードタイム-は破棄します。 残っているものはテーブルに還元されます。
テストは、次のデータセットで実行されました。
- カテゴリ（エンティティ、エンティティ）-323
- 商品の特性（属性、属性）-47,229
- 商品アイテム-6 989
- 値（値）-1 102279
選択したセクションに直接、41の項目と22の特性がありました。
試験結果
クエリは1行を返します（多くの選択条件）
WHERE mv.tiger IN ('poor', 'white', 'orange', 'red') AND mv.bowl BETWEEN 1000 AND 4000 AND mv.clock > 3000 AND mv.legs < 2000 AND mv.snake = 'crazy'
| 繰り返し | 選択 | 見る | テーブル | マットビュー |
|---|
| 1 | 61 | 53 | 22 | 20 |
| 2 | 49 | 50 | 20 | 15 |
| 3 | 50 | 49 | 20 | 14 |
| 平均時間ms | 53 | 50 | 20 | 16 |
クエリは複数の行を返します（選択条件が少ない）
WHERE mv.tiger IN ('poor', 'white', 'orange', 'red') AND mv.bowl BETWEEN 1000 AND 4000
| 繰り返し | 選択 | 見る | テーブル | マットビュー |
|---|
| 1 | 92 | 70 | 31 | 19 |
| 2 | 63 | 56 | 19 | 14 |
| 3 | 54 | 54 | 22 | 18 |
| 平均時間ms | 69 | 60 | 24 | 17 |
直接クエリの場合、これらはまだ良い結果です。テスト中にデータセットを数回変更し、サンプリング時間が1秒（1000ミリ秒）を超えることがありましたが、実体化された表現からの選択には30ミリ秒しかかかりませんでした。
ご覧のように、テーブルにデータを格納している場合でも、マテリアライズドビューからの選択は高速です。
可能な最適化
マテリアライズドビュー
マテリアライズド表現は、直接クエリよりも何倍も高速です。
データを変更する場合、ビューを更新する必要があります（更新されたマテリアル化されたビュー）。
属性（特性）を追加する場合、ビューを再作成する必要があります（CREATE MATERIALIZED VIEW）。
これらの操作には数秒かかります。 これらのアクションは、有資格者が関与することなく自動化および実行できます。
しかし、100万人のユーザーがいて、常に新しいカタログの位置（見出し）を埋め、管理者が1時間に数回特性のリストを変更する場合、これは問題になる可能性があります。
テーブル
テーブルへのクエリとマテリアライズドビューへのクエリは、直接クエリよりも何倍も速く動作しますが、テーブルへのクエリはマテリアライズドビューへのクエリよりも少し遅くなります。
テーブルは、属性のリストを変更する場合、マテリアライズドビューと同じ問題を抱えますが、特性の値を変更する場合、テーブルのすべてのレコードを更新する必要はありません。変更された行と列のみを更新するだけで十分です。
プレゼンテーション用の外部キーを作成できない場合は、テーブルで可能であり、必要です。これにより、カタログの位置と特性テーブルのレコードをリンクできるため、データの一貫性が向上します。 テーブルを使用すると、テーブルのすべての機能（トリガー、ロギング、バックアップ）を使用できます。
提出
データ定義言語 CREATE VIEW tea_v AS SELECT ri.item_id item_id, ( SELECT sm.string FROM item_content ic JOIN content c ON ic.content_id = c.id JOIN property p ON c.property_id = p.id JOIN string_matter sm ON sm.content_id = c.id WHERE ic.item_id = ri.item_id AND p.code = 'snake' ) "snake" , ( SELECT sm.string FROM item_content ic JOIN content c ON ic.content_id = c.id JOIN property p ON c.property_id = p.id JOIN string_matter sm ON sm.content_id = c.id WHERE ic.item_id = ri.item_id AND p.code = 'tiger' ) "tiger" , ( SELECT sm.string FROM item_content ic JOIN content c ON ic.content_id = c.id JOIN property p ON c.property_id = p.id JOIN string_matter sm ON sm.content_id = c.id WHERE ic.item_id = ri.item_id AND p.code = 'beans' ) "beans" , ( SELECT sm.string FROM item_content ic JOIN content c ON ic.content_id = c.id JOIN property p ON c.property_id = p.id JOIN string_matter sm ON sm.content_id = c.id WHERE ic.item_id = ri.item_id AND p.code = 'sweater' ) "sweater" , ( SELECT sm.string FROM item_content ic JOIN content c ON ic.content_id = c.id JOIN property p ON c.property_id = p.id JOIN string_matter sm ON sm.content_id = c.id WHERE ic.item_id = ri.item_id AND p.code = 'pudding' ) "pudding" , ( SELECT sm.string FROM item_content ic JOIN content c ON ic.content_id = c.id JOIN property p ON c.property_id = p.id JOIN string_matter sm ON sm.content_id = c.id WHERE ic.item_id = ri.item_id AND p.code = 'bumper' ) "bumper" , ( SELECT sm.string FROM item_content ic JOIN content c ON ic.content_id = c.id JOIN property p ON c.property_id = p.id JOIN string_matter sm ON sm.content_id = c.id WHERE ic.item_id = ri.item_id AND p.code = 'train' ) "train" , ( SELECT sm.string FROM item_content ic JOIN content c ON ic.content_id = c.id JOIN property p ON c.property_id = p.id JOIN string_matter sm ON sm.content_id = c.id WHERE ic.item_id = ri.item_id AND p.code = 'alligator' ) "alligator" , ( SELECT sm.string FROM item_content ic JOIN content c ON ic.content_id = c.id JOIN property p ON c.property_id = p.id JOIN string_matter sm ON sm.content_id = c.id WHERE ic.item_id = ri.item_id AND p.code = 'trousers' ) "trousers" , ( SELECT dm.digital FROM item_content ic JOIN content c ON ic.content_id = c.id JOIN property p ON c.property_id = p.id JOIN digital_matter dm ON dm.content_id = c.id WHERE ic.item_id = ri.item_id AND p.code = 'bowl' ) "bowl" , ( SELECT dm.digital FROM item_content ic JOIN content c ON ic.content_id = c.id JOIN property p ON c.property_id = p.id JOIN digital_matter dm ON dm.content_id = c.id WHERE ic.item_id = ri.item_id AND p.code = 'cabbage' ) "cabbage" , ( SELECT dm.digital FROM item_content ic JOIN content c ON ic.content_id = c.id JOIN property p ON c.property_id = p.id JOIN digital_matter dm ON dm.content_id = c.id WHERE ic.item_id = ri.item_id AND p.code = 'ship' ) "ship" , ( SELECT dm.digital FROM item_content ic JOIN content c ON ic.content_id = c.id JOIN property p ON c.property_id = p.id JOIN digital_matter dm ON dm.content_id = c.id WHERE ic.item_id = ri.item_id AND p.code = 'undershirt' ) "undershirt" , ( SELECT dm.digital FROM item_content ic JOIN content c ON ic.content_id = c.id JOIN property p ON c.property_id = p.id JOIN digital_matter dm ON dm.content_id = c.id WHERE ic.item_id = ri.item_id AND p.code = 'simmer' ) "simmer" , ( SELECT dm.digital FROM item_content ic JOIN content c ON ic.content_id = c.id JOIN property p ON c.property_id = p.id JOIN digital_matter dm ON dm.content_id = c.id WHERE ic.item_id = ri.item_id AND p.code = 'squirrel' ) "squirrel" , ( SELECT dm.digital FROM item_content ic JOIN content c ON ic.content_id = c.id JOIN property p ON c.property_id = p.id JOIN digital_matter dm ON dm.content_id = c.id WHERE ic.item_id = ri.item_id AND p.code = 'ceiling' ) "ceiling" , ( SELECT dm.digital FROM item_content ic JOIN content c ON ic.content_id = c.id JOIN property p ON c.property_id = p.id JOIN digital_matter dm ON dm.content_id = c.id WHERE ic.item_id = ri.item_id AND p.code = 'legs' ) "legs" , ( SELECT dm.digital FROM item_content ic JOIN content c ON ic.content_id = c.id JOIN property p ON c.property_id = p.id JOIN digital_matter dm ON dm.content_id = c.id WHERE ic.item_id = ri.item_id AND p.code = 'jacket' ) "jacket" , ( SELECT dm.digital FROM item_content ic JOIN content c ON ic.content_id = c.id JOIN property p ON c.property_id = p.id JOIN digital_matter dm ON dm.content_id = c.id WHERE ic.item_id = ri.item_id AND p.code = 'book' ) "book" , ( SELECT dm.digital FROM item_content ic JOIN content c ON ic.content_id = c.id JOIN property p ON c.property_id = p.id JOIN digital_matter dm ON dm.content_id = c.id WHERE ic.item_id = ri.item_id AND p.code = 'stomach' ) "stomach" , ( SELECT dm.digital FROM item_content ic JOIN content c ON ic.content_id = c.id JOIN property p ON c.property_id = p.id JOIN digital_matter dm ON dm.content_id = c.id WHERE ic.item_id = ri.item_id AND p.code = 'clock' ) "clock" , ( SELECT dm.digital FROM item_content ic JOIN content c ON ic.content_id = c.id JOIN property p ON c.property_id = p.id JOIN digital_matter dm ON dm.content_id = c.id WHERE ic.item_id = ri.item_id AND p.code = 'furniture' ) "furniture" FROM rubric_item ri WHERE ri.rubric_id = ( SELECT id FROM rubric WHERE code = 'tea' ) ;
ビューは直接リクエストよりも少し速く動作し、ビューを使用するとコードの行数が減ります。 要求を簡単に1000行以上に拡張できる場合、ビューへのアクセスは1行だけです。DBMSサーバーとアプリケーションサーバー間で送信するデータが少なくなり、DBMSパーサーの作業が少なくなります。
追加のアクションなしですぐにデータを変更すると、ビューに反映されます。
特性（属性）のリストを変更する場合、ビューを再作成する必要があります。
EAVモデルの使用
冷蔵庫や自動車部品を販売している通常のオンラインストアでは、EAVの使用は正当化される以上のものであり、アイテムごとに特性テーブルに新しいテーブルや追加の列を作成しません。 さらに、各見出しには通常100個以下のアイテムがあり、各見出しに対して個別の実体化表現を作成することをお勧めします。数百の他の行を検索することは、数百万の属性行を検索するよりも高速です。
特性の値の変更は、一生に一度-商品アイテムを追加した瞬間に発生します。 特性のリストの変更は、ファッションの各ターンで発生します-6ヶ月に1回以下です。 商品アイテムの追加-各配送-週に1回以下。
つまり、より頻繁に読み取られ、ほとんど変更されないデータがあります。
EAVモデルは、この用途に完全に適合します。 マテリアライズドビューによる最適化により、読み取り速度は通常のテーブルからの読み取り速度以上になります。
ボーナス
前の記事で、EAVへのリクエストを生成するためのクラスを共有することを約束しました。 私はまだそれらを書くための手を手に入れていません
が 、
これがすべてであるリポジトリを共有することができます、唯一のものはドキュメントがありません。
誰かがこの善意の墓地に対処することを約束するなら、私に連絡してください、連絡します（私の連絡先はプロファイルに示されています、VKで書くほうが良いです）。
実験を繰り返したい場合
最適化のテストを伴う私の集合的農業が疑わしいと思われる人のために、私は自分でテストを行うことを提案します。 このためのすべてのスクリプト
はリポジトリに投稿されます 。
アクションのシーケンスは
use_case.sqlで説明されてい
ます 。
また、束を使用して：
- noun.txt
- adjective.txt
- get_pdo.php
- words_input.php
- data_generation.php
- ddl_generation.php
独自のデータセットを作成して、実験を行うことができます。
スクリプトには多くのコードがありません（3つのphpファイルにつき1,000行）。好きなようにカットします。
コードはPHP 7.1およびPostgreSQL 10.1で機能しますが、ファイル処理後は以前のバージョンでも機能します。
おわりに
気に入ったら、チャンネルに登録して、コメントを書いてください。