Collation, diferencias entre utf8_general_ci y utf8_unicode_ci

Para los que estamos acostumbrados a trabajar en Linux es normal usar el formato de codificación UTF-8, y en mi opinión personal es lo que se debería de usar para todo. En el caso de MySQL se puede definir en una base de datos, tabla y columna el formato de codificación como CHARSET y un sub formato llamado COLLATE. La intención del COLLATION es poder ayudar en la Presición vs Rendimiento con los carácteres especiales.

Para conocer todos los formatos disponibles de codificación y el sub formato por defecto de MySQL, ejecute el siguiente comando:

SHOW CHARACTER SET;

Como podemos ver en la lista, el COLLATION por defecto de UTF8 es el utf8_general_ci, existe otro llamado utf8_unicode_ci, y entre los dos hay grandes diferencias y al conocerlas entenderemos porque una está por defecto:

Precisión para ordenar los datos:

  • utf8_unicode_ci: Se basa en el estándar Unicode para ordenar, y ordena con precisión en una amplia gama de idiomas.
  • utf8_general_ci: Se acerca mucho para ordenar correctamente con Unicode en muchos idiomas comunes, pero tiene una serie de imprecisiones al ordenar en algunos idiomas, por lo que no es recomendable para la correcta ordenación en todos los idiomas.

Rendimiento:

  • utf8_general_ci: Es más rápido en las comparaciones y la ordenación, está puntualmente diseñado para el rendimiento, ignora los detalles según el idioma.
  • utf8_unicode_ci: Utiliza un algoritmo mucho más complejo para comparar, tiene como objetivo la clasificación correcta de acuerdo en una amplia gama de idiomas. Esto hace que sea más lento para ordenar y comparar un gran número de campos.

Limitaciones:

  • No se puede realizar comparación directa entre valores de diferente COLLATION, puede resolverlo usando el operador BINARY, pero usar esta alternativa baja mucho el rendimiento.

Quedo claro que el formato por defecto utf8_general_ci es el más rápido y el menos impreciso. La definición de estos formatos depende de nuestras necesidades muy particulares.

La siguiente consulta permite listar todas las tablas que no tienen el collation por defecto:

SELECT table_schema, table_name, engine, table_collation
FROM INFORMATION_SCHEMA.TABLES
WHERE table_type = 'BASE TABLE'
AND table_schema NOT IN ('mysql', 'performance_schema')
AND (engine <> 'InnoDB' OR table_collation <> 'utf8_general_ci');

Si queremos profundisar un poco más sobre el tema, les dejo los siguientes enlases oficiales de MySQL que explican todo estoy muy bien, y hasta tiene unos ejemplos:

Bloqueos en MySQL

El bloqueo es la esencia de cualquier manejador de bases de datos relacional (RDBMS), para el caso de MySQL es importante destacar que el engine InnoDB lo gestiona de forma óptima, hasta la fecha no hay otro que lo haga mejor.

No podemos confundir bloqueos con niveles de aislamiento, ya que el bloqueo se hace a nivel de registros usando una instrucción específica, mientras el nivel de aislamiento es el comportamiento por defecto para cualquier otra instrucción normal que manipule los registros.

Los bloqueos existen para poder garantizar el ACID, su correcta implementación permite que halla concurrencia, de lo contrario se puede presentar la contención y es fatal.

Los bloqueos los podemos dividir en dos grandes grupos; está el bloqueo pesimista y el bloqueo optimista, vamos a entender cada uno:

  • Pesimista: Es aquel que se apodera del recurso (row/tabla) y no lo libera hasta que se haya modificado. Generalmente se hace con instrucciones propias del RDBMS. Para el caso de MySQL tenemos dos instrucciones; el SELECT … FOR UPDATE y el SELECT … LOCK IN SHARE MODE y no hablar de LOCK TABLE. Éste tipo de bloqueo produce Deadlock sin importar la forma como lo controlemos, al final estamos bloqueando un recurso que usa una conexión especifica, tiene un timeout, y otros procesos que esperan por el, esto puede generar una contención, problemas de atomicidad, y muchas más situaciones lamentables.
  • Optimista: Es la mejor forma de bloquear un recurso fomentando la concurrencia, no existe instrucción SQL que añada ésta funcionalidad, más bien usamos las instrucciones DML existentes, y se basa en la lógica del MVCC. Consiste en añadir una columna para versionar el registro y evitar que el último estado sea modificado. Por ejemplo: cuando una sesión quiere modificar un registro, ésta lee y hace UPDATE con la última versión conocida de dicho registro, si hay otra transacción que se adelanta para hacer el mismo UPDATE, la versión cambia, y el UPDATE será obsoleto.

