Índices en Mysql Workbench PDF

Preview

Summary

Índices en Mysql Workbench...

Description

Índices en Mysql Workbench Los índices en MySQL le permiten localizar y devolver registros de una manera sencilla y rápida, son especialmente útiles cuando queremos buscar elementos en los millones o incluso billones de registros que una tabla puede contener en un momento dado [1]. Cuando no usamos índices, a veces podemos percibir que MySQL tarda demasiado en responder una consulta o, incluso para usuarios inexpertos, puede parecer que se ha creado un índice pero que no se siente una mejora en la velocidad. Los índices son usados para encontrar rápidamente los registros que tengan un determinado valor en alguna de sus columnas. Sin un índice, MySQL tiene que iniciar con el primer registro y leer a través de toda la tabla para encontrar los registros relevantes. Aún en tablas pequeñas, de 1000 registros, es por lo menos 100 veces más rápido leer los datos usando un índice, que haciendo una lectura secuencial [1]. Cuando MySQL trata de responder una consulta, examina una variedad de estadísticas acerca de nuestros datos y decide como buscar los datos que deseamos de la manera más rápida. Sin embargo, cuando en una tabla no existen índices, en los cuales pueda auxiliarse MySQL para resolver una consulta, tendrán que leer todos los registros de la tabla de manera secuencial, esto es llamado un escaneo completo de una tabla, y es muchas veces es algo que se debe evitar [1]. Estas son algunas razones porque evitar los escaneos completos de tablas: Sobrecarga de disco: En una tabla muy grande, un escaneo completo consume una gran cantidad de entrada/salida en el disco. Esto puede alentar significativamente nuestro servidor de bases de datos, especialmente si tenemos un disco IDE algo antiguo [1]. Concurrencia: Mientras MySQL está leyendo los datos de una tabla, éste la bloquea, de tal manera que nadie más puede escribir en ella, aunque si pueden leerla, cuando MySQL está actualizando o eliminando filas de una tabla, éste la bloquea, y por lo tanto nadie puede al menos leerla [1]. Sobrecarga de CPU: El proceso de checar cada uno de los registros en una tabla es insignificante cuando se tienen pocos datos, pero puede convertirse en un problema a medida que va aumentando la cantidad de registros en nuestra tabla [1].

Índice primario: Es un índice que tienen archivos ordenados secuencialmente, es el índice cuya clave de búsqueda especifica el orden secuencial del archivo [2].También se llama índice con agrupación. La clave de búsqueda de un índice primario suele ser la clave primaria, aunque no necesariamente. Representan uno de los esquemas de índices más antiguos usados por los sistemas de bases de datos, se emplean en aquellas aplicaciones que demandan un procesamiento secuencial del archivo completo así como un acceso directo a sus registros [2]. Índice denso: El registro de índice contiene el valor de la clave de búsqueda y un puntero al primer registro con el valor de la clave de búsqueda [2]. Los registros restantes con el mismo valor de clave de búsqueda se almacenan consecutivamente después del primer registro, dado que el índice es el índice principal, los registros se ordenan con la misma clave de búsqueda. Una implementación de índice denso puede almacenar una lista de punteros a todos los registros con la misma clave de búsqueda; esto no es necesario para el índice principal [2]. Índice disperso: Este índice solo crea registros de índice para ciertos valores, son como los índices densos, cada registro de índice, contiene el valor de una clave de búsqueda y un puntero al primer registro con el valor clave, para ubicar el registro buscado con la entrada de índice con el valor máximo o igual a este valor. Se empieza con el registro señalado por la entrada de índice y luego se continúa usando el puntero de archivo hasta que encuentre el registro que desea [2]. Índices multinivel: Este índice consiste en varios índices secuenciales vinculados, y habrá un índice que apunta al registro de datos y otros índices que apuntan a índices de nivel inferior [2]. La aparición de este método es para mejorar la organización del orden de la cadena, los índices de alto nivel suelen ser escasos y el primer nivel es denso. Al igual que con la organización secuencial, el aumento del número de registros seguirá aumentando el número de entradas. Los índices multinivel están estrechamente relacionados con la estructura de árbol, tales como los árboles binarios usados para la indexación en memoria. [2]

Índices secundarios: Los índices secundarios deben ser densos, con una entrada en el índice por cada valor de la clave de búsqueda, y un puntero a cada registro del archivo, si un índice secundario almacena sólo algunos de los valores de la clave de búsqueda, los registros con los valores de la clave de búsqueda intermedios pueden estar en cualquier lugar del archivo y, en general, no se pueden encontrar sin explorar el archivo completo [2]. Por lo general, los índices secundarios están estructurados de manera diferente a como lo están los índices primarios, por tanto, un índice secundario debe contener punteros a todos los registros [2]. Los punteros en estos índices secundarios no apuntan directamente al archivo, en vez de eso, cada puntero apunta a un cajón que contiene punteros al archivo [2]. El diseñador de la base de datos debe decidir qué índices secundarios son deseables, según una estimación sobre las frecuencias de las consultas y las modificaciones. Podemos tener los siguientes tipos de índices en una tabla de MySQL: -

Únicos

-

Primarios

-

Ordinarios

-

De texto completo

-

Parte de campos o columnas

