La selectividad de los índices en MySQL

Performance de base de datosAgrego la definición de selectividad respecto a índices en motores de bases de datos:

La selectividad es la relación que hay entre el número de valores posibles en una columna, y el número total de registros. La mejor selectividad es 1, y ocurre cuando hay tantos valores posibles como registros presentes, precisamente en el caso de una clave primaria sobre una columna; la peor selectividad es la que tiende a 0, y ocurre cuando hay muy pocos valores posibles en relación a los registros presentes, por ejemplo una columna sexo.

De esta manera, no es eficiente crear índices para columnas que tienen un número pequeño de posible valores. Incluso he leído, que un optimizador de consultas decente ignorará la presencia de un índice sobre esa columna con poco valores distintos, porque notará que la selectividad es muy baja.

A través del Snipplr de un compañero de trabajo, descubro un interesante script que retorna los 10 índices de MySQL con el peor rendimiento:

/*
SQL script to grab the worst performing indexes
in the whole server
*/
SELECT
  t.TABLE_SCHEMA AS `db`
 , t.TABLE_NAME AS `table`
 , s.INDEX_NAME AS `inde name`
 , s.COLUMN_NAME AS `field name`
 , s.SEQ_IN_INDEX `seq in index`
 , s2.max_columns AS `# cols`
 , s.CARDINALITY AS `card`
 , t.TABLE_ROWS AS `est rows`
 , ROUND(((s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) * 100), 2) AS `sel %`
FROM INFORMATION_SCHEMA.STATISTICS s
 INNER JOIN INFORMATION_SCHEMA.TABLES t
  ON s.TABLE_SCHEMA = t.TABLE_SCHEMA
  AND s.TABLE_NAME = t.TABLE_NAME
 INNER JOIN (
  SELECT
     TABLE_SCHEMA
   , TABLE_NAME
   , INDEX_NAME
   , MAX(SEQ_IN_INDEX) AS max_columns
  FROM INFORMATION_SCHEMA.STATISTICS
  WHERE TABLE_SCHEMA != 'mysql'
  GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME
 ) AS s2
 ON s.TABLE_SCHEMA = s2.TABLE_SCHEMA
 AND s.TABLE_NAME = s2.TABLE_NAME
 AND s.INDEX_NAME = s2.INDEX_NAME
WHERE t.TABLE_SCHEMA != 'mysql'                         /* Filter out the mysql system DB */
AND t.TABLE_ROWS > 10                                   /* Only tables with some rows */
AND s.CARDINALITY IS NOT NULL                           /* Need at least one non-NULL value in the field */
AND (s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) < 1.00 /* Selectivity < 1.0 b/c unique indexes are perfect anyway */
ORDER BY `sel %`, s.TABLE_SCHEMA, s.TABLE_NAME          /* Switch to `sel %` DESC for best non-unique indexes */
LIMIT 10;

Un ejemplo del resultado de esta consulta en un proyecto en producción, retorna índices de campos booleanos o campos que indican el sexo:

Screenshot phpMyAdmin Selectivity

2 comentarios en “La selectividad de los índices en MySQL

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos necesarios están marcados *

Puedes usar las siguientes etiquetas y atributos HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>