Para la mayoría de las aplicaciones WEB, se debe utilizar el bloqueo optimista, como podemos ver, no es más que una implementación de una columna que versiona el registro y la combinación simple de un SELECT y un UPDATE, de esta forma evitamos los Deadlock, la contención, y muchos otros problemas. Los ORM, como por ejemplo Hibernate o ActiveRecord tienen ésta funcionalidad implementada, nos quitan una capa más de detalle y no será necesario modificar el modelo y las consultas.

¿Por qué no usar CloudFormation para Bases de Datos?

Dedique varias semanas en investigar como implementar CloudFormation de Amazon para montar un entorno de pruebas de Percona XtraDB Cluster con cuatro nodos, dos HAProxy, un servidor con Nagios, y una VIP, adicionalmente usando Puppet, la idea que me atrajo de usar CloudFormation fue la practicidad de definir toda una infraestructura de maquinas virtuales usando un JSON como template, el tener un formulario para definir variables, y luego tener la ventaja de poder crear y destruir cualquier cantidad de veces el cluster de forma automatizada.

Al final me di cuenta que no es seguro usar CloudFormation por varios motivos:

  • CloudFormation se asegura de mantener toda la infraestructura al 100% como está definida en el JSON. Si no hay algo que no encaja, crea una nueva maquina y destruye la anterior, al tener una Base de Datos con datos de producción los podemos perder muy fácilmente. Esto me paso al cambiar la versión de la AMI, note como se creaban nuevas maquinas, y luego cuando estaban listas, fueron remplazadas por las viejas y se perdieron todos los datos.
  • Se tendría que parametrizar mucho para reducir las probabilidades de destruir una maquina de forma accidentada. Esto le añade más complejidad en el flujo de trabajo y deja de ser intuitivo para cualquiera.
  • Contemplar todos los casos de administración de cada uno de los nodos de un cluster es complejo, por ejemplo, debemos contemplar la ampliación del disco sin perder los datos, cambiar el tipo de instancia, y cambiar las políticas de seguridad. Quien sabe que más puede pasar.

Poder superar todas las desventajas mencionadas requiere muchas horas de investigación y pruebas hasta lograr dominar todo, puede que nos demos cuenta que faltan más reglas por parte del CloudFormation, pienso que esto no fue echo para Bases de Datos, sino para servidores de API y WEB, donde los grandes volúmenes de datos no son un problema.

También pienso que usar CloudFormation en un caso de Bases de Datos está bastante bien para hacer una demo, o crear entornos para un sandbox por ejemplo, pero para producción lo veo muy peligroso.

Mi recomendación es irnos por lo clásico, crear tus propios scrips que hacen llamadas a la API de Amazon, así creas toda la infraestructura, defines tú el flujo de trabajo, y llevas el control, si yo habría echo así desde un principio, habría ahorrado mucho tiempo. De todo esto se obtiene una lección aprendida, y aquí la comparto.

Seguridad en MySQL con mysql_config_editor

En la versión 5.6.6 se incluyo una nueva herramienta llamada mysql_config_editor, la cual permite almacenar las credenciales de autenticación de una forma segura y otros datos básicos en un archivo llamado ‘.mylogin.cnf’ en el directorio home del usuario, luego estas credenciales pueden ser recuperadas para conectarse al servidor de MySQL deseado.

A simple vista no parece, pero es muy importante que considere que el archivo ‘.mylogin.cnf’ no está cifrado, y no es completamente seguro, pero añade una capa más de seguridad ocultando los datos sensibles por la línea de comandos. Por ejemplo, ocultamos la contraseña en el; historial, alias, logs, scripts, etc… donde puede ser visible de muchas formas.

En el siguiente ejemplo se muestra la forma tradicional de conectarse a una base de datos usando todos los parámetros básicos; host, usuario y clave:

$ mysql -h production.db.empresa.org -u root -pmypass

Usando la nueva herramienta nos conectamos de la siguiente forma:

$ mysql --login-path=production

Asumiendo que ya hemos creado la configuración ‘production’ usando la herramienta, podemos ver que estamos especificando con el parámetro ‘–login-path’ para que use la configuración ‘production’ y así conectarse al servidor.

Para poder agregar una configuración, debemos ejecutar el siguiente comando:

mysql_config_editor set --login-path=production --host=production.db.empresa.org --user=root -p

La contraseña no se puede pasar por línea de comando, debemos escribirla para mantenerla un poco más segura. Si queremos conocer todas las configuraciones disponibles, solo debemos ejecutar el siguiente comando:

mysql_config_editor print --all

Si queremos aprender más sobre esta herramienta, recomiendo visitar su página web.