Mysql Optimización en el diseño de BD

De MediaWiki
Ir a la navegación Ir a la búsqueda

Introducción


  • En este punto vamos a indicar aspectos en el diseño físico de las bases de datos, formas de realizar las consultas,estructura de tablas,... que harán que las consultas se ejecuten de forma más rápida.



Optimizando la estructura de las bases de datos



Optimizando el diseño de las tablas

  • Columnas de las tablas:
  • Intentar escoger el tipo de dato más pequeño posible. Así, por ejemplo. MEDIUMINT ocupa un 25% menos de espacio que un INT.
  • Mejor declarar columnas con la propiedad NOT NULL ya que va a hacer que los índices funcionen más rápidos al no tener que chequear si el valor es NULL en las comparaciones.
  • ROW FORMAT: Podemos consultar las características de los diferentes formatos de filas en este enlace. Lo que representa este parámetro es la forma en como Mysql guarda cada fila de datos. Lo que hace Mysql por defecto es utilizar el tipo COMPACT. Si queremos que la fila 'ocupe' menos deberemos escoger la opción COMPRESSED , 'comprimiendo' los datos por lo que aumenta el uso de la CPU. Una explicación en este enlace.
  • Indices:
  • Las columnas que conforman los índices deben ser de un tamaño lo más pequeño posible. Recordar como ya vimos en la sección de índices que los índices secundarios guardan el valor del índice primario.
  • Crea el número de índices que necesites. teniendo en cuenta que estos son adecuados para consultas pero que dejan de ser adecuados si realizamos muchas operaciones de inserción, actualización o borrado.
  • Si se realizan consultas en los que se busca por varios campos, se debe de crear un índice multicolumna mejor que varios índices parciales La primera parte del índice debería ser siempre la columna más usada. En el caso de usar siempre varias columnas es mejor que la primera columna sea la que tenga más duplicados.
  • En el caso de utilizar un índice sobre una columna de tipo cadena de un tamaño grande, es mejor hacer un índice parcial.
  • JOINS:
  • Podemos declarar columnas que se referencian mucho en tablas diferentes para aumentar el rendimiento (duplicamos la información, problema de integridad si hay modificaciones).
  • Intentar que los nombres de las columnas sean simples. Por ejemplo, en una tabla Clientes, la columna 'nombre' es adecuada, mejor que 'cliente_nombre'. Por motivos de compatibilidad con otros gestores es mejor que los nombres de las columnas tengan menos de 18 caracteres.



Optimizando los tipos de datos

  • Siempre es preferible valores numéricos para identificar las filas que valores de cadena.
  • Si la información a guardar puede ser guardada en formato numérico o cadena, es preferible que sea numérica.
  • Con columnas inferiores en tamaño a 8KB es mejor hacer uso del tipo de datos varchar que blob.
  • Si habitualmente en una tabla se disponen de columnas a las cuales raramente se va a acceder, es mejor mover dichas columnas a otra tabla y relacionarlas con una clave primaria-foránea con la tabla original.



Optimizando cuando el número de tablas abiertas es alto

  • Lo que indica:
El valor por defecto de la variable del sistema table_open_cache es de 400.
Si aumentamos el número de tablas abiertas debemos vigilar que el servicio Mysql pueda abrir ese número de ficheros.
Para ello debemos de mirar el valor de la variable del sistema open_files_limit.
Para poder establecer un valor diferente debemos de cambiar un fichero del S.O.(probado en un Ubuntu 16.04) como se indica en este enlace. Después debemos ejecutar la orden 'sudo systemctl daemon-reload' y reiniciar el servicio Mysql.


  • El valor de la variable table_open_cache será demasiado pequeño cuando la variable de estado Opened_tables es demasiado alto o este se incrementa muy rápidamente. Dicha variable informa de cuantas tablas fueron abiertas desde que se levantó el servicio Mysql. La orden SQL para obtener el valor de dicha variable es: SHOW GLOBAL STATUS LIKE 'Opened_tables' Disponemos de otra variable que indica cuantas tablas están siendo abiertas en este momento:SHOW GLOBAL STATUS LIKE 'Open_tables'
Su valor debe de ser lo más próximo posible al valor de table_open_cache.
Podéis consultar un ejemplo de optimización en este enlace. La base teórica en la que se apoya no lo tengo muy claro, ya que determina el número máximo de tablas de la base de datos y la multiplica por el número de threads concurrentes y de ahí aplica un 50%. Me parece muy exagerado pensar que cada uno de las conexiones a la base de datos acceda a todas las tablas y aplicar un 50% porque sí ???? Yo creo que está mejor el método comentado anteriormente del número máximo de conexiones simultáneas por el número de tablas máximo que pueda acceder una consulta.
Para saber si es necesario aumentar el valor de 'table_open_cache' habría que muestrear durante un intervalo de tiempo y ver si la variable 'Opened_tables' aumenta su tamaño de forma importante. Indicar que cuando se hace uso de tablas temporales, aumenta por 2 el número de tablas abiertas en 'Opened_tables' por lo que también puede ser una razón de que el número sea elevado. Para obtener información sobre las tablas temporales utilizadas podemos ejecutar la orden SQL: SHOW GLOBAL STATUS LIKE '%tmp%';





Procesamiento de consultas

  • Cuando el gestor ejecuta una consulta pasa por las siguiente fases:
  • Parseo.
  • Análisis.
  • Optimización.
Antes de hacer pasar por estas etapas, el servidor consulta la caché para no tener que repetir el proceso.


Caché de consultas

  • Nota Importante: El uso de la caché está 'deprecated' a partir de MySQL 5.7.20, y será eliminada en MySQL 8.0.
Esto ya está comentado en esta propia WIKI.


  • Mysql almacena cada consulta en la caché a no ser que se ejecute con la opción SQL_NO_CACHE.
Para que funcione la caché esta tiene que estar activa y podemos activarla o consultar su estado en la variable del sistema query_cache_type.
Sólo las consultas que se repiten muchas veces deben ser cacheadas.
Nunca serán cacheadas las consultas que forman parte de una subconsulta, las consultas en el interior de procedientos almacenados, función, trigger o evento.


  • A partir de la versión 5.7 la caché está desactivada por defecto.
Para activarla debemos modificar el valor de la variable del sistema query_cache_type.
Otra variable relacionada con la anterior es la que indica el tamaño de la caché. Esta viene especificada por el variable del sistema query_cache_size.
Recordar que lo que estamos reservando es espacio de memoria para guardar los resultados de consultas.
Por lo tanto tendríamos que analizar las sentencias SQL que se ejecutan en el servidor y ver cuales de ellas son ejecutadas más veces y cual es su tamaño medio (cuantos bytes ocupan el resultado de la ejecución).
A partir de estos datos y en función de la memoria disponible en el ordenador donde se ejecuta el Mysql podremos activar la caché e indicar el tamaño de la misma.
Para limitar las consultas que serán guardadas en la caché en base al tamaño del resultado de una consulta, disponemos de la variable del sistema query_cache_limit.


  • Para determinar si la caché está funcionando adecuadamente, podremos hacer una consulta al gestor, para que muestre los valores del las variables de estado relacionadas con la caché: show status like 'qc%';
