MySQL/Optimización/Texto completo

Metodología para optimizar una Base de Datos MySQL

Si nuestra Base de Datos está provista de un buen mantenimiento, obtendremos un mejor rendimiento de las consultas que realicemos (los resultados se obtendrán de forma más rápida).

Es importante hacer un diseño óptimo desde las primeras fases de la creación de la Base de Datos, de modo que sea más fácil construir aplicaciones óptimas, así como aumentar el tamaño de la Base de Datos sin perjudicar al rendimiento, para ello debemos de tener como idea fundamental el minimizar el espacio que ocupan en el almacenamiento físico, de modo que el sistema reduzca el flujo de entrada salida de las tablas.

Los pasos que debemos seguir para mejorar el rendimiento de nuestra Base de Datos y obtener resultados óptimos son los siguientes:

• Concretar al máximo nuestro diseño.
• Debe existir coherencia con los tipos de campos en sus tablas, es recomendable utilizar los mismos tipos de campos para el mismo tipo de información en distintas tablas. Si necesitara cruzar tablas con campos del mismo tipo ganará en rapidez.
• Utilizar los tipos de datos menores posibles siempre que se ajusten a los requisitos de nuestras tablas, por ejemplo usar un VARCHAR en lugar de un CHAR, ya que el primero deja libre el espacio de los caracteres que no utilicemos.
• Siempre que sea posible usaremos campos NOT NULL, ya que facilita el uso de los índices y evita tener que realizar consultas sobre si un valor es nulo.
• Utilizar un tipo de dato lo más pequeño posible para el índice correspondiente a la clave primaria para acelerar las consultas.
• Para el caso de índices texto es mejor crear el índice sobre los primeros caracteres del campo en lugar de sobre todo el mismo.
• Crear índices adecuados a la necesidad que se quiera satisfacer.

Asimismo la optimización de la Base de Datos puede ser de tres tipos:

Optimización semántica

Como hemos indicado más arriba, es primordial dedicar el tiempo necesario al diseño de nuestra Base de datos. Indicar las tablas, campos y sus relaciones, en función de las necesidades que tengamos, puede facilitarnos el mantenimiento y garantizarnos un rendimiento adecuado. Para conseguir un buen diseño de las tablas que integrarán nuestra base de datos, se utilizará el Modelo Relacional, donde se extraerán los elementos, propiedades y relaciones entre los mismos, que se traducirán en nuestra BD en tablas, campos, índices y claves relacionadas.

Del mismo modo debemos de tener en cuenta que tipo de dato vamos a utilizar en la Base de Datos, pues como referimos en pasos a seguir para mejorar el rendimiento, utilizar datos menores, que ocupen menor espacio, utilizar campos NOT NULL, índices con datos pequeños y adecuados a los requisitos del caso, redundará en mejor rendimiento del sistema, es decir, mayor velocidad de respuesta al realizar consultas o actualizar datos.

Para optimizar semánticamente la Base de Datos debemos de tener en cuenta que tipo de operaciones de búsqueda se hacen, puesto que para las operaciones de búsqueda sobre varios campos es mejor crear un índice conjunto que uno por cada campo, siendo en estos casos mejor que el índice más usado para las búsquedas sea el primer campo de la izquierda, y si en todas las consultas aparecen varios campos, es mejor que el primero en el índice sea el que tenga más duplicados. Lo vemos mejor con un ejemplo:

Ejemplo índice conjunto libro

Índice	Subíndice	Título
1	1	Introducción
1	2	Historia
1	3	Progreso
2	1	Desarrollo
2	2	Más desarrollo
2	3	Ejemplo

Es muy conveniente que los campos iguales que intervengan en combinaciones de varias tablas se nombren de la misma forma, ya que si no es así las comparaciones serán mucho más lentas.

Para tipos de datos no binarios podemos utilizar la opción BINARY con el fin de acelerar las consultas que impliquen comparaciones, dado que en este caso las comparaciones se realizan byte a byte en lugar de caracteres. Por ejemplo en lugar de indicar mediante texto los días de la semana que un programa X se emite, creamos varios atributos binarios, de forma que 1 sea un "si" y 0 "No", de esta forma resultará mucho más rápido realizar búsquedas y consultas.

