Category Archives: Database

MySql tunning

Uno de los primeras cosas que hay que saber antes de empezar a optimizar consultas en MySql es conocer la existencia de la cláusula EXPLAIN.

Con EXPLAIN podemos obtener el plan de ejecución de una consulta dada, como se muestra en el siguiente ejemplo:

EXPLAIN SELECT * FROM People

Resultado:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE People ALL NULL NULL NULL NULL 979

En el ejemplo anterior el plan de ejecución es un simple full scan (type = ALL) de toda la tabla People.

Vamos a ver otro ejemplo:

EXPLAIN SELECT * FROM People WHERE gender = 'Male'

Resultado:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE People ALL NULL NULL NULL NULL 979 Using where

En este caso vemos que la consulta devuelve al motor de base de datos sigue devolviendo todos los registros en un full scan.  En este caso podemos optimizar la consulta creando un índice para gender (Idx_gender). Y de esta forma el resultado de EXPLAIN será:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE People ref Idx_gender Idx_gender 22 const 489 Using where

Ahora se puede ver como ya no se requiere un full scan (type != ALL) y únicamente la filas con valores en el índice serán las seleccionadas.

Hasta ahora se puede intuir que cuando el tipo es ALL y no queremos un full scan (todas las filas), siempre se puede optimizar. Lo más sencillo es con la creación de índices que agilicen las búsquedas más frecuentes.

A continuación teneís el detalle de todos los campos que devuelve EXPLAIN:

Id: El identificador de la SELECT. Es un número secuencial.

Select_type: Tipo de la SELECT.

  • SIMPLE: SELECT simple, sin UNION o subconsultas.
  • PRIMARY: Se refiere a la consulta “más externa”; a la principal.
  • UNION: Resto de consulta/s (segunda, tercera, etc.) en una UNION.
  • DEPENDENT UNION: Resto de consulta/s (segunda, tercera, etc.) en una UNION que depende una una consulta “externa”; PRIMARY.
  • UNION RESULT: Resultado de una UNION.
  • SUBQUERY: Primera consulta de una subconsulta.
  • DEPENDENT SUBQUERY: Primera consulta de una subconsulta que depende una consulta “externa”.
  • DERIVED: Tabla derivada en una SELECT; subquery en la cláusula FROM.
  • UNCACHEABLE SUBQUERY: Subconsulta que no puede ser almacenada en caché, o re-evaluada.
  • UNCACHEABLE UNION: Resto de consulta/s (segunda, tercera, etc.) en una UNION que no puede ser almacenada en caché, o re-evaluada.

Table: tabla a la que se refiere la fila del resultado.

Type: el tipo de JOIN. Los tipos de muestran a continuación ordenados desde el mejor al peor.

  • system: la tabla únicamente tiene una fila y es una tabla del sistema. Es un caso especial del tipo const.
  • const: en la tabla coincide una única fila que el optimizador tomo como constante. También se utiliza cuando se comparan todas las partes de una clave primaria o un índice único (UNIQUE) con un valor constante.
  • Eq_ref: una fila de una tabla es leída por cada combinación de filas en una segunda tabla. Se utiliza cuando se usan todas las partes de una clave primaria o un índice único (UNIQUE).
  • ref: todas las filas que coincidan con con los valores de un índice serán leídos por cada combinación de filas de las tablas anteriores. En JOIN se utiliza cuando no se puede seleccionar una única fila en base al valor de la clave. Puede ser usada, por el optimizador, en columnas indexadas que se comparan con =, <=>.
  • fulltext: la JOIN se realiza con un índice FULLTEXT.
  • ref or null: el tipo de JOIN es como ref pero hay una búsqueda extra para campos nulos. Este tipo se suele utilizar para resolver subconsultas.
  • index_merge: se utiliza la optimización index merge.
  • unique_subquery: sustituye a ref en algunas subconsultas (del tipo SELECT clave_primaria) que utilizan IN.
  • index_subquery: sustituye a ref en algunas subconsultas (del tipo SELECT valor_columna) que utilizan IN y que no son únicas.
  • range: cuando se devuelve filas dentro de un rango.
  • index: igual que un full scan pero cuando se escanea el índice.
  • ALL: full scan. Normalmente se debe evitar.

Possible_keys: Muestra que índices puede seleccionar MySql para devolver las filas.

Key: Indica que índice va a utilizar MySql para devolver las filas.

Key_len: tamaño del índice que se va a utilizar.

Ref: columna/s o constante/s que se van a comparar con el índice.

Rows: indica el número de filas que se prevee que MySql examine. En tablas InnoDB es un valor aproximado.

Extra: información adicional sobre como MySql resolverá la consulta.

Para más información sobre el resultado de EXPLAIN por favor mirar en la página web de MySql.

