Ediciones ENI Editorial | líder en informática
Optimización
Hardware y sistema operativo
1. Procesador
Las arquitecturas con varios procesadores o con varios núcleos son las más comunes; cabe preguntarse si es mejor favorecer la velocidad o el número de procesadores.
Antes de la versión 5.5, MySQL adolecía de fuertes limitaciones cuando muchos threads estaban en ejecución concurrente, limitaciones que le impedían funcionar de forma correcta con los equipos que disponen de muchos núcleos. Era corriente tener que limitarse a arquitecturas con un máximo de 4 núcleos con MySQL 5.1 para evitar degradaciones de rendimiento significativas.
Estos problemas se han corregido permitiendo el uso de un número alto de núcleos con MySQL 5.6 y aún más con MySQL 5.7. Sin embargo, es importante no descuidar la velocidad de los procesadores. En efecto, no podemos paralelizar la ejecución de una petición en varios procesadores, haciendo que la velocidad del procesador sea muchas veces determinante en la rapidez de la respuesta. Como ejemplo, en un entorno de replicación para un servidor esclavo, esta limitación puede aparecer rápidamente: aunque la mayor parte de la carga procede de las peticiones replicadas, solo el thread de replicación estará activo y solo podrá ocupar un procesador. Esto nos llevará en este caso a un procesador cargado al 100 %, mientras que los demás no están prácticamente utilizados (si configuramos la replicación multithread, quizás no tengamos nunca este problema).
Recuerde que, para un servidor esclavo, tendremos todo el interés en favorecer la velocidad del procesador antes que el número de núcleos, de modo que la replicación siga siendo lo más síncrona posible, y en un servidor maestro, más núcleos implican una mayor capacidad de tratar conexiones simultáneas.
¿Por qué no buscar en los servidores esclavos a la vez la velocidad y el número de procesadores? Simplemente porque tenemos en general varios servidores esclavos, lo que permitirá distribuir las peticiones, pero todas las peticiones procedentes de la replicación deberán ejecutarse en todos los esclavos. La cuestión del coste entra, por supuesto, también en juego.
Un último punto para los usuarios de Linux: en algunas...
Optimización del esquema
1. Tipos de datos
a. Principios generales
La búsqueda del mejor tipo de datos posible es una tarea que a menudo se descuida durante la fase de diseño del esquema de la base de datos, ya que es a la vez difícil y tedioso preguntarse, para cada campo, cuáles son los valores mínimos y máximos que se han de almacenar. Y al poner la aplicación en producción, la modificación de los campos suele verse como una operación arriesgada, con riesgos de pérdidas de datos, y sin impacto significativo sobre el rendimiento. Sin embargo, una elección informada de los tipos de datos será siempre beneficiosa. Esta sección tiene por objeto establecer algunas reglas simples que conviene seguir para seleccionar de manera eficaz los tipos de datos.
En primer lugar, tenga en cuenta que, cuanto más simple y compacto sea el tipo de datos, más ligero y eficiente resultará. Así, un entero es más sencillo que una cadena de caracteres, ya que los conceptos de juegos de caracteres y conjuntos no existen para los enteros. Del mismo modo, entre los diferentes tipos de números enteros, un TINYINT (un byte por valor) es más compacto que un BIGINT (ocho bytes por valor) y por lo tanto más rápido de procesar.
En segundo lugar, debemos evitar al máximo las columnas NULL o, lo que es lo mismo, declarar todas las columnas NOT NULL, salvo en caso de expresa necesidad. Las columnas que pueden ser NULL requieren un trabajo adicional que es mejor evitar en el servidor. A menudo, el valor NULL utilizado como valor por defecto puede ser sustituido por 0 o una cadena vacía.
b. Números
Los datos numéricos se dividen en dos categorías bien diferenciadas: los enteros y los números reales.
Para los enteros, el tipo más corriente es INT (cuatro bytes por valor almacenado), pero existe también el tipo TINYINT (un byte), SMALLINT (dos bytes), MEDIUMINT (tres bytes) y BIGINT (ocho bytes). El número de bytes ocupados por valor almacenado devuelve la ventana de valores de cada uno de los tipos: un byte para representar 256 valores, N bytes para representar 256ˆN valores. La optimización consiste en elegir el tipo más pequeño posible, con un rango de valores suficiente para almacenar todos los valores posibles.
Cuando solo...
Indexación
1. Aspectos generales de los índices
a. Rol de un índice
Cuando las tablas crecen mucho, el servidor emplea cada vez más tiempo en recuperar los datos que los clientes solicitan, y esta hiperactividad se traduce, entre otras cosas, en peticiones largas de ejecutar. Para encontrar un buen rendimiento, una solución usual consiste en añadir uno o varios índices a la tabla. Un índice es una estructura de datos relacionada con una tabla y cuya función es comparable al índice en un libro: si quiere buscar una palabra en un libro, es más rápido buscar esa palabra en el índice, donde encontrará directamente el número de todas las páginas donde aparece, que no leer todo el libro de comienzo a fin.
Existen muchos tipos de índice; algunos tienen más limitaciones sobre los datos de la tabla, pero en todos los casos la finalidad es la misma: recuperar cuanto antes una referencia a los datos deseados.
La utilización de un índice para resolver una petición la decide el servidor durante la fase de optimización de la petición: antes de realizar una petición, el servidor intenta determinar cuál va a ser el medio más rápido de buscar los resultados utilizando un subprograma especializado denominado optimizador de peticiones.
b. Claves e índice
Algunos sistemas de bases de datos distinguen claramente las claves y los índices. Una clave es una limitación sobre los datos, mientras que un índice es una estructura que tiene por objeto permitir buscar rápidamente un conjunto de datos. Así, se puede definir en estos sistemas una limitación de unicidad o una limitación para obligar a todos los valores de una columna a ser superiores a 10.
Con MySQL, el concepto de limitación también existe, pero de una manera más limitada. Y, sobre todo, todas las limitaciones se implementan empleando un índice. Por esta razón, en la continuación de este libro, las palabras clave e índice se considerarán a veces equivalentes, aun cuando en algunos casos será habitual que se emplee una en lugar de la otra.
c. Columnas que pueden beneficiarse de un índice
Todas las columnas de una tabla pueden, en teoría, beneficiarse de un índice. En la práctica...
El comando EXPLAIN
1. Rol
Cuando una petición se ejecuta con lentitud, necesitará información sobre lo que hace MySQL de forma interna a fin de poder influir en su comportamiento, por ejemplo, añadiendo un índice, modificando la petición o modificando uno o varios parámetros a nivel del servidor.
El comando EXPLAIN desempeña un papel especialmente importante, ya que es gracias a este que MySQL comunica el plan de ejecución de la petición. ¿Qué es un plan de ejecución? Se trata simplemente de la estrategia adoptada por el servidor para determinar el resultado de una petición. Recuerde que SQL es un lenguaje en el que indicamos los criterios de búsqueda o la acción (por ejemplo: añadir 1 a la edad de todas las personas nacidas el 1 de marzo), pero en el que no especificamos cómo encontrar los registros correspondientes a la búsqueda.
2. Acceso a los datos
Como veremos a continuación en este capítulo, EXPLAIN nos informará más o menos directamente sobre la forma en que el servidor va a acceder a los datos para encontrar los resultados de una petición. Por consiguiente, es interesante conocer los métodos de acceso más eficaces y los que hay que intentar evitar.
a. Acceso secuencial o aleatorio
Sea cual sea el medio de almacenamiento (memoria RAM, disco duro tradicional o SSD), los datos siguen organizándose en una estructura como de árbol o lista encadenada. Cuando desee acceder a varios datos en una estructura, hay dos posibilidades: o bien los datos están situados unos al lado de otros o están diseminados a través de la estructura.
En el primer caso, tan pronto como se determina la posición del primer dato, basta con leer los siguientes datos para recuperar toda la información necesaria. Hablamos entonces de acceso secuencial.
En el segundo caso, la posición de cada dato debe ser calculada. Hablamos entonces de acceso aleatorio. Como las operaciones que se han de realizar son más complejas, un acceso aleatorio es más lento que un acceso secuencial.
Esta visión es simplista, ya que la fragmentación de una estructura puede provocar accesos aleatorios donde solo deberíamos tener accesos secuenciales. Pero en el caso de una estructura de acceso secuencial, los accesos aleatorios...
Optimización de las peticiones
En esta sección, veremos una serie de técnicas para mejorar el tiempo de ejecución de sus peticiones. Lea estas propuestas como consejos que pueden ayudarle, no como recetas absolutas que funcionan en todos los casos. La optimización sigue siendo un arte y la experiencia sigue siendo la cualidad esencial para encontrar la mejor manera de acelerar una petición lenta.
1. Aislamiento de las columnas
La encapsulación de una columna en una función prohíbe la utilización de un índice. Si es posible, asegúrese, por tanto, de reescribir su petición de manera que no necesite de una función. Este caso se presenta a menudo con las fechas.
Ejemplo de una petición que no puede usar un índice:
mysql> EXPLAIN SELECT * FROM rental WHERE TO_DAYS(CURRENT_DATE())
- TO_DAYS(rental_date) < 10\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: rental
type: ALL
possible_keys: NULL
key: NULL
...
Al tratar de aislar la columna rental_date sobre la cual existe un índice, el resultado es mejor:
mysql> EXPLAIN SELECT * FROM rental WHERE rental_date >
CURRENT_DATE() + INTERVAL 10 DAY\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: rental
type: range
possible_keys: rental_date
key: rental_date
...
Si hemos leído bien la sección sobre la caché de peticiones, sabremos que el uso de CURRENT_DATE() impedirá la puesta en caché; por tanto, es preferible calcular la fecha actual en la aplicación e inyectar la constante así obtenida en la petición.
Sería mejor especificar la lista de los campos necesarios antes que pasar por SELECT *, que impide, por ejemplo la utilización de un posible índice de cobertura.
2. Joins
MySQL solo conoce un método para realizar joins: las tablas se adjuntan...
Optimizaciones para MySQL 5.6/5.7
MySQL introdujo muchos cambios en el optimizador de peticiones. El objetivo es lograr encontrar nuevas maneras de realizar peticiones que eran muy lentas en las versiones anteriores, es decir, no se ejecutaban de forma óptima. Cabe señalar que MariaDB 5.5 implementa casi los mismos cambios, aunque el código es diferente.
Las técnicas que presentamos a continuación son seleccionadas de forma automática por el optimizador cuando aportan un beneficio. En teoría no tenemos, por lo tanto, que hacer nada para aprovecharlas. Tenga presente que solo la experiencia permite confirmar en qué casos estas mejoras son interesantes y en qué casos es mejor evitarlas.
1. Index Condition Pushdown
Ya hemos mencionado el hecho de que MySQL no puede filtrar empleando las columnas de un índice más allá de la primera diferencia. Por ejemplo, si una tabla contiene un índice de columnas (a, b) y la petición se refiere a la condición WHERE a > 5 AND b = 10, solo la primera columna del índice puede utilizarse para el filtrado.
A partir de MySQL 5.6, una optimización llamada Index Condition Pushdown permite poner en el índice las condiciones que no pueden, por norma, tenerse en cuenta; así se evita al motor de almacenamiento tener que buscar la fila correspondiente y rechazarla si no cumple los requisitos.
Veamos el funcionamiento...
Mantenimiento de las tablas
1. Actualización de las estadísticas del índice
Disponer de estadísticas de índice actualizadas es tener la garantía de que el optimizador de peticiones seleccionará el mejor plan de ejecución. Por lo tanto, le asegura que el rendimiento del servidor no se degrada de forma aleatoria solo porque algunas peticiones van a resolverse con un costoso recorrido completo de la tabla, cuando con un índice se podría haber limitado el número de filas para leer.
En general, notaremos que no es necesario forzar un nuevo cálculo de estas estadísticas porque InnoDB las vuelve a calcular cada cierto tiempo mediante la opción innodb_stats_auto_recalc, activada por defecto. Este cálculo se realiza principalmente cuando el número de registros de la tabla ha variado de forma significativa. Para calcular las estadísticas, InnoDB no tiene necesidad de desplazarse por la tabla completa; recorre solo una muestra de páginas y deduce las estadísticas que supone correctas para toda la tabla.
Esta forma de proceder tiene la ventaja de hacer la operación de actualización de las estadísticas poco costosa en tiempo y carga del servidor. Pero si algunas tablas tienen distribuciones de datos muy heterogéneas, este muestreo puede ocasionar estadísticas erróneas. El número de páginas para...