物理データベースモデルの作成製品設計物理データベースモデルの作成：SQL Serverの製品設計分割テーブル

こんばんは/日/朝の親愛なるhabraloudi！私たちのお気に入りのオープンソースのRDBMS PostgreSQLについてのブログを開発および補足し続けます。それは奇跡的であったので、今日のトピックのテーマはここでは決して上がったことがないことがわかった。私は、PostgreSQLに分割することは非常によく、マニュアルに記載されていることを言わなければならないが、それは私を停止します？）。

前書き

一般に、分割の下で、一般に、それらはいくつかの技術ではなく、データベースの設計への取り組みを理解しており、これはDBMSがいわゆる維持を開始する前に長く現れました。パーティション化されたテーブル。その考えは非常に単純です - テーブルを小さいサイズのいくつかの部分に分割します。水平方向と垂直方向のパーティション化の2つの亜種があります。

水平方向の分割

テーブルの一部は異なる行を含む。特定の抽象アプリケーション - ログのログの表を入力します。 2009年1月のログ、もう一方 - 2009年2月などの丸太に分割することができます。

垂直方向の分割

テーブルの一部には異なる列が含まれています。水平よりもやや複雑な垂直分割（実際に正当化されている場合）の申請を検索します。球形の馬として、私はこのオプションを考慮することを提案します。ニューステーブルにはID、ShortText、LongText、およびLongTextフィールドが最初の2つよりずっと使用されています。この場合、列のニューステーブルを分割することは理にかなっています（それぞれShortTextとLongTextの2つのテーブルを作成し、両方の列を含む関連プライマリキー+ビューニュースの作成）。したがって、ニュースの説明だけが必要な場合は、DBMSはテキストニュース全体でもディスクから読み取る必要はありません。

最近のDBMSでの分割をサポートします

最近のDBMSのほとんどは、テーブルの分割を1つの形式でもサポートしています。

オラクル - 8バージョンでのパーティションをサポートします。一方でセクションを操作することは非常に簡単です（一般に、あなたはそれらについて考えることができない、あなたは通常のテーブル*を使って作業することができません）、そして他のものは非常に柔軟です。セクションは「サブパーティション」、削除、分割、転送に壊れることがあります。区分化表を索引付けするためのさまざまなオプションがサポートされています（グローバル索引分割索引）。膨大な説明への言及。
Microsoft SQL Server - 最近、シングルサポートが表示されました（2005年）。「まあ、ついに!! :)」、「作品、すべてが大丈夫だと思われる」という最初の印象です。 MSDNに関する文書化
ミーズル - バージョン5.1からのサポート。
等…

* -etraは、もちろん、標準の困難なセットがあります - 時間の経過とともに新しいセクションを作成し、捨てることなどがありますが、それでもどういうわけかすべて簡単で理解できます。

PostgreSQLでの分割

PostgreSQL内のテーブルの分割は、他のデータベースからの販売が多少異なります。パーティションの基礎は、テーブルの継承（排他的にPostgreSQLのもの）の継承です。つまり、メインテーブル（マスターテーブル）が必要であり、そのセクションはHEIRテーブルになります。実際のタスクの例についての分割を現実に近づけることを検討します。

問題の定式化

データベースは、サイト/サイトの訪問者のデータを収集および分析するために使用されます。データボリュームは、分割について考えるのに十分な大きさです。分析するとき、ほとんどの場合データは最終日に使用されます。
1.基本テーブルを作成します。

テーブルAnalytics.Eventsを作成します。

USER_ID UUID NOT NULL、
event_type_id smallint not null、
event_timeタイムスタンプデフォルト（）NOT NULL、
URL VARCHAR（1024）NOT NULL、
紹介varchar（1024）、
IP Inet Not Null
);

2. Event_timeフィールドで日々分割します。毎日新しいセクションを作成します。 alaytics.events_ddmmyyyyに従って、セクションを呼び出します。たとえば、2010年1月1日のセクションです。

テーブルAnalytics.Events_01012010を作成します。
EVENT_ID BIGINTデフォルトNEXTVAL（ "Analytics.SEQ_EVENTS"）主キー、
チェック（EVENT_TIME\u003e \u003d TIMESTAMP "2010-01-01 00:00:00"とevent_time< TIMESTAMP "2010-01-02 00:00:00" )
）継承（Analytics.Events）;
*このソースコードは、ソースコードの蛍光ペンで強調表示されていました。

セクションを作成するときは、event_idフィールド（主キーが継承されていない）を明示的に指定し、挿入しすぎないようにevent_timeフィールドにチェック制約を作成します。

3. Event_timeフィールドにインデックスを作成します。セクションのテーブルを分割するときは、イベントテーブルへのほとんどの要求がEVENT_TIMEフィールドの条件を使用することを意味します。そのため、このフィールドのインデックスは非常に役立ちます。

