Construire le modèle de base de données physique : concevoir les performances. Création du modèle de base de données physique : partitionnement du serveur SQL Performance Design

Bonsoir/jour/matin cher habralyudi ! Nous continuons à développer et à compléter le blog sur mon rdbms open source préféré Postgresql. Miraculeusement, il se trouve que le sujet du sujet d'aujourd'hui n'a jamais été soulevé ici. Je dois dire que le partitionnement dans postgresql est très bien documenté, mais cela va-t-il m'arrêter ?).

introduction

En général, le partitionnement est généralement compris non pas comme une sorte de technologie, mais plutôt comme une approche de la conception de bases de données qui est apparue bien avant que le SGBD ne commence à prendre en charge ce qu'on appelle. tables partitionnées. L'idée est très simple - diviser la table en plusieurs petites parties. Il existe deux sous-espèces - sectionnement horizontal et vertical.

Cloisonnement horizontal

Certaines parties du tableau en contiennent différentes lignes. Disons que nous avons une table de journal d'une application abstraite - LOGS. Nous pouvons le diviser en parties - une pour les journaux de janvier 2009, une autre pour février 2009, etc.

Coupe verticale

Les parties d'un tableau contiennent différentes colonnes du tableau. Trouver une application pour le partitionnement vertical (quand cela est vraiment justifié) est un peu plus difficile que pour le partitionnement horizontal. En tant que cheval sphérique, je propose de considérer cette option : la table NEWS a les colonnes ID, SHORTTEXT, LONGTEXT, et laisse le champ LONGTEXT être utilisé beaucoup moins souvent que les deux premiers. Dans ce cas, il est logique de diviser la table NEWS en colonnes (créer deux tables pour SHORTTEXT et LONGTEXT, respectivement, liées par des clés primaires + créer une vue NEWS contenant les deux colonnes). Ainsi, lorsque nous n'avons besoin que d'une description de l'actualité, le SGBD n'a pas à lire l'intégralité du texte de l'actualité à partir du disque.

Prise en charge du partitionnement dans les SGBD modernes

La plupart des SGBD modernes prennent en charge le partitionnement de table sous une forme ou une autre.

• Oracle- prend en charge le partitionnement depuis la version 8. D'une part, travailler avec des sections est très simple (vous n'avez pas du tout à y penser, vous travaillez comme avec table ordinaire*), et d'autre part, tout est très flexible. Les sections peuvent être divisées en "sous-partitions", supprimées, divisées, déplacées. Prise en charge différentes variantes indexation d'une table partitionnée (index global, index partitionné). Lien vers une description volumineuse.
• Serveur Microsoft SQL- Le support de partitionnement est apparu récemment (en 2005). La première impression d'utilisation - "Eh bien, enfin !! :)", la seconde - "Ça marche, comme si tout allait bien". documentation msdn
• MySQL- prend en charge depuis la version 5.1. Très bonne description sur Habré
• Etc…

* - mensonges, bien sûr, il y a ensemble standard difficultés - créer une nouvelle section à temps, jeter l'ancienne, etc., mais tout est quand même simple et clair.

Partitionnement dans Postgresql

Le partitionnement de table dans postgresql est légèrement différent dans l'implémentation des autres bases de données. La base du partitionnement est l'héritage de table (une chose inhérente exclusivement à postgresql). C'est-à-dire que nous devons avoir une table maître, et ses sections seront des héritiers de tables. Nous allons considérer le sectionnement à partir de l'exemple d'un problème proche de la réalité.

Formulation du problème

La base de données est utilisée pour collecter et analyser des données sur les visiteurs du/des site(s). Les volumes de données sont suffisamment volumineux pour envisager le partitionnement. Dans la plupart des cas, l'analyse utilise les données du dernier jour.
1. Créez la table principale :

CRÉER UNE TABLE analytics.events

user_id UUID NON NULL,
event_type_id SMALLINT NON NULL,
event_time TIMESTAMP DEFAULT now () NON NULL,
url VARCHAR (1024) NON NULL,
référent VARCHAR (1024),
ip INET NON NULL
);

2. Nous allons partitionner par jour en fonction du champ event_time. Nous allons créer une nouvelle section pour chaque jour. Nous nommerons les sections selon la règle : analytics.events_DDMMYYYY. Voici un exemple de section pour le 1er janvier 2010.

CRÉER UNE TABLE analytics.events_01012010
event_id BIGINT DEFAULT nextval ("analytics.seq_events") CLÉ PRIMAIRE,
CHECK (event_time> = TIMESTAMP "2010-01-01 00:00:00" ET event_time< TIMESTAMP "2010-01-02 00:00:00" )
) HÉRITE (analytics.events);

* Ce code source a été mis en évidence avec Source Code Highlighter.

Lors de la création d'une section, nous définissons explicitement le champ event_id (PRIMARY KEY n'est pas hérité) et créons une CHECK CONSTRAINT sur le champ event_time, afin de ne pas en insérer trop.

