Cómo acelerar las consultas SQL utilizando índices [Edición Python]

Acelerar consultas SQL con índices [Python]

 

Supongamos que estás buscando información en las páginas de un libro. Y quieres encontrar la información que estás buscando mucho más rápido. ¿Cómo lo harías? Bueno, probablemente buscarías el índice de términos y luego saltarías a las páginas que hacen referencia a un término en particular. Los índices en SQL funcionan de manera similar a los índices en los libros.

En la mayoría de los sistemas del mundo real, ejecutarás consultas contra una tabla de base de datos con un gran número de filas (piensa en millones de filas). Las consultas que requieren un escaneo completo de la tabla a través de todas las filas para recuperar los resultados serán bastante lentas. Si sabes que tendrás que consultar información basada en algunas de las columnas con frecuencia, puedes crear índices de base de datos en esas columnas. Esto acelerará significativamente la consulta.

Entonces, ¿qué aprenderemos hoy? Aprenderemos a conectar y consultar una base de datos SQLite en Python, utilizando el módulo sqlite3. También aprenderemos cómo agregar índices y ver cómo mejora el rendimiento.

• Controversia de Disney en Hollywood ¡Interviene la IA, los escritores y actores se retiran!
• RBI adopta la IA conversacional y los pagos sin conexión utilizando UPI
• OpenAI presenta GPTBot un rastreador web diseñado para extraer datos de toda la Internet automáticamente

Para seguir este tutorial, debes tener Python 3.7+ y SQLite instalados en tu entorno de trabajo.

Nota: Los ejemplos y las salidas de muestra en este tutorial son para Python 3.10 y SQLite3 (versión 3.37.2) en Ubuntu LTS 22.04.

 

Conectarse a una base de datos en Python

 

Utilizaremos el módulo integrado sqlite3. Antes de comenzar a ejecutar consultas, necesitamos:

• conectarnos a la base de datos
• crear un cursor de base de datos para ejecutar consultas

Para conectarnos a la base de datos, utilizaremos la función

connect() del módulo sqlite3. Una vez que hemos establecido una conexión, podemos llamar a cursor() en el objeto de conexión para crear un cursor de base de datos, como se muestra a continuación:

import sqlite3

# conectarse a la base de datos
db_conn = sqlite3.connect('people_db.db')
db_cursor = db_conn.cursor()

 

Aquí intentamos conectarnos a la base de datos “people_db”. Si la base de datos no existe, ejecutar el fragmento anterior creará la base de datos sqlite para nosotros.

 

Crear una tabla e insertar registros

 

Ahora, crearemos una tabla en la base de datos y la poblaremos con registros.

Creemos una tabla llamada people en la base de datos people_db con los siguientes campos:

• name (nombre)
• email (correo electrónico)
• job (trabajo)

# main.py
...
# crear tabla
db_cursor.execute('''CREATE TABLE people (
                  id INTEGER PRIMARY KEY,
                  name TEXT,
                  email TEXT,
                  job TEXT)''')

...

# confirmar la transacción y cerrar el cursor y la conexión de la base de datos
db_conn.commit()
db_cursor.close()
db_conn.close()

 

Generación de datos sintéticos con Faker

 

Ahora tenemos que insertar registros en la tabla. Para hacer esto, utilizaremos Faker, un paquete de Python para la generación de datos sintéticos, que se puede instalar a través de pip:

$ pip install faker

 

Después de instalar faker, puedes importar la clase Faker en el script de Python:

# main.py
...
from faker import Faker
...

 

El siguiente paso es generar e insertar registros en la tabla de personas. Para que sepamos cómo los índices pueden acelerar las consultas, vamos a insertar una gran cantidad de registros. Aquí, insertaremos 100.000 registros; establece la variable num_records en 100000.

Luego, hacemos lo siguiente:

• Instanciamos un objeto Faker llamado fake y establecemos la semilla para obtener reproducibilidad.
• Obtenemos una cadena de nombre utilizando nombres y apellidos, llamando a first_name() y last_name() en el objeto fake.
• Generamos un dominio falso llamando a domain_name().
• Utilizamos los nombres y apellidos y el dominio para generar el campo de correo electrónico.
• Obtenemos un trabajo para cada registro individual utilizando job().

Generamos e insertamos registros en la tabla people:

