MySQL / Optimizacion de consultas

Sean bienvenidos a mi nuevo post, hoy trataremos sobre como optimizar las consultas a nuestras bases de datos hablaremos sobre la indexacion, pros y contras, algunos consejos a la hora de crear la tabla con respecto a los tipos de datos y tamaño de columnas y un par de tips mas, empecemos con indexacion.

Primero hablemos sobre la creacion y eliminacion de los mismos y algunas caracteristicas, para crear un indice se puede declarar en el CREATE TABLE para una tabla nueva, para una tabla existente se puede utilizar CREATE INDEX o ALTER TABLE, disponemos de tres tipos de indices INDEX, UNIQUE y PRIMARY KEY para el caso de los ultimos dos atributos estos deben ser unicos e irrepetibles, especialmente el PRIMARY KEY, estos son ideales para llevar un indice unico de cada linea donde puede ser utilizada como identificacion de un producto o un empleado y esto nos evitara tener informacion repetida, UNIQUE tambien trabaja de forma parecido pero permite estar en otras columnas (aunque no es habitual) a diferencia de PRIMARY KEY que estara solo en una columna y estas no pueden ser del tipo NULL, con CREATE INDEX se pueden agregar indices del INDEX y UNIQUE pero no PRIMARY KEY, repasemos un poco las tablas creadas anteriormente en este post:

