Desarrollo del software Ruby on Rails. Índices v2

Cuando trabajamos con el framework Ruby on Rails, solemos tratar con bases de datos relacionales como MySQL o PostgreSQL. Al definir migraciones utilizando las migraciones de registros activos, nos encontramos con los llamados índices, pero los principiantes no suelen entender muy bien los índices y qué ventajas aportan.

En este post, me gustaría explicar qué son los índices, para qué se utilizan y presentar algunas buenas prácticas sobre cómo emplearlos.

Base de datos

Hay muchos motores de bases de datos, y uno de los más populares son los ya mencionados MySQL, PostgreSQL, Oracle o Microsoft SQL Server. Todas ellas son bases de datos relacionales, lo que significa que todos los datos están relacionados entre sí y se almacenan en tablas. Cada fila de la tabla se denomina registro, y cada uno tiene su propio identificador único (id). Puede consultar la clasificación de los motores de bases de datos más populares en https://db-engines.com/en/ranking. Allí también encontrarás algunas bases de datos no relacionales, como MongoDB.

Creación de un índice

Las tablas de nuestras bases de datos pueden tener desde unas pocas hasta varias docenas -en casos extremos, hasta varios cientos- de columnas. Hay que tener en cuenta que cada tabla puede tener un número ilimitado de filas. Este número no se deriva directamente de la estructura de la base de datos y siempre debemos asumir que el número de registros irá aumentando sucesivamente y, en consecuencia, nuestra base de datos crecerá. Las hipótesis iniciales y las consultas escritas en las aplicaciones existentes pueden ser estupendas para un número pequeño o mediano de registros, pero con el tiempo, cuando llegan más datos, la comunicación de la aplicación con la base de datos deja de ser eficiente.

El papel del programador es escribir consultas para recuperar algunos datos de la tabla o tablas, pero la forma óptima de procesar la consulta depende del motor de la base de datos. Recuerda que los motores de bases de datos cargan los datos del disco en la memoria y luego los escanean. Esto significa que si muchos usuarios realizan operaciones complejas al mismo tiempo, varios de ellos tendrán que esperar su turno por falta de recursos para ejecutar sus búsquedas. Por eso son tan importantes los índices pertinentes.

Wiki: Índice: estructura de datos que aumenta la velocidad de las operaciones de búsqueda en una tabla.

Para cada índice, debemos definir claves (para una o varias columnas) que se utilizarán para buscar registros en la tabla. Los datos del índice se ordenarán con la clave que se haya definido previamente, lo que agilizará considerablemente la búsqueda de datos en la tabla. El ejemplo más sencillo de la vida cotidiana es una guía telefónica en la que las personas se ordenan por nombre y apellidos. Se puede decir que nuestro índice en este caso será el nombre y los apellidos.

¿Cómo elegir la mejor llave índice? No es difícil: basta con recordar algunas reglas. Cree un índice basado en columnas que:

- se utilizará a menudo en nuestras consultas (DONDE),

- en combinación entre sí dan un valor único (es decir, que indicará exactamente una fila),

- se utilizarán como columnas de conexión (JOIN),

- dan las claves más selectivas, es decir, las que devuelven el menor número de líneas al escribir una consulta.

Si ya sabemos qué claves serán óptimas para nuestra tabla, también podemos preguntarnos cuántos índices necesitamos. En este caso, lo mejor es conocer las consultas que harán referencia a nuestra tabla ya en la fase de diseño.

Creemos índices para consultas concretas que vayan a aparecer, pero no los escribamos para cada columna. Los índices, al igual que las tablas, necesitan almacenarse en algún lugar, por lo que cuando creamos tablas con un índice para cada columna, debemos tener en cuenta que la cantidad de espacio utilizado puede aumentar significativamente.

Crear índice único

Otra cuestión en la que debemos pensar es la unicidad. Merece la pena dedicar cinco minutos más a pensar si nuestro índice es realmente único. De esta forma, le decimos al optimizador de consultas que no tiene que esperar duplicados en la consulta. Por ejemplo, direcciones de correo electrónico:

frozenstringliteral: true

class CrearUsuarios < ActiveRecord::Migración[6.0]
def change
createtable :users do |t|
t.string :email, null: false
end
addindex :users, :email, unique: true
end
end

En el ejemplo del motor PostgreSQL, mostraré la diferencia de velocidad de consulta en la columna email con un índice único y sin índice.

1. Puede utilizar la muestra código en su propia base de datos para poder probar el ejemplo siguiente. En primer lugar, vamos a crear una tabla vacía con una columna:

 CREATE TABLE usuarios (
   email varchar
 );

2. Generemos 10.000 registros para la prueba:

 DO $
   BEGIN FOR i IN 1..10000 LOOP
     INSERT INTO users values((select 'usuario' || i || '@ejemplo.com'));
   END LOOP; END;
 $;

Utilizaremos EXPLAIN ANALYZE para comprobar la rapidez con la que se procesará nuestra consulta cuando queramos encontrar un usuario concreto en la base de datos.

EXPLAIN ANALYZE SELECT email FROM users WHERE email = 'user890example.com';

Nuestra consulta obliga a iterar por toda la tabla en busca del registro que nos interesa.

Este proceso se denomina exploración secuencial. En este caso, leer toda la tabla y filtrar filas concretas es la mejor forma de realizar el trabajo.

PostgreSQL filtrará las líneas innecesarias y simplemente nos devolverá las que nos interesan. Esto es realmente lo mejor que se puede hacer en este caso. El escaneo secuencial no siempre es malo, hay casos en los que el escaneo secuencial es ideal.

4. Ahora es el momento de comprobar la consulta ya realizada sobre la tabla que tiene INDEX UNIQUE. Fijemos el índice y ejecutemos la consulta.

 EATE UNIQUE INDEX index_email on users(email);
 EXPLAIN ANALYZE SELECT email FROM users WHERE email = 'user890example.com';

Esta vez PostgreSQL aprovechó la exploración de índices porque todas las columnas necesarias ya están en el índice.

Seleccionar sólo unas pocas líneas será muy eficaz si se utiliza el índice. Sin embargo, si se seleccionan más datos, escanear el índice y la tabla llevará demasiado tiempo.

Resumen

Como se puede ver, el tiempo de ejecución de una consulta sobre una columna con un índice es mucho menor (en el ejemplo mostrado es una disminución de 1,267 ms a 0,111 ms, ¡tanto como 91,24%!). La diferencia más importante es la forma en que PostgreSQL busca el registro que nos interesa. En el primer caso, el motor de la base de datos tenía que buscar en toda la tabla el registro que nos interesaba. En el segundo, sin embargo, la estructura del índice está ordenada y es única, por lo que el motor sabía dónde se encontraba el registro, lo que aceleró considerablemente el tiempo de procesamiento de la consulta.

En el caso de grandes bases de datos y consultas muy complejas, unos índices correctamente configurados pueden acelerar considerablemente el trabajo de su aplicación sin necesidad de aumentar la velocidad de la máquina en la que realiza la búsqueda en la base de datos.

Conviene recordar que crear índices en cada columna no es una buena práctica. Los índices establecidos acelerarán el trabajo del optimizador a la hora de buscar datos de interés, pero al mismo tiempo ralentizarán la inserción de nuevos y la actualización de los existentes.

Más información:

– ¿Necesitas utilizar los frameworks JS habituales en tu aplicación Rails? Stimulus.js puede ser una alternativa

– Ha llegado la hora de una nueva realidad. Hace un mes comenzó la era del trabajo a distancia

– Desarrollo de aplicaciones web: ¿Por qué merece la pena elegir la tecnología Ruby on Rails?