Entre la información que obtendremos estará:
  • Qcache_free_memory: Es la memoria que queda libre para nuevas consultas en la caché.
  • Qcache_hits: Indica cuantas consultas fueron 'servidas' por la caché. Cuanto más grande su valor mejor.
  • Qcache_inserts: Consultas que han sido cacheadas desde que se inició el servidor Mysql. Debemos de tener en cuenta que si se producen modificación en los datos, la consulta vuelve a cachearse, por lo que el valor de esta variable se incrementará, no así la variable de estado Qcache_queries_in_cache que veremos a continuación.
  • Qcache_lowmem_prunes: Indica cuantas consultas fueron descartadas por la caché al no poder añadirlas por falta de espacio.
  • Qcache_queries_in_cache: Número de consultas en la caché.


  • Podéis consultar una explicación de estos parámetros en este enlace.
  • Recordar que se puede hacer que una consulta se ejecute sin que haga uso de la caché con la opción: SELECT SQL_NO_CACHE col1,col2,....
De todas formas el propio S.O. tiene su propia caché de acceso a disco y el propio Mysql hace uso de una caché de índices y páginas de disco, por lo que puede suceder que al ejecutar una consulta que haga uso de la caché, al volver a ejecutarla desactivando dicha caché, siga devolviendo los datos en un tiempo muy pequeño.
Lo mejor es mirar los parámetros anteriores para ver si está bien configurada.


  • Tener en cuenta también que si se ejecutan sentencias que modifiquen los datos, la caché deja de ser válida y es necesario volver a guardar los resultados en memoria.



Ejercicio propuesto uso caché

Sobre la base de datos employees.


  • Activa la caché en el servidor Mysql y comprueba que está activa.
  • Obtén el tamaño máximo que puede tener la consulta para que pueda ser guardada en la cache.
  • Realiza una consulta sobre la tabla departments y comprueba que se ha guardado en la caché.
Comprueba que haciendo la misma consulta se devuelve el resultado de la caché.
  • Añade una nueva fila a la tabla departments y comprueba como al ejecutar la misma consulta de SELECT se tuvo que añadir a la caché de nuevo y por tanto no se sirvió de la caché.



Solución Ejercicio propuesto uso caché

  • Activa la caché en el servidor Mysql y comprueba que está activa.
Necesitamos modificar el archivo de configuración de Mysql y añadir la línea: query_cache_type=1


  • Obtén el tamaño máximo que puede tener la consulta para que pueda ser guardada en la cache.
Ejecutar la orden SQL: select @@query_cache_limit


  • Realiza una consulta sobre la tabla departments y comprueba que se ha guardado en la caché.
1 show status like 'qc%';
2 select * 
3 from departments;
4 show status like 'qc%';
Podemos comprobar como Qcache_inserts y Qcache_queries_in_cache se han incrementado.
La variable de estado Qcache_hits sigue en 0 ya que no se ha realizado la misma consulta desde que ha guardado en la caché.


Comprueba que haciendo la misma consulta se devuelve el resultado de la caché.
Debemos de realizar la misma consulta y comprobar como Qcache_hits aumenta:
1 select * 
2 from departments;
3 show status like 'qc%';


  • Añade una nueva fila a la tabla departments y comprueba como al ejecutar la misma consulta de SELECT se tuvo que añadir a la caché de nuevo y por tanto no se sirvió de la caché.
Al realizar el insert podemos comprobar como Qcache_inserts aumenta, no así Qcache_queries_in_cache.
Al mismo tiempo, Qcache_hits no ha aumentado, por lo que se demuestra que el select no fue servidor por la caché ya que tuvo que guardarse nuevamente.



Parseo, análisis y optimización

  • Una vez que se comprueba que la consulta no está en la caché, el gestor transforma la sentencia en partes (parseo) identificando:
  • Tipo de consulta: INSERT, UPDATE, DELETE, SELECT, GRANT,...
  • Tablas que se van a utilizar.
  • Contenido de la clásula WHERE (si existe)
  • Otras cláusulas como GROUP, HAVING, ORDER BY,...
  • Una vez tiene identificada las partes, intenta establecer cual es la forma más óptima de ejecutar la consulta para recuperar las filas de la forma más rápida posible (siendo este un proceso complejo).



Optimización de consultas e índices


  • Partimos de la base de que el objetivo más importante de los índices es hacer que las consultas se realicen de la forma más rápida.
Recordar que ya vimos en la Wiki como activar el fichero de Log que registra la ejecución de consultar que tarden mucho en ejecutarse.


Por lo tanto será necesario conocer el tipo de sentencias que se ejecutarán y que recuperan información de la base de datos.- Normalmente este tipo de sentencia se refiere a sentencias SELECT con parte WHERE, pero podría aplicarse a sentencias UPDATE o DELETE con parte WHERE.


  • Podemos encontrarnos con dos casos:
  • Una base de datos con índices creados pero que la ejecución de sentencias sea muy lenta => En este caso es necesario analizar los índices que existen y averiguar la causa de que las sentencias se ejecuten de forma lenta.
  • Una base de datos sin índices creados => Es necesario crear los índices de acuerdo al tipo de sentencias SQL


  • Los índices se utilizan en las siguientes situaciones:
  • Consultar con WHERE en la que se usan columnas con índices.
  • En combinaciones de tablas cuando la relación entre ellas (clave primaria / foránea) se realiza con columnas que tengan un índice asociado.
  • Para encontrar el valor de una función de agregado sobre campos indexados (no necesitamos acceder a los datos).
  • Para ordenar o agrupar sobre columnas con índices asociados (si es múltiple, empezando por la izquierda).
  • Para casos en los que sólo queremos acceder a los datos de las columnas que tengan un índice.



Nota: Recordar que los índices ocupan espacio y que si las tablas asociadas tienen muchas operaciones de modificación de datos, los indices pueden dejar de ser efectivos, ya que habrá que reconstruirlos.
Podemos solucionarlo con dos comandos que veremos posteriormente:
  • Comando Analyze: Vuelve a recrear la estructura del índice.
  • Comando Optimze: Vuelve a crear la tabla permitiendo recuperar el espacio que ya no se utilice y haciendo la operación anterior de recrear los índices.


Creación de índices: Bases de datos con índices no creados

  • Modificar el Mysql WorkBench para que el número de filas esté limitado a 50.000 y hacer que las consultas tarden más tiempo en ejecutarse.
Mysql indices opt 2.jpg


  • El número de índices máximo recomendado sobre una tabla es de 6
  • El número de columnas máximo de un índice recomendado no debería ser superior a 5
Recordar que el número máximo de índices sobre una tabla y el número máximo de columnas sobre un índice viene determinado en el motor de la base de datos, como ya vimos.


  • Recordar que el objetivo de los índices es acelerar las consultas que tengan un tiempo de ejecución elevado y que por tanto impacten sobre el rendimiento del servidor.
