INDICES
¿Qué es un índice?
• Es una estructura auxiliar que permite mejorar
el rendimiento de las consultas mediante la
reducción de la actividad de entrada/salida
necesaria para recuperar los datos solicitados.
• Cuando el sistema necesita buscar u ordenar
datos, recurre (si conviene) al índice en lugar de
trabajar con la tabla completa, lo cual aumenta
la rapidez en el acceso.
Indices no agrupados
• MyISAM utiliza índices tradicionales (no agrupados):
• El índice se almacena en un fichero separado que contiene
una lista de valores de índice y un valor que representa la
posición del registro en la tabla.
• Ventajas:
• El fichero del índice es más pequeño que el de la tabla, por lo que la
búsqueda es más rápida.
• El SGBD generalmente “cachea” en memoria RAM el índice por lo
que no hay que leer de disco (menos rápido)
• El índice no tiene valores duplicados por lo que en cuanto
encontremos el primer valor la búsqueda podría detenerse
• Los datos en el índice están ordenados, que junto con el punto
anterior permite algoritmos de búsqueda muy rápidos.
• Inconveniente:
• El fichero de datos puede estar muy fragmentado, por lo que
encontrar los datos a los que apunta el índice puede resultar lento
(ver gráfico siguiente).
Indice tradicional
Indices agrupados (clustered index)
• En cada tabla InnoDB existe un índice agrupado.
• El índice se almacena junto a los datos referidos a
este en una estructura de árbol (el índice y los
datos están ordenados)
• Cuando un nuevo registro es añadido a la tabla, se
escribe en el sitio que le corresponde dentro del
árbol.
• Si se define una clave primaria, el índice de esta
será el índice agrupado. Si no, se tomará el primer
índice UNIQUE que tenga columnas NOT NULL. Y
si no se cumple nada de esto, InnoDB toma un
identificador de fila que crea automaticamente
como índice agrupado
Indices agrupados (clustered index)
• Los datos en InnoDB se almacenan en páginas de
16 Kb. Si el tamaño de la tabla es pequeño puede
almacenarse en una sóla página formando una
especie de lista ordenada.
Indices agrupados (clustered index)
• Cuando los datos ya no pueden almacenarse en una
sola página, la lista se convierte en un árbol. La página
de datos se divide en dos y la página original se
convierte en el índice que cubre ambas páginas.
Indices agrupados (clustered index)
• Si hay más y más datos, el árbol va creciendo y haciéndose más
complejo.
• Las páginas azules representan índices. Vemos que los índices no
sólo pueden apuntar a páginas con datos, sino a otros índices
intermedios.
Indices agrupados (clustered index)
• Es obvio por tanto que sólo puede haber un índice agrupado
por tabla.
• Dado que todos los datos se organizan según este índice, es
importante elegir como clave primaria de las tablas campos
que sufran poca variación a lo largo del tiempo, por ejemplo,
mejor el DNI que un teléfono para identificar a un cliente.
• Esta estructura, comparada con la solución tradicional, ahorra
acceso a disco aunque la tabla sea grande y el índice no se
encuentre en la misma página que los datos.
• En InnoDB, las entradas en índices no agrupados (también
llamados índices secundarios) contienen también el valor
de clave primaria de la fila siempre. InnoDB utiliza este valor
de clave primaria para buscar la fila a partir del índice
agrupado. A mayor tamaño de la clave primaria, mayor
tamaño de estos índices secundarios.
Estructuras de índices
• B-TREE
• Una de las estructuras más habituales en los SGBD
• Son los mejores para consultas basadas en rangos
• HASH
• Se basan en una función que mapea o hace
corresponder los valores de clave con valores
numéricos que permite localizar el registro
correspondiente.
• No es recomendable en búsquedas de rangos.
• R-TREE
• Se usan para datos de tipo espacial
index_type
Algunos motores de MySQL permiten especificar un tipo de
índice cuando lo estamos creando.
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
[USING index_type] ON tbl_name (index_col_name,...)
Motor de almacenamiento Tipos de índices permitidos
MyISAM BTREE
InnoDB BTREE
Memory HASH, BTREE
Tipos de índices en MySQL
INDICE CONVENCIONAL
Permite valores duplicados y también nulos.
INDICE EXCLUSIVO (UNIQUE)
No permite valores duplicados
Si se permite valores nulos
INDICE DE TEXTO COMPLETO (FULL-TEXT)
Facilita la búsqueda de palabras en campos de texto
TEXT, CHAR o VARCHAR.
Se puede usar solo en MyISAM e INNODB
INDICE ESPACIAL (SPATIAL)
Indexa columnas espaciales como LINE o CURVE
Creación de un índice convencional
CREATE TABLE nombreTabla(campo1
tipo, campo2 tipo, INDEX
[nombreIndice] (campol [, campo2,...]
));
ALTER TABLE nombreTabla ADD INDEX
[nombreIndice](campol [,campo2,...]);
CREATE INDEX nombreIndice ON
nombreTabla (campo1 [,campo2...]);
Creación de un índice único
CREATE TABLE nombreTabla(campo1
tipo UNIQUE, campo2 tipo,.. ) ;
CREATE TABLE nombreTabla(campo1
tipo, campo2 tipo, UNIQUE [INDEX]
[nombreIndice] (campol [, campo2,...]
));
ALTER TABLE nombreTabla ADD UNIQUE
[nombreIndice](campol [,campo2,...]);
CREATE UNIQUE INDEX nombreIndice
ON nombreTabla (campo1
[,campo2...]);
Creación de un índice Full-Text
CREATE TABLE nombreTabla(campo1
tipo, campo2 tipo, FULLTEXT
[INDEX] [nombreIndice] (campol [,
campo2,...] ) ) ;
ALTER TABLE nombreTabla ADD
FULLTEXT [nombreIndice](campol
[,campo2,...]);
CREATE FULLTEXT INDEX nombreIndice
ON nombreTabla (campo1
[,campo2...]);
Sustituyendo FULLTEXT por SPATIAL crearemos un índice espacial
Indices parciales
Podemos crear índices que no ocupen la
totalidad en campos CHAR, VARCHAR, BLOB
y TEXT para que estos sean más pequeños y
las inserciones sean más rápidas.
Se realiza indicando la longitud entre paréntesis
tras el nombre del campo. Es obligatorio si los
campos son BLOB o TEXT
CREATE INDEX nombreParcial ON clientes (nombre(15));
Eliminación de un índice
Eliminación de un índice
ALTER TABLE tabla DROP INDEX nombreIndice;
DROP INDEX nombreIndice ON tabla;
Si desconocemos el nombre podemos usar:
SHOW CREATE TABLE nombretabla;
SHOW INDEX FROM nombretabla;
SHOW INDEX FROM…
Restricciones e Indices
TIPO DE RESTRICCION INDICE
PRIMARY KEY Genera un índice único
Genera un índice
convencional para la clave
FOREIGN KEY
foránea si no existe
previamente.
UNIQUE Genera un índice único