13
Nov 13

Pildoritas de Bases de Datos: Optimización. Explain Plan

pildoras bbdd

Después de un parón algo más largo (pero no tan largo como el de verano), afrontamos ya el último de los posts de las Pildoritas de Bases de Datos. En él vamos a ver de forma muy básica como lanzar un Explain Plan y las ideas generales para poder interpretarlo, conforme a las cuales actuaremos en la optimización.

El Explain Plan es una funcionalidad que comparten todos los motores de BBDD y aunque cada uno tiene sus particularidades propias, se comparten ciertas ideas troncales para que cualquiera, independientemente de la tecnología, lo pueda entender.

El Explain Plan está asociado a las consultas y nos muestra un análisis de que tipos de recorridos se realizan en las tablas de consultas, lo cual nos sirve para saber si nuestra consulta es más o menos rápida. La forma de saber las diferencias de rendimiento de cada consulta y cada parte de una consulta es el coste.

Hay GUIs que dibujan en forma de árbol los explain plan, pero al final todos tiran de un comando más o menos igual, el EXPLAIN:

ORACLE

EXPLAIN  FOR [LA SELECT A ANALIZAR]

MySQL

EXPLAIN [LA SELECT A ANALIZAR]

Lo que más diferencia un explain plan realizado en diferentes tecnologias son los nombres de los accesos y los tipos de joins, aunque tienen relación suficiente para que uno lo entienda.

En el caso de acceso, hay dos grandes grupos: FULL TABLE SCAN e INDEX SCAN. No obstante dentro de estos grupos, como veremos, existen multitud de subcategorías.

Por otra parte también hay que tener en cuenta también como está haciendo el JOIN entre tablas nuestra BBDD. También veremos cuatro tipos de JOIN.

En ambos casos utilizaré los tipos de Oracle, no obstante otras tecnologías tienen, si no los mismos nombres, si muy parecidos.

Tipos de acceso

Como ya vimos en la otra pildorita de optimización, lo mejor siempre es que nuestra consulta tire de los índices y evitar así los FULL TABLE SCAN, los cuales recorren por completo la tabla y que, como es de imaginar, es el acceso más costoso. Esto no ocurre siempre, ya que a veces si el recorrido del índice es muy costoso un FULL TABLE puede dar mejor resultado.

Los recorridos de índices pueden ser de cuatro tipos en Oracle, aunque, como ya he aclarado antes, en el resto de tecnologías veremos "cosas" parecidas:

Index Unique Scan

El acceso busca una única clave a través de un índice único. Eso significa que siempre la búsqueda devolverá un resultado, en otro caso ya no sería UNIQUE. Un ejemplo claro de este tipo de acceso es el que hacemos cuando buscamos a través de la PK.

Index Range Scan

Como su nombre indica, en este caso no buscamos por un único valor, sino por un rango de ellos, por lo que nos devolverá más de un registro. Esto ocurre cuando usamos los operadores de rango (<, >, <>, <=, >=, BETWEEN).

Index Full Scan

Esta es la peor de las decisiones con índices y para que ocurra deberemos de no filtrar por el índice y que sea el motor el que decida que este acceso es más óptimo que un FULL SCAN, lo cual es raro, ya que, como os comenté en su momento, recorrer un índice es costoso y mejora la optimización sólo si está ordenado y se recorre un rango "pequeño" de registros de la tabla. Ahora bien, si por ejemplo queremos tirar un ORDER BY de una tabla sin filtrado y este orden es el utilizado por el índice, en ese caso entonces el motor decidirá acceder por esta vía.

Estas pautas de accesos son a nivel de lectura de los registros de la tabla. También hay otra parte muy importante que debemos tener en cuenta: Como se realizan los JOINs.

Tipos de JOIN

Nested Loop

Este es el JOIN típico. Se recorren los registros de A, y luego se prueba que cada uno de los registros de A esté en B. Es por eso que en otros capítulos dábamos las pautas de que hay que intentar siempre filtrar al máximo la tabla de unión (en el caso exacto la A) y que esta tabla fuente tiene que ser, si es posible, la más pequeña de ambas (incluyendo filtrados).

Hash Merge Join

Esto no está en todos los motores, así que no entraremos demasiado en el tema. Simplemente basta saber que este caso es incluso más eficiente que el Nested Loop, ya que la utilización de un Hash Map para la recuperación de datos es mucho más eficiente.

Sort Merge Join

Este es el peor JOIN que podemos encontrar y tiene lugar cuando hay que ordenar previamente el resultado de ambas tablas, lo que genera mayor carga de datos.

Producto Cartesiano

Esto es el terror de las SELECT, ya que recorre ambas tablas por completo. Esto ocurre porque la consulta está mal escrita y falta el JOIN entre ambas tablas.

 

Y con estos conceptos creo que podéis defenderos ante un EXPLAIN PLAN y entenderlo lo suficiente para hacer las tareas de optimización típicas en desarrollos. Claro está este tema puede profundizarse muchísimo. El Explain Plan muchas veces indica al motor como tiene que ejecutar la consulta, es más, si lanzamos un EXPLAIN PLAN - al menos en Oracle - de una consulta, su rendimiento mejorará, ya que el motor lo almacena en caché. Por ejemplo, existe un parámetro, OPTIMIZER_MODE (nuevamente hablo de Oracle), que sirve para indicar si queremos optimizar la consulta para búsquedas de todos los campos, para devolver todos los campos, para seguir reglas definidas por nosotros...pero como digo, esto ya es tarea de un DBA y yo por desgracia sólo soy una humilde (y pobre) desarrolladora.

Y ahora ya sí que sí ponemos punto y final a las Pildoritas de Bases de Datos. Esta ha sido la primera vez que me animo a escribir sobre un tema técnico y aunque seguramente haya cometido más de un fail, deseo de corazón que estas pildoritas os hayan hecho disfrutar una décima parte de lo que yo lo he hecho escribiéndolas. No sólo ha sido un reto el conseguir organizar las ideas para explicarlas de forma comprensible a otros, sino que además me ha servido para volver a refrescar muchas cosas que ya había olvidado.

Algunos pensaréis que es imposible unir contenido técnico y disfrute en la misma frase, pero para que veáis que hablo en serio os diré que ya tengo en mente una segunda serie de Pildoritas en mente, esta vez sobre POO y/o Java. Todavía es una idea embrionaria, y ni siquiera he pensado aún sobre cómo preparar y organizar el temario, ni que incluir en él exactamente. No obstante, me apetece muchísimo seguir haciendo Pildoritas, por lo que esto no es un Adiós, sino un Hasta Luego 🙂

Índice de Pildoritas

Share
30
Oct 13

Pildoritas de Bases de Datos: Optimización. Condiciones SARGABLE

pildoras bbdd

Después de la pildorita de la semana pasada sobre cuestiones de optimización de operadores, aprovecho que tenéis el tema fresco y sobre todo que escribí ambos posts de forma casi simultanea, para desentrañar por fin que es eso de SARGABLE xD

SARGABLE viene del acrónico SARG (Search ARGument) y se refiere a una clausula WHERE que compara una columna con una constante. ¿Y eso que significa? Pues que un WHERE es SARGABLE cuando se puede apoyar en índices para mejorar la respuesta. Los WHERE NON-SARGABLE, en cambio, son aquellos que no pueden ayudarse de índices a la hora de realizar el filtrado de datos. Ahora ya entendeis por qué decía en el tema anterior que había que evitar los operadores NON-SARGABLE.

Como vimos ya por encima, hay algunos operadores que pueden impedir (aunque no siempre) la utilización de índices en un WHERE. Los operadores en cuestión son: “IS NULL”, “<>”, “!=”, “!>”, “!<” (estados dos en caso de que existan para ese motor, ya que no son comunes), “NOT”, “NOT EXIST” , “NOT IN”, “NOT LIKE”. Básicamente todas las que usan NOT)

Además, las expresiones que incluyen una función sobre una columna, comparaciones contra una columna - no es una constante - u operadores con la misma columna en ambos lados, son también NON-SARGABLE.

No obstante, a veces que haya alguna condición con estos operadores no significa necesariamente que el WHERE sea SARGABLE, porque otra condición puede hacer que la consulta utilice un índice que evite el TABLE/INDEX SCAN (es lo que ocurre con WHERE NON-SARGABLE) y realice un COVERED INDEX sin necesidad de utilizar el índice. Esto ocurre cuando el índice incluye los campos devueltos por el SELECT y los del JOIN. Por otra parte, un WHERE puede también estar compuesto por otra clausula que tire de otro índice.

