La persistencia de datos es clave en la época moderna, existen dos enfoques principales cuando se habla de bases de datos: las SQL y NoSQL (por cierto NoSQL no significa No SQL, sino Not Only SQL, ahí te dejo el dato, volvamos al artículo).

Las bases de datos SQL surgieron como una manera para estructurar adecuada y normalizada-mente la información en una época donde el almacenamiento era muy costoso y la normalización era una solución para el ahorro de espacio. Aún con el creciente abaratamiento del almacenamiento son mayoría probablemente los casos de uso donde un motor SQL tradicional como MySQL o Postgres se ajuste mejor.

Sin embargo muchos desarrolladores solo usan el motor de base de datos y algunos no tienen claro como funciona por dentro. En esta entrada te intentare explicar como funciona y se almacena la información en SQL, en este caso tomaremos como punto de referencia MySQL con InnoDB.

Aprenderás:

• ¿Cómo se guarda la información en MySQL/InnoDB?
• ¿Qué es una página en el contexto de MySQL?
• ¿Cómo se organizan internamente los datos?
• ¿Qué es un índice y cual es su importancia?
• ¿Cuáles son los tipos de índices que existen?
• ¿Cómo funcionan los índices internamente?
• ¿Cómo podemos analizar las consultas y optimizarlas?

Esta larga la lista, acompáñame en este artículo.

¿Cómo se guarda la información en MySQL?

En MySQL por debajo cuando creamos una tabla y agregamos datos, la información se guarda en estructuras llamadas pages , una página es la unidad básica de información y tienen un tamaño por defecto de 16KB, las pages almacenan las filas con los datos que vamos agregando, cuando se completa una página se crea una nueva y así sucesivamente.

Otros dos conceptos importantes para entender la estructura interna de MySQL son los extends y segments, las extensiones son un grupo de páginas contiguas y los segmentos son una agrupación de extensiones.

En cuanto a las pages existen dos tipos principales: de datos y de índices. En esta entrada hablaremos de las dos, empecemos por las data pages.

Una página de datos gráficamente se podría representar así:

Cada página contiene un encabezado con información general y metadatos de la página como pueden ser la cantidad de registros que posee y el espacio disponible entre otros. El cuerpo principal de la página dispone de los datos que vamos agregando a la tabla y al final existe una especie de directorio o índice interno de la página, que permite ir directamente a la posición donde esta cada registro. No confundamos estos índices con los que creamos, estos son propios de la página en sí.

En MySQL con InnoDB si a una tabla no se le designa explícitamente una llave primaria se le asignará una implícitamente, esto permite que las tablas creadas ya tengan un índice clusterizado implícitamente (hablaremos de esto más adelante). En otros motores de bases de datos esto no sucede y en las tablas sin índice explicito definido las páginas se agrupan en una estructura llamada heap.

Según se van sumando datos y se van completando las páginas se van creando nuevas páginas de información.

¿Cómo saber cuantas páginas tiene tu tabla? En realidad no existe una forma fácil de saberlo, y tampoco creo sea muy relevante, pero si eres curioso como yo con este comando que te muestro debajo podrás obtener el tamaño de la tabla, así que dividiendo por 16K puedes imaginar un aproximado (16k es el tamaño por defecto, pero este valor puede configurarse).

SHOW TABLE STATUS LIKE 'tu-tabla';

Ahora que sabemos como se guarda la información hablemos de índices.

¿Qué es un índice en MySQL?

Los índices son estructuras creadas para mejorar las operaciones de búsquedas sobre las tablas de datos, su uso agrega un mejor rendimiento y optimización en el proceso de recuperación de información. Existen dos tipos de índices principales los clustered y non-clustered. En MySQL con InnoDB existe un único índice clustered, y pueden existir otros de tipo no clusterizados, a estos índices non-clustered también se les conoce como índices secundarios.

Los índices no almacenan datos, solo almacenan punteros a datos que están en las data pages.

El índice clustered en MySQL con InnoDB se construye usando la llave primaria, donde los datos se almacenan en un árbol BTree ordenado (fundamental) por la clave primaria. Cada nodo del árbol de tipo index page contiene la referencia a una data page o a otro index page. En la data page los datos se ordenan por clave primaria, por lo que solo puede haber uno por tabla, sino definimos una llave primaria, mysql asignará una por defecto. Se llama clustered porque hace referencia a un grupo de claves (ver el gráfico más adelante para mejor comprensión).

El índice secundario es cualquier índice adicional creado sobre una tabla, utilizado para mejorar la búsqueda en columnas distintas de la PRIMARY KEY. También se basa en un BTree, pero solo almacena la columna indexada y un puntero a la llave primaria, lo que permite encontrar registros más rápido sin la necesidad de hacer un fullscan sobre la tabla. El/los índices secundarios puede ser simple o compuesto. Si es simple usa una sola columna, compuesto más de una columna.

