记录每一次成长

简介

索引是数据库中一种快速查询数据的方法。

不建议建索引的情况

表非常小（几百行）：顺序扫描比索引更快
字段的选择性差（如性别、状态）：扫描代价不值得
高并发写入系统（日志、事件流）：索引严重拖慢插入性能
查询频率极低、临时字段：建了浪费

创建索引

创建索引的过程中会把表中的数据全部读一遍，这个过程由表大小决定
创建索引的过程中，查询可以正常进行，增、删、改需要等索引建完后才能进行(可以并发创建索引)

sql


# 联系人表
create table contacts (
  id int primary key,
  name varchar(40),
  phone varchar(32)[],
  address text
);


# 创建索引 - 在name上创建一个Btree索引
create index idx_contacts_name on contacts(name);

# 使用 会直接精准定位到行，不用全表扫描了
select * from contacts where name = '李四';

# 创建索引 - 在phone创建一个GIN索引
create index idx_contacts_phone on contacts using gin(phone);

# 使用：查询号码1234是谁
# @>是数组操作符，表示包含的意思
# 后面是数组构造表达式，表示创建了一个包含'1234'（类型为varchar(32)）的数组
select * from contacts where phone @> array['1234'::varchar(32)];


# 指定存储参数
# 写多、频繁更新（如订单、日志）	 50～80
# 写少、读多（如国家、省份表）	   90～100（默认即可）
# 唯一索引、主键、B-tree 索引	    60～80（视数据量而定）
create index idx_contacts_name on contacts(name) with (fillfactor=50);

并发创建索引

可以不长时间阻塞更新，会执行2次表扫描，所以创建索引的时间更长

sql


# 创建索引时使用concurrently
create index concurrently idx_contacts_name on contacts(name);

# 重建索引不能使用concurrently
# 可以通过新建索引（同一个字段可以创建多个索引），新建索引，然后删除旧索引
# 并发创建索引时中途取消，会遗留无效索引，需要删除这个无效索引

修改索引

sql


# 索引改名
alter index idx_contacts_name rename to idx_contacts_name_old;

# 把索引移到表空间'tbs_data01'下
alter index idx_contacts_name_old set tablespace tbs_data01;

# 把索引的填充因子'fillfactor'设置为50
alter index idx_contacts_name_old set (fillfactor = 75);

# 把索引的填充因子重置为默认值
alter index idx_contacts_name_old reset (fillfactor);

删除索引

sql


# 如果有对象依赖该索引，会删除失败
drop index if exists idx_contacts_name_old;

# 将依赖索引的对象一并删除
drop index index_unique_class_no cascade;

简介 ​

分类 ​

不建议建索引的情况 ​

创建索引 ​

并发创建索引 ​

修改索引 ​

删除索引 ​

简介

分类

不建议建索引的情况

创建索引

并发创建索引

修改索引

删除索引