Performance - script
From Ibbddunq
Contents |
Indices
Un índice (o KEY, o INDEX) es un grupo de datos que MySQL asocia con una o varias columnas de la tabla. En este grupo de datos aparece la relación entre el contenido y el número de fila donde está ubicado.
Los índices -como los índices de los libros- sirven para agilizar las consultas a las tablas, evitando que mysql tenga que revisar todos los datos disponibles para devolver el resultado.
Podemos crear el índice a la vez que creamos la tabla, usando la palabra INDEX seguida del nombre del índice a crear y columnas a indexar (que pueden ser varias): INDEX nombre_indice (columna_indexada, columna_indexada2...)
La sintaxis es ligeramente distinta segun la clase de índice:
PRIMARY KEY (nombre_columna_1 [,nombre_columna2...]) UNIQUE INDEX nombre_indice (columna_indexada1 [,columna_indexada2 ...]) INDEX nombre_index (columna_indexada1 [,columna_indexada2...])
Podemos también añadirlos a una tabla después de creada:
ALTER TABLE nombre_tabla ADD INDEX nombre_indice (columna_indexada);
Los índices permiten mayor rápidez en la ejecución de las consultas a la base de datos tipo SELECT ... WHERE
La regla básica es crear índices sobre aquellas columnas que se usen en una cláusula WHERE. Pero existen varias reglas donde el uso de un índice podría afectar directamente a la performance de una consulta (Group BY, Order BY)
Otra regla básica es que son mejores candidatas a indexar aquellas columnas que presentan muchos valores distintos, mientras que no son buenas candidatas las que tienen muchos valores idénticos, como por ejemplo sexo (masculino y femenino) porque cada consulta implicará siempre recorrer practicamente la mitad del indice.
Diferentes tipos de indices
En algunas bases de datos existen diferencias entre KEY e INDEX ( no así en MySQL donde son sinónimos)
- Un índice que sí es especial es el llamado PRIMARY KEY. Se trata de un índice diseñado para consultas especialmente rápidas. Todos sus campos deben ser UNICOS y no admite NULL
- Un indice UNIQUE es aquel que no permite almacenar dos valores iguales.
- Los indices FULL TEXT permiten realizar búsquedas de palabras sobre tablas MYSAM. Puedes crear indices FULLTEXT sobre columnas tipo CHAR, VARCHAR o TEXT. Una vez creado puedes hacer búsquedas del tipo:
SELECT * FROM nombre_tabla WHERE MATCH(nombre_indice_fulltext) AGAINST('palabra_a_buscar');
Los índices ordinarios no tienen restricciones en cuanto a la existencia de valores idénticos o nulos. Una posibilidad interesante, si pensamos crear un índice sobre columnas CHAR y VARCHAR es la de limitar el campo a indexar. Por ejemplo, cada entrada en la columna puede ser de hasta 40 caracteres y nosotros indexar unicamente los primeros 10 de cada una. Para crear estos índices basta con indicar entre paréntesis el numero de caracteres a indexar despues del nombre de la columna:
ALTER TABLE libros ADD INDEX idx_autor(nombre(10), apellidos(10));
Tips de indices
Si necesitamos un select del tipo SELECT ... WHERE columna_1 = X AND columna_2 = Y y ya tenemos un INDEX con la columna_1, podemos crear un segundo indice con la columna 2, o mejor todavía, crear un único indice combinado con las columnas 1 y 2. Estos son los índices multicolumna, o compuestos.
No obstante si tienes índices multicolumna y los utilizas en las clausulas WHERE, incluir siempre de izquierda a derecha las columnas indexadas; o el indice NO se usará:
Pautas Basicas de optimización
Pautas básicas a tener en cuenta para una estrategia de optimizacion de rendimiento.
- Esquema de diseño.
- Optimización del query.
- Indexación.
- Locking.
- Server Tuning.
Importancia: Los niveles más bajos de la lista son muy importantes pues impactan en forma directa a la performance, no es lo mismo tratar de solucionar o mejorar rendimiento sobre algo no muy correctamante diseñado, es decir, si se invierte tiempo y recursos en el esquema de diseño, la base de datos será más fácil de optimizar para los demás niveles.
Orden: Comienzar desde el esquema de diseño y hacia el tunning del server. Este es el orden en que los elementos de la base de datos son más fáciles de optimizar.
Esquema de Diseño
La primera y más importante tarea de performance es optimizar el esquema de diseño. Y optimizar el esquema - diseño de base de datos tiene un impacto directo sobre el resto de los niveles de optimización. En esta etapa engloba los conceptos típicos de normalización, desnormalizacion y generalización o método de análisis de relaciones, columnas, etc etc.
Optimización de Query
El segundo paso en la optimización de la estrategia de performance es optimizar los queries. Esto involucra el rediseño de queries. Luego de que optimices el esquema de base de datos se debe revisar y rediseñar los queries.
Indexación
El paso siguiente es la optimización de índices. La existencia o ausencia de los indices pueden afectar la performance de la base de datos ya sea mejorando o empeorando la misma. Es por ello que hay que revisar los índices solo después de haber optimizado los queries y re evaluado tu esquema de base de datos.
Locking
La penúltima fase es la estrategia de optimización de locks. Esta fase impacta en la concurrencia de la base de datos, afectando la performance de la misma.
Server Tunning
Él ultimo paso es la estrategia de optimización es tunear el server. Se puede tunear el server en diferentes aspectos: agregar memoria, mejorar el disco i/O, sumar procesador, etc. Dependiendo de donde tengamos el cuello de botella. Ojo, sumar mas hardware no asegura que el problema de performance desaparezca, sino que esta intimamente ligado a los aspectos tratados con anterioridad y lo que si puede pasar es que tarde un poco mas en alcanzar el pico, por ejemplo memoria, si no se revee bien los niveles anteriores.