3. Créez un index sur le champ event_time. Lors de la division de la table en sections, nous supposons que la plupart des requêtes sur la table des événements utiliseront la condition sur le champ event_time, donc un index sur ce champ nous aidera beaucoup.

CRÉER UN INDEX events_01012010_event_time_idx ON analytics.events_01012010 EN UTILISANT btree (event_time);

* Ce code source a été mis en évidence avec Source Code Highlighter.

4. Nous voulons nous assurer que lorsqu'elles sont insérées dans le tableau principal, les données apparaîtront dans la section qui leur est destinée. Pour ce faire, nous faisons l'astuce suivante - nous créons un déclencheur qui contrôlera les flux de données.

CRÉER OU REMPLACER UNE FONCTION analytics.events_insert_trigger ()
DÉCLENCHEMENT DES RETOURS COMME $$
COMMENCER
SI (NOUVEAU .event_time> = TIMESTAMP "2010-01-01 00:00:00" ET
NOUVEAU .event_time< TIMESTAMP "2010-01-02 00:00:00" ) THEN
INSÉRER DANS LES VALEURS analytics.events_01012010 (NOUVEAU. *);
AUTRE
AUGMENTEZ L'EXCEPTION "La date% est hors plage. Corrigez analytics.events_insert_trigger", NOUVEAU .event_time;
FIN SI;
RETOUR NULL ;
FINIR;
$$
LANGUE plpgsql;

* Ce code source a été mis en évidence avec Source Code Highlighter.

CREATE TRIGGER events_before_insert
AVANT D'INSÉRER SUR analytics.events
POUR CHAQUE PROCÉDURE D'EXÉCUTION DE LIGNE analytics.events_insert_trigger ();

* Ce code source a été mis en évidence avec Source Code Highlighter.

5. Tout est prêt, nous avons maintenant une table analytics.events partitionnée. Nous pouvons commencer à analyser furieusement ses données. Soit dit en passant, nous avons créé des contraintes CHECK non seulement pour protéger les sections des données incorrectes. Postgresql peut les utiliser lors de l'élaboration d'un plan de requête (bien qu'avec un index en direct sur event_time, le gain sera minime), utilisez simplement la directive restrict_exclusion :

SET contrainte_exclusion = on ;
SELECT * FROM analytics.events WHERE event_time> CURRENT_DATE ;

* Ce code source a été mis en évidence avec Source Code Highlighter.

Fin de la première partie

Alors qu'est-ce que nous avons? Détaillons point par point :
1. Tableau des événements, divisé en sections, l'analyse des données disponibles pour le dernier jour devient plus facile et plus rapide.
2. L'horreur de réaliser que tout cela doit être pris en charge d'une manière ou d'une autre, pour créer des sections à temps, sans oublier de modifier le déclencheur en conséquence.

Je vais vous dire à quel point il est facile et insouciant de travailler avec des tables partitionnées dans la deuxième partie.

UPD1 : remplacement du partitionnement par le partitionnement
UPD2 :
D'après la remarque d'un des lecteurs, qui, malheureusement, n'a pas de compte sur Habré :
Il y a plusieurs choses à considérer avec l'héritage lors de la conception. Les partitions n'héritent pas de la clé primaire et des clés étrangères sur leurs colonnes. Autrement dit, lors de la création d'une section, vous devez explicitement créer des clés PRIMARY KEY et FOREIGN KEY pour les colonnes de la section. Pour ma part, je note que créer une FOREIGN KEY sur les colonnes d'une table partitionnée n'est pas le meilleur moyen. Dans la plupart des cas, une table partitionnée est une « table de faits » et elle-même fait référence à la « dimension » de la table.

Bonsoir/jour/matin cher habralyudi ! Nous continuons à développer et à compléter le blog sur mon rdbms open source préféré Postgresql. Miraculeusement, il se trouve que le sujet du sujet d'aujourd'hui n'a jamais été soulevé ici. Je dois dire que le partitionnement dans postgresql est très bien documenté, mais cela va-t-il m'arrêter ?).

introduction

En général, le partitionnement est généralement compris non pas comme une sorte de technologie, mais plutôt comme une approche de la conception de bases de données qui est apparue bien avant que le SGBD ne commence à prendre en charge ce qu'on appelle. tables partitionnées. L'idée est très simple - diviser la table en plusieurs petites parties. Il existe deux sous-espèces - sectionnement horizontal et vertical.

Cloisonnement horizontal

Certaines parties du tableau en contiennent différentes lignes. Disons que nous avons une table de journal d'une application abstraite - LOGS. Nous pouvons le diviser en parties - une pour les journaux de janvier 2009, une autre pour février 2009, etc.

Coupe verticale

