Mantenimiento básico para los índices de MySQL

Mantenimiento básico para los índices de MySQL

  7:34:00 p.m.    ReggieClassic

---

Es importante mantener sus índices limpios y ordenados. Aquí hay cuatro reglas sencillas (con ejemplos) que puede seguir para mantener sus bases de datos funcionando sin problemas.


En esta entrada del blog, vamos a ver algunos de los pasos básicos de limpieza y mantenimiento para los índices de MySQL.

Todos sabemos que los índices pueden ser la diferencia entre una base de datos de alto rendimiento y una experiencia lenta y dolorosa. Es una parte crítica que necesita merece un poco de limpieza de vez en cuando. Así que, ¿qué debemos comprobar? En ningún orden en particular, aquí hay algunas cosas que ver:

1. Indices no utilizados

Con el esquema sys, es bastante fácil de encontrar índices no utilizados: utilizar la vista schema_unused_indexes.

mysql> select * from sys.schema_unused_indexes;






+---------------+-----------------+-------------+






| object_schema | object_name     | index_name  |






+---------------+-----------------+-------------+






| world         | City            | CountryCode |






| world         | CountryLanguage | CountryCode |






+---------------+-----------------+-------------+






2 rows in set (0.01 sec)

Tenga en cuenta que esto demuestra que los índices no se han utilizado desde el último reinicio del servicio mysqld. Las estadísticas que residen en el  PERFORMANCE_SCHEMA se restablecen al reiniciar el servicio.

2. Índices duplicados

La vista schema_redundant_indexes también es fácil de usar una vez que haya instalado el esquema sys. La diferencia es que se basa en la tabla information_schema.statistics:

mysql> select * from sys.schema_redundant_indexes;
*************************** 1. row ***************************
              table_schema: world
                table_name: CountryLanguage
      redundant_index_name: CountryCode
   redundant_index_columns: CountryCode
redundant_index_non_unique: 1
       dominant_index_name: PRIMARY
    dominant_index_columns: CountryCode,Language
 dominant_index_non_unique: 0
            subpart_exists: 0
            sql_drop_index: ALTER TABLE `world`.`CountryLanguage` DROP INDEX `CountryCode`
1 row in set (0.00 sec)

Una vez que encuentre el índice redundante, elimínelo.

3. Índices que puedan faltar

El sumario de  declaraciones de las tablas del esquema Perfomance tienen varios campos de interés. Para nuestro caso, dos de ellos son bastante importantes: NO_INDEX_USED (significa que la sentencia realiza una exploración de tabla sin usar un índice) y NO_GOOD_INDEX_USED ( "1" si el servidor no encontró un  índice a utilizar para la sentencia, "0" en caso contrario).

El esquema Sys tiene una vista basada en la tabla performance_schema.events_statements_summary_by_digest, y es útil para este propósito:

statements_with_full_table_scans, que enumera todas las sentencias normalizadas que han hecho un table scan.

mysql> select * from world.CountryLanguage where isOfficial = 'F';
55a208785be7a5beca68b147c58fe634  -
746 rows in set (0.00 sec)
mysql> select * from statements_with_full_table_scansG
*************************** 1. row ***************************
                   query: SELECT * FROM `world` . `Count ... guage` WHERE `isOfficial` = ?
                      db: world
              exec_count: 1
           total_latency: 739.87 us
     no_index_used_count: 1
no_good_index_used_count: 0
       no_index_used_pct: 100
               rows_sent: 746
           rows_examined: 984
           rows_sent_avg: 746
       rows_examined_avg: 984
              first_seen: 2016-09-05 19:51:31
               last_seen: 2016-09-05 19:51:31
                  digest: aa637cf0867616c591251fac39e23261
1 row in set (0.01 sec)

La consulta anterior no utiliza un índice porque no había buen índice para usar.

4. Indices en multiples columnas

A la hora de utilizar indices en multiples columnas, no hay que olvidar las cuestiones de orden. MySQL sólo utilizará indices en múltiples columnas dentro de una tabla, si se especifica al menos un valor para el indice en  la primera columna.

Por ejemplo, considere la siguiente tabla:

mysql> show create table CountryLanguage;
*************************** 1. row ***************************
       Table: CountryLanguage
Create Table: CREATE TABLE `CountryLanguage` (
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `Language` char(30) NOT NULL DEFAULT '',
  `IsOfficial` enum('T','F') NOT NULL DEFAULT 'F',
  `Percentage` float(4,1) NOT NULL DEFAULT '0.0',
  PRIMARY KEY (`CountryCode`,`Language`),
  KEY `CountryCode` (`CountryCode`),
  CONSTRAINT `countryLanguage_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Una consulta contra el campo "Language" no utilizará un índice:

mysql> explain select * from CountryLanguage where Language = 'English';
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: CountryLanguage
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 984
        Extra: Using where

Simplemente porque no es el prefijo de más a la izquierda de la clave principal. Si añadimos el campo "CountryCode", ahora se utilizará el índice:

mysql> explain select * from CountryLanguage where Language = 'English' and CountryCode = 'CAN';
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: CountryLanguage
         type: const
possible_keys: PRIMARY,CountryCode
          key: PRIMARY
      key_len: 33
          ref: const,const
         rows: 1
        Extra: NULL

Colocar mas selectivamente el indice de la primera columna de una tabla es una buena idea cuando el propósito del índice es optimizar  las búsquedas. Puede que tenga que elegir el orden de las columnas, de modo que sea lo más selectivo posible para que ayuden a la mayoría de las consultas que se realicen.