莱芜定制网站建设公司,wordpress卡慢,中国进入一级战备有多可怕?,网站销售怎么推广索引简介
索引是数据库中一种快速查询数据的方法。索引中记录了表中的一列或多列值与其物理位置之间的对应关系#xff0c;就好比一本书前面的目录#xff0c;通过目录中页码就能快速定位到我们需要查询的内容。
建立索引的好处是加快对表中记录的查找或排序#xff0c;但…索引简介
索引是数据库中一种快速查询数据的方法。索引中记录了表中的一列或多列值与其物理位置之间的对应关系就好比一本书前面的目录通过目录中页码就能快速定位到我们需要查询的内容。
建立索引的好处是加快对表中记录的查找或排序但建索引需要付出以下代价
增加了数据库的存储空间在插入和修改数据时要花费较多的时间因为索引也要随之更新
除了加快查询的作用外索引还有一些其他的用途如唯一索引还可以起到唯一约束的作用。
索引的分类
PG中支持以下几类索引
BTree:最常用的索引BTree适用于处理等值查询和范围查询HASH:只能处理简单的等值查询GiST:不是单独一种索引类型而是一种架构可以在这种架构上实现很多不同的索引策略SP-GiST:“Space-Partitioned GiST”的缩写即空间分区GiST索引。GIN:反转索引可以处理包含多个健的值如数组等它支持用户定义的索引策略可通过定义GIN索引的特定操作符类型实现不同的功能。PG的标准发布中包含了用于一维数组的GIN操作符类比如它支持包含操作符“”、被包含操作符“”、相等操作符“”、重叠操作符“”等等
创建索引
CREATE [UNIQUE] INDEX [CONCURRENTLY] [name] ON table_name [USING method]
( { column_name | (expression)} [COLLATE collation] [opclass] [ASC | DESC] [ NULLS {FIRST | LAST}] [,...])
[WITH (storage_parameter value [,...])]
[TABLESPACE tablespace_name]
[WHERE predicate]一般在创建索引的过程中会把表中的数据全部读一遍该过程所用时间由表的大小决定对于较大的表可能会花费很久的时间。在创建索引的过程中对表的查询可以正常运行但对表的增、删、改等操作需要等索引建完后才能进行。对此PG提供了并发创建索引的方法。
假设由一张联系人的表命令如下
CREATE TABLE contacts(id int primary key,name varchar(40),phone varchar(32)[],address text
);在该表中由于一个人可能有多个电话号码所以把“phone”定义为一个数组
为了实现按name快速查找可以在字段name上建一个简单的BTree索引命令如下
CREATE INDEX idx_contacts_name on contacts(name);如果像按电话号码phone字段快速查询比如查询某个电话号码是谁的由于此字段是一个数组前面所建的BTree索引将不再起作用这时可以建一个GIN索引命令如下
CREATE INDEX idx_contacts_phone on contacts using gin(phone);如果想要查询号码“15873135680”是谁的可以使用下面的查询语句
SELECT * FROM contacts WHERE phone array[15873135680::varchar(32)];HASH索引的更新不会记录到WAL日志中所以实际使用场景很少
创建索引可以指定存储参数“WITH(storage_paramter value)”常用的存储参数为FILLFACTOR比如可以这样创建索引
CREATE INDEX idx_contacts_name on contacts(name) WITH (FILLFACTOR 50);也可以按降序创建索引
CREATE INDEX idx_contacts_name on contacts(name desc);如果字段name中有空值则可以在创建索引时指定空值排在非空值前面
CREATE INDEX idx_contacts_name on contacts(name desc NULLS FIRST);也可以指定空值排在非空值后面
CREATE INDEX idx_contacts_name on contacts(name desc NULLS LAST);并发创建索引
通常情况下在创建索引的时候PG会锁定表以防止写入然后对表做全表扫描从而完成创建索引的操作。在此过程中其他用户仍然可以读取表但是插入、更新、删除等操作将一直被阻塞直到索引创建完毕。
如果这张表示更新较频繁且比较大的表那么创建索引可能需要几十分钟甚至数个小时这段时间内都不能做任何插入、删除、更新操作这在大多数的在线数据库中都是不可接受的。
所以PG支持在长时间阻塞更新的情况下建索引通过在CREATE INDEX中加CONCURRENTLY选项来实现。
该选项PG会执行表的两次扫描因此会需要更长的时间来建索引但是它还是很有用的。
并发创建索引测试 create table jxx_test(id int primary key,note text);insert into jxx_test select generate_series(1,5000000),generate_series(1,5000000); 同时开两个窗口一个窗口执行创建索引另一个窗口删除数据
删除操作在创建完索引之后才会执行图片上因为执行删除数据操作在创建索引发生之后才触发有时间差
继续同时开两个窗口一个窗口执行创建索引使用concurrently关键字另一个窗口删除数
创建索引时间虽然变长了但是删除数据的操作不受阻塞直接完成
并发重建索引
PG中重建索引不支持CONCURRENTLY选项但是PG中一个字段可以创建两个索引所以并发重建索引可以执行以下步骤
使用CONCURRENTLY选项建一个新的索引删除旧索引
无效索引
创建索引过程需要注意如果在创建索引过程中强行取消操作会留下一个无效的索引
仍然会导致更新速度变慢如果是唯一索引这个无效索引还会导致插入重复值失败 上述图片可以通过shell窗口输入\dtable的命令查看。
需要手动删除该索引
drop index idx_jxx_test_note;修改索引
alter index idx_jxx_test_note rename to idx_jxx_test_note_new;
