Cómo utilizar índices para mejorar el rendimiento de MySQL

GuíasBases de datosMySQLProgramacionSQL

En esta guía vamos cómo utilizar los índices de MySQL del mejor modo para mejorar el rendimiento de las consultas.

Los índices de las tablas MySQL permiten mejorar el rendimiento de las consultas SELECT. En un tabla pequeña apenas notarás la diferencia pero, sin embargo, notarás una mejora considerable de rendimiento en tablas con una gran cantidad de datos. Los siguientes síntomas son un claro indicador de que tu base de datos se podría beneficiar del uso de índices:

  • Tu aplicación o tu web no se puede conectar a la base de datos por estar saturada, siendo los datos de acceso correctos.
  • Si tu aplicación o tu web tarda demasiado en cargar o ha dejado de cargarse o responder por completo.
  • Si compruebas que algunas consultas en ciertas tablas tardan más de la cuenta.

Si tus tablas tienen muchos registros y experimentas alguno de estos problemas sin encontrar la causa, es recomendable que pruebes agregar índices a las tablas. Si tu servidor no tiene demasiados recursos, también puedes consultar esta guía para reducir el uso de memoria de MySQL.

Vamos a ver cómo analizar las consultas existentes y cómo agregar índices si es necesario.

Cómo analizar las consultas a la base de datos

Para saber cuáles son las tablas en las que deberías agregar índices debes analizar el tiempo de ejecución de las consultas y, en base a ello, decidir qué columnas indexar. Afortunadamente, MySQL dispone de la sentencia EXPLAIN SELECT para lograr este objetivo. Esta sentencia funciona como un SELECT tradicional, pero nos da información adicional, como las columnas que es recomendable indexar. Para ver cómo funciona sigue estos pasos:

  1. Abre la terminal de comandos o conéctate a tu servidor mediante SSH.
  2. Conéctate a MySQL con este comando, reemplazando usuario por tu nombre de usuario:
    mysql -u usuario -p

    Una vez introducido se te pedirá la contraseña antes de iniciar la conexión.

  3. Indica a MySQL que quieres acceder a la base de datos en la que se localiza la tabla que quieres analizar. Usa esta sentencia reemplazando base_de_datos por el nombre de la base de datos:
    USE base_de_datos
  4. Ejecuta ahora una consulta EXPLAIN SELECT sobre la tabla, reemplazando tabla por el nombre de la tabla y condiciones por las condiciones que quieras establecer en la consulta:
    EXPLAIN SELECT * FROM tabla WHERE condiciones \G

    Vamos a ejecutar la sentencia EXPLAIN SELECT sobre una tabla de ejemplo que contiene información acerca de los productos que vende una empresa:

    EXPLAIN SELECT title FROM productos WHERE nombre LIKE 'M%' \G

    Vamos a ver dos posibles resultados del comando anterior, fijándonos principalmente en los valores key, possible_keys, y rows:

    • En este primer posible resultado vemos que tanto possible_keys como key tienen valor NULL, indicando que la tabla MySQL no tiene ningún índice que le permita acelerar las consultas. También vemos que, según el valor de rows, se han recorrido 140 filas para generar los resultados, algo que puede ser excesivo, ya que MySQL debe analizar fila por fila y comprobar si el campo nombre del producto comienza por la letra M en cada una de ellas.
      id: 1
      select_type: SIMPLE
      table: productos
      type: ALL
      possible_keys: NULL
      key: NULL
      key_len: NULL
      ref: NULL
      rows: 140
      Extra: Using where
    • Si agregamos un índice llamado indice_nombre_producto en la columna nombre y ejecutamos de nuevo la consulta anterior, el resultado podría ser el que ves debajo de este comentario. Tal y como puedes ver, tanto el valor de key como el de possible_keys indican que MySQL ha encontrado un índice que puede utilizar para optimizar la consulta. Si observas la fila rows, podrás ver que el número de filas que MySQL ha tenido que recorrer es de tan solo 20, que es un número muy inferior a las 120 filas anteriores. Es decir, al estar el nombre del producto indexado, MySQL solamente ha tenido que recorrer las 20 filas que comienzan por la letra M.
      Por último, también tenemos el valor Extra, indicando que MySQL usará un índice en esta consulta.

      id: 1
      select_type: SIMPLE
      table: productos
      type: range
      possible_keys: indice_nombre_producto
      key: indice_nombre_producto
      key_len: 22
      ref: NULL
      rows: 20
      Extra: Using where; Using index

Ahora que seguramente comprendas mejor las ventajas de usar índices, vamos a ver cómo agregarlos, verlos y eliminarlos.

Si quires obtener más información acerca de la sentencia EXPLAIN SELECT, puedes consultas la documentación de MySQL.

Cómo agregar, ver y eliminar índices de una tabla

Una vez analizadas las consultas de al base de datos, ya sabrás en dónde está el problema de pérdida de rendimiento, por lo que sabrás en dónde agregar los índices. Sigue estos pasos para agregar, ver o eliminar índices:

  1. Abre la terminal de comandos o conéctate a tu servidor mediante SSH.
  2. Conéctate a MySQL, reemplazando usuario por tu nombre de usuario e introduciendo la contraseña cuando se te pida:
    mysql -u usuario -p
  3. Selecciona la base de datos en donde está la tabla a la que quieres agregar el índice, reemplazando base_de_datos por el nombre de la base de datos:
    USE base_de_datos

Ahora procede con una de las siguientes tareas.

Cómo crear un índice en una tabla

Puedes crear un índice en una tabla existente modificando la tabla mediante la sentencia ALTER TABLE, reemplazando tabla por el nombre de la tabla, nombre_indice por el nombre que le quieres dar al índice y columna por el nombre de la columna en la que quieres agregarlo:

ALTER TABLE tabla ADD INDEX nombre_indice (columna);

Antes de que te pongas a agregar índices a la desesperada, es importante que sepas que si abusas de ellos, la tabla podría tener incluso peor rendimiento que antes, ya que a mayor número de índices, mayor carga de trabajo tendrá MySQL para mantenerlos actualizados. Es importante encontrar el balance adecuado, de modo que se obtenga una mejora de rendimiento en donde realmente hace es necesario.

Es por este motivo anterior por el cual, vez hayas creado el índice debes utilizar de nuevo la sentencia EXPLAIN SELECT para ver cuántas consultas han sido afectadas por los cambios y, además, obtener el tiempo de ejecución de cada una.

Cómo eliminar un índice de una tabla

Puedes eliminar un índice de una tabla alterándola mediante la sentencia ALTER TABLE, reemplazando tabla por el nombre de la tabla y nombre_indice por el nombre del índice que quieres eliminar:

ALTER TABLE tabla DROP INDEX nombre_indice;

Cómo ver un índice de una tabla

Puedes ver un índice existente de una tabla mediante la sentencia SHOW INDEX de SQL, reemplazando tabla por el nombre de la tabla:

SHOW INDEX FROM tabla \G

Se mostrarán todos los índices existentes en la tabla indicada