Analytics.Events_01012010にinterytics.events_01012010にindent_01012010_IDXを作成する（event_time）。
*このソースコードは、ソースコードの蛍光ペンで強調表示されていました。

4.メインテーブルに挿入するときに、データがそれらを対象としたセクションであることを確認します。これを行うには、次のFINTを作成します - データストリームを管理するトリガーを作成します。

関数を作成または置換するAnalytics.Events_InSert_trigger（）
トリガーを$$として返します
ベギン。
if（新しい.event_time\u003e \u003dタイムスタンプ "2010-01-01 00:00:00"
新しい.event_time。< TIMESTAMP "2010-01-02 00:00:00" ) THEN
Analytics.Events_01012010値に挿入（新規。*）;
そうしないと。
例外を上げる「日付％が範囲外です。Analytics.Events_Insert_Triggerを修正する、新しい.event_time;
if;
nullを返します。
終わり;
$$
言語PLPGSQL;
*このソースコードは、ソースコードの蛍光ペンで強調表示されていました。

トリガーイベントを作成します._before_insert。
Analytics.Eventsに挿入する前に
各行の実行プロシージャのAnalytics.Events_InSert_Trigger（）;
*このソースコードは、ソースコードの蛍光ペンで強調表示されていました。

5.すべての準備ができている、私達は現在パーティション化されたテーブルAnalytics.Eventsを持っています。データを激しく分析することができます。ちなみに、誤ったデータからセクションを保護するだけではなく作成された制約を確認します。 PostgreSQLは、要求計画を作成するときにそれらを使用することができます（ただし、event_timeの生活索引では、winningsが最小値にする）、constraint_exclusionディレクティブを使用するのに十分です。

set constraint_exclusion \u003d on;
event_time\u003e current_dateを選択するAnalytics.Eventsから*を選択します。
*このソースコードは、ソースコードの蛍光ペンで強調表示されていました。

最初の部分の終わり

だから私たちは何を持っていますか？どれどれ：
1.イベントテーブル、セクションに分割されて、過去1日にわたる利用可能なデータの分析がより簡単かつ速くなります。
2.どういうわけか維持する必要があるという実現の恐怖は、それに応じてトリガを変更することを忘れないでください。

パーティション化されたテーブルを使用した簡単で不注意な作業については、2番目の部分で説明します。

UPD1：パーティション分割を置き換えました
UPD2：
残念ながら、持っていない読者の1つのコメントによると、Habréのアカウント：
継承を伴うと、いくつかのモーメントが接続されています。これは設計時に考慮されるべきです。セクションは、それらの列に主キーと外部キーを継承しません。つまり、セクションを作成するときは、セクション列に主キーと外部キーを明示的に作成する必要があります。私自身から、パーティションテーブルの列に外部キーを作成することは最良の方法ではありません。ほとんどの場合、パーティション化テーブルは「事実の表」で、「ディメンション」テーブルを参照しています。

OracleのDBMSには機会があります 分割ビュー。主なアイデアは簡単です。物理テーブルをいくつかの表に分割する（方法を使用してオプション）分割テーブル）パーティションの基準に従って、より生産的な要求の処理を行います。パーティションの基準が求められます 述語分割。次に、ヘルプがこれらのテーブルへのアクセスが容易になるようにビューを作成して設定できます。プレゼンテーションセクションは、値の範囲に従って決定されます。キーパーティション化。プレゼンテーションセクションからデータをサンプリングするための値の範囲を使用する要求は、値の範囲に対応するセクションのみにアクセスします。キーパーティション化.

プレゼンテーションセクションは述語によって決定できます分割チェックまたはWHEREオファーを使用して指定します。我々は、前のセクションで考慮された複数修正された「販売」表（販売）の例について、両方の受容を適用できる方法を示します。暦年の販売データが4つの別々のテーブルに配置されているとする。それぞれがQ1_SALES、Q2_SALES、Q3_SALES、およびQ4_SALESの四半期に対応しています。

例20.14.

チェックの制限付き。 ALTER TABLEコマンドを使用して、各テーブルの「SALE」列（S_DATE）に制限を追加して、その行が年の四半期の1つに対応するようにします。その後、作成された販売表現により、これらのテーブルに連絡することができます。

ALTER TABLE Q1_SALES制約C0チェック（「1月1日から2002」の間のS_DATEと「MAR-31-2002」）。 ALTER TABLE Q2_SALES CONSTRAINT C1チェック（「4例1-2002」と「JUN-30-2002」の間のS_DATE）。 ALTER TABLE Q3_SALES制約C2チェック（「7月1-2002」と「SEP-30-2002」の間のS_DATE）。 ALTER TABLE Q4_SALES制約C3チェック（「10月1-2002」と「DEC-31-2002」の間のS_DATE）。 Q1Sales Union AlimからSelect *としてsales_vを作成します。Q3_Sales Union Alim Select *からQ4_SALESのSELECT *を選択します。

