Capítulo 7

Optimización de consultas

ÍNDICE CAPÍTULO 7

Índices• Tipos de índices• Índices en MySQL• Gestión de índices• Procesamiento de consultas• Optimización de consultas e índices

Sugerencias de ejecución de consultas

Índices

Ficheros que contienen referenecias a datos de la base de datos

Aumentan la velocidad de acceso a los datos

No debe abusarse de ellos, ocupan espacio y deben ser gestionados

ÍndicesTipos

Únicos

Con valores repetidos

Parciales

Multicolumna

Secundarios y Cluster

Basados en funciones

Índices en MySQL

En cuanto a su estructura

• HASH• B-TREE

En cuanto al tipo

• UNIQUE: formados por campos cuyo valor no se repite en la tabla

• PRIMARY: son índices sobre los campos que forman parte de la clave primaria de una tabla

• full-text: formados por uno o varios campos de texto y utilizados para la búsqueda de palabras dentro de un campo

• SPATIAL: son índices usados para datos de tipo espacial como LINE o CURVE

Gestión de índices ICreación de índices

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name[index_type]ON tbl_name (index_col_name,...)[index_type]

index_col_name:col_name [(length)] [ASC | DESC]

index_type:USING {BTREE | HASH}

También podemos usar el comando ALTER TABLE de modificación de tablas

Visualización de índices

SHOW {INDEX | INDEXES | KEYS}{FROM | IN} tbl_name[{FROM | IN} db_name][WHERE expr]

Eliminar índices

DROP INDEX index_name ON tbl_name

Gestión de índices II

Ejemplo

Por ejemplo, para añadir un índice de tipo b-tree a nuestra tabla cliente usaríamos algo así:

ALTER TABLE cliente ADD INDEX nombre USING BTREE

Para ver los índices creados:

SHOW INDEXES FROM cliente

Y para eliminarlo:

ALTER TABLE cliente DROP INDEX nomreindice

Los índices no pueden actualizarse, deben ser eliminados y destruidos para modificarlos.

Procesamiento de consultas I

Caché de consultas

• Determinada por valor de la variable query_cache_type

• Determina comportamiento de las cláusulas SQL_CACHE, SQL_NO_CACHE

Procesamiento de consultas II

Parseo, análisis y optimización

El parseo y análisis tienen en cuenta:

-El tipo de consulta:INSERT, UPDATE, SELECT o DELETE, GRANT o SET

-Tablas involucradas

-Contenido de la cláusula WHERE (si existe)

-Otros modificadores, como GROUP BY, ORDER BY, etc.

Optimización de consultas e índices I

Aspectos a tener en cuenta en la optimización:

Cómo se ejecutan/procesan las consultas

Índices existentes

Cómo se almacenan los índices

Optimización de consultas e índices II

Índices son especialmente importantes en los siguientes casos

• Consultas con cláusulas WHERE que contienen columnas indexadas.

• Para descartar filas en consultas

• En combinaciones de tablas cuando existe un índice sobre los campos comunes

• Para encontrar el valor de una función de agregado sobre campos indexados

• Para ordenar o agrupar campos indizados de tablas siempre que se haga sobre la parte más a la izquierda del índice

• Para casos en que solo se requieren columnas indizadas

Comando EXPLAIN

Sintaxis:

EXPLAIN [EXTENDED | PARTITIONS] SELECT select_options

Ejemplo

EXPLAIN EXTENDED SELECT * FROM enlaces WHERE link_id=24\G

Resultado:

id: 1select_type: SIMPLEtable: wp_linkstype: constpossible_keys: PRIMARYkey: PRIMARYkey_len: 8ref: constrows: 1

Comando EXPLAINSalida I

Sintaxis:

• id: es el número de tabla en la consulta

• select_type: es el rol de la tabla en la consulta

SIMPLEUNIONDEPENDENT UNIONUNION RESULTSUBQUERYDEPENDENT SUBQUERYPRIMARYDERIVED

• Table: es el nombre de la tabla de la que se extraen los registros

• Type: indica qué tipo de valores se usan en la consulta

const, ref, range, index y ALL

