P11 Indices y explain plan

Gestión de índices en MySQL

Los índices son estructuras en árbol opcionales, asociadas con tablas, que permiten un camino de acceso más rápido a las filas de una tabla de datos. 

Sin los índices, MySQL debe recorrer una a una las filas de una tabla hasta encontrar aquellas que sean relevantes. 

Crear índices

Sintaxis

CREATE INDEX nombreindice

  ON nombretabla (col [,col2…]);

La siguiente sentencia crea un índice llamado idx_profesor_nombre, sobre la columna nombre de la tabla profesor.

CREATE INDEX idx_profesor_nombre

  ON profesor (nombre);

Borrar índices

Para borrar un índice:

DROP INDEX nombreindice

  ON nombre tabla;

La siguiente sentencia borra un índice de nombre idx_profesor_nombre creado sobre la tabla profesor.

DROP INDEX idx_profesor_nombre 

  ON profesor;

Índices de clave primaria

MySQL crea un índice de tipo primary key con cada clave primaria al crear una tabla. Así, la sentencia siguiente crea la tabla imparte con la restricción de clave primaria en las columnas (prof, asig), y un índice de nombre PRIMARY sobre las columnas (prof,asig) de la tabla imparte.

CREATE TABLE imparte (prof varchar(10), asig varchar(10), PRIMARY KEY (prof, asig));

Consultar índices

Para consultar los índices que se han creado asociados a una tabla se utiliza la sentencia SHOW INDEX:

SHOW INDEX FROM nombreTabla;

Mostrará la tabla (columna table) sobre la que se ha definido el índice, su nombre (columna key_name) y la columna sobre la que se ha definido (columna column_name). En el caso de que sea un índice definido sobre más de una columna, se mostrarán tantas filas como columnas forman el índice.

Ejemplo:

SHOW INDEX FROM profesor;

El resultado nos muestra una tabla con 3 filas que nos indica que la Table='imparte' tiene 2 índices creados:

• Key_name = 'PRIMARY' : índice definido sobre la clave primaria de la tabla formado por dos columnas: Column_name='dni' y sobre la Column_name='asignatura'

• Key_name = 'imparte_fk_2asignatura': índice definido sobre la column_name='asignatura'

Consultar el plan de ejecución

El conjunto de operaciones que el módulo optimizador de MySQL selecciona para resolver una consulta se denomina plan de ejecución. La sentencia EXPLAIN proporciona información acerca del plan de ejecución de cualquier sentencia.

EXPLAIN instrucción_SQL;

Su ejecución devuelve una fila con diferente información, pero las que nos interesan son las columnas: 

• possible_keys: muestra los posibles índices a elegir (los que podría mejorar esa consulta). Si no puede usar ninguno, aparecerá '(NULL)'

• key: de todos los anteriores, el índice finalmente usado. Si no usa ninguno, aparecerá '(NULL)'

• extra: información adicional sobre el uso de ese índice (para qué se ha usado y cómo). Algunos valores típicos son: using where (el índice se ha usado para filtrar la condición WHERE), using index (el índice se ha usado para mostrar los datos de la consulta)

Ejemplo:

EXPLAIN 

  SELECT * 

  FROM imparte 

  WHERE dni=’1’;

Explicación: en esta consulta el sistema sólo tiene para elegir el índice PRIMARY y finalmente hace uso de él para filtrar la condición WHERE y para mostrar los datos de la consulta.