Además, no siempre viene bien hacer un COVERED INDEX, ya que cuando los índices son muy extensos puede aumentar mucho la utilización de CPU y bus de Entrada / Salida, que puede resultar en algunos casos más perjudicial incluso que una query que no utilice índices.

Al igual que en el resto de pildoritas de optimización, vamos a ver una serie de pautas que pueden llevar a mejoras de rendimiento en las consultas.

Pautas para convertir condiciones NON-SARGABLE a SARGABLE.

No utilizar funciones sobre columnas

Como decíamos en la introducción, ésta es una de las causas más comunes de que una condición sea NON-SARGABLE, y muchas veces es perfectamente reescribible a una condición que no se apoye en la columna, sino en una constante (literal, parámetro).

Imaginad que queremos obtener aquellos empleados cuyo Apellido comience por la M. La query sería tal que así:

SELECT NOMBRE, APELLIDO FROM EMPLEADO WHERE SUBSTRING(APELLIDO, 1, 1) = ‘M’

Esta query utiliza sobre la columna APELLIDOS la función SUBSTR, por lo que no es SARGABLE, pero con un pequeño cambio de operador podemos tener el mismo resultado y una query SARGABLE.

SELECT NOMBRE, APELLIDO FROM EMPLEADO WHERE APELLIDO LIKE ‘M%’

Como veis, utilizando el LIKE dejamos de utilizar una función sobre la columna por lo que el WHERE pasa a ser SARGABLE. Más adelante entraremos en consideraciones particulares del operador LIKE, ya que dependiendo de algunos factores de la cadena a comparar, este puede ser NON-SARGABLE.

Otro ejemplo podría ser el de una query que buscara los proyectos que empiezan en menos de un mes de una fecha introducida por parámetro.

SELECT ID_PROYECTO FROM PROYECTO 
WHERE DATEDIFF(FECHA_INICIO , :FECHA_PARAMETRO) < 30

Esta query no es SARGABLE porque se está aplicando una función a una columna. Ahora bien, si pensamos un poco podemos sacar una query equivalente, eso sí, utilizando otro operador.

SELECT ID_PROYECTO FROM PROYECTO 
WHERE FECHA_INICIO > DATE_ADD(:FECHA_PARAMETRO , INTERVAL 30 DAYS)

Operador NOT

El operador NOT siempre es NON-SARGABLE, pero de igual que en el caso anterior, se pueden encontrar formas diferentes de escribir una consulta sin necesidad de usarlo.

Por ejemplo, vamos, a imaginar que nuestra tabla Empleado tiene un campo SALARIO, donde se indica la cantidad de dinero que cobra el trabajador bruto al año.

Si quisiéramos obtener aquellos trabajadores que no cobran más de 20.000€ brutos / años, nuestra primera idea podría ser ésta:

SELECT NOMBRE, APELLIDOS FROM EMPLEADO WHERE NOT SALARIO > 20.000

*También podríamos haber usado el operador !>, que funciona exactamente igual, pero este operador es muy poco común en la mayoría de motores.

Una forma muy sencillita de variar esta consulta y no utilizar el operador NOT es la siguiente:

SELECT NOMBRE, APELLIDOS FROM EMPLEADO WHERE SALARIO <= 20.000

Utilización de índices por otras vías

Pese a estos consejos, muchas veces no podemos deshacernos de las clausulas NON-SARGABLE. En estos casos se pueden definir índices computados, es decir, índices que son resultado de aplicar una función.

Por ejemplo, podríamos definir un índice para las iniciales de apellidos que fuera IDX(SUBSTRING(APELLIDO, 1, 1))

Esta solución sólo debe utilizarse cuando la query se lance constantemente y no haya forma de intentar quitar la función de la propia columna, ya que los índices computados requieren una sobrecarga de la BBDD importante.


Y ya con esto ponemos punto y final a la idea inicial de optimización que os comenté en este post, pero que de nuevo he vuelto a cambiar.

Al hablaros en este tema de cuestiones como TABLE SCAN o COVERED INDEX, he creído conveniente hacer una pildorita más explicando de forma muy básica como lanzar un explain plan e interpretarlo.

Así que nada, todavía queda una pildorita más de BBDD, que odio esto de las despedidas xD

Índice de Pildoritas

Share
23
Oct 13

Pildoritas de Bases de Datos: Optimización. Consideraciones de rendimiento de los operadores

pildoras bbdd

¡Sorpresa! Sí, tras tan sólo una semana de espera vuelven de nuevo las Pildoritas de Bases de Datos. La pildorita de hoy está dedicada a consideraciones de rendimiento de los operadores... OH, WAIT!

Si, dije que primero veríamos los WHERE SARGABLE, pero también que me estaba costando horrores organizarme las ideas y que no era definitivo (ya me imaginaba que esto podía ocurrir xD), así que sintiéndolo mucho, tendréis que esperar un poco más para salir de dudas con el tema del WHERE SARGABLE (culpa vuestra por no haberlo buscado ya 😀 )

Que conste que comencé con el tema de NON-SARGABLE primero, pero al ver que había temas que se me iban a mezclar con los operadores, no me quedó más remedio que darle la vuelta a la idea inicial y comenzar por éste tema.

Como decía (ya me empiezo a liar), en esta pildorita veremos cuestiones sobre el rendimiento de los operadores y posibles alternativas para evitar utilizar aquellos más costosos.

Tanto en esta pildorita como la siguiente hay que tener siempre claro que muchas de estas pautas no son más que simples consejos. La optimización es todo un mundo y muchas veces las pautas típicas de nada sirven y hasta es posible que lo contrario a lo que pensábamos sea la solución. En estos dos temas intentaré dejar siempre claro que esto no son reglas absolutas, pero me gustaría destacarlo para que así lo tengamos siempre presente.

Bueno, no me enrollo más, al lío xD

Rendimiento de operadores en el WHERE

Como decía hace un momento, el rendimiento de los operadores puede variar en cada consulta, pero se pueden clasificar de mejor a peor rendimiento de la siguiente forma:

  • =
  • >, >=, <, <=
  • LIKE
  • <>

A parte del operador en sí, también repercuten en el rendimiento otras cuestiones como el tipo de los operandos utilizados, el orden de estos, etc.

Ordenadas de mejor a peor rendimiento, estas son las otras pautas que también hay que tener en cuenta con respecto a los operadores:

  • Utilizar un literal único en lugar de varios
  • Utilizar un nombre de columna o un parámetro
  • Una expresión multiperando
  • Un número único exacto
  • Un número único no exacto al lado de un operador (date, time)
  • Datos de caracteres o NULL.

No obstante, esto pocas veces tiene mucha importancia ya que no es aplicable cuando el WHERE contiene más de una expresión, así que estos consejos rara vez podrán solucionaros la vida.

Operador IN

Siempre que podamos intentaremos sustituir este operador por cualquiera de las siguientes opciones.

EXISTS

NOT EXISTS, al estar conjugado con el operador NOT es NON-SARGABLE (por ahora sólo basta con saber que es malo xD), pero pese a ello su rendimiento es mejor que el IN muchas veces. Otras veces lo mejor es variar por IN utilizando el conjunto complementario de elementos, o en caso de que sea sólo un caso el comando EXISTS con esos complementarios, ya que es mucho más eficiente que el IN.

Vamos a verlo una vez más con un ejemplo. Imaginemos que queremos ver los datos de empleado de aquellos que no tengan proyecto.

SELECT NOMBRE, APELLIDOS, DNI, TELEFONO, ID_PUESTO, ID_COMPAÑIA 
FROM EMPLEADO WHERE ID_EMPLEADO NOT IN (
SELECT ID_EMPLEADO FROM EMPLEO_HAS_PROYECTO_HAS_DEPARTAMENTO
)

Esta query es transformable a:

SELECT NOMBRE, APELLIDOS, DNI, TELEFONO, ID_PUESTO, ID_COMPAÑIA 
FROM EMPLEADO 
WHERE ID_EMPLEADO EXISTS (
SELECT ID_EMPLEADO FROM EMPLEO_HAS_PROYECTO_HAS_DEPARTAMENTO
)

LEFT OUTER JOIN y chequear por la condición (si es un NOT IN se mirará que sea NULL)