Espero que os halla sido de utilidad este breve artículo de cómo utilizar EXPLAIN para optimizar vuestras consultas a bases de datos.

¡Un saludo a tod@s!

Cómo utilizar JOIN en sentencias UPDATE o DELETE.

Una práctica muy recomendable antes de lanzar una sentencia UPDATE o DELETE sobre un subconjunto de tuplas, es probar a realizar la proyección, afinando las condiciones de restricción para el subconjunto (mediante una sentencia WHERE). Cuando nos encontramos ante una condiciones sencillas, reutilizar la misma clausula WHERE es trivial. Pero qué sucede cuando queremos utilizar funciones de composición (JOIN) para restringir el conjunto de datos.

Ejemplo para UPDATE, donde se puede ver como se han utilizado varios JOINs en el bloque de referencia a la table (table_reference en el manual de MySql):

UPDATE table1 T1 INNER JOIN table2 T2 ON T1.id = T2.id INNER JOIN table3 T3 ON T3.other_id = T2.other_id INNER JOIN table4 T4 ON T4.category = T3.category SET T1.readed = 0 WHERE T1.type = 'X' AND T1.category  = 'A';

Ejemplo para DELETE. En este caso después del DELETE hay que especificar la tabla o tablas (multi-delete):

DELETE T2 FROM table2 T2 ON T1.id = T2.id INNER JOIN table3 T3 ON T3.other_id = T2.other_id INNER JOIN table4 T4 ON T4.category = T3.category WHERE T1.type = 'X' AND T1.category  = 'A';

Espero que estos ejemplos os sirvan de ayuda.

NOTA: Todos los ejemplos se han ejecutado en un servidor MySql version 5.1.

Actualización

Ejemplo para MS SQL 2005:

UPDATE table1 SET T1.readed = 0 FROM table1 T1 INNER JOIN table2 T2 ON T1.id = T2.id INNER JOIN table3 T3 ON T3.other_id = T2.other_id INNER JOIN table4 T4 ON T4.category = T3.category  WHERE T1.type = 'X' AND T1.category  = 'A';

Funciones de agregación para valores no numéricos.

Las funciones de agregación se utilizan habitualmente, en SQL, durante la agrupación de resultados para mostrar sumatorios, estadísticas, etc. Pero puede ser que necesitemos obtener una concatenación de strings a partir de una agrupación. En MySql la solución viene de la mano de la función de agregación GROUP_CONCAT. La función acepta como parámetro una sintaxis basado en SQL para seleccionar el campo que se quiere concatenar, el separador a utilizar e incluso si hay que ordenar el resultado o mostrar valores no repetidos.

Ejemplo:

SELECT idPregunta, nombre, `email`, fecha, pregunta, GROUP_CONCAT(
    DISTINCT respuesta ORDER BY idRespuesta SEPARATOR ', '
)
FROM Respuestas
WHERE bloqueRespuestas = 'BqcjWf8en'
GROUP BY idPregunta /*, nombre, `email`, fecha, pregunta*/;

En el ejemplo se puede ver una consulta para recuperar las preguntas a un cuestionario y las posibles respuestas concatenadas en un string. Lo más curioso es como, a diferencia de la agrupación normal (con enteros), en este caso no hace falta agrupar por todos los campos proyectados en la consulta. Únicamente por el campo que se desea agrupar.

Alta disponibilidad (HA) con MySql

Uno de los problemas más comunes al desarrollar un sistema es el diseño y la elección de la infraestructura que deberá soportar la gestión y acceso a los datos. Como ocurre en muchos otros campos, la infraestructura de datos se diseña acorde a unas expectativas menores que las finales y en consecuencia los equipos de desarrollo deben trabajar en varios frentes: optimización en el “uso” de los recursos de datos (optimización de sentencias SQL, implementación de pools de conexiones, etc.) y escalado de los sistemas de soporte a las bases de datos. Hoy comentaré una de las posibles soluciones que abarca principalmente la optimización y de paso aumentar la escalabilidad de la infraestructura. Un este caso práctico la infraestructura de soporte a datos está bajo MySql con un única instancia sobre un servidor (infradb01.mydomain.org). Debido al éxito de la aplicación, han aumentado considerablemente las inserciones de datos así como las consultas de datos sobre la instancia de MySql. Para resolver el problema se recurrirá a la aplicación de una configuración maestro-esclavo (master/slave | publisher/subscriber) en la infraestructura de soporte a datos. Para empezar es necesario dar de alta una nueva instancia MySql (infradb02.mydomain.org) que trabajará como esclavo, o suscriptor, de la instancia principal. Para evitar problemas de rendimiento es mejor separa ambas instancias en máquinas diferentes. El disponer de dos servidores en configuración maestro-esclavo, significa que el servidor maestro compartirá, publicará, el registro de transacciones con el esclavo; por lo que cualquier modificación el el maestro se verá reflejada en el esclavo casi de forma instantánea. Trabajando en esta configuración es beneficioso si el código de la aplicación realiza las inserciones, modificaciones y eliminaciones en el servidor maestro y TODAS las consultas sobre el servidor esclavo. El resultado es la reducción del tiempo de bloqueos entre lecturas/escrituras y el aumento del rendimiento del sistema. Como añadido también se benefician las copias de seguridad (backups), que siempre se realizaran sobre el servidor esclavo y como guinda se dispone en el sistema de un servidor completamente actualizado y en funcionamiento por si el servidor maestro no está disponible.