Les parties d'un tableau contiennent différentes colonnes du tableau. Trouver une application pour le partitionnement vertical (quand cela est vraiment justifié) est un peu plus difficile que pour le partitionnement horizontal. En tant que cheval sphérique, je propose de considérer cette option : la table NEWS a les colonnes ID, SHORTTEXT, LONGTEXT, et laisse le champ LONGTEXT être utilisé beaucoup moins souvent que les deux premiers. Dans ce cas, il est logique de diviser la table NEWS en colonnes (créer deux tables pour SHORTTEXT et LONGTEXT, respectivement, liées par des clés primaires + créer une vue NEWS contenant les deux colonnes). Ainsi, lorsque nous n'avons besoin que d'une description de l'actualité, le SGBD n'a pas à lire l'intégralité du texte de l'actualité à partir du disque.

Prise en charge du partitionnement dans les SGBD modernes

La plupart des SGBD modernes prennent en charge le partitionnement de table sous une forme ou une autre.

• Oracle- prend en charge le partitionnement depuis la version 8. D'une part, travailler avec des sections est très simple (vous n'avez pas du tout à y penser, vous travaillez comme avec une table ordinaire *), et d'autre part, tout est très flexible. Les sections peuvent être divisées en "sous-partitions", supprimées, divisées, déplacées. Différentes options d'indexation d'une table partitionnée (index global, index partitionné) sont supportées. Lien vers une description volumineuse.
• Serveur Microsoft SQL- Le support de partitionnement est apparu récemment (en 2005). La première impression d'utilisation - "Eh bien, enfin !! :)", la seconde - "Ça marche, comme si tout allait bien". documentation msdn
• MySQL- prend en charge depuis la version 5.1.
• Etc…

* -C'est un mensonge, bien sûr, il existe un ensemble standard de difficultés - créer une nouvelle section à temps, jeter l'ancienne, etc., mais tout de même, tout est en quelque sorte simple et compréhensible.

Partitionnement dans Postgresql

Le partitionnement de table dans postgresql est légèrement différent dans l'implémentation des autres bases de données. La base du partitionnement est l'héritage de table (une chose inhérente exclusivement à postgresql). C'est-à-dire que nous devons avoir une table maître, et ses sections seront des héritiers de tables. Nous allons considérer le sectionnement à partir de l'exemple d'un problème proche de la réalité.

Formulation du problème

La base de données est utilisée pour collecter et analyser des données sur les visiteurs du/des site(s). Les volumes de données sont suffisamment volumineux pour envisager le partitionnement. Dans la plupart des cas, l'analyse utilise les données du dernier jour.
1. Créez la table principale :

CRÉER UNE TABLE analytics.events

user_id UUID NON NULL,
event_type_id SMALLINT NON NULL,
event_time TIMESTAMP DEFAULT now () NON NULL,
url VARCHAR (1024) NON NULL,
référent VARCHAR (1024),
ip INET NON NULL
);

2. Nous allons partitionner par jour en fonction du champ event_time. Nous allons créer une nouvelle section pour chaque jour. Nous nommerons les sections selon la règle : analytics.events_DDMMYYYY. Voici un exemple de section pour le 1er janvier 2010.

CRÉER UNE TABLE analytics.events_01012010
event_id BIGINT DEFAULT nextval ("analytics.seq_events") CLÉ PRIMAIRE,
CHECK (event_time> = TIMESTAMP "2010-01-01 00:00:00" ET event_time< TIMESTAMP "2010-01-02 00:00:00" )
) HÉRITE (analytics.events);

* Ce code source a été mis en évidence avec Source Code Highlighter.

Lors de la création d'une section, nous définissons explicitement le champ event_id (PRIMARY KEY n'est pas hérité) et créons une CHECK CONSTRAINT sur le champ event_time, afin de ne pas en insérer trop.

3. Créez un index sur le champ event_time. Lors de la division de la table en sections, nous supposons que la plupart des requêtes sur la table des événements utiliseront la condition sur le champ event_time, donc un index sur ce champ nous aidera beaucoup.

CRÉER UN INDEX events_01012010_event_time_idx ON analytics.events_01012010 EN UTILISANT btree (event_time);

* Ce code source a été mis en évidence avec Source Code Highlighter.

4. Nous voulons nous assurer que lorsqu'elles sont insérées dans le tableau principal, les données apparaîtront dans la section qui leur est destinée. Pour ce faire, nous faisons l'astuce suivante - nous créons un déclencheur qui contrôlera les flux de données.

CRÉER OU REMPLACER UNE FONCTION analytics.events_insert_trigger ()
DÉCLENCHEMENT DES RETOURS COMME $$
COMMENCER
SI (NOUVEAU .event_time> = TIMESTAMP "2010-01-01 00:00:00" ET
NOUVEAU .event_time< TIMESTAMP "2010-01-02 00:00:00" ) THEN
INSÉRER DANS LES VALEURS analytics.events_01012010 (NOUVEAU. *);
AUTRE
AUGMENTEZ L'EXCEPTION "La date% est hors plage. Corrigez analytics.events_insert_trigger", NOUVEAU .event_time;
FIN SI;
RETOUR NULL ;
FINIR;
$$
LANGUE plpgsql;