La otra forma de convertir esta query es con un LEFT OUTER JOIN, chequeando que ese campo sea NULL.

De nuevo veamos con el mismo ejemplo la conversión a través del LEFT OUTER JOIN:

SELECT EMP.NOMBRE, EMP.APELLIDOS, EMP.DNI, 
EMP.TELEFONO, EMP.ID_PUESTO, EMP.ID_COMPAÑIA 
FROM EMPLEADO EMP LEFT JOIN EMPLEO_HAS_PROYECTO_HAS_DEPARTAMENTO EPD 
ON EMP.ID_EMPLEADO = EPD.ID_EMPLEADO 
WHERE EMP.ID_EMPLEADO IS NULL

Siempre que queramos comprobar solamente la existencia de un registro utilizaremos el EXISTS en vez del IN.

Si no es viable prescindir del operador IN debemos poner en la parte izquierda los valores más frecuentes de encontrar y al final de la lista los menos frecuentes.

¿Y el operador BETWEEN?

El operador BETWEEN comprueba que un valor se encuentre dentro de un rango, por lo que también puede sustituir en muchas ocasiones a la clausula IN y su rendimiento es mucho mejor. Siempre que podamos escoger, utilizaremos el operador BETWEEN.

SELECT ID_DEPARTAMENTO FROM DEPARTAMENTO WHERE ID_DEPARTAMENTO IN (1, 2, 3, 4)

Por:

SELECT ID_DEPARTAMENTO FROM DEPARTAMENTO WHERE ID_DEPARTAMENTO BETWEEN 1 AND 4

En el caso de que ID_DEPARTAMENTO tenga un índice (cosa casi segura porque es PK), al motor le resultará mucho más fácil calcular el resultado a través del operador BETWEEN.

Utilización del operador LIKE

Cuando utilicemos el operador LIKE debemos de intentar que nuestra cadena de búsqueda no comience jamás por una wildcard (%, *, etc), ya que en estos casos el operador no puede utilizar su índice, lo que le convierte en una clausula NON-SARGABLE y aumenta mucho más su coste.

Operador OR

En la primera pildorita os decíamos que una posible modificación para el UNION era usar OR, pero esto no siempre es verdad. Algunos motores de Bases de Datos como MySQL no utilizan los índices cuando hay operadores OR, por lo que a veces es mejor utilizar un UNION ALL a un OR.

Si tenéis dudas, lo mejor es tirar ambas queries y ver si vuestra tecnología tiene problemas de rendimiento con el OR.

Índice de Pildoritas

Share
16
Oct 13

Pildoritas de Bases de Datos: Optimización. Definición de las selects

pildoras bbdd
Vuelven de nuevo las Pildoritas de Bases de Datos tras el extenso parón de verano.

Las vacaciones y la mudanza no han ayudado, pero sobre todo lo que no ha ayudado es mi limitada capacidad de organizarme las ideas en el tema de Optimización. Resulta curioso que en estas pildoritas -que en un primer momento las pensé orientar exclusivamente a optimización- este tema sea el que más me está costando escribir.

Después de mucho organizar todas esas pautas y consejillos varios que he ido aprendiendo con el tiempo, he llegado más o menos a un esquema definitivo:

  • Consejos sobre la definición de las selects
  • WHERE NON SARGABLE
  • Cuestiones de rendimiento en Operadores y Funciones de agregación.

Quizás no sea un buen orden, pero de verdad que no se me ocurre otra manera de organizar todo este batiburrillo de ideas de la forma más clara posible. Si algún conocedor de optimización pasa por aquí y piensa que hay mejores maneras de organizar la información, gustosamente seré todo oidos 🙂

Comencemos pues con el primer capítulo de la parte de optimización: Pautas a la hora de escribir una consulta SELECT.

Aunque parezca mentira muchas veces cosas tan nimias como el orden, el uso de mayúsculas, o los prefijos pueden influenciar de cara al rendimiento. También hay que tener en cuenta ciertas consideraciones sobre el uso de predicados que abordaremos en este post, los cuales vimos en las últimas pildoritas, y que daban por finalizada la parte de SQL.

Como siempre utilizaremos para nuestros ejemplos el modelo de datos definido en nuestra primera pildorita. Si habeis seguido estas pildoritas estoy segura que ya lo habíais abierto xD

Especifica sólo los campos que quieras obtener

Puede parecer obvio, pero mucha gente casca un * y se queda tan pancha. Esto cuando hay varias tablas en juego puede ser un auténtico dolor para el motor de la base de datos.

Escribe siempre igual la query

Algunos motores SQL como Oracle almacenan en memoria las sentencias que ya han sido ejecutadas para así optimizar futuras respuestas, aunque la sentencia tiene que ser idéntica, y eso incluye el orden de los campos, tablas y condiciones, si esta estaba escrita en mayúsculas y minúsculas, etc.

En el caso concreto de que se tiren estas queries en una aplicación como puedes imaginar lo útil sería reutilizar siempre que se pueda la misma query, y para ello utilizaríamos variables BIND (parámetros).

Por ejemplo, imaginemos que tenemos una aplicación con dos funcionalidades diferentes: Una funcionalidad que obtiene los departamentos de Desarrollo y otra que obtiene aquellos de Sistemas.

Cada funcionalidad se transcribiría en las siguientes consultas:

SELECT NOMBRE FROM DEPARTAMENTO WHERE AREA = 'DESARROLLO'
SELECT NOMBRE FROM DEPARTAMENTO WHERE AREA = 'SISTEMAS'

Cada query es diferente, por lo que el sistema no las tratará como la misma consulta y no será del todo óptimo. Si utilizamos un parametro BIND, en cambio, sí que podremos agrupar ambas consultas en una sola, y que el motor almacene ambos casos como la misma planificación:

SELECT NOMBRE FROM DEPARTAMENTO WHERE AREA = :AREA

Utiliza prefijos / alias.

Una práctica muy extendida es crear alias para las tablas, que no son más que seudónimos para referirse a la misma. Pues bien, si especificamos el nombre de la tabla o en su defecto el alias que se refiere a la misma antes de especificar cada campo, le ahorraremos tiempo al motor de búsqueda, ya que no tendrá que ser él el encargado de identificar a que tabla pertenece cada campo.

En los siguientes ejemplos de este capítulo se utilizan alias para definir la tabla intermedia que relaciona a empleado con proyectos. Además, cuando utilizamos más de una tabla hemos puesto como prefijo el nombre de la tabla a la que pertenece la columna. Aun así, es una buena práctica que lo hagais siempre, no sólo al tener nombres idénticos en diferentes columnas o nombres demasiado largos.

Utiliza TOP / ROWNUM

Si nos vale con un número finito de resultados podemos utilizar las clausulas TOP, que devuelve el primer registro, o ROWNUM, por el cual indicamos exactamente las filas a devolver.

El uso de esta clausula disminuye increiblemente los tiempos de respuesta de la consulta.

Por ejemplo, si queremos obtener el empleado con mayor salario, haremos un ORDER BY, que tiene un coste bastante alto, o utilizaremos en caso de disponer de él el operador MAX. Si limitados los resultados a una fila, que es lo que nos hace falta, podremos mejorar altamente el rendimiento de la query.

Elige buenas condiciones en el WHERE

Otra pauta muy importante es definir en el WHERE las condiciones de más a menos restrictiva e intentar utilizar en dichas condiciones campos que estén contenidos en índices.

Si el índice es compuesto hay que filtrar por todos los campos si es posible, y en caso de no ser así, debe filtrarse por los primeros campos obligatoriamente. Me explico:

Si tengo un índice compuesto tal que IDX(Apellidos, Nombre), es aconsejable filtrar por apellidos y nombre o al menos por apellido. Si filtramos por nombre el índice no se utilizará.

Es más, no uses el WHERE

Los WHERE no son más que joins, y el rendimiento puede mejorar mucho más si en vez de definir las uniones entre claves y las condiciones en el WHERE lo hacemos directamente en la parte del FROM como un INNER JOIN.

El where funciona exactamente igual que la clausula INNER JOIN, con la salvedad de que al motor le cuesta menos procesar las clausulas en el mismo operador, que ir a buscarlas al WHERE.

Para que lo veais mejor utilizaré el primer ejemplo de la pildorita de los joins.

