La página que tardaba diez segundos
Un equipo de soporte abre los tickets de un cliente. El primer día la página sale al instante. Un año después hay millones de tickets y la misma página tarda diez segundos.
Nadie cambió la consulta. La tabla creció, y ahora la base lee todos los tickets para encontrar los tuyos.
Un índice es el arreglo habitual, y EXPLAIN te dice si lo necesitas.
Al terminar esta lección
Nota
Puedo acelerar una consulta con un índice y leer EXPLAIN para comprobarlo.
Palabras clave
| Palabra | Qué significa | Ejemplo |
|---|---|---|
| Índice | una lista ordenada que se guarda junto a una tabla para encontrar filas sin leerlas todas | |
| Lectura completa | leer todas las filas de una tabla | type ALL en EXPLAIN |
| EXPLAIN | un comando que muestra cómo piensa la base ejecutar una consulta, sin ejecutarla | |
| Plan de ejecución | los pasos que la base elige para responder a una consulta | |
| Estimación | el cálculo aproximado de la base, no un recuento exacto |
Las celdas que rellenan los estudiantes quedan en blanco en esta vista previa.
Una tabla lo bastante grande para ir lenta
La tienda del laboratorio solo tiene unas decenas de productos, así que toda consulta es instantánea. Para ver un índice en acción necesitas más filas.
Estos comandos crean una tabla llamada tickets con 5000 filas en tu propia base. La tienda no se toca.
Puedes repetirlos cuando quieras para empezar de nuevo.
Aceptar el certificado del servidor
printf '[client]\nssl-verify-server-cert=0\n' > ~/.my.cnfSe muestra aquí y no se ejecuta. Crea una cuenta gratuita para pulsar Ejecutar y escribirlo en el terminal de tu propio laboratorio.
Un nombre corto para el cliente
alias db='mariadb -h db -u student -pstudent -t -e'Se muestra aquí y no se ejecuta. Crea una cuenta gratuita para pulsar Ejecutar y escribirlo en el terminal de tu propio laboratorio.
Crear tu propia base
db "CREATE DATABASE IF NOT EXISTS student_depot"Se muestra aquí y no se ejecuta. Crea una cuenta gratuita para pulsar Ejecutar y escribirlo en el terminal de tu propio laboratorio.
Tirar una tabla antigua
db "DROP TABLE IF EXISTS student_depot.tickets"Se muestra aquí y no se ejecuta. Crea una cuenta gratuita para pulsar Ejecutar y escribirlo en el terminal de tu propio laboratorio.
Crear la tabla
db "CREATE TABLE student_depot.tickets (id INT PRIMARY KEY, customer_id INT NOT NULL, status VARCHAR(10) NOT NULL, opened_on DATE NOT NULL)"Se muestra aquí y no se ejecuta. Crea una cuenta gratuita para pulsar Ejecutar y escribirlo en el terminal de tu propio laboratorio.
Llenarla con 5000 tickets
db "SET SESSION cte_max_recursion_depth = 10000; INSERT INTO student_depot.tickets WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 5000) SELECT i, 1 + (i * 37) % 800, IF(i % 5 = 0, 'open', 'closed'), DATE_ADD('2024-01-01', INTERVAL i % 365 DAY) FROM n"Se muestra aquí y no se ejecuta. Crea una cuenta gratuita para pulsar Ejecutar y escribirlo en el terminal de tu propio laboratorio.
Contarlos
db "SELECT COUNT(*) AS rows_in_table FROM student_depot.tickets"Se muestra aquí y no se ejecuta. Crea una cuenta gratuita para pulsar Ejecutar y escribirlo en el terminal de tu propio laboratorio.
EXPLAIN muestra el plan
Escribe la palabra EXPLAIN delante de una consulta. La base no la ejecuta. Muestra cómo lo haría.
Importan tres columnas. type dice cómo se lee la tabla. key nombra el índice usado, o NULL si no hay. rows es su estimación de cuántas filas debe mirar.
Pregúntale por los tickets de un solo cliente.
El plan para un cliente, sin índice
db "EXPLAIN SELECT * FROM student_depot.tickets WHERE customer_id = 42"Se muestra aquí y no se ejecuta. Crea una cuenta gratuita para pulsar Ejecutar y escribirlo en el terminal de tu propio laboratorio.
Ejemplo: leer el plan
| Columna | Lo que viste | Qué significa |
|---|---|---|
| type | ALL | una lectura completa: se leen todas las filas |
| key | NULL | no se usa ningún índice |
| rows | unas 5000 | espera leer la tabla entera |
| Extra | Using where | lee cada fila y luego prueba customer_id |
Las celdas que rellenan los estudiantes quedan en blanco en esta vista previa.
Un índice es un atajo ordenado
El índice de un libro lista palabras en orden con el número de página. No lees todo el libro para encontrar una palabra.
Un índice de base de datos hace lo mismo. Guarda los valores de customer_id en orden, cada uno con el lugar de su fila.
CREATE INDEX crea uno. Dale un nombre, y luego la tabla y la columna.
Crear un índice sobre customer_id
db "CREATE INDEX idx_customer ON student_depot.tickets (customer_id)"Se muestra aquí y no se ejecuta. Crea una cuenta gratuita para pulsar Ejecutar y escribirlo en el terminal de tu propio laboratorio.
Pedir el plan otra vez
db "EXPLAIN SELECT * FROM student_depot.tickets WHERE customer_id = 42"Se muestra aquí y no se ejecuta. Crea una cuenta gratuita para pulsar Ejecutar y escribirlo en el terminal de tu propio laboratorio.
De 5000 filas a unas 6
Ahora type es ref, key es idx_customer y rows es 6. La base va directa a los tickets de ese cliente.
La consulta no cambió. Solo cambió el plan.
Una clave primaria ya tiene índice. Una clave foránea también: MySQL crea uno por ti.
Ver los índices de la tabla orders
mariadb -h db -u student -pstudent shop -t -e "SHOW INDEX FROM orders"Se muestra aquí y no se ejecuta. Crea una cuenta gratuita para pulsar Ejecutar y escribirlo en el terminal de tu propio laboratorio.
Un índice tiene un precio
Un índice es una segunda copia de una columna, guardada en orden. Ocupa espacio.
Cada INSERT, UPDATE y DELETE también debe cambiar el índice. Así, cada índice de más hace la escritura un poco más lenta.
Añade un índice para las preguntas que haces a menudo, no para todas las columnas.
Ver el espacio que ocupa un índice
db "ANALYZE TABLE student_depot.tickets; SELECT data_length, index_length FROM information_schema.tables WHERE table_schema = 'student_depot' AND table_name = 'tickets'"Se muestra aquí y no se ejecuta. Crea una cuenta gratuita para pulsar Ejecutar y escribirlo en el terminal de tu propio laboratorio.
Un error frecuente
Advertencia
Mucha gente cree que un índice se usa siempre que existe. No es cierto. Si la consulta esconde la columna dentro de una función, la base no puede usar la lista ordenada. Mira el plan, no lo supongas.
Indexar la fecha de apertura
db "CREATE INDEX idx_opened ON student_depot.tickets (opened_on)"Se muestra aquí y no se ejecuta. Crea una cuenta gratuita para pulsar Ejecutar y escribirlo en el terminal de tu propio laboratorio.
El plan con una función sobre la columna
db "EXPLAIN SELECT * FROM student_depot.tickets WHERE YEAR(opened_on) = 2024"Se muestra aquí y no se ejecuta. Crea una cuenta gratuita para pulsar Ejecutar y escribirlo en el terminal de tu propio laboratorio.
El plan con un intervalo sobre la columna
db "EXPLAIN SELECT * FROM student_depot.tickets WHERE opened_on >= '2024-03-01' AND opened_on < '2024-03-08'"Se muestra aquí y no se ejecuta. Crea una cuenta gratuita para pulsar Ejecutar y escribirlo en el terminal de tu propio laboratorio.