2.2 简单的索引

创建索引

发动更多工作进程来扫描非常大的表有时候并不能解决问题。读取整个表只为寻找一行通常也不是什么好主意。因此，我们需要创建索引（PostgreSQL使用Lehman-Yao的高并发性B-树作为标准索引）：

CREATE INDEX
    idx_id
ON
    t_test (id);


SELECT
    *
FROM
    t_test
WHERE
    id = 432332;
    
   id   | name
--------+------
 432332 | hans
(1 row)

Time: 0.649 ms

postgres=# \di+

                               List of relations
 Schema |      Name       | Type  |  Owner   |   Table    | Size  | Description
--------+-----------------+-------+----------+------------+-------+-------------
 public | idx_id          | index | postgres | t_test     | 86 MB |
(1 rows)

我们的索引包含4百万行，用掉86MB空间。对该表的写入会变慢，因为索引必须一直被保持同步。

使用排序输出

B-树索引并非只对查找行有用。它们也可用来给下一阶段处理提供排序好的数据：

 EXPLAIN
 SELECT
     *
 FROM
     t_test
 ORDER BY
     id DESC
 LIMIT 10;
 
                                          QUERY PLAN

--------------------------------------------------------------------------------
---------------
 Limit  (cost=0.43..0.74 rows=10 width=9)
   ->  Index Scan Backward using idx_id on t_test
         (cost=0.43..125505.43 rows=4000000 width=9)
(2 rows)

在这种情况中，索引已经按正确的顺序返回了数据，因此不需要再对整个数据集排序。读取索引的最后10行就足够了。

min()和max()函数也都与排序有关，因此索引也可以用来加速这两个操作。

EXPLAIN
SELECT
    min(id),
    max(id)
FROM
    t_test;

                                                     QUERY PLAN

--------------------------------------------------------------------------------
-------------------------------------
 Result  (cost=0.93..0.94 rows=1 width=8)
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.43..0.46 rows=1 width=4)
           ->  Index Only Scan using idx_id on t_test
                 (cost=0.43..135505.43 rows=4000000 width=4)
                 Index Cond: (id IS NOT NULL)
   InitPlan 2 (returns $1)
     ->  Limit  (cost=0.43..0.46 rows=1 width=4)
           ->  Index Only Scan Backward using idx_id on t_test t_test_1
                 (cost=0.43..135505.43 rows=4000000 width=4)
                 Index Cond: (id IS NOT NULL)
(9 rows)

一次使用多个索引

PostgreSQL允许在单个查询中使用多个索引。当然，如果同时有很多列被查询这才有意义。但并非总是如此，也可能会发生同一个索引被多次使用来处理同一列的情况。

EXPLAIN
SELECT
    *
FROM
    t_test
WHERE
    id = 30
    OR id = 50;
    
                                QUERY PLAN
---------------------------------------------------------------------------
 Bitmap Heap Scan on t_test  (cost=8.88..16.85 rows=2 width=9)
   Recheck Cond: ((id = 30) OR (id = 50))
   ->  BitmapOr  (cost=8.88..8.88 rows=2 width=0)
         ->  Bitmap Index Scan on idx_id  (cost=0.00..4.44 rows=1 width=0)
               Index Cond: (id = 30)
         ->  Bitmap Index Scan on idx_id  (cost=0.00..4.44 rows=1 width=0)
               Index Cond: (id = 50)
(7 rows)

首先该查询会找30，然后又会找50，PostgreSQL将会进行所谓的位图扫描。

在位图扫描背后的思想是：PostgreSQL将首先扫描第一个索引，收集含有数据的块的一个列表。然后第二个索引被扫描并且同样得到一个块列表。在OR的情况下，这些列表会被统一，留给我们一个由含有数据的块构成的大列表。最后才会使用这个列表来扫描表以检索出那么块。

现在的问题是：PostgreSQL已经检索出的数据比需要的更多。在我们的情况中，查询将查找两行，但是位图扫描返回的是几个块。因此，执行器将进行复查来过滤掉不满足条件的行。

有效地使用位图扫描

什么时候一个位图扫描最有效并且什么时候优化器才会选择它？实际上有两种用例：

• 避免反复地使用同一个块；
• 组合相对不太好的条件。

第一种情况相当普遍。假定用户正在查找讲一种特定语言的每一个人，假设所有人中有10%讲这种语言。扫描索引意味着表中的一个块必须被一次又一次地扫描，因为很多说这种语言的人可能被存储在同一块中。通过应用一个位图扫描，可以确保一个特定的块只被使用一次，这当然能够带来更好的性能。

