Hosting guebs

Capítulo 14. Motores de almacenamiento de MySQL y tipos de tablas

Tabla de contenidos

14.1. El motor de almacenamiento MyISAM
14.1.1. Opciones de arranque de MyISAM
14.1.2. Cuánto espacio necesitan las claves
14.1.3. Formatos de almacenamiento de tablas MyISAM
14.1.4. Problemas en tablas MyISAM
14.2. El motor de almacenamiento MERGE
14.2.1. Problemas con tablas MERGE
14.3. El motor de almacenamiento MEMORY (HEAP)
14.4. El motor de almacenamiento BDB (BerkeleyDB)
14.4.1. Sistemas operativos que soporta BDB
14.4.2. Instalación de BDB
14.4.3. Opciones de arranque de BDB
14.4.4. Características de las tablas BDB
14.4.5. Temas pendientes de arreglo para BDB
14.4.6. Limitaciones en las tablas BDB
14.4.7. Errores que pueden darse en el uso de tablas BDB
14.5. El motor de almacenamiento EXAMPLE
14.6. El motor de almacenamiento FEDERATED
14.6.1. Instalación del motor de almacenamiento FEDERATED
14.6.2. Descripción del motor de almacenamiento FEDERATED
14.6.3. Cómo usar las tablas FEDERATED
14.6.4. Limitaciones del motor de almacenamiento FEDERATED
14.7. El motor de almacenamiento ARCHIVE
14.8. El motor de almacenamiento CSV

MySQL soporta varios motores de almacenamiento que tratan con distintos tipos de tabla. Los motores de almacenamiento de MySQL incluyen algunos que tratan con tablas transaccionales y otros que no lo hacen:

Este capítulo describe cada uno de los motores de almacenamiento MySQL excepto InnoDB y NDB Cluster, que se tratan en Capítulo 15, El motor de almacenamiento InnoDB y Capítulo 16, MySQL Cluster.

Cuando crea una nueva tabla, puede decirle a MySQL qué tipo de tabla crear añadiendo la opción de tabla ENGINE o TYPE al comando CREATE TABLE :

CREATE TABLE t (i INT) ENGINE = INNODB;
CREATE TABLE t (i INT) TYPE = MEMORY;

Aunque se soporta TYPE en MySQL 5.0, ENGINE es el término preferido.

Si omite la opción ENGINE o TYPE, se usa el motor de almacenamiento por defecto, que es MyISAM. Puede cambiarlo usando las opciones de arranque --default-storage-engine o --default-table-type , o cambiando la variable de sistema storage_engine o table_type .

Cuando se instala MySQL en Windows usando el MySQL Configuration Wizard, InnoDB es el motor de almacenamiento por defecto en lugar de MyISAM. Consulte Sección 2.3.5.1, “Introducción”.

Para convertir una tabla de un tipo a otro, use un comando ALTER TABLE que indique el nuevo tipo:

ALTER TABLE t ENGINE = MYISAM;
ALTER TABLE t TYPE = BDB;

Consulte Sección 13.1.5, “Sintaxis de CREATE TABLE y Sección 13.1.2, “Sintaxis de ALTER TABLE.

Si trata de usar un motor de almacenamiento que no está compilado o que está desactivado, MySQL crea una tabla de tipo MyISAM. Este comportamiento es conveniente cuando quiere copiar tablas entre servidores MySQL que soportan distintos motores. (Por ejemplo, en una inicialización de replicación, tal vez su maestro suporte un motor de almacenamiento transaccional para más seguridad, pero los esclavos usan un motor de almacenamiento no transaccional para mayor velocidad.)

La sustitución automática del tipo MyISAM cuando se especifica un tipo no especificado puede ser confuso para nuevos usuarios. En MySQL 5.0, se genera una advertencia cuando se cambia un tipo de tabla automáticamente.

MySQL siempre crea un fichero .frm para guardar la definición de tabla y columnas. El índice y datos de la tabla puede estar almacenado en uno o más ficheros, en función del tipo de tabla. El sevidor crea el fichero .frm por encima del nivel de almacenamiento del motor. Los motores de almacenamiento individuales crean los ficheros adicionales necesarios para las tablas que administran.

Una base de datos puede contener tablas de distintos tipos.

Las tablas transaccionales (TSTs) tienen varias ventajas sobre las no transaccionales (NTSTs):

En MySQL 5.0, InnoDB usa valores de configuración por defecto si no los especifica. Consulte Sección 15.3, “Configuración de InnoDB.

Tablas no transaccionales tienen varias ventajas al no tener una sobrecarga transaccional:

Puede combinar tablas transaccionales y no transaccionales en el mismo comando para obtener lo mejor de ambos mundos. Sin embargo, en una transaccion con autocommit desactivado, los cambios de tablas no transaccionales son permanentes inmediatamente y no pueden deshacerse.

14.1. El motor de almacenamiento MyISAM

MyISAM es el motor de almacenamiento por defecto. Se basa en el código ISAM pero tiene muchas extensiones útiles. (Tenga en cuenta que MySQL 5.0 no soporta ISAM.)

Cada tabla MyISAM se almacena en disco en tres ficheros. Los ficheros tienen nombres que comienzan con el nombre de tabla y tienen una extensión para indicar el tipo de fichero. Un fichero .frm almacena la definición de tabla. El fichero de datos tiene una extensión .MYD (MYData) . El fichero índice tiene una extensión .MYI (MYIndex) .

Para especificar explícitamente que quiere una tabla MyISAM, indíquelo con una opción ENGINE:

CREATE TABLE t (i INT) ENGINE = MYISAM;

(Nota: Antiguas versiones de MySQL usaban TYPE en lugar de ENGINE (por ejemplo: TYPE = MYISAM). MySQL 5.0 soporta esta sintaxis para compatibilidad con versiones anteriores pero TYPE está obsoleto y ahora se usa ENGINE .)

Normalmente, la opción ENGINE no es necesaria; MyISAM es el motor de almacenamiento por defecto a no ser que se cambie.

Puede chequear o reparar tablas MyISAM con la utilidad myisamchk . Consulte Sección 5.8.3.7, “Usar myisamchk para recuperación de desastres”. Puede comprimir tablas MyISAM con myisampack para que ocupen mucho menos espacio. Consulte Sección 8.2, “myisampack, el generador de tablas comprimidas de sólo lectura de MySQL”.

Las siguientes son algunas características del motor de almacenamiento MyISAM :

  • Todos los datos se almacenan con el byte menor primero. Esto hace que sean independientes de la máquina y el sistema operativo. El único requerimiento para portabilidad binaria es que la máquina use enteros con signo en complemento a dos (como todas las máquinas en los últimos 20 años) y formato en coma flotante IEEE (también dominante en todas las máquinas). La única área de máquinas que pueden no soportar compatibilidad binaria son sistemas empotrados, que a veces tienen procesadores peculiares.

    No hay penalización de velocidad al almacenar el byte menor primero; los bytes en un registro de tabla normalmente no están alineados y no es un problema leer un byte no alineado en orden normal o inverso. Además, el código en el servidor que escoge los valroes de las columnas no es crítico respecto a otro código en cuanto a velocidad.

  • Ficheros grandes (hasta longitud de 63 bits) se soportan en sistemas de ficheros y sistemas operativos que soportan ficheros grandes.

  • Registros de tamaño dinámico se fragmentan mucho menos cuando se mezclan borrados con actualizaciones e inserciones. Esto se hace combinando automáticamente bloques borrados adyacentes y extendiendo bloques si el siguiente bloque se borra.

  • El máximo número de índices por tabla MyISAM en MySQL 5.0 es 64. Esto puede cambiarse recompliando. El máximo número de columnas por índice es 16.

  • La longitud máxima de clave es 1000 bytes. Esto puede cambiarse recompilando. En caso de clave mayor a 250 bytes, se usa un tamaño de bloque mayor, de 1024 bytes.

  • Las columnas BLOB y TEXT pueden indexarse.

  • Valores NULL se permiten en columnas indexadas. Esto ocupa 0-1 bytes por clave.

  • Todos los valores de clave numérico se almacenan con el byte mayor primero para mejor compresión de índice.

  • Cuando se insertan registros en orden (como al usar columnas AUTO_INCREMENT ), el árbol índice se divide de forma que el nodo mayor sólo contenga una clave. Esto mejora la utilización de espacio en el árbol índice.

  • El tratamiento interno de una columna AUTO_INCREMENT por tabla. MyISAM actualiza automáticamente esta colunmna para operaciones INSERT y UPDATE . Esto hace las columnas AUTO_INCREMENT más rápidas (al menos 10%). Los valores iniciales de la secuencia no se reúsan tras ser borrados. (Cuando una columna AUTO_INCREMENT se define como la última columna de un índice de varias columnas, se reúsan los valores borrados iniciales de la secuencia.) El valor AUTO_INCREMENT puede cambiarse con ALTER TABLE o myisamchk.

  • Si una tabla no tiene bloques libres en medio del fichero de datos, puede INSERT nuevos registros a la vez que otros flujos leen de la tabla. (Esto se conoce como inserciones concurrentes.) Un bloque libre puede ser resultado de borrar o actualziar registros de longitud dinámica con más datos que su contenido. Cuando todos los bloques libres se usan (se rellenan), las inserciones futuras vuelven a ser concurrentes.

  • Puede tener el fichero de datos e índice en directorios distintos para obtener más velocidad con las opciones DATA DIRECTORY y INDEX DIRECTORY para CREATE TABLE. Consulte Sección 13.1.5, “Sintaxis de CREATE TABLE.

  • Cada columna de carácteres puede tener distintos conjuntos de carácteres. Consulte Capítulo 10, Soporte de conjuntos de caracteres.

  • Hay un flag en el fichero índice MyISAM que indica si la tabla se ha cerrado correctamente. Si mysqld se arranca con la opción --myisam-recover , Las tablas MyISAM se chequean automáticamente al abrirse, y se reparan si la tabla no se cierra correctamente.

  • myisamchk marca las tablas como chequeadas si se ejecuta con la opción --update-state . myisamchk --fast cheque sólo las tablas que no tienen esta marca.

  • myisamchk --analyze almacena estadísticas para partes de las claves, así como para las claves enteras.

  • myisampack puede comprimir columnas BLOB y VARCHAR .

MyISAM soporta las siguientes características:

  • Soporte de un tipo VARCHAR auténtico; una columna VARCHAR comienza con la longitud almacenada en dos bytes.

  • Tablas con VARCHAR pueden tener longitud de registro fija o dinámica.

  • VARCHAR y CHAR pueden ser de hasta 64KB.

  • Un índice hash puede usarse para UNIQUE. Esto le permite tener UNIQUE o cualquier combinación de columnas en una tabla . (Sin embargo, no puede buscar en un índice UNIQUE .)

14.1.1. Opciones de arranque de MyISAM

Las siguientes opciones de mysqld pueden usarse para cambiar el comportamiento de tablas MyISAM :

  • --myisam-recover=mode

    Cambia el modo para recuperación automática para tablas MyISAM .

  • --delay-key-write=ALL

    No vuelca buffers de clave entre escrituras para cualquier tabla MyISAM .

    Nota: Si hace esto, no debe usar tablas MyISAM desde otro programa (como otro servidor MySQL server o con myisamchk) cuando la tabla está en uso. Hacerlo provoca corrupción de índice.

    Usar --external-locking no ayuda para tablas que usan --delay-key-write.

Consulte Sección 5.3.1, “Opciones del comando mysqld.

Las siguientes variables de sistema afectan al comportamiento de tablas MyISAM :

  • bulk_insert_buffer_size

    Tamaño del árbol de caché usado en optimización de inserciones. Nota: Este es el límite por flujo!

  • myisam_max_extra_sort_file_size

    Usado para ayudar a MySQL a decidir cuándo usar el método de creación de índice de clave caché lento pero seguro Nota: Este parámetro se daba en bytes antes de MySQL 5.0.6, cuando se eliminó.

  • myisam_max_sort_file_size

    No usa el método de ordenación de índice rápido para crear un índice si el fichero temporal será más grande a este tamaño. Nota: En MySQL 5.0, este parámetro se da en bytes.

  • myisam_sort_buffer_size

    Cambia el tamaño del búffer usado al recuperar tablas.

Consulte Sección 5.3.3, “Variables de sistema del servidor”.

La recuperación automática se activa si arranca mysqld con la opción --myisam-recover . En ese caso, cuando el servidor abre una tabla MyISAM , chequea si la tabla está marcada como mal cerrada o si el contador de veces que se ha abierto la tabla no es 0 y está ejecuando el servidor con --skip-external-locking. Si alguna de estas condiciones es cierta, ocurre lo siguiente:

  • Se chequea la tabla para errores.

  • Si el servidor encuentra un error, trata de hacer una reparación de tabla rápida (ordenando y sin recrear el fichero de datos).

  • Si falla la reparación debido a un error en el fichero de datos (por ejemplo, error de clave duplicada), el servidor lo intenta otra vez, esta vez recreando el fichero de datos.

  • Si sigue fallando, el servidor trata una vez más con el método de reparación antiguo (escrito registro a registro sin ordenar). Este método debe ser capaz de reparar cualquier clase de error y tiene requerimientos de espacio bajos.

Si la recuperación no fuera capaz de recuperar todos los registros de un comando préviamente completado y no ha especificado FORCE en la opción --myisam-recover , la recuperación automática aborta con un mensaje de error en el log de errores:

Error: Couldn't repair table: test.g00pages

Si especifica FORCE, se escribe una advertencia como esta:

Warning: Found 344 of 354 rows when repairing ./test/g00pages

Tenga en cuenta qui el valor de recuperación automático incluye BACKUP, el proceso de recuperación crea ficheros con nombres de la forma tbl_name-datetime.BAK. Debe tener un script cron que mueva estos ficheros automáticamente del directorio de base de datos al dispositivo de copia de seguridad.

14.1.2. Cuánto espacio necesitan las claves

Tablas MyISAM usan índices B-tree. Puede calcular el tamaño del fichero índice mediante (key_length+4)/0.67, sumado sobre todas las claves. Este es el peor caso en que todas las claves se insertan en orden ordenado y la tabla no tienen ninguna clave comprimida.

Los índices de cadenas de carácteres están comprimidos en espacio. Si la primera parte del índice es una cadena de carácteres, también tiene el prefijo comprimido. La compresión de espacio hace que el fichero índice sea menor que el peor caso si la columna de la cadena de carácteres tiene muchos espacios finales o es una columna VARCHAR que no se usa siempre con la longitud total. La compresión de prefijo se usa en claves que comienzan con una cadena de carácteres. La compresión de prefijo ayuda si hay muchas cadenas de carácteres con un prefijo idéntico.

En tablas MyISAM puede comprimir números prefijo especificando PACK_KEYS=1 cuando crea la tabla. Esto ayuda cuando tiene muchas claves enteras con un prefijo idéntico cuando los números se almacenan con el byte mayor primero.

14.1.3. Formatos de almacenamiento de tablas MyISAM

MyISAM soporta tres formatos de almacenamiento distintos. Dos de ellos (formato fijo y dinámico) se eligen automáticamente en función del tipo de columnas que esté usando. El tercero, formato comprimido, puede ser creado sólo con la utilidad myisampack .

Cuando CREATE o ALTER una tabla sin columnas BLOB o TEXT , puede forzar el formato de tabla a FIXED o DYNAMIC con la optión de tabla ROW_FORMAT . Esto hace que las columnas CHAR y VARCHAR sean CHAR para formato FIXED, o VARCHAR para formato DYNAMIC .

Puede comprimir o descomprimir tablas especificando ROW_FORMAT={COMPRESSED | DEFAULT} con ALTER TABLE. Consulte Sección 13.1.5, “Sintaxis de CREATE TABLE.

14.1.3.1. Características de tablas estáticas (con ancho fijo o Fixed-Length)

El formato por defecto para MyISAM es el estático. Se usa cuando la tabla no contiene columnas de longitud variable (VARCHAR, BLOB, o TEXT). Cada registro se almacena usando un número de bytes fijo.

De los tres formatos de almacenamiento MyISAM, el formato estático es el más simple y seguro (menos sujeto a corrupción). También es el más rápido de los formatos sobre disco. La velocidad proviene de la facilidad con que se encuentran los registros en el fichero de datos en disco: Cuando se busca un registro basándose en un número de registro en el índice, multiplica el número de registro por la longitud de registro. También , al escanear una tabla, es muy fácil leer un número constante de registro con cada operación de lectura de disco.

La seguridad se evidencia si su máquina falla mientras el servidor MySQL está escribiendo en un fichero MyISAM de formato fijo. En este caso, myisamchk puede determinar fácilmente dónde comienza cada registro y dónde acaba, así que usualmente puede recuperar todos los registros excepto los parcialmente escritos. Tenga en cuenta que los índices de tabla MyISAM siempre pueden reconstruirse basados en los registros de datos.

Características generales de tablas de formato estático:

  • Las columnas CHAR añaden espacios hasta la anchura de columna. Esto también es cierto para columnas NUMERIC, y DECIMAL creadas antes de MySQL 5.0.3.

  • Muy rápido.

  • Fácil de cachear.

  • Fácil de reconstruir tras un fallo, ya que los registros se localizan en posiciones fijas.

  • La reorganización no es necesaria a no ser que borre un gran número de registros y quiera devolver espacio libre al sistema operativo. Para ello, use OPTIMIZE TABLE o myisamchk -r.

  • Usualmente requiere más espacio de disco que para tablas de formato dinámico.

14.1.3.2. Características de tablas dinámicas

El formato de almacenamiento dinámico se usa si una tabla MyISAM contiene alguna columna de longitud variable (VARCHAR, BLOB, o TEXT), o si la tabla se crea con la opción ROW_FORMAT=DYNAMIC .

Este formato es un poco más complejo ya que cada columna tiene una cabecera que indica la longitud. Un registro puede acabar en más de una localización cuando es alarga como resultado de una actualización.

Puede usar OPTIMIZE TABLE o myisamchk para defragmentar una tabla. Si tiene columnas de longitud fija a las que accede o cambia frecuentemente en una tabla que también contenga alguna columna de longitud variable, puede ser buena idea mover las columnas de longitud variable a otras tablas para evitar fragmentación.

Características generales de tablas de formato dinámico:

  • Todas las columnas de cadenas de carácteres son dinámicas excepto aquéllas con longitud menor a cuatro.

  • Cada registro viene precedido por un bitmap que indica qué columnas contienen la cadena vacía (para columnas de cadenas) o cero (para columnas numéricas). Tenga en cuenta que esto no incluye columnas que contienen valores NULL. Si una columna de cadena de carácteres tiene una longitud de cero tras eliminar los espacios en blanco finales, o una columna numérica tiene un valor de cero, se marca en el bitmap y no se guarda en disco. Las cadenas no vacías se guardan como un byte de longitud más al de los contenidos de la cadena.

  • Para tablas de longitud fija normalmente se necesita mucho menos espacio de disco.

  • Cada registro usa sólo tanto espacio como necesita. Sin embargo, si un registro crece, se divide en tantos trozos como haga falta, resultando en una fragmentación de registro. Por ejemplo, si actualiza un registro con información que alarga la longitud del registro, el registro se fragmenta. En este caso, puede que tenga que ejecutar OPTIMIZE TABLE o myisamchk -r de vez en cuando para mejorar el rendimiento. Use myisamchk -ei para obtener estadísticas de tabla.

  • Más difícil de reconstruir tras un fallo que las tablas de formato estático, ya que los registros pueden fragmentarse en varios trozos y puede faltar algún enlace (fragmento).

  • La longitud de registro esperada para registros de longitud dinámica se calcula usando la siguiente expresión:

    3
    + (número de columnas + 7) / 8
    + (número de columnas de carácteres)
    + (tamaño comprimido de columnas de cadenas)
    + (longitud de cadenas)
    + (número de columnas NULL + 7) / 8
    

    Hay una penalización de 6 bytes para cada enlace. Un registro dinámico se enlaza si una actualización provoca aumentar el tamaño de un registro. Cada nuevo enlace es al menos de 20 bytes, así que la siguiente ampliación probablemente irá en el mismo enlace. Si no es así, se crea otro enlace. Puede encontrar el número de enlaces usando myisamchk -ed. Pueden eliminarse todos los enlaces con myisamchk -r.

14.1.3.3. Características de las tablas comprimidas

El formato de almacenamiento comprimido es de sólo lectura generado con la herramienta myisampack.

Todas las distribuciones MySQL incluyen por defecto myisampack. Los escaneos de tablas comprimidas son descomprimidas por myisamchk.

Las tablas comprimidas tienen las siguientes características:

  • Las tablas comprimidas ocupan muy poco espacio. Esto minimiza el uso de disco, lo que es útil al usar discos lentos (como CD-ROMs).

  • Cada registros se comprime por separado, así que hay poca sobrecarga de acceso. La cabecera de un registro ocupa de 1 a 3 bytes en función del registro más grande en la tabla. Cada columna está comprimida de forma distinta. Usualmente hay una árbol de Huffman para cada columna. Algunos de los tipos de compresión son:

    • Compresión espacial de sufijo.

    • Compresión espacial de prefijo.

    • Números con valor de cero se almacenan usando un bit.

    • Si los valores de una columna entara tienen un rango pequeño, la columna se almacena usando el tipo menor posible. Por ejemplo, una columna BIGINT (ocho bytes) puede almacenarse como columna TINYINT (un byte) si todos los valores están en el rango de -128 a 127.

    • Si una columna tiene sólo un pequeño conjunto de valores posibles, el tipo de columna se convierte a ENUM.

    • Una columna puede usar cualquier combinación de los tipos de compresión precedentes.

  • Pueden tratar registros de longitud fija o variable.

14.1.4. Problemas en tablas MyISAM

El formato de fichero que usa MySQL para almacenar datos se ha probado extensivamente, pero siempre hay circunstancias que pueden hacer que las tablas se corrompan.

14.1.4.1. Tablas MyISAM corruptas

Incluso el formato de tabla MyISAM es muy fiable (todos los cambios hechos en una tabla por un comando SQL se escriben antes que retorne el comando), puede obtener tablas corruptas si cualquiera de los siguientes eventos ocurre:

  • El proceso mysqld muere durante una escritura.

  • La máquina se apaga inesperadamente.

  • Fallos de hardware.

  • Usa un programa externo (como myisamchk) en una tabla que está siendo modificada por el servidor a la vez.

  • Un bug en el código de MySQL o MyISAM.

Los síntomas típicos de una tabla corrupta son:

  • Obtiene el siguiente error al seleccionar datos de una tabla:

    Incorrect key file for table: '...'. Try to repair it
    
  • Las consultas no obtienen registros en la tabla o retornan datos incompletos.

Puede chequear la salud de una tabla MyISAM usando el comando CHECK TABLE , y reparar una tabla MyISAM corrupta con REPAIR TABLE. Cuando mysqld no está en ejecución, puede chequear o reparar una tabla con el comando myisamchk . Consulte Sección 13.5.2.3, “Sintaxis de CHECK TABLE, Sección 13.5.2.6, “Sintaxis de REPAIR TABLE, and Sección 5.8.3.1, “Sintaxis para invocar myisamchk.

Si sus tablas se corrompen frecuentemente, debe tratar de determinar porqué ocurre. Lo más importante es saber si la tabla se corrompe como resultado de un fallo de servidor. Puede verificarlo fácilmente buscando un mensaje restarted mysqld reciente en el log de errores. Si encuentra dicho mensaje, es probable que la corrupción de tabla sea resultado de la caída del servidor. De otro modo, la corrupción pudo haber ocurrido durante operaciones normales. Esto es un bug. Debe tratar de crear un caso de test reproducible que demuestre el problema. Consulte Sección A.4.2, “Qué hacer si MySQL sigue fallando (crashing)” y Sección D.1.6, “Crear un caso de prueba tras haber encontrado una tabla corrupta”.

14.1.4.2. Problemas debidos a tablas que no se han cerrado debidamente

Cada fichero índice MyISAM (.MYI) tiene un contador en la cabecera que puede usarse para chequear si una tabla se ha cerrado correctamente. Si obtiene la siguiente advertencia de CHECK TABLE o myisamchk, significa que el contador se ha desincronizado:

clients are using or haven't closed the table properly

Esta advertencia no significa necesariamente que la tabla esté corrupta, pero al menos debe chequear la tabla.

El contador funciona como se muesta:

  • La primera vez que se actualiza la hora de una tabla en MySQL, se incrementa un contador en la cabecera del fichero índice.

  • El contador no cambia durante otras actualizaciones.

  • Cuando se cierra la última instancia de una tabla (debido a una operación FLUSH TABLES o porque no hay espacio en la caché de la tabla), el contador se decrementa si la tabla se ha actualizado en cualquier punto.

  • Cuando repara la tabla o chequea la tabla y está correcta, el contador se resetea a cero.

  • Para evitar problemas con interacciones con otros procesos que pueden chequear la tabla, el contador no se decrementea al cerrar si era cero.

En otras palabras, el contador puede desincronizarse sólo bajo las siguientes condiciones:

  • Las tablas MyISAM se copian sin ejecutar en primer lugar LOCK TABLES y FLUSH TABLES.

  • MySQL falla entre una actualización y el cierre final. (Tenga en cuenta que la tabla puede estar ok, ya que MySQL siempre realiza escrituras entre cada comando.)

  • Una tabla se modifica con myisamchk --recover o myisamchk --update-state a la vez que se usa con mysqld.

  • Usando múltiples servidores mysqld usando la tabla y un servidor realiza un REPAIR TABLE o CHECK TABLE en la tabla mientras estaba en uso por otro servidor. En este caso, se puede usar CHECK TABLE, aunque puede obtener una advertencia de otros servidores. Sin embargo, REPAIR TABLE debe evitarse ya que cuando un servidor reemplaza el fichero de datos con uno nuevo, no se envía a los otros servidores.

    En general, no es buena idea compartir un directorio de datos entre varios servidores. Consulte Sección 5.11, “Ejecutar más de un servidor MySQL en la misma máquina” para más informaicón.

14.2. El motor de almacenamiento MERGE

El motor de almacenamiento MERGE , también conocido como MRG_MyISAM , es una colección de tablas MyISAM idénticas que pueden usarse como una. "Idéntica"significa que todas lsas tablas tienen información de columna e índice idéntica. No puede mezclar tablas en que las columnas se listen en orden distinto, no tengan exactamente las mismas columnas, o tengan los índices en orden distinto. Sin embargo, alguna o todas las tablas pueden comprimirse con myisampack. Consulte Sección 8.2, “myisampack, el generador de tablas comprimidas de sólo lectura de MySQL”. Diferencias en las opciones de las tablas tales como AVG_ROW_LENGTH, MAX_ROWS, o PACK_KEYS no importan.

Cuando crea una tabla MERGE , MySQL crea dos ficheros en disco. Los ficheros tienen nombres que comienzan con el nombre de la tabla y tienen una extensión para indicar el tipo de fichero, Un fichero .frm almacena la definición de tabla , y un fichero .MRG contiene los nombres de las tablas que deben usarse como una. Las tablas no tienen que estar en la misma base de datos que la tabla MERGE misma.

Puede usar SELECT, DELETE, UPDATE, y INSERT en la colección de tablas, Debe tener permisos de SELECT, UPDATE, y DELETE en las tablas que mapea a una tabla MERGE.

Si hace un DROP de la tabla MERGE , sólo borra la especificación MERGE . Las tablas subyacentes no se ven afectadas.

Cuando crea una tabla MERGE , debe especificar una cláusula UNION=(list-of-tables) que indica qué tablas quiere usar como una. Puede especificar opcionalmente una opción INSERT_METHOD si quiere que las inserciones en la tabla MERGE se realicen en la primera o última tabla de la lista UNION . Use un valor de FIRST o LAST para hacer que las inserciones se hagan en la primera o última tabla, respectivamente. Si no especifica una opción INSERT_METHOD o si la especifica con un valor de NO, intentos de insertar registros en la tabla MERGE producen un error.

El siguiente ejemplo muestra cómo crear una tabla MERGE :

mysql> CREATE TABLE t1 (
    ->    a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->    message CHAR(20));
mysql> CREATE TABLE t2 (
    ->    a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->    message CHAR(20));
mysql> INSERT INTO t1 (message) VALUES ('Testing'),('table'),('t1');
mysql> INSERT INTO t2 (message) VALUES ('Testing'),('table'),('t2');
mysql> CREATE TABLE total (
    ->    a INT NOT NULL AUTO_INCREMENT,
    ->    message CHAR(20), INDEX(a))
    ->    TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;

Tenga en cuenta que la columna a está indexada en la tabla MERGE , pero no está declarada como PRIMARY KEY como lo está en las tablas MyISAM subyacente. Esto es necesario ya que una tabla MERGE no puede forzar unicidad en un conjunto de tablas subyacentes.

Tras crear la tabla MERGE, puede realizar consultas que operen en el grupo de tablas como unidad:

mysql> SELECT * FROM total;
+---+---------+
| a | message |
+---+---------+
| 1 | Testing |
| 2 | table   |
| 3 | t1      |
| 1 | Testing |
| 2 | table   |
| 3 | t2      |
+---+---------+

Tenga en cuenta que puede manipular el fichero .MRG directamente desde fuera del servidor MySQL :

shell> cd /mysql-data-directory/current-database
shell> ls -1 t1 t2 > total.MRG
shell> mysqladmin flush-tables

Para rempaear una tabla MERGE a una colección diferente de tablas MyISAM, puede realizar una de las siguientes opciones:

  • DROP la tabla MERGE y recrearla.

  • Use ALTER TABLE tbl_name UNION=(...) para cambiar la lista de tablas subyacentes.

  • Cambie el fichero .MRG y realice un comando FLUSH TABLE para la tabla MERGE y todas las tablas subyacentes para forzar al motor de almacenamiento a leer el nuevo fichero de definición.

Las tablas MERGE pueden ayudarle a arreglar los siguientes problemas:

  • Administrar fácilmente un largo conjunto de tablas. Por ejemplo, puede poner datos de meses distintos en tablas separadas, comprimir algunos de ellos con myisampack, y luego crear una tabla MERGE para usarlas como una.

  • Obtener más velocidad. Puede dividir una tabla grande de sólo lectura basándose en algún criterio, y luego poner las tablas individuales en distintos discos. Una tabla MERGE puede ser mucho más rápida que usar una tabla grande.

  • Realizar búsquedas más eficientes. Si conoce exactamente lo que busca, puede buscar en sólo una de las tablas divididas y usar una tabla MERGE para las otras. Puede tener distintas tablas MERGE que usen conjuntos no disjuntos de tablas.

  • Realizar reparaciones más eficientes. Es más fácil de reparar tablas individuales mapeadas en una tabla MERGE que reparar una única tabla grande.

  • Mapear instantáneamente varias tablas como una. Una tabla MERGE no necesita mantener un índice propio ya que usa los índices de las tablas individuales. Como resultado, las colecciones de tablas MERGE son muy rápidas de crear o remapear. (Tenga en cuenta que debe especificar las definiciones de índices cuando crea una tabla MERGE , incluso cuando no se crean índices.)

  • Si tiene un conjunto de tablas que une como una tabla grande bajo demanda o batch, debe crear una tabla MERGE sobre ellas bajo demanda. Esto es mucho más rápido y ahorra mucho espacio de disco.

  • Excede el límite de tamaño del sistema operativo. Cada tabla MyISAM está ligada a este límite, pero una colección de tablas MyISAM no lo está.

  • Puede crear un alias o sinónimo para una tabla MyISAM definiendo una tabla MERGE que mapee a una tabla. No debería haber un impacto de rendimiento realmente impactante al hacer esto (sólo un par de llamadas indirectas y llamadas memcpy() para cada lectura).

Las desventajas de las tablas MERGE son:

  • Sólo puede usar tablas MyISAM idénticas para una tabla MERGE .

  • No puede usar un número de características MyISAM en tablas MERGE . Por ejemplo, no puede crear índices FULLTEXT en tablas MERGE . (Puede crear índices FULLTEXT en las tablas MyISAM subyacentes, puero no puede buscar en la tabla MERGE con búsquedas full-text.)

  • Si la tabla MERGE no es temporal, todas las tablas subyacentes MyISAM deben ser permanentes. Si la tabla MERGE es temporal, las tablas MyISAM pueden ser cualquier mezcla de tablas temporales y no temporales.

  • Las tablas MERGE usan más descriptores de fichero. Si 10 clientes usan una tabla MERGE que mapee a 10 tablas, el servidor usa (10*10) + 10 descriptores de fichero. (10 descriptores de ficheros de datos para cada uno de los 10 clientes, y 10 descriptores de ficheros de índice compartidos entre los clients.)

  • Las lecturas de claves son más lentas. Cuando lee una clave, el motor de almacenamiento MERGE necesita leer en todas las tablas subyacentes para chequear cuál se parece más a la clave dada. Si luego lee el siguiente, el motor MERGE necesita buscar en los buffers de lectura para buscar la siguiente clave. Sólo cuando se usa un búffer de claves el motor necesita leer el siguiente bloque de claves. Esto hace que las claves MERGE sean mucho más lentas en búsquedas eq_ref , pero no mucho más lentas en búsquedas ref . Consulte Sección 7.2.1, “Sintaxis de EXPLAIN (Obtener información acerca de un SELECT)” para más información sobre eq_ref y ref.

14.2.1. Problemas con tablas MERGE

A continuación se describen problemas conocidos con tablas MERGE:

  • Si usa ALTER TABLE para cambiar una tabla MERGE a otro tipo de tabla, el mapeo de las tablas subyacentes se pierde. En su lugar, los registros de las tablas subyacentes MyISAM se copian en la tabla alterada, que luego se asigna al nuevo tipo.

  • REPLACE no funciona.

  • No puede usar DROP TABLE, ALTER TABLE, DELETE FROM sin una cláusula WHERE , REPAIR TABLE, TRUNCATE TABLE, OPTIMIZE TABLE, o ANALYZE TABLE en alguna de las tablas que se mapean en una tabla MERGE abierta. Si lo hace, la tabla MERGE puede referirse a la tabla original, lo que conduce a resultados inesperados. La forma más fácil de solucionar esto es realizar un comando FLUSH TABLES antes de realizar ninguna de estas operaciones para segurar que no quedan abiertas tablas MERGE .

  • Una tabla MERGE no puede mantener restricciones UNIQUE sobre la tabla entera. Cuando realiza un INSERT, los datos van a la primera o última tabla MyISAM (dependiendo del valor de la opción INSERT_METHOD ). MySQL se asegura que los valores de clave única sean únicos dentro de la tabla MyISAM , pero no entre todas las tablas en la colección.

  • Cuando crea una tabla MERGE , no se chequea que las tablas subyacentes existan y tengan una estructura idéntica. Cuando se usa la tabla MERGE , MySQL chequea que la longitud del registro para todas las tablas mapeadas sea la misma, pero esto no es "a prueba de bombas". Si crea una tabla MERGE de tablas MyISAM disimilares, es muy posible que tenga problemas extraños.

  • El orden de índices en la tabla MERGE y sus tablas subyacentes debe ser el mismo. Si usa ALTER TABLE para añadir un índice UNIQUE a una tabla usada en una tabla MERGE , y después usa ALTER TABLE para añadir un índice no único en la tabla MERGE , la ordenación de índice es distinta para las tablas si ya hay un índice no único en las tabla subyacentees. (Esto es porque ALTER TABLE pone los índices UNIQUE antes de los índices no únicos para facilitar detección rápida de claves duplicadas.) Consecuentemente, las consultas en tablas con tales índices pueden retornar resultados no esperados.

  • DROP TABLE en una tabla en uso por una tabla MERGE no funciona en Windows ya que el mapeo del motor de almacenamiento MERGE está escondido por la capa superior de MySQL. Desde Windows no se permite el borrado de ficheros abiertos, debe volcar todas las tablas MERGE (con FLUSH TABLES) o borrar la tabla MERGE antes de borrar la tabla.

14.3. El motor de almacenamiento MEMORY (HEAP)

El motor de almacenamiento MEMORY crea tablas con contenidos que se almacenan en memoria.Éstas se conocían préviamente como HEAP . En MySQL 5.0, MEMORY es el término preferido, aunque HEAP se soporta para compatibilidad con versiones anteriores.

Cada tabla MEMORY está asociada con un fichero de disco. El nombre de fichero comienza con el nombre de la tabla y tiene una exensión de .frm para indicar que almacena la definición de la tabla.

Para especificar explícitamente que quiere una tabla MEMORY, indíquelo con una opción ENGINE :

CREATE TABLE t (i INT) ENGINE = MEMORY;

Como indica su nombre, las tablas MEMORY se almacenan en memoria y usan índices hash por defecto. Esto las hace muy rápidas, y muy útiles para crear tablas temporales. Sin embargo, cuando se apaga el servidor, todos los datos almacenados en las tablas MEMORY se pierde. Las tablas por sí mismas continúan existiendo ya que sus definiciones se almacenan en ficheros .frm en disco, pero están vacías cuando reinicia el servidor.

Este ejemplo muestra cómo puede crear, usar , y borrar una tabla MEMORY :

mysql> CREATE TABLE test ENGINE=MEMORY
    ->     SELECT ip,SUM(downloads) AS down
    ->     FROM log_table GROUP BY ip;
mysql> SELECT COUNT(ip),AVG(down) FROM test;
mysql> DROP TABLE test;

Las tablas MEMORY tienen las siguientes características:

  • El espacio para tablas MEMORY se reserva en pequeños bloques. Las tablas usan el 100% del hashing dinámico para insrciones. No se necesita área de desbordamiento o espacio extra para claves. No se necesita espacio extra para listas libres. Los registros borrados se ponen en una lista encadenada y se reúsan cuando inserta nuevos datos en la tabla. Las tablas MEMORY no tienen ninguno de los problemas asociados con borrados más inserciones en tablas hasheadas.

  • Las tablas MEMORY pueden tener hasta 32 índices por tabla, 16 columnas por índice y una clave de longitud máxima de 500 bytes.

  • En MySQL 5.0, el motor MEMORY implementa índices HASH y BTREE . Puede espcificar uno u otro para cada índice añadiendo una cláusula USING tal y como se muestra:

    CREATE TABLE lookup
        (id INT, INDEX USING HASH (id))
        ENGINE = MEMORY;
    CREATE TABLE lookup
        (id INT, INDEX USING BTREE (id))
        ENGINE = MEMORY;
    

    Las características generales de B-trees e índices hash se describen en Sección 7.4.5, “Cómo utiliza MySQL los índices”.

  • Puede tener claves no únicas en una tabla MEMORY. (Esta es una característica no común de implementaciones de índices hash.)

  • En MySQL 5.0, puede usar INSERT DELAYED con tablas MEMORY . Consulte Sección 13.2.4.2, “Sintaxis de INSERT DELAYED.

  • Si tiene un índice hash en una tabla MEMORY que tenga un alto índice de duplicación de claves (muchas entradas de índice con el mismo valor), las actualizaciones a la tabla que afecten valores claves y todos los borrados son significativamente más lentos. El rango de esta ralentización es proporcional al rango de duplicación (o inversamente proporcional al grado cardinalidad). Pude usar un índice BTREE para evitar este problema.

  • Las tablas MEMORY usan una longitud de registro fija.

  • MEMORY no soporta columnas BLOB o TEXT .

  • MEMORY en MySQL 5.0 incluye soporte para columnas AUTO_INCREMENT e índices en columnas que contengan valores NULL .

  • Las tablas MEMORY se comparten entre todos los clientes (como cualquier otra tabla no-TEMPORARY).

  • Los contenidos de las tablas MEMORY se almacenan en memora , lo que es una propiedad que las tablas MEMORY comparten con las tablas internas que el servidor va creando al procesar consultas. Sin embargo, los dos tipos de tablas difierne en que las tablas MEMORY no están sujetas a conversión de almacenamiento, mientras que las tablas internas sí:

    • Si una tabla interna llega a ser demasiado grande, el servidor la convierte automáticamente a una tabla en disco. El límite de tamaño lo determina la variable de sistema tmp_table_size .

    • Las tablas MEMORY nunca se convieren en tablas de disco. Para segurar que no comete un error accidentalmente, puede cambiar la variable de sistema max_heap_table_size para que imponga un tamaño máximo de tablas MEMORY . Para tablas individuales, puede especificar la opción de tabla MAX_ROWS en el comando CREATE TABLE .

  • El servidor necesita suficiente memoria para mantener todas las tablas MEMORY en uso a la vez.

  • Para liberar memoria usada por una tabla MEMORY cuando no se requiere su contenido, debe ejecutar DELETE FROM o TRUNCATE TABLE, o borrar toda la tabla con DROP TABLE.

  • Si quiere rellenar una tabla MEMORY cuando arranca el servidor MySQL, puede usar la opción --init-file . Por ejemplo, puede usar comandos como INSERT INTO ... SELECT o LOAD DATA INFILE en este fichero para cargar la tabla de una fuente de datos persistente. Consulte Sección 5.3.1, “Opciones del comando mysqld y Sección 13.2.5, “Sintaxis de LOAD DATA INFILE.

  • Si usa replicación, las tablas MEMORY del servidor maestro se vacían cuando se apaga y reinicia. Sin embargo, un esclavo no es consciente que se vacían estas tablas, así que retorna contenido desfasado si selecciona datos del mismo. En MySQL 5.0, cuando se usa una tabla MEMORY en el maestro por primera vez desde que arrancó el maestro, se escribe un comando DELETE FROM en el log binario del maestro automáticamente, resincronizando el maestro y el esclavo otra vez. Tenga en cuenta que incluso con esta estrategia, el esclavo tiene datos desfasados en la tabla en el intervalo entre el reinicio del maestro y el primer uso de la tabla. Sin embargo, si usa la opción --init-file para rellenar la tabla MEMORY al arrancar el maestro, se asegura que este intervalo sea cero.

  • La memoria necesaria por un registro en una tabla MEMORY se calcula con la siguiente expresión:

    SUM_OVER_ALL_BTREE_KEYS(max_length_of_key + sizeof(char*) * 4)
    + SUM_OVER_ALL_HASH_KEYS(sizeof(char*) * 2)
    + ALIGN(length_of_row+1, sizeof(char*))
    

    ALIGN() representa un factor de redondeo para que la longitud del registro sea un múltiplo exacto del tamaño del puntero char . sizeof(char*) es 4 en máquinas de 32-bit y 8 en máquinas de 64-bit.

