sql - not - ¿Cómo afectan los valores NULL al rendimiento en una búsqueda de base de datos?

que es not null en base de datos (8)

En nuestro producto tenemos un motor de búsqueda genérico e intentamos optimizar el rendimiento de la búsqueda. Muchas de las tablas utilizadas en las consultas permiten valores nulos. ¿Debemos rediseñar nuestra tabla para no permitir valores nulos para la optimización o no?

Nuestro producto se ejecuta tanto en Oracle como en MS SQL Server .

En Oracle , los valores NULL no están indexados, es decir, esta consulta:

SELECT * FROM table WHERE column IS NULL

siempre utilizará el escaneo completo de la tabla ya que el índice no cubre los valores que necesita.

Más que eso, esta consulta:

SELECT column FROM table ORDER BY column

También utilizará la exploración de la tabla completa y la clasificación por la misma razón.

Si sus valores no permiten intrínsecamente los valores NULL , marque la columna como NOT NULL .

En mi experiencia, NULL es un valor válido y generalmente significa "no sé". Si no lo sabe, entonces realmente no tiene sentido recuperar un valor predeterminado para la columna o intentar imponer alguna restricción NOT NULL. NULL pasa a ser un caso específico.

El verdadero desafío para los NULL es que complica un poco la recuperación. Por ejemplo, no puede decir WHERE column_name IN (NULL, ''value1'', ''value2'').

Personalmente, si encuentra muchas de sus columnas, o ciertas columnas contienen muchos NULL, creo que querrá volver a visitar su modelo de datos. Tal vez esas columnas nulas se pueden poner en una tabla secundaria? Por ejemplo: una tabla con números de teléfono donde se encuentra el nombre, teléfono de casa, teléfono celular, fax, número de trabajo, número de emergencia, etc. Sólo puede rellenar uno o dos de ellos y sería mejor normalizarlo.

Lo que debe hacer es retroceder y ver cómo se accederá a los datos. ¿Es esta una columna que debería tener un valor? ¿Es esta una columna que solo tiene un valor para ciertos casos? ¿Es esta una columna que será muy consultada?

La cuestión de si usar Nulls porque afectan el rendimiento es uno de esos actos de equilibrio del diseño de la base de datos. Tienes que equilibrar las necesidades del negocio con el rendimiento.

Se deben usar nulos si son necesarios. Por ejemplo, puede tener una fecha de inicio y una fecha de finalización en una tabla. A menudo, no sabría la fecha de finalización en el momento de la creación del registro. Por lo tanto, debe permitir nulos si afectan el rendimiento o no, ya que los datos simplemente no se pueden introducir. Sin embargo, si los datos deben estar de acuerdo con las reglas comerciales en el momento en que se crea el registro, entonces no debe permitir nulos Esto mejoraría el rendimiento, simplificaría un poco la codificación y garantizaría que se conserva la integridad de los datos.

Si tiene datos existentes que le gustaría cambiar para que ya no permita nulos, debe considerar el impacto de ese cambio. Primero, ¿sabe qué valor debe poner en los registros que actualmente son nulos? En segundo lugar, ¿tiene un montón de código que está usando isnull o coalesce que necesita actualizar (estas cosas ralentizan el rendimiento, por lo que si ya no necesita isnull , debe cambiar el código)? ¿Necesita un valor por defecto? ¿Puedes realmente asignar uno? Si no, algunos de los códigos de inserción o actualización se romperán si no se considera que el campo ya no puede ser nulo. A veces la gente pone mala información para permitirles deshacerse de los nulos. Así que ahora el campo de precios debe contener valores decimales y cosas como ''desconocido'' y, por lo tanto, no puede ser un tipo de datos decimal y, luego, tiene que ir a todo tipo de longitudes para hacer cálculos. Esto a menudo crea problemas de rendimiento tan malos o peores que el nulo creado. ADEMÁS, debe revisar todo su código y siempre que usó una referencia a que el archivo fue nulo o no nulo, debe volver a escribir para excluir o incluir según los posibles valores erróneos que alguien pondrá porque la información no está permitida. ser nulo

Realizo una gran cantidad de importaciones de datos desde los datos del cliente y cada vez que obtenemos un archivo donde un campo que debería permitir nulos no lo hace, obtenemos datos de basura que deben limpiarse antes de importarlos a nuestro sistema. El correo electrónico es uno de estos. A menudo, los datos se ingresan sin conocer este valor y generalmente se trata de algún tipo de datos de cadena, por lo que el usuario puede escribir cualquier cosa aquí. Vamos a importar correos electrónicos y encontrar cosas "no sé". Es difícil intentar enviar un correo electrónico a "No sé". Si el sistema requiere una dirección de correo electrónico válida y verifica algo como la existencia de un signo @, obtendríamos ''[email protected] "¿Cómo es útil para los usuarios de los datos datos de basura como este?

Algunos de los problemas de rendimiento con valores nulos son el resultado de escribir consultas no recargables. A veces, solo reorganizar la cláusula where en lugar de eliminar un nulo necesario puede mejorar el rendimiento.

Los campos anulables pueden tener un gran impacto en el rendimiento cuando se realizan consultas "NO EN". Debido a que las filas con todos los campos indexados configurados en nulo no se indexan en los índices de un árbol B, Oracle debe realizar un escaneo completo de la tabla para verificar los enteros nulos, incluso cuando existe un índice.

Por ejemplo:

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 consulta debe verificar los valores nulos, por lo que debe realizar una exploración de tabla completa de t2 para cada fila en t1.

