Comment les valeurs NULL affecter les performances d'une base de données de recherche?

Dans notre produit nous avons un générique du moteur de recherche, et en essayant de optimze la recherche de la performance. Beaucoup de tableaux utilisés dans les requêtes autoriser les valeurs null. Devons-nous repenser notre table pour interdire les valeurs null pour l'optimisation ou pas?

Notre produit fonctionne sur les deux Oracle et MS SQL Server.

Jakob, ce genre de performance sont les problèmes rencontrés avec les valeurs Null ?
bien aucun problème jusqu'à présent. Mais je me souviens, j'ai lu un article quelque chose à propos de la baisse des performances lors de l'utilisation de valeurs null. Donc, la discussion a commencé dans notre équipe, si l'on doit autoriser les valeurs null ou n'est pas - et nous ne sommes pas venus pour tout conslusion encore. Nous avons quelques très huges tables avec des millions de lignes et beaucoup de clients, donc c'est un assez gros changement pour le projet. Mais les clients soulevé une question au sujet de la performance dans le moteur de recherche.
SI vous avez des problèmes avec la performance dans les moteurs de recherche, je regarde beaucoup d'autres endroits avant d'éliminer les valeurs null. Démarrer avec l'indexation, Regardez les plans d'exécution pour voir ce qui se passe réellement. Vous regarder où l'une des clauses pour voir si elles sont sargeable. Regardez ce que vous êtes de retour, avez-vous utiliser select * (mauvais pour la performance si vous avez une jointure comme un champ au moins est répété ainsi wating création d'un réseau de ressources), avez-vous l'utilisation de sous-requêtes au lieu de jointures? Avez-vous utiliser un curseur? Est la clause where suffisamment d'exclusivité? Avez-vous utilisez un caractère générique pour le premier caractère? Et sur et sur et sur.

InformationsquelleAutor Jakob Ojvind Nielsen | 2009-06-19

27

Dans Oracle, NULL valeurs ne sont pas indexés, j'. e. cette requête:
```
SELECT  *
FROM    table
WHERE   column IS NULL
```
utilisera toujours full table scan depuis indice ne couvre pas les valeurs que vous avez besoin.

De plus, cette requête:
```
SELECT  column
FROM    table
ORDER BY
        column
```
sera également l'utilisation complète de la table d'analyse et de tri pour la même raison.

Si vos valeurs ne sont pas intrinsèquement permettre NULL's, puis la marque la colonne comme NOT NULL.
- Comment les mêmes requêtes effet un SERVEUR MS SQL?
- SQL Server ne l'index NULL est
- Vous pouvez contourner cette limitation, avec une fonction d'index de base dans lequel vous incluez une valeur littérale, comme la création de l'INDICE de MY_INDEX SUR MA_TABLE (MY_NULLABLE_COLUMN, 0)
- hey les gens c'est pas toujours vrai - voir sur la les réponses ci-dessous
InformationsquelleAutor Quassnoi

Un supplément de répondre à attirer une certaine attention supplémentaire à David Aldridge de commentaire sur Quassnoi accepté de répondre.

La déclaration:

cette requête:

SELECT * from table where colonne
EST NULL

utilisera toujours full table scan

n'est pas vrai. Ici, c'est le contre-exemple à l'aide d'un index avec une valeur littérale:

SQL> create table mytable (mycolumn)
2  as
3   select nullif(level,10000)
4     from dual
5  connect by level <= 10000
6  /
Table created.
SQL> create index i1 on mytable(mycolumn,1)
2  /
Index created.
SQL> exec dbms_stats.gather_table_stats(user,'mytable',cascade=>true)
PL/SQL procedure successfully completed.
SQL> set serveroutput off
SQL> select /*+ gather_plan_statistics */ *
2    from mytable
3   where mycolumn is null
4  /
MYCOLUMN
----------
1 row selected.
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'))
2  /
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
SQL_ID  daxdqjwaww1gr, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ *   from mytable  where mycolumn
is null
Plan hash value: 1816312439
-----------------------------------------------------------------------------------
| Id  | Operation        | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |      1 |        |      1 |00:00:00.01 |       2 |
|*  1 |  INDEX RANGE SCAN| I1   |      1 |      1 |      1 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("MYCOLUMN" IS NULL)
19 rows selected.

Comme vous pouvez le voir, l'index est utilisé.

Ce qui concerne,
Rob.

InformationsquelleAutor Rob van Wijk

8

Réponse courte: oui, à condition!

Le principal problème avec les valeurs null et la performance est à voir avec l'en avant les recherches.

