Pregunta Errores de desarrollo de base de datos realizados por los desarrolladores de aplicaciones [cerrado]


¿Cuáles son los errores comunes de desarrollo de bases de datos realizados por los desarrolladores de aplicaciones?


566
2018-03-29 13:38


origen


Respuestas:


1. No usar índices apropiados

Este es relativamente fácil, pero aún sucede todo el tiempo. Las claves externas deben tener índices en ellas. Si estás usando un campo en WHERE deberías (probablemente) tener un índice. Dichos índices a menudo deben cubrir múltiples columnas en función de las consultas que necesita ejecutar.

2. No aplicar la integridad referencial

Su base de datos puede variar aquí, pero si su base de datos es compatible con la integridad referencial, lo que significa que todas las claves externas tienen la garantía de apuntar a una entidad que existe, debe utilizarla.

Es bastante común ver esta falla en las bases de datos MySQL. No creo que MyISAM lo apoye. InnoDB lo hace. Encontrarás personas que están usando MyISAM o aquellas que están usando InnoDB, pero no las están usando de todos modos.

Más aquí:

3. Uso de claves primarias naturales en lugar de sustitutas (técnicas)

Las claves naturales son claves basadas en datos externamente significativos que (ostensiblemente) son únicos. Ejemplos comunes son códigos de productos, códigos de estado de dos letras (EE. UU.), Números de la seguridad social, etc. Las claves primarias subrogadas o técnicas son aquellas que no tienen ningún significado fuera del sistema. Se inventan puramente para identificar a la entidad y suelen ser campos que se autoincrementan (SQL Server, MySQL, otros) o secuencias (sobre todo Oracle).

En mi opinión, deberías siempre usar claves sustitutivas Este problema ha surgido en estas preguntas:

Este es un tema un tanto controvertido sobre el que no obtendrá un acuerdo universal. Si bien es posible que encuentres a algunas personas que creen que las claves naturales son correctas en algunas situaciones, no encontrarás ninguna crítica a las claves sustitutivas aparte de ser innecesariamente discutible. Esa es una pequeña desventaja si me preguntas.

Recuerda, incluso los países pueden dejar de existir (por ejemplo, Yugoslavia).

4. Escribir consultas que requieren DISTINCT trabajar

A menudo se ve esto en las consultas generadas por ORM. Mire la salida de registro de Hibernate y verá que todas las consultas comienzan con:

SELECT DISTINCT ...

Este es un pequeño atajo para garantizar que no devuelva filas duplicadas y así obtener objetos duplicados. A veces verá gente haciendo esto también. Si lo ves demasiado, es una verdadera bandera roja. Eso no DISTINCT es malo o no tiene aplicaciones válidas. Lo hace (en ambos casos) pero no es un sustituto o un recurso provisional para escribir consultas correctas.

De Por qué odio DISTINCT:

Donde las cosas empiezan a ponerse feas en mi   opinión es cuando un desarrollador es   construir una consulta sustancial, unir   tablas juntas, y de repente   se da cuenta de que miradas como él es   obteniendo filas duplicadas (o incluso más)   y su respuesta inmediata ... su   La "solución" a este "problema" es   lanzar en la palabra clave DISTINCT y MARICÓN   todos sus problemas desaparecen.

5. Favorecer la agregación sobre las uniones

Otro error común de los desarrolladores de aplicaciones de bases de datos es no darse cuenta de cuánto más agregación costosa (es decir, el GROUP BY cláusula) se puede comparar con las uniones.

Para darte una idea de cuán extendido está esto, he escrito sobre este tema varias veces aquí y he votado mucho por ello. Por ejemplo:

De Instrucción SQL - "unirse" vs "agrupar por y tener":

Primera consulta:

SELECT userid
FROM userrole
WHERE roleid IN (1, 2, 3)
GROUP by userid
HAVING COUNT(1) = 3

Tiempo de consulta: 0.312 s

Segunda consulta:

SELECT t1.userid
FROM userrole t1
JOIN userrole t2 ON t1.userid = t2.userid AND t2.roleid = 2
JOIN userrole t3 ON t2.userid = t3.userid AND t3.roleid = 3
AND t1.roleid = 1

Tiempo de consulta: 0.016 s