* Ce code source a été mis en évidence avec Source Code Highlighter.

CREATE TRIGGER events_before_insert
AVANT D'INSÉRER SUR analytics.events
POUR CHAQUE PROCÉDURE D'EXÉCUTION DE LIGNE analytics.events_insert_trigger ();

* Ce code source a été mis en évidence avec Source Code Highlighter.

5. Tout est prêt, nous avons maintenant une table analytics.events partitionnée. Nous pouvons commencer à analyser furieusement ses données. Soit dit en passant, nous avons créé des contraintes CHECK non seulement pour protéger les sections des données incorrectes. Postgresql peut les utiliser lors de l'élaboration d'un plan de requête (bien qu'avec un index en direct sur event_time, le gain sera minime), utilisez simplement la directive restrict_exclusion :

SET contrainte_exclusion = on ;
SELECT * FROM analytics.events WHERE event_time> CURRENT_DATE ;

* Ce code source a été mis en évidence avec Source Code Highlighter.

Fin de la première partie

Alors qu'est-ce que nous avons? Détaillons point par point :
1. Tableau des événements, divisé en sections, l'analyse des données disponibles pour le dernier jour devient plus facile et plus rapide.
2. L'horreur de réaliser que tout cela doit être pris en charge d'une manière ou d'une autre, pour créer des sections à temps, sans oublier de modifier le déclencheur en conséquence.

Je vais vous dire à quel point il est facile et insouciant de travailler avec des tables partitionnées dans la deuxième partie.

UPD1 : remplacement du partitionnement par le partitionnement
UPD2 :
D'après la remarque d'un des lecteurs, qui, malheureusement, n'a pas de compte sur Habré :
Il y a plusieurs choses à considérer avec l'héritage lors de la conception. Les partitions n'héritent pas de la clé primaire et des clés étrangères sur leurs colonnes. Autrement dit, lors de la création d'une section, vous devez explicitement créer des clés PRIMARY KEY et FOREIGN KEY pour les colonnes de la section. Pour ma part, je note que créer une FOREIGN KEY sur les colonnes d'une table partitionnée n'est pas le meilleur moyen. Dans la plupart des cas, une table partitionnée est une « table de faits » et elle-même fait référence à la « dimension » de la table.

Au cours de travaux sur de grandes tables, nous rencontrons constamment des problèmes de performance de leur maintenance et de mise à jour des données. Le partitionnement est l'une des solutions les plus productives et pratiques aux problèmes émergents.
En termes généraux, le partitionnement est la division d'une table ou d'un index en blocs. Selon le paramètre de partitionnement, les blocs peuvent être des tailles différentes, peuvent être stockés dans différents groupes de fichiers et fichiers.
Le partitionnement présente à la fois des avantages et des inconvénients.
Les avantages sont bien décrits sur le site. Microsoft, je vais donner un extrait :

« Le partitionnement de tables ou d'index volumineux peut offrir les avantages suivants en matière de gestion et de performances.

• Cela permet de migrer et d'accéder à des sous-ensembles de données rapidement et efficacement tout en maintenant l'intégrité de l'ensemble de données. Par exemple, une opération telle que le chargement de données d'OLTP vers système OLAP, est exécuté en secondes, et non en minutes et en heures, comme dans le cas des données non partitionnées.
• Les opérations de service peuvent être effectuées plus rapidement avec une ou plusieurs sections. Les opérations sont plus efficaces car elles sont effectuées uniquement sur des sous-ensembles de données et non sur la table entière. Par exemple, vous pouvez compresser des données dans une ou plusieurs partitions, ou reconstruire une ou plusieurs partitions d'index.
• Vous pouvez améliorer la vitesse des requêtes en fonction des requêtes qui s'exécutent fréquemment dans votre configuration matérielle. Par exemple, l'optimiseur de requêtes peut effectuer des requêtes d'équiconnexion plus rapides sur deux ou plusieurs tables partitionnées si les tables partagent les mêmes colonnes de partitionnement, car les partitions elles-mêmes peuvent être jointes.

Le tri des données pour les opérations d'E/S dans SQL Server trie d'abord les données en partitions. SQL Server ne peut accéder qu'à un seul disque à la fois, ce qui peut dégrader les performances. Pour accélérer le tri des données, il est recommandé de répartir les fichiers de données en partitions sur plusieurs disques durs en créant un RAID. De cette façon, même si les données sont triées en partitions, SQL Server pourra accéder simultanément à tous les disques durs de chaque partition.
De plus, vous pouvez améliorer les performances en appliquant des verrous au niveau de la partition plutôt qu'au niveau de la table entière. Cela peut réduire le nombre de conflits de verrouillage pour la table.».

Les inconvénients incluent la complexité de l'administration et du maintien du fonctionnement des tables partitionnées.

Nous ne nous attarderons pas sur la mise en place du partitionnement, puisque ce problème est très bien décrit sur le site de Microsoft.

Au lieu de cela, nous essaierons de montrer un moyen d'optimiser le fonctionnement des tables partitionnées, ou plutôt, nous montrerons le moyen optimal (à notre avis) de mettre à jour les données pour n'importe quelle période de temps.

Le gros avantage d'une table partitionnée est la délimitation physique de ces partitions. Cette propriété nous permet d'échanger des sections entre elles ou avec n'importe quelle autre table.
Pour une mise à jour normale des données à l'aide d'une fenêtre glissante (par exemple, pour un mois), nous devons suivre les étapes suivantes :

1. Trouvez les lignes requises dans un grand tableau ;
2. Supprimez les lignes trouvées de la table et de l'index ;
3. Insérez de nouvelles lignes dans la table, mettez à jour l'index.

Lorsqu'on stocke des milliards de lignes dans un tableau, ces opérations seront assez longues, mais on peut se limiter à pratiquement une action : il suffit de remplacer la section souhaitée par un tableau (ou section) préparé à l'avance. Dans ce cas, nous n'avons pas besoin de supprimer ou d'insérer des lignes, et nous devons également mettre à jour l'index sur l'ensemble de la grande table.

Passons des paroles aux actes et montrons comment les mettre en œuvre.

1. Tout d'abord, configurez une table partitionnée comme décrit dans l'article ci-dessus.
2. Créez les tables nécessaires à l'échange.

Pour mettre à jour les données, nous avons besoin d'une mini-copie de la table cible. Il s'agit d'une mini-copie car elle stockera les données qui doivent être ajoutées à la table cible, c'est-à-dire données en seulement 1 mois. Vous aurez également besoin d'une troisième table vide pour mettre en œuvre l'échange de données. Pourquoi c'est nécessaire - j'expliquerai plus tard.

Il y a des conditions strictes pour la mini-copie et la table d'échange :

• Les deux tables doivent exister avant d'utiliser l'instruction SWITCH. Avant d'effectuer une opération de commutation, la table à partir de laquelle la partition est déplacée (table source) et la table recevant la partition (table cible) doivent exister dans la base de données.
• La section de réception doit exister et doit être vide. Si une table est ajoutée en tant que partition à une table partitionnée existante, ou si une partition est déplacée d'une table partitionnée à une autre, la partition secondaire doit exister et être vide.
• Une table de recherche non partitionnée doit exister et doit être vide. Si la partition est destinée à former une seule table non partitionnée, la table qui reçoit la nouvelle partition doit exister et être une table non partitionnée vide.
• Les sections doivent provenir de la même colonne. Si une partition est basculée d'une table partitionnée à une autre, alors les deux tables doivent être partitionnées sur la même colonne.
• Les tables source et destination doivent se trouver dans le même groupe de fichiers. Les tables source et cible de l'instruction ALTER TABLE ... SWITCH doivent être stockées dans le même groupe de fichiers, ainsi que leurs colonnes de grande valeur. Tous les index, sections d'index ou vues de section indexées correspondants doivent également être stockés dans le même groupe de fichiers. Cependant, il peut différer du groupe de fichiers pour les tables correspondantes ou d'autres index pertinents.

Permettez-moi d'expliquer les limites avec notre exemple :

1. La mini-copie de la table doit être partitionnée par la même colonne que la cible. Si la mini-copie est une table non partitionnée, elle doit être stockée dans le même groupe de fichiers que la partition à remplacer.

2. La table d'échange doit être vide et doit également être partitionnée par la même colonne ou doit être stockée dans le même groupe de fichiers.

3. Nous mettons en œuvre l'échange.

Nous avons maintenant les éléments suivants :
Table avec des données pour tous les temps (ci-après Table_A)
Tableau avec données pour 1 mois (ci-après Tableau_B)
Table vide(ci-après Tableau_C)

Tout d'abord, nous devons savoir dans quelle section nous stockons les données.
Vous pouvez le découvrir en demandant :

SÉLECTIONNER
Compter comme
, $ PARTITION. (Dt) as
, rang () sur (ordre par $ PARTITION. (dt))
DE dbo. (aucun verrou)
grouper par $ PARTITION. (dt)

Dans cette requête, nous obtenons des sections qui contiennent des lignes avec des informations. Vous n'avez pas besoin de compter le nombre - nous l'avons fait pour vérifier la nécessité d'échanger des données. Nous utilisons Rank pour pouvoir aller en boucle et mettre à jour plusieurs sections en une seule procédure.

Dès que nous avons découvert dans quelles sections nous stockons les données, elles peuvent être échangées. Disons que les données sont stockées dans la section 1.

Ensuite, vous devez effectuer les opérations suivantes :
Échangez des sections de la table cible avec la table d'échange.
MODIFIER TABLE. PASSEZ LA PARTITION 1 À. PARTITION 1
Nous avons maintenant les éléments suivants :
Il n'y a plus de données dans la table cible dans la section dont nous avons besoin, c'est-à-dire la section est vide
Permuter les partitions de la table cible et de la minicopie
MODIFIER TABLE. PASSEZ LA PARTITION 1 À. PARTITION 1
Nous avons maintenant les éléments suivants :
Les données mensuelles sont apparues dans la table cible, mais maintenant la mini-copie est vide
Effacer ou supprimer la table d'échange.

Si vous avez un index clusterisé sur la table, ce n'est pas non plus un problème. Il doit être créé sur les 3 tables partitionnées sur la même colonne. Lors de la modification des sections, l'index sera automatiquement mis à jour sans reconstruction.

Page 23 sur 33

Partitionnement par plage - Détails des ventes

L'utilisation des données de vente est souvent fluide. En règle générale, les données du mois en cours sont des données opérationnelles ; les données des mois précédents sont dans une large mesure des données destinées à l'analyse. Le plus souvent, l'analyse est effectuée mensuellement, trimestriellement ou annuellement. Étant donné que différents analystes peuvent avoir besoin de quantités importantes de données analytiques différentes en même temps, le partitionnement est le meilleur moyen d'isoler leurs activités. Dans le scénario ci-dessous, les données sont empilées à partir de 283 nœuds et livrées sous forme de deux fichiers ASCII standard. Tous les fichiers sont envoyés au serveur de fichiers central au plus tard à 3 heures du matin le premier jour de chaque mois. La taille des fichiers varie, mais environ 86 000 commandes par mois en moyenne. Chaque commande compte en moyenne 2,63 éléments de ligne, de sorte que les fichiers OrderDetails comptent en moyenne 226 180 lignes. Environ 25 millions de nouvelles commandes et 64 millions de lignes de commande sont ajoutées chaque mois. Le serveur d'analyse de l'historique conserve les données des 2 dernières années. Les données sur deux ans représentent un peu moins de 600 millions de commandes et plus de 1,5 milliard de lignes dans la table OrderDetails. Comme les données sont souvent analysées en comparant les indicateurs des mois du même trimestre, ou des mêmes mois des années précédentes, un partitionnement par plages est choisi. Le mois est sélectionné comme taille de plage.

Sur la base du diagramme 11 (Étapes pour créer une table partitionnée), nous avons décidé de partitionner la table à l'aide d'un partitionnement par plage sur la colonne OrderDate. Nos analystes combinent et analysent principalement les données des 6 derniers mois, ou des 3 derniers mois de l'année en cours et des dernières années (par exemple, janvier-mars 2003 plus janvier-mars 2004). Pour maximiser la stratification des disques, et en même temps isoler la plupart des regroupements de données, plusieurs groupes de fichiers seront situés sur un même disque physique, mais ils seront décalés de six mois afin de réduire le nombre de conflits lors du partage des ressources. Le mois en cours est octobre 2004 et les 283 bureaux distincts gèrent leurs ventes actuelles localement. Le serveur stocke les données d'octobre 2002 à septembre 2004 inclus. Pour tirer parti du nouveau système à 16 processeurs et du SAN (Storage Area Network), les données de chaque mois résideront dans son propre fichier de groupe de fichiers, situé sur un ensemble de miroirs rayés (RAID 1 + 0). La figure 12 illustre le placement des données sur lecteurs logiques NS.

Figure 12 : Tableau partitionné des commandes

Chacun des 12 lecteurs logiques utilise une configuration RAID 1 + 0, de sorte que le nombre total de disques requis pour les tableaux Orders et OrderDetails est de 48. Cependant, le SAN prend en charge jusqu'à 78 disques, les 30 disques restants sont donc utilisés pour le journal des transactions, TempDB. bases du système données et autres petites tables telles que Clients (9 millions d'enregistrements) et Produits (386 750 enregistrements), etc. Les tables Orders et OrderDetails utiliseront les mêmes conditions aux limites et la même disposition de disque ; en fait, ils utiliseront le même schéma de partitionnement. Par conséquent (regardez les deux lecteurs logiques E:\ et F:\ dans la Figure 13), les données des tables Orders et OrderDetails pour les mêmes mois seront situées sur les mêmes lecteurs :