Otro aspecto a tener en cuenta en la optimización semántica de la Base de Datos es cuales son nuestras prioridades, ya que si por ejemplo queremos priorizar la velocidad de la Base de Datos para utilizar herramientas de minería de datos, podríamos añadir redundancia controlada y agregar tablas con datos estadísticos, sin embargo si preferimos que ocupe menos espacio en el disco, podemos reducir la redundancia usando identificadores numéricos en las tablas, evitando repetir datos y facilitando la combinación de las tablas.

Optimización sintáctica

Debemos de tener en cuenta varios aspectos fundamentales para optimizar las consultas de nuestra Base de Datos:

• Como se ejecutan. Cada fila de la primera tabla de una consulta se procesa comparándola con cada fila de las siguientes tablas en una subconsulta o siguiente tabla en una combinación.

• Que índices hay definidos. Si se ha creado más de un índice sobre un mismo campo el SGBD deberá elegir el más óptimo, que en general lo hará basándose en su número de registros.

• Como se almacenan los índices. Los índices son archivos ordenados por la columna o columnas que contienen los registros de la columna idexada junto con la dirección física del registro con los datos de la tabla correspondiente.

• Más índices, no es sinónimo de mejor rendimiento. Demasiados índices pueden hacer que nuestro sistema vaya mucho más lento, puesto que este debe de buscar cual es el índice más adecuado a cada consulta y mantener sincronizados los datos. Tenemos que buscar un término medio que nos proporcione una correcta indexación del contenido de la Base de Datos y un buen rendimiento.

Si la consulta que vamos a realizar afecta únicamente a campos indexados, si se realizara sobre la clave primaria de una tabla, no es necesario acceder a los datos de la tabla, sino que podemos utilizar directamente el archivo de índices para obtener el resultado.

Los índices son especialmente importantes cuando realizamos operaciones de consulta, sobretodo cuando:

• Realizamos consultas con clausulas WHERE que contienen columnas indexadas, dado que hacen un filtrado previo usando únicamente el archivo de índices.
• Queremos descartar filas en consultas, puesto que el SGBD elegirá el índice más restrictivo, el que menos registros tenga, si existe más de un índice sobre la misma columna.
• En combinaciones de tablas cuando existe un índice sobre los campos comunes.
• Para encontrar el valor de una función de agregado sobre campos indexados sin tener que acceder a la tabla.

Cuando se realiza una consulta SELECT, hay que evitar en lo posible el uso del comodín '*', e indicar sólo los campos imprescindibles que se necesitan. Eso reducirá el tamaño de la consulta.

Evite en lo posible el uso de LIKE. Las comparaciones entre campos de texto (BLOB, TEXT…) ralentizan las consultas. Si fuera necesario, cree índices fulltext para los campos de texto sobre los que vaya a efectuar consultas.

Evite también el uso de GROUP BY, ORDER BY o HAVING.

Pruebe sus consultas con anterioridad, mediante el comando EXPLAIN. Le mostrará un listado informativo sobre cómo se realiza la consulta.

Sintaxis: Explain select * from tabla

 mysql> explain SELECT * FROM a left join b using (i) WHERE a.i < 2;
 +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
 | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
 +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
 |  1 | SIMPLE      | a     | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where |
 |  1 | SIMPLE      | b     | ALL  | NULL          | NULL | NULL    | NULL |    3 |             |
 +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
 2 rows in set (0.01 sec)

Para añadir registros a sus tablas, es más eficiente realizar una inserción múltiple que varias inserciones por separado. Sintaxis: INSERT INTO table (campo1, campo2) VALUES (1, ‘valor1’), (2, ‘valor2’) Encole la inserción de datos para evitar esperas innecesarias, mediante el uso de la sentencia INSERT DELAYED. La inserción se encola (agrupándose en bloques para ejecutarse de forma más eficiente) a la espera de que la tabla afectada no esté siendo utilizada por ningún otro proceso. Si tiene dudas sobre el rendimiento de alguna expresión, utilice la función: Le mostrará el tiempo que tarda en ejecutarse. BENCHMARK (contador, expresión)

mysql> SELECT BENCHMARK(100000000, CONCAT('a','b'));
+---------------------------------------+
| BENCHMARK(100000000, CONCAT('a','b')) |
+---------------------------------------+
|                                     0 |
+---------------------------------------+
1 row in set (21.30 sec)

Optimización física