Una vez contada la “vida y milagros” de la alta disponibilidad con MySql en un entorno maestro/esclavo, es hora de pasar a la práctica y configurar dicho entorno. Primero de todo hay que modificar el fichero de configuración (my.cng) de la instancia que hará el papel de maestro, en este caso: infradb01.mydomain.org:

Todos los cambios en el fichero de configuración hay que hacerlos bajo la directiva:

[mysqld]

Es necesario habilitar el log binario para poder realizar la replicación:

48
49
50
# Replication Master Server (default)
# binary logging is required for replication
log-bin         = /var/log/mysql/mysql-bin.log

Si únicamente hay que hacer replicación de un esquema, hay que añadir:

51
binlog-do-db   = myschema

El servidor maestro SIEMPRE tiene en id = 1 puede tener un id entero mayor o igual que 1 y SIEMPRE diferente al servidor esclavo:

53
54
55
56
# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id       = 1

Una vez están hechos los cambios en el fichero de configuración y se ha reiniciado el servidor, para que acepte los cambios, hay que crear un usuario con permisos de replicación (desde la línea de comandos de MySql, o desde el MySql Query Browser o el Administrator):

1
2
3
grant replication slave on *.*
to replicUser@'*.mydomain.org'
identified by 'passwd';

Y como último paso hay que volcar los datos del servidor maestro en el esclavo:

mysqldump -u root --all-databases --single-transaction --master-data=1 > mydatadump.sql

En el servidor esclavo lo primero a realizar es el volcado de los datos del servidor maestro, de esta forma ambos servidores contendrán los mismos datos:

mysql < mydatadump.sql

Al servidor esclavo hay que decirle quien es el servidor maestro e indicarle con que usuario se realizara la replicación (un usuario con permisos para ello, claro!):

1
2
3
change master to master_host = 'infradb01.mydomain.org',
master_user ='replicUser',
master_password = 'passwd';

Y finalmente hay que indicarle al servidor esclavo que empiece a replicar:

1
start slave;

Para verificar el status del servidor simplemente hay que invocar el comando:

1
show slave statusG;

La última fila indicará el tiempo de respuesta entre ambos servidores, que normalmente debería ser 0 o un valor próximo a cero y por otro lado si el valor es nulo (NULL) querrá decir que la replicación no está funcionando (comprobar Last_errno).

En este punto únicamente queda modificar el código de las aplicaciones para que admitan dos grupos de conexiones; uno para modificar tablas (inserciones, modificaciones y eliminaciones) y otro para consultar tablas, pero este ya será otro tema!!!

Oracle y Sun Microsystems la pareja de moda

Podemos leer en casi cualquier sitio como al final Oracle ha comprado, finalmente,  Sun Microsystems, dando un giro inesperado a la historia que empezó con IBM al acecho de Sun. Según se puede leer en la pagina web oficial de Sun el valor de la compra asciende a unos 7.400 millones de dólares (precio por acción : 9,50 $).

Ahora queda esperar como afectara este nuevo escenario a la tecnología Java y sobretodo a los RDBMS de Oracle y Sun. Recordemos que no hace mucho Sun Microsystems compro a MySql, por lo que ahora Oracle y MySql son hermanastros.

Set or reset AUTO_INCREMENT in a table on MySql

If you want to set or reset the AUTO_INCREMENT value to a determinated value in MySql, use this command

ALTER TABLE TABLE_NAME AUTO_INCREMENT = X

where X is the new value or 0 to reset.

SQL case-sensitive comparison over MySql

If someone is finding a way to do a case sensitive comparison in SQL (over MySql), below can find the recipe:

This one produces a True result:

1
SELECT 'A' = 'a';

The solution is doing the comparison with a = or LIKE and the BINARY clause:

2
SELECT 'A' LIKE BINARY 'a';

or

3
SELECT 'A' = BINARY 'a';

This produces a False result (good for case sensitive comparisons)

And be careful because this:

4
SELECT 'A' LIKE 'a';

produces a True result.