CREATE TABLE alumnos
(
id int(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
nombre varchar(20) NOT NULL,
apellido varchar(20) NOT NULL,
sexo ENUM(‘F’,’M’) NOT NULL,
edad int(2) NOT NULL,
ciudad varchar(40) NULL,
pcia varchar(4) NULL
);

Cuando creamos las tablas de nuestra base tutorial en este post en el query de creacion de la tabla alumnos definimos como PRIMARY KEY a id, este era el codigo de identificacion de cada alumno, como se ve al ser PRIMARY KEY le asignamos el no ser NULL (NOT NULL) y para facilitarnos nuestras tareas le agregamos un AUTO_INCREMENT donde cada alumno tendra un id unico, la siguiente tabla creada fue examenes:

CREATE TABLE examenes
(
id int(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
fecha DATE NOT NULL,
tipo varchar(1) NOT NULL
);

En este caso hicimos lo mismo, le dimos un id unico a cada examen donde identificabamos el tipo de examen y la fecha, y por ultimo creamos una tabla llamada notas donde nosotros guardabamos la nota, el id del examen y el id del alumno para en caso de necesitar hacer una busqueda donde se utilizaran los id (indices) de las otras tablas, si nosotros utilizamos el siguiente query:

select
examenes.fecha, concat(alumnos.nombre, ", ", alumnos.apellido) as Alumnos, 
notas.nota 
from alumnos, examenes, notas 
where notas.alumno_id=alumnos.id and notas.examen_id=examenes.id 
order by examenes.id asc, notas.nota desc;

Obtendriamos un resultado como el siguiente:

+------------+---------------------+------+
| fecha      | Alumnos             | nota |
+------------+---------------------+------+
| 2017-04-15 | Valeria, Suarez     |    7 |
| 2017-04-15 | Marta, Gargaglione  |    6 |
| 2017-04-15 | Martin, Miranda     |    6 |
| 2017-04-15 | Juan, Perez         |    5 |
| 2017-04-15 | Roberto, Lopez      |    4 |
| 2017-04-15 | Griselda, Contreras |    3 |
| 2017-04-22 | Martin, Miranda     |    7 |
| 2017-04-22 | Valeria, Suarez     |    7 |
| 2017-04-22 | Marta, Gargaglione  |    7 |
| 2017-04-22 | Juan, Perez         |    6 |
| 2017-04-22 | Griselda, Contreras |    6 |
| 2017-04-22 | Roberto, Lopez      |    4 |
| 2017-04-29 | Juan, Perez         |    9 |
| 2017-04-29 | Valeria, Suarez     |    7 |
| 2017-04-29 | Martin, Miranda     |    6 |
| 2017-04-29 | Marta, Gargaglione  |    5 |
| 2017-04-29 | Roberto, Lopez      |    2 |
| 2017-04-29 | Griselda, Contreras |    1 |
| 2017-05-22 | Griselda, Contreras |   10 |
| 2017-05-22 | Valeria, Suarez     |   10 |
| 2017-05-22 | Roberto, Lopez      |    9 |
| 2017-05-22 | Marta, Gargaglione  |    8 |
| 2017-05-22 | Juan, Perez         |    7 |
| 2017-05-22 | Martin, Miranda     |    5 |
| 2017-05-29 | Valeria, Suarez     |    8 |
| 2017-05-29 | Marta, Gargaglione  |    7 |
| 2017-05-29 | Roberto, Lopez      |    7 |
| 2017-05-29 | Juan, Perez         |    7 |
| 2017-05-29 | Griselda, Contreras |    6 |
| 2017-05-29 | Martin, Miranda     |    4 |
| 2017-06-05 | Martin, Miranda     |   10 |
| 2017-06-05 | Marta, Gargaglione  |    8 |
| 2017-06-05 | Valeria, Suarez     |    7 |
| 2017-06-05 | Roberto, Lopez      |    6 |
| 2017-06-05 | Griselda, Contreras |    5 |
| 2017-06-05 | Juan, Perez         |    5 |
+------------+---------------------+------+

Como pueden ver en el query se utiizaron mayoritariamente los PRIMARY KEY de cada una de las tablas para poder hacer una busqueda mas rapida (en vez de utilizar nombre y apellido o fecha del examen) donde MySQL al tener definido los mismos como indices los mantiene en una tabla aparte (invisible para nosotros o los clientes) donde primero buscara ahi y una vez coincidido procedera a traer el resto de la informacion solicitada en el query, obviamente este es solo un ejemplo de como se utiliza y como crear a traves de CREATE TABLE, otra sintaxis para crear una PRIMARY KEY puede ser esta:

CREATE TABLE examenes
(
id int(20) NOT NULL AUTO_INCREMENT,
fecha DATE NOT NULL,
tipo varchar(1) NOT NULL,
PRIMARY KEY(id)
);

Donde nosotros definimos todas las columnas y por ultimo designamos la PRIMARY KEY, a parte de todos los argumentos utilizados en este ejemplo se puede agregar UNIQUE pero para mi es redundante porque es «unico» de por si, una linea para agregar un indice a traves de CREATE INDEX seria asi:

CREATE UNIQUE INDEX indice ON alumnos (id,nombre,apellido)
CREATE INDEX indice ON alumnos (id,nombre,apellido)

Donde indice va a ser el nombre identificatorio del mismo, alumnos va a ser la tabla donde lo vamos a crear y por ultimo entre parentesis vamos a tener las columnas a indexar. La sintaxis de ALTER TABLE seria algo parecida a esta:

ALTER TABLE alumnos ADD INDEX indice (id,nombre,apellido)
ALTER TABLE alumnos ADD UNIQUE indice (id,nombre,apellido)
ALTER TABLE alumnos ADD PRIMARY KEY indice (id)

Donde primero va la tabla (alumnos) luego el nombre del indice (indice) y por ultimo las columnas a indexar entre parentesis, salvo PRIMARY KEY la cual debe ser unica y lleva una sola (en este caso id). Para eliminar un indice de una tabla se puede utilizar los comandos DROP INDEX o ALTER TABLE, la sintaxis seria asi:

DROP INDEX nombre_de_indice ON nombre_de_tabla;
ALTER TABLE nombre_de_tabla DROP INDEX nombre_de_indice;
ALTER TABLE nombre_de_tabla DROP PRIMARY KEY;

En el caso de DROP INDEX, primero indicamos el nombre del indice y luego el de la tabla para el caso de ALTER TABLE podemos eliminarlo como INDEX o como PRIMARY KEY.

Un beneficio de manejar de esta forma las bases de datos es poder ubicar una informacion sin necesidad de utilizar de varios queries como vimos en el ejemplo de la busqueda de las notas de los examenes, tambien se nos permite un mejor orden con la clausula ORDER BY tambien mostrado en el ejemplo, cual es la desventaja de este metodo, ocupa mas espacio en el disco porque genera una tabla donde guarda todas las indexaciones, lentifica el ingreso de datos en la tabla porque no solamente ingresa los datos sino tambien debe hacer una modificacion en el indice, esto tambien aplica al borrar informacion y borra columnas porque estas tambien afectan a los indices, tanto como si la columna pertenecia al indice como al reordenar el mismo por borrar informacion, para un mejor aprovechamiento de los mismos, se recomienda indices unicos, no indexe la informacion a buscar sino aquella que permita la busqueda, los inidices tambien deben ser cortos o similares y por ultimo no sobrecargue de indices la tabla en general con uno solo es suficiente, otro metodo utilizado es el optimizador, este es un proceso interno del MySQL el cual se encarga de evaluar el mejor metodo de un query, para un mejor funcionamiento recuerden estos simples tips:

• Al momento de comparar columnas utilicen siempre del mismo tipo.
• Las columnas indexadas deben permanecer solas en las comparaciones
• No utilizar comodines (%) al comienzo de una comparacion like
• Utilice EXPLAIN para verificar la operacion del optimizador

Estos son algunos de los tips mas basicos para optimizar, entre ellos nombramos a EXPLAIN, este comando va adelante del SELECT donde nos devolvera una muestra de los indices, cantidad de lineas y otros datos para evaluar el query ejecutado, veamos un par de ejemplos:

Si bien no se ve muy bien en la imagen, esto seria un ejemplo de como devolveria el EXPLAIN todos los datos de un query, esto es util para evaluar los tiempos, todos los tipos de interacciones realizados por el mismo y evaluar si no se esta haciendo una busqueda redundante.

Tambien en determinadas circunstancias no debemos usar el optimizador, por ejemplo para borrar una tabla, cuando nosotros utilizamos el comando DELETE sin ningun condicional (WHERE) este vuelve a crear los archivos e indices en base a la informacion de descripcion en el archivo de informacion pero para algunos tipos de programa y/o muestra de trabajo realizado necesita saber la cantidad de lineas afectadas, para eso utilizamos el siguiente comando:

DELETE FROM nombre_de_tabla WHERE 1  > 0

Este comando borrara linea por linea, y como se pueden imaginar sera un proceso mucho mas lento pero para algunos casos con un mejor resultado de que la tarea fue realizada, tambien se puede evitar el optimizador para recuperar datos de la tabla de forma aleatoria porque en determinadas circunstancias necesitamos evitar el uso de ORDER BY RAND(), por ejemplo con versiones viejas de MySQL, entonces debemos hacer el RAND() sobre una columna en el momento de seleccionar las columnas a mostrar.
Lo siguiente tambien debe tenerlo en cuenta al momento de crear las tablas:

• Utilizar columnas de longitud fija y no variable
• Evitar el uso de columnas innecesariamente largas
• Tener columnas NOT NULL
• Considerar el uso de columnas ENUM
• Empaquetar algunos datos en tipo BLOB o TEXT
• Evitar recuperar innecesariamente datos tipo BLOB o TEXT en un query
• Utilizar los datos tipo BLOB o TEXT en otra tabla

Como vimos en este post, otra forma de optimizar la carga es a traves de los comandos de carga de archivos internos y externos, LOAD DATA y mysqlimport, porque nos permitiran carga de informacion de forma masiva sin necesidad de utilizar el comando INSERT pero obviamente esto es para cargas muy grandes de informacion (o recuperacion de un back up), hasta aqui algunos consejos para los usuarios sobre como optimizar las consultas y otros procesos para verificar la mejor opcion a la hora de ejecutar un query, para los administradores tambien hay una parte de tarea para hacer, por ejemplo: key_buffer_size este parametro indica cual es el tamaño indicado para utilizar los bloques de indices si se dispone de una buena cantidad de memoria puede ser incrementado para mejorar la cantidad de indices y por ende el tiempo de consulta y modificacion, pasemos a algunos tips de hardware a tener en cuenta:

• Instalar una buena cantidad de memoria
• Con una buena cantidad de RAM, elimine la mayor cantidad de archivos de intercambio del disco.
• Utilice discos rigidos con una mejor velocidad
• Trate de instalar MySQL con librerias estaticas en lugar de compartidas

Para ir terminando, siempre traten de efectuar algun mantenimiento sobre el equipo ya sea desde reiniciarlo cada cierto tiempo, desfragmentarlo, y hacer chequeos sobre los discos porque tanto la informacion como el servidor deben mantener una buena salud para una mejor carga y busqueda de los datos, para un uso de tipo personal uno no se vera tan afectado por todo esto pero en empresas donde se manipula mucha informacion esto puede acarrear un gran problema, espero les haya sido de utilidad en mi caso personal hay un par de tips a implementar para mi propio server, sigueme en tumblr, Twitter o Facebook para recibir una notificacion cada vez que subo un nuevo post en este blog, nos vemos en el proximo post.

Tengo un Patreon donde podes acceder de manera exclusiva a material para este blog antes de ser publicado, sigue los pasos del link para saber como.