第二种情况常见于将相对较弱的条件用在一起。假设要找年龄介于20 ~ 30岁且拥有一件黄色衬衫的每一个人。现在，可能有15%介于20 ~ 30岁，并且15%拥有一件黄色衬衫。顺序扫描一个表是非常昂贵的，因此PostgreSQL可能会决定选择两个索引，因为最终的结果可能只由1%的数据构成。扫描这两个索引可能比读取所有数据要划算。

以一种聪明的方式使用索引

索引在某些情况下也会相对没有意义。

比如，在t_test表中已经有一个索引覆盖了id列。在下一步中将查询name列，先在name上创建一个索引：

CREATE INDEX
    idx_name
ON
    t_test (name);

可以看到，PostgreSQL将决定使用该索引。

EXPLAIN
SELECT
    *
FROM
    t_test
WHERE
    name = 'hans2';
    
                              QUERY PLAN
-----------------------------------------------------------------------
 Index Scan using idx_name on t_test  (cost=0.43..4.45 rows=1 width=9)
   Index Cond: (name = 'hans2'::text)
(2 rows)

但我们查询的是hans2，记住：hans2在表中不存在，并且查询计划反映了这一点。rows=1表示计划器只期待该查询返回数据的一个非常小的子集。在表中一个行也没有，但是PostgreSQL从不会估计出零行，因为那会让后续的估计变得困难。

如果我们查找更多数据，会发生什么：

EXPLAIN
SELECT
    *
FROM
    t_test
WHERE
    name = 'hans'
    OR name = 'paul';

                           QUERY PLAN
----------------------------------------------------------------
 Seq Scan on t_test  (cost=0.00..81622.00 rows=3000051 width=9)
   Filter: ((name = 'hans'::text) OR (name = 'paul'::text))
(2 rows)

在这种情况下，PostgreSQL将使用一个直截了当的顺序扫描。为什么系统忽略了所有的索引？这是因为：hans和paul组成了整个数据集。因此，PostgreSQL认为整个表无论怎样都必须被读取。如果只需要读取表就足够，那么就没有理由去读取索引。

换句话说，PostgreSQL并不会只因为有一个索引而使用它。PostgreSQL会在索引有意义时使用。如果行数较小，PostgreSQL将再次考虑位图扫描和普通索引扫描：

EXPLAIN
SELECT
    *
FROM
    t_test
WHERE
    name = 'hans2'
    OR name = 'paul2';
    
                                 QUERY PLAN
-----------------------------------------------------------------------------
 Bitmap Heap Scan on t_test  (cost=8.88..12.89 rows=1 width=9)
   Recheck Cond: ((name = 'hans2'::text) OR (name = 'paul2'::text))
   ->  BitmapOr  (cost=8.88..8.88 rows=1 width=0)
         ->  Bitmap Index Scan on idx_name  (cost=0.00..4.44 rows=1 width=0)
               Index Cond: (name = 'hans2'::text)
         ->  Bitmap Index Scan on idx_name  (cost=0.00..4.44 rows=1 width=0)
               Index Cond: (name = 'paul2'::text)
(7 rows)

这里要学习的最重要的一点是执行计划取决于输入值。它们不是静态的并且依赖于表中的数据。

使用只用索引的扫描

-- 使用一个索引来寻找一个行
EXPLAIN
SELECT
    *
FROM t_test
WHERE
    id = 34234;
    
                             QUERY PLAN
---------------------------------------------------------------------
 Index Scan using idx_id on t_test  (cost=0.43..8.45 rows=1 width=9)
   Index Cond: (id = 34234)
(2 rows)


-- 只用索引的扫描
EXPLAIN
SELECT 
    id
FROM 
    t_test
WHERE
    id = 34234;
    
                                QUERY PLAN
--------------------------------------------------------------------------
 Index Only Scan using idx_id on t_test  (cost=0.43..8.45 rows=1 width=4)
   Index Cond: (id = 34234)
(2 rows)

在例子中，id列已经被建立索引，因此它的内容很自然地存在于该索引中。如果所有的数据已经可以从索引中取出，那么在大部分情况下就没有必要去表中取。（几乎）只有额外的列被查询时，才需要去表中取数据。因此，只用索引的扫描将会带来比普通索引扫描明显更好的性能。