Mysql Optimización en el diseño de BD

De MediaWiki
Saltar a: navegación, buscar

Introdució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 propieadad NOT NULL ya que va a hacer que los índices funcionen más rápidos ya que no tienen 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 operaciones de inserción y actualización.
  • 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.
  • 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.
  • Siempre que se pueda es mejor utilizar la ordenación y comparación haciendo uso del operador BINARY.
  • Siempre que se comparan columnas es mejor que dichas columnas tengan la misma codificación (collation) y juego de caracteres.
  • 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.
  • 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 habiendo llegado al máximo del indicado por table_open_cache. La orden SQL para obtener el valor de dicha variable es: SHOW GLOBAL STATUS LIKE 'Opened_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

  • 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.


  • Recordar que podemos aplicar un índice sobre varias columnas.
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.
Así, si tengo un índice multicolumna sobre las columnas pais y edad de una tabla 'personal', las siguientes consultas harían uso del índice:
  • select nombre from personal where pais='España' and edad=50;
  • select nombre from personal where pais='Francia' and edad>50;
  • select nombre from personal where pais='Irlanda' and (edad=5 or edad=60);
Sin embargo estas otras no:
  • select nombre from personal where edad=50;
  • select nombre from personal where pais='España' or edad=50;


  • Si queremos saber cuando Mysql hace uso de un índice o no debemos hacer uso del comando explain.


  • IMPORTANTE: Debemos de recordar que aunque podemos tener múltiples índices creados sobre una tabla, Mysql sólo va a utilizar uno de ellos, el que tenga la cardinalidad más alta (esto es, que tengo un mayor número de entradas con valores diferentes).
Podéis consultar lo que es la cardinalidad en este enlace.
Para determinar las cardinalidades de un índice en una tabla:
  • Podemos ejecutar la orden SQL: SHOW INDEX FROM nombre_tabla;
  • Podemos ejecutar la orden SQL: SELECT COUNT(DISTINCT columna) FROM TABLA;
En ciertos casos (a partir de la versión 5) es posible combinar dos índices (índice combinado, indicado posteriormente) con cláusulas or dentro del where.


  • El número de índices máximo recomendado sobre una tabla es de 6 (información indicada en este enlace).


  • 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.
  • 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.



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. Es una estimación.
  • 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 no pueda obtener la información hará un escaneo de todas las filas sin hacer uso de índices.
  • 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 intentará por todos los medios no 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

  • Debemos de tener en cuenta que si no existe cláusula WHERE, Mysql considera que no existe razón para hacer uso del índice.
  • 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. En este caso la columna key indicará el nombre del índice utilizado.
  • 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 ref si entre otros motivos, la columna indexeda utiliza los operadores = o <=>
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 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.
Ya comentamos que Mysql sólo usará uno de ellos.
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


Como vemos escoge el asociado a la columna de 'birth_date'. ¿ Por qué ?
Por la cardinalidad, de los dos escoge el que tenga el número de entrada diferentes más alto:
Mysql explain 14.jpg
Podemos comprobar si forzamos a ejecutar la orden SQL utilizando el índice 'idx_first_last' que el tiempo de respuesta es mayor.


Siguiendo con este mismo ejemplo, ¿ cual sería el mejor índice a utilizar ? Aquel que devuelva un menor número de filas.
En el caso anterior, con el índice se devuelve 63 filas, pero el resultado final (el número de filas de la consulta) es de 1.
Para mejorarlo, podemos hacer un índice compuesto de los campos 'first_name' y 'birth_date':
Mysql explain 14B.jpg
Si ahora ejecutamos las consultas utilizando los dos índices, podemos comprobar como mejora con el compuesto (debemos mirar la segunda columna, fetch):
Mysql explain 14C.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 decirm que no debe 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 qye 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 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').


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.
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' por lo tanto está haciendo uso del índice:
Mysql explain 16.JPG



  • 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
Por lo tanto si hacemos un order by de tipo descendente, tendrá que hacer uso de una tabla temporal para dicha ordenación, indicándolo en la columna 'Extra' como 'Using filesort'.


  • 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 explain 20.JPG
Como vemos es mejor la el plan de ejecución de Mysql que el forzar el uso del índice.


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



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).