AOWS

Just another adrian’s weblog

Inserción de datos eficiente en MySQL

with 4 comments

De vez en cuando hacemos inserciones que implican cientos (e incluso miles) de filas en un corto espacio de tiempo, por ejemplo al importar datos de una fuente externa. Cuando esto ocurre el rendimiento puede verse muy mermado, y para evitarlo enumero a continuación algunas posibles optimizaciones a tener en cuenta.

Si insertamos los datos de una sola vez:

  1. Si es posible, usar LOAD DATA INFILE. Esto proporciona beneficios en cuanto al rendimiento reduciendo la cantidad de trabajo a realizar por cliente y servidor al preparar los datos para su inserción en la tabla.
  2. Desactivar temporalmente la creación de índices al insertar grandes cantidades de datos. Esto permite la creación de los índices de la tabla de forma más eficiente después de que la información esté ya en la tabla. Para ello utilizaremos la sentencia ALTER TABLE:

    [sql]
    ALTER TABLE tabla DISABLE KEYS;
    # Sentencias de insercion
    ALTER TABLE tabla ENABLE KEYS;
    [/sql]

  3. Para las tablas MyISAM, bloquear la tabla mientras se insertan los datos para prevenir lecturas que puedan enlentecer las escrituras. Tal y como vemos a continuación:

    [sql]
    LOCK TABLES tabla WRITE;
    # Sentencias de inserción
    UNLOCK TABLES;
    [/sql]

  4. En el caso de InnoDB, realizaremos las inserciones dentro de una transacción:

    [sql]
    BEGIN;
    # Sentencias de inserción
    COMMIT;
    [/sql]

  5. Usar INSERTs multifila. Este tipo de sentencias reducen la cantidad de trabajo que tanto cliente como servidor deben hacer para procesar una consulta antes de que los datos sean escritos en la tabla. Así:

    [sql]
    INSERT INTO tabla (a,b,c) VALUES (1,2,3), (4,5,6), (7,8,9);
    [/sql]

  6. Si la tabla en la que insertamos no está vacía, podemos modificar la variable de sistema bulk_insert_buffer_size.

En el caso de que entre las inserciones se produzcan lecturas, podemos llevar a cabo las siguientes optimizaciones:

  1. En MyISAM, activar la opción DELAY_KEY_WRITE. Esto reduce las escrituras a disco que MySQL hace cuando crea nuevas entradas en el índice de la tabla. Se realiza con una sentencia ALTER TABLE:

    [sql]
    ALTER TABLE tabla DELAY_KEY_WRITE = 1;
    [/sql]

    Ojo: si el servidor cae, las tablas que tengan la opción DELAY_KEY_WRITE activada pueden tener índices incompletos. Para asegurarnos de que los índices estén completos, cuando volvamos a lanzar el servidor lo haremos con la coletilla --myisam-recover=BACKUP,FORCE.

  2. Usar InnoDB. Este tipo de tablas manejan mucho mejor que MyISAM las lecturas y escrituras concurrentes.
  3. No escribir desde la tabla de la que se está leyendo. Es mejor crear una tabla adicional para manejar las escrituras y después fusionar las filas desde la tabla de escrituras en la tabla de lectura a intervalos regulares.
Basado en un artículo de MySQL Phrasebook y en Speed of INSERT Statements.

Written by adrian

23 agosto, 2007 a 22:44

Publicado en Posts

Tagged with

4 comentarios

Subscribe to comments with RSS.

  1. Buen día y gracias por tu información, he tenido problemas con una base de datos, me estoy iniciando en todo este relajo del web, he creado un sistema de comentarios simple, uso MySQL, el problema es que al insertar los campos de texto dentro de la tabla no se guardan de forma correcta, si escribo un comentario largo como este, no se guarda de forma completa, solo se guarda una parte, lo que hago es que después de enviar los datos desde un redirecciono automáticamente a una página, no se porque pase esto, en localhost trabaja perfecto, se debe a que el servidor web es muy lento?

    Gerardo

    19 agosto, 2008 at 20:55

  2. Comprueba que el campo sea de tipo text, o si es varchar que tenga la longitud deseada. Con lo que me dices es lo único que se me ocurre.

    adrian

    19 agosto, 2008 at 22:32

  3. Hola a todos.
    Mi pregunta es la siguinte tambien cuento con pagina donde los visitantes pueden dejar sus comentarios, en mysql esta tenia el tipo varchar (255), lamentablemente los datos que ingresaban son largos, por lo cual se cortaba la información, ahora lo tengo con text, se soluciono en cuestion al tamaño. Pero el formato no me lo respeta y ni los caracteres especiales. Hay alguna otra opcion para solucionar esto.
    Gracias.

    Mario C

    25 septiembre, 2008 at 20:36

  4. Prueba a cambiar el charset de la tabla o de ese campo, usa utf8 por ejemplo…

    Salu2

    Sergio

    5 noviembre, 2008 at 22:06


Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: