postgresql - stable - ¿Son los índices únicos mejores para el rendimiento de búsqueda de columna?(PGSQL y MySQL)

prosgress

Question

Tengo curiosidad por saber si

CREATE INDEX idx ON tbl (columns);

contra

CREATE UNIQUE INDEX idx ON tbl (columns);

tiene un beneficio de rendimiento algorítmico significativo en las implementaciones de PostgreSQL o MySQL al escanear la (s) columna (s) indexada (s), o si la palabra clave UNIQUE simplemente introduce una restricción única junto al índice.

Me imagino que es probable que sea justo decir que existe un beneficio marginal en la medida en que es probable que los índices se implementen internamente como una especie de estructura similar a ^un hash ¹ , y el manejo de colisiones, por definición, resulta en algo distinto del rendimiento O (1). Dada esta premisa, es probable que si un gran porcentaje de valores sea idéntico a la estructura degenere en algo lineal.

Entonces, para los propósitos de mi pregunta, supongamos que la distribución de valores es relativamente discreta y uniforme.

¡Gracias por adelantado!

^{1 Lo que es una cuestión de pura especulación para mí, ya que no estoy familiarizado con los aspectos internos de RDBM.}

Answer 1

Bueno, generalmente los índices son B-Trees, no hashes (hay índices basados ​​en hash, pero el índice más común (al menos en PostgreSQL) se basa en B Tree).

En cuanto a la velocidad, la velocidad única debe ser más rápida: cuando la exploración de índice encuentra una fila con un valor determinado, no tiene que buscar si hay otras filas con este valor, y puede finalizar la exploración de forma inmediata.

Answer 2

Hay una pequeña penalización durante las operaciones de actualización / inserción por tener la restricción única. Tiene que buscar antes de la operación de inserción / actualización para asegurarse de que no se viole la restricción de unicidad.

Answer 3

Si sus datos son únicos, debe crear un índice UNIQUE en ellos.

Esto no implica una sobrecarga adicional y afecta las decisiones del optimizador en ciertos casos para que pueda elegir un mejor algoritmo.

En SQL Server y en PostgreSQL , por ejemplo, si ordena en una clave UNIQUE , el optimizador ignora las cláusulas ORDER BY que se usan después (ya que son irrelevantes), es decir, esta consulta:

SELECT *
FROM mytable
ORDER BY
col_unique, other_col
LIMIT 10

utilizará un índice en col_unique y no ordenará en other_col porque es inútil.

Esta consulta:

SELECT *
FROM mytable
WHERE mycol IN
(
SELECT othercol
FROM othertable
)

también se convertirá en un INNER JOIN (en lugar de un SEMI JOIN ) si hay un índice UNIQUE en othertable.othercol .

Un índice siempre contiene algún tipo de puntero a la fila ( ctid en PostgreSQL , puntero de fila en MyISAM , clave principal / uniquificador en InnoDB ) y las hojas están ordenadas en estos punteros, por lo que de hecho cada hoja de índice es única de alguna manera ( aunque puede no ser obvio).

Ver este artículo en mi blog para detalles de rendimiento:

• Haciendo un índice UNIQUE