Índice de PostgreSQL

El índice es una consulta especial de tabla que acelera la búsqueda de datos en el motor de búsqueda. En términos simples, el índice es un puntero a los datos de la tabla. Un índice en una base de datos es muy similar al índice de un libro.

Para ilustrar con el índice de una página de directorio de un diccionario chino, podemos encontrar rápidamente la palabra necesaria mediante el orden alfabético, trazos, componentes parciales, etc.

Los índices ayudan a acelerar las consultas SELECT y las cláusulas WHERE, pero pueden ralentizar la entrada de datos utilizando las sentencias UPDATE y INSERT. Los índices pueden crearse o eliminarse, pero no afectan los datos.

Se crea un índice utilizando la sentencia CREATE INDEX, lo que permite nombrar el índice, especificar la tabla y la columna o columnas que se van a indexar, y señalar si el índice debe estar ordenado en forma ascendente o descendente.

索引也可以是唯一的，与 UNIQUE 约束类似，在列上或列组合上防止重复条目。

CREATE INDEX 命令

CREATE INDEX （创建索引）的语法如下：

CREATE　INDEX　index_name　ON　table_name;

索引类型

单列索引

单列索引是一个只基于表的一个列上创建的索引，基本语法如下：

CREATE　INDEX　index_name
ON　table_name　(column_name);

组合索引

组合索引是基于表的多列上创建的索引，基本语法如下：

CREATE　INDEX　index_name
ON　table_name　(column1_name,　column2_name);

不管是单列索引还是组合索引，该索引必须是在 WHEHE 子句的过滤条件中使用非常频繁的列。

如果只有一列被使用到，就选择单列索引，如果有多列就使用组合索引。

唯一索引

使用唯一索引不仅是为了性能，同时也为了数据的完整性。唯一索引不允许任何重复的值插入到表中。基本语法如下：

CREATE　UNIQUE　INDEX　index_name
on　table_name　(column_name);

局部索引

局部索引 是在表的子集上构建的索引；子集由一个条件表达式上定义。索引只包含满足条件的行。基础语法如下：

CREATE　INDEX　index_name
on　table_name　(conditional_expression);

隐式索引

隐式索引 是在创建对象时，由数据库服务器自动创建的索引。索引自动创建为主键约束和唯一约束。

在线示例

下面示例将在 COMPANY 表的 SALARY 列上创建索引：

#　CREATE　INDEX　salary_index　ON　COMPANY　(salary);

现在，用 　\d company 命令列出 COMPANY 表的所有索引：

#　\d　company

得到的结果如下，company_pkey 是隐式索引 ，是表创建表时创建的：

w3codeboxdb=#　\d　company
　　　　　　　　　　　　　　　　　　Table　"public.company"
　Column　　|　　　　　Type　　　　　　|　Collation　|　Nullable　|　Default　
---------+---------------+-----------+----------+---------
　id　　　　　　|　integer　　　　　　　|　　　　　　　　　　　|　not　null　|　
　name | texto | | | no nulo　
　age | entero | | | no nulo　
　address | character(|50) | | | |　
　salary | real | | |　
Índices:
　　　　"company_pkey" PRIMARY KEY, btree (id)
　　　　"salary_index" btree (salary)

Puede usar el comando \di para listar todos los índices de la base de datos:

w3codeboxdb=# \di
　　　　　　　　　　　　　　　　　　　　Lista de relaciones
　Esquema | Nombre | Tipo | Propietario | Tabla　　　　
--------+-----------------+-------+----------+------------
　public | company_pkey | índice | postgres | company
　public | department_pkey | índice | postgres | department
　public | salary_index | índice | postgres | company
(3　rows)

DROP INDEX (eliminar índice)

Un índice se puede eliminar con el comando DROP de PostgreSQL.

DROP INDEX index_name;

Puede usar la siguiente sentencia para eliminar el índice creado anteriormente:

# DROP INDEX salary_index;

Después de eliminar, se puede ver que salary_index ya ha sido eliminado de la lista de índices:

w3codeboxdb=# \di
　　　　　　　　　　　　　　　　　　　　Lista de relaciones
　Esquema | Nombre | Tipo | Propietario | Tabla　　　　
--------+-----------------+-------+----------+------------
　public | company_pkey | índice | postgres | company
　public | department_pkey | índice | postgres | department
(2　rows)

En qué situaciones se debe evitar el uso del índice?

Aunque el propósito del índice es mejorar el rendimiento de la base de datos, hay algunas situaciones en las que es necesario evitar su uso.

Al usar el índice, es necesario considerar las siguientes reglas:

• El índice no debería usarse en tablas pequeñas.

• El índice no debería usarse en tablas con actualizaciones o inserciones en grandes volúmenes frecuentes.

• El índice no debería usarse en columnas con una gran cantidad de valores NULL.

• El índice no debería usarse en columnas con operaciones frecuentes.