Está bien. La versión de unión I   propuesto es Veinte veces más rápido que   la versión agregada.

6. No simplifica consultas complejas a través de vistas

No todos los proveedores de bases de datos admiten vistas, pero para aquellos que sí lo hacen, pueden simplificar enormemente las consultas si se usan de forma juiciosa. Por ejemplo, en un proyecto usé un modelo de fiesta genérico para CRM. Esta es una técnica de modelado extremadamente poderosa y flexible, pero puede conducir a muchas combinaciones. En este modelo había:

  • Fiesta: personas y organizaciones;
  • Papel del partido: cosas que esos partidos hicieron, por ejemplo, Empleado y Empleador;
  • Relación de roles del partido: cómo esos roles se relacionan entre sí.

Ejemplo:

  • Ted es una Persona, siendo un subtipo de Parte;
  • Ted tiene muchos roles, uno de los cuales es Empleado;
  • Intel es una organización, siendo un subtipo de una Parte;
  • Intel tiene muchos roles, uno de los cuales es Employer;
  • Intel emplea a Ted, lo que significa que existe una relación entre sus respectivos roles.

Entonces, se unieron cinco tablas para vincular a Ted con su empleador. Usted asume que todos los empleados son Personas (no organizaciones) y proporcionan esta vista de ayudante:

CREATE VIEW vw_employee AS
SELECT p.title, p.given_names, p.surname, p.date_of_birth, p2.party_name employer_name
FROM person p
JOIN party py ON py.id = p.id
JOIN party_role child ON p.id = child.party_id
JOIN party_role_relationship prr ON child.id = prr.child_id AND prr.type = 'EMPLOYMENT'
JOIN party_role parent ON parent.id = prr.parent_id = parent.id
JOIN party p2 ON parent.party_id = p2.id

Y de repente tiene una vista muy simple de los datos que desea, pero en un modelo de datos altamente flexible.

7. No desinfección de entrada

Esta es una gran. Ahora me gusta PHP, pero si no sabes lo que estás haciendo, es muy fácil crear sitios vulnerables a los ataques. Nada lo resume mejor que el historia del pequeño Bobby Tables.

Datos proporcionados por el usuario a través de URL, datos de formulario y cookies siempre debe ser tratado como hostil y desinfectado. Asegúrate de obtener lo que esperas.

8. No usar declaraciones preparadas

Las declaraciones preparadas son cuando compila una consulta menos los datos utilizados en inserciones, actualizaciones y WHERE cláusulas y luego suministrar eso más tarde. Por ejemplo:

SELECT * FROM users WHERE username = 'bob'

vs

SELECT * FROM users WHERE username = ?

o

SELECT * FROM users WHERE username = :username

dependiendo de tu plataforma

He visto las bases de datos arrodillarse al hacer esto. Básicamente, cada vez que una base de datos moderna encuentra una nueva consulta, debe compilarla. Si encuentra una consulta que se haya visto antes, le está dando a la base de datos la oportunidad de almacenar en caché la consulta compilada y el plan de ejecución. Al realizar muchas consultas, le da a la base de datos la oportunidad de descubrirlo y optimizarlo en consecuencia (por ejemplo, fijando la consulta compilada en la memoria).

El uso de declaraciones preparadas también le dará estadísticas significativas sobre la frecuencia con que se utilizan ciertas consultas.

Las declaraciones preparadas también lo protegerán mejor contra ataques de inyección SQL.

9. No normalizar lo suficiente

Normalización de base de datos es básicamente el proceso de optimizar el diseño de la base de datos o cómo organizas tus datos en tablas.

Esta semana me topé con un código en el que alguien implosionó una matriz y la insertó en un solo campo en una base de datos. Normalizar eso sería tratar el elemento de esa matriz como una fila separada en una tabla hija (es decir, una relación de uno a muchos).

Esto también surgió en El mejor método para almacenar una lista de ID de usuario:

He visto en otros sistemas que la lista se almacena en una matriz PHP serializada.

Pero la falta de normalización viene en muchas formas.

Más:

10. Normalizar demasiado

