Este artículo presenta las estrategias de MySQL para mejorar la eficiencia de las operaciones de carga de datos. Muchas veces le preocupa optimizar las consultas SELECT porque son las consultas más utilizadas y determinar cómo optimizarlas no siempre es sencillo. Cargar datos en una base de datos es relativamente sencillo. Muchas veces le preocupa optimizar las consultas SELECT porque son las consultas más utilizadas y determinar cómo optimizarlas no siempre es sencillo. Cargar datos en una base de datos es relativamente sencillo. Sin embargo, existen estrategias que se pueden utilizar para mejorar la eficiencia de las operaciones de carga de datos, cuyos principios básicos son los siguientes:
La carga masiva es más rápida que la carga de una sola fila porque no es necesario vaciar la caché de índice después de cargar cada registro; se puede vaciar después de cargar el lote de registros;
Cargar una tabla sin índice es más rápido que cargar después de un índice. Si hay índices, no sólo se deben agregar registros a los archivos de datos, sino que cada índice debe modificarse para reflejar la adición de nuevos registros.
Las sentencias SQL más cortas son más rápidas que las sentencias SQL más largas porque implican menos análisis en el lado del servidor y porque son más rápidas de enviar a través de la red desde el cliente al servidor. Algunos de estos factores pueden parecer triviales (especialmente el último), pero si carga una gran cantidad de datos, incluso los factores pequeños pueden marcar una gran diferencia en los resultados. Podemos utilizar los principios generales anteriores para derivar varias conclusiones prácticas sobre cómo cargar datos más rápido:
LOAD DATA (en todas sus formas) es más eficiente que INSERT porque carga filas en lotes. Las actualizaciones del índice son menores y el servidor solo tiene que analizar e interpretar una declaración en lugar de varias.
LOAD DATA es más eficiente que LOAD DATA LOCAL. Con LOAD DATA, el archivo debe estar ubicado en el servidor y debe tener permisos de ARCHIVO, pero el servidor puede leer el archivo directamente desde el disco. Con LOAD DATA LOCAL, el cliente lee el archivo y lo envía a través de la red al servidor, lo cual es lento.
Si debe usar INSERT, debe usar un formulario que permita especificar varias filas en una sola declaración, como por ejemplo:
Cuantas más filas puedas especificar en una declaración, mejor. Esto reduce la cantidad de declaraciones necesarias y reduce la cantidad de actualizaciones del índice. Si usa mysqldump para generar un archivo de respaldo de base de datos, debe usar la opción --extended-insert para que el archivo de volcado contenga declaraciones INSERT de varias líneas. También puede usar --opt (optimización), que habilita la opción --extended-insert. Por el contrario, se debe evitar el uso de la opción --complete-insert en mysqldump; esta opción hace que las declaraciones INSERT sean de una sola línea, tarden más en ejecutarse y requieran más análisis que las declaraciones generadas sin la opción --complete-insert.
Utilice protocolos cliente/servidor comprimidos para reducir el tráfico de datos de la red. Para la mayoría de los clientes MySQL, esto se puede especificar con la opción de línea de comando --compress. Generalmente sólo se usa en redes más lentas porque la compresión requiere mucho tiempo de procesador.
Deje que MySQL inserte valores predeterminados; no especifique columnas en la declaración INSERT a las que se les asignarán valores predeterminados de ninguna manera. En promedio, esto da como resultado declaraciones más cortas y reduce la cantidad de caracteres enviados a través de la red al servidor. Además, las declaraciones que contienen menos valores requieren menos análisis y transformación por parte del servidor.
Si la tabla está indexada, puede utilizar inserciones masivas (LOAD DATA o instrucciones INSERT de varias filas) para reducir la sobrecarga del índice. Esto minimiza el impacto de las actualizaciones del índice porque el índice solo necesita actualizarse cuando se han procesado todas las filas, en lugar de después de cada fila.
Si necesita cargar una gran cantidad de datos en una nueva tabla, debe crear la tabla y cargarla cuando no esté indexada, y luego crear el índice después de cargar los datos. Esto es más rápido. Crear el índice una vez (en lugar de modificarlo una vez por fila) es más rápido.
Si elimina o desactiva un índice antes de cargarlo, volver a crearlo o habilitarlo después de cargar los datos puede acelerar la carga. Si desea utilizar una estrategia de eliminación o deshabilitación para la carga de datos, asegúrese de hacer algunos experimentos para ver si vale la pena (si está cargando una pequeña cantidad de datos en una tabla grande, la reconstrucción y la indexación pueden tardar más que cargar los datos) ).
DROP INDEX y CREATE INDEX se pueden utilizar para eliminar y reconstruir índices. Una alternativa es deshabilitar y habilitar índices usando myisamchk o isamchk. Esto requiere una cuenta en el servidor MySQL con acceso de escritura a los archivos de la tabla. Para deshabilitar los índices de las tablas, ingrese el directorio de la base de datos correspondiente y ejecute uno de los siguientes comandos:
Utilice myisamchk para tablas MyISAM con archivos de índice con extensión .MYI e isamchk para tablas ISAM con archivos de índice con extensión .ISM. Después de cargar datos en la tabla, active el índice de la siguiente manera:
Si decide utilizar la activación y desactivación del índice, debe utilizar el protocolo de bloqueo de reparación de tabla descrito en el Capítulo 13 para evitar que el servidor cambie los bloqueos al mismo tiempo (aunque la tabla no se repara en este momento, se modifica como una tabla proceso de reparación, por lo que debe utilizar el mismo protocolo de bloqueo).
Los principios de carga de datos descritos anteriormente también se aplican a consultas fijas relacionadas con clientes que necesitan realizar diferentes operaciones. Por ejemplo, generalmente desea evitar ejecutar consultas SELECT largas en tablas actualizadas con frecuencia. Las consultas SELECT de larga duración pueden generar mucha discordia y reducir el rendimiento del escritor. Una posible solución es almacenar primero los registros en una tabla temporal y luego agregar periódicamente los registros a la tabla principal si las escrituras son principalmente operaciones INSERT. Este no es un enfoque factible si se requiere acceso inmediato a nuevos registros. Pero este método se puede utilizar siempre que no se acceda a ellos durante un corto período de tiempo. Hay dos beneficios al usar tablas temporales. Primero, reduce la contención con la instrucción de consulta SELECT en la tabla principal y, por lo tanto, se ejecuta más rápido. En segundo lugar, el tiempo total para cargar registros de la tabla temporal en la tabla principal es menor que el tiempo total para cargar los registros por separado solo necesita actualizarse al final de cada carga por lotes, en lugar de después de cada fila; carga. Una aplicación de esta estrategia es acceder a la base de datos MySQL desde la página web del servidor web. En este escenario, es posible que no exista un nivel superior de autoridad que garantice la entrada inmediata del registro en la tabla principal.
Si los datos no son exactamente el tipo de registro único que se insertaría en caso de un apagado del sistema, otra estrategia para reducir las actualizaciones del índice es usar la opción de creación de tablas DELAYED_KEY_WRITE para tablas MyISAM (lo cual puede ser posible si se usa MySQL para algunos trabajos de entrada de datos). Esta opción hace que la caché del índice se actualice sólo ocasionalmente, en lugar de después de cada inserción.
Si desea aprovechar la actualización retrasada del índice en todo el servidor, simplemente inicie mysqld con la opción --delayed-key-write. En este escenario, las escrituras del bloque de índice se retrasan hasta que el bloque debe vaciarse para dejar espacio para otros valores de índice, hasta que se ejecuta un comando de descarga de tablas o hasta que se cierra la tabla de índice.
-