SELECT EMPLEADO.DNI, EMPLEADO.NOMBRE, PUESTO.NOMBRE AS PUESTO
FROM PUESTO, EMPLEADO
WHERE EMPLEADO.ID_PUESTO = PUESTO.ID_PUESTO

Es equivalente a:

SELECT EMPLEADO.DNI, EMPLEADO.NOMBRE, PUESTO.NOMBRE AS PUESTO
FROM PUESTO INNER JOIN EMPLEADO ON EMPLEADO.ID_PUESTO = PUESTO.ID_PUESTO

A mi en particular me gusta utilizar el WHERE siempre que es posible porque me resulta más claro ver los todos los filtrados en el mismo lugar, que "mezclados" junto a las tablas en el FROM. No obstante, esto es una cuestión de gustos, y para muchos la forma más intuitiva es utilizar los INNER JOIN.

Si tenéis problemas de rendimiento deberíais transformar las condiciones del WHERE en INNER JOINs. Sea como fuere, en el INNER JOIN sólo incluiremos la unión entre indices como condiciones y en el WHERE seguiremos indicando otro tipo de filtrados.

GROUP BY y HAVING cuando haya funciones de agregación.

Siempre que se pueda hay que evitar utilizar clausulas en el HAVING y que esos filtrados se realicen en el WHERE, ya que el coste en el HAVING es superior.

Por otra parte, realizar un GROUP BY sin funciones de agregación es factible, pero devuelve lo mismo que un DISTINCT, y el coste del segundo es menor, así que es absurdo utilizar un GROUP BY y no un DISTINCT en agrupaciones sin función de agregación asociada.

Por ejemplo, imaginemos que queremos averiguar los empleados que han trabajado en un determinado proyecto. Puede darse el caso de que un empleado haya estado más de una vez en el mismo proyecto, por lo que se pueden dar repeticiones.

Por tanto, la primera idea puede ser "agrupemos por dni, así no tendremos repeticiones:

SELECT PROY.DNI 
FROM PROYECTO_HAS_EMPLEADO AS PROY_EMP 
WHERE ID_PROYECTO = :ID_PROYECTO
GROUP BY PROY.DNI

Pero lo dicho, aquí no tenemos ninguna función de agregación, por lo que la query es completamente reescribible con el predicado DISTINCT.

SELECT DISTINCT PROY.DNI 
FROM PROYECTO_HAS_EMPLEADO AS PROY 
WHERE ID_PROYECTO = :ID_PROYECTO

Otra cosa sería si quisieramos sacar el número de empleados por proyecto, por poner un ejemplo. En este caso utilizaremos la función de agregación count, así que no hay más alternativa que agrupar por proyectos (es más, si intentarais escribir una query con una función de agregación sin un GROUP BY vuestro motor daría un error).

SELECT COUNT(PROY.DNI)
FROM PROYECTO_HAS_EMPLEADO AS PROY
WHERE ID_PROYECTO =  :ID_PROYECTO
GROUP BY PROY.ID_PROYECTO

Aún así, el DISTINCT con cuidadito.

Siempre que hacemos agrupaciones, sea con el DISTINCT o con el GROUP BY, debemos evaluar concienzudamente si es realmente necesario o no. Muchas veces vemos datos repetidos y cortamos por lo sano con un DISTINCT, pero estas repeticiones pueden deberse a que no se ha filtrado todo lo bien que se podría o falta algún join entre tablas.

En caso de usar un GROUP BY, se austero.

Intenta que la query tenga el mínimo de campos a devolver, así como el menor número posible de funciones de agregación. Como es obvio tampoco se debe agrupar por columnas que son ya de por sí redundantes.

Por otra parte, mucho cuidado con los JOINS cuando realizamos un GROUP BY. Siempre que se puede hay que intentar convertirlo en una subquery, lo cual mejorará el rendimiento. Si no queda más remedio que hacer el JOIN, hay que meter en el GROUP BY columnas de la misma tabla que la que se haya utilizado en la función de agregación.

Utiliza UNION ALL en vez de UNION

Hacer un UNION es lo mismo que hacer un UNION ALL entre dos queries con el predicado DISTINCT, el cual, como ya comentaba en el punto anterior sobre el DISTINCT, significa mayor carga en el rendimiento.

Si sabemos que los datos no se van a repetir ni necesitan una ordenación concreta, utilizaremos la clausula UNION ALL, que tiene un coste muy inferior.

Por ejemplo, si queremos buscar departamentos que sean del area de Desarrollo y departamentos del area de Sistemas, utilizaríamos esta query:

SELECT NOMBRE FROM DEPARTAMENTO WHERE AREA = ‘DESARROLLO’
UNION
SELECT NOMBRE FROM DEPARTAMENTO WHERE AREA = ‘SISTEMAS’

Como comentaba arriba, esta query es exactamente igual a utilizar un UNION ALL con la clausula DISTINCT

SELECT DISTINCT NOMBRE FROM DEPARTAMENTO WHERE AREA = ‘DESARROLLO’
UNION
SELECT DISTINCT NOMBRE FROM DEPARTAMENTO WHERE AREA = ‘SISTEMAS’

Ahora bien, ¿en nuestro modelo pueden existir departamentos que sean tanto del área de Sistemas como de Desarrollo? Si así fuera el caso, el uso de UNION tendría todo el sentido del mundo, pero habitualmente no hay departamentos mixtos, por lo que en este caso no habrá resultados repetidos y lo inteligente es utilizar el predicado UNION ALL, que es mucho más eficiente:

SELECT NOMBRE FROM DEPARTAMENTO WHERE AREA = ‘DESARROLLO’
UNION ALL
SELECT NOMBRE FROM DEPARTAMENTO WHERE AREA = ‘SISTEMAS’

Aún así tanto UNION como UNION ALL tienen un coste muy elevado, por lo que siempre hay que usarlo cuando sea estrictamente necesario, ya que muchas veces lo utilizamos cuando esa query es perfectamente reescribible en una query simple, ya sea por medio de un JOIN, operadores lógicos OR, etc.

Para ilustrarlo nada mejor que un ejemplo más.

Tenemos esta query con un UNION:

SELECT NOMBRE FROM DEPARTAMENTO WHERE AREA = ‘DESARROLLO’
UNION ALL
SELECT NOMBRE FROM DEPARTAMENTO WHERE AREA = ‘SISTEMAS’

La cual es igual a:

SELECT NOMBRE FROM DEPARTAMENTO WHERE AREA = DESARROLLO OR AREA = ‘SISTEMAS’

O a ésta otra si utilizamos el operador IN:

SELECT NOMBRE FROM DEPARTAMENTO WHERE AREA IN (‘DESARROLLO’, ‘SISTEMAS’)

Vamos, que si tienes algún problema de complejidad con una query que contiene una UNION, lo primero que debes pararte a pensar es si no es posible reescribirla sin utilizar dicho predicado.

Utilizar ORDER BY tampoco es recomendable

El ORDER BY es otro de esos predicados con un coste de rendimiento altísimo.

Siempre que podamos intentaremos hacer esa ordenación de lado del cliente y no en BBDD, pero si no existe más remedio hay que seguir una serie de pautas parecidas a las del GROUP BY.

Hay que intentar utilizar el mínimo número de campos en la ordenación y devolver también lo mínimo posible en la parte SELECT. El tamaño físico de las columnas de ordenación también deben de ser lo más pequeño posible. Por último, hay que utilizar para las ordenaciones campos numéricos en vez de tipos de dato carácter.

 

Y con esto ya hemos terminado la primera parte de optimización SQL. En el próximo capítulo veremos los operadores SARGABLE y NO SARGABLE, explicaremos que demonios significa eso, y que implicaciones tiene. Eso sí, no se si este tema se cubrirá con un sólo post o harán falta más. En su momento ya se verá 🙂

Índice de Pildoritas

Share
11
Oct 13

¡Vuelta a la vida!

Si habéis intentado entrar estas semanas al blog habréis visto un hermoso error que indicaba de forma vaga un problema en el espacio de la BBDD:

Fatal error: Out of memory (allocated 46399488) (tried to allocate 2490419 bytes) in /homepages/24/d222083343/htdocs/wordpress/wp-includes/wp-db.php on line 952

Efectivamente, mi BBDD estaba hasta los topes. Le quedaban nada más que 13MB de espacio libre. Además, no me preguntéis en que pensaba el día que configuré el wordpress pero cogí una de las BBDD secundarias de 1and1, que son de 100MB, y no la principal de 1GB.