14.4. El motor de almacenamiento BDB (BerkeleyDB)

Sleepycat Software ha proporcionado a MySQL el motor de almacenamiento transaccional Berkeley DB. A este motor de almacenamiento se le llama tradicionalmente BDB. Se incluye soporte para BDB en distribuciones fuentes de MySQL y en distribuciones binarias MySQL-Max .

Las tablas BDB pueden tener una gran probabilidad de sobrevivir a fallos del sistema y ser capaces de realizar COMMIT y ROLLBACK en transacciones. La distibución fuente MySQL incluye una distribución BDB preparada para funcionar con MySQL. No puede usar una versión de BDB no preparada para funcionar con MySQL.

En MySQL AB trabajamos codo a codo con Sleepycat para mantener la calidad de la interfaz MySQL/BDB . (Incluso aunque Berkeley DB está muy testeado y es muy fiable, la interfaz MySQL se considera de calidad gamma. Continuamos mejorando y optimizándola.)

Cuando hay algún problema con tablas BDB, ayudamos a nuestros usuarios a localizar el problema y reproducir casos de test. Cualquiera de estos casos de test se envía a Sleepycat, que nos ayuda a encontrar y arreglar el problema. Con esta operación de dos fases, cualquier problema con tablas BDB puede tardar un poco más en ser resuelto que con nuestros motores de almacenamiento. Sin embargo, no anticipamos dificultades significativas con este procedimiento ya que el código Berkeley DB se usa en muchas aplicaciones distintas a MySQL.

Para información general sobre Berkeley DB, visite el sitio web de Sleepycat , http://www.sleepycat.com/.

14.4.1. Sistemas operativos que soporta BDB

Actualmente, sabemos que el motor de almacenamiento BDB funcioina con los siguientes sistemas operativos:

  • Linux 2.x Intel

  • Sun Solaris (SPARC y x86)

  • FreeBSD 4.x/5.x (x86, sparc64)

  • IBM AIX 4.3.x

  • SCO OpenServer

  • SCO UnixWare 7.1.x

  • Windows NT/2000/XP

BDB no funciona con los siguientes sistemas operativos:

  • Linux 2.x Alpha

  • Linux 2.x AMD64

  • Linux 2.x IA-64

  • Linux 2.x s390

  • Mac OS X

Nota: La lista precedente no está completa. La actualizamos cuando tenemos más información.

Si compila MySQL de las fuentes con soporte para tablas BDB , pero ocurre el siguiente error cuando arranca mysqld, significa que BDB no está soportado por su arquitectura:

bdb: architecture lacks fast mutexes: applications cannot be threaded
Can't init databases

En este caso, debe recompilar MySQL sin soporte para tablas BDB o arrancar el servidor con la opción --skip-bdb .

14.4.2. Instalación de BDB

Si ha bajado una versión binaria de MySQL que incluya soporte para Berkeley DB, símplemente siga las instrucciones de instalación usuales. (Distribuciones MySQL-Max incluyen soporte BDB .)

Si compila MySQL de las fuentes, puede activar soporte BDB ejecutando configure con la opción --with-berkeley-db además de cualquier otra distribución que use normalmente. Descargue una distribución MySQL 5.0 , cambie la localización en su directorio más alto, y ejecute este comando:

shell> ./configure --with-berkeley-db [other-options]

Para más información consulte Sección 2.7, “Instalación de MySQL en otros sistemas similares a Unix”, Sección 5.1.2, “El servidor extendido de MySQL mysqld-max, y Sección 2.8, “Instalación de MySQL usando una distribución de código fuente”.

14.4.3. Opciones de arranque de BDB

Las siguientes opciones de mysqld pueden usarse para cambiar el comportamiento del motor de almacenamiento BDB:

  • --bdb-home=path

    Directorio base para tablas BDB . Debe ser el mismo directorio que use para --datadir.

  • --bdb-lock-detect=method

    El método de bloqueo BDB . El valor debe ser DEFAULT, OLDEST, RANDOM, o YOUNGEST.

  • --bdb-logdir=path

    El directorio del fichero de log BDB .

  • --bdb-no-recover

    No arranca Berkeley DB en modo recuperación.

  • --bdb-no-sync

    No vuelca síncronamente los logs BDB . Esta opción está obsolta; use --skip-sync-bdb-logs en su lugar (consulte la descripción de --sync-bdb-logs).

  • --bdb-shared-data

    Arranca Berkeley DB en modo multi proceso. (No use DB_PRIVATE al inicializar Berkeley DB.)

  • --bdb-tmpdir=path

    El directorio de ficheros temporales de BDB .

  • --skip-bdb

    Desactiva el motor de almacenamiento BDB .

  • --sync-bdb-logs

    Vuelca síncronamente los logs BDB . Esta opción está activada por defecto ; use --skip-sync-bdb-logs para desactivarla.

See Sección 5.3.1, “Opciones del comando mysqld.

Si usa la opción --skip-bdb , MySQL no inicializa la biblioteca Berkeley DB library y esto ahorra mucha memoria. Sin embargo, si usa esta opción, no puede usar tablas BDB . Si trata de crear una tabla BDB , MySQL crea una tabla MyISAM en su lugar.

Normalmente, debe arrancar mysqld sin la opción --bdb-no-recover si quiere usar tablas BDB . Sin embargo, esto puede causar problemas cuando trata de arrancar mysqld si los ficheros de log de BDB están corruptos. Consulte Sección 2.9.2.3, “Arrancar y resolver problemas del servidor MySQL”.

Con la variable bdb_max_lock , puede especificar el máximo número de bloqueos que pueden estar activos en una tabla BDB . Por defecto son 10,000. Debe incrementar esto si ocurren errores como el siguiente cuando realiza transacciones largas o cuando mysqld tiene que examinar muchos registros para ejecutar una consulta:

bdb: Lock table is out of available locks
Got error 12 from ...

Puede cambiar las variables binlog_cache_size y max_binlog_cache_size si usa transacciones de varios comandos muy largas. Consulte Sección 5.10.3, “El registro binario (Binary Log)”.

Consulte Sección 5.3.3, “Variables de sistema del servidor”.

14.4.4. Características de las tablas BDB

Cada tabla BDB se almacena en disco en dos ficheros. Los ficheros tienen nombres que comienzan con el nombre de la tabla y tienen una extensión para indicar el tipo de fichero. Un fichero .frm almacena la definición de tabla, y un fichero .db contiene los datos de tabla e índices.

Para especificar explícitamente que quiere una tabla BDB, indíquelo con una opición de tabla ENGINE o TYPE:

CREATE TABLE t (i INT) ENGINE = BDB;
CREATE TABLE t (i INT) TYPE = BDB;

BerkeleyDB es sinónimo de BDB en la opción ENGINE o TYPE .

El motor de almacenamiento BDB proporciona tablas transaccionales. La forma de usar estas tablas depende del modo autocommit:

  • Si está ejecutando con autocommit activado (por defecto), los cambios en las tablas BDB se efectúan inmediatamente y no pueden deshacerse.

  • Si está ejecutando con autocommit desactivado, los cambios no son permanentes hasta que ejecuta un comando COMMIT . En lugar de hacer un commit puede ejecutar ROLLBACK para olvidar los cambios.

    Puede comenzar una transacción con el comando BEGIN WORK para suspender autocommit, o con SET AUTOCOMMIT=0 para desactivar autocommit explícitamente.

Consulte Sección 13.4.1, “Sintaxis de START TRANSACTION, COMMIT y ROLLBACK.