Como normas generales:
  • Se debe de crear un índice sobre la columna que es clave primaria (ya lo hace automáticamente el indicar que es clave primaria):
  • Se debe crear un índice sobre las columnas que son claves foráneas. Por ejemplo, cuando actualizamos la clave primaria de una tabla, se producirá una actualización en cascada. El gestor tiene que buscar todas las filas que tengan un valor igual al valor antiguo de la clave primaria que hemos actualizado.
  • Si la tabla sólo tiene cientos de registros no vale la pena crear más índices.
  • En el resto de los casos será necesario analizar la consulta SQL para crear el mejor índice.



  • En base a lo anterior, tendremos que crear un índice:
  • En todas las columnas que sean clave primaria: Las columnas marcadas con clave primaria ya se crea automáticamente un índice por lo que no será necesario crearlo.
Recordar que el índice asociado a la clave primaria hace que la fila de datos esté ordenada físicamente con el índice, como explicamos anteriormente en la Wiki.
  • En las columnas que sean clave foránea.
Recordar que ya vimos en el punto anterior como agregar índices a una tabla.



El resto de índices será necesario crearlos en base al tipo de sentencias que ejecutaremos en las que se recuperen filas.
Veremos en el siguiente punto como haciendo uso del comando 'explain' podemos saber si nuestro índice funciona correctamente.

Nota: Indicar que las recomendaciones que viene a continuación son 'generales' pero puede darse el caso que en base a los valores de los datos guardados, sea necesario crear otro índice diferente o no crear índice alguno.


Caso 1: Parte Where con una única condición

  • La sentencia SQL es del tipo:
1 SELECT columnas
2 FROM Tabla
3 WHERE col=valor
Para este tipo de sentencias es necesario crear un índice sobre la columna a la que aplicamos la condición.
Veamos un ejemplo:
1 SELECT *
2 FROM employees.employees
3 where hire_date='1986-06-26';
Si ejecutamos esta sentencia dos veces, una sin el índice creado y la siguiente vez con un índice creado sobre la columna hire_date nos dará este resultado:
Mysql indices opt 1.jpg
Podemos comprobar como la velocidad (columna Duration) es mucho menor en el segundo caso gracias al uso del índice.


  • Podemos ver en este enlace un ejemplo en el que el uso de un índice aplicado a una columna es peor que no tenerlo (de hecho Mysql tiene un optimizador y sabe que no debe hacer uso de él). Fijarse que la sentencia count es sobre la columna 'name' que no se forma parte de la estructura del índice.



Caso 2: Parte Where con una varias condiciones de tipo AND

Nota: Partimos de un ejemplo de dos columnas, aplicándose los mismos conceptos a 3 o más columnas.
  • La sentencia SQL es del tipo:
1 SELECT columnas
2 FROM Tabla
3 WHERE col1=valor1 and col2=valor2
En este caso se debe de 'cubrir' todas las columnas de la parte Where.
Aquí podemos tener dos posibilidades:
  • índice sobre las columnas col1,col2
  • índice sobre las columnas col2,col1
Fijarse que el orden es importante, no es lo mismo ordenar por apellidos y dentro de cada apellido por el nombre, que ordenar por el nombre y por cada nombre, ordenar todos los apellidos.
¿ Cual debemos elegir ?
Como siempre vamos a dar un consejo 'general' pero que dependiendo de los datos puede que no sea la mejor opción.
Si sobre la base de datos tenemos, a parte de la consulta anterior, consultas del tipo:
1 SELECT columnas
2 FROM Tabla
3 WHERE col1=valor1
Debemos crear el índice con las columnas col1,col2.
Este índice cubre las dos consultas anteriores y otras, siempre que se busque por col1:
 1 SELECT columnas
 2 FROM Tabla
 3 WHERE col1=valor1
 4 
 5 SELECT columnas
 6 FROM Tabla
 7 WHERE col1=valor1 and col2=valor2
 8 
 9 SELECT columnas
10 FROM Tabla
11 WHERE col2=valor2 and col1=valor1
12 
13 
14 SELECT columnas
15 FROM Tabla
16 WHERE col1=valor1 and (col2=valor2 or col2=valor3)
17 
18 SELECT columnas
19 FROM Tabla
20 WHERE col1=valor1 and col2>=valor2 and col2<=valor3


Si por el contrario, tenemos consultas del tipo:
1 SELECT columnas
2 FROM Tabla
3 WHERE col2=valor2
4 
5 SELECT columnas
6 FROM Tabla
7 WHERE col1=valor1 and col2=valor2
Escogeremos el índice: col2,col1.



Nota::
  • Siempre que tengamos una consulta SQL en la que la parte where afecte a más de una columna, será mejor hacer un único índice sobre todas las columnas del where que el realizar múltiples índices sobre cada columna individual.
  • Como dije al principio, esta regla puede no ser correcta dependiendo de los datos. Si la condición col1='valor1' devuelve casi todos los registros de la tabla, será mejor hacer índice a 'col2,col1' no hacer índice sobre col1 (explicamos a continuación por qué)


  • Debemos de tener en cuenta otro factor a la hora de elegir el orden, que es el de la cardinalidad.
Este factor indica cuantos valores diferentes guarda un índice.
La idea es que cuantos más valores diferentes haya, mejor, ya que el número de filas que devolverá el índice debería ser menor, al seleccionar filas en base a algún criterio.
Para saber la cardinalidad de una columna podemos ejecutar la siguiente orden SQL: SELECT COUNT(DISTINCT col).


Nota: Una desventaja que tiene esta opción es que en la recuperación de datos se moverá más por la estructura en árbol del índice si la condición devuelve muchos valores diferentes, mientras que si primero hacemos uso de la columna que tenga más duplicados, podrá devolver más filas con los mismos valores y por tanto no será necesario moverse tanto por la estructura de índice.
Por eso en varios sitios encontraréis que es mejor utilizar en primer lugar aquella columna que tenga más duplicados.
Son dos opciones contradictorias. Como consejo intentaremos escoger aquella opción que nos devuelve el menor número posible de resultados en base a los datos que tengamos de los datos empleados en la consulta.
Si no podemos saber este dato, escogeremos primero aquellas columnas que tengan la cardinalidad más alta.



  • Veamos un ejemplo:
1 SELECT *
2 FROM employees.employees
3 where hire_date='1986-06-26' and first_name='Shuzo'
Comprobamos cual de las dos columnas tiene la cardinalidad más alta:
Mysql indices opt 3.jpg
Como vemos la columna 'hire_date' tiene más valores diferentes (cardinalidad más alta).
Por lo tanto el índice que deberíamos crear sería: hire_date,first_name
Aún no vimos el comando 'explain' pero si creamos los dos tipos de índices sobre la tabla (hire_date,first_name y first_name,hire_date) podemos comprobar cual escoge Mysql:
Mysql indices opt 4.jpg
Como vemos escoge el formado por las columnas 'hire_date,first_name'.


Pero como estoy indicando desde el principio esto son 'líneas generales' pero que puede que necesiten modificarse dependiendo de los datos.
Por ejemplo, si ejecutamos la sentencia anterior de forma separada por cada columna del where y contamos el número de filas obtenemos:
Mysql indices opt 5.jpg
Como vemos hemos acertado en el sentido que devuelve menos filas el índice 'hire_date' que 'first_name',!!!!CON ESTOS DATOS!!!!'
Por eso Mysql escoge el índice formado por 'hire_date,first_date'
Pero si usamos datos que devuelvan más filas por la condición 'hire_date' que por la condición 'first_name', ¿ qué pasará ?, ¿ qué índice deberíamos escoger ?
Lo veremos más adelante con el comando explain.


