一、索引介绍¶
索引是数据库中的一种快速查询数据的方法。索引中记录了表中的一列或多列值与其物理位置之间的对应关 系，就好比是一本书前面的目录，通过目录中页码就能快速定位到我们需要查询的内容。
索引的代价：
- 增加了数据库的存储空间
- 在插入和修改数据时要花费较多的时间，因为索引也要随之更新
索引其他用途： 除了加快查询以外，比如唯一索引还可以起到唯一约束的作用
二、索引的分类¶
BTree：最常用的索引，BTree索引适合用于处理等值查询和范围查询
HASH：只能处理简单的等值查询
GiST：不是单独一种索引类型，而是一种架构，可以在这种架构上实现很多不同的索引策略
SP-GiST：SP-GiST是“Space-Partitioned GiST”的缩写，即空间分区GiST索引。PostgreSQL9.2引入
GIN：反转索引，可以处理包含多个键的值
三、创建索引¶
案例：
CREATE TABLE contacts(
id int primary key,
name varchar(40),
phone varchar(32)[],
address text );
为了实现按姓名“name”快速查询，可以在字段“name”上建一个简单的BTree索引在name字段上创建索引：
CREATE INDEX idx_contacts_name on contacts(name);
如果想按电话号码“phone”字段做快速查询，比如，想查询某个电话号码是谁的，由于此字段是一个数组，前 面所建的BTree索引将不再起作用，这时可以建一个GIN索引
CREATE INDEX idx_contacts_phone on contacts using gin(phone);
想查询号码“13422334455”是谁的
SELECT * FROM contacts WHERE phone @> array['13422334455'::varchar(32)];
解释：“@>”是数组操作符，表示“包含”的意思，GIN索引能在“@>”上起作用
创建索引可以带上参数：
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);
3.1 并发创建索引¶
通常情况下默认方式创建索引代价比较大： 因为：在创建索引的时候PostgreSQL会锁定表以防止写入，然后对表做全表扫描，从而完成创建索引的操 作。在此过程中，其他用户仍然可以读取表，但是插入、更新、删除等操作将一直被阻塞，直到索引创建完毕
加索引问题案例：
创建一张测试表：
CREATE TABLE testtab01(id int primary key, note int);
插入测试数据：
INSERT INTO testtab01 select generate_series(1,5000000), generate_series(1,5000000);
这时开两个psql窗口，在其中一个窗口中建索引 一个窗口：
CREATE INDEX idx_testtab01_note on testtab01(note);
另一个窗口：
DELETE FROM testtab01 where id=1;
有没有不阻塞dml的加索引方式？
PostgreSQL支持在不长时间阻塞更新的情况下建立创建索引，在CREATE INDEX中加CONCURRENTLY选项来
优点：加索引过程中不会阻塞 dml语句
确定：由于要两次扫描表，所以耗时会长一些
案例：
其中一个窗口加索引
DROP INDEX idx_testtab01_note;
CREATE INDEX CONCURRENTLY idx_testtab01_note on testtab01(note);
另一个窗口的删除语句执行
DELETE FROM testtab01 where id=2;
DELETE FROM testtab01 where id=3;
如果一些场景下想要重建索引（因为统计信息不准确等），表更新很频繁，但是重建索引不支持 CONCURRENTLY选项，怎么做？
PostgreSQL中在同个字段中可以建两个索引，因此可以考虑这样做：使用CONCURRENTLY选项建一个新的 索引，然后把旧索引删除掉，这样就相当于重建了这个索引，
并发创建索引的坑：
索引可能会残留，什么意思？ 案例：
创建索引
CREATE INDEX CONCURRENTLY idx_testtab01_note on testtab01(note); 创建过程中取消
然后使用“\d”命令查看表，可以看到遗留了一个INVALID索引：
\d testtab01
如何处理：
这时，手动删除此索引就可以了
DROP INDEX idx_testtab01_note;
四、修改索引¶
给索引改名的命令如下：
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);
查看索引信息，命令如下：
\d+ idx_contacts_name_old
五、删除索引¶
如果索引“idx_contacts_name_old”存在则删除，如果不存在也不报错
DROP INDEX IF EXISTS idx_contacts_name_old;
删除索引时，默认使用选项“RESTRICT”，RESTRICT特性：如果有对象依赖该索引，则会删除失败
案例：
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;
解决办法：此时加上“CASCADE”即可删除成功
DROP INDEX index_unique_class_no CASCADE;
同时删除了外键约束关系