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.

    0 
    1 <?php
    2 
    3    // Pongo el tiempo de ejecución ilimitado
    4    set_time_limit(0);
    5 
    6    // Datos del test:
    7    $Prueba_x_tanda = 3;
    8 
    9    $motores = array();
   10    $motores[] = 'BLACKHOLE';
   11    $motores[] = 'MEMORY';
   12    $motores[] = 'MYISAM';
   13    $motores[] = 'ARCHIVE';
   14    $motores[] = 'InnoDB';
   15 
   16    $tandas = array();
   17    $tandas[] = 1;
   18    $tandas[] = 10;
   19    $tandas[] = 100;
   20    $tandas[] = 1000;
   21 //   $tandas[] = 10000;
   22 //   $tandas[] = 100000;
   23 //   $tandas[] = 1000000;
   24 //   $tandas[] = 10000000;
   25 
   26    DEFINE (BD_HOST, 'nombre_del_host');
   27    DEFINE (BD_USER, 'usuario');
   28    DEFINE (BD_PASS, 'password');
   29 
   30    $base_de_datos = "db_engines";
   31 
   32    // Creo la conexión
   33    $conexion = mysql_connect(BD_HOST, BD_USER, BD_PASS);
   34    if (!$conexion){
   35       echo 'ERROR CONECTANDO CON LA BASE DE DATOS.<BR>';}
   36    mysql_selectdb($base_de_datos, $conexion);
   37 
   38    // Creo las tablas
   39    echo "Motores de la prueba: ";
   40    foreach ($motores as $m) {
   41       echo $m.', ';
   42       mysql_query("DROP TABLE `tabla_$m`");
   43       mysql_query("CREATE TABLE `tabla_$m` (i BIGINT) ENGINE=$m");
   44    }
   45 
   46    $CALBDD = 0;
   47 
   48    $tt = microtime();
   49    $TT = time();
   50 
   51    //Hago la prueba:
   52    $resultado = array();
   53 
   54    echo '<br><br><br><br>';
   55 
   56    foreach ($motores as $m) {
   57       echo "MOTOR: $m <BR>";
   58       $sql = "INSERT INTO `tabla_$m` (i) VALUES (";
   59       foreach ($tandas as $t) {
   60          echo "&nbsp;&nbsp;TANDA: $t <br>";
   61          for ($p=0; $p<$Prueba_x_tanda; $p++){
   62             echo "&nbsp;&nbsp;&nbsp;&nbsp;MEDICIÓN $p :";
   63 
   64             $a = microtime();
   65             $A = time();
   66 
   67             for ($i=0; $i<$t; $i++) {
   68                mysql_query($sql.$i.')');
   69             }
   70 
   71             $a = microtime()-$a;
   72             $A = time() - $A;
   73             $A = $A+$a;
   74 
   75             printf ("%.6f", $A);
   76 
   77             $resultado[$t][$p][$m] = $A;
   78 
   79             echo "<br>";
   80          }
   81          echo "<br>";
   82       }
   83       echo "<br>";
   84    }
   85 
   86    // Muestro los resultados
   87    $colspan=count($motores)+0;
   88    echo '<STYLE>
   89       TABLE {WIDTH:100%; BORDER-COLLAPSE:COLLAPSE;}
   90       .nivel_1 {BACKGROUND-COLOR:#DDDDDD;}
   91       .nivel_2 {BACKGROUND-COLOR:#EEEEEE;}
   92       .nivel_4 {BACKGROUND-COLOR:#EEEEFF;}
   93       .nivel_1, .nivel_2, .nivel_3, .nivel_4
   94           {BORDER: SOLID 1px #CCCCCC; text-align:center;}
   95       }</STYLE>';
   96    echo '<TABLE>';
   97    foreach ($tandas as $t) {
   98 
   99       // FILA DE CADA TANDA
  100       echo '<tr>';
  101       echo '<td>.</td>';
  102       echo '   <td colspan="'.$colspan.'" class="nivel_1">';
  103       echo "TANDA $t";
  104       echo '   </td>';
  105       echo '</tr>';
  106 
  107       // CABECERA DE TANDA
  108       echo '<tr>';
  109          echo '<td>.</td>';
  110       foreach ($motores as $m) {
  111          echo '<td class="nivel_2">';
  112          echo $m;
  113          echo '</td>';
  114       }
  115       echo '</tr>';
  116 
  117       // RESULTADOS
  118       for ($i=0; $i<$Prueba_x_tanda; $i++) {
  119          echo '<tr>';
  120             echo '<td align="right">p'.$i.'</td>';
  121             foreach ($motores as $m) {
  122                echo '<td class="nivel_3">';
  123                printf ("%.6f", $resultado[$t][$i][$m]);
  124                echo '</td>';
  125             }
  126          echo '</tr>';
  127       }
  128 
  129       // MEDIA
  130       echo '<tr>';
  131          echo '<td align="right">MEDIA</td>';
  132          foreach ($motores as $m) {
  133             $suma = 0;
  134             $s = 0;
  135             for ($i=0; $i<$Prueba_x_tanda; $i++) {
  136                $suma +=$resultado[$t][$i][$m];
  137                $s++;
  138             }
  139             $media = $suma / $s;
  140             echo '<td class="nivel_4">';
  141             printf ("%.6f", $media);
  142             echo '</td>';
  143          }
  144       echo '</tr>';
  145    }
  146 
  147    echo '</TABLE>';
  148 
  149    $tt = microtime()-$tt;
  150    $TT = time() - $TT;
  151    $TT = $TT+$tt;
  152 
  153    echo '<br><br><br>Tiempo total del test: ' .
  154       $TT.' segundos.<br><br>';
  155 
  156    $est = 0;
  157    foreach ($tandas as $t) {
  158       $est += $t;
  159    }
  160    $est *= count ($motores);
  161    $est *= $Prueba_x_tanda;
  162 
  163    echo 'Consultas exactas a la BBDD: '.$est;
  164 
  165 ?>
  166 
  167

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:

grafico.png mysql_logo.png