Ejercicio Propuesto

En una base de datos se ejecutan este tipo de sentencias:
1 SELECT *
2 FROM employees.employees
3 where first_name='DATO1' and last_name='DATO2'
Analiza las columnas first_name, last_name y determina el orden de las columnas que conforman el índice


Nota: Si los resultados no son los esperados, probar a ejecutar la orden SQL 'Analyze table TABLA' o desde el Mysql WorkBench, seleccionando la tabla y pulsando el botón derecho, escoger la opción 'Table Inspector':
Mysql indices opt 12.jpg



Solución Ejercicio Propuesto

  • Si hacemos un count(distinct()) de cada columna podemos comprobar como la que tiene más valores diferentes es 'last_name'.
Por tanto el índice debería ser: last_name,first_name
Recordar: Esto es así porque presuponemos que el número de filas que devolverá el indice aplicando la primera columna será menor al tener menos valores repetidos, pero dependiendo de los datos, esto puede no ser cierto.
1 SELECT *
2 FROM employees.employees
3 where last_name='Rosch' and first_name='Shuzo'
Si averiguamos cuantas filas devuelve cada condición individualmente:
Mysql indices opt 6.jpg
Podemos comprobar como first_name devuelve más filas, por lo tanto es mejor escoger el índice: last_name,first_name como hemos decidido.
En este ejemplo, si dejamos a Mysql que elija el índice (teniendo los dos) escoge (no sé la razón, seguramente por el número de filas devuelto, que es muy bajo) el índice contrario:
Mysql indices opt 7.jpg
Sin embargo nuestra elección es la correcta (Mysql se equivoca) ya que si 'forzamos' a ejecutar la consulta con cada uno de los índices da mejor tiempo el índice 'last_name,first_name):
Mysql indices opt 8.jpg
De todas formas al ser el número de filas de diferencia entre un índice y otro no se notaría en este caso prácticamente diferencia y seguramente es por eso que Mysql escoge el otro índice.



Caso 3: Parte Where con una varias condiciones de tipo OR

  • Borramos todos los índices creados sobre la tabla 'employees' menos el de clave primaria.
  • Con una condición OR no tiene efecto un índice compuesto y tendremos tres posibilidades:
1 SELECT *
2 FROM tabla
3 WHERE col1=dato1 or col2=dato2
  • Crear un índice sobre col1
  • Crear un índice sobre col2
  • Crear un índice sobre col1 y crear otro índice sobre col2


Las dos primeras opciones no tienen sentido, ya que a pesar de tener un índice, Mysql tendría que recorrer toda la tabla para comprobar la condición que no tiene índice asociado.
La tercera opción podrá ser viable ya que Mysql a partir de la versión 5, puede aplicar varios índices haciendo una operación de combinación (en el comando explain aparece 'Using ....'.
Podéis consultar todo lo referente a combinación de índices en este enlace.
Pero al igual que en el Caso 2, tendremos que tener en cuenta la cardinalidad de cada columna.
Si esta es baja, no valdrá la pena crear un índice sobre la columna.


  • Veamos un ejemplo:
1 SELECT *
2 FROM employees.employees
3 WHERE first_name='Georgi' or hire_date='1985-11-21'
Creamos un índice sobre la columna 'first_name' y otro sobre la columna 'hire_date'
Recordar que hacer un índice sobre una de las columnas no tiene sentido
Con el comando Explain nos indica que está combinando los dos índices uniendo los resultados.
Mysql indices opt 9.jpg


Al igual que en todos los casos anteriores muchas veces vamos a depender de los datos guardados en las tablas.
En foros diversos establecen que que cuando una de las columnas (o ambas) tiene varios valores posibles es mejor cambiar la consulta y utilizar OR.
Podéis mirar el ejemplo propuesto en este enlace.
La idea es que toda instrucción OR se puede descomponer en dos órdenes SELECT unidas con el comando UNION de la forma:
1 SELECT *
2 FROM employees.employees
3 where first_name='Georgi' or hire_date='1985-11-21'
Es igual a:
1 SELECT *
2 FROM employees.employees
3 WHERE first_name='Georgi'
4 UNION
5 SELECT *
6 FROM employees.employees
7 WHERE hire_date='1985-11-21'
De esta segunda forma puede aplicar el índice individual a cada columna y hacer la unión de los dos resultados.


Pero por pruebas realizadas eso debería de ser así en versiones anterior de Mysql. En la actual y con la combinación de índices, siempre obtengo resultados más rápidos con el índice combinado.
De todas formas tener en cuenta esta posibilidad ya que puede ser que dependiendo de la consulta os convenga utilizar esta opción.



Ejercicio Propuesto

Borra todos los índices de la tabla employees menos el de clave Primaria.
Indica que índice crearías para optimizar este tipo de consultas:
1 SELECT *
2 FROM employees
3 WHERE (first_name like 'A%' and gender='M') or
4       (hire_date between '1993-01-01' and  '1995-01-01')




Solución Ejercicio Propuesto

La consulta está formada por dos partes separadas por el operador OR.
Con este tipo de operador intentaremos que haga uso de un índice combinado.
El primer índice será sobre la columna 'hire_date'
El segundo índice llega hacerlo sobre la columna 'first_name'. Tendríamos que escoger primero la columna que tuviera la cardinalidad más alta (first_name) y después la segunda columna. La columna 'gender' no va a compensar utilizarla ya que los datos están repartidos prácticamente al 50% entre los valores M y F, por lo que el costo de ir a recorrer la segunda parte del índice será mayor que el de ir seleccionando las filas devueltas por la primera columna. La cosa variaría si hubiese muy pocas filas con el valor 'M'. En ese caso podría hasta convenir escoger dicha columna en primer lugar.






Creación de índices: Bases de datos con índices creados. Analizando los índices. Comando Explain

  • Explain se puede aplicar a una tabla para obtener información sobre una ella (como hace el comando DESCRIBE, o el comando SHOW COLUMNS).


  • Aplicado a la optimización, lo que vamos a conseguir haciendo uso de este comando es determinar como Mysql ejecuta la consulta y que recursos emplea (plan de ejecución).
Se puede aplicar a sentencias SQL: SELECT, UPDATE, DELETE, INSERT y REPLACE.
En versiones anteriores a 5.7 se podía utilizar la opción EXTENDED para obtener más información, pero ahora dicha opción ya está activada por defecto por lo que no es necesario ponerla.


Su sintaxis básisa es: EXPLAIN SELECT consulta
Podéis consultar en este enlace la información que suministra explain en estos casos.


Veamos el resultado sobre una consulta simple en la base de datos employees: EXPLAIN SELECT * FROM departments ORDER BY dept_no;
Mysql explain 1.jpg
Entre la información que devuelve:
  • id: Es el número identificar de cada consulta que tenga que realizar. Imaginemos un SELECT con UNION entre dos tablas. En este caso, tendría que hacer un select de la primera tabla (identificar 1), un select de la segunda (identificador 2) y unirlas.
  • select_type: El tipo de consulta SELECT que se está realizando. Tenéis en este enlace los diferentes tipos. En el ejemplo indica SIMPLE ya que es una consulta que no tiene uniones ni subconsultas.
  • table: Nombre de la tabla de donde se obtienen los registros.
  • partitions: En caso de que la tabla esté particionada, indicaría las particiones donde tendría que ir a buscar los registros. Más información en este enlace. Podéis ver las ventajas que tiene realizar particiones de los datos en este enlace.
  • type: Indica qué tipo de valores se usan en la consulta. Podéis consultar los diferentes tipos en este enlace. En nuestro ejemplo indica INDEX. Esto indica que el acceso a los datos se ha realizado utilizando un índice (no aparece nada en la columna 'Extra').
  • posible_keys: Posibles índices que se podrían utilizar para buscar los datos.
  • key: El nombre del índice que Mysql ha decidido utilizar entre todos los posibles. En nuestro ejemplo el índice asociado a la clave primaria de la tabla.
  • key_len: Indica el tamaño del índice que utilza Mysql. En nuestro ejemplo, la clave primaria tiene un tamaño de 4 caracteres y está haciendo uso de todo su tamaño.
  • ref: Son las columnas o valores que se hacen coincidir con la clave. En nuestro ejemplo no hay. Si pusiéramos en la consulta: where dept_no='d001' , en la columna ref indicaría 'const'.
  • rows: Indica el número de filas que Mysql cree que deben examinarse por parte del índice. Es una estimación. Lo ideal es que ese número se aproxime al número de filas que devuelve la consulta.
  • filtered: Indica el porcentaje de filas que después de realizar la estimación son realmente enviadas como resultado de la consulta. No se debe de hacer mucho caso como se indica en este enlace.
  • Extra: Muestra información adicional, como el uso de ciertas cláusulas o el uso de índices cuando son utilizados. En nuestro ejemplo aparece NULL por utilizar el índice primario, pero si usáramos otro índice aparecería el nombre del mismo. Podéis consultar en este enlace el significado de las entradas que aparecen en esta columna.


  • Mysql va a determinar cuando debe de hacer uso de los índices.
Nosotros podemos 'forzarlo' a utilizarlos.
Podremos poner después de la cláusula FROM tabla lo indicado en este enlace.
  • Con USE indicamos a Mysql que haga uso únicamente' del índice/s indicado/s. En caso de que el optimizador de consultas crea que es más rápido hacer un escaneo de todas las filas sin hacer uso de índices lo podrá hacer.
  • Con IGNORE indicamos que no haga uso del índice/s.
  • Con FORCE indica a Mysql que siempre haga uso del índice/s indicado/s. A diferencia de USE no podrá hacer uso del escaneo.
  • Con FOR indicamos que haga uso del índice sólo para consultas del tipo combinación (JOIN), ordenación (ORDER BY) y agrupación (GROUP BY).




Optimizando consultas con WHERE

  • Cuando usamos la cláusula WHERE aplicada a una columna indexada para buscar filas con ciertos valores:
  • El campo type indicará range si sobre esa columna hay un índice y se están seleccionando un rango de valores. Con esto se indica que sólo los registros con los valores buscados serán devueltos haciendo uso de índice.
  • El campo type indicará ALL si sobre esa columna no hay un índice. En este caso indica que es necesario recorrer toda la tabla para obtener las filas. Es ineficiente. En el campo extra aparecerá la frase: Using Where
  • El campo type indicará const si estamos buscando un valor concreto sobre la primary key o índice de tipo Unique (operador =)
  • El campo type indicará ref si estamos utilizando una expresión aritmética con los operadores =,<=,=> aplicada a una columna indexada.


Veamos un ejemplo sobre la tabla employees.
Si realizamos la siguiente consulta:
1 explain 
2 SELECT * FROM employees.employees
3 where first_name='Hein'
Obtendremos este resultado:
Mysql explain 5.jpg
Como podemos observar, type=ALL indicando que hay que recorrer todos los registros y rows, que indica el número de filas estimadas que debe examinar el índice, da un valor próximo al número total de registros.


Si ahora creamos un índice sobre dicha columna, el resultado será el siguiente:
Mysql explain 6.jpg
Como podemos observar el valor de rows ha bajado considerablemente.


Fijarse ahora si cambiamos los campos del select y hacemos que sólo se devuelva el campo que forma parte del índice (valdría en índices multicolumnas):
Mysql explain 7.jpg
Vemos que en la columna 'Extra' aparece Using Index indicando que los datos son mostrados haciendo uso del índice sin necesidad de 'ir' a la página de los registros a buscar los campos a mostrar.


  • Veamos ahora el uso de un índice multicolumna.
Crearemos un índice sobre las columnas first_name y last_name en la tabla employees (y con este orden).
Si ahora ejecutamos la orden:
1 explain 
2 SELECT * FROM employees.employees
3 where first_name='Hein'
Vemos que el índice sigue actuando de igual forma que en el caso del índice creado sobre una columna:
Mysql explain 8.jpg


Si ampliamos la cláusula where para que también busque por last_name:
1 explain 
2 SELECT * FROM employees.employees
3 where first_name='Hein' and last_name='Angot'
Vemos que el índice sigue actuando:
Mysql explain 9.jpg
Al igual que en el caso anterior, si hacemos un select de las columnas que integran el índice, el gestor no tiene que ir a buscar las columnas a las páginas de datos.
Si podemos en orden diferente las condiciones de la cláusula where, todo sigue funcionando igual:
Mysql explain 10.jpg


  • Veamos ahora una búsqueda con rango de valores, haciendo uso del índice anterior:
1 explain 
2 SELECT * FROM employees.employees
3 where first_name='Hein' and (last_name between 'Angot' and 'Baalen')
Vemos que el índice sigue actuando:
Mysql explain 11.jpg
La columna type indica range indicando que sólo las filas que están dentro del between son recuperadas. La columna ref tiene el valor null para este tipo de búsqueda y en la columna Extra se indica que se hace un test sobre cada una de las filas recuperadas por el índice para saber si cumplen la condición.


  • Veamos que pasa si en vez de una condición 'and' tenemos una 'or'.
1 explain 
2 SELECT * FROM employees.employees
3 where first_name='Hein' OR (last_name between 'Angot' and 'Baalen')
En este caso el índice deja de actuar ya que necesita realizar la búsqueda por la segunda condición que no tiene índice asociado, por lo que tiene que recorrer todas las filas, verificando las dos condiciones:
Mysql explain 12.jpg


  • Normalmente si queremos crear un índice de varias columnas es mejor que vayan primero aquellas que tengan una cardinalidad más alta.
Podéis consultar un ejemplo en este enlace.
En este mismo artículo podéis ver un ejemplo en el que una cardinalidad baja puede no ser malo, entrando en otro concepto, el de la 'selectividad'. En el ejemplo, un índice creado sobre un campo SEXO el cual sólo tiene dos valores posibles, por lo tanto, su cardinidad es muy pequeña. Sin embargo, si los datos son mayoritariamente de un sexo, por ejemplo 'F', quedarán muy pocos datos con sexo 'M' y por lo tanto si las consultas a realizar lo son buscando sexo 'M' será mejor utilizar este campo en primer lugar para que devuelva el menor número de filas.


  • Veamos ahora que pasa si tenemos varios índices y la consulta puede hacer uso de los dos.
Veamos un ejemplo en el que creamos un nuevo índice sobre la tabla 'employees' y columna 'birth_date'
Si ahora intentamos ejecutar esta consulta:
1 explain 
2 SELECT * FROM employees.employees
3 where first_name='Hein' and birth_date='1960-03-23'
Mysql podrá utilizar el índice creado sobre first_name o el índice creado sobre 'birth_date'. ¿ Cual escoge ?
Veamos el resultado:
Mysql explain 13.jpg
En versiones anteriores de Mysql este cogería el índice con la cardinalidad más alta, pero a partir de la versión 5, puede aplicar los dos índices combinando los resultados.


Mysql (a partir de la versión 5.X) debido a los ajustes del optimizador, realiza un cálculo aproximado en base a la distribución de los valores de cada índice y determina cual de los dos índices devuelve el menor número de filas.
Si cambiamos la condición anterior por esta:
1 explain 
2 SELECT * FROM employees.employees
3 where first_name='Hein' and birth_date>'1960-03-23'
La condición birth_date va a devolver un número tan grande de filas que no le compensa hacer uso del segundo índice.
Por eso sólo usa el índice asociado a 'first_name' para recuperar la información.
Mysql indices opt 10.jpg


Esto no cambia el hecho de que si tenéis que escoger entre dos o más columnas para crear un índice siempre tenéis que escoger el que tenga una cardinalidad mayor.

Optimizando consultas con ORDER BY

  • Ordenar los resultados de una consulta es un proceso costoso que se debe intentar evitar.
Para hacerlo podemos hacer uso de los índices.
Nota: Mysql siempre tendrá en cuenta la cláusula WHERE por encima de la de ORDER BY.
Por lo tanto siempre será mejor tener un índice que nos devuelva un número pequeño de filas que otro que nos sirva para 'ordenar'.


Recordar que Mysql sólo va a hacer uso de un índice por lo que si aplica uno por la cláusula WHERE no lo va a aplicar a la parte ORDER, únicamente que tengamos un índice sobre una columna que se vaya a aplicar en la parte where y después el la parte order by o bien un índice multicolumna.


  • Cuando una tabla no dispone de índice o este no sirve para aplicarse a la ordenación, cuando ejecutemos el comando explain, aparecerá en la columna Extra el texto Using filesort
Veamos un ejemplo sobre la tabla 'employees' (borrar los índices que tengáis de prácticas anteriores y dejar sólo el de la clave primaria)
Mysql explain 15.JPG


Si ahora hacemos la ordenación por la clave primaria hará uso del índice y por tanto no mostrará nada en la columna 'Extra':
Mysql explain 16.JPG


  • Si tenemos de sentencias de este tipo:
1 SELECT col1,col2,...
2 FROM Tabla
3 WHERE col1 = 'valor1'
4 ORDER BY col2,col3
Aplicando los criterios anteriores a la creación de índices, tendremos en cuenta las columnas que conforman el ORDER BY para crear un índice compuesto.
Por lo tanto el índice podría estar compuesto por: 'col1,col2,col3'.
Nota: Recordar que estas son indicaciones de tipo 'general' y van a depender de los datos.
Si por ejemplo, en el caso anterior, col3 devolviera un número muy pequeño de valores diferentes, podría no ser 'rentable' gastar el espacio en crear un índice con una nueva columna, cuando casi todos los datos vendrán con esa columna con el mismo valor.



  • Otro factor a tener en cuenta en la ordenación es el tipo de ordenación que tenga un índice, el cual puede ser ascendete o descendente.
  • Hasta la versión 8.0, la ordenación de los índices siempre es ascendente, a pesar que se pueda indicar en la definición lo contrario,
  • A partir de la versión 8.0 ya hace caso como podéis leer en este enlace
Si hacemos uso de columnas simples, el índice va a funcionar tanto en ordenación descendente como ascendente, o con varias columnas en las que la ordenación sea igual en todas ellas, pero en ordenaciones de varias columnas en las que las direcciones de ordenación son diferentes, tendrá que hacer uso de una tabla temporal para dicha ordenación, indicándolo en la columna 'Extra' como 'Using filesort'.
Podéis consultar este hilo donde se comenta esto mismo.


  • Debemos de tener en cuenta que dependiendo del número de filas devueltas por la consulta, Mysql puede decidir no hacer uso del índice creado.
Veamos un ejemplo.
Mysql explain 17B.JPG
Como vemos estamos creando un índice sobre la columna 'salary'.
Ejecutemos una consulta con ordenación para ver si hace uso del índice:
Mysql explain 18B.JPG


¿ COMO NO APLICA EL ÍNDICE ?
Mysql prefiere no hacer uso del índice.
La explicación a esto es debida a que como estamos haciendo un select SIN WHERE Mysql considera que tiene que recorrer toda la estructura del índice y por cada entrada debe ir a la estructura de datos a recoger la información. Eso lleva consigo estar 'saltando' de página de datos a página de datos por cada fila. En ese caso, prefiere no hacer uso del índice, recorrer la tabla entera de forma secuencial y hacer una ordenación en memoria de los datos obtenidos.

Veamos la diferencia de tiempo 'forzando' a que haga uso del índice:

Mysql indices opt 11.jpg
Como vemos Mysql se equivoca. Para forzar el uso del índice podemos poner la opción de FORCE INDEX:
1 SELECT *
2 FROM salaries
3 FORCE INDEX (idx_salario)
4 ORDER BY salary asc;


Veamos como esto cambia si añadimos una condición WHERE que disminuya el número de filas devueltas:
Mysql explain 21B.JPG
Como vemos hace uso del índice.
Puede darse el caso contrario, de que la tabla tenga pocas filas y Mysql decida no hacer uso del índice.
  • Otro aspecto a tener en cuenta y que está relacionado con lo anterior es la parte SELECT. Recordar que cuando creamos un índice, el dato de la columna se guarda en la estructura del índice así como la clave primaria.
Si en el ejemplo anterior, en vez de realizar un SELECT *, realizamos un SELECT salary sí o sí va a hacer uso del índice aunque la consulta no tenga parte where:
Mysql explain 22B.JPG




Optimizando consultas con GROUP BY


  • Si Mysql necesita crear tablas temporales para resolver este tipo de consultas, aparecerá en la columna 'Extra' del comando 'explain' la frase Using temporary.
Mysql explain 35.jpg


  • Normalmente vamos a necesitar que el índice 'cubra' todas las columnas indicadas en el 'ORDER BY' y en el mismo orden.
Recordar que 'normalmente' es más importante la parte WHERE (que nos limita el número de filas devueltas) que la parte ORDER BY.


  • Mysql puede aplicar el group by de dos formas diferentes:
  • Loose Index Scan: En esta forma aplica la agrupación al mismo tiempo que coge las filas que cumplen el criterio del WHERE.
  • Tight Index Scan: En esta forma, primero escoge las filas y después aplica la agrupación.



Loose Index Scan

  • Este sería la forma más óptima de aplicar el group by.
  • Se deben cumplir las siguientes condiciones:
  • La consulta se aplica sobre una única tabla.
  • Las columnas que conforman el group by deben estar definidas en el mismo orden en el índice.
  • Las funciones de agregado que se pueden utilizar son MIN() y MAX() siempre aplicadas sobre la misma columna.
  • Las columnas que conforman el índice deben estar indexadas en todo su tamaño, no valen índices parciales.
  • En el caso de utilizar una funcin de agregado diferente (AVG(DISTINCT), SUM(DISTINCT), and COUNT(DISTINCT)), se podría aplicar el índice siempre que:
  • No haya cláusula GROUP BY o DISCTINCT en la consulta (aplicado a una columna).


  • Veamos un ejemplo sobre la tabla 'employees' (creamos un índice previamente sobre la columna 'first_name'):
Mysql explain 32.jpg



Tight Index Scan

  • Es menos eficiente que el caso anterior pero por lo menos no necesita crear tablas temporales para obtener los resultados.
  • Para que este método funcione, es suficiente que exista una condición de igualdad constante para alguna de las columnas que conforman el índice y que forman parte de la cláusula group by.
La cláusula group by debe de agrupar todas las columnas del índice en el mismo orden.
  • Veamos un ejemplo sobre la tabla 'employees' (creamos un índice previamente sobre la columna 'first_name'):
Mysql explain 34.jpg




Optimizando consultas con más de una tabla

  • Cuando hacemos uso de varias tablas, tanto si es un JOIN como una SUBCONSULTA, debemos de fijarnos en la columna type del comando EXPLAIN:
  • eq_ref: Indica que la clave ajena de la tabla secundaria es única por lo que cada fila de la tabla principal se combina con una única fila de la tabla con la clave foránea.
  • ref: En este caso existe más de una fila en la tabla secundaria cuya clave foránea coincida con la clave en la tabla principal.
  • unique_subquery: indica que la subconsulta se basa solo en campos clave de una tabla de tal forma que no es necesario acceder a la tabla original a buscar datos y todo se calcula con los campos del índice. Ejemplo de consulta: value IN (SELECT primary_key FROM single_table WHERE some_expr)
  • index_subquery: Igual a la anterior pero haciendo uso de un índice no único (cuyos valores pueden repetirse). Ejemplo de consulta: value IN (SELECT key_column FROM single_table WHERE some_expr).


  • Para comprender como aplicar los índices es necesario saber cómo funciona Mysql cuando relaciona dos o más tablas.
  • Primero fija una de las tablas, la cual recorrerá de acuerdo a las cláusulas where que tenga.
  • Por cada fila de la tabla anterior buscas en la siguiente tabla aquellas filas que coincidan con la condicón de relación entre las dos tablas aplicando previamente la condición where (si tiene) de esta segunda tabla.
  • Si hubiera más tablas, se fijaría la fila de la segunda tabla y se repetiría el proceso descrito en el paso anterior.


  • Veamos un ejemplo entre las tablas 'employees' y la tabla 'dept_emp'.
Ambas están relacionadas a través de una clave primaria-foránea.
La tabla 'dept_emp' tiene dos índices, uno asociado a la tabla 'employees' y otro a la tabla 'departments'.
Mysql explain 40.JPG
Podemos observar como primero aplica el índice de la clave primaria de la tabla 'employees' para obtener casi 300000 filas y después por cada una de ellas busca las filas de la tabla dept_emp empleando el índice de dicha tabla y estimando que devolverá una fila.
Veamos que pasa si formazos a que lo haga al revés, es decir, que primero fije la tabla dept_emp y después busque los datos del empleado:
Mysql explain 41.jpg
Como vemos es mejor el plan escogido por Mysql ya que tenemos que recorrer un menor número de filas.
  • Importante: Siempre debemos de fijarnos en el número de filas estimado devuelto por cada índice, ya que tendremos que tener índices que devuelvan la menor cantidad posible de filas. Tened en cuenta que para procesar la consulta tiene recorrer el resultado de multiplicar el número de filas devueltas por cada índice.
Así, si tengo un índice que me devuelve 50 filas en la tabla principal y 500 en la secundaria, tendrá que recuperar 50x5000 mientras que si aplico un índice más restrictivo (por ejemplo, un índice que aplicado a la segunda tabla me devuelva 50) tendrá que recuperar 50x50 filas.



Ejemplo práctico Optimizando consultas con más de una tabla

  • Veamos la siguiente consulta:
1 select employees.*
2 from employees 
3 where emp_no in (select emp_no
4                  from titles
5 		 where from_date='1960-01-01');
Veamos el resultado del plan de ejecución dejando sólo los índices de claves primarias/foráneas:
Mysql explain 42.jpg
Como vemos, recorre todas las filas de la tabla titles, aplicando a cada una de ellas la condición where. Después por cada fila de titles, coge el emp_no y busca dicho empleado en la tabla employees utilizando el índice de clave primaria.
En este caso está claro que debemos crear un índice sobre la tabla 'titles' y campo 'from_date' (dependiendo del resto de consultas podría ser necesario crear un índice compuesto) pero teniendo en cuenta el orden.
Al hacerlo tendremos este resultado:
Mysql explain 43.jpg




Determinando el tamaño de los campos varchar/char en un índice

  • Como comenté anteriormente, cuando tengamos un campo de tipo cadena sobre el que queramos aplicar un índice será mejor que el tamaño de dicho campo sea lo más pequeño posible ya que las operaciones de comparar serán más rápidas y el espacio de disco necesario menor.
  • ¿ Cómo podemos determinar el tamaño de dichos campos ?
Pues consultando el índice que nos da la relación de valores diferentes con respecto al total de valores.
  • Veamos un ejemplo con la tabla 'employees', campo last_name:
Mysql explain 30.jpg
Como podemos observar da un índice de '0.0055'. Por lo tanto necesitamos escoger un número suficiente de caracteres que nos acerquen a este índice.
Ahora veamos qué indice obtenemos cogiendo sólo parte de la columna, concretamente, 3,4,5 y 6 caracteres
Mysql explain 31.jpg


Como podemos observar, si escogemos un índice con sólo 6 caracteres ya igualamos el índice original (podríamos coger el del 4 caracteres)




Modificando las consultas SQL

  • IMPORTANTE: Es importante remarcar que puede ser necesario modificar las consultas SQL para adaptarlas a las necesidades de uso de índices y mejorar el rendimiento.
Para que un índice pueda ser aplicado a una columna, las columnas deben estar 'isolated' es decir que no deben formar parte de una expresión o estar dentro de una función.


  • Ejemplos de consultas en las que no se va a aplicar el índice:
1 select *
2 from salaries
3 where salary+10000 > 60000


1 select *
2 from employees
3 where year(birth_date)=1953


Analicemos una de ella, por ejemplo, la segunda, suponiendo que sólo disponemos del índice asociado a la clave primaria.
Lógicamente el índice de la clave primaria no vale y por tanto tendrá que recorrer todas las filas.
Lo primero que se nos puede ocurrir es crear un índice sobre la columna 'birth_date', pero no funcionará, ya que la condición where utiliza la función 'year' y por tanto no hará uso del índice:
En este caso sería necesario realizar una modificación de la consulta SQL para hacer uso del índice:
Mysql explain 14F.jpg
Ahora podemos consultar los tiempos de ejecución de las dos consultas:
Mysql explain 14G.jpg


  • Otro caso puede ser el uso de LIKE. Si es posible, deberemos de no hacer uso del comodín '%'.
1 select * from employees
2 where first_name like '%om'
3 order by emp_no


  • Otro caso es el de operaciones matemáticas sobre los campos de las tablas.
Así, si tengo esta consulta:
1 select * from salaries
2 where salary/1000 > 40
La debemos convertir a:
1 select * from salaries
2 where salary > 40000


  • Nos podemos encontrar con consultas en las que Mysql aplique los índices, pero que esa no sea la forma más óptima y que sea necesario modificar la consulta SQL.
(Lógicamente esto lo podremos hacer si podemos modificar la consulta SQL, lo cual no siempre es posible).


Veamos la siguiente consulta:
1 select *
2 from employees
3 where (hire_date='1998-01-01' or salary beetween 10000 and 30000) and
4       emp_no > 10000
(Nota: Esta sentencia no es aplicable ya que la tabla employees no tiene campo salary, es sólo un ejemplo ficticio).


Como vemos tenemos una consulta SQL con cláusulas OR.
Antes de la versión 5, Mysql tenía que decidir cual de los dos índices utilizar, si el aplicado a la columna 'hire_date' o el índice aplicado a la columna 'salary'.
A partir de la versión 5, permite aplicar un índice combinado. Normalmente Mysql haría una combinación de los índices mostrando resultado en el campo 'extra' aplicando el comando 'explain', la cadena 'Using Union'.
Pero puede suceder que la consulta Mysql tenga mas condiciones where aplicadas a más campos (en el ejemplo sería la condición emp_no>10000) y que Mysql decida aplicar un índice sobre esas condiciones antes de aplicar un índice combinado y que esta forma no sea la más óptima.
Para optimizar este tipo de consultas podemos transformar la orden SQL para que se ejecuten dos sentencias separadas (cada una con su índice) y después 'una' los resultados:
1 select *
2 from employees
3 where (hire_date='1998-01-01' or salary beetween 10000 and 30000) and
4       emp_no > 10000
Podemos transformarla en:
1 select *
2 from employees
3 where hire_date='1998-01-01' and
4       emp_no > 10000
5 UNION 
6 select *
7 from employees
8 where salary beetween 10000 and 30000 and
9       emp_no > 10000
Algunas veces es necesario modificar la orden SQL para que Mysql aplique el índice combinado sin necesidad de hacer el UNION.
Podéis consultar un ejemplo real de este tipo de optimización.



  • Otros ejemplos de necesidad de realizar modificaciones son el uso de las subconsultas.
Más información sobre optimización de subconsultas en este enlace y en este otro.
Podéis consultar un ejemplo real de optimización de este tipo de consultas.


Ejemplo práctico Optimizando consultas con WHERE

  • Elimina todos los índices 'salary' menos el PRIMARY KEY
  • Determina que índice sería el mejor para resolver este tipo de consulta (se puede proponer la modificación de la consulta):
1 select * 
2 from salaries
3 where salary=74333 and
4       year(from_date)=1993
5 order by emp_no








  • Posible solución:
  • Cambiamos la función YEAR para que aplique la condición a los valores de la columna from_date.
  • Miramos cual de las dos columnas tiene mayor cardinalidad (SELECT COUNT(DISTINCT col)) y el resultado es que salary tiene más.
  • A partir de lo anterior podemos hacer un índice de múltiples columnas en el siguiente orden: salary, from_date, emp_no
1 SELECT * FROM employees.salaries
2 where from_date>='1993-01-01' and from_date<='1993-12-31' and salary=74333
3 order by emp_no


cente

Podemos comprobar como hacemos uso del índice. Indica 'Using filesort' ya que el resultado da un conjunto de filas muy pequeño (concretamente devuelve dos) y prefiere realizar la ordenación sin hacer uso del índice.
Por otro lado indicar que están creados varios índices a mayores, combinándolos de diferentes formas, para ver cual elegía Mysql (por eso aparecen muchos 'possible_keys').


Comando Analyze

  • El objetivo de dicho comando es analizar y distribuir las claves de una tabla.
Es válido para tablas MyISAM, BDB e InnoDB.
1 analyze no_write_to_binlog table employees;
Mysql analyze 1.jpg
  • Mysql va a hacer uso de la distribución de claves para determinar el orden en que se harán las consultas JOIN y determinar qué índices utilizar.
Por eso es importante que cuando haya modificaciones de datos en las tablas, se debería ejecutar este comando para actualizar las claves e índices asociados.
Nota: Recordar que en Mysql podemos hacer uso de STRAIGHT_JOIN y forzar el orden en la consulta, pero corremos el riesgo de que los datos se actualicen y más adelante ya no sea una forma óptima de ejecutar la consulta. Es mejor actualizar las claves.
  • De forma predeterminada este comando guarda las modificaciones realizadas en el fichero de log binario si éste está activado, por si es necesario replicar los cambios en los equipos esclavos (en un escenario de replicación). Si no queremos que guarde dicha información, podemos incluir no_write_to_binlog en la ejecución del comando.


  • Veamos un ejemplo:



Comando Repair

  • El objeto de este comando es el de 'reparar' o 'arreglar' tablas que tengan los índices o los datos corrompidos.
Sólo es aplicable a los motores MyISAM y ARCHIVE.
  • Ejemplo de uso:
1 repair no_write_to_binlog table employees;
  • Opciones que podemos utilizar:
  • QUICK: Indica que se va a reparar el archivo de índices.
  • EXTENDED, que crea el índice fila a fila en vez de crear el índice en un sólo paso.
  • USE_FRM: Indica que no debe hacer uso del archivo .MYI (archivo de índices) y que rehaga el archivo de índices haciendo uso del archivo de datos (.FRM).



Comando Optimize Table

  • El objetivo de este comando es el de reutilizar y desfragmentar el espacio de una tabla después de realizar muchas operaciones de borrado y modificación sobre tipos de datos VARCHAR, VARBINARY, BLOB o TEXT.
Sólo es aplicable a los motores MyISAM, InnoDB y ARCHIVE.
  • Ejemplo de uso:
1 optimize no_write_to_binlog table employees;



Comando Check Table

  • El objetivo de este comando es el de chequear el estado de las tablas e informar si es necesario su 'reparación' con el comando visto anteriormente.
Sólo es aplicable a los motores MyISAM, InnoDB y ARCHIVE.
Si el mensaje después de su ejecución es diferente a 'ok' o 'Table up to date' es conveniente realizar un 'repair'.
  • Ejemplo de uso:
1 check table employees;
Podéis consultar las diferentes opciones del comando en el enlace del comienzo de este punto.
  • FOR UPGRADE: Indica que se debe chequear las tablas en busca de incompatibilidades con la versión de Mysql donde fueron creados (cuando traemos las tablas de un Mysql anterior)
  • El resto indica el grado profundidad en la revisión de la tablas.



Comando CheckSum

  • El objetivo de este comando es el de generar un único número en base a los datos guardados en la tabla, de tal forma que cualquier cambio que se haga producirá un número diferente, por lo que nos puede servir como recurso para saber si una tabla fue cambiada sin nuestro conocimiento.
  • Ejemplo de uso:
1 checksum table employees;




Artículos interesantes



-- Ángel D. Fernández González -- (2017).