これの利点分割表現チェック制限の述語は、各クエリ文字列に対して推定されないことです。そのような述語は、述語基準に対応しない行テーブルへの挿入を除外します。述語に対応する行分割データベースから早く抽出されます。

例20.15.

表現の分割 WHEREオファーを使う上記の例と同じテーブルのアイデアを作成します。

CREATE VIEW SALES_V AS SELECT * FROM Q1_SALES WHERE "JAN-1から2002" AND "MAR-31から2002" UNION ALL SELECT BETWEEN S_DATE * Q2_SALES FROM WHERE S_DATE BETWEEN "APR-1から2002" AND "6月 - 30から2002まで" S_DATE "10月 - 1から2002" と "12月 - 31から2002まで" の間Q4_SalesからS_DATE "7月-1から2002" と "SEP-30から2002" 連合すべて選択*とのQ3_Sales FROM UNION ALL SELECT *;

方法分割表現いくつかの欠点がある場所の助けを借りて。まず、基準分割クエリによってカバーされているすべてのセクションのすべての行の実行中にチェックします。第二に、ユーザはそのセクションにない文字列を誤って挿入することができます。第3四半期の第1四半期に関連する文字列を挿入し、それはこれらの四半期の間違ったデータのサンプルにつながります。

このレセプションはチェックチェックの使用と比較して、尊厳があります。リモートデータベース上のWhere述部に対応するセクションを配置できます。配管の定義の断片を以下に示す。

作成を決定すると、次の要因を覚えておく必要があります。

データを削除し、セクションレベルで作業し、基本表全体ではありません。
セクションの1つにアクセスすると、他のセクションのデータに対してもアクションはありません。
Oracle DBMSには必要な内蔵認識機能があります。分割されたアイデア.
表現の分割多数の履歴データを含むテーブルを操作するときに非常に便利です。

MS SQL ServerファミリのDBMS内のテーブルの分割

分割テーブルの作成

MS SQL ServerファミリのDBMSでもサポートされています分割テーブル、インデックスおよび表現。ただし、OracleファミリのDBMSとは対照的に、分割 MS SQL ServerファミリのDBMSは、Unified Schemeに従って実行されます。

MS SQL Serverでは、データベース内のすべてのテーブルとインデックスは、ただ1つのセクションのみで構成されていても、パーティション化と見なされます。実際、セクションはテーブルとインデックスの物理アーキテクチャの基本的な組織単位です。つまり、いくつかのセクションを含むテーブルとインデックスの論理アーキテクチャと物理アーキテクチャは、1つのセクションからなるテーブルとインデックスのアーキテクチャを完全に反映しています。

分割テーブルとインデックスは行レベルで一生懸命尋ねられます（分割カラムは、可）及びセクションのアプリケーションの数は、アプリケーション・コードに知る必要がないように単一の入力点（テーブル名またはインデックス名）を介したアクセスを許可されていません。分割基本テーブル、およびその関連インデックスで実行できます。

セクション内の値の各領域には、FOR値ステートメントで定義されている境界があります。販売日が2006年6月23日であれば、文字列はセクション2（P2）に格納されます。

今創造されましたスキーム分割. スキーム分割さまざまなファイルグループにセクションを表示します（名前付き MyFileGroup1、MyFileGroup2、MyFileGroup3、MyFileGroup4次のチームに示すように、

Partition Scheme MyPartitionsChemeをMyPartitionFunctionとして作成する（myFileGroup1、MyFileGroup2、MyFileGroup3、MyFileGroup4）

MyPartitionSchemeは名前ですスキームセクションMyPartitionFunctionの名前が決定します分割機能。このコマンドは、1つ以上のファイルグループに関連付けられているセクションへのデータを表示します。販売列の値（DATE_OF_EVENT DATE）の値（DATE_OF_EVENT DATE）を持つデータがMyFileGroup1に関連付けられています。大規模または1/01/05および1/01/07に等しい値を、この列の行は、myFilegroup2に割り当てられています。値、大きい、または1/01 / 07と1/01/09までの行はMyFileGroup3に関連付けられています。 1/01/09以上の値を持つ他のすべての行はMyFileGroup4に関連付けられています。

境界値の各セットに対して（値の条件で指定されます。パーティショニング機能）セクション数は「境界値の数」+ 1セクションになります。前の作成パーティション方式には、3つの制限と4つのセクションが含まれています。かかわらず、範囲を右または範囲LEFTとセクションが作成されているかどうかの、セクションの数は常にアップテーブルの上に1000個のセクションに、「境界値の数」+ 1になります。