La optimización física se aplica sobretodo a los índices de las tablas, a la estructura y tipo de índice que es, esto afecta al peso de nuestra Base de Datos, es decir al espacio que ocupa en el disco, pero sobretodo a la velocidad con la que responderá el sistema, pues crearemos el índice sobre aquellos campos que más utilicemos en las consultas, de forma que el Sistema sepa donde buscar e ir al registro directamente sin necesidad de comparar registro por registro hasta llegar a lo que buscamos. Supongamos que somos una gran empresa y que queremos consultar los datos de producción de un trabajador, para ello emplearemos:

 SELECT * FROM Producción WHERE codigo_empleado=5468

En esta consulta MySQL tiene que ir leyendo uno a uno los registros y seleccionar los que coincidan con el código del empleado elegido. Este proceso puede llevarle mucho tiempo al SGBD y resultar por lo tanto muy poco eficiente. No obstante si añadiésemos un índice sobre el campo que utilizamos para la consulta, en este ejemplo sobre el código del empleado, podremos localizar con mayor rapidez lo que queremos consultar. Para ello utilizaremos el siguiente comando:

 ALTER TABLE Producción ADD INDEX (codigo_empleado)

De esta forma creamos una lista ordenada con toda la producción realizada por todos los empleados , creando de este modo un nueva tabla donde se muestran los códigos de los empleados y su posición dentro de la tabla. Este método sacrifica almacenamiento físico para obtener mayor velocidad en el procesado de las consultas.

Como hemos indicado más arriba, debemos de tener claras las prioridades de la Base de Datos, ya que dependiendo de ellas podemos elegir entre varios tipos de índices los cuales los podemos clasificar según su uso:

• Índices parciales. Optimizan el espacio, es decir, ocupan menor espacio en el almacenamiento físico, por contra, tienen un menor rendimiento en cuanto a velocidad. Se pueden crear con la siguiente sentencia:

 ALTER TABLE Actividad ADD INDEX (objetivos(60))

• Índices multicolumna. Se definen sobre dos o más columnas de la tabla y se usan sobretodo cuando las consultas que se suelen hacer sobre esas columnas emplean mucho la cláusula WHERE. Se crean de la siguiente forma:

 ALTER TABLE Producción ADD INDEX (codigo_empleado, codigo_producto)

Según la estructura de los índices:

• Índices BTree o árboles B. Están formados pro un conjunto de nodos cada uno de los cuales contiene valores de índice ordenados que apuntan a registros de disco. Son índices rápidos de recorrer por el SGBD. En ellos los nodos internos deben de tener un número variable de nodos hijo dentro de un rango predefinido. Esto hace que cuando insertamos o borramos un dato de la estructura, los nodos internos se junten o partan para mantener el número de nodos dentro del rango predefinido, por lo que no necesitarán rebalanceo. Este tipo de índice es recomendable para consultas basadas en rangos de datos, sobretodo para aquellas que utilizan cláusulas como BETWEEN.

• Índices RTree o árboles R. Se utilizan para datos de tipo espacial o de n-dimensiones, como las coordenadas de un objeto. En el cada nodo contiene un número variable de entradas no superior a un máximo y cada una de las entradas contiene un apuntador al nodo hijo y el denominado MBR (Minimum Bounding Rectangle) o superficie, volumen mínimo que alberga todos los puntos existentes en los nodos hijos. MySQL idexa las diferentes formas que pueden ser representadas y usa el rectángulo mínimo representable calculándolo para que la forma quede contenida completamente.

• Índice Hash. Se basan en una función que hace corresponder valores de clave con valores numéricos, haciendo una asociación de cada clave con un número que permite localizar el registro correspondiente. El número de valores debe de limitarse al máximo tamaño de almacenamiento para que el índice sea viable. Aunque es un índice muy rápido, es un sistema muy poco predecible, funciona de forma muy lenta para consultas basadas en rangos y puede colisionar y dar fallos cuando existe un mismo hash para varias claves.

También podemos clasificar los tipos de índices según estén formados:

• UNIQUE. Están formados por campos cuyos valores no pueden repetirse en una tabla.

• PRIMARY. Son índices creados sobre la clave primaria de la tabla.

• full-text. Están formados por varios campos de texto y se utilizan para la búsqueda de palabras dentro de un campo o para búsqueda de cadenas de texto.

• SPATIAL. Se usan para datos de tipo espacial, como lineas (LINE) o curvas (CURVE).

Aparte de los tipos de índices que podemos usar en MySQL es conveniente conocer los diferente motores de almacenamiento que utiliza este SGBD. Los más importantes y sin entrar en muchos detalles son MyiSAM e InnoDB, entre los cuales la principal diferencia es que el segundo es un motor que permite la seguridad transaccional, es decir, permite el empleo de COMMIT, ROLLBACK y la capacidad de recuperar la información tras una fallo.

