MySQL: Analizando la performance de un SELECT

Me encontré con la necesidad de saber con exactitud cuánto demora un SELECT de una fila cuando el campo que buscamos no es un índice, o si es un índice secundario o uno primario.

Para explicar mejor la situación, propongo el siguiente ejemplo:

Tenemos la siguiente Tabla de Usuarios:

La configuración que primero se nos ocurre es que el campo “Id” sea un índice primario, pero qué sucede si por alguna razón, tenemos que hacer siempre un SELECT donde en el WHERE se use con la columna UserName, por ejemplo, si tenemos que loguear al usuario y el dato que tenemos es el username, la consulta sería:

SELECT * FROM Users WHERE UserName=’pepe’;

En cualquier sistema, hasta en un servidor con escasos recursos, esta consulta no tarda mucho y poco importa si tarda unos milisegundos más o menos…  pero en ciertas ocasiones, con tablas de miles de registros,  dependiendo del entorno,  los milisegundos comienzan a tomar importancia, sobre todo si no tenemos mucho poder de procesamiento, el timing comienza a ser de mucha importancia.

Las bases de datos actuales, para resumirlo de una forma brutal y básica, guardan la información en estructuras de datos ordenadas (árboles avanzados) por índices, lo cual nos hace pensar que si buscamos por el campo “Id” es más rápido que si buscamos por el campo “UserName” ya que el campo “Id” está indexado.

Qué podemos hacer entonces??

Hagamos que UserName sea un índice!!!  Muy bonito, pero, cuando la base de datos busca, comparar un INTEGER (“Id”) es muchísimo más rápido que comparar un VARCHAR (“UserName”) de unos 20 caracteres de longitud, por lo tanto, cualquier operación en la tabla se vuelve un poco más lenta…

Entonces, que conviene más?  Usar un índice que haga la tabla más lenta en general, pero que devuelva una fila mucho más rápido? o usar un índice ágil, pero luego tendremos demoras al buscar una sola fila por otro campo?

La respuesta a esta última pregunta, depende un 100% del entorno del sistema en el que estemos…
En un sistema con pocos usuarios, podría no ser un inconveniente, pero cuando tenemos un servidor con escasos recursos, como por ejemplo la Raspberry Pi o similares y en nuestra aplicación una diferencia de milisegundos importa, se convierte en un inconveniente.

Se me ocurrieron algunas opciones para medir el rendimiento y realizar algunos benchmarks de MySQL corriendo con bajos recursos…

• Comparar los resultados usando:

  • a)  El campo Id como Índice Primario y el resto campos comunes. (Situación normal)
  • b)  El campo Id como Índice Primario y el campo UserName como Índice Secundario
  • c)  El campo UserName como Índice Primario y el campo Id como Índice Secundario

• Comparar el comportamiento de las configuraciones anteriores con distintas cantidades de registros:

  • a)  100 (cien) registros
  • b)  1000 (mil) registros
  • c)  10000 (diez mil) registros
  • d)  100000 (cien mil) registros
  • f )  1000000 (un millón) de registros

• Ya que estamos haciendo benchmarks, comparemos los resultados anteriores con los dos motores más populares de MySQL:

  • a)  MyISAM
  • b)  InnoDB

Cómo hago estos benchmarks???

Para ello, escribí unas pocas lineas en PHP, que me permitieron llenar una BD de pruebas con información aleatoria y precisa para las pruebas…  si, en total generé más de 6.500.00 filas! casi 1GB de registros aleatorios. (demoró varios minutos)
Por si a alguien le interesa, al final del post les dejo la descarga del archivo PHP utilizado para generar los datos aleatorios, la estructura de la base de datos sin registros y la bd llena de datos.

Condiciones de las pruebas:

• Las consultas se hacen en PhpMyAdmin.
• Se busca un registro a la mitad del total de la tabla.
• El tiempo es obtenido de PhpMyAdmin.
• El servidor MySQL corre en una Raspberry Pi.
• El sistema operativo es Raspbian ‘Wheezy’.
• Las configuraciones de Apache/PHP/MySQL son las que vienen por defecto al instalarlos.
• El cache MySQL es reseteado antes de cada prueba.
• El campo UserName es un VARCHAR de 25 caracteres.

Ejemplo de consultas:

SELECT * FROM  `innodb_100_usuarios_noindex` WHERE id=50;
/*
(1 total, Query took 0.0061 sec)
id	name	           username	                 telephone	      notes
50	7Rge46fiCLEp8Au	84cdde86a4560c10000000050	vjA0WXmydgFTLf1	84cdde86a4560c10000000050 - 50
*/

SELECT * FROM  `innodb_100_usuarios_noindex` WHERE username='84cdde86a4560c10000000050'
/*
(1 total, Query took 0.0073 sec)
id	name	           username	                 telephone	      notes
50	7Rge46fiCLEp8Au	84cdde86a4560c10000000050	vjA0WXmydgFTLf1	84cdde86a4560c10000000050 - 50
*/

Basta de palabras!!!   quiero ver los números!

Todos los valores de tiempos están expresados en mS (milisegundos).

Aquí tenemos dos tablas, una con los resultados de MyISAM y otra con los de InnoDB. En ambos casos comparamos las tres configuraciones de índices mencionadas anteriormente (a, b, c) en distintas cantidades de registros. Primero buscando por el campo ID y luego por el campo UserName.

MyISAM

[table id=1 /]

InnoDB

[table id=2 /]

Resultados:

A primera vista, podemos notar la diferencia entre InnoDB y MyISAM en cuanto a la performance de los SELECTs.  Si tomamos como referencia las primer columna, donde ID es un Índice Primario, en InnoDB buscar un indice en 1.000.000 de registros, es un 35% más lento, pero si buscamos un registro no indexado, InnoDB es un 440% más lento que MyISAM.

Entonces, descartamos InnoDB para el resto de las comparaciones.

Evidentemente, buscar un registro que no es un índice, en un millón de filas, demora 8158.8 milisegundos, eso es es más de 8 segundos!!!  De hecho en 10.000 filas, demora casi 100 milisegundos, lo cual, en algunos casos, puede ocasionar problemas. Obviamente y como podíamos imaginar, si el tiempo es clave, ésta es la peor configuración.

Nos quedan dos opciones, utilizar el campo UserName como un indice secundario, o como un indice primario.

Como se puede ver en la tabla, si buscamos la mejor velocidad, nos conviene hacerlo un índice primario, pero en contra, la busqueda por ‘Id’ demora un par de milisegundos más.

Conclusión Final:

Como conclusión final, voy a elegir la configuración de “UserName como índice secundario“.  Porqué? Porque en promedio, parece tener los mejores tiempos, ningun tiempo supera los 6ms, sin importar si es índice primario o secundario. Realmente, muy buenos timings.