El motor de almacenamiento BDB tiene las siguientes características:

  • En MySQL 5.0, las tablas BDB pueden tener hasta 31 índices por tabla, 16 columnas por índice, y un máximo de tamaño de clave de 1024 bytes.

  • MySQL necesita una PRIMARY KEY en cada tabla BDB para que cada registro pueda identificarse unívocamente. Si no crea una explícitamente, MySQL crea y mantiene una PRIMARY KEY oculta. La clave oculta tiene una longitud de 5 bytes y se incrementa para cada intento de inserción. Esta clave no aparece en la salida de SHOW CREATE TABLE o DESCRIBE.

  • La PRIMARY KEY es más rápida que cualquier otro índice, ya que la PRIMARY KEY se almacena junto con los datos. Los otros índices se almacenan como los datos claves + la PRIMARY KEY, por lo que es importante mantener la PRIMARY KEY tan pequeña como sea posible para ahorrar espacio en disco y tener más velocidad.

    Este comportamiento es similar al de InnoDB, donde las claves primarias pequeñas ahorran espacio no sólo en el índice primario sino también en índices secundarios.

  • Si todas las columnas a las que accede en una tabla BDB son parte del mismo índice o parte de la clave primaria, MySQL puede ejcutar la consulta sin tener que acceder al registro. En una tabla MyISAM , esto sólo puede hacerse si las columnas son parte del mismo índice.

  • El escaneo secuencial es más lento que para tablas MyISAM ya que los datos en tablas BDB se almacena en B-trees y no en un fichero de datos separado.

  • Los valores clave no tienen compresión de prefijo ni sufijo como los valores clave en tablas MyISAM . En otras palabras, la información clave ocupa más espacio en tablas BDB en comparación con MyISAM .

  • A menudo hay huecos en la tabla BDB que le permiten insertar nuevos registros en medio del árbol índice. Esto hace que las tablas BDB sean algo más grandes que las MyISAM.

  • SELECT COUNT(*) FROM tbl_name es lento para tablas BDB , ya que no se mantiene conteo de registros en la tabla.

  • El optimizador necesita saber el número aproximado de registros en la tabla. MySQL resuelve esto contando inerciones y manteniendo esto en un segmento separado en cada tabla BDB . Si no realiza muchos comandos DELETE o ROLLBACK , este número debe ser lo bastante adecuado para el optimizador MySQL . Sin embargo, MySQL almacena el número sólo al cerrar, así que puede ser incorrecto si el servidor termina inesperadamente. Puede que no sea fatal incluso si este número no es 100% correcto. Puede actualizar el contador de registros usando ANALYZE TABLE o OPTIMIZE TABLE. Consulte Sección 13.5.2.1, “Sintaxis de ANALYZE TABLE y Sección 13.5.2.5, “Sintaxis de OPTIMIZE TABLE.

  • Bloqueo interno en tablas BDB se realiza a nivel de páginas.

  • LOCK TABLES funciona en tablas BDB como con otras tablas. Si no usa LOCK TABLES, MySQL realiza un bloqueo interno de múltiple escritura en la tabla (un bloqueo que no bloquea otros escritores) para asegurar que la tabla está bloqueada apropiadamente si otro flujo realiza un bloqueo de tabla.

  • Para poder deshacer transacciones, el motor de almacenamiento BDB mantiene ficheros de log. Para máximo rendimiento puede usar la opción --bdb-logdir para guardar los logs BDB en un disco distinto al que tiene las bases de datos.

  • MySQL realiza un checkpoint cada vez que se comienza un nuevo fichero log BDB , y borra cualquier fichero de log BDB no necesario para transacciones actuales. Puede usar FLUSH LOGS en cualquier momento para hacer un checkpoint de tablas Berkeley DB.

    Para recuperación de desastres, debe usar copias de seguridad de tablas más el log binario de MySQL. Consulte Sección 5.8.1, “Copias de seguridad de bases de datos”.

    Atención: Si borra ficheros de log antiguos que estén en uso, BDB no es capaz de recuperar todo y puede perder datos si algo no va bien.

  • Las aplicaciones deben estar siempre preparadas para tratar casos donde cualquier cambio en una tabla BDB pueda causar un rollback automático y cualquier lectura puede fallar con un error de deadlock.

  • Si obtiene un disco entero con una tabla BDB , obtiene un error (probablemente error 28) y la transacción debe deshacerse. Esto contrasta con tablas MyISAM en las que mysqld espera a tener más espacio libre para continuar.

14.4.5. Temas pendientes de arreglo para BDB

  • Abrir muchas tablas BDB a la vez puede ser muy lento. Si va a usar tablas BDB no debe tener una caché de tabla muy grande (por ejemplo, con tamaño superior a 256) y debe usar la opción --no-auto-rehash cuando use el cliente mysql .

  • SHOW TABLE STATUS no proporciona alguna información para tablas BDB :

    mysql> SHOW TABLE STATUS LIKE 'bdbtest'\G
    *************************** 1. row ***************************
               Name: bdbtest
             Engine: BerkeleyDB
            Version: 10
         Row_format: Dynamic
               Rows: 154
     Avg_row_length: 0
        Data_length: 0
    Max_data_length: 0
       Index_length: 0
          Data_free: 0
     Auto_increment: NULL
        Create_time: NULL
        Update_time: NULL
         Check_time: NULL
          Collation: latin1_swedish_ci
           Checksum: NULL
     Create_options:
            Comment:
    
  • Optimizar rendimiento.

  • No use bloqueos de página para operaciones de escaneo de tabla..

14.4.6. Limitaciones en las tablas BDB

La siguiente lista muestra restricciones a tener en cuenta al usar tablas BDB :

  • Cada tabla BDB almacena en el fichero .db la ruta al fichero como si se hubiera creado. Esto se hizo para ser capaz de detectar bloqueos en un entorno multi usuario que soporte enlaces simbólicos. Como consecuencia, no es posible mover ficheros de tablas BDB de un directorio de base de datos a otro.

  • Al hacer copias de seguridad de tablas BDB, debe usar mysqldump o hacer una copia de seguridad que incluya los ficheros para cada tabla BDB (los ficheros .frm y .db ) así como los ficheros de log BDB . El motor de almacenamiento BDB almacena transacciones no acabadas en su log y las necesita cuando arranca mysqld . Los logs BDB son los ficheros en el directorio de datos con nombres de la forma log.XXXXXXXXXX (10 dígitos).

  • Si una columan que permite valores NULL tiene un índice único, sólo un se permite un valor NULL. Esto difiere de otros motores de almacenamiento.

14.4.7. Errores que pueden darse en el uso de tablas BDB

  • Si ocurre el siguiente error cuando arranca mysqld tras actualizar, significa que la nueva versión BDB no soporta el antiguo formato de log:

    bdb:  Ignoring log file: .../log.XXXXXXXXXX:
    unsupported log version #
    

    En este caso, debe borrar todos los logs BDB de su directorio de datos (los fichero con nombres que tienen el formato log.XXXXXXXXXX) y reinicie mysqld. También recomendamos que use mysqldump --opt para volcar las tablas BDB , borre las tablas, y las restaure del fichero de volcado.

  • Si el modo autocommit está desactivado y borra una tabla BDB referenciada por otra transacción, puede obtener un mensaje de error de la siguiente forma en el log de error MySQL:

    001119 23:43:56  bdb:  Missing log fileid entry
    001119 23:43:56  bdb:  txn_abort: Log undo failed for LSN:
                           1 3644744: Invalid
    

    Esto no es fatal, pero hasta arreglar el problema, recomendamos que no borre tablas BDB excepto con el modo autocommit activado. (La forma de arreglarlo no es trivial.)

14.5. El motor de almacenamiento EXAMPLE

El motor de almacenamiento EXAMPLE es un motor de pruebas que no hace nada. Su propósito es servir como ejemplo en el código fuente MySQL para ilustrar cómo empezar a escribir nuevos motores de almacenamiento. Como tal, tiene interés principalmente para desarrolladores.

Para examinar la fuente del motor EXAMPLE , consulte el directorio sql/examples de distribuciones fuentes MySQL 5.0.

Para activar este motor de almacenamiento, use la opción --with-example-storage-engine con configure al compilar MySQL.

Cuando crea una tabla EXAMPLE , el servidor crea un fichero de definición de tabla en el directorio de base de datos. El fichero comienza con el nombre de tabla y tiene una extensión .frm . No se crean más ficheros. No puede almacenarse ni recuperarse datos de la tabla.

mysql> CREATE TABLE test (i INT) ENGINE = EXAMPLE;
Query OK, 0 rows affected (0.78 sec)

mysql> INSERT INTO test VALUES(1),(2),(3);
ERROR 1031 (HY000): Table storage engine for 'test' doesn't have this option

mysql> SELECT * FROM test;
Empty set (0.31 sec)

El motor EXAMPLE no soporta indexación.

14.6. El motor de almacenamiento FEDERATED

El motor FEDERATED está disponible desde MySQL 5.0.3. Es un motor que accede a datos en tablas de bases de datos remotas en lugar de tablas locales.

Para examinar la fuente para el motor FEDERATED, consulte el directorio sql de una distribución fuente de MySQL 5.0.3 o posterior.

14.6.1. Instalación del motor de almacenamiento FEDERATED

Para activar este motor, use la opción --with-federated-storage-engine con configure al compilar MySQL.

14.6.2. Descripción del motor de almacenamiento FEDERATED