Si surgen problemas de lentitud en ciertas consultas o si un índice nos da problemas en una tabla debemos de ver como están los datos indexados, que valores tienen o como es el índice y a partir de ahí realizar las modificaciones pertinentes en base a todo lo anterior que hemos explicado..

También puede ocurrir que necesitemos realizar un reciclado de nuestras tablas si observamos que han aumentado nuestros registros considerablemente, para ello debemos hacer un análisis y comprobar cuales de estos registros han quedado anticuado y pueden eliminarse o archivarse. Cuando hayamos realizado alguna modificación debemos ejecutar la sentencia OPTIMIZE TABLE que reparará y ordenará la tabla. Reducir el tamaño de las tablas mejorará el rendimiento.

OPTIMIZE TABLE MyTable1

Comandos para la gestión de índices

Creación de índices

 CREATE [UNIQUE FULLTEXT SPATIAL] INDEX index name

[index_type]
ON table_name (index_col_name, ... )
[index_type]
index col name :
col name [ (length) ] [ASC | DESC]
index type:

USING (BTREE 1 HASH)

Dentro de este comando se utilizan los parámetros que describiremos a continuación:

• Tipo de índice: UNIQUE FULLTEXT o SPATIAL.

• index_name: Nombre del índice.

• index_type: Si es un árbol B, árbol R o un Hash.

• index_col_name: Nombre de la columna sobre la que se crea el índice, tamaño y orden en que se almacena.

Mostrar los índices

 SHOW (INDEX | INDEXES | KEYS )
IN nombre_tabla
[ {FROM 1 IN} db_name ]

Podemos usar indistintamente INDEX, INDEXES o KEYS para ver los índices de una tabla especificada por IN.

La salida de esta sentencia nos devolverá los siguientes campos:

• Table: nombre de la tabla que contiene el índice.

• Non_unique: si no es único, esto nos interesa porque nos dice si el índice se ha establecido sobre una columna en la que el valor es UNIQUE o no.

• Key_name: nombre del índice.

• Seq_in_index: si es parte de un índice multicolumna y cual es su orden dentro del índice.

• Column_name: el nombre del campo o columna usado para crear el índice.

• Collation: como está ordenado el índice. Null indica que está sin ordenar.

• Cardinality: estimación del número de valores distintos en el índice.

• Sub_part: número de caracteres indexados si la columna no está totalmente idexada. Null significa que el índice es sobre todos los caracteres.

• Packed: como está empaquetada la columna.

• Null: si admite valores nulos.

• Index_type: estructura que usa para almacenar el índice.

Eliminar índice

DROP INDEX index_name ON tabla

Con este comando borramos el elemento completo de la base de datos.

Normalización

La Teoría de Normalización se basa en desordenar o reordenar las relaciones existentes en otras más sencillas y cuya principal propiedad sea la ausencia de fallos en la inserción, borrado o modificación de datos; este proceso se realiza mediante el proceso de transformar datos complejos de nuestra Base de Datos a un conjunto de estructuras de datos más pequeñas, que además de ser mas simples y estables, son más fáciles de mantener.

La Teoría de la Normalización se basa en las dependencias funcionales y en las formas normales.

Objetivos de la Normalización

• Organizar los datos en grupos lógicos de tal manera que cada grupo describa una pequeña parte del todo.
• Minimizar la cantidad de datos duplicados almacenados en una base de datos.
• Mejorar la organización de los datos de tal manera que, cuando se necesite introducir modificaciones, el cambio solo deba aplicarse en un lugar.
• Construir una base de datos a la que se pueda acceder de forma rápida y donde sea posible manipular los datos con la máxima eficiencia y sin comprometer su integridad.

Dependencia funcional

La Dependencia funcional se puede denominar como la conexión existente entre uno o más atributos. Por ejemplo si se conoce le valor de DNI tiene una conexión con Apellido o Nombre. Las dependencias funcionales del sistema se escribe utilizando una flecha, de la siguiente manera:

FechaDeNacimiento->Edad

De la normalización (lógica) a la implementación (física o real) puede ser sugerible tener estas dependencias funcionales para lograr la eficiencia en las tablas.