Sintaxis de la sentencia de MySQL CREATE INDEX:

Con el código superior CREATE INDEX se puede crear uno o varios índices ordinarios en una tabla existente [3]. Con el código CREATE INDEX se crea uno o varios índices de texto completo que es el código FULLTEXT y se puede crear en una tabla existente [3].

Incide único (UNIQUE, ADD UNIQUE): Los índices únicos son básicamente como los índices ordinarios, excepto que los valores duplicados no son permitidos [4]. Para crear un índice UNIQUE se tienen básicamente dos opciones: 1) Crear un índice único cuando la tabla es creada con el uso de la opción UNIQUE [4].

2) Si la tabla ya existe, se usa la sentencia ALTER TABLE [4].

3) De igual manera, también es posible usar la sentencia CREATE INDEX para crear un índice único en una tabla existente [4].

Índice Primario (PRIMARY KEY): Es un índice sobre uno o más campos donde cada valor es único y ninguno de los valores son NULL. Para crear un índice de clave primaria tenemos básicamente dos opciones: 1) Crear el índice de clave primaria al momento de crear la tabla. En este caso se usa la opción PRIMARY KEY al final de la definición de los campos, con una lista de los campos que serán parte del índice [4].

2) Crear una clave primaria en una tabla existente con el uso del comando ALTER TABLE [4].

Índice Ordinario (ADD INDEX): Este tipo de índice en MySQL permite le existencia o inserción de valores duplicados en una tabla, con este tipo solamente mejoramos la ejecución y rendimiento de las consultas [4]. Para crear un índice ordinario tenemos básicamente dos opciones: 1) Podemos crear un índice ordinario al mismo tiempo que creamos la tabla con el uso de la opción INDEX [4].

2) Podemos crear el índice con el uso de la sentencia ALTER TABLE si es que la tabla ya existe.

3) También es posible usar la sentencia CREATE INDEX para crear un índice en una tabla existente [4].

Ambas sentencias piden el nombre del índice, sin embargo con la sentencia CREATE INDEX el nombre es obligatorio. Índices de texto completo (FULLTEXT): Los índices de texto completo son del tipo FULLTEXT, se usan en tablas del tipo MyISAM, y pueden contener uno o más campos del tipo CHAR, VARCHAR y TEXT. Un índice de texto completo está diseñado para facilitar y optimizar la búsqueda de palabras clave en tablas que tienen grandes cantidades de información en campos de texto [4]. Para crear un índice de texto completo tenemos básicamente dos opciones: 1) Crear el índice al momento de crear la tabla [4].

2) Crear el índice una vez que ha sido creada la tabla [4].

3) También esta sentencia se puede usar para crear un índice cuando la tabla ya existe [4].

Índices Compuestos: Los índices compuestos son simplemente aquellos que están basados en múltiples columnas. MySQL únicamente usa un índice por tabla cuando está procesando una consulta, esto significa que si tenemos varias columnas que frecuentemente aparecen juntas en una cláusula WHERE, tenemos la oportunidad de acelerar estas consultas al crear un índice compuesto [4]. Si una tabla tiene un índice formado por múltiples columnas, cualquier prefijo más a la izquierda puede ser usado por el optimizador de consultas de MySQL para encontrar las filas. Por ejemplo, si tenemos un índice compuesto por tres columnas (col1, col2, col3), tendríamos capacidades de búsqueda en (col1), (col1, col2) y (col1, col2, col3) [4]. MySQL no puede usar un índice parcial si las columnas no forman un prefijo más a la izquierda del índice. Supongamos que tenemos unas sentencias SELECT:

Si el índice existe en (col1, col2, col3), sólo la primera de estas consultas usará el índice. La segunda y la tercera involucran a las columnas en el índice, pero (col2) y (col2, col3) no son los prefijos más a la izquierda de (col1, col2, col3) [4]. Índices con parte de campos o columnas: Este tipo de índices aligeran las consultas sobre campos de textos [3]. 1) Crear el índice al momento de crear la tabla [3].

Con ALTER TABLE modificamos la tabla [3]. Con ADD INDEX definimos la columna que hará de índice siendo X el número de caracteres a indexar. Podemos definir varias columnas [3]. 2) Crear el índice una vez que ha sido creada la tabla [3]

CREATE TABLE creamos la tabla y definimos los tipos de columna [3]. Con INDEX definimos la columna o columnas que harán de índices siendo X el número de caracteres a indexar [3].

Conclusión:

Referencias Bibliográficas: [1] Maslakowski M., Butcher Tony (2001), Aprendiendo MySQL en 21 días, Prentice Hall [2] Korth, H.; Silbertchatz, A. Fundamentos de Bases de datos. (5ª ed.) Ed. McGraw Hill. Cap. 11 Almacenamiento y Estructura de Archivos, (pp.283-289) [3] Aner Barrena. (junio 24, 2016). MySQL CREATE INDEX: Crear o añadir índices en tablas. septiembre 06, 2021, de ANER_BARENA Sitio web: https://www.anerbarrena.com/mysql-createindex-5281/ [4] Ramos, F. (marzo 16, 2017). Índices y optimización de consultas. septiembre 06, 2021, de Programacion.net Sitio web: https://programacion.net/articulo/indices_y_optimizacion_de_consultas_305/2...