Hosting, registro y alojamiento web, dominios y servidores
Tu servidor de alojamiento web, servidores y registro de dominios (acceso a página principal)
Favoritos | Recomiéndanos | Enlázate | Contacto | Buscador   
Acerca de Dimensis | Informaciones legales   
··································   
[ Acceso a la Página Principal ]   
 
  BLOG Consultas Soporte Programación Utilidades Artículos Mapa web Área privada  


Soporte técnico sobre alojamiento web y servidores de Dimensis

Información para administrar tus recursos, resolver dudas, contactar con nuestro soporte y estar siempre al día



Artículos publicados en Univers Dimensis

· Artículos por temas
· Definiciones
· Archivo de artículos
· Ranking (top)
· Sindicación (RSS)

· Artículos en catalán



  
MySql: Índices y optimización de consultas

Hacer que una consulta trabaje es una cosa, pero obtener una consulta que trabaje lo más rápidamente es otra muy diferente. Podemos acelerar nuestras consultas de dos maneras básicamente, una de ellas es afinando nuestro servidor para que responda lo mejor posible, y la otra, que es la que trataremos en este artículo, es haciendo uso de los índices de una manera inteligente. 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 unos 1000 registros, es por lo menos 100 veces más rápido leer los datos usando un índice, que haciendo una lectura secuencial. 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, como se acaba de mencionar, cuando en una tabla no existen índices en los cuales pueda auxiliarse MySQL para resolver una consulta se tendrán que leer todos los registros de la tabla de manera secuencial. Esto es comúnmente llamado un "escaneo completo de una tabla", y es muchas veces algo que se debe evitar. En particular, debemos evitar las escaneos completos de tablas por las siguientes razones:

  • 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. Existe una relación proporcional entre el número de registros que tiene una tabla y la cantidad de tiempo que le toma a MySQL revisarla completamente.
  • 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.
  • Sobrecarga de disco. En una tabla muy grande, un escaneo completo consume una gran cantidad de entrada/salida en el disco. Esto puede alentar siginificativamente nuestro servidor de bases de datos, especialmente si tenemos un disco IDE algo antiguo.
En resumen, lo mejor es tratar de que los escaneos completos de tablas sean mínimos -- especialmente si nuestra aplicación necesita escalabilidad en tamaño, número de usuarios, o ambos. Las versiones actuales de MySQL hacen distintas mejoras en cuanto a concurrencia, pero ese tema está más allá de nuestra discusión. Es en estos casos donde la indexación puede ayudarnos. De manera simple, un índice le permite a MySQL determinar si un valor dado coincide con cualquier fila en una tabla. Cuando indexamos una columna en particular, MySQL crea otra estructura de datos (un índice) que usa para almacenar información extra acerca de los valores en la columna indexada. Los valores indexados son llamados frecuentemente claves. Aunque esta es la manera simple de explicar los índices, realmente es un poco más complejo, ya que MySQL almacena todas las claves del índice en una estructura de datos de árbol. Esta estructura de datos de árbol le permite a MySQL encontrar claves muy rápidamente. Cuando MySQL encuentre que hay un índice en una columna, lo usará en vez de hacer un escaneo completo de la tabla. Esto reduce de manera imporante los tiempos de CPU y las operaciones de entrada/salida en disco, a su vez que se mejora la concurrencia porque MySQL bloqueará la tabla únicamente para obtener las filas que necesite (en base a lo que encontró en el índice). Cuando tenemos grandes cantidades de datos en nuestras tablas, la mejora en la obtención de los datos puede ser muy significativa.


Creación de índices

Existen cuatro tipos de índices que podemos utilizar en MySQL; de clave primaria, únicos, de texto completo, y ordinarios. Cada uno de ellos será explicado a continuación.

Índices de clave primaria

Una clave primaria 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.

CREATE TABLE nombreTabla(campo1 tipoDato,
 [campo2...,] PRIMARY KEY (campo1 [,campo2...]) );
Hacemos énfasis en que la palabra clave NOT NULL es obligatoria para un campo cuando éste vaya a formar parte de una clave primaria; como mencionamos anteriormente, las claves primarias no pueden contener valores nulos. Si intentamos crear una clave primaria sobre un campo nulo, MySQL nos marcará un error.

2. Crear una clave primaria en una tabla existente con el uso del comando ALTER TABLE:

ALTER TABLE nombreTabla ADD PRIMARY KEY(campo1 [,campo2...]);
Por ejemplo, suponiendo que ya tenemos en nuestro sistema una tabla que fue creada de la siguiente manera (sin clave primaria, y con el campo id aceptando valores NUL):
CREATE TABLE usuarios(id int, nombre varchar(50), apellidos varchar(70));
Podemos crear una clave primaria sobre el campo id con esta sentencia:
ALTER TABLE usuarios MODIFY id INT NOT NULL, ADD PRIMARY KEY(id);
Para observar los cambios que hemos hecho, podemos examinar las columnas de la tabla usuarios con una sentencia DESCRIBE:
mysql> DESCRIBE usuarios;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id        | int(11)     |      | PRI | 0       |       |
| nombre    | varchar(50) | YES  |     | NULL    |       |
| apellidos | varchar(70) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)


El campo id no tiene un valor YES en la columna Null, lo que indica que este campo ya no podrá almacenar valores nulos. Se puede observar también que se tiene un valor PRI en la columna Key, lo que indica que este campo es una clave primaria.

Las claves primarias pueden constar de más de un campo. Hay algunas veces en las que un solo campo no puede identificar de manera única a un registro.

Índices ordinarios

Un índice que no es primario permite valores duplicados (a menos que los campos hayan sido especificados como UNIQUE).

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.

CREATE TABLE nombreTabla(campo1 tipoDato, campo2 tipoDato,..
  INDEX [nombreIndice] (campo1 [,campo2...]));
2. De igual manera, podemos crear el índice con el uso de la sentencia ALTER TABLE si es que la tabla ya existe.
ALTER TABLE nombreTabla ADD INDEX [nombreIndice] (campo1 [,campo2...]);
También es posible usar la sentencia CREATE INDEX para crear un índice en una tabla existente.
CREATE INDEX nombreIndice ON nombreTabla(campo1 [,campo2...]);


Ambas sentencias piden el nombre del índice, sin embargo con la sentencia CREATE INDEX el nombre es obligatorio.

Por ejemplo, para la siguiente definición de tabla:

CREATE TABLE usuarios(id int, nombre varchar(50), apellidos varchar(70));
Se puede crear un índice en la columna apellidos con una sentencia ALTER TABLE:
ALTER TABLE usuarios ADD INDEX idx_apellidos (apellidos);
O bien, con una sentencia CREATE INDEX:
CREATE INDEX idx_apellidos ON usuarios(apellidos);


Siguiente página (2/5) Siguiente página


  

· DESTACADOS ·

Máquinas dedicadas y servidores virtuales
NAS para almacenamiento remoto accesible mediante NFS


· DESCUENTOS ·

Ofertas de alojamiento web y servidores
Allotjament de pàgines web en català



.:Webs destacadas:.

· el blog de DIMENSIS
· Cambio climático
· Productes del Camp


Productes del Camp
Especial Productes del Camp

Xarxa ECO



HacklabValls



1999-2011 Dimensis Global Communications (Los contenidos pueden compartirse bajo licencia Copyleft)
Alojamiento web, servidores virtuales, servidores dedicados y registro de dominios.
Optimizado para resolución de 800x600 y 1024x768. Requiere activación de cookies, Java y Flash.
WebSite realizado con PHP-Nuke, bajo licencia GNU/GPL. (Tiempo de carga: 0.005 segundos)