Así que nada, me tocó exportar la BBDD del esquema en el que estaba al principal, con la dificultad añadida de que phpmyadmin es una jodida basura que no te deja exportar esquemas grandes, por lo que tuve que ir tabla a tabla u_U!

Después de migrar la BBDD con éxito, me encuentro que el error sigue produciéndose, lo cual descoloca bastante, porque problemas de espacio en BBDD ya no había...

Como la informática es un campo digno de Iker Jimenez y los misterios de la informática se mean en los de la nave del misterio, no me quedó otra que "ponerle una velita" a Google y “pedirle ayuda”.

Miro en el santísimo Google y encuentro varias soluciones en las que todas ellan apuntan a la necesidad de indicar un límite máximo de BBDD, utilizando para ello el archivo php.ini (resulta que 1and1 no te deja ese nivel de administración), .htaccess o el wp-config.php. Ninguna de las opciones funcionó.

Así que nada, decidí cortar por lo sano, e imaginándome que podía ser un problema de los plugins (después de tanta búsqueda vi que era común que esto pasara con las actualizaciones de plugins), hice un backup y me los calcé. Con ello el problema se solventó 😀

Luego fui uno a uno recuperándolos hasta que di con el plugin maldito, el Next Gen Gallery. Lo he bajado de nuevas y ya va bien, así que nada, ha costado, entre otras cosas porque últimamente mi tiempo por casa es casi nulo, pero la web de nuevo ha vuelto a la vida.

Eso sí, no creáis que he perdido el tiempo (no demasiado :P), porque he comenzado ya a escribir la parte de optimización de las Pildoritas de Bases de Datos, eso sí, aún me queda rematarla y sigo sin tener claro los posts siguientes.

En un primer lugar daré recomendaciones a la hora de construir las SELECT, que por sí mismas mejorarán de normal el rendimiento. Este punto sí lo tengo claro y es el que estoy desarrollando.

Tras esto, mi idea era ver los WHERE NO SARGABLE, y a continuación dar consejos sobre operadores y funciones de agregación (utilización del LIKE, uso del IN y EXISTS, etc).

¿Qué os parece el orden?

Si creéis que hay una forma mejor de ordenar el tema de optimización, no dudéis en dejar vuestras opiniones en los comentarios. En cuanto al primer post, espero que esté listo este fin de semana 😀

Share
20
Jun 13

Pildoritas de Bases de Datos: SQL. Predicados

pildoras bbdd
¡Hola corazones! Una vez más vuelvo con una pildorita de Bases de Datos y de nuevo es otra pildorita de SQL, pero tengo una buena noticia, es la última 😀

Como os comenté en el anterior artículo el tema de hoy serán los predicados y va a ser muy cortito.

Predicados

Los predicados son palabras reservadas que se indican entre SELECT y el nombre de la primera columna, y que indican ciertas características sobre los conjuntos recogidos en la consulta.

Los predicados principales son los siguientes:

Predicado Descripción
ALL Devuelve todos los registros de la consulta. Equivale a no indicar nada entre los campos y el SELECT.
DISTINCT Omite del resultado los registros que tienen los mismos datos en los campos seleccionados.
DISTINCTROW Omite del resultado los registros repetidos (en su totalidad, no sólo en los campos seleccionados)
TOP/LIMIT /ROWNUM Devuelve sólo los registros que se indiquen después de la palabra reservada.

 

Como veis este tema tampoco es que tenga mucha chicha, pero viene bien conocer los predicados porque se utilizan mucho en el día a día. Pondré varios ejemplos para que veáis su potencial.

Vamos a usar de nuevo nuestra amada tabla empleados del anterior post:

empleado

Imaginad que obtenemos los Apellidos de los empleados.

SELECT APELLIDOS FROM EMPLEADO

Puede ocurrir el caso de que existan en nuestra tabla dos personas con los mismos apellidos. En ese caso, obtendríamos dos registros, uno por cada empleado. Si queremos que no haya repeticiones, utilizaríamos el predicado DISTINCT.

SELECT DISTINCT APELLIDOS FROM EMPLEADO

Por otra parte, imaginaos que queremos saber quiénes son las 20 personas de la empresa que cobran más. Para eso utilizaríamos el predicado TOP, junto a la clausula ORDER BY.
Este comando es de los que he visto variar más entre los diferentes motores de búsqueda. Por tanto, indicaré la query para cada motor:

SQLServer

SELECT TOP 20 * EMPLEADOS ORDER BY SALARIO DESC

MySQL

SELECT * EMPLEADOS ORDER BY SALARIO DESC LIMIT 20

Oracle

SELECT * EMPLEADOS WHERE ROWNUM >= 20 ORDER BY SALARIO DESC

Como veis en MySQL y Oracle estas funcionalidades no son predicados como tal, sino clausulas - en el caso de MySQL - o incluso operadores dentro del WHERE (Oracle). No obstante, aunque no se coloquen después de la palabra SELECT su función es exactamente la misma.

DISTINCTROW os puede parecer igual a DISTINCT, pero tiene una diferencia notable: No afecta a los campos de la query repetidos, sino al registro completo.

Por ejemplo, imaginemos que un empleado de nuestro modelo ha estado en dos proyectos. Primero trabajo con una compañía y un tiempo más tarde entra al mismo proyecto pero siendo trabajador de otra cárnica. Si queremos saber simplemente en que proyectos ha trabajado deberíamos utilizar DISTINCTROW, ya que de otra forma tendríamos resultados repetidos.

ejemplo distinctrow

SELECT DISTINCTROW 
PROYECTO.NOMBRE 
FROM 
EMPLEADO, PROYECTO, PROYECTO_HAS_EMPLEADO 
WHERE 
PROYECTO.ID_PROYECTO = PROYECTO_HAS_EMPLEADO.ID_PROYECTO 
AND EMPLEADO.ID_EMPLEADO = PROYECTO_HAS_EMPLEADO.ID_EMPLEADO 
AND ID_EMPLEADO = 3

Bueno, con esto doy por finalizada la parte de SQL de las Pildoritas de Bases de Datos, aunque he sido un poco malvada, porque realmente no hemos terminado con SQL, ya que lo seguiremos usando en lo que queda culturitas. Dejemoslo en que hemos dejado atrás la parte coñazo de aprendizaje del lenguaje en sí. En siguientes pildoritas empezaremos con optimización de SQL.

Índice de Pildoritas

Share
5
Jun 13

Pildoritas de Bases de Datos: SQL. JOIN

pildoras bbdd
En las últimas pildoritas hemos adquirido conocimientos bastante aceptables de sintáxis SQL, pero hasta este momento no hemos utilizado álgebra relacional, que es el verdadero potencial de las Bases de datos SQL. Os pido que a partir de ahora penséis en los datos de tablas o los resultados de una query como conjuntos de datos, si es que no lo hacíais ya.

En muchas ocasiones, por no decir la gran mayoría, necesitaremos datos que se encuentran repartidos en varias tablas. Para ello, nos apoyaremos en la operación por antonomasia para relacionar tablas, el JOIN.

JOIN es el operador cartesiano de Algebra relacional y combina los registros de dos tablas tal que su resultado es la combinación de todos los registros de la primera tabla, con los de la segunda.

Un JOIN, como digo, es una relación entre tablas. Por tanto, ambas tablas deben tener al menos un campo común entre ellas con el que poderlas relacionar. Dichos campos suelen ser keys de las tablas.

El JOIN más típico, es aquel que realizamos de la siguiente forma:

SELECT EMPLEADO.DNI, EMPLEADO.NOMBRE, PUESTO.NOMBRE AS PUESTO 
FROM PUESTO, EMPLEADO 
WHERE EMPLEADO.ID_PUESTO = PUESTO.ID_PUESTO

Con esta consulta obtenemos el DNI, nombre y puesto del empleado en la misma consulta, relacionando como se ve las tablas de Empleado y Puesto. Como vemos, lo único que hemos hecho es igualar la PK de Puesto (FK en Empleado) de ambas tablas.

Este JOIN realizado en el WHERE es igual al INNER JOIN, pero no nos adelantemos, poco a poco.

Existen muchas variaciones del JOIN, así que primeros vamos a ver los básicos: JOIN, LEFT JOIN, RIGHT JOIN, INNER JOIN y FULL OUTER JOIN.

Para los ejemplos de esta sección utilizaremos las tablas de Empleado y Puesto:

Joins (tablas ejemplo)

Por otra parte, los datos de las tablas serán los siguientes:

Empleado

DNI idPuesto idCompañia Nombre Apellidos Teléfono
87190290G 1 1 Pepe Padefo 666666666
28050763D 1 2 Antonia Picateclas 688888888
82500395Q 3 1 Eustaquio Traumas 699999999
01148640C 2 5 Augusto Flipao 655555555
70686067Y NULL 2 Pablo Unknow 644444444

 

Puesto

idPuesto Nombre Descripción
1 Programador Blablau!
2 Analista Blablauuuu!
3 Jefe de Proyecto RequeteBlablauuu!
4 Gerente MegaBlablauuuu!

 

INNER JOIN

Como comentaba justo arriba, el JOIN que se da al igualar la Key de dos claves es el INNER JOIN. Este sólo nos devolverá los datos que sean comunes a ambas tablas (esto tiene sentido si, por ejemplo, hay empleados sin un puesto). El JOIN simple es también un INNER JOIN.

SELECT 
EMPLEADO.DNI, 
EMPLEADO.NOMBRE,
PUESTO.ID_PUESTO,
PUESTO.NOMBRE AS PUESTO 
FROM PUESTO INNER JOIN EMPLEADO 
ON EMPLEADO.ID_PUESTO = PUESTO.ID_PUESTO

inner join

DNI NOMBRE ID_PUESTO PUESTO
87190290G Pepe 1 Programador
28050763D Antonia 1 Programador
82500395Q Eustaquio 3 Jefe Proyecto
01148640C Augusto 2 Analista

 

RIGHT JOIN

El RIGHT JOIN fuerza a que aparezcan todos los registros de la tabla de la derecha. Si no hay vinculación con uno de ellos, se colocarán los campos referenciados de la tabla de la izquierda a NULL.

SELECT EMPLEADO.DNI, 
EMPLEADO.NOMBRE, 
PUESTO.ID_PUESTO, 
PUESTO.NOMBRE AS PUESTO 
FROM PUESTO RIGHT JOIN EMPLEADO 
ON EMPLEADO.ID_PUESTO = PUESTO.ID_PUESTO

right join

DNI NOMBRE ID_PUESTO PUESTO
87190290G Pepe 1 Programador
28050763D Antonia 1 Programador
82500395Q Eustaquio 3 Jefe Proyecto
01148640C Augusto 2 Analista
NULL NULL 4 Gerente

 

LEFT JOIN

El LEFT JOIN fuerza a que aparezcan todos los registros de la tabla de la izquierda. Si no hay vinculación entre alguno de esos registros y los de la tabla de la derecha, se colocarán los campos de la tabla de la derecha a NULL.

SELECT 
EMPLEADO.DNI, 
EMPLEADO.NOMBRE, 
PUESTO.ID_PUESTO, 
PUESTO.NOMBRE AS PUESTO 
FROM PUESTO LEFT JOIN EMPLEADO 
ON EMPLEADO.ID_PUESTO = PUESTO.ID_PUESTO

left join

DNI NOMBRE ID_PUESTO PUESTO
87190290G Pepe 1 Programador
28050763D Antonia 1 Programador
82500395Q Eustaquio 3 Jefe Proyecto
01148640C Augusto 2 Analista
70686067Y Pablo NULL NULL

 

FULL OUTER JOIN

El FULL OUTER JOIN realiza un producto cartesiano entre ambas tablas y obtiene las combinaciones entre todos los registros, haya relación o no.

SELECT 
EMPLEADO.DNI, 
EMPLEADO.NOMBRE, 
PUESTO.ID_PUESTO, 
PUESTO.NOMBRE AS PUESTO 
FROM PUESTO FULL OUTER JOIN EMPLEADO 
ON EMPLEADO.ID_PUESTO = PUESTO.ID_PUESTO

full outer join

DNI NOMBRE ID_PUESTO PUESTO
87190290G Pepe 1 Programador
28050763D Antonia 1 Programador
82500395Q Eustaquio 3 Jefe Proyecto
01148640C Augusto 2 Analista
70686067Y Pablo NULL NULL
NULL NULL 4 Gerente

 

Con esto hemos acabado los JOIN, pero no con el tema, ya que ahora vamos a ver operadores que en vez de combinar tablas, combinan los resultados de varias queries.

UNION / UNION ALL

UNION es como una suma, o un OR lógico para ser más correctos. Lo que hace es unir los registros devueltos por dos consultas en un único conjunto de resultado. Para ello, es obligatorio que ambas queries tengan las mismas columnas en el SELECT.

Si se quiere que se muestren registros duplicados usaremos UNION ALL.

SELECT DNI, NOMBRE FROM EMPLEADO WHERE ID_PUESTO = 1

UNION

SELECT DNI,NOMBRE FROM EMPLEADO WHERE ID_PUESTO = 2

union

 

DNI NOMBRE
87190290G Pepe
28050763D Antonia
01148640C Augusto

 

INTERSECT

INTERSECT es parecido al operador UNION, pero en su caso aplica un AND lógico en vez de un OR.

SELECT DNI, NOMBRE FROM EMPLEADO WHERE ID_PUESTO = 1

INTERSECT

SELECT DNI, NOMBRE FROM EMPLEADO WHERE DNI =  ‘28050763D’

intersect

 

 

DNI NOMBRE
28050763D Antonia

 

MINUS

El operador MINUS lo que hace es subtraer de la primera query los datos devueltos de la segunda que se encuentren en ella.

SELECT DNI, NOMBRE FROM EMPLEADO WHERE ID_PUESTO = 1

MINUS

SELECT DNI, NOMBRE FROM EMPLEADO WHERE DNI =  ‘28050763D’

minus

 

 

DNI NOMBRE
87190290G Pepe

 

 

 

Ahora ya sí, terminamos con el tema de los JOIN. En la siguiente pildorita veremos los predicados, tema que pone fin (ya era hora) a la parte de SQL.

Índice de Pildoritas

Share
29
Apr 13

Pildoritas de Bases de Datos: SQL. Clausulas I

pildoras bbdd

En la entrada anterior de Pildoritas de Bases de Datos comenzamos con SQL viendo los comandos de definición de la base de datos (DDL) y los de manipulación de los datos (DML). Lo siguiente que vamos a ver son las clausulas, una parte importantísima en la definición de comandos DML.

Donde mejor se puede ver la importancia de las clausulas es en las consultas así que os adelanto que a partir de ahora vais a ver el comando SELECT hasta en la sopa.

Clausulas

Las clausulas son condiciones de filtrado para manipular datos.

Ya os adelanté un poco por encima en el anterior post las clausulas FROM y WHERE, pero ni siquiera rascamos un poco el potencial de estas clausulas. Paso a explicarlas y luego ya, como os prometí en el anterior post, veremos ejemplos prácticos donde aplicaremos lo aprendido.

FROM

Especifica de dónde queremos obtener los datos, es decir, de que tabla. Se utiliza no sólo en el comando de consulta, SELECT, sino también en los comandos UPDATE y DELETE por razones obvias, si no indicas donde está al menos lo que quieres manipular, mal vamos.

WHERE

Está clausula es donde se indican las condiciones de filtrado de los datos. Estas condiciones se definen a través de los operadores lógicos y de comparación. Pero no nos adelantemos aún, cada cosa a su tiempo 😉

Esta clausula también se usa en todos los comandos DML.

GROUP BY

La clausula GROUP BY sirve para agrupar la información. Es de gran utilidad combinada con las funciones de agregado que más adelante explicaremos.

Esta clausula es aplicable exclusivamente al comando SELECT.

HAVING

La clausula HAVING tiene siempre que ir acompañada de un GROUP BY. En ella indicamos las condiciones que debe satisfacer cada grupo y donde utilizaremos funciones de agregación. Como en el caso del GROUP BY de utilizarla será en el SELECT.

ORDER BY

Sirve para ordenar la información obtenida a través de la consulta en un determinado orden.

 

Una vez conocidas las clausulas, vamos a ver los operadores lógicos, de comparación y los predicados, lo cual nos servirá para poder indicar nuestras condiciones de filtrado en la clausula WHERE.

Operadores condicionales

Los operadores condicionales se utilizan en la lógica proposicional para admitir o rechazar proposiciones, así como para conectarlas. Los valores de estas proposiciones tendrán un valor Verdadero/Falso (0 o 1 o apagado y encendido, si lo llevamos al campo de los circuitos digitales). Los operadores lógicos operan con estas proposiciones para dar lugar a nuevos valores lógicos.