Figure 13 : Placement de l'étendue des partitions de plage sur les baies de disques

Bien que cela semble déroutant, tout est très simple à mettre en œuvre. La partie la plus difficile de la création de notre table partitionnée est d'obtenir les données de un grand nombre sources - 283 dépôts devraient avoir un mécanisme de livraison standard. Cependant, il n'y a qu'une seule table Orders et une seule table OrderDetails sur le serveur central. Pour partitionner les deux tables, nous devons d'abord créer une fonction et un schéma de partitionnement. Le schéma de partitionnement définit l'emplacement physique des partitions sur les disques, les groupes de fichiers doivent donc également exister. Puisque nos tables ont besoin de groupes de fichiers, L'étape suivante est leur création. La syntaxe des instructions de création pour chaque groupe de fichiers est la même que ci-dessous, cependant, les vingt-quatre groupes de fichiers doivent être créés de cette façon. Vous pouvez modifier les noms/emplacements de lecteur en un seul lecteur afin de tester et d'apprendre la syntaxe. Assurez-vous de corriger les tailles de fichier en Mo au lieu de Go, et choisissez une taille de fichier de départ plus petite en fonction de votre disponibilité. espace disque... Vingt-quatre fichiers et groupes de fichiers seront créés dans la base de données SalesDB. Tous auront une syntaxe similaire, à l'exception de l'emplacement, du nom de fichier et du nom du groupe de fichiers :

