Clever Geek Handbook

DBA的SQL技巧。为数据库开发人员提供的即用型建议



当我以开发人员身份开始职业生涯时，我的第一份工作是DBA（数据库管理员，DBA）。在那些年中，甚至在AWS RDS，Azure，Google Cloud和其他云服务出现之前，DBA就有两种类型：



  • , . « », , .
  • : , , SQL. ETL- . , .


应用程序DBA通常是开发团队的一部分。他们对特定主题有深入的了解，因此通常只从事一个或两个项目。基础结构DBA通常是IT团队的一部分，可以同时处理多个项目。



我是应用程序数据库管理员



我从来没有冲动过备份或调整存储的冲动（我相信这很有趣！）。到目前为止，我想说的是，我是一位知道如何开发应用程序的数据库管理员，而不是了解数据库的开发人员。



在本文中，我将分享我在职业生涯中学到的一些数据库开发技巧。



内容：







仅更新需要更新的内容



该操作UPDATE消耗大量资源。加快速度的最佳方法是仅更新需要更新的内容。



这是一个规范化电子邮件列的请求示例： 



db=# UPDATE users SET email = lower(email);
UPDATE 1010000
Time: 1583.935 ms (00:01.584)


看起来很清白吧？该请求将更新1,010,000个用户的邮件地址。但是是否所有行都需要更新？ 



db=# UPDATE users SET email = lower(email)
db-# WHERE email != lower(email);
UPDATE 10000
Time: 299.470 ms


仅需要更新10,000行。通过减少处理的数据量，我们将执行时间从1.5秒减少到不到300ms。这还将为我们节省维护数据库的更多工作。





仅更新需要更新的内容。



这种大更新类型在数据迁移脚本中非常常见。下次编写这样的脚本时，请确保仅更新所需的内容。



禁用重负载的约束和索引



约束是关系数据库的重要组成部分：它们可以保持数据的一致性和可靠性。但是，所有东西都有自己的价格，而且在很多情况下，您必须在加载或更新大量行时付费。



让我们定义一个小的存储模式： 



DROP TABLE IF EXISTS product CASCADE;
CREATE TABLE product (
    id serial PRIMARY KEY,
    name TEXT NOT NULL,
    price INT NOT NULL
);
INSERT INTO product (name, price)
    SELECT random()::text, (random() * 1000)::int
    FROM generate_series(0, 10000);

DROP TABLE IF EXISTS customer CASCADE;
CREATE TABLE customer (
    id serial PRIMARY KEY,
    name TEXT NOT NULL
);
INSERT INTO customer (name)
    SELECT random()::text
    FROM generate_series(0, 100000);

DROP TABLE IF EXISTS sale;
CREATE TABLE sale (
    id serial PRIMARY KEY,
    created timestamptz NOT NULL,
    product_id int NOT NULL,
    customer_id int NOT NULL
);


它定义了不同类型的约束，例如“ not null”以及唯一约束。



要设置起点，让我们开始向表中添加sale外键 



db=# ALTER TABLE sale ADD CONSTRAINT sale_product_fk
db-# FOREIGN KEY (product_id) REFERENCES product(id);
ALTER TABLE
Time: 18.413 ms

db=# ALTER TABLE sale ADD CONSTRAINT sale_customer_fk
db-# FOREIGN KEY (customer_id) REFERENCES customer(id);
ALTER TABLE
Time: 5.464 ms

db=# CREATE INDEX sale_created_ix ON sale(created);
CREATE INDEX
Time: 12.605 ms

db=# INSERT INTO SALE (created, product_id, customer_id)
db-# SELECT
db-#    now() - interval '1 hour' * random() * 1000,
db-#    (random() * 10000)::int + 1,
db-#    (random() * 100000)::int + 1
db-# FROM generate_series(1, 1000000);
INSERT 0 1000000
Time: 15410.234 ms (00:15.410)


定义约束和索引后，将一百万行加载到表中大约需要15.4秒。



现在，首先，让我们将数据加载到表中，然后再添加约束和索引： 



db=# INSERT INTO SALE (created, product_id, customer_id)
db-# SELECT
db-#    now() - interval '1 hour' * random() * 1000,
db-#    (random() * 10000)::int + 1,
db-#    (random() * 100000)::int + 1
db-# FROM generate_series(1, 1000000);
INSERT 0 1000000
Time: 2277.824 ms (00:02.278)

db=# ALTER TABLE sale ADD CONSTRAINT sale_product_fk
db-# FOREIGN KEY (product_id) REFERENCES product(id);
ALTER TABLE
Time: 169.193 ms

