¿Que es un índice?
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);
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.
13.1.4. Sintaxis de CREATE INDEX
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
[USING index_type]
ON tbl_name (index_col_name,...)
index_col_name:
col_name [(length)] [ASC | DESC]
En MySQL 5.0, CREATE INDEX se mapea a un comando ALTER TABLE para crear índices. Consulte Sección 13.1.2, “Sintaxis de ALTER TABLE”.
Normalmente, crea todos los índices en una tabla cuando se crea la propia tabla con CREATE TABLE. Consulte Sección 13.1.5, “Sintaxis de CREATE TABLE”. CREATE INDEX le permite añadir índices a tablas existentes.
Una lista de columnas de la forma (col1,col2,...) crea un índice de múltiples columnas. Los valores de índice se forman al concatenar los valores de las columnas dadas.
Para columnas CHAR y VARCHAR, los índices pueden crearse para que usen sólo parte de una columna, usando col_name(length) para indexar un prefijo consistente en los primeros length caracteres de cada valor de la columna. BLOB t TEXT pueden indexarse, pero se debe dar una longitud de prefijo.
El comando mostrado aquí crea un índice usando los primeros 10 caracteres de la columna name :
CREATE INDEX part_of_name ON customer (name(10));
Como la mayoría de nombres usualmente difieren en los primeros 10 caracteres, este índice no debería ser mucho más lento que un índice creado con la columna name entera. Además, usar columnas parcialmente para índices puede hacer un fichero índice mucho menor, que puede ahorrar mucho espacio de disco y además acelarar las operaciones INSERT .
Los prefijos pueden tener una longitud de hasta 255 bytes. Para tablas MyISAM y InnoDB en MySQL 5.0, pueden tener una longitud de hasta 1000 bytes . Tenga en cuenta que los límites de los prefijos se miden en bytes, mientras que la longitud de prefijo en comandos CREATE INDEX se interpreta como el número de caracteres. Tenga esto en cuenta cuando especifique una longitud de prefijo para una columna que use un conjunto de caracteres de múltiples bytes.
En MySQL 5.0:
Puede añadir un índice en una columna que puede tener valores NULL sólo si está usando MyISAM, InnoDB, o BDB .
Puede añadir un índice en una columna BLOB o TEXT sólo si está usando el tipo de tabla MyISAM, BDB, o InnoDB .
Una especificación index_col_name puede acabar con ASC o DESC. Estas palabras se permiten para extensiones futuras para especificar almacenamiento de índice ascendente o descendente. Actualmente se parsean pero se ignoran; los valores de índice siempre se almacenan en orden ascendente.
En MySQL 5.0, algunos motores le permiten especificar un tipo de índice cuando se crea un índice. La sintaxis para el especificador index_type es USING type_name. Los valores type_name posibles soportados por distintos motores se muestran en la siguiente tabla. Donde se muestran múltiples tipos de índice , el primero es el tipo por defecto cuando no se especifica index_type .
Motor de almacenamiento
Tipos de índice permitidos
MyISAM
BTREE
InnoDB
BTREE
MEMORY/HEAP
HASH, BTREE
Ejemplo:
CREATE TABLE lookup (id INT) ENGINE = MEMORY;
CREATE INDEX id_index USING BTREE ON lookup (id);
TYPE type_name puede usarse como sinónimo de USING type_name para especificar un tipo de índice. Sin embargo, USING es la forma preferida. Además, el nombre de índice que precede el tipo de índice en la especificación de la sintaxis de índice no es opcional con TYPE. Esto es debido a que, en contra de USING, TYPE no es una palabra reservada y se interpreta como nombre de índice.
Si especifica un tipo de índice que no es legal para un motor de almacenamiento, pero hay otro tipo de índice disponible que puede usar el motor sin afectar los resultados de la consulta, el motor usa el tipo disponible.
Para más información sobre cómo MySQL usa índices, consulte Sección 7.4.5, “Cómo utiliza MySQL los índices”.
Índices FULLTEXT en MySQL 5.0 puede indexar sólo columnas CHAR, VARCHAR, y TEXT , y sólo en tablas MyISAM . Consulte Sección 12.7, “Funciones de búsqueda de texto completo (Full-Text)”.
Índices SPATIAL en MySQL 5.0 puede indexar sólo columnas espaciales, y sólo en tablas MyISAM . Los tipo de columna espaciales se describen en Capítulo 18, Extensiones espaciales de MySQL.
Referencia:
Como crear indices en Oracle:
¿QUÉ ES UN ÍNDICE?
Un índice es una estructura de memoria secundaria que permite el acceso directo a las filas de una tabla (esté o no agrupada).
Aumenta la velocidad de respuesta de la consulta, mejorando su rendimiento y optimizando su resultado.
Su manejo se hace de forma inteligente. Es el propio Oracle quien decide qué índice se necesita.
TIPOS DE ÍNDICES EN ORACLE
Lectura/Escritura
B-tree (árboles binarios)
Function Based
Reserve key
Sólo lectura (read only)
Bitmap
Bitmap join
Index-organized table (algunas veces usados en lectura/escritura)
Cluster y hash cluster
Domain (muy específicos en aplicaciones Oracle)
ÍNDICES CREADOS POR ORACLE DE MANERA AUTOMÁTICA
Al crearse la tabla se crea:
Un índice UNIQUE basado en B*-tree para mantener las columnas que se hayan definido como clave primaria de una tabla utilizando el constraint PRIMARY KEY de una tabla no organizada por índice.
Un índice UNIQUE basado en B*-tree para mantener la restricción de unicidad de cada grupo de columnas que se haya declarado como único utilizando el constraint UNIQUE.
Un índice basado en B*-tree para mantener las columnas que se hayan definido como clave primaria y todas las filas de una tabla organizada por índice.
Un índice basado en hashing para mantener las filas de un grupo de tablas (“cluster”) organizado por hash.
REGLAS EN EL DISEÑO DE ÍNDICES
Indexe solamente las tablas cuando las consultas no accedan a una gran cantidad de filas de la tabla.
No indexe tablas que son actualizadas con mucha frecuencia.
Indexe aquellas tablas que no tengan muchos valores repetidos en las columnas escogidas.
Las consultas muy complejas (en la cláusula WHERE) por lo general no toman mucha ventaja de los índices.
SINTAXIS: CREACIÓN
Básica
CREATE INDEX nombre_indice ON [esquema.] nombre_tabla (columna1 [, columna2, ...])
UNIQUE garantizan que en una tabla (o “cluster”) no puedan existir dos filas con el mismo valor.
SINTAXIS: MODIFICACIÓN
Básica
ALTER INDEX [schema.]index options
SINTAXIS: ELIMINACIÓN
Básica
DROP INDEX [schema.]index [FORCE]
ESTRUCTURA: B*-TREE
Se estructura como un árbol cuya raíz contiene múltiples entradas y valores de claves que apuntan al siguiente nivel del árbol.
Nivel 0.
tablas pequeñas de datos estáticos.
Nivel 1.
Indexa tablas dinámicas con el valor único de los identificadores de columna.
Nivel 2.
Indexa largas tablas o con poca cardinalidad.
ESTRUCTURA: BITMAP
Son efectivos para columnas simples con poca cardinalidad, esto es muchos valores distintos.
Más rápidos que los B*-Tree en entornos de read-only.
Almacenan valores de 0 ó 1 en el ROWID.
Ejemplo:
create bitmap index person_region on person (region);
Creación de indices en la Base de Datos Veterinaria:
D:\xampp\mysql\bin>mysql -hlocalhost -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.5.27 MySQL Community Server (GPL)
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| cdcol |
| mysql |
| phpmyadmin |
| sergio |
| veterinaria |
| webauth |
+--------------------+
7 rows in set (0.00 sec)
mysql> use veterinaria;
Database changed
mysql> show tables;
+-----------------------+
| Tables_in_veterinaria |
+-----------------------+
| cliente |
| general |
| general1 |
| mascota |
| visita |
+-----------------------+
5 rows in set (0.00 sec)
mysql> create index part_of_name ON cliente (nombre(10));
Query OK, 8 rows affected (0.09 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> show tables;
+-----------------------+
| Tables_in_veterinaria |
+-----------------------+
| cliente |
| general |
| general1 |
| mascota |
| visita |
+-----------------------+
5 rows in set (0.00 sec)
mysql> describe visita;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| nombre | char(20) | NO | | NULL | |
| razon | char(50) | YES | | NULL | |
| fecha | varchar(10) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.03 sec)
mysql> create index part_of_name ON visita (nombre(10));
Query OK, 20 rows affected (0.08 sec)
Records: 20 Duplicates: 0 Warnings: 0
mysql> create index part_of_razon ON visita (razon(10));
Query OK, 20 rows affected (0.09 sec)
Records: 20 Duplicates: 0 Warnings: 0
mysql> create index partofid ON general (id_nombre(1));
Query OK, 4 rows affected (0.11 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> describe mascota
-> ;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| numcliente | int(11) | NO | PRI | NULL | |
| color | varchar(30) | YES | | NULL | |
| sexo | varchar(10) | YES | | NULL | |
| raza | varchar(30) | YES | | NULL | |
| motivoconsulta | varchar(30) | YES | | NULL | |
| nombrem | varchar(30) | YES | | NULL | |
+----------------+-------------+------+-----+---------+-------+
6 rows in set (0.04 sec)
mysql> create index partofmascota ON mascota (raza(11));
Query OK, 3 rows affected (0.03 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> create index partofcolsulta ON mascota (motivoconsulta(15));
Query OK, 3 rows affected (0.04 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> describe general1;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| nombre | varchar(30) | YES | | NULL | |
| direccion | varchar(30) | YES | | NULL | |
| telefono | int(11) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
3 rows in set (0.02 sec)
mysql> create index partofname ON general1 (nombre(15));
Query OK, 4 rows affected (0.11 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> create index partofdireccion ON general1 (direccion(15));
Query OK, 4 rows affected (0.18 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql>
No hay comentarios:
Publicar un comentario