今創造することができますパーティションテーブル事実「販売」（販売）。生き物パーティションテーブル通常のテーブルの作成とはあまり異ならず、名前を参照する必要があります。スキームセクション下のチームに示すように、オン状態で。

テーブルの作成販売（Sales_Chb Bigint Identity（1,1）プライマリ化されていませんNULL、cust_id bigint null、rese_id bigint null、reg_id char（10）null、time_of_event time null、数量整数Not Null、Dec（8）、2）NOT NULL、DATE_OF_EVENT DATE NOT NULL（DATE_OF_EVENT）

名前を定義するスキームセクション設計者はこのテーブルがインデックスであることを示しています。これにより、設計者は、テーブルの全データに基づいて、分割データに基づいてインデックスの構造を設計することができる。パーティション索引の作成区分索引に関する個々のバランスのとれたツリーの作成を伴います。インデックスの分離の結果として、より小さなインデックスが作成されます。管理者BD または、変更中、データの追加と削除中にHDがそれらを提供しやすくなっています。

パーティション索引を作成するときは、作成できます aled または 発行されていないインデックス。整列索引は、区分データデータとの直接接続を意味します。具体的なインデックスの場合、別のものが選択されていますスキーム分割.

これら2つの方法のうち、作成後にデフォルトで選択されている整列索引が好ましいパーティションテーブルインデックスは別のものを指定せずに作成されますスキームセクション。整列索引の使用は、表に追加のセクションを作成するために必要な柔軟性を提供し、特定のセクションに属するものを別のテーブルに転送することもできます。に関連するほとんどのタスクを解決するため分割、インデックスに申し込むだけですスキーム分割テーブル

例。 20.19.

パーティションを作成します非クラスタ化インデックス上に分割された前の例20.18からの表「販売」（販売）。

パーティション方式のindex_primary_left_schemeをパーティションINDEX_LET_PARTITION ALL ALL to（）

次に示すように、インデックス作成コマンドを実行してください。

index_primary_left_scheme（cust_id）上のmultiple_partition（cust_id）で非クラスタ化されたインデックスcl_multiple_partitionを作成します。

その中で非クラスター字インデックスインデックスキーとして、「購入者識別子」列が使用されます（cust_id）。キーパーティション化販売テーブル

決定分割インデックス HDの操作段階で、設計段階またはHD管理者でHDの設計者によって受け入れられました。目的分割インデックスこれはクエリパフォーマンスを提供するか、またはインデックスサポート手順を単純化します。

この記事では、パーティション化されたテーブルにアクセスするときのクエリ実行プランの機能を実証するつもりです。パーティションテーブル（SQL Server 2005でのみ使用可能になっている）とパーティションビュー（SQL Server 2000で使用可能で、まだSQL Server 2005以降で利用可能になっていて、まだ使用可能で、まだ利用可能である）の間に大きな違いがあります。パーティションビューの要求の要求の特徴は、別の記事で説明します。

テーブルの表示

単純なパーティション化テーブルを作成しましょう。

値の範囲（0,10,100）としてパーティション関数PF（INT）を作成する

パーティションPFとしてパーティションスキームPSを作成する（）

pSの表T（a int、b int）を作成する（a）

このスクリプトは4つのセクションを持つテーブルを作成します。 SQL Serverは、表に示すように4つのセクションのそれぞれを識別子に割り当てました。

Ptnid.	値。
1	t.A.<= 0
2	0 < t.a <= 10
3	10 < t.a <= 100
4	100 < t.a

これで、オプティマイザがテーブルスキャンを使用するように強制するような要求の計画を検討しましょう。テーブルスキャン：

...... | -Constantスキャン（値：（（（1））、（（2））、（（3））、（（4））））
....... | -table scan（オブジェクト：（[t]））

上記の計画では、SQL Serverは定数スキャンステートメント内のすべてのセクション識別子を明示的に指定します。これは、テーブルビューを実装し、ネストサイクルを接続するためにデータをオペレータに供給します。外部表からの各値に対して一度ここでは、我々の場合では、「コンスタントスキャン」である（（この場合には、テーブルの完全なビューである）ネストされたサイクルのオペレータは、内部テーブルに沿って通路を行うことを想起すべきです）。したがって、テーブルを4回視聴する。各セクション識別子に1回。

ネストされたサイクルの化合物は、外部表がセクションのIDが格納されている列の値であることを明確に示していることにも注意してください。それは（残念ながら、私たちは時々、この情報に気づくことはありません）実行計画のテキストビューにすぐに表示されませんが、テーブルを表示すると、表示を完了し、スキャンにどの部分を決定するために選択されたセクションの識別子でカラムを使用しています。この情報は常に実行グラフィックで利用可能です（テーブル表示演算子のプロパティを調べる必要があります）、およびクエリ実行プランのXMLビューにあります。