2.3 Creación de Indices

INDICE:

Un indice es una estructura interna que el sistema puede usar para encontrar 1 o mas registros en una tabla de forma rapida,un indice de Base de Datos se crea para una columna o grupo de columnas, un indice de Base de Datos se crea para una columna o grupo de columnas. A un indice se le asigna un nombre de acuerdo con las mismas reglas que se aplican para los nombres de tablas[1].

Los índices son "estructuras" alternativa a la organización de los datos en una tabla. El propósito de los índices es acelerar el acceso a los datos mediante operaciones físicas más rápidas y efectivas. Para enterder mejor la importancia de un índice pongamos un ejemplo; imagínate que tienes delante las páginas amarillas, y deseas buscar el teléfono de Manuel Salazar que vive en Alicante. Lo que harás será buscar en ese pezado libro la población Alicante, y guiandote por la cabecera de las páginas buscarás los apellidos que empiezan por S de Salazar. De esa forma localizarás más rápido el apellido Salazar. Pues bien, enhorabuena, has estado usando un índice.
Pues el objetivo de definir índices en SQL Server es exáctamente para conseguir el mismo objetivo: acceder más rápido a los datos. Además SQL Server tiene dos tipos de índices que analizaremos a continuación[2].  

¿Por qué usar Índices?

• Mayor velocidad para localizar datos
• Reforzar la identificación única

Arquitectura de los Índices

• Es un conjunto de páginas distribuidas en un árbol B.
• Cada página mide 8kb = 8192 bytes, con un encabezado de 96b, dejando 8096b para datos.
• SQL Server accesa al Disco Duro por pagina
• En la Tabla “Sys indexes” se encuentra un puntero a la pagina raíz.

Sintaxis

Create índex nom_indice
On nom_tabla (columnas(s))

Ejemplo

Create índex idxclave
On clientes (Clave)[1]

Más informacion acerca de :

Creación de Índices

Si se utiliza el motor de datos Jet de Microsoft sólo se pueden crear índices en bases de datos del mismo motor. La sintaxis para crear un índice en ua tabla ya definida en la siguiente: 

CREATE [ UNIQUE ] INDEX índice
ON Tabla (campo [ASC|DESC][, campo [ASC|DESC], ...])
[WITH { PRIMARY | DISALLOW NULL | IGNORE NULL }]

En donde:

índice	Es el nombre del índice a crear.
tabla	Es el nombre de una tabla existente en la que se creará el índice.
campo	Es el nombre del campo o lista de campos que constituyen el índice.
ASC|DESC	Indica el orden de los valores de los campos ASC indica un orden ascendente (valor predeterminado) y DESC un orden descendente.
UNIQUE	Indica que el índice no puede contener valores duplicados.
DISALLOW NULL	Prohibe valores nulos en el índice
IGNORE NULL	Excluye del índice los valores nulos incluidos en los campos que lo componen.
PRIMARY	Asigna al índice la categoría de clave principal, en cada tabla sólo puede existir un único índice que sea "Clave Principal". Si un índice es clave principal implica que no puede contener valores nulos ni duplicados.

En el caso de ACCESS, se puede utilizar CREATE INDEX para crear un pseudo índice sobre una tabla adjunta en una fuente de datos ODBC tal como SQL Server que no tenga todavía un índice. No necesita permiso o tener acceso a un servidor remoto para crear un pseudo índice, además la base de datos remota no es consciente y no es afectada por el pseudo índice. Se utiliza la misma sintaxis para las tablas adjuntas que para las originales. Esto es especialmente útil para crear un índice en una tabla que sería de sólo lectura debido a la falta de un índice[3].

Índices no-agrupados

• 
  
  
  Los índices no agrupados tienen la misma estructura de árbol b que los índices agrupados, con algunos matices; como hemos visto antes, en los índices agrupados, en el último nivel del índice (nivel de hoja) están los datos; en los índices no-agrupados, en el nivel de hoja del índice, hay un puntero a la localización física de la fila correspondiente en el índice agrupado. Además, la ordenación de las filas del índice está construida en base a la(s) columna(s) indexadas, lo cual no quiere decir (a diferencia de los índices agrupados), que la organización física de las páginas de datos corresponda con el índice.

  Consideraciones para usar índices agrupados

