网站首页 > 知识剖析 正文
索引是数据库中一种快速查询数据的方法。索引中记录了表中一列或多列的值与其物理位置之间的对应关系,就好比试一本书正文面的目录,通过目录后面的页号能快速定位到需要查询的内容。
索引分类
在PostgreSQL中,支持以下几类索引:
- B-tree:最常用的索引,B-tree索引适合处理等值查询和范围查询
- Hash:智能处理简单的等值查询
- GiST:不是一种单独的索引类型,而是一种架构,可以在这种架构上实现很多不同的索引策略。GiST索引定义的特定操作符可以用于特定索引策略。PostgreSQL的标准发布中包含了用于二维几何数据类型的GiST操作符类。比如,一个图形包含另一个图形的操作符"@>",一个图形在另一个图形的左边且没有重叠的操作符"<<",等等。
- SP-GiST: SP-GiST是"space-partitioned GiST"的缩写,即空间分区GiST索引。它是从PostgreSQL9.2版本开始提高了一种新索引类型,主要是通过一些新的索引算法提高GiST索引在某个情况下的性能。
- GIN:反转索引,它可以处理包含多个键的值,如数组等。与GiST类似,GIN支持用户定义的索引策略,可通过定义GIN索引的特定操作符类型实现不同的功能。PostgreSQL的标准发布中包含了用于一维数组的GIN操作符类。比如,它支持包含操作符"@>",被包含操作符"<@",相等操作符"=",重叠操作符"&&".
创建索引
语法:
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON [ ONLY ] table_name [ USING method ]
( { column_name | ( expression ) } [ COLLATE collation ] [ opclass [ ( opclass_parameter = value [, ... ] ) ] ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
[ INCLUDE ( column_name [, ...] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) ]
[ TABLESPACE tablespace_name ]
[ WHERE predicate ]
一般在创建索引的过程中,会把表的数据都读一遍,这个过程所用时间由表的大小决定,对于比较大的表,可能会花很久的时间。
在创建索引的过程中,对表的查询可以正常运行,但对表的增删改等操作需要等索引建完后才能进行,为此PostgreSQL提供了一种并发创建索引的方法。
如何在不同的情况下创建索引?
假设有一个联系人的表
CREATE TABLE contacts(
id int primary key,
name varchar(40),
phone varchar(32)[],
address text
);
在该表中,由于一个人可能有多个电话号码,所以把phone定义为一个数组。
为了实现按姓名快速查询,可以在字段name上建一个简单的B-tree索引
CREATE INDEX idx_contacts_name on contacts(name);
插入数据
INSERT INTO contacts VALUES(1,'塞尔达',ARRAY['13422222221','13422222222'],'海拉鲁城堡');
INSERT INTO contacts VALUES(2,'林克',ARRAY['13411111111','13411111112'],'海拉鲁城堡');
INSERT INTO contacts VALUES(3,'英达',ARRAY['13433333333','13433333334'],'英拉村');
如果想按电话号码(phone)字段做快速查询,比如,想查询一个电话号码是谁的,由于此字段是一个数组,前面所建的B-tree索引不再起作用,因此这时可以建一个GIN索引
CREATE INDEX idx_contacts_phone on contacts using gin(phone);
这时就可进行快速查询了。假设想查询号码"13422222222"是谁的,查询
SELECT * FROM contacts WHERE phone @> array['13422222222'::varchar(32)];
注意 "@>"是数组操作符,表示包含。GIN索引能在"@>"上起作用。
Hash索引的更新不会记录到WAL日志中,索引在实际场景中应用较少。
创建索引时指定存储参数"WITH(storage_parameter=value)" ,常用的存储参数为FILLFACTOR。
CREATE INDEX idx_contacts_name_01 on contacts(name) WITH (FILLFACTOR=50);
也可以按降序建索引
CREATE INDEX idx_contacts_name_02 on contacts(name desc);
如果时字段name 中有空值,可以在建索引时,指定空值排在非空值前面
CREATE INDEX idx_contacts_name_03 on contacts(name DESC NULLS FIRST);
或空值排在非空值后面
CREATE INDEX idx_contacts_name_04 on contacts(name DESC NULLS LAST);
并发创建索引
通常,在创建索引的说话PostgreSQL会锁定表以防止写入,然后对表做全表扫描,从而完成创建索引操作。
在此过程中,其他用仍然可以读取表,但是插入、更新、删除等操作将被一直阻塞,知道索引创建完毕。如果这张表更新比较频繁,且表比较大,那么创建索引可能需要几十分钟,甚至数个小时,这段时间内都不能做任何的插入、删除、更新,这在大多数的在线数据库中都是不可接受的行尾。鉴于此,PostgreSQL支持不长时间阻塞更新的情况下创建索引,这是通过在CREATE INDEX中加CONCURRENTLY(并发创建索引)选项来实现的。当该选项被使用时,PostgreSQL会执行表的两次扫描,因此该方法需要更长一些的时间来建索引。尽管如此,这个选项也是很有用的一个功能。
测试表
CREATE TABLE test01(id int primary key, note text);
插入数据
INSERT INTO test01 select generate_series(1,500000), generate_series(1,500000);
这时开两个psql的窗口,在其中一个创建中建索引:
psql postgres
\timing
CREATE INDEX idx_test01_note on test01(note);
另一个窗口中删除一条数据,可以看到,它一直在等另一个窗口中创建索引的操作完成:
psql postgres
\timing
DELETE FROM test01 where id=1;
如果创建索引时加上"CONCURRENTLY"选项
DROP INDEX idx_test01_note;
CREATE INDEX CONCURRENTLY idx_test01_note on test01(note);
另一个窗口的删除操作不会出现等待
DELETE FROM test01 where id=2;
DELETE FROM test01 where id=3;
一个表经过频繁更新后,如果想重建其上的索引该怎么做?
要知道,在PostgreSQL中重建索引不支持"CONCURRENTLY"选项,但它支持在同个字段中建两个索引,因此可以考虑这样做:使用"CONCURRENTLY"选项建一个新的索引,
然后把旧索引删除掉,这样就相对于重建了这个索引
CREATE INDEX CONCURRENTLY idx_test01_note on test01(note);
CREATE INDEX CONCURRENTLY idx_test01_note_2 on test01(note);
DROP INDEX idx_test01_note;
db01=# \d test01
Table "public.test01"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | not null |
note | text | | |
Indexes:
"test01_pkey" PRIMARY KEY, btree (id)
"idx_test01_note_2" btree (note)
并发创建索引的时候需要注意,如果索引在创建过程中被强制取消,可能会留下一个无效索引,这个索引仍然会导致更新变慢。
如果创建的是唯一索引,这个无效的索引还会导致插入重复值失败,测试示例如下:
先在索引创建过程中取消操作
CREATE INDEX CONCURRENTLY idx_test01_note on test01(note);
^CCancel request sent
ERROR: canceling statement due to user request
然后使用\d查看表,可以看到遗留一个INVALID索引:
db01=# \d test01
Table "public.test01"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | not null |
note | text | | |
Indexes:
"test01_pkey" PRIMARY KEY, btree (id)
"idx_test01_note" btree (note) INVALID
"idx_test01_note_2" btree (note)
这时,若插入重复数据,此无效唯一索引的约束仍然有效,如下:
INSERT INTO test01 VALUES(10,'10');
ERROR: duplicate key value violates unique constraint "test01_pkey"
DETAIL: Key (id)=(10) already exists.
对于此,手工删除索引就可以了。
DROP INDEX idx_test01_note;
修改索引
语法:
ALTER INDEX name RENAME TO new_name
ALTER INDEX name SET TABLESPACE tablespace_name
例子:
给索引改名
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);
查看索引的信息
db01=# \d+ idx_contacts_name_old
Index "public.idx_contacts_name_old"
Column | Type | Key? | Definition | Storage | Stats target
--------+-----------------------+------+------------+----------+--------------
name | character varying(40) | yes | name | extended |
btree, for table "public.contacts"
删除所有
语法:
DROP INDEX name [, ...] [ CASCADE | RESTRICT ]
例子:
确认索引是否存在,若存在则删除,若不存在,将不报错
DROP INDEX IF EXISTS idx_contacts_name_old;
DROP INDEX IF EXISTS idx_contacts_name_old;
NOTICE: index "idx_contacts_name_old" does not exist, skipping
DROP INDEX
删除索引时,默认会使用选项"RESTRICT",如果有依赖对象依赖这个索引,则删除会失败。而使用"CASCADE"选项,表示当有依赖于这个索引的对象时,一并把这些对象删除掉,例如外键约束。
CREATE TABLE class(
class_no int,
class_name varchar(40)
);
CREATE UNIQUE INDEX index_unique_class_no ON class(class_no);
CREATE TABLE student(
student_no int primary key,
student_name varchar(40),
age int,
class_no int REFERENCES class(class_no)
);
如果表"student"上的外键引用了表"class"上的唯一索引"index_unique_class_no",这时删除此索引将会失败
DROP INDEX index_unique_class_no;
ERROR: cannot drop index index_unique_class_no because other objects depend on it
DETAIL: constraint student_class_no_fkey on table student depends on index index_unique_class_no
HINT: Use DROP ... CASCADE to drop the dependent objects too.
此时,加上CASCADE即可删除成功
DROP INDEX index_unique_class_no CASCADE;
猜你喜欢
- 2024-12-13 面试官:怎么优化sql?
- 2024-12-13 MySQL架构优化实战系列1:数据类型与索引调优全解析
- 2024-12-13 SQL Server创建索引
- 2024-12-13 浏览器里的本地数据库:IndexedDB
- 2024-12-13 「走进RDS」之RDS PostgreSQL索引推荐原理及最佳实践
- 2024-12-13 14. WPS表格使用JS宏查询报表明细之查询实现
- 2024-12-13 大数据从业者必知必会的Hive SQL调优技巧
- 2024-12-13 数据库 CTREATE INDEX 用法
- 2024-12-13 什么是数据库的索引?
- 2024-12-13 这次是真拯救了我,MySQL索引优化,explain讲得非常清楚了
- 最近发表
- 标签列表
-
- xml (46)
- css animation (57)
- array_slice (60)
- htmlspecialchars (54)
- position: absolute (54)
- datediff函数 (47)
- array_pop (49)
- jsmap (52)
- toggleclass (43)
- console.time (63)
- .sql (41)
- ahref (40)
- js json.parse (59)
- html复选框 (60)
- css 透明 (44)
- css 颜色 (47)
- php replace (41)
- css nth-child (48)
- min-height (40)
- xml schema (44)
- css 最后一个元素 (46)
- location.origin (44)
- table border (49)
- html tr (40)
- video controls (49)