MySQL/Optimización/Optimización de la BD

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.