• Dependencia funcional completa o plena: Sobre un descriptor compuesto X (X1, X2) podremos afirmar que y posee dependencia tiene dependencia funcional completa o plena de X si sólo depende funcionalmente de X pero no depende de ningún subgrupo de éste. Se representaría mediante el grafo X->y
• Dependencia funcional transitiva: Si tenemos una correspondencia M (X, y, z) en la que existen dependencias funcionales entre los elementos del interior del paréntesis, de manera que, X tiene una dependencia funcional con Y e y con Z pero Y no tiene dependencia funcional con X. Por tanto decimos que Z tiene una dependencia transitiva respecto de X a través de Y.

Formas Normales

Denominaremos como Formas Normales a una serie de reglas que sirven para ayudar a los diseñadores de bases de datos a desarrollar un esquema que minimice los problemas de lógica. Dichas reglas son consecutivas, es decir, cada regla está basada en la que la antecede.

Existen seis de niveles normalización: Primera Forma Normal (1FN), Segunda Forma Normal (2FN), Tercera Forma Normal (3FN), Forma Normal de Boyce-Codd (FNCB), Cuarta Forma Normal (4FN) y Quinta Forma Normal (5FN).

Si se cumple la primera regla, se dice que la base de datos está en la "primera forma normal". Si se cumplen las tres primeras reglas, la base de datos se considera que está en la "tercera forma normal". Aunque son posibles otros niveles de normalización (ya que existe hasta una "quinta forma normal"), la tercera forma normal se considera el máximo nivel para la mayor parte de las aplicaciones.

Primera Forma Normal (1FN)

La regla de la Primera Forma Normal establece que las columnas repetidas deben eliminarse y colocarse en tablas separadas.

Poner la base datos en la Primera Forma Normal resuelve el problema de los encabezados de columnas múltiples. Muy a menudo, los diseñadores de bases de datos inexpertos harán algo similar a la tabla no normalizada. Una y otra vez, crearán columnas que representen los mismos datos.

La normalización ayuda a clarificar la base de datos y a organizarla en partes más pequeñas y más fáciles de entender. En lugar de tener que entender una tabla gigantesca y monolítica que tiene muchos diferentes aspectos, sólo tenemos que entender los objetos pequeños y más tangibles, así como las relaciones que guardan con otros objetos también pequeños.

Segunda Forma Normal (2FN)

La regla de la Segunda Forma Normal establece que todas las dependencias parciales se deben eliminar y separar dentro de sus propias tablas. Una dependencia parcial es un término que describe a aquellos datos que no dependen de la clave primaria de la tabla para identificarlos.

Una vez alcanzado el nivel de la Segunda Forma Normal, se controlan la mayoría de los problemas de lógica. Podemos insertar un registro sin un exceso de datos en la mayoría de las tablas.

Tercera Forma Normal (3FN)

En esta forma todas las columnas que no son clave son funcionalmente dependientes por completo de la clave primaria y no hay dependencias transitivas (existen columnas que no son clave que dependen de otras columnas que tampoco son clave).

Cuando las tablas están en la Tercera Forma Normal se previenen errores de lógica cuando se insertan o borran registros. Cada columna en una tabla está identificada de manera única por la clave primaria (dependencia exclusiva de la clave primaria), y no deben haber datos repetidos. Esto provee un esquema limpio y elegante, que es fácil de trabajar y expandir.

Forma Normal de Boyce-Codd (FNCB)

La Forma Normal de Boyce-Codd requiere que no existan dependencias funcionales de los atributos que no sean un conjunto de la clave candidata. La Segunda Forma Normal y la Tercera Forma Normal eliminan las dependencias parciales y transitivas de la clave primaria respectivamente pero podrían existir respecto a otras claves candidatas. Solo hay que comprobar si hay 2 ó más claves candidatas. Todos los campos dependen de cualquiera de los otros campos. La solución consistiría en partir la tabla agrupando las claves candidatas, y dejar el resto de los campos en la tabla principal.

Cuarta Forma Normal (4FN)

Esta forma tiene como objetivo la eliminación de multivalores en las claves candidatas, es decir, no existen dependencias multivaluadas.

Quinta Forma Normal (5FN)

Para poder llegar hasta esta forma, en primer lugar debe estar en 4FN y además no deben existir restricciones impuestas por el creador de la Base de Datos. Una restricción de este tipo puede ser por ejemplo que una tabla se divida en subtablas. Esta forma normal tiene poca aplicación práctica porque dificulta el diseño ya que genera un número enorme de tablas.