Ambos índices se representan en una estructura de BTree como se mencionó anteriormente. Una estructura de árbol binario se ve de esta manera:

En el caso del árbol que se usa para representar los índices, las hojas del árbol serán las data pages, mientras que en los intermediarios estarían las index pages.

Veamos un ejemplo de clustered index (a partir de llave primaria).

Si usando estas tablas, se tuviera que ejecutar una consulta para obtener los datos de los jugadores de cuyos IDs están entre 1 y 5 solo se recorrería el ala mas a la izquierda del árbol, evitando un fullscan de la tabla, veamos esto en la práctica a continuación.

Queda para otro artículo la representación de índices secundarios, pero si veremos como manejarlos a continuación.

Veamos la importancia de los índices.

Supongamos tenemos una tabla como la siguiente:

CREATE TABLE employees (
    id INT, 
    name VARCHAR(100),
    last_name VARCHAR(100),
    email VARCHAR(50) UNIQUE,
    department VARCHAR(100), 
    salary DECIMAL(10, 2) 
) ENGINE=InnoDB;

Como se puede ver, es una tabla sencilla, que NO tiene primary key definida, y para nuestro ejemplo hemos creado un procedimiento almacenado que nos ayudará a popular dicha tabla, te dejo el código por si te interesa (este procedimiento lo hice con la asistencia de deepseek).

DELIMITER $$

CREATE PROCEDURE InsertRandomEmployees()
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE random_name VARCHAR(100);
    DECLARE random_last_name VARCHAR(100);
    DECLARE random_email VARCHAR(50);
    DECLARE random_department VARCHAR(100);
    DECLARE random_salary DECIMAL(10, 2);

    -- Bucle para insertar 100,000 registros
    WHILE i < 100000 DO
        -- Generar valores aleatorios
        SET random_name = CONCAT('Name', FLOOR(RAND() * 100000));
        SET random_last_name = CONCAT('LastName', FLOOR(RAND() * 100000));
        SET random_email = CONCAT('email', i + 1, '@example.com');  -- Usar el contador para garantizar unicidad
        SET random_department = ELT(FLOOR(1 + RAND() * 5), 'Sales', 'Marketing', 'IT', 'HR', 'Finance');
        SET random_salary = RAND() * 10000;

        -- Insertar el registro en la tabla
        INSERT INTO employees (id, name, last_name, email, department, salary)
        VALUES (i + 1, random_name, random_last_name, random_email, random_department, random_salary);

        -- Incrementar el contador
        SET i = i + 1;
    END WHILE;
END$$

DELIMITER ;

Como se puede ver, insertamos unos 100K registros, una vez populada la tabla ejecutamos una query como la siguiente:

select count(*) from employees where department='Marketing';

A primera vista nada está mal, sintácticamente es correcta, pero si hacemos una análisis de la consulta veremos:

 explain select * from employees where department='Marketing';
+----+-------------+-----------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 99661 |    10.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

No fue posible usar ningún índice y la consulta hizo un fullscan, recorriendo los 100K de registros de nuestra tabla, a este ritmo, cuando siga creciendo la consulta se irá deteriorando cada vez más. (La columna rows muestra 99661 porque ilustra solo un aproximado)

Llevemos el explain al siguiente nivel usando analyze para más detalles:

 explain analyze select * from employees where department='Marketing';
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                               |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Filter: (employees.department = 'Marketing')  (cost=10102.35 rows=9966) (actual time=0.037..50.405 rows=20052 loops=1)
    -> Table scan on employees  (cost=10102.35 rows=99661) (actual time=0.017..42.679 rows=100000 loops=1)
 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Aquí se ve mas claro el problema, explicación del resultado:

Operación: Filter:

• Descripción: Se aplica un filtro para seleccionar solo las filas donde department = 'Marketing'.
• Costo estimado (cost): 10102.35 unidades de costo. Este valor es una estimación de cuánto esfuerzo requiere MySQL para realizar esta operación.
• Filas estimadas (rows): 9966 filas. MySQL estima que aproximadamente 9966 filas cumplirán la condición department = 'Marketing'.
• Tiempo real (actual time):
  • 0.037..50.405: El tiempo que tomó realizar esta operación. El primer valor es el tiempo inicial para encontrar la primera fila, y el segundo valor es el tiempo total para procesar todas las filas.
• Filas reales (rows): 20052 filas. Este es el número real de filas que cumplen la condición.
• Bucles (loops): 1. Indica que esta operación se ejecutó una vez.

Operación: Table scan on employees:

• Descripción: Se aplica un escaneo completo de la tabla employees para encontrar las filas que cumplen la condición.
• Costo estimado (cost): 10102.35 unidades de costo.
• Filas estimadas (rows): 99661 filas. MySQL estima que escaneará aproximadamente 99661 filas.
• Tiempo real (actual time):
  • 0.017..42.679: El tiempo que tomó realizar el escaneo completo de la tabla.
• Filas reales (rows): 100000 filas. Este es el número total de filas en la tabla employees.
• Bucles (loops): 1. Indica que esta operación se ejecutó una vez.

Si finalmente ejecutamos la consulta:

select count(*) from employees where department='Marketing';
+----------+
| count(*) |
+----------+
|    20052 |
+----------+
1 row in set (0.04 sec)

No se equivocaron las estimaciones, las filas reales del filtrado de arriba fueron 20052 y el tiempo estimado del scan fue 0.04.

Esto, aunque funciona, no es aceptable, hay que mejorarlo. Probemos ahora haciendo dos cambios, primero creando una llave primaria en la tabla con el id (tocará reconstruir la tabla) y en segundo lugar crearemos un índice secundario con la columna departamento.

Creando una PK en la tabla

Hemos redefinido la tabla, ahora creando una llave primaria.

CREATE TABLE employees2 (
    id INT PRIMARY KEY AUTO_INCREMENT, 
    name VARCHAR(100),
    last_name VARCHAR(100),
    email VARCHAR(50) UNIQUE,
    department VARCHAR(100), 
    salary DECIMAL(10, 2) 
) ENGINE=InnoDB;

Luego de popular la tabla con 100K de registros, vamos a repetir el explain analyze para ver los resultados.

explain analyze select * from employees2 where department='Marketing';
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                 |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Filter: (employees2.department = 'Marketing')  (cost=10107.75 rows=9972) (actual time=0.076..32.344 rows=20062 loops=1)
    -> Table scan on employees2  (cost=10107.75 rows=99715) (actual time=0.072..25.278 rows=100000 loops=1)
 |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)

El resultado a grosso modo es el mismo, no mejoró nada creando una llave primaria, esto sucede porque el dato que estamos filtrando en realidad no depende de la llave primaria en sí. Sin embargo si estuviéramos en otro escenario donde tuviéramos que hacer JOIN con otra tabla por la clave primaria, este cambio si tuviera un valor significativo en el rendimiento.

Para entender el impacto de este cambio y la importancia, cambiemos la consulta para filtrar dependiendo de la clave primaria, por ejemplo:

Caso 1: Tabla employees.

 explain analyze select * from employees where id > 5000;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Filter: (employees.id > 5000)  (cost=10102.35 rows=33217) (actual time=2.533..47.277 rows=95000 loops=1)
    -> Table scan on employees  (cost=10102.35 rows=99661) (actual time=0.069..42.645 rows=100000 loops=1)
 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.06 sec)

Caso 2: Tabla employees2

explain analyze select * from employees2 where id > 5000;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                     |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Filter: (employees2.id > 5000)  (cost=9986.95 rows=49857) (actual time=0.016..31.573 rows=95000 loops=1)
    -> Index range scan on employees2 using PRIMARY over (5000 < id)  (cost=9986.95 rows=49857) (actual time=0.015..27.243 rows=95000 loops=1)
 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)

Creo el resultado habla por si solo, en la tabla de employees2 se usó el índice por PK para filtrar, evitando el fullscan.

Siempre que necesitemos hacer consultas, aseguremos que se usan los índices adecuados para lograr resultados más optimizados.

Ahora, arreglemos nuestro caso de estudio, para ellos vamos a agregar el índice a la tabla por el campo que se usa para el filtrado que es department.

Creando un índice secundario

Para crear el índice usaremos la sentencia de creación índices de MySQL.

CREATE INDEX idx_department ON employees2 (department);

Una vez creado, vamos a repetir el explain.

 explain analyze select * from employees2 where department='Marketing';
+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                |
+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Index lookup on employees2 using idx_department (department='Marketing')  (cost=4335.55 rows=39268) (actual time=0.200..32.259 rows=20062 loops=1)
 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)

Solo se empleará el índice creado para la ejecución de la consulta en una única iteración.

Los resultados:

select count(*) from employees2 where department='Marketing';
+----------+
| count(*) |
+----------+
|    20062 |
+----------+
1 row in set (0.01 sec)

La consulta pasó de demorar 0.04 a 0.01.

Me he divertido y aprendido bastante realizando este artículo, espero que a ti también te haya gustado.

Nos vemos en el próximo.