索引是加速搜索引擎检索数据的一种特殊表查询。简单地说，索引是一个指向表中数据的指针。可以将索引类比为一本书的索引目录。索引有助于加快 SELECT 查询和 WHERE 子句，但它会减慢使用 UPDATE 和 INSERT 语句时的数据输入。索引可以创建或删除，但不会影响数据。
使用 CREATE INDEX 语句创建索引，它允许命名索引，指定表及要索引的一列或多列，并指示索引是升序排列还是降序排列。
索引也可以是唯一的，与 UNIQUE 约束类似，在列上或列组合上防止重复条目。

1.创建命令

  1. CREATE INDEX index_name ON table_name;
  3. --使用CREATE INDEX来创建后面可以指定索引的名称，以及要索引的列

2.索引类型

2.1单列索引

  1. --单列索引是一个只基于表的一个列上创建的索引，基本语法如下：
  2. CREATE INDEX index_name
  3. ON table_name (column_name);

2.2组合索引

  1. --组合索引是基于表的多列上创建的索引，基本语法如下：
  2. CREATE INDEX index_name
  3. ON table_name (column1_name, column2_name);

不管是单列索引还是组合索引，该索引必须是在 WHERE 子句的过滤条件中使用非常频繁的列。
如果只有一列被使用到，就选择单列索引，如果有多列就使用组合索引。

2.3唯一索引

  1. --使用唯一索引不仅是为了性能，同时也为了数据的完整性。
  2. --唯一索引不允许任何重复的值插入到表中。
  3. --基本语法如下：
  4. CREATE UNIQUE INDEX index_name
  5. on table_name (column_name);

2.4局部索引

  1. --局部索引是在表的子集上构建的索引；
  2. --子集由一个条件表达式上定义。索引只包含满足条件的行。
  3. --基础语法如下：
  4. CREATE INDEX index_name
  5. on table_name (conditional_expression);

2.5隐式索引

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

3.示例

  1. --在company表的salary列上创建一个索引
  2. runoobdb=# CREATE INDEX salary_index ON company(salary);
  3. CREATE INDEX

4.查看索引

  1. --查看某个表中的所有索引
  2. --查看company表中的所有索引
  3. runoobdb=# \d company
  4.                数据表 "public.company"
  5.   栏位   |     类型      | 校对规则 |  可空的  | 预设
  6. ---------+---------------+----------+----------+------
  7.  id      | integer       |          | not null |
  8.  name    | text          |          | not null |
  9.  age     | integer       |          | not null |
  10.  address | character(50) |          |          |
  11.  salary  | real          |          |          |
  12. 索引：
  13.     "company_pkey" PRIMARY KEY, btree (id) --这是个隐式索引，是创建表时自动创建的
  14.     "salary_index" btree (salary)
  16. --查看数据库的所有索引
  17. runoobdb=# \di
  18.                           关联列表
  19.  架构模式 |       名称       | 类型 |  拥有者  |   数据表
  20. ----------+------------------+------+----------+-------------
  21.  public   | company1_pkey    | 索引 | postgres | company1
  22.  public   | company3_age_key | 索引 | postgres | company3
  23.  public   | company3_pkey    | 索引 | postgres | company3
  24.  public   | company4_pkey    | 索引 | postgres | company4
  25.  public   | company5_pkey    | 索引 | postgres | company5
  26.  public   | company6_pkey    | 索引 | postgres | company6
  27.  public   | company7_pkey    | 索引 | postgres | company7
  28.  public   | company_pkey     | 索引 | postgres | company
  29.  public   | department1_pkey | 索引 | postgres | department1
  30.  public   | department_pkey  | 索引 | postgres | department
  31.  public   | salary_index     | 索引 | postgres | company
  32. (11 行记录)

5.删除索引

  1. --一个索引可以使用DROP命令删除
  2. DROP INDEX index_name;
  4. --删除前面我们创建的索引
  5. runoobdb=# DROP INDEX salary_index;
  6. DROP INDEX
  8. --查看一下发现只剩下一个隐式索引了
  9. runoobdb=# \d company
  10.                数据表 "public.company"
  11.   栏位   |     类型      | 校对规则 |  可空的  | 预设
  12. ---------+---------------+----------+----------+------
  13.  id      | integer       |          | not null |
  14.  name    | text          |          | not null |
  15.  age     | integer       |          | not null |
  16.  address | character(50) |          |          |
  17.  salary  | real          |          |          |
  18. 索引：
  19.     "company_pkey" PRIMARY KEY, btree (id)

6.什么时候避免使用索引

使用索引时，需要考虑下列准则：

  • 索引不应该使用在较小的表上。
  • 索引不应该使用在有频繁的大批量的更新或插入操作的表上。
  • 索引不应该使用在含有大量的 NULL 值的列上。
  • 索引不应该使用在频繁操作的列上。