# crear e insertar registros
fake = Faker() # asegúrate de importar: from faker import Faker
Faker.seed(42)

num_records = 100000

for _ in range(num_records):
    first = fake.first_name()
    last = fake.last_name()
    name = f"{first} {last}"
    domain = fake.domain_name()
    email = f"{first}.{last}@{domain}"
    job = fake.job()
    db_cursor.execute('INSERT INTO people (name, email, job) VALUES (?,?,?)', (name,email,job))

# confirmar la transacción y cerrar el cursor y la conexión a la base de datos
db_conn.commit()
db_cursor.close()
db_conn.close()

Ahora el archivo main.py tiene el siguiente código:

# main.py
# imports
import sqlite3
from faker import Faker

# conectarse a la base de datos
db_conn = sqlite3.connect('people_db.db')
db_cursor = db_conn.cursor()

# crear tabla
db_cursor.execute('''CREATE TABLE people (
                  id INTEGER PRIMARY KEY,
                  name TEXT,
                  email TEXT,
                  job TEXT)''')

# crear e insertar registros
fake = Faker()
Faker.seed(42)

num_records = 100000

for _ in range(num_records):
    first = fake.first_name()
    last = fake.last_name()
    name = f"{first} {last}"
    domain = fake.domain_name()
    email = f"{first}.{last}@{domain}"
    job = fake.job()
    db_cursor.execute('INSERT INTO people (name, email, job) VALUES (?,?,?)', (name,email,job))

# confirmar la transacción y cerrar el cursor y la conexión a la base de datos
db_conn.commit()
db_cursor.close()
db_conn.close()

Ejecuta este script, una vez, para poblar la tabla con num_records número de registros.

Consultando la Base de Datos

Ahora que tenemos la tabla con 100K registros, ejecutemos una consulta de ejemplo en la tabla people.

Ejecutemos una consulta para:

• obtener los nombres y correos electrónicos de los registros con el título de trabajo ‘Product manager’, y
• limitar los resultados de la consulta a 10 registros.

Utilizaremos el temporizador por defecto del módulo time para obtener el tiempo aproximado de ejecución de la consulta.

# sample_query.py

import sqlite3
import time

db_conn = sqlite3.connect("people_db.db")
db_cursor = db_conn.cursor()

t1 = time.perf_counter_ns()

db_cursor.execute("SELECT name, email FROM people WHERE job='Product manager' LIMIT 10;")

res = db_cursor.fetchall()
t2 = time.perf_counter_ns()

print(res)
print(f"Tiempo de consulta sin índice: {(t2-t1)/1000} us")

Aquí está la salida:

Salida >>
[
    ("Tina Woods", "[email protected]"),
    ("Toni Jackson", "[email protected]"),
    ("Lisa Miller", "[email protected]"),
    ("Katherine Guerrero", "[email protected]"),
    ("Michelle Lane", "[email protected]"),
    ("Jane Johnson", "[email protected]"),
    ("Matthew Odom", "[email protected]"),
    ("Isaac Daniel", "[email protected]"),
    ("Jay Byrd", "[email protected]"),
    ("Thomas Kirby", "[email protected]"),
]

Tiempo de consulta sin índice: 448.275 us

También puedes invocar el cliente de línea de comandos de SQLite ejecutando sqlite3 nombre_bd en la línea de comandos:

$ sqlite3 people_db.db
Versión de SQLite 3.37.2 2022-01-06 13:25:41
Ingresa ".help" para obtener pistas de uso.

Para obtener la lista de índices, puedes ejecutar .index:

sqlite> .index

Como actualmente no hay índices, no se mostrará ningún índice.

También puedes verificar el plan de consulta de la siguiente manera:

sqlite> EXPLAIN QUERY PLAN SELECT name, email FROM people WHERE job='Gerente de Producto' LIMIT 10;
PLAN DE CONSULTA
`--ESCANEADO de personas

 

Aquí el plan de consulta consiste en escanear todas las filas, lo cual es ineficiente.

 

Crear un Índice en una Columna Específica

 

Para crear un índice de base de datos en una columna específica, puedes usar la siguiente sintaxis:

CREATE INDEX nombre-índice en tabla (columna(s))

 

Supongamos que necesitamos buscar con frecuencia los registros de personas con un título de trabajo específico. Sería útil crear un índice people_job_index en la columna de trabajo:

# create_index.py

import time
import sqlite3

db_conn = sqlite3.connect('people_db.db')

db_cursor =db_conn.cursor()

t1 = time.perf_counter_ns()

db_cursor.execute("CREATE INDEX people_job_index ON people (job)")

t2 = time.perf_counter_ns()

db_conn.commit()

print(f"Tiempo para crear el índice: {(t2 - t1)/1000} us")


Salida >>
Tiempo para crear el índice: 338298.6 us

 

Aunque crear el índice tarda mucho tiempo, es una operación única. Aún así, obtendrás una mejora sustancial en la velocidad al ejecutar múltiples consultas.

Ahora, si ejecutas .index en la línea de comandos de SQLite, obtendrás:

sqlite> .index
people_job_index

 

Consultando la Base de Datos con un Índice

 

Si ahora observas el plan de consulta, deberías poder ver que ahora buscamos en la tabla people utilizando el índice people_job_index en la columna job:

sqlite> EXPLAIN QUERY PLAN SELECT name, email FROM people WHERE job='Gerente de Producto' LIMIT 10;
PLAN DE CONSULTA
`--BÚSQUEDA en personas UTILIZANDO ÍNDICE people_job_index (job=?)

 

Puedes volver a ejecutar sample_query.py. Solo modifica la instrucción print() y observa cuánto tiempo tarda en ejecutarse la consulta ahora:

# sample_query.py

import sqlite3
import time

db_conn = sqlite3.connect("people_db.db")
db_cursor = db_conn.cursor()

t1 = time.perf_counter_ns()

db_cursor.execute("SELECT name, email FROM people WHERE job='Gerente de Producto' LIMIT 10;")

res = db_cursor.fetchall()
t2 = time.perf_counter_ns()

print(res)
print(f"Tiempo de consulta con el índice: {(t2-t1)/1000} us")

 

Aquí está la salida:

Salida >>
[
    ("Tina Woods", "[email protected]"),
    ("Toni Jackson", "[email protected]"),
    ("Lisa Miller", "[email protected]"),
    ("Katherine Guerrero", "[email protected]"),
    ("Michelle Lane", "[email protected]"),
    ("Jane Johnson", "[email protected]"),
    ("Matthew Odom", "[email protected]"),
    ("Isaac Daniel", "[email protected]"),
    ("Jay Byrd", "[email protected]"),
    ("Thomas Kirby", "[email protected]"),
]

Tiempo de consulta con el índice: 167.179 us

 

Vemos que la consulta ahora tarda aproximadamente 167.179 microsegundos en ejecutarse.

 

Mejora de Rendimiento

 

Para nuestra consulta de ejemplo, la consulta con el índice es aproximadamente 2.68 veces más rápida. Y obtenemos una mejora de velocidad del 62.71% en los tiempos de ejecución.

También puedes probar ejecutar algunas consultas adicionales: consultas que involucren filtrar en la columna de trabajo y ver la mejora de rendimiento.

También ten en cuenta: Como hemos creado un índice solo en la columna de trabajo, si estás ejecutando consultas que involucran otras columnas, las consultas no se ejecutarán más rápido que sin el índice.

 

Resumen y Próximos Pasos

 

Espero que esta guía te haya ayudado a entender cómo la creación de índices de base de datos, en columnas consultadas con frecuencia, puede acelerar significativamente las consultas. Este es una introducción a los índices de base de datos. También puedes crear índices de múltiples columnas, múltiples índices para la misma columna y mucho más.

Puedes encontrar todo el código utilizado en este tutorial en este repositorio de GitHub. ¡Feliz codificación!     Bala Priya C es una desarrolladora y redactora técnica de India. Le gusta trabajar en la intersección entre matemáticas, programación, ciencia de datos y creación de contenido. Sus áreas de interés y experiencia incluyen DevOps, ciencia de datos y procesamiento del lenguaje natural. Le gusta leer, escribir, programar y tomar café. Actualmente, está trabajando en aprender y compartir sus conocimientos con la comunidad de desarrolladores mediante la creación de tutoriales, guías prácticas, artículos de opinión y más.

We will continue to update Zepes; if you have any questions or suggestions, please contact us!