Esto puede parecer una contradicción con el punto anterior, pero la normalización, como muchas cosas, es una herramienta. Es un medio para un fin y no un fin en sí mismo. Creo que muchos desarrolladores se olvidan de esto y comienzan a tratar un "medio" como un "fin". Las pruebas unitarias son un buen ejemplo de esto.

Una vez trabajé en un sistema que tenía una gran jerarquía para los clientes que fue algo así como:

Licensee ->  Dealer Group -> Company -> Practice -> ...

de modo que tenía que unir unas 11 tablas antes de poder obtener datos significativos. Fue un buen ejemplo de normalización llevada demasiado lejos.

Más concretamente, la desnormalización cuidadosa y considerada puede tener enormes beneficios de rendimiento, pero hay que tener mucho cuidado al hacer esto.

Más:

11. Usando arcos exclusivos

Un arco exclusivo es un error común cuando se crea una tabla con dos o más claves externas donde una y solo una de ellas puede ser no nula. Gran error.  Por un lado, se vuelve mucho más difícil mantener la integridad de los datos. Después de todo, incluso con integridad referencial, nada impide que se establezcan dos o más de estas claves foráneas (a pesar de las complejas restricciones de verificación).

De Una guía práctica para el diseño de bases de datos relacionales:

Recomendamos encarecidamente no construir construcciones de arco exclusivas donde sea   posible, por la buena razón de que pueden ser incómodos al escribir el código   y plantean más dificultades de mantenimiento.

12. No hacer análisis de rendimiento en consultas en absoluto

El pragmatismo reina supremo, particularmente en el mundo de las bases de datos. Si te apegas a los principios hasta el punto de convertirse en un dogma, es probable que hayas cometido errores. Tome el ejemplo de las consultas agregadas de arriba. La versión global puede parecer "agradable", pero su rendimiento es lamentable. Una comparación de desempeño debería haber terminado el debate (pero no fue así), pero más al grano: en primer lugar, lanzar tales opiniones mal informadas es ignorante, incluso peligroso.

13. Confianza excesiva en los constructivos UNION ALL y particularmente UNION

Una UNIÓN en términos de SQL simplemente concatena conjuntos de datos congruentes, lo que significa que tienen el mismo tipo y número de columnas. La diferencia entre ellos es que UNION ALL es una concatenación simple y debe preferirse siempre que sea posible, mientras que UNION realizará implícitamente un DISTINCT para eliminar las tuplas duplicadas.

UNIONs, como DISTINCT, tienen su lugar. Hay aplicaciones válidas. Pero si te encuentras haciendo muchos de ellos, particularmente en subconsultas, entonces probablemente estés haciendo algo mal. Ese podría ser un caso de construcción de consultas deficiente o un modelo de datos mal diseñado que lo obliga a hacer tales cosas.

Los UNION, particularmente cuando se usan en uniones o subconsultas dependientes, pueden paralizar una base de datos. Intenta evitarlos siempre que sea posible.

14. Uso de las condiciones de O en las consultas

Esto puede parecer inofensivo. Después de todo, AND está bien. O debería estar bien también ¿no? Incorrecto. Básicamente una condición Y restringe el conjunto de datos mientras que una condición O crece pero no de una manera que se preste a la optimización. Particularmente cuando las diferentes condiciones de OR pueden cruzarse, forzando así al optimizador a una operación DISTINCT en el resultado.

Malo:

... WHERE a = 2 OR a = 5 OR a = 11

Mejor:

... WHERE a IN (2, 5, 11)

Ahora su optimizador de SQL puede efectivamente convertir la primera consulta en la segunda. Pero puede que no. Simplemente no lo hagas.

15. No diseñar su modelo de datos para que se preste a soluciones de alto rendimiento

Este es un punto difícil de cuantificar. Por lo general se observa por su efecto. Si se encuentra escribiendo consultas difíciles para tareas relativamente simples o si las consultas para encontrar información relativamente directa no son eficientes, entonces probablemente tenga un modelo de datos deficiente.

De alguna manera, este punto resume todos los anteriores, pero es más una advertencia que hacer cosas como la optimización de consultas a menudo se hace primero cuando debería hacerse en segundo lugar. En primer lugar, debe asegurarse de tener un buen modelo de datos antes de tratar de optimizar el rendimiento. Como Knuth dijo:

La optimización temprana es la raíz de todo mal

16. Uso incorrecto de transacciones de base de datos

Todos los cambios de datos para un proceso específico deben ser atómicos. Es decir. Si la operación tiene éxito, lo hace completamente. Si falla, los datos no se modifican. - No debería haber posibilidad de cambios a medio terminar.

Idealmente, la forma más sencilla de lograr esto es que todo el diseño del sistema debe esforzarse por admitir todos los cambios de datos a través de instrucciones únicas INSERT / UPDATE / DELETE. En este caso, no se necesita ningún manejo de transacción especial, ya que su motor de base de datos debería hacerlo automáticamente.

Sin embargo, si algún proceso requiere que se realicen múltiples declaraciones como una unidad para mantener los datos en un estado constante, entonces es necesario un control de transacción apropiado.

  • Comience una transacción antes de la primera declaración.
  • Confirme la transacción después de la última declaración.
  • En cualquier error, revertir la transacción. Y muy NB! No se olvide de omitir / abortar todas las declaraciones que siguen después del error.

También se recomienda prestar especial atención a las deficiencias de cómo la capa de conectividad de su base de datos y el motor de la base de datos interactúan en este sentido.

17. No entender el paradigma 'basado en conjuntos'

El lenguaje SQL sigue un paradigma específico adecuado para tipos específicos de problemas. A pesar de varias extensiones específicas de proveedores, el idioma tiene dificultades para lidiar con problemas que son triviales en langues como Java, C #, Delphi, etc.

Esta falta de comprensión se manifiesta de varias maneras.

  • Imponer demasiada lógica procedimental o imperativa en la base de datos.
  • Uso inapropiado o excesivo de los cursores. Especialmente cuando una sola consulta sería suficiente.
  • Asumiendo incorrectamente que los disparos desencadenan una vez por fila afectada en las actualizaciones de múltiples filas.

Determine la división clara de responsabilidades y procure utilizar la herramienta adecuada para resolver cada problema.


1003



Errores en el diseño de la base de datos y la programación cometidos por los desarrolladores

  • Diseño y uso de bases de datos egoístas.  Los desarrolladores a menudo tratan la base de datos como su almacén personal de objetos persistentes sin considerar las necesidades de otros interesados ​​en los datos. Esto también se aplica a los arquitectos de aplicaciones. El mal diseño de la base de datos y la integridad de los datos dificultan el trabajo de terceros con los datos y pueden aumentar sustancialmente los costos del ciclo de vida del sistema. Informes y MIS tiende a ser un primo pobre en el diseño de aplicaciones y solo se hace como una idea de último momento.

  • Abusando de datos desnormalizados. Exagerar los datos desnormalizados e intentar mantenerlos dentro de la aplicación es una receta para problemas de integridad de datos. Use la desnormalización con moderación. No querer agregar unirme a una consulta no es una excusa para denormalizar.

  • Asustado de escribir SQL.  SQL no es ciencia espacial y en realidad es bastante bueno para hacer su trabajo. Las capas de mapeo O / R son bastante buenas para hacer el 95% de las consultas que son simples y se ajustan bien a ese modelo. A veces SQL es la mejor manera de hacer el trabajo.

  • Políticas dogmáticas de 'No procedimientos almacenados'.  Independientemente de si cree que los procedimientos almacenados son malos, este tipo de actitud dogmática no tiene cabida en un proyecto de software.

  • No comprende el diseño de la base de datos.  La normalización es tu amiga y es no ciencia de cohetes.  La unión y la cardinalidad son conceptos bastante simples: si estás involucrado en el desarrollo de aplicaciones de bases de datos, no hay excusa para no entenderlas.


110



  1. No se usa el control de versión en el esquema de la base de datos
  2. Trabajando directamente contra una base de datos en vivo
  3. No leer y comprender conceptos de bases de datos más avanzados (índices, índices agrupados, restricciones, vistas materializadas, etc.)
  4. Si no prueba la escalabilidad ... los datos de prueba de solo 3 o 4 filas nunca le darán la imagen real del rendimiento real en vivo

80



Uso excesivo y / o dependencia de los procedimientos almacenados.