ALTER DATABASE SalesDB AJOUTER LE FICHIER (NOM = N "VentesDBFG1Fichier1", NOM DE FICHIER = N "E: \ SalesDB \ SalesDBFG1File1.ndf", TAILLE = 20 Go, TAILLE MAX = 35 Go, CROISSANCE DE FICHIER = 5 Go) AU GROUPE DE FICHIERS Aller

Une fois que les vingt-quatre fichiers et groupes de fichiers ont été créés, vous pouvez définir la fonction et le schéma de partitionnement. Vous pouvez vérifier que vos fichiers et groupes de fichiers sont créés à l'aide des procédures stockées système sp_helpfile et sp_helpfilegroup.

La fonction de section sera définie par la colonne OrderDate avec le type de données datetime. Pour que les deux tables soient partitionnées par la colonne OrderDate, cette colonne doit être présente dans les deux tables. En fait, les valeurs de clé de partitionnement des deux tables (si les deux tables sont partitionnées par la même clé) se dupliqueront l'une l'autre ; cependant, cela est nécessaire pour tirer parti de l'alignement, et dans la plupart des cas, les colonnes clés seront relativement petites (datetime n'est que de 8 octets). Comme déjà décrit dans le chapitre "CRÉER UNE FONCTION DE PARTITION pour les partitions de plage", notre fonction sera une fonction de partitionnement de plage avec la première condition aux limites dans la première partition (GAUCHE).