db=# ALTER TABLE sale ADD CONSTRAINT sale_customer_fk
db-# FOREIGN KEY (customer_id) REFERENCES customer(id);
ALTER TABLE
Time: 185.633 ms

db=# CREATE INDEX sale_created_ix ON sale(created);
CREATE INDEX
Time: 484.244 ms


加载速度更快，为2.27秒。而不是15.4。加载数据后，创建索引和限制的时间更长，但是整个过程要快得多：3.1秒。而不是15.4。



不幸的是，在PostgreSQL中，不能对索引执行相同的操作，只能抛出并重新创建它们。在其他数据库（例如Oracle）中，您可以禁用和启用索引而无需重建。



UNLOGGED-



当您在PostgreSQL中更改数据时，更改将被写入预写日志（WAL）。它用于维护一致性，在恢复过程中快速重新索引以及维护复制。



通常需要写入WAL，但是在某些情况下，您可以选择退出WAL以加快处理速度。例如，在登台表的情况下。



中间表称为一次性表，其中存储用于实现某些过程的临时数据。例如，在ETL流程中，通常将CSV文件中的数据加载到登台表中，清除信息，然后将其加载到目标表中。在这种情况下，临时表是一次性使用的，在备份或副本中不使用。





未登录的表。



发生故障时不需要恢复并且在副本中不需要的登台表可以设置为UNLOGGED： 



CREATE UNLOGGED TABLE staging_table ( /* table definition */ );


注意：使用之前UNLOGGED，请确保您完全理解所有含义。



使用WITH和RETURNING实施整个流程



假设您有一个用户表，发现它包含重复数据： 



Table setup
db=# SELECT u.id, u.email, o.id as order_id
FROM orders o JOIN users u ON o.user_id = u.id;

 id |       email       | order_id
----+-------------------+----------
  1 | foo@bar.baz       |        1
  1 | foo@bar.baz       |        2
  2 | me@hakibenita.com |        3
  3 | ME@hakibenita.com |        4
  3 | ME@hakibenita.com |        5


用户haki benita注册了两次，并使用邮件ME@hakibenita.com和me@hakibenita.com。由于我们在将电子邮件地址输入表格中时并未规范化电子邮件地址，因此我们现在必须处理重复项。



我们需要：



  1. 以小写字母标识重复的地址，并将重复的用户彼此链接。
  2. 更新订单，以便它们仅引用重复项之一。
  3. 从表中删除重复项。


您可以使用登台表链接重复的用户： 



db=# CREATE UNLOGGED TABLE duplicate_users AS
db-#     SELECT
db-#         lower(email) AS normalized_email,
db-#         min(id) AS convert_to_user,
db-#         array_remove(ARRAY_AGG(id), min(id)) as convert_from_users
db-#     FROM
db-#         users
db-#     GROUP BY
db-#         normalized_email
db-#     HAVING
db-#         count(*) > 1;
CREATE TABLE

db=# SELECT * FROM duplicate_users;
 normalized_email  | convert_to_user | convert_from_users
-------------------+-----------------+--------------------
 me@hakibenita.com |               2 | {3}


中间表包含汇整之间的链接。如果具有标准化电子邮件地址的用户出现多次，我们将为其分配一个最小用户ID，并将所有重复项折叠到其中。其余的用户存储在array列中，对它们的所有引用都将更新。



使用中间表，我们将更新表中重复项的链接orders： 



db=# UPDATE
db-#    orders o
db-# SET
db-#    user_id = du.convert_to_user
db-# FROM
db-#    duplicate_users du
db-# WHERE
db-#    o.user_id = ANY(du.convert_from_users);
UPDATE 2


现在，您可以安全地从中删除重复项users： 