Los operadores lógicos son los cimientos de la informática, así que confío en que si habéis llegado a un manual de SQL este concepto lo teneis medianamente claro. Si no es así, os recomiendo una lectura al artículo de Wikipedia donde se explica el Álgebra de Boole.

AND

El operador AND se traduce en lógica y matemáticas como conjunción. Su resultado será verdadero si ambos operandos son verdaderos.

A B A AND B
Verdadero Verdadero Verdadero
Verdadero Falso Falso
Falso Verdadero Falso
Falso Falso Falso

OR

El operador OR es lo que en lógica se conoce como disyunción. Su resultado será verdadero cuando uno o más de sus operandos es verdadero.

A B A OR B
Verdadero Verdadero Verdadero
Verdadero Falso Verdadero
Falso Verdadero Verdadero
Falso Falso False

NOT

El operador NOT, o negación, opera sobre un sólo operando y nos devuelve su valor contrario.

A NOT A
Verdadero Falso
Falso Verdadero

 

* Para indicar estos valores en SQL indicaremos los vocablos anglosajones TRUE (Verdadero) y FALSE (Falso).

Operadores de comparación

Los operadores de comparación también son operadores lógicos como los condicionales, ya que nos devolverán un valor booleano, es decir, verdadero o falso.

Estos operadores comparan el valor de dos valores y determinan la relación existente entre ambos.

Operador Descripción
= Igual que
< Menor que
> Mayor que
<> Distinto de
<= Menor o igual que
>= Mayor o igual que
BETWEEN Existencia del valor entre un intervalo de valores
LIKE Se aplica con valores de tipo cadena y se utiliza para encontrar registros coincidentes a un patrón modelo
IN Compara conjuntos de valores
IS Compara si dos valores lógicos son iguales

 

Los operadores de comparación suelen ser usados junto a los operadores condicionales para construir expresiones más complejas. Por ejemplo, podríamos utilizar dos operadores diferentes de comparación con el operador lógico AND para determinar si ambas expresiones son verdaderas.

Dependiendo del motor que utilicemos, encontraremos otros muchos operadores de comparación, pero para eso no hay más que echarle un vistazo rápido a la documentación de dicha tecnología.

Ejemplos

Y ahora, como os prometí, vamos a ver algunos ejemplos prácticos donde apliquemos lo aprendido arriba, que es la mejor forma de asimilar todo este tocho de teoría. Los ejemplos de esta sección todavía no serán demasiado complejos, ya que por ahora lo mejor es no complicarnos mucho y asimilar la información poco a poco. Más adelante, cuando termine con la teoría de SQL haré una pildorita exclusiva de ejemplos prácticos.

Para este y para la mayoría de ejemplos utilizaremos la base de datos que definimos en el primer post de las pildoritas.

Entidad-Relacion-Final

Obtener el nombre y apellidos del empleado con DNI 99999999R

SELECT NOMBRE, APELLIDOS FROM EMPLEADOS WHERE DNI = '99999999R'

Obtener los proyectos que hayan empezado a partir del 01/01/2012 y hayan acabado antes de 31/12/2012.

SELECT * FROM PROYECTO 
WHERE FECHA_INICIO > '01/01/2012' AND FECHA_FIN < '31/12/2012'

* Como veis en la parte donde deberíamos indicar los campos hemos utilizado el carácter asterisco. Esto indica que queremos todos los campos de la tabla o tablas indicadas en el FROM

Obtener el nombre de todos los departamentos que estén en las áreas de Desarrollo y Bases de Datos

SELECT NOMBRE FROM PROYECTO WHERE AREA IN ('Desarrollo', 'Bases de Datos')

Obtener todas las compañías cuyo nombre empiece por A y sean de tipo S.L.

SELECT * FROM COMPAÑIA WHRE NOMBRE LIKE'A%' AND TIPO = 'S.L.'

* El caracter % que colocamos después de la A es el llamado carácter comodín y se utiliza para indicar cualquier cadena.

 

Y con esto ponemos fin a la segunda pildorita de SQL. Siento si me he extendido un poco en el tamaño de este post, pero no he visto forma humana de poder dividirlo en más partes sin que se pierda el contexto.

En la próxima pildorita veremos el resto de clausulas que nos quedan, GROUP BY y HAVING, y las funciones de agregación.

Índice de Pildoritas

Share
16
Apr 13

Pildoritas de Bases de Datos: SQL. Tipos de Comandos

pildoras bbdd
A petición popular (si entendemos como popular más de una persona) sigo con las Pildoritas de Bases de Datos.

Después de explicar el diseño de modelos relacionales vamos a pasar a la chicha, es decir, a crear modelos de datos relacionales y manipularlos. Para realizar esta tarea todos los motores de bases de datos relacional usan un lenguaje de datos normalizado (aunque con leves diferencias entre tecnologías) llamado SQL.

SQL (Structured Query Language) es un lenguaje declarativo de acceso a bases de datos que aplica álgebra relacional para obtener y manipular conjuntos de datos desde un modelo relacional (ahora seguramente entendáis por qué empecé con los dos anteriores posts). SQL también permite ejecutar otra serie de comandos enfocados en la definición de modelos de datos relacionales.

SQL está compuesto de dos tipos de comandos:

  • DLL (Data Definition Language): Permite crear y definir bases de datos, tablas, campos, índices...
  • DML (Data Manipulation Language): Filtra y ordena los datos que queremos manipular.

 

Comandos DDL

CREATE

Sirve para crear un nuevo objeto dentro del gestor de bases de datos. Los objetos de una bases de datos son tablas, índices, procedimientos almacenados y vistas.

Creación de una base de datos:

CREATE DATABASE NOMBRE_BASE_DE_DATOS

Creación de un índice:

CREATE INDEX NOMBRE_ÍNDICE ON "NOMBRE_TABLA" (NOMBRE_COLUMNA)

Creación de un procedimiento almacenado:

CREATE CREATE PROCEDURE NOMBRE_PROCEDIMIENTO
[PARAMETROS] 
AS 
(Sentencias SQL)
[RETURN [VALOR]]

*La definición de los procedimientos almacenados suele cambiar dependiendo del motor de bases de datos utilizado. En este caso he utilizado SQL Server, no obstante, la estructura es muy similar en el resto de motores, por lo que para definir un procedimiento almacenado en otra bases de datos con mirar la documentación os bastará.

Creación de una vista:

CREATE VIEW NOMBRE_VISTA AS
SUBCONSULTA;

ALTER

El comando ALTER permite realizar modificaciones en un objeto de la base de datos ya existente.

El uso más común del comando ALTER es el enfocado en la edición de una tabla, ALTER TABLE.

Por ejemplo, para agregar una nueva columna a una tabla ya existente ejecutaríamos el siguiente comando:

ALTER TABLE 'NOMBRE_TABLA' ADD NUEVO_CAMPO TIPO;

No obstante, el comando ALTER, como todos los comandos DLL, es aplicable a cualquier objeto de la base de datos.

DROP

Sirve para eliminar objetos de la base de datos. Por ejemplo, podríamos borrar una tabla con la siguiente sentencia:

DROP TABLE 'NOMBRE_TABLA'

 

Comandos DML

SELECT

Este comando sirve para recuperar un conjunto de datos siguiendo unos criterios de filtrado.

El comando SELECT, así como los comandos UPDATE y DELETE, necesitan su vez de una serie de clausulas, en las cuales entraré en el próximo post, que indicaran de donde obtener la información y que condiciones deben cumplir dichos datos.

No obstante, con indicar la procedencia de los datos ya podríamos crear nuestra primera consulta:

SELECT CAMPO FROM TABLA

Esta sentencia recuperaría todos los datos de la tabla, pero como podéis imaginar podemos querer filtrar estos campos para obtener sólo un subconjunto (utilizando las clausulas WHERE y HAVING) o agruparlos y ordenarlos de una determinada forma (por medio de las clausulas GROUP BY y ORDER BY)

INSERT

la sentencia INSERT agrega registros a la base de datos. La forma básica de este comando es el siguiente:

INSERT INTO 'NOMBRE_TABLA' ('COLUMNA1',['COLUMNA2,... '])
VALUES ('VALOR1', ['VALOR2,...'])