Cuando crea una tabla FEDERATED , el servidor crea un fichero de definición de tabla en el directorio de base de datos. El fichero comienza con el nombre de tabla y tiene extensión .frm . No se crean más ficheros, ya que los datos reales están en la base de datos remota. Esto difiere de cómo funcionan los motores con tablas locales.

Para tablas de bases de datos locales, los ficheros de datos son locales. Por ejemplo, si crea una tabla MyISAM llamada users, el handler MyISAM crea un fichero de datos llamado users.MYD. Un handler para tablas locales lee, inserta, borra y actualiza datos en ficheros de datos locales, y los registros se guardan en un formato particular del handler. Para leer registros, el handler debe parsear los datos en columnas. Para escribir registros, los valores de la columna deben convertirse al formato de registro usado por el handler y escribirse en el fichero de datos local.

Con el motor MySQL FEDERATED no hay ficheros de datos locales para una tabla (por ejemplo, no hay fichero .MYD ). En su lugar, una base de datos remota almacena los datos que normalmente estarían en la tabla. Esto necesita el uso de la API del cliente MySQL para leer, borrar, actualizar e insertar datos. La recuperación de datos se inicia via un comando SELECT * FROM tbl_name . Para leer el resultado, los registros se tratan uno a uno usando la función de la API C mysql_fetch_row() y luego se convierten desde las columnas del conjunto de resultados SELECT al formato que el handler FEDERATED espera.

El flujo básico es el siguiente:

  1. Llamadas SQL efectuadas localmente

  2. API del handler MySQL (datos en formato del handler )

  3. API del cliente MySQL (datos convertidos a llamadas SQL )

  4. Base de datos remota -> API del cliente MySQL

  5. Convierte el conjunto de resultados al formato del handler

  6. API del handler -> registros resultado o conteo de registros afectados a local

14.6.3. Cómo usar las tablas FEDERATED

El procedimiento para usar tablas FEDERATED es muy simple. Normalmente, tiene dos servidores en ejecución, en la misma máquina o en distintas. (También es posible para una tabla FEDERATED usar otra tabla administrada por el mismo servidor, aunque no tiene mucho sentido.)

Primero, tiene que tener una tabla en el servidor remoto que quiera acceder con la tabla FEDERATED . Suponga que la tabla remota está en la base de datos federated y se define así:

CREATE TABLE test_table (
    id     int(20) NOT NULL auto_increment,
    name   varchar(32) NOT NULL default '',
    other  int(20) NOT NULL default '0',
    PRIMARY KEY  (id),
    KEY name (name),
    KEY other_key (other)
)
ENGINE=MyISAM
DEFAULT CHARSET=latin1;

La opción de tabla ENGINE puede nombrar cualquier motor de almacenamiento; la tabla no tiene porqué ser MyISAM .

A continuación, cree una tabla FEDERATED en el servidor local para acceder a la tabla remota:

CREATE TABLE federated_table (
    id     int(20) NOT NULL auto_increment,
    name   varchar(32) NOT NULL default '',
    other  int(20) NOT NULL default '0',
    PRIMARY KEY  (id),
    KEY name (name),
    KEY other_key (other)
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
COMMENT='mysql://root@remote_host:9306/federated/test_table';

La estructura de esta tabla debe ser exactamente la misma que la de la tabla remota, excepto que la opción de tabla ENGINE debe ser FEDERATED y la opción de tabla COMMENT es una cadena de conexión que indica al motor FEDERATED cómo conectar al servidor remoto.

El motor FEDERATED crea sólo el fichero test_table.frm en la base de datos federated .

La información del equipo remoto indica el servidor remoto al que se conecta el servidor local, y la información de base de datos y tabla indica la tabla remota a usar como fichero de datos. En este ejemplo, el servidor remoto está indicado para ser remote_host corriendo en el puerto 9306, así que puede arrancar ese servidor para que escuche en el puerto 9306.

La forma general de la cadena de conexión en la opción COMMENT es la siguiente:

scheme://user_name[:password]@host_name[:port_num]/db_name/tbl_name

Sólo se soporta mysql como scheme en este punto; la contraseña y el número de puerto son opcionales.

Aquí hay algunas cadenas de conexión de ejemplo:

COMMENT='mysql://username:password@hostname:port/database/tablename'
COMMENT='mysql://username@hostname/database/tablename'
COMMENT='mysql://username:password@hostname/database/tablename'

El uso de COMMENT para especificar la cadena de conexión no es óptima y posiblemente cambiará en MySQL 5.1. Téngalo en cuenta al usar tablas FEDERATED , ya que significa que habrá que hacer modificaciones cuando ocurra.

Como cualquier contraseña usada se almacena en la cadena de conexión como texto plano, puede ser leído por cualquier usuario que pueda usar SHOW CREATE TABLE o SHOW TABLE STATUS para la tabla FEDERATED , o consultar la tabla TABLES en la base de datos INFORMATION_SCHEMA .

14.6.4. Limitaciones del motor de almacenamiento FEDERATED

Lo que el motor FEDERATED soporta y lo que no:

  • En la primera versión, el servidor remoto debe ser un servidor MySQL. El soporte de FEDERATED para otros motores de bases de datos se añadirá en el futuro.

  • La tabla remota a la que apunta una tabla FEDERATED debe existir antes de intentar acceder a ella.

  • Es posible para una tabla FEDERATED apuntar a otra, pero debe tener cuidado de no crear un bucle.

  • No hay soporte para transacciones.

  • No hay forma que el motor FEDERATED sepa si la tabla remota ha cambiado. La razión es que la tabla debe funcionar como un fichero de datos que nunca se escribirá para hacer algo distinto a la base de datos. La integridad de los datos en la tabla local debe comprobarse si hay algún cambio en la base de datos remota.

  • El motor FEDERATED soporta SELECT, INSERT, UPDATE, DELETE, e índices. No soporta ALTER TABLE, DROP TABLE, o cualquier otro comando Data Definition Language . La implementación actual no usa comandos preparados.

  • La implementación usa SELECT, INSERT, UPDATE, y DELETE, pero no HANDLER.

  • Las tablas FEDERATED no funcionan con la caché de consultas.

Algunas de estas limitaciones pueden mejorarse en futuras versiones del handler FEDERATED .

14.7. El motor de almacenamiento ARCHIVE

El motor de almacenamiento ARCHIVE se usa para guardar grandes cantidades de datos sin índices con una huella relativamente pequeña.

Para activar este motor, use la opción --with-archive-storage-engine con configure al compilar MySQL.

Cuando crea una tabla ARCHIVE , el servidor crea un fichero de definición de tabla en el directorio de base de datos. El fichero comienza con el nombre de tabla y tiene una extensión de .ARZ y .ARM. Un fichero .ARN puede aparecer durante operaciones de optimización.

El motor ARCHIVE soporta sólo INSERT y SELECT. Esto significa que no puede ejecutar DELETE, REPLACE, o update . Un SELECT realiza un escaneo de tabla completo. Los registros se comprimen al insertarse. OPTIMIZE TABLE puede usarse para comprimir la tabla.

El motor ARCHIVE usa bloqueo a nivel de registro.

14.8. El motor de almacenamiento CSV

El motor de almacenamiento CSV almacena datos en ficheros de texto usando valores separados por comas.

Para activar este motor de almacenamiento, use la opción --with-csv-storage-engine con configure al compilar MySQL.

Cuando crea una tabla CSV , el servidor crea un fichero de definición de tabla en el directorio de base de datos. El fichero comienza con el nombre de tabla y tienen una extensión .frm. El motor de almacenamiento crea un fichero de datos. Su nombre comienza con el nombre de tabla y tiene extensión .CSV. El fichero de datos es un fichero de texto. Cuando almacena datos en la tabla, el motor la guarda en el fichero de datos en formato CVS.

mysql> CREATE TABLE test(i INT, c CHAR(10)) ENGINE = CSV;
Query OK, 0 rows affected (0.12 sec)

mysql> INSERT INTO test VALUES(1,'record one'),(2,'record two');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test;
+------+------------+
| i    | c          |
+------+------------+
|    1 | record one |
|    2 | record two |
+------+------------+
2 rows in set (0.00 sec)

Si examina el fichero test.CSV en el directorio de base de datos creado al ejecutar los comandos precedentes, su contenido debe ser como este:

"1","record one"
"2","record two"

El motor de almacenamiento CSV no soporta indexación.


Ésta es una traducción del manual de referencia de MySQL, que puede encontrarse en dev.mysql.com. El manual de referencia original de MySQL está escrito en inglés, y esta traducción no necesariamente está tan actualizada como la versión original. Para cualquier sugerencia sobre la traducción y para señalar errores de cualquier tipo, no dude en dirigirse a mysql-es@vespito.com.