【数据库学习】Postgresql && 索引

1，概念

索引中记录了表中一列或多列值与其物理位置之间的对应关系。
索引建立后，系统在存取数据时会自动选择合适的索引作为存取路径，用户不能显式的选择索引。

1）优缺点

1>缺点

1. 增加了数据库的存储空间；
2. 在增删改数据时要花费较多的时间(因为索引也要随之变动)。

2>优点

a) 唯一索引进行唯一性约束

通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

b) 加快检索效率

c) 加快表之间的连接速度

特别是在实现数据的参考完整性方面特别有意义。

怎么理解呢？现在有A、B两个表，A表数据量1000w，B表数据2000w，关联字段是ID。对于全表搜索，不管建不建索引，速度都会非常慢。 若有其他过滤条件，且最后只返回少量数据，才适合使用索引。比如：

select A.*,B.*
FROM A  JOIN B ON A.ID = B.ID
WHERE A.ID >= 135 and A.ID <=160

假如只返回20条数据，那么整个执行过程：（可能存在谓词推导）
先用过滤条件 A.ID >= 135 and A.ID <=160 在A的索引种查找符合要求的数据，从1000w条数据中，过滤出20条数据（ID不一定是主键）；
然后，对于在A表中找到的每一条记录，遍历1次B表的索引，因为在A表中找到20条记录，所以一共遍历20次，每一次遍历，都是在B表的索引中查找，速度极快。

d) 加快查询中分组和排序

在使用group by 和order by 检索数据时，显著减少查询中分组和排序的时间。

e) 优化隐藏器

通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。
优化隐藏就是指在执行查询语句、使用多表连接检索或者指定查询语句操作的对象表时，明确地指出应该使用的查询方法、连接算法或者对表的操作方式。

2）唯一索引（唯一约束作用）

在表上一个或者多个字段组合建立的索引，且要求这个索引不可重复。
MySQL 在处理主键约束以及唯一性约束时，考虑周全。数据库用户创建主键约束的同时， MySQL 自动创建主索引（ primary index ），且索引名称为 Primary ；数据库用户创建唯一性索引时， MySQL 自动创建唯一性索引（ unique index ），默认情况下，索引名为唯一性索引的字段名。

3）聚集索引（聚簇索引）

在mysql中，聚集索引和非聚集索引都是B+树实现的。
InnoDB一定有主键，主键一定是聚簇索引、唯一索引。
MyISM没用聚簇索引。
如果涉及到大数据量的排序、全表扫描、count之类的操作（需要把索引等数据都拿内存中），MyISM索引占用内存较少，更占优势。

postgres没有聚集索引的概念。

1>概念

将数据与索引存放在一起，并按一定顺序存储的。数据顺序与索引数据一致。===》找到了索引就找到了数据，只要索引相邻，那么数据也相邻。
一个表只能有一个聚集索引，一般是主键索引。

非聚集索引：
B+树的叶子结点不存数据，存的是数据行地址。==》根据索引找到数据行地址，再去磁盘查数据。

辅助索引：
正常的查找操作：先通过辅助索引查找到对应的主键id，然后通过聚集索引拿到真正的数据。==》会二次查找
非聚集索引都是辅助索引。

2>优缺点

优点

1. 查询效率高（非覆盖索引情况下）；

覆盖索引：查询的数据刚好是索引的内容
只查索引就能查到所有的数据。不需要二次回表查询数据。
如：select id from tb_1

2. 范围查询效率高；
3. 适合用在sort by的场合。

缺点

对表进行修改速度较慢，这是为了保持表中的记录的物理顺序与索引的顺序一致，而把记录插入到数据页的相应位置，必须在数据页中进行数据重排，降低了执行速度。在插入新记录时数据文件为了维持B+Tree 的特性而频繁的分裂调整，十分低效。

1. 维护索引代价昂贵。
   插入数据需要重排。
   建议：大量插入新数据时，选用负载较低的时间段，通过OPTIMIZE_TABLE 优化表，因为必须被移动行数据可能造成碎片。 使用独享表空间可以弱化碎片。

2. 使用UUID（随机ID）作为主键，可能会让数据存储稀疏，导致全表扫描更慢。
   建议使用自增int作为主键。

3. 如果主键比较大，占用空间较大，会导致辅助索引更大。
   因为辅助索引保存主键并通过聚集索引找到具体的数据。

3>聚集索引和非聚集索引的区别？

1. 根本区别：表中记录的物理顺序和索引的排列顺序是否一致。
2. 都采用了B+树的结构，但非