Algunos desarrolladores de aplicaciones ven los procedimientos almacenados como una extensión directa del código de nivel medio / front-end. Esto parece ser un rasgo común en los desarrolladores de Microsoft stack, (soy uno, pero he crecido fuera de él) y produce muchos procedimientos almacenados que realizan lógica de negocios compleja y procesamiento de flujo de trabajo. Esto está mucho mejor hecho en otro lugar.

Los procedimientos almacenados son útiles cuando se ha demostrado que algún factor técnico real necesita su uso (por ejemplo, rendimiento y seguridad). Por ejemplo, mantener la agregación / filtrado de grandes conjuntos de datos "cerca de los datos".

Recientemente tuve que ayudar a mantener y mejorar una gran aplicación de escritorio Delphi de la cual el 70% de la lógica y las reglas de negocios se implementaron en 1400 procedimientos almacenados de SQL Server (el resto en los controladores de eventos de IU). Esto fue una pesadilla, principalmente debido a la dificultad de introducir pruebas unitarias efectivas para TSQL, falta de encapsulación y herramientas deficientes (depuradores, editores).

Trabajando con un equipo de Java en el pasado descubrí rápidamente que a menudo todo lo contrario se cumple en ese entorno. Un arquitecto de Java me dijo una vez: "La base de datos es para datos, no para código".

En estos días, creo que es un error no considerar los procesos almacenados en absoluto, sino que deben utilizarse con moderación (no de forma predeterminada) en situaciones donde ofrecen beneficios útiles (ver las otras respuestas).


46



¿Problema número uno? Solo prueban en bases de datos de juguetes. Así que no tienen idea de que su SQL se rastreará cuando la base de datos se agrande, y que alguien tenga que venir y arreglarlo más tarde (ese sonido que puedes oír es el rechinamiento de mis dientes).


41



No usando índices.


31



Rendimiento bajo causado por subconsultas correlacionadas

La mayoría de las veces quiere evitar subconsultas correlacionadas. Una subconsulta se correlaciona si, dentro de la subconsulta, hay una referencia a una columna de la consulta externa. Cuando esto sucede, la subconsulta se ejecuta al menos una vez por cada fila devuelta y podría ejecutarse más veces si se aplican otras condiciones después de aplicar la condición que contiene la subconsulta correlacionada.

Perdona el ejemplo artificial y la sintaxis de Oracle, pero digamos que querías encontrar a todos los empleados que han sido contratados en cualquiera de tus tiendas desde la última vez que la tienda hizo menos de $ 10,000 en ventas en un día.

select e.first_name, e.last_name
from employee e
where e.start_date > 
        (select max(ds.transaction_date)
         from daily_sales ds
         where ds.store_id = e.store_id and
               ds.total < 10000)

La subconsulta en este ejemplo está correlacionada con la consulta externa por store_id y se ejecutará para cada empleado de su sistema. Una forma de optimizar esta consulta es mover la sub consulta a una vista en línea.

select e.first_name, e.last_name
from employee e,
     (select ds.store_id,
             max(s.transaction_date) transaction_date
      from daily_sales ds
      where ds.total < 10000
      group by s.store_id) dsx
where e.store_id = dsx.store_id and
      e.start_date > dsx.transaction_date

En este ejemplo, la consulta en la cláusula from es ahora una vista en línea (nuevamente, una sintaxis específica de Oracle) y solo se ejecuta una vez. Dependiendo de su modelo de datos, esta consulta probablemente se ejecutará mucho más rápido. Tendría un mejor rendimiento que la primera consulta a medida que crecía el número de empleados. La primera consulta podría funcionar mejor si hubiera pocos empleados y muchas tiendas (y tal vez muchas de las tiendas no tenían empleados) y la tabla daily_sales estaba indexada en store_id. Este no es un escenario probable, pero muestra cómo una consulta correlacionada podría funcionar mejor que una alternativa.

He visto a desarrolladores junior correlacionar subconsultas muchas veces y, por lo general, ha tenido un impacto severo en el rendimiento. Sin embargo, al eliminar una subconsulta correlacionada, asegúrese de mirar el explicar el plan antes y después para asegurarse de no empeorar el rendimiento.


28



En mi experiencia:
No comunicarse con DBA experimentados.


21