Índices múltiples y únicos
Estoy un poco avergonzado de preguntar esto ya que he estado trabajando con MySQL durante años, pero oh bien.
Tengo una tabla con dos campos, a y b. Voy a ejecutar las siguientes consultas en él:
SELECT * FROM ... WHERE A = 1;SELECT * FROM ... WHERE B = 1;SELECT * FROM ... WHERE A = 1 AND B = 1;
Desde el punto de vista del rendimiento, es al menos una de las siguientes configuraciones de índices más lento para al menos una consulta? En caso afirmativo, por favor elaborar.
ALTER TABLE ... ADD INDEX (a); ALTER TABLE ... ADD INDEX (b);ALTER TABLE ... ADD INDEX (a, b);ALTER TABLE ... ADD INDEX (a); ALTER TABLE ... ADD INDEX (b); ALTER TABLE ... ADD INDEX (a, b);
Gracias (tenga en cuenta que estamos hablando de índices no únicos)
4 answers
Sí, al menos un caso es considerablemente más lento. Si solo define el siguiente índice:
ALTER TABLE ... ADD INDEX (a, b);
... entonces la consulta SELECT * FROM ... WHERE B = 1; no usará ese índice.
Cuando se crea un índice con una clave compuesta, el orden de las columnas de la clave es importante. Se recomienda tratar de ordenar las columnas en la clave para mejorar la selectividad, con las columnas más selectivas a la izquierda, la mayor parte de la clave. Si no lo hace, y poner una columna no selectiva como la primera parte de la clave, corre el riesgo de no usar el índice en absoluto. (Fuente: Consejos para optimizar el Índice Compuesto de SQL Server )
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-01-28 21:37:42
Es muy improbable que la mera existencia de un índice ralentice una consulta SELECT: simplemente no se utilizará.
En teoría, el optimizador puede elegir incorrectamente un índice más largo en (a, b) en lugar de uno en (a) para servir a la consulta que busca solo a.
En la práctica, nunca lo he visto: MySQL generalmente comete el error opuesto, tomando un índice más corto cuando existe uno más largo.
Actualización:
En su caso, cualquiera de los siguientes las configuraciones serán suficientes para todas las consultas:
(a, b); (b)
O
(b, a); (a)
MySQL también puede utilizar dos índices separados con index_intersect, por lo que la creación de estos índices
(a); (b)
También acelerará la consulta con a = 1 AND b = 1, aunque en menor medida que cualquiera de las soluciones anteriores.
Es posible que también desee leer este artículo en mi blog:
Actualizar 2:
Parece que finalmente entendí su pregunta:)
ALTER TABLE ... ADD INDEX (a); ALTER TABLE ... ADD INDEX (b);
Excelente para a = 1 y b = 1, razonablemente bueno para a = 1 AND b = 1
ALTER TABLE ... ADD INDEX (a, b);
Excelente para a = 1 AND b = 1, casi excelente para a = 1, pobre para b = 1
ALTER TABLE ... ADD INDEX (a); ALTER TABLE ... ADD INDEX (b); ALTER TABLE ... ADD INDEX (a, b);
Excelente para las tres consultas.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-01-28 15:53:48
SQL elegirá el índice que mejor cubra la consulta. Un índice en A, B cubrirá la consulta para ambos casos 1 y 3, pero no para 2 (ya que la columna principal del índice es A)
Así que para cubrir las tres consultas necesitas dos índices:
ALTER TABLE ... ADD INDEX (a, b); ALTER TABLE ... ADD INDEX (b)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-01-28 15:43:29
Para el ejemplo que tiene el conjunto de índices #3 es óptimo. Mysql elegirá los índices A y B para las cláusulas where de una sola columna, y utilizará el índice compuesto para la cláusula A y B where.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-01-28 15:45:05