Optimizando
Consultas SELECT
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:
- Deshabilitar los índices con el uso de la sentencia ALTER TABLE nombreTabla DISABLE KEYS;
- 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.
- 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.
Tutoriales
