MySQL: Motores de almacenamiento

Leyendo la documentación de MySQL he encontrado algo de información sobre los motores que se pueden utilizar para gestionar los datos de las tablas. Cada motor potencia una serie de características que optimizan el funcionamiento en determinadas aplicaciones. El motor comúnmente utilizado, y que está establecido por defecto, es MyISAM. Éste se caracteriza principalmente por ofrecer una lectura y escritura rápidas. Además, es el único motor que posibilita la búsqueda fulltext, es decir, permite buscar subcadenas de texto sobre un campo. La carencia más importante de MyISAM es que no incluye soporte para transacciones. Una transacción consiste en una serie de alteraciones que deben realizarse de forma simultánea en un conjunto de registros para mantener la integridad de los datos. Por ejemplo, si en un sistema electrónico de venta compramos un producto, se debe descontar del saldo del comprador una cantidad de dinero y agregar en algún lugar el producto adquirido. Si se produce un fallo de alimentación justo después de descontar el saldo, el cliente se encontrará con un serio problema: tendrá menos dinero en su cuenta y no constará en ningún lado la adquisición del producto. Las transacciones evitan este tipo de problemas garantizando que se han producido todas las modificaciones correctamente o bien, no se ha escrito ninguna. El motor InnoDB soporta transacciones a cambio de un sacrificio de la velocidad de lectura y escritura en un factor 1:10. Además de InnoDB y MyISAM existen los siguientes motores con unas características muy especiales:

MEMORY: Mantiene los datos en memoria, lo que permite obtener una velocidad muy alta. Por contra, los datos se pierden al apagar el servidor.
MERGE: Posibilita acceder a varias tablas con la misma estructura como si se tratase de una misma tabla.
BLACKHOLE: Procesa todas las consultas pero no almacena los datos en ningún sitio. Es como un agujero negro.

Para comparar el rendimiento de los distintos motores de almacenamiento he escrito un script php que crea tablas con distintos motores y ejecuta consultas para agregar datos de tipo BIGINT.

<?php

   // Pongo el tiempo de ejecución ilimitado
   set_time_limit(0);

   // Datos del test:
   $Prueba_x_tanda = 3;

   $motores = array();
   $motores[] = 'BLACKHOLE';
   $motores[] = 'MEMORY';
   $motores[] = 'MYISAM';
   $motores[] = 'ARCHIVE';
   $motores[] = 'InnoDB';

   $tandas = array();
   $tandas[] = 1;
   $tandas[] = 10;
   $tandas[] = 100;
   $tandas[] = 1000;