Si vous insérez une ligne dans une table, avec des valeurs nulles, elle est placée dans la nature de la page qui il appartient. Toute requête à la recherche pour que l'enregistrement va trouver dans l'endroit approprié. Facile jusqu'à présent....

...mais disons que la page se remplit, et maintenant que la ligne est blotti dans parmi les autres lignes. Va toujours bien...

...jusqu'à ce que la ligne est mise à jour, et la valeur null contient maintenant quelque chose. La ligne de la taille a augmenté au-delà de l'espace disponible, de sorte que le moteur de base de a à faire quelque chose à ce sujet.

Le plus rapide de la chose pour le serveur à faire est de déplacer la ligne hors cette page dans une autre, et de remplacer la ligne d'entrée avec un pointeur vers l'avant. Malheureusement, cela nécessite une recherche lorsqu'une requête est effectuée: l'une pour trouver l'emplacement naturel de la ligne, et de trouver son emplacement actuel.

Donc, la réponse courte à votre question est oui, rendant les champs non nullable aidera les performances de recherche. Cela est particulièrement vrai si il arrive souvent que les champs null dans les dossiers de votre recherche sont mises à jour à la non-null.

Bien sûr, il y a d'autres sanctions (notamment I/O, bien que dans une infime mesure de l'indice de profondeur) associés avec les plus grands ensembles de données, et puis vous avez des problèmes d'application avec interdisant les valeurs null dans les champs que sur le plan conceptuel en nécessitent, mais bon, c'est un autre problème 🙂
- La définition de ces colonnes not NULL ne résoudra pas la "ligne de la migration" problème: si l'information n'est pas connue au moment de l'insertion, de l'autre la valeur par défaut sera entré (comme '.') et vous aurez encore des lignes migrés lorsque les données réelles seront remplacer la valeur par défaut. Dans Oracle, vous définissez PCTFREE adéquate pour empêcher la ligne de la migration.
InformationsquelleAutor Jeremy Smyth
5

Si votre colonne ne contient pas de valeurs Null il est préférable de déclarer cette colonne NOT NULL, l'optimiseur peut-être en mesure de prendre plus efficace.

Toutefois, si vous avez les valeurs Null dans votre colonne, vous n'avez pas beaucoup de choix (une valeur non nulle valeur par défaut peut créer plus de problèmes qu'elle n'en résout).

Comme Quassnoi dit, les valeurs Null ne sont pas indexés dans Oracle, ou pour être plus précis, une ligne ne sera pas indexé si toutes les colonnes indexées sont NULLES, ce qui signifie:
- que les valeurs Null peut éventuellement accélérer votre recherche, en raison de l'indice ont moins de lignes
- vous pouvez toujours indexer les lignes NULLES si vous ajoutez une autre colonne not NULL à l'index ou même une constante.
Le script suivant illustre un moyen de l'indice des valeurs NULL:
```
CREATE TABLE TEST AS 
SELECT CASE
WHEN MOD(ROWNUM, 100) != 0 THEN
object_id
ELSE
NULL
END object_id
FROM all_objects;
CREATE INDEX idx_null ON test(object_id, 1);
SET AUTOTRACE ON EXPLAIN
SELECT COUNT(*) FROM TEST WHERE object_id IS NULL;
```
InformationsquelleAutor Vincent Malgrat
5

Je dirais que le test est nécessaire, mais il est intéressant de savoir que des expériences d'autres personnes. Dans mon expérience sur ms sql server, les valeurs null et ne peut être la source de graves problèmes de performances (les différences). Dans un très simple test maintenant, j'ai vu une requête de retour en 45 secondes lorsqu'il n'est pas null a été mis sur les champs correspondants dans la table instruction de création et plus de 25 minutes où il n'était pas ensemble (je me gave d'attendre et a juste pris un pic à l'estimation de plan de requête).

Des données de Test est de 1 million de lignes x 20 colonnes qui sont construits à partir de 62 aléatoire en minuscules caractères alpha sur un i5-3320 normal HD et 8GO de RAM (SQL Server à l'aide de 2 GO) /SQL Server 2012 Enterprise Edition sur windows 8.1. Il est important d'utiliser des données aléatoires /irrégulier de données pour faire l'essai d'un réaliste "pire" des cas. Dans les deux cas, le tableau a été recréé et rechargé avec des données aléatoires qui a pris environ 30 secondes sur les fichiers de base de données, qui était déjà une quantité appropriée de l'espace libre.
```
select count(field0) from myTable where field0 
not in (select field1 from myTable) 1000000
CREATE TABLE [dbo].[myTable]([Field0] [nvarchar](64) , ...
vs
CREATE TABLE [dbo].[myTable]([Field0] [nvarchar](64) not null,
```
pour des raisons de performances, les deux avaient l'option de tableau data_compression = mise en page et tout le reste a été défini par défaut. Pas d'index.
```
alter table myTable rebuild partition = all with (data_compression = page);
```
N'ayant pas les valeurs null est une exigence pour le en mémoire optimisé les tables pour lesquelles je ne suis pas spécialement en utilisant toutefois, sql server va évidemment faire ce qui est le plus rapide qui, dans ce cas spécifique semble être massivement en faveur de ne pas avoir des valeurs nulles dans les données et en utilisant not null sur la table à créer.

Toutes les requêtes suivantes de la même forme sur cette table de retour en deux secondes, donc, je suppose que standard par défaut de statistiques, et après avoir éventuellement le (1,3 GO) tableau ajustement dans la mémoire de bien travailler.
c'est à dire
```
select count(field19) from myTable where field19 
not in (select field18 from myTable) 1000000
```
Sur un côté de ne pas avoir des valeurs nulles et ne pas avoir à traiter avec null cas également fait des requêtes beaucoup plus simple, plus court, moins d'erreurs et très normalement plus rapide. Si possible, le mieux pour éviter les valeurs null généralement sur ms sql server au moins, sauf s'ils sont explicitement requise et ne peut pas raisonnablement être travaillé sur la solution.

De départ avec une nouvelle table et de dimensionnement jusqu'à 10m lignes /13GO même requête prend 12 minutes, ce qui est très respectable compte tenu du matériel et pas d'index en cours d'utilisation. Pour info requête a été complètement IO lié avec IO oscillant entre 20 mo/s à 60 mo/s. Une répétition de la même requête a 9 minutes.

InformationsquelleAutor Andrew

Nullable les champs peuvent avoir un grand impact sur les performances lorsque vous faites "n'est PAS" interroge. Parce que les lignes avec tous les champs indexés null ne sont pas indexés dans un B-Arbre d'index, Oracle doit faire un full table scan pour vérifier la valeur null entrées, même si un index existe.

Par exemple:

create table t1 as select rownum rn from all_objects;
create table t2 as select rownum rn from all_objects;
create unique index t1_idx on t1(rn);
create unique index t2_idx on t2(rn);
delete from t2 where rn = 3;
explain plan for
select *
from t1
where rn not in ( select rn
from t2 );
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 50173 |   636K|  3162   (1)| 00:00:38 |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| T1   | 50205 |   637K|    24   (5)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T2   | 45404 |   576K|     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

La requête pour vérifier les valeurs null, alors il doit faire un full table scan de t2 pour chaque ligne t1.

Maintenant, si nous faisons les champs non nullable, il peut utiliser l'index.

alter table t1 modify rn not null;
alter table t2 modify rn not null;
explain plan for
select *
from t1
where rn not in ( select rn
from t2 );
-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |  2412 | 62712 |    24   (9)| 00:00:01 |
|   1 |  NESTED LOOPS ANTI |        |  2412 | 62712 |    24   (9)| 00:00:01 |
|   2 |   INDEX FULL SCAN  | T1_IDX | 50205 |   637K|    21   (0)| 00:00:01 |
|*  3 |   INDEX UNIQUE SCAN| T2_IDX | 45498 |   577K|     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------

InformationsquelleAutor Daniel Emge

3

La question de savoir si l'utilisation de valeurs nulles, car ils affectent la performance est l'une de celles de l'équilibrage des actes de conception de base de données. Vous devez équilibrer les besoins de l'entreprise la performance.

Les valeurs null devraient être utilisées que si elles sont nécessaires. Par exemple, vous pouvez avoir une date de début et une date de fin dans une table. Souvent, vous ne savez pas la date de fin au moment de la création de l'enregistrement. Par conséquent, vous devez autoriser les valeurs null si elles affectent la performance ou non selon les données est tout simplement pas là pour mettre en. Toutefois, si les données doivent, par les règles d'affaires, être là au moment de la création de l'enregistrement, alors vous ne devez pas autoriser les valeurs null. Cela permettrait d'améliorer les performances, faire le codage d'un peu plus simple, et assurez-vous que l'intégrité des données est préservée.

Si vous avez des données que vous souhaitez changer, de ne plus autoriser les valeurs null, alors vous devez tenir compte de l'impact de ce changement. Tout d'abord, savez-vous quelle est la valeur que vous avez besoin de mettre dans les dossiers qui sont actuellement nulle? Deuxièmement, avez-vous beaucoup de code à l'aide de isnull ou coalesce dont vous avez besoin pour mettre à jour (ces choses ralentir les performances, si vous n'avez plus besoin de vérifier pour eux, vous devez changer le code)? Avez-vous besoin d'une valeur par défaut? Pouvez-vous vraiment vous attribuer un? Si non est-ce que certains d'insérer ou de mettre à jour le code de rupture si elle n'est pas en considérant que le champ ne peut plus être null. Parfois, les gens seront mis dans de mauvaises informations pour leur permettre de se débarrasser de la valeur null. Donc, maintenant, le prix de champ doit contenir des valeurs décimales et des choses comme "inconnu" et ne peut donc pas être correctement un type de données décimal, puis vous devez aller à toutes sortes de longueurs afin de faire les calculs. Cela crée souvent des problèmes de performances aussi mauvais ou pire que l'null créé. De PLus, vous devez aller à travers tout votre code et à l'endroit où vous avez utilisé une refernce à l'déposé nul ou pas nul, vous avez besoin de réécrire pour exclure ou inclure fondée sur des valeurs incorrectes quelqu'un va mettre en car les données ne sont pas autorisés à être null.

Je fais beaucoup de l'importation des données à partir des données du client et à chaque fois que nous avons un fichier où un domaine qui devrait autoriser les valeurs null ne sont pas, nous obtenons des ordures de données qui doit être nettoyé avant de nous importer de notre système. Le courrier électronique est l'un de ces. Souvent, les données d'entrée ne connaissant pas cette valeur et il est généralement un certain type de données de chaîne, de sorte que l'utilisateur peut taper quoi que ce soit ici. Nous allons à l'importation d'emails et de trouver les choses "je ne sais pas". Difficile d'essayer d'envoyer un email à "je ne sais pas". Si le système requres une adresse email valide et vérifie quelque chose comme l'existence d'un signe@, nous obtenir '[email protected]" Comment est-ordures des données de cette utile pour les utilisateurs des données?

Certains des problèmes de performances avec les valeurs null sont un résultat de l'écriture nonsargable requêtes. Parfois, tout simplement réorganiser la clause where plutôt que d'éliminer un nécessaire null peut améliorer les performances.

InformationsquelleAutor HLGEM
0

Dans mon expérience, la valeur NULL est une valeur valide et signifie en général "ne sait pas". Si vous ne savez pas alors il est vraiment inutile de faire une valeur par défaut pour la colonne ou d'essayer d'appliquer certains de contrainte not NULL. NULL arrive juste à être un cas particulier.

Le véritable défi pour les Nuls, est il compliquer la récupération un peu. Par exemple, vous ne pouvez pas dire OÙ column_name DANS (NULL,'valeur1','valeur2').

Personnellement si vous trouvez beaucoup de vos colonnes, ou de certaines colonnes contiennent beaucoup de Zéros, je pense que vous pourriez revoir votre modèle de données. Peut-être que ces colonnes null peut être mis dans une table d'enfant? Par exemple: un tableau avec les numéros de téléphone où son nom, homephone, téléphone portable, faxno, worknumber, emergencynumber etc... Vous ne pouvez remplir un ou deux de ceux-ci et qu'il serait mieux de les normaliser.

Ce que vous avez besoin de faire un pas en arrière et voir comment les données seront accessibles. Est-ce une colonne qui doit avoir une valeur? Est-ce une colonne qui a une valeur pour certains cas? Est-ce une colonne qui va être interrogé beaucoup?
- Je n'utilise que des nuls à exprimer une inexistant clé étrangère (par exemple, un "Coupon de Réduction" clé étrangère sur un élément de la facture de la table peut ne pas exister). Cependant, je n'utilise pas les valeurs null dans les non-colonnes de clé étrangère; comme tu le dis, "généralement" les moyens de ne pas savoir. Le problème avec les valeurs null, c'est qu'ils peuvent signifier plusieurs choses, - "inconnu", "non applicable", "ne pas exister" (mon cas), etc. En non-clé des cas, vous aurez toujours à mapper un nom pour le champ NULL quand vous obtenez finalement autour de l'utiliser. Mieux vaut avoir que la cartographie valorisée défini dans la colonne elle-même comme une valeur réelle plutôt que de gruger la cartographie everytwhere.
InformationsquelleAutor David

Vous devez vous connecter pour publier un commentaire.