CRÉER UNE FONCTION DE PARTITION TwoYearDateRangePFN (datetime) COMME PLAGE GAUCHE POUR LES VALEURS ("20021031 23 : 59 : 59,997", - oct. 2002 "20021130 23 : 59 : 59.97" - novembre 2002 "20021231 23 : 59 : 59.97" - décembre 2002 "20030131 23 : 59 : 59.97" - janvier 2003 "20030228 23 : 59 : 59.97" - février 2003 "20030331 23 : 59 : 59.97", - mars 2003 "20030430 23: 59: 59.97", - Avr 2003 "20030531 23:59:59.97" - Mai 2003 "20030630 23 : 59 : 59.97" - juin 2003 "20030731 23: 59: 59.97", - Juil 2003 "20030831 23 : 59 : 59.97" - août 2003 "20030930 23:59:59.97", - sept. 2003 "20031031 23 : 59 : 59.97", - octobre 2003 "20031130 23 : 59 : 59.97" - novembre 2003 "20031231 23 : 59 : 59.97" - décembre 2003 "20040131 23 : 59 : 59.97" - janvier 2004 "20040229 23: 59: 59.97" - Février 2004 "20040331 23 : 59 : 59.97" - mars 2004 "20040430 23 : 59 : 59.97" - avril 2004 "20040531 23 : 59 : 59.97" - mai 2004 "20040630 23 : 59 : 59.97" - juin 2004 "20040731 23: 59: 59.97", - juillet 2004 "20040831 23 : 59 : 59.97" - août 2004 "20040930 23 : 59 : 59.97") - Septembre 2004 Aller

Étant donné que les cas des bords d'extrême gauche et d'extrême droite sont couverts, cette fonction de partitionnement crée en fait 25 sections. Le tableau supportera la 25e section, qui restera vide. Aucun groupe de fichiers spécial n'est requis pour cette section vide, car aucune donnée ne devrait jamais y entrer. Afin de s'assurer qu'aucune donnée n'y entre, la contrainte va restreindre la plage de données de cette table. Afin de diriger les données vers les disques appropriés, un schéma de partitionnement est utilisé qui mappe les partitions aux groupes de fichiers. Le schéma de partitionnement utilisera une définition de groupe de fichiers explicite pour chacun des 24 groupes de fichiers de données et PRIMARY pour la 25e partition vide.