• Columnas con datos muy selectivos
• Consultas que no devuelven muchas filas.
• Columnas en WHERE.
• Evitar acceso a páginas de datos realizando el acceso sólo por el índice.
• Covered queries (consultas cubiertas).
  En SQL Server 2005, son nuevos los índices INCLUDE que son índices no-agrupados que en el nivel de hoja del índice (donde está el puntero al índice agrupado), se puede incluir más columnas; el objetivo de este nuevo tipo de índices es beneficiar el uso de las consultar cubiertas para evitar que se acceda a la página de datos del índice agrupado[2].

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);

Si queremos eliminar un índice: ALTER TABLE tabla_nombre DROP INDEX nombre_indice¿para que sirven ?

LOs index permiten mayor rápidez en la ejecución de las consultas a la base de datos tipo SELECT ... WHERE

La regla básica es pues crear tus índices sobre aquellas columnas que vayas a usar con una cláusula WHERE, y no crearlos con aquellas columnas que vayan a ser objeto de un SELECT: SELECT texto from tabla_libros WHERE autor = Vazquez; En este ejemplo, la de autor es una columna buena candidata a un indice; la de texto, no.

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.

La regla de la izquierda 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 indices multicolumna y los utilizas en las clausulas WHERE, debes incluir siempre de izquierda a derecha las columnas indexadas; o el indice NO se usará:
Supongamos un INDEX usuario (id, name, adress), y una cláusula SELECT ... WHERE NAME = x. Este Select no aprovechará el índice. Tampoco lo haría un SELECT ... WHERE ID =X AND ADRESS = Y. Cualquier consulta que incluya una columna parte del index sin incluir además las columnas a su izquierda, no usará el indice.

Por tanto en nuestro ejemplo solo sacarian provecho del indice las consultas SELECT ... WHERE ID = x, o WHERE ID = X AND NAME = y o WHERE ID = x AND NAME = y AND ADRESS = Z
Cuando un índice contiene mas de una columna, cada columna es leida por el orden que ocupa de izquierda a derecha, y a efectos prácticos, cada columna (por ese orden) es como si constituyera su propio índice. Esto significa que en el ejemplo anterior, no haría falta crear otro INDEX ID (id) ya que podríamos usar nuestro INDEX USUARIO simplemente con la cláusula SELECT ... WHERE ID = X;
Puedes ver si tu llamada sql usa o no los índices correctos anteponiendo a select la orden explain:
EXPLAIN SELECT * FROM mitable WHERE ....
Y para ser sinceros, usando explain para comprobar el uso de indices en distintos selects con indices multicolumna, he obtenido resultados poco consistentes con la 'regla de la izquierda' ya que en muchos casos parece que se usaban indices que teóricamente no debian estar disponibles ... posiblemente un caso de mala configuracion en mi tabla-test.

Tipos de indice

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. 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');

Algunas limitaciones de los indices fulltext: solo busca por palabras completas. indice no encontrará indices. No se indexan las palabras de menos de cuatro letras. No se indexan columnas que contengan menos de tres filas, ni palabras que aparezcan en la mitad o mas de las filas. Las palabras separadas por guiones se cuentan como dos palabras.
Los indices 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));
 
 Desventajas de los indices Los índices se actualizan cada vez que se modifica la columna o columnas que utiliza. Por ello no es aconsejable usar como indices columnas en las que serán frecuentes operaciones de escritura (INSERT, UPDATE, DELETE).

Tampoco tendría sentido crear indices sobre columnas cuando cualquier select sobre ellos va a devolver una gran cantidad de resultados; por ejemplo una columna booleana que admita los valores Y/N. En fin, tampoco es necesario usar indices en tablas demasiado pequeñas, ya que en estos casos no hay ganancia de rapidez frente a una consulta normal.
Finalmente, los índices ocupan espacio. A veces, incluso mas que la tabla de datos[4].

Referencias

[1]  http://sistemas.itlp.edu.mx/tutoriales/tallerdebasesdedatos/t23.htm

[2] http://www.zonacodigos.com/index.php/sql/59-creacion-de-indices 

[3] http://www.mailxmail.com/curso-sql/creacion-indices

[4] http://www.webtaller.com/construccion/lenguajes/mysql/lecciones/indices-mysql-2.php