Índices y optimización de consultas

En general, cuando deseamos hacer que una consulta SELECT ... WHERE se ejecute m�s r�pido, lo primero que debemos checar es si podemos agregar un �ndice. Todas las referencias entre tablas diferentes deben usualmente ser hechas con �ndices. Por supuesto, debemos usar una sentencia EXPLAIN para determinar cu�les �ndices est�n siendo usados para resolver la consulta.

�Sentencias INSERT

El tiempo que le toma a MySQL insertar un registro est� determinado por los siguientes factores, donde los n�meros indican �nicamente valores aproximados:

• Establecer la conexi�n: (3)
• Enviar la consulta al servidor: (2)
• Analizar la consulta: (2)
• Insertar el registro: (1 x tama�o del registro)
• Insertar �ndices: (1 x n�mero de �ndices)
• Cerrar: (1)

Esto no toma en consideraci�n la sobrecarga inicial de abrir las tablas, lo cual es hecho una vez por cada consulta en ejecuci�n de manera concurrente.

El tama�o de la tabla hace m�s lenta la inserci�n de los �ndices por log N, asumiendo que los �ndices son �rboles B.

Por lo tanto, podemos usar los siguientes m�todos para lograr que los INSERTs se ejecuten m�s r�pido:

• Si estamos insertando muchas filas desde el mismo cliente al mismo tiempo, usemos una sentencia INSERT con m�ltiples listas de valores para insertar varias filas a la vez. Esto es mucho m�s r�pido que usar varias sentencias INSERT de manera separada. Por ejemplo, la siguiente consulta:

  INSERT INTO nombreTabla VALUES(registro1),(registro2),... (registroN);

  es mucho m�s r�pida que esta alternativa:

  INSERT INTO nombreTabla VALUES(registro1);
  INSERT INTO nombreTabla VALUES(registro2);
  ...
  INSERT INTO nombreTabla VALUES(registroN);

• Si estamos insertando una gran cantidad de filas desde diferentes clientes, podemos obtener una mayor velocidad al usar la sentencia INSERT DELAYED.
• Cuando estemos cargando datos en una tabla a partir de un archivo de texto, lo mejor es usar la sentencia LOAD DATA INFILE. Esto es usualmente 20 veces m�s r�pido que usar una gran cantidad de sentencias INSERT.
• Es posible hacer que LOAD DATA INFILE se ejecute a�n m�s r�pido cuando la tabla tiene muchos �ndices. El procedimiento a seguir es:
  1. Deshabilitar los �ndices con el uso de la sentencia ALTER TABLE nombreTabla DISABLE KEYS;
  2. Insertar los datos en la tabla con LOAD DATA INFILE. En este momento no se actualizar� ning�n �ndice y por lo tanto ser� muy r�pido cargar los datos.
  3. Iniciar la creaci�n de los �ndices necesarios con el uso de ALTER TABLE nombreTabla ENABLE KEYS. Esto crear� los �ndices en memoria antes de escribirlos en disco, lo cual es mucho m�s r�pido ya que se evita una gran cantidad de lecturas y escrituras en disco. El �rbol B del �ndice es tambi�n perfectamente balanceado.
• Es un hecho que no siempre estamos en la posibilidad de insertar los datos a partir de un archivo de texto, sin embargo, podemos acelerar las operaciones INSERT que son hechas con m�ltiples sentencias al bloquear nuestras tablas:

  LOCK TABLES nombreTabla WRITE;
  INSERT INTO nombreTabla VALUES(registro1),(registro2),(registro3);
  INSERT INTO nombreTabla VALUES(registro4),(registro5),(registro6);
  ...
  INSERT INTO nombreTabla VALUES(registroN);
  UNLOCK TABLES;

  Un beneficio de eficiencia ocurre ya que el b�ffer del �ndice es escrito a disco s�lo una vez, despu�s de que todas las sentencias INSERT han sido completadas. Normalmente hay tantas escrituras del b�ffer de un �ndice a disco como diferentes sentencias INSERT son ejecutadas.

  El uso explicito de las sentencias de bloqueo (LOCK) no son necesarias si se pueden insertar todos las filas con una sola sentencia INSERT. Para las tablas transaccionales, debemos usar BEGIN/COMMIT en vez de LOCK TABLE para obtener el mismo resultado.

  El bloqueo tambi�n reduce el tiempo total en casos de conexiones m�ltiples, a pesar de que el tiempo de espera m�ximo para conexiones individuales puede incrementarse ya que se tiene que esperar para al desbloqueo. Por ejemplo:

  • La conexi�n 1 hace 1000 inserciones.
  • La conexi�n 2, 3 y 4 hacen una inserci�n.
  • La conexi�n 5 hace 1000 inserciones.

  Si no estamos usando bloqueo, las conexiones 2,3 y 4 terminar�n antes que la 1 y la 5. Usando bloqueo, las conexiones 2,3, y 4 probablemente no terminar�n antes de la 1 y la 5, pero el tiempo total debe ser apr�ximadamente de 40% m�s r�pido.

  Las sentencias INSERT, UPDATE y DELETE son muy r�pidas en MySQL, pero obtendremos una mejor eficiencia al agregar bloqueo cuando se ejecuten en promedio m�s de 5 inserciones o actualizaciones en una fila.

�Sentencias UPDATE

Las sentencias UPDATE son optimizadas de manera similar a las sentencias SELECT con la sobrecarga adicional de la escritura. Por ejemplo, para efectos de optimizaci�n, el siguiente c�digo:

UPDATE nombreCampo FROM nombreTabla WHERE algunaCondicion

Es el mismo que este:

SELECT nombreCampo FROM nombreTabla WHERE algunaCondicion

Es decir, podemos optimizar una sentencia UPDATE de la misma forma que su equivalente sentencia SELECT.

La velocidad de escritura depende de la cantidad de datos que est�n siendo actualizados y el n�mero de �ndices que son actualizados, por lo tanto debemos tener cuidado de crear �ndices que no sean verdaderamente �tiles, o bien, hacer que los campos de la tabla sean m�s grandes de lo que realmente necesitamos.

Tambi�n, otra forma de obtener actualizaciones r�pidas es retrasar los UPDATEs y entonces hacer muchas actualizaciones en una fila posteriormente. Hacer muchas actualizaciones en una fila es mucho m�s r�pido que hacer uno a la vez si se bloquea la tablas.

Debemos notar que para una tabla MyISAM que usa el formato de registro din�mico, el actualizar el registro a una longitud total m�s grande puede dividir el registro. Si esto llega a ocurrir, es muy importante usar el comando OPTIMIZE TABLE ocasionalmente.

�Sentencias DELETE

El tiempo de eliminar registros individuales es exactamente proporcional al n�mero de �ndices. Cuando eliminamos, cada registro necesita ser eliminado desde cualquier �ndice asociado, as� como tambi�n del archivo principal de datos.

Si deseamos eliminar todos los registros de una fila, es preferible usar el comando TRUNCATE TABLE en lugar de ejecutar la tradicional sentencia DELETE, ya que se borra toda la tabla en una s�lo operaci�n, sin la necesidad de eliminar cada �ndice y cada registro de manera individual.

Para obtener mayor velocidad en las tablas MyISAM, tanto para las sentencias INSERT, como para las sentencias DELETE, podemos hacer m�s grande la cach� de claves al incrementar la variable de sistema key_buffer_size.