No obstante, y dependiendo del motor de bases de datos, se pueden insertar en una sentencia varios registros indicados de forma literal o incluso por medio de una consulta.

UPDATE y DELETE

La sentencia UPDATE sirve para modificar los valores de un conjunto de registros existentes en una tabla.

UPDATE NOMBRE_TABLA SET CAMPO = 'VALOR' WHERE CAMPO = 'VALOR';

El comando DELETE elimina uno o varios registros de una tabla:

DELETE FROM NOMBRE_TABLA WHERE CAMPO = 'VALOR';

Y hasta aquí ha llegado la primera pildorita de SQL. Se que ha sido un peñazo terrible, pero hay que empezar con un poco de teoría inicial para poder enterarnos de la sintaxis básica del lenguaje. En las siguientes entregas intentaré que la lectura sea más práctica y amena.

En el próximo post hablaré de las clausulas, los operadores lógicos y las funciones de agregado.

Índice de Pildoritas

Share
14
Mar 13

Pildoritas de Bases de Datos: Diseño de Bases de Datos II

pildoras bbdd

En el anterior artículo de Pildoritas de Bases de Datos hablamos largo y tendido del Diseño de Bases de Datos, en concreto sobre la realización de un digrama Entidad / Relación y su posterior normalización.

Cuando un modelo de datos está normalizado hasta la tercera forma normal nuestro diseño ya es bastante bueno. Gracias a la normalización no deberías tener datos redundantes, ni columnas iguales con enumeraciones (proyecto 1, proyecto2…proyecto n), etc.

No obstante, hay otras consideraciones que debemos tener en cuenta y que paso a mencionar a continuación. Son algunas recomendaciones que creo os resultarán de ayuda si tenéis algún día que realizar el diseño de una BBDD.

No guardes datos innecesarios ni consultes campos que no requieras

Hay que grabarse a fuego una verdad que a veces obviamos: No hay nada más costoso en tiempo que el acceso a BBDD. Por tanto, siempre tenemos que evitar acceder a ella de forma “gratuita”.

Mucha gente piensa eso de que no hay nada malo en guardar todo lo posible en BBDD. De hecho, cuanto más guarde pues mejor, no vaya a ser que un futuro haya datos que no almacenaba y luego hagan falta.

Hay que tener presente que la BBDD consiste en pedir a un servicio (muchas veces en otra máquina) que lea de disco una información y nos la devuelva, así que cuanto mayor sea el tamaño de estas tablas, mayor tiempo nos llevará acceder a ellas.

Es por ello que aquellos datos que puedan calcularse a partir de otros no deben de estar almacenados en la bases de datos. Así mismo, si tenemos que recoger datos de BBDD, deberemos de recuperar los campos que sean necesarios, no hacer, por ejemplo, un SELECT * FROM tabla.

Selecciona el tipo de dato y longitud adecuados

Parece una tontería pero veo a mucha gente poco cuidadosa en este aspecto. Por ejemplo, a la hora de crear un campo de texto, mucha gente decide crear un campo VARCHAR por pura costumbre, cuando a lo mejor en un caso concreto los valores de un campo siempre van a tener una longitud fija y merece la pena mejor crearlo de tipo CHAR, ya que ocupa menos. Otro ejemplo es crear un campo de tipo DATETIME cuando lo que se necesita almacenar es sólo la fecha y valdría un campo DATE. En general, hay que pararse un segundo a ver de qué tipo de dato es cada atributo y con qué longitud debemos crear cada campo.

También hay que tener en cuenta la integridad de datos. En el ejemplo del anterior artículo el Nombre y Apellidos del empleado no deberían aceptar caracteres nulos, o el DNI, en caso de que no fuera una PK, debería de ser único. Podemos pensar que esa validación no tiene sentido si no vamos a recibir ese campo vacío nunca por imposición de la lógica de negocio, pero hay amigos, los caminos del desarrollo software son inescrutables, así que mejor poner control desde BBDD no vaya a ser que nos llevemos una sorpresita y a algún proceso le de por saltarse esta norma y meter a un pobre empleado sin nombre ni apellidos.

Crea índices cuando sea posible

Los índices funcionan un poco como el índice de los libros (así a groso modo y por no liar mucho) y sirven para agilizar el tiempo de ejecución de las consultas de SQL. Usando la analogía del libro, si queremos ir al Capítulo 5, en vez de ir página a página hasta que lleguemos a él, mejor mirar el índice e ir a la página directamente.

La primera pregunta que nos asalta a todos cuando oímos por primera vez hablar sobre ellos es “¿Por qué no ponemos índices en todas las columnas?

A parte de que guardar las relaciones de los índices y los datos de la columna ocupa espacio (a veces más que la propia tabla), tenemos que tener en cuenta que cada vez que insertemos, borremos o actualicemos en la BBDD, tendremos también que consumir tiempo en actualizar la relación del índice.

Por tanto, tener demasiados índices en el fondo acaba empeorando el tiempo de respuesta global del acceso a la base de datos, por lo que crearemos un índice cuando tengamos claro que es beneficioso.

¿Cómo sabemos que es beneficioso? Pues es difícil saberlo. Lo mejor con los índices siempre es realizar un explain plan antes y después de añadir el índice para ver si mejora la respuesta, pero intentaremos marcar unas pautas generales que nos ayudarán a la hora de crear índices eficientes.

Para empezar los índices deben encontrarse más como filtro de nuestras consultas (es decir, en el WHERE) que ser parte de los datos que obtenemos (los campos indicados tras el SELECT).

Tampoco es buena idea marcar como índice aquella columna que tiene muchos valores idénticos. Por ejemplo, una columna booleana que indica True o False no tendría sentido colocarla como índice. Por tanto, un índice debe ser un campo donde muchos de los valores sean distintos. Si la tabla tiene pocos registros tampoco merece la pena crear un índice, ya que no mejorará sustancialmente la el tiempo de respuesta.

Por último, hay que tener también en cuenta cuando se crea un índice la regla de la izquierda. Si filtramos por dos campos en el WHERE muy a menudo, más que indicar dos índices por cada campo deberíamos crear un índice multicolumna. A la hora de utilizar dicho índice multicolumna tenemos que tener en cuenta la regla de la izquierda que os comentaba: Hay que incluir en el WHERE las columnas indexadas por orden de izquierda a derecha al igual están definidas en el índice o si no, la consulta no usará dicho índice. Además, en los índices multicolumna si sólo hacemos referencia a uno de sus campos no usaremos el índice completo, por lo que hay que tener muy claro cuando definir un índice de este tipo en vez de uno simple. Hablaré sobre este tema de nuevo en la parte de optimización SQL.

Define una hoja de estilos y mantenla siempre

En algunos casos, como MySQL, hay diferenciación entre mayúsculas y minúsculas en el nombre de las tablas, es por ello que viene bien definir un estándar a la hora de ponerles un nombre: Todas en mayúscula o en formato camelCase. Tampoco es recomendable utilizar caracteres especiales a la hora de nombrar tablas o campos.

Si todos los campos se deciden nombrar en singular, esto tiene que ser así siempre. Por otra parte, si estas desarrollando una aplicación que puede ser utilizada por gente de otros países es recomendable usar siempre nombres en inglés.

Resumiendo, márcate tu propio estándar o usa uno ya existente, pero sigue ese estándar a muerte.

Las reglas que yo suelo usar, pero que pueden ser perfectamente otras, son las siguientes:

  • Separa las palabras a partir de un guión bajo o siguiendo el estilo camelCase. En mi caso prefiero la primera opción.
  • Nombra la tabla y los campos en singular.
  • Si tienes muchas tablas que pueden ser agrupadas en grupos lógicos, utiliza un prefijo que te ayude a identificarlas.
  • Intenta que los nombres sean descriptivos y evita abreviaciones, pero sin que sean demasiado largos.
  • Los campos Primary Key deben empezar por el prefijo ID.
  • Primero se crearán los campos PK, seguidos luego de las FK.
  • Escribir todo en mayúsculas o minúsculas. En mi caso soy más de la primera opción.
  • No utilizar caracteres numéricos, al menos en el nombre de las tablas.
  • Los campos que estén en varias tablas (como las FK) deben tener el mismo nombre.

Bueno, con este post ya acabamos la parte de Diseño de Bases de Datos. En la siguiente pildorita vamos a empezar ya un poco con la sintáxis SQL, ¡así que estaros atentos!

Índice de Pildoritas

Share