//   $tandas[] = 10000;
//   $tandas[] = 100000;
//   $tandas[] = 1000000;
//   $tandas[] = 10000000;

   DEFINE (BD_HOST, 'nombre_del_host');
   DEFINE (BD_USER, 'usuario');
   DEFINE (BD_PASS, 'password');

   $base_de_datos = "db_engines";

   // Creo la conexión
   $conexion = mysql_connect(BD_HOST, BD_USER, BD_PASS);
   if (!$conexion){
      echo 'ERROR CONECTANDO CON LA BASE DE DATOS.<BR>';}
   mysql_selectdb($base_de_datos, $conexion);

   // Creo las tablas
   echo "Motores de la prueba: ";
   foreach ($motores as $m) {
      echo $m.', ';
      mysql_query("DROP TABLE `tabla_$m`");
      mysql_query("CREATE TABLE `tabla_$m` (i BIGINT) ENGINE=$m");
   }

   $CALBDD = 0;

   $tt = microtime();
   $TT = time();

   //Hago la prueba:
   $resultado = array();

   echo '<br><br><br><br>';

   foreach ($motores as $m) {
      echo "MOTOR: $m <BR>";
      $sql = "INSERT INTO `tabla_$m` (i) VALUES (";
      foreach ($tandas as $t) {
         echo "  TANDA: $t <br>";
         for ($p=0; $p<$Prueba_x_tanda; $p++){
            echo "    MEDICIÓN $p :";

            $a = microtime();
            $A = time();

            for ($i=0; $i<$t; $i++) {
               mysql_query($sql.$i.')');
            }

            $a = microtime()-$a;
            $A = time() - $A;
            $A = $A+$a;

            printf ("%.6f", $A);

            $resultado[$t][$p][$m] = $A;

            echo "<br>";
         }
         echo "<br>";
      }
      echo "<br>";
   }

   // Muestro los resultados
   $colspan=count($motores)+0;
   echo '<STYLE>
      TABLE {WIDTH:100%; BORDER-COLLAPSE:COLLAPSE;}
      .nivel_1 {BACKGROUND-COLOR:#DDDDDD;}
      .nivel_2 {BACKGROUND-COLOR:#EEEEEE;}
      .nivel_4 {BACKGROUND-COLOR:#EEEEFF;}
      .nivel_1, .nivel_2, .nivel_3, .nivel_4
          {BORDER: SOLID 1px #CCCCCC; text-align:center;}
      }</STYLE>';
   echo '<TABLE>';
   foreach ($tandas as $t) {

      // FILA DE CADA TANDA
      echo '<tr>';
      echo '<td>.</td>';
      echo '   <td colspan="'.$colspan.'" class="nivel_1">';
      echo "TANDA $t";
      echo '   </td>';
      echo '</tr>';

      // CABECERA DE TANDA
      echo '<tr>';
         echo '<td>.</td>';
      foreach ($motores as $m) {
         echo '<td class="nivel_2">';
         echo $m;
         echo '</td>';
      }
      echo '</tr>';

      // RESULTADOS
      for ($i=0; $i<$Prueba_x_tanda; $i++) {
         echo '<tr>';
            echo '<td align="right">p'.$i.'</td>';
            foreach ($motores as $m) {
               echo '<td class="nivel_3">';
               printf ("%.6f", $resultado[$t][$i][$m]);
               echo '</td>';
            }
         echo '</tr>';
      }

      // MEDIA
      echo '<tr>';
         echo '<td align="right">MEDIA</td>';
         foreach ($motores as $m) {
            $suma = 0;
            $s = 0;
            for ($i=0; $i<$Prueba_x_tanda; $i++) {
               $suma +=$resultado[$t][$i][$m];
               $s++;
            }
            $media = $suma / $s;
            echo '<td class="nivel_4">';
            printf ("%.6f", $media);
            echo '</td>';
         }
      echo '</tr>';
   }

   echo '</TABLE>';

   $tt = microtime()-$tt;
   $TT = time() - $TT;
   $TT = $TT+$tt;

   echo '<br><br><br>Tiempo total del test: ' .
      $TT.' segundos.<br><br>';

   $est = 0;
   foreach ($tandas as $t) {
      $est += $t;
   }
   $est *= count ($motores);
   $est *= $Prueba_x_tanda;

   echo 'Consultas exactas a la BBDD: '.$est;

?>

La siguiente tabla muestra el resultado del script para los motores BLACKHOLE, MEMORY, MYISAM, ARCHIVE e InnoDB. Tiene bucles de inserción de registros de 1, 10, 100 y 1000 iteraciones. Cada test se repite 3 veces.

.	TANDA 1
.	BLACKHOLE	MEMORY	MYISAM	ARCHIVE	InnoDB
p0	0.000446	0.000457	0.000629	0.017311	0.014479
p1	0.000155	0.000166	0.000205	0.000229	0.008381
p2	0.000172	0.000145	0.000171	0.000140	0.008202
p3	0.000166	0.000131	0.000251	0.000136	0.008335
p4	0.000132	0.000188	0.000202	0.000145	0.008273
p5	0.000127	0.000161	0.000189	0.000133	0.008198
p6	0.000127	0.000138	0.000171	0.000129	0.008274
p7	0.000130	0.000132	0.000252	0.000133	0.008335
p8	0.000142	0.000127	0.000209	0.000128	0.008177
p9	0.000130	0.000127	0.000170	0.000133	0.008276
MEDIA	0.000173	0.000177	0.000245	0.001862	0.008893
.	TANDA 10
.	BLACKHOLE	MEMORY	MYISAM	ARCHIVE	InnoDB
p0	0.001250	0.001130	0.001633	0.001267	0.083396
p1	0.001156	0.001208	0.001534	0.001190	0.083427
p2	0.001160	0.001131	0.002141	0.002285	0.083456
p3	0.001186	0.001700	0.001504	0.002098	0.083253
p4	0.001156	0.001248	0.001748	0.002053	0.083454
p5	0.001311	0.001151	0.001558	0.002110	0.083399
p6	0.001152	0.001252	0.001368	0.002206	0.083390
p7	0.001462	0.001165	0.001308	0.002132	0.083341
p8	0.001287	0.001207	0.001405	0.001894	0.083514
p9	0.001231	0.001140	0.001385	0.002013	0.085688
MEDIA	0.001235	0.001233	0.001558	0.001925	0.083632
.	TANDA 100
.	BLACKHOLE	MEMORY	MYISAM	ARCHIVE	InnoDB
p0	0.011746	0.011740	0.013287	0.019100	1.446220
p1	0.011926	0.012301	0.013751	0.016464	0.834643
p2	0.013859	0.012032	0.013679	0.012100	0.834690
p3	0.011638	0.012085	0.014822	0.012302	1.095091
p4	0.012228	0.011678	0.013183	0.011944	0.834800
p5	0.011694	0.012231	0.013359	0.012380	0.834617
p6	0.012275	0.011749	0.013966	0.011890	0.851252
p7	0.011681	0.011811	0.013240	0.011841	1.534883
p8	0.012219	0.011901	0.014330	0.012405	0.968274
p9	0.011691	0.011787	0.013353	0.011816	0.834647
MEDIA	0.012096	0.011932	0.013697	0.013224	1.006912
.	TANDA 1000
.	BLACKHOLE	MEMORY	MYISAM	ARCHIVE	InnoDB
p0	0.128271	0.129290	0.177843	0.159341	10.038253
p1	0.129090	0.128835	0.152291	0.131118	10.504115
p2	0.127853	0.128183	0.149975	0.131117	10.189407
p3	0.128910	0.131192	0.156987	0.136375	10.966568
p4	0.127714	0.128493	0.153548	0.137993	10.073468
p5	0.129599	0.137452	0.150052	0.134967	10.635576
p6	0.128307	0.136740	0.144581	0.130362	10.240787
p7	0.128687	0.128636	0.146075	0.148265	10.181287
p8	0.127957	0.129382	0.143752	0.162386	9.831424
p9	0.127626	0.137208	0.143666	0.126140	10.306660
MEDIA	0.128401	0.131541	0.151877	0.139806	10.296755

De forma gráfica se observa un resultado más claro:

Goose, la red social libre

PleasOn - Menu QR

Motores de almacenamiento