Comando EXPLAINSalida II

Sintaxis:

• Possible_keys: posibles índices

• Key: nombre del índice que MySQL decidie usar

• Ref: columnas usados para hacer coincidir con la clave

• Eq_ref cuando se utiliza todo el índice único de unatabla

• Rows indica el número de filas que MySQL debeexaminar

• Extra información adicional

Optimizando con EXPLAINForzar índices

En consultas SELECT después de la cláusula FROM

USE|FORCE|IGNORE {INDEX|KEY}[{FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])

Ejemplo

En el siguiente ejemplo forzamos el índice fecha para la obtenciónde los datos de noticias:

SELECT * FROM noticias FORCE INDEX (fecha) ORDER BY fecha;

Optimizando con EXPLAINWHERE I

En búsquedas de rangos de datos

Campo type de EXPLAIN = range

Campo Extra de EXPLAIN= Using where

Optimizando con EXPLAINWHERE II

Ejemplo

EXPLAIN SELECT * FROM fuentes WHERE fuente_id between 10 AND 20;

Consulta simple, debe recorrer todas las filas de la tabla fuentes con un filtroWHERE

Si indexamos con una clave primaria:

ALTER TABLE fuentes ADD PRIMARY KEY cp(link_id)

Vemos que ahora se indica range como type (es decir, va a usar el archivo de índicepara buscar las filas necesarias) y rows = 1, es decir el servidor estima quenecesitará acceder aproximadamente a 1 filas para poder realizar la consulta.

id: 1select_type: SIMPLEtable: wp_linkstype: rangepossible_keys: PRIMARYkey: PRIMARYkey_len: 4ref: NULLrows: 1Extra: Using where

También se añade información extra indicando que hay un filtro WHERE

Optimizando con EXPLAINORDER BY, GROUP BY

Campo Extra= Using filesort

Orden debe coincidir con el ordendel índice

No se puede hacer sobre camposde más de un índice

Optimizando con EXPLAINORDER BY, GROUP BY

Ejemplo

Plan de ejecución de la consulta que obtiene los datos de los equipos ordenados porciudad:

EXPLAIN SELECT * FROM equipo ORDER BY ciudad;

En el campo Extra de EXPLAIN veremos el valor: Using filesort

Lo que indica la necesidad de ordenación.

Para evitarlo los campos de ordenación deben ser todos ellos parte de un índice y la ordenación debe ser en el mismo orden.

En el siguiente ejemplo sobre motorblog podemos verlo:

EXPLAIN SELECT * FROM noticias ORDER BY id;

El resultado no incluirá el valor using filesort anterior para el campo Extra lo quedemuestra el uso de índices para la consulta.

Optimizando con EXPLAINGROUP BY I

Campo Extra= Using temporary

Cuando se usan distintos camposrespecto a ORDER BY

Optimizando con EXPLAINGROUP BY II

Ejemplo

En el siguiente ejemplo obtenemos el número de partidos de cada equipocomo local (es un campo indizado):

EXPLAIN SELECT count(*) FROM partido GROUP BY local;

En este caso obtenemos en el campo Extra el valor Using index indicandoque la consulta se puede realizar solamente usando el archivo de índices.

Sin embargo, si lo que queremos es el número de partidos de cada equipocomo visitante, es decir:

EXPLAIN SELECT count(*) FROM partido GROUP BY visitante;

En este caso el campo Extra contendrá el valor Using index, Using temporary, Using filesort indicando la necesidad del índice, de una tablatemporal y de una ordenación para realizar la consulta.

Optimizando con EXPLAINVarios índices

Se elige el que de menor cardinalidad en EXPLAIN

Ejemplo

SELECT titulo FROM noticias WHERE fecha >’2010-02-02’ AND id>2000’;

Al haber índices sobre ambos campos, MySQL debe elegir el más óptimo que será el que menos filas requiera para realizar la consulta. Si usamos EXPLAIN obtenemos que en este caso usará el índice fecha mientras que si subimos de 2000 a 20000 en la condición de id se decantará por el índice sobre id.

Optimizando con EXPLAINVarias tablas I

Debe observarse el campo type en la salida de EXPLAIN:

• eq_ref

Cuando la clave ajena de la tabla secundaria (la segunda que muestra EXPLAIN cuando la consulta es de dos tablas) es única en cuyo caso cada fila de la tablaprincipal se combina con una única fila de la secundaria

•ref

La clave ajena no tiene por que ser única de forma que cada fila de la tabla principal se combina con cada coincidencia en la secundaria

• unique_subquery

Cuando la subconsulta se basa solo en campos clave de una tabla de forma que esuna función la que hace al cálculo sin necesidad de acceder a la tabla original

• index_subquery

similar a la anterior pero permitiendo índices no únicos

Optimizando con EXPLAINVarias tablas II

Recordatorio proceso de una consulta:

1. El servidor lee la primera fila de la primera tabla

2. Compara dicha fila con cada fila de la segunda tablasegún las condiciones WHERE especificadas

3. Para cada coincidencia repite el proceso con la terceratabla. El proceso se repite para cada fila de la segundatabla con la tercera y así sucesivamente hasta recorrertodas las tablas

Optimizando con EXPLAINVarias tablas III

Ejemplo (suponemos que no hay índices creados)

noticias del autor con id = 1:

SELECT * FROM autor STRAIGHT_JOIN noticias ON noticias.autor=autor.nombre AND autor.id=1\G

Hemos usado un STRAIGHT_JOIN para asegurar que la combinación se realiceen el orden que queremos para ilustrar mejor el uso de índices

Resultado EXPLAINid: 1select_type: SIMPLEtable: autortype: ALLpossible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 6Extra: Using where

id: 1select_type: SIMPLEtable: noticiastype: ALLpossible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 3843Extra: Using where

Optimizando con EXPLAINVarias tablas IV

Ejemplo (continuación)

Debemos recorrer 6 × 3843 filas

Si optimizamos creando índices en columna autor de la tabla noticias

Ahora debemos recorrer 5x6 filas

id: 1select_type: SIMPLEtable: utype: ALLpossible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 6Extra: Using where

id: 1select_type: SIMPLEtable: ptype: refpossible_keys: in1key: in1key_len: 8ref: constrows: 5Extra:

Optimizando con EXPLAINVarias tablas V

Ejemplo (continuación)

Si además creamos índice único en el campo id mejoraremos la consulta

ALTER TABLE autor ADD PRIMARY KEY(id);

Después de EXPLAIN obtenemos:

Ahora debemos recorrer 8x1 filas

id: 1select_type: SIMPLEtable: utype: constpossible_keys: PRIMARYkey: PRIMARYkey_len: 4ref: constrows: 1Extra:

id: 1select_type: SIMPLEtable: ptype: refpossible_keys: in1key: in1key_len: 8ref: constrows: 5Extra:

Optimizando con EXPLAINVarias tablas VI

Ejemplo (continuación)

Vemos el type= ref

Si modificamos la consulta forzando el orden con STRAGIHT_JOIN:

SELECT * FROM noticias STRAGIHT_JOIN autor WHERE noticias.autor=autor.nombre\GDespués de EXPLAIN obtenemos:

Encontramos eq_ref en el campo type de la salida de EXPLAIN. Esto es asíporque para cada fila de la tabla noticias hay un solo posible autor.

id: 1select_type: SIMPLEtable: utype: eq_refpossible_keys: PRIMARYkey: PRIMARYkey_len: 4ref: motorblog.p.autorrows: 1Extra: Using where

id: 1select_type: SIMPLEtable: ptype: ALLpossible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 3843Extra:

Sugerencias de ejecución de consultas

La importancia de las pruebas benchmarking

MySQL Benchmark Suite, mysqlslap, Sysbench

Encontrar lo cuellos de botella

Ajustar el diseño

Particionar las tablas

No abusar de los índices

Conocer los tipos de tabla

Revisar los comandos de comprobación de tablas

ANALYZE TABLE, CHECK TABLE, OPTIMIZE TABLE

Observa las opciones específicas de los comandos MySQL

Estar actualizado: Leer blogs específicos (mysqlpeformanceblog.com, etc.

FIN CAPITULO 7