db=# DELETE FROM
db-#    users
db-# WHERE
db-#    id IN (
db(#        SELECT unnest(convert_from_users)
db(#        FROM duplicate_users
db(#    );
DELETE 1


请注意，我们使用了unnest函数来“转换”数组，该数组将每个元素转换为字符串。



结果： 



db=# SELECT u.id, u.email, o.id as order_id
db-# FROM orders o JOIN users u ON o.user_id = u.id;
 id |       email       | order_id
----+-------------------+----------
  1 | foo@bar.baz       |        1
  1 | foo@bar.baz       |        2
  2 | me@hakibenita.com |        3
  2 | me@hakibenita.com |        4
  2 | me@hakibenita.com |        5


很好，所有用户3（ME@hakibenita.com）实例都转换为用户2（me@hakibenita.com）。



我们还可以检查是否从表中删除了重复项users： 



db=# SELECT * FROM users;
 id |       email
----+-------------------
  1 | foo@bar.baz
  2 | me@hakibenita.com


现在我们可以摆脱登台表： 



db=# DROP TABLE duplicate_users;
DROP TABLE


可以，但是需要太长时间并且需要清洁！有没有更好的办法？



广义表表达式（CTE）



使用通用表表达式（也称为表达式）WITH，我们可以使用单个SQL表达式执行整个过程： 



WITH duplicate_users AS (
    SELECT
        min(id) AS convert_to_user,
        array_remove(ARRAY_AGG(id), min(id)) as convert_from_users
    FROM
        users
    GROUP BY
        lower(email)
    HAVING
        count(*) > 1
),

update_orders_of_duplicate_users AS (
    UPDATE
        orders o
    SET
        user_id = du.convert_to_user
    FROM
        duplicate_users du
    WHERE
        o.user_id = ANY(du.convert_from_users)
)

DELETE FROM
    users
WHERE
    id IN (
        SELECT
            unnest(convert_from_users)
        FROM
            duplicate_users
    );


我们创建了通用表表达式并重用了它，而不是登台表。



从CTE返回结果



在表达式中执行DML的优点之一WITH是，您可以使用RETURNING关键字从中返回数据。假设我们需要有关更新和删除的行数的报告： 



WITH duplicate_users AS (
    SELECT
        min(id) AS convert_to_user,
        array_remove(ARRAY_AGG(id), min(id)) as convert_from_users
    FROM
        users
    GROUP BY
        lower(email)
    HAVING
        count(*) > 1
),

update_orders_of_duplicate_users AS (
    UPDATE
        orders o
    SET
        user_id = du.convert_to_user
    FROM
        duplicate_users du
    WHERE
        o.user_id = ANY(du.convert_from_users)
    RETURNING o.id
),

delete_duplicate_user AS (
    DELETE FROM
        users
    WHERE
        id IN (
            SELECT unnest(convert_from_users)
            FROM duplicate_users
        )
        RETURNING id
)

SELECT
    (SELECT count(*) FROM update_orders_of_duplicate_users) AS orders_updated,
    (SELECT count(*) FROM delete_duplicate_user) AS users_deleted
;


结果： 



orders_updated | users_deleted
----------------+---------------
              2 |             1


这种方法的优点在于，整个过程由单个命令完成，因此无需管理事务，也不必担心在发生过程故障时刷新登台表。



警告：一位Reddit读者向我指出了通用表表达式中DML执行的可能不可预测的行为：



中的子表达式WITH彼此并与主查询并发执行。因此，当在WITH数据修改表达式中使用时，实际的更新顺序将是不可预测的。


这意味着您不能依赖于执行独立子表达式的顺序。事实证明，如上例所示，如果它们之间存在依赖关系，则可以在使用它们之前依赖于依赖子表达式的执行。



避免选择性低的列中的索引



假设您有一个注册过程，其中用户使用电子邮件地址登录。要激活您的帐户，您需要验证您的邮件。该表可能如下所示： 



db=# CREATE TABLE users (
db-#    id serial,
db-#    username text,
db-#    activated boolean
db-#);
CREATE TABLE


您的大多数用户都是公民意识的用户，他们使用正确的邮寄地址注册并立即激活帐户。让我们用用户数据填充表格，并假设90％的用户已被激活： 



db=# INSERT INTO users (username, activated)
db-# SELECT
db-#     md5(random()::text) AS username,
db-#     random() < 0.9 AS activated
db-# FROM
db-#     generate_series(1, 1000000);
INSERT 0 1000000

db=# SELECT activated, count(*) FROM users GROUP BY activated;
 activated | count
-----------+--------
 f         | 102567
 t         | 897433

db=# VACUUM ANALYZE users;
VACUUM


要查询已激活和未激活用户的数量，可以按列创建索引activated： 



db=# CREATE INDEX users_activated_ix ON users(activated);
CREATE INDEX


而且，如果您要求未激活的用户数，那么该数据库将使用索引： 



db=# EXPLAIN SELECT * FROM users WHERE NOT activated;
                                      QUERY PLAN
--------------------------------------------------------------------------------------
 Bitmap Heap Scan on users  (cost=1923.32..11282.99 rows=102567 width=38)
   Filter: (NOT activated)
   ->  Bitmap Index Scan on users_activated_ix  (cost=0.00..1897.68 rows=102567 width=0)
         Index Cond: (activated = false)


基地决定过滤器将返回102,567个项目，大约占表的10％。这与我们加载的数据一致，因此表做得很好。



但是，如果查询激活的用户数，则会发现数据库已决定不使用索引： 



db=# EXPLAIN SELECT * FROM users WHERE activated;
                          QUERY PLAN
---------------------------------------------------------------
 Seq Scan on users  (cost=0.00..18334.00 rows=897433 width=38)
   Filter: activated


当数据库不使用索引时，许多开发人员会感到困惑。为了解释为什么这样做，以下是：如果您需要读取整个表，是否会使用索引？



可能不是，为什么这是必要的？从磁盘读取非常昂贵，因此您需要尽可能少地读取。例如，如果表为10 MB，索引为1 MB，则要读取整个表，您将必须从磁盘读取10 MB。如果添加索引，您将获得11 MB。这很浪费。



现在让我们看一下PostgreSQL在我们的表上收集的统计信息： 



db=# SELECT attname, n_distinct, most_common_vals, most_common_freqs
db-# FROM pg_stats
db-# WHERE tablename = 'users' AND attname='activated';
------------------+------------------------
attname           | activated
n_distinct        | 2
most_common_vals  | {t,f}
most_common_freqs | {0.89743334,0.10256667}


PostgreSQL解析表时，发现column中activated有两个不同的值。t列中的值most_common_vals对应于列中的频率0.89743334，most_common_freqs值f对应于频率0.10256667。分析表后，数据库确定有89.74％的记录是激活用户，其余10.26％的用户未激活。



基于这些统计信息，PostgreSQL决定扫描整个表比假设90％的行都满足条件要好。数据库可以决定是否使用索引的阈值取决于许多因素，并且没有经验法则。





低选择性和高选择性色谱柱的索引。



使用部分索引



在上一章中，我们为一个布尔列创建了一个索引，该布尔列具有约90％的记录true（激活的用户）。



当我们询问活动用户数时，数据库未使用索引。当询问未激活的数量时，数据库使用了索引。



出现了一个问题：如果数据库不使用索引来过滤掉活动的用户，为什么我们要首先为它们建立索引？



在回答这个问题之前，让我们先按列activated查看完整索引的权重： 



db=# \di+ users_activated_ix

 Schema |      Name          | Type  | Owner | Table | Size
--------+--------------------+-------+-------+-------+------
 public | users_activated_ix | index | haki  | users | 21 MB


该索引重21 MB。仅供参考：用户表为65 MB。也就是说，指标重量约为基础重量的32％。话虽如此，我们知道约90％的索引内容不太可能被使用。



在PostgreSQL中，您只能在表的一部分上创建索引-所谓的部分索引： 



db=# CREATE INDEX users_unactivated_partial_ix ON users(id)
db-# WHERE not activated;
CREATE INDEX


我们使用一个表达式WHERE来约束索引覆盖的字符串。让我们检查一下是否可行： 



db=# EXPLAIN SELECT * FROM users WHERE not activated;
                                           QUERY PLAN
------------------------------------------------------------------------------------------------
 Index Scan using users_unactivated_partial_ix on users  (cost=0.29..3493.60 rows=102567 width=38)


很好，该数据库足够聪明，可以意识到我们在查询中使用的布尔表达式可能适用于部分索引。



这种方法还有另一个优点： 



db=# \di+ users_unactivated_partial_ix
                                 List of relations
 Schema |           Name               | Type  | Owner | Table |  Size
--------+------------------------------+-------+-------+-------+---------
 public | users_unactivated_partial_ix | index | haki  | users | 2216 kB


全列索引重21 MB，而部分索引只有2.2 MB。即10％，对应于表中未激活用户的比例。



始终加载排序的数据



这是我在解析代码时最常发表的评论之一。该建议不像其他建议那样直观，并且可能对生产率产生巨大影响。



假设您有一张特定销售量的巨大桌子： 



db=# CREATE TABLE sale_fact (id serial, username text, sold_at date);
CREATE TABLE


在ETL过程中的每个晚上，您都将数据加载到表中： 



db=# INSERT INTO sale_fact (username, sold_at)
db-# SELECT
db-#     md5(random()::text) AS username,
db-#     '2020-01-01'::date + (interval '1 day') * round(random() * 365 * 2) AS sold_at
db-# FROM
db-#     generate_series(1, 100000);
INSERT 0 100000

db=# VACUUM ANALYZE sale_fact;
VACUUM


为了模拟下载，我们使用随机数据。我们插入了10万行带有随机名称的行，其销售日期为2020年1月1日以及未来两年。



在大多数情况下，该表用于汇总销售报告。通常，他们按日期过滤以查看特定时期的销售额。为了加快范围扫描，让我们通过sold_at以下方式创建索引： 



db=# CREATE INDEX sale_fact_sold_at_ix ON sale_fact(sold_at);
CREATE INDEX


让我们看一下2020年6月获取所有销售额的请求的执行计划： 



db=# EXPLAIN (ANALYZE)
db-# SELECT *
db-# FROM sale_fact
db-# WHERE sold_at BETWEEN '2020-07-01' AND '2020-07-31';

                            QUERY PLAN
-----------------------------------------------------------------------------------------------
 Bitmap Heap Scan on sale_fact  (cost=108.30..1107.69 rows=4293 width=41)
   Recheck Cond: ((sold_at >= '2020-07-01'::date) AND (sold_at <= '2020-07-31'::date))
   Heap Blocks: exact=927
   ->  Bitmap Index Scan on sale_fact_sold_at_ix  (cost=0.00..107.22 rows=4293 width=0)
         Index Cond: ((sold_at >= '2020-07-01'::date) AND (sold_at <= '2020-07-31'::date))
 Planning Time: 0.191 ms
 Execution Time: 5.906 ms


多次运行请求以预热缓存后，执行时间稳定在6 ms的水平。



位图扫描



在执行方面，我们看到该库使用位图扫描。它分为两个阶段：



  • (Bitmap Index Scan)：基遍历整个索引，sale_fact_sold_at_ix并找到表中包含相关行的所有页面。
  • (Bitmap Heap Scan)：库读取包含相关字符串的页面，并找到满足条件的页面。


页面可以包含许多行。第一步，使用索引查找页面。第二阶段在页面中搜索行，因此Recheck Cond执行计划中的操作如下。



此时，许多DBA和开发人员都将四舍五入并转到下一个查询。但是有一种方法可以改善此查询。



索引扫描



让我们对数据加载进行一些小的更改。 



db=# TRUNCATE sale_fact;
TRUNCATE TABLE

db=# INSERT INTO sale_fact (username, sold_at)
db-# SELECT
db-#     md5(random()::text) AS username,
db-#     '2020-01-01'::date + (interval '1 day') * round(random() * 365 * 2) AS sold_at
db-# FROM
db-#     generate_series(1, 100000)
db-# ORDER BY sold_at;
INSERT 0 100000

db=# VACUUM ANALYZE sale_fact;
VACUUM


这次，我们加载了按排序的数据sold_at。



现在，相同查询的执行计划如下所示： 



db=# EXPLAIN (ANALYZE)
db-# SELECT *
db-# FROM sale_fact
db-# WHERE sold_at BETWEEN '2020-07-01' AND '2020-07-31';

                           QUERY PLAN
---------------------------------------------------------------------------------------------
 Index Scan using sale_fact_sold_at_ix on sale_fact (cost=0.29..184.73 rows=4272 width=41)
   Index Cond: ((sold_at >= '2020-07-01'::date) AND (sold_at <= '2020-07-31'::date))
 Planning Time: 0.145 ms
 Execution Time: 2.294 ms


经过几次运行，执行时间稳定在2.3ms。我们已经实现了约60％的可持续节省。



我们还看到这次数据库没有使用位图扫描，而是应用了“正常”索引扫描。为什么？



相关性



当数据库分析该表时，它将收集它可以获得的所有统计信息。参数之一是相关性：



行的物理顺序与列的值的逻辑顺序之间的统计相关性。如果该值约为-1或+1，则与减少相关值约为0相比，认为对列进行索引扫描更为有利，因为减少了对磁盘的随机访问。


如官方文档中所述，相关性是衡量磁盘上特定列中的值如何被``排序''的度量。





关联=1。



如果关联为1左右，则意味着页面以与表中的行大致相同的顺序存储在磁盘上。这很常见。例如，自动递增的ID往往具有接近1的相关性。显示创建行的日期和时间戳列也具有接近1



的相关性。如果相关性为-1，则页面以相反的列顺序排序。





相关性〜0.



如果相关性接近0，则意味着该列中的值与表中的页面顺序不相关或几乎不相关。



让我们回到sale_fact。当我们不预先排序就将数据加载到表中时，相关性如下所示： 



db=# SELECT tablename, attname, correlation
db-# FROM pg_stats
db=# WHERE tablename = 'sale_fact';

 tablename | attname  | correlation
-----------+----------+--------------
 sale      | id       |            1
 sale      | username | -0.005344716
 sale      | sold_at  | -0.011389783


自动生成的列ID的相关性为1.该列的sold_at相关性非常低：连续的值分散在整个表中。



当我们将排序后的数据加载到表中时，她计算了相关性： 



tablename | attname  |  correlation
-----------+----------+----------------
 sale_fact | id       |              1
 sale_fact | username | -0.00041992788
 sale_fact | sold_at  |              1


现在，相关性sold_at相等1。



那么为什么基数在相关性较低时使用位图扫描，而在相关性较高时使用索引扫描呢？



  • 当相关为1时，基准确定所请求范围的行可能在连续的页面中。然后最好使用索引扫描来读取多个页面。
  • 当相关接近于0时，基数确定所请求范围的行可能会散布在整个表中。然后，建议对包含所需行的那些页面使用位图扫描，然后再使用条件提取它们。


下次将数据加载到表中时，请考虑将请求多少信息并进行排序，以便索引可以快速扫描范围。



CLUSTER命令



按特定索引对磁盘上的表进行排序的另一种方法是使用CLUSTER命令。



例如： 



db=# TRUNCATE sale_fact;
TRUNCATE TABLE

-- Insert rows without sorting
db=# INSERT INTO sale_fact (username, sold_at)
db-# SELECT
db-#     md5(random()::text) AS username,
db-#     '2020-01-01'::date + (interval '1 day') * round(random() * 365 * 2) AS sold_at
db-# FROM
db-#     generate_series(1, 100000)
INSERT 0 100000

db=# ANALYZE sale_fact;
ANALYZE

db=# SELECT tablename, attname, correlation
db-# FROM pg_stats
db-# WHERE tablename = 'sale_fact';

  tablename | attname  |  correlation
-----------+-----------+----------------
 sale_fact | sold_at   | -5.9702674e-05
 sale_fact | id        |              1
 sale_fact | username  |    0.010033822


我们以随机顺序将数据加载到表中，因此相关性sold_at接近于零。



要通过来“重组”表sold_at，我们使用命令CLUSTER根据索引对磁盘上的表进行排序sale_fact_sold_at_ix： 



db=# CLUSTER sale_fact USING sale_fact_sold_at_ix;
CLUSTER

db=# ANALYZE sale_fact;
ANALYZE

db=# SELECT tablename, attname, correlation
db-# FROM pg_stats
db-# WHERE tablename = 'sale_fact';

 tablename | attname  | correlation
-----------+----------+--------------
 sale_fact | sold_at  |            1
 sale_fact | id       | -0.002239401
 sale_fact | username |  0.013389298


将表聚类后，相关性sold_at变为1。





CLUSTER命令。



注意事项：



  • 将表聚集在特定列上可能会影响另一列的相关性。例如，看一下通过进行聚类后ID的相关性sold_at。
  • CLUSTER 这是一项繁重且阻塞的操作，因此请勿将其应用于活动表。


由于这些原因，最好插入已经排序但不依赖的数据CLUSTER。



与BRIN高度相关的列索引



当涉及索引时，许多开发人员都会想到B树。但是PostgreSQL提供了其他类型的索引，例如BRIN：



BRIN设计用于处理非常大的表，其中某些列与表中的物理位置自然相关




BRIN代表块范围索引。根据文档，BRIN在高度相关的列上效果最佳。正如我们在前几章中所看到的，自动递增的ID和时间戳与表的物理结构自然相关，因此BRIN对其更有利。



在某些条件下，与可比的B树索引相比，BRIN在大小和性能方面可以提供更好的“物有所值”。





布林



BRIN是表中几个相邻页面中的值范围。假设我们在一列中有以下值，每个值都在单独的页面上： 



1, 2, 3, 4, 5, 6, 7, 8, 9


BRIN适用于相邻页面范围。如果指定了三个相邻的页面，则索引会将表划分为多个范围： 



[1,2,3], [4,5,6], [7,8,9]


对于每个范围，BRIN存储最小值和最大值： 



[1–3], [4–6], [7–9]


让我们使用此索引查找值5：



  • [1-3]-他当然不在。
  • [4-6]-也许在这里。
  • [7–9]-他当然不在。


使用BRIN，我们将搜索区域限制为4-6块。



让我们再举一个例子。让列中的值具有接近零的相关性，即它们不排序： 



[2,9,5], [1,4,7], [3,8,6]


索引三个相邻的块将为我们提供以下范围： 



[2–9], [1–7], [3–8]


让我们寻找值5：



  • [2-9]-可能在这里。
  • [1-7]-可能在这里。
  • [3-8]-可能在这里。


在这种情况下，索引根本不会缩小搜索范围，因此毫无用处。



了解pages_per_range



相邻页面的数量由参数确定pages_per_range。范围中的页面数会影响BRIN的大小和精度：



  • pages_per_range较小而准确度较低的索引将提供较大的价值。
  • 较小的值pages_per_range将给出较大且更准确的索引。


默认pages_per_range值为128。





具有较低pages_per_range的BRIN。



为了说明这一点，让我们创建一个范围为两页的BRIN，并查找值为5：



  • [1-2]-他当然不在。
  • [3–4]-他当然不在。
  • [5-6]-可能在这里。
  • [7-8]-他当然不在。
  • [9]-这里绝对不是。


对于两页范围，我们可以将搜索限制在块5和6。如果范围是三页，索引将搜索限制在块4、5和6。



两个索引之间的另一个区别是，当范围是三页时，我们需要存储三个范围，在一个范围内有两个页面的情况下，我们已经获得了五个范围，并且索引增加了。



创建BRIN



让我们来一张表，sales_fact并按列创建一个BRIN sold_at： 



db=# CREATE INDEX sale_fact_sold_at_bix ON sale_fact
db-# USING BRIN(sold_at) WITH (pages_per_range = 128);
CREATE INDEX


默认值为pages_per_range = 128。



现在让我们查询销售日期期间： 



db=# EXPLAIN (ANALYZE)
db-# SELECT *
db-# FROM sale_fact
db-# WHERE sold_at BETWEEN '2020-07-01' AND '2020-07-31';
                                    QUERY PLAN
--------------------------------------------------------------------------------------------
 Bitmap Heap Scan on sale_fact  (cost=13.11..1135.61 rows=4319 width=41)
   Recheck Cond: ((sold_at >= '2020-07-01'::date) AND (sold_at <= '2020-07-31'::date))
   Rows Removed by Index Recheck: 23130
   Heap Blocks: lossy=256
   ->  Bitmap Index Scan on sale_fact_sold_at_bix  (cost=0.00..12.03 rows=12500 width=0)
         Index Cond: ((sold_at >= '2020-07-01'::date) AND (sold_at <= '2020-07-31'::date))
 Execution Time: 8.877 ms


基准使用BRIN来获取日期期限，但这没什么有趣的...



优化pages_per_range



根据执行计划，数据库从页面中删除了23,130行，并使用索引找到了这些行。这可能表明我们为此索引指定的范围太大。让我们创建一个索引，该索引的范围是页面数量的一半： 



db=# CREATE INDEX sale_fact_sold_at_bix64 ON sale_fact
db-# USING BRIN(sold_at) WITH (pages_per_range = 64);
CREATE INDEX

db=# EXPLAIN (ANALYZE)
db- SELECT *
db- FROM sale_fact
db- WHERE sold_at BETWEEN '2020-07-01' AND '2020-07-31';
                                        QUERY PLAN
---------------------------------------------------------------------------------------------
 Bitmap Heap Scan on sale_fact  (cost=13.10..1048.10 rows=4319 width=41)
   Recheck Cond: ((sold_at >= '2020-07-01'::date) AND (sold_at <= '2020-07-31'::date))
   Rows Removed by Index Recheck: 9434
   Heap Blocks: lossy=128
   ->  Bitmap Index Scan on sale_fact_sold_at_bix64  (cost=0.00..12.02 rows=6667 width=0)
         Index Cond: ((sold_at >= '2020-07-01'::date) AND (sold_at <= '2020-07-31'::date))
 Execution Time: 5.491 ms


在范围内有64页的情况下，基址删除了使用索引发现的较少行-9434。这意味着它必须执行较少的I / O操作，并且查询的执行速度更快，大约为5.5 ms，而不是大约8.9。



让我们用不同的值测试索引pages_per_range：



pages_per_range 重新检查索引时删除了行
128 23130
64 9434
8 874
4 446
2 446


减少pages_per_range索引变得更加精确，并且从找到的页面中删除更少的行。



请注意，我们已经优化了非常具体的查询。这可以很好地说明问题，但在现实生活中最好使用能够满足大多数查询需求的值。



估计索引的大小



BRIN的另一个主要优点是其尺寸。在前面的章节中，我们为fieldsold_at创建了B树索引。它的大小是2224 KB。带有参数的BRIN大小pages_per_range=128仅为48 KB：小46倍。 



Schema |         Name          | Type  | Owner |   Table   | Size
--------+-----------------------+-------+-------+-----------+-------
 public | sale_fact_sold_at_bix | index | haki  | sale_fact | 48 kB
 public | sale_fact_sold_at_ix  | index | haki  | sale_fact | 2224 kB


BRIN的大小也会受到影响pages_per_range。例如，BRIN的pages_per_range=2重量为56 Kb，略大于48 Kb。



使索引“不可见”



PostgreSQL具有很酷的事务DDL功能。在与Oracle这些年来，我已经习惯于使用DDL命令，例如CREATE，DROP和在交易结束ALTER。但是在PostgreSQL中，您可以在事务内执行DDL命令，并且只有在提交事务后才能应用更改。



我最近发现，使用事务性DDL可以使索引不可见！当您想查看没有索引的执行计划时，这很有用。



例如，在一个表中，sale_fact我们在column上创建了一个索引sold_at。7月销售获取请求的执行计划如下所示： 



db=# EXPLAIN
db-# SELECT *
db-# FROM sale_fact
db-# WHERE sold_at BETWEEN '2020-07-01' AND '2020-07-31';
                                         QUERY PLAN
--------------------------------------------------------------------------------------------
 Index Scan using sale_fact_sold_at_ix on sale_fact  (cost=0.42..182.80 rows=4319 width=41)
   Index Cond: ((sold_at >= '2020-07-01'::date) AND (sold_at <= '2020-07-31'::date))P


要查看没有索引的计划的外观sale_fact_sold_at_ix，可以将索引放入事务中并立即回滚： 



db=# BEGIN;
BEGIN

db=# DROP INDEX sale_fact_sold_at_ix;
DROP INDEX

db=# EXPLAIN
db-# SELECT *
db-# FROM sale_fact
db-# WHERE sold_at BETWEEN '2020-07-01' AND '2020-07-31';
                                   QUERY PLAN
---------------------------------------------------------------------------------

 Seq Scan on sale_fact  (cost=0.00..2435.00 rows=4319 width=41)
   Filter: ((sold_at >= '2020-07-01'::date) AND (sold_at <= '2020-07-31'::date))

db=# ROLLBACK;
ROLLBACK


首先，让我们开始进行交易BEGIN。然后我们删除索引并生成执行计划。请注意，该计划现在使用全表扫描，就好像索引不存在一样。此时，交易仍在进行中，因此索引尚未删除。要完成事务而不删除索引，请使用命令将其回滚ROLLBACK。



让我们检查一下索引是否仍然存在： 



db=# \di+ sale_fact_sold_at_ix
                                 List of relations
 Schema |         Name         | Type  | Owner |   Table   |  Size
--------+----------------------+-------+-------+-----------+---------
 public | sale_fact_sold_at_ix | index | haki  | sale_fact | 2224 kB


其他不支持事务性DDL的数据库可能会以不同方式实现目标。例如，Oracle允许您将索引标记为不可见，然后优化器将忽略它。



警告：如果你删除一个事务中的索引，它会导致竞争的业务的阻塞SELECT，INSERT，UPDATE并DELETE在表中，直到交易活跃。在测试环境中请谨慎使用，并避免在生产设施中使用。



不要安排很长的流程在任何时候开始



投资者知道，当股价达到漂亮的整数值时，可能会发生奇怪的事情，例如10美元，100美元，1000美元。这是他们写的：



资产价格可能会发生不可预测的变化，跨越每股50美元或100美元的整数值。许多经验不足的交易者喜欢在价格达到整数时购买或出售资产，因为他们认为这是公平价格。


从这个角度来看，开发商与投资者并没有太大的不同。当他们需要安排一个漫长的过程时，通常会选择一个小时。





典型的通宵系统负载。



在这几个小时内，这可能导致负载峰值。因此，如果您需要安排一个较长的过程，则系统在其他时间将处于空闲状态的可能性更大。



还建议在时间表中使用随机延迟，以免每次都在同一时间开始。这样，即使这个小时安排了另一个任务，也不会有什么大问题。如果您使用systemd计时器，则可以使用RandomizedDelaySec选项。



结论



本文根据我的经验提供了不同程度的证据提示。有些易于实现，有些则需要对数据库的工作方式有深入的了解。数据库是大多数现代系统的基础，因此花时间学习如何工作对于任何开发人员来说都是一笔不错的投资！


More articles:


All Articles