CRÉER UN SCHÉMA DE PARTITION COMME PARTITION TwoYearDateRangePFN À (, , , , , , , , , ,,, ,,,,,, ,,,,,, Aller

Une table peut être créée en utilisant la même syntaxe prise en charge par les versions précédentes de SQL Server — en utilisant le groupe de fichiers par défaut ou défini par l'utilisateur (pour créer une table NON partitionnée) — ou en utilisant un schéma (pour créer une table partitionnée). Quant à savoir laquelle des options est préférable (même si cette table devient partitionnée dans le futur), tout dépend de la façon dont la table sera remplie et du nombre de partitions que vous allez manipuler. Remplir le tas puis créer un index clusterisé dessus fournira probablement meilleure performance que de charger dans une table contenant un index clusterisé. De plus, sur les systèmes multiprocesseurs, vous pouvez charger des données dans une table en parallèle, puis également créer des index en parallèle. À titre d'exemple, créons une table Orders et chargeons-y des données à l'aide des instructions INSERT… SELECT. Pour créer la table Orders comme partitionnée, définissez le schéma de partitionnement dans la clause ON de l'instruction CREATE TABLE.

CRÉER TABLE SalesDB .. NON NULL, NUL, NUL, NUL, NUL, NUL, NON NULL, NUL, NUL, NUL, NON NULL, NUL CONTRAINTE CommandesPlageAnnée VERIFIER (> = "20021001" ET< "20041001" ), NUL Aller

Étant donné que la table OrderDetails va utiliser le même schéma, elle doit inclure une colonne OrderDate.

CRÉER UN TABLEAU. ( NON NULL, NON NULL, NUL, NUL, NUL, NUL, NUL, NON NULL CONTRAINTE Détails de la commandePlageAnnéeCK VERIFIER (> = "20021001" ET< "20041001" ), NUL, NON NULL CONTRAINTE DEFAULT (getdate()), COMME ((*)), COMME ((-)) Aller

L'étape suivante charge les données dans les tables à partir de la nouvelle base de données exemple AdventureWorks. Assurez-vous d'avoir installé la base de données AdventureWorks.

INSÉRER dbo. SÉLECTIONNER o. , o. , o. , o. , o. , o. , o. , o. , o. , o. , o. , o. , o. DE AdventureWorks.Purchasing.PurchaseOrderHeader AS o O (> = "20021001" ET< "20041001" ) Aller INSÉRER dbo. SELECT od.PurchaseOrderID , od.LigneNumber , od.ProductID , od.PrixUnité , od.Commande , od.ReçuQté , od.RejectedQty , o.Date de commande , od.DateDate , od.ModifiedDate FROM AdventureWorks.Purchasing.PurchaseOrderDetail AS od REJOIGNEZ AdventureWorks.Purchasing.PurchaseOrderHeader AS o ON o.PurchaseOrderID = od.PurchaseOrderID O (o.> = "20021001" ET o.< "20041001" ) Aller

Maintenant que vous avez chargé les données dans la table partitionnée, vous pouvez utiliser la nouvelle fonction système intégrée pour déterminer la partition sur laquelle résideront les données. La requête suivante pour chacune des sections contenant des données renvoie des informations sur le nombre de lignes que chaque section contient, ainsi que les valeurs minimale et maximale du champ OrderDate. Une section qui ne contient pas de lignes ne sera pas incluse dans le résultat final.

SELECT $ partition.TwoYearDateRangePFN (o.OrderDate) COMME , min (o.OrderDate) AS , max (o.OrderDate) AS FROM dbo.Orders AS o GROUP BY $ partition.TwoYearDateRangePFN (o.OrderDate) COMMANDÉ PAR Aller SELECT $ partition.TwoYearDateRangePFN (od.OrderDate) COMME , min (od.OrderDate) AS , max (od.OrderDate) AS , Compter comme FROM dbo.OrderDetails AS od GROUP BY $ partition.TwoYearDateRangePFN (od.OrderDate) COMMANDÉ PAR Aller

Enfin, maintenant que vous avez chargé les données, vous pouvez créer un index clusterisé et une clé étrangère entre les tables OrderDetails et Orders. Dans ce cas, l'index clusterisé sera construit sur clé primaire(Primary Key) de la même manière que vous identifiez ces deux tables par leur clé de partitionnement (pour OrderDetails, vous ajouterez la colonne LineNumber à l'index pour qu'elle soit unique). Par défaut, la création d'index sur une table partitionnée les aligne avec la table partitionnée selon le même schéma de partitionnement ; il n'est pas nécessaire de spécifier explicitement le schéma.

Commandes ALTER TABLE AJOUTER UNE CONTRAINTE CommandesPK Aller Aller

La syntaxe complète définissant le schéma de partitionnement ressemblerait à ceci :

Commandes ALTER TABLE AJOUTER UNE CONTRAINTE CommandesPK CLÉ PRIMAIRE EN CLUSTER (OrderDate, OrderID) ON TwoYearDateRangePScheme (OrderDate) Aller ALTER TABLE dbo.OrderDetails AJOUTER UNE CONTRAINTE OrderDetailsPK CLÉ PRIMAIRE EN CLUSTER (OrderDate, OrderID, LineNumber) ON TwoYearDateRangePScheme (OrderDate) Aller