Ahora, si hacemos que los campos no sean anulables, puede usar el índice.

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 | -----------------------------------------------------------------------------

Respuesta corta: sí, condicional!

El problema principal con los valores nulos y el rendimiento está relacionado con las búsquedas avanzadas.

Si inserta una fila en una tabla, con valores nulos, se coloca en la página natural a la que pertenece. Cualquier consulta que busque ese registro lo encontrará en el lugar apropiado. Fácil hasta ahora ....

... pero digamos que la página se llena, y ahora esa fila está acurrucada entre las otras filas. Sigue yendo bien ...

... hasta que la fila se actualice, y el valor nulo ahora contiene algo. El tamaño de la fila ha aumentado más allá del espacio disponible, por lo que el motor DB tiene que hacer algo al respecto.

Lo más rápido que puede hacer el servidor es mover la fila de esa página a otra, y reemplazar la entrada de la fila con un puntero de avance. Desafortunadamente, esto requiere una búsqueda adicional cuando se realiza una consulta: una para encontrar la ubicación natural de la fila y otra para encontrar su ubicación actual.

Por lo tanto, la respuesta corta a su pregunta es sí, hacer que esos campos no sean anulables ayudará al rendimiento de la búsqueda. Esto es especialmente cierto si a menudo sucede que los campos nulos en los registros que busca se actualizan a no nulos.

Por supuesto, hay otras penalizaciones (especialmente I / O, aunque en un grado pequeño de profundidad del índice) asociadas con conjuntos de datos más grandes, y luego tiene problemas de aplicación con la desactivación de nulos en campos que los requieren conceptualmente, pero bueno, ese es otro problema :)

Si su columna no contiene NULL, es mejor declarar esta columna NOT NULL , el optimizador puede ser capaz de tomar una ruta más eficiente.

Sin embargo, si tiene NULL en su columna, no tiene muchas opciones (un valor predeterminado que no sea nulo puede crear más problemas de los que resuelve).

Como mencionó Quassnoi, los NULL no están indexados en Oracle, o para ser más precisos, una fila no se indexará si todas las columnas indexadas son NULL, esto significa:

que los NULL pueden potencialmente acelerar su investigación porque el índice tendrá menos filas
aún puede indexar las filas NULL si agrega otra columna NOT NULL al índice o incluso una constante.

La siguiente secuencia de comandos muestra una forma de indexar los valores 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;

Una respuesta adicional para llamar la atención sobre el comentario de David Aldridge sobre la respuesta aceptada de Quassnoi.

La declaración:

esta consulta:
SELECT * FROM table WHERE column NULL
siempre utilizará escaneo de tabla completa

no es verdad. Aquí está el ejemplo del contador que usa un índice con un valor literal:

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.

Como puede ver, el índice se está utilizando.

Saludos, Rob.

Yo diría que se requieren pruebas pero es bueno conocer las experiencias de otras personas. En mi experiencia en el servidor ms sql, los nulos pueden causar y causan problemas de rendimiento masivos (diferencias). En una prueba muy simple, ahora he visto un retorno de consulta en 45 segundos cuando no se estableció el valor nulo en los campos relacionados en la tabla crear declaración y más de 25 minutos en los que no se estableció (Renuncié a esperar y simplemente alcancé un pico en el plan de consulta estimado).

Los datos de prueba son 1 millón de filas x 20 columnas que se construyen a partir de 62 caracteres alfa minúsculos aleatorios en un i5-3320 HD normal y 8 GB de RAM (SQL Server usando 2GB) / SQL Server 2012 Enterprise Edition en Windows 8.1. Es importante usar datos aleatorios / datos irregulares para hacer que las pruebas sean un caso "peor" realista. En ambos casos, la tabla fue recreada y recargada con datos aleatorios que tomaron aproximadamente 30 segundos en archivos de base de datos que ya tenían una cantidad adecuada de espacio 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,

por razones de rendimiento, ambos tenían la opción de tabla data_compression = conjunto de páginas y todo lo demás estaba predeterminado. No hay índices.

alter table myTable rebuild partition = all with (data_compression = page);

No tener nulos es un requisito para las tablas optimizadas en memoria para las cuales no estoy usando específicamente, sin embargo el servidor SQL obviamente hará lo que sea más rápido, lo que en este caso específico parece ser enormemente a favor de no tener nulos en los datos y no usar los nulos en el tabla de crear.

Cualquier consulta posterior de la misma forma en esta tabla regresa en dos segundos, por lo que asumo que las estadísticas estándar predeterminadas y posiblemente tener la tabla (1.3GB) en la memoria están funcionando bien. es decir

select count(field19) from myTable where field19 not in (select field18 from myTable) 1000000

Además, no tener nulos y no tener que lidiar con casos nulos también hace que las consultas sean mucho más sencillas, más cortas, menos propensas a errores y, normalmente, más rápidas. Si es posible, lo mejor es evitar los nulos en general en el servidor ms sql al menos a menos que sean explícitamente necesarios y no puedan resolverse razonablemente con la solución.

Comenzando con una nueva tabla y dimensionando esto hasta 10m filas / 13GB, la misma consulta lleva 12 minutos, lo cual es muy respetable considerando el hardware y no los índices en uso. Para información, la consulta estaba completamente enlazada a IO con IO que oscilaba entre 20MB / sa 60MB / s. Una repetición de la misma consulta tomó 9 minutos.