阮胜昌的技术记录站-LinuxMySQL数据库运维

LIKE和ILIKE是SQL的两个基本功能。人们在他们的应用程序中到处使用这些东西，因此从性能的角度来看这个主题是有意义的。PostgreSQL可以采取哪些措施来加快这些操作的速度，通常可以采取哪些措施要首先了解问题，其次才能获得更好的PostgreSQL数据库性能。


创建示例数据
在本文中，你将学到关于Gist和GIN索引的大部分知识。这两种索引类型都可以处理LIKE和ILIKE。这些索引类型的效率不一样，因此深入研究主题并找出最佳的索引类型是有意义的。


在开始之前，我创建了一些示例数据。为了避免在web上搜索示例数据，我决定生成一些数据。一个简单的md5哈希就足以证明我的观点。
test=# CREATE TABLE t_hash AS SELECT id, md5(id::text)
FROM generate_series(1, 50000000) AS id;
SELECT 50000000
test=# VACUUM ANALYZE;
VACUUM
让我们看一下数据。我们这里有5000万个id和它们的哈希表。下面的清单显示了数据的一般情况:
test=# SELECT * FROM t_hash LIMIT 10;
id | md5
----+----------------------------------
  1 | c4ca4238a0b923820dcc509a6f75849b
  2 | c81e728d9d4c2f636f067f89cc14862c
  3 | eccbc87e4b5ce2fe28308fd9f2a7baf3
  4 | a87ff679a2f3e71d9181a67b7542122c
  5 | e4da3b7fbbce2345d7772b0674a318d5
  6 | 1679091c5a880faf6fb5e6087eb1b2dc
  7 | 8f14e45fceea167a5a36dedd4bea2543
  8 | c9f0f895fb98ab9159f51fd0297e236d
  9 | 45c48cce2e2d7fbdea1afc51c7c6ad26
10 | d3d9446802a44259755d38e6d163e820
(10 rows)
运行简单的like查询
让我们把注意力转向LIKE:下面的查询选择数据中只存在一次的子字符串。请注意，百分号不仅在匹配的末尾，而且也在匹配的开头:
test=# \timing
Timing is on.
test=# SELECT * FROM t_hash WHERE md5 LIKE '%e2345679a%';
      id | md5
----------+----------------------------------
37211731 | dadb4b54e2345679a8861ab52e4128ea
(1 row)
Time: 4767.415 ms (00:04.767)
在我的iMac上，查询需要4.7秒才能完成。在90％以上的应用程序中，这已经太长了。用户体验已经受到煎熬，长期运行的查询很有可能已经大大增加了服务器的负载。


为了了解到底发生了什么，我决定看看SQL语句的执行计划：
test=# explain SELECT * FROM t_hash WHERE md5 LIKE '%e2345679a%';
                                  QUERY PLAN
------------------------------------------------------------------------------
Gather (cost=1000.00..678583.88 rows=5000 width=37)
  Workers Planned: 2
  -> Parallel Seq Scan on t_hash (cost=0.00..677083.88 rows=2083 width=37)
      Filter: (md5 ~~ '%e2345679a%'::text)
(4 rows)
Time: 11.531 ms
由于表的大小，PostgreSQL查询优化器将进行并行查询。这基本上是一件好事，因为执行时间减少了一半。但是：这也意味着我们很容易牺牲两个CPU内核来回答仅返回一行的查询。


性能不佳的原因是表实际上很大，并且数据库必须从头到尾读取它来处理请求：
test=# \dt+
List of relations
Schema |  Name  |  Type | Owner |    Size | Description
--------+--------+-------+-------+---------+-------------
public | t_hash | table |    hs | 3256 MB |
(1 row)
读取3.2 GB来获取单个数据现在已经非常高效了。


那么我们能做什么来解决这个问题呢?


pg_trgm：高级索引
幸运的是，PostgreSQL提供了一个模块，它在模式匹配方面提供了很多技巧。pg_trgm扩展实现了“trigrams”，这是一种帮助模糊搜索的方法。该扩展是PostgreSQL contrib包的一部分，因此应该出现在绝大多数系统上:
test=# CREATE EXTENSION pg_trgm;
CREATE EXTENSION
Time: 77.216 ms
如你所见，启用扩展很容易。现在的问题是:什么是trigram?让我们一起来看看:
test=# SELECT show_trgm('dadb4b54e2345679a8861ab52e4128ea');
                                                              show_trgm
---------------------------------------------------------------------------------------------------------------------------------------------
{" d"," da",128,1ab,234,28e,2e4,345,412,456,4b5,4e2,52e,54e,567,61a,679,79a,861,886,8ea,9a8,a88,ab5,adb,b4b,b52,b54,dad,db4,e23,e41,"ea "}
(1 row)
你可以观察到trigram就像一个滑动的3个字符的窗口。所有这些标记都将显示在索引中，稍后你将看到。


为了支持like使用索引，pg_trgm模块支持两种PostgreSQL索引类型:Gist和GIN。两种都将被评估。


Gist
许多人在PostgreSQL中加速模糊搜索的方法是使用Gist索引。下面是如何部署这种类型的索引:
test=# CREATE INDEX idx_gist ON t_hash USING gist (md5 gist_trgm_ops);
CREATE INDEX
Time: 2383678.930 ms (39:43.679)
可以看到，构建索引需要相当长的时间。需要指出的重要一点是，即使设置更高的maintenance_work_mem也不会加快进程。即使有4GB的maintenance_work_mem，这个过程也需要40分钟。


值得注意的是，这个索引非常大:
test=# \di+
List of relations
Schema |  Name  |  Type | Owner |  Table |  Size  | Description
--------+----------+-------+-------+--------+---------+-------------
public | idx_gist | index |    hs | t_hash | 8782 MB |
(1 row)
这个表只有3.5 GB——索引是表的2.5倍大。


但是，索引总是会让事情变得更快，对吗?嗯，实际上不是…
test=# SELECT * FROM t_hash WHERE md5 LIKE '%e2345679a%';
      id | md5
----------+----------------------------------
37211731 | dadb4b54e2345679a8861ab52e4128ea
(1 row)
Time: 105506.064 ms (01:45.506)
我们真的“优化”了查询?不是4.7秒，PostgreSQL几乎需要2分钟来完成这项工作。为什么会这样呢?让我们来看看执行计划是怎么说的:
test=# explain SELECT * FROM t_hash WHERE md5 LIKE '%e2345679a%';
                                  QUERY PLAN
----------------------------------------------------------------------------
Bitmap Heap Scan on t_hash (cost=495.30..18812.90 rows=5000 width=37)
    Recheck Cond: (md5 ~~ '%e2345679a%'::text)
    -> Bitmap Index Scan on idx_gist (cost=0.00..494.05 rows=5000 width=0)
      Index Cond: (md5 ~~ '%e2345679a%'::text)
(4 rows)
Time: 13.433 ms
PostgreSQL优化器决定进行“位图索引扫描”。所以直接索引扫描可能更好?
test=# SET enable_bitmapscan TO off;
SET
Time: 11.302 ms
test=# explain analyze SELECT * FROM t_hash WHERE md5 LIKE '%e2345679a%';
                                  QUERY PLAN
-----------------------------------------------------------------------------
Index Scan using idx_gist on t_hash (cost=0.55..20428.04 rows=5000 width=37)
  (actual time=13750.850..99070.510 rows=1 loops=1)
  Index Cond: (md5 ~~ '%e2345679a%'::text)
Planning Time: 0.074 ms
Execution Time: 99070.618 ms
(4 rows)
Time: 99072.657 ms (01:39.073)
实际上，查询仍然会显示糟糕的执行时间。


简而言之:在这里使用Gist索引可能并不合适。它需要很长的时间来创建，很大，比顺序扫描要慢得多。


Gin
幸运的是，pg_trgm扩展提供了第二个操作符类来完成这项工作。GIN索引通常用于PostgreSQL全文搜索(FTS)。让我们测试看看是否也能加速like和ilike查询？在此之前，我们重置当前连接并删除旧索引:
test=# DISCARD ALL;
DISCARD ALL
Time: 12.000 ms
test=# DROP INDEX idx_gist;
DROP INDEX
Time: 3123.336 ms (00:03.123)
创建一个新索引:
test=# CREATE INDEX idx_gin ON t_hash USING gin (md5 gin_trgm_ops);
CREATE INDEX
Time: 698063.038 ms (11:38.063)
在我的机器上，它需要11分钟，这是很多，但实际上比创建Gist索引要快得多。但是，索引创建只发生一次，所以在这种情况下我们不应该太担心。通常更重要的是查询执行时间:


test=# explain analyze SELECT * FROM t_hash WHERE md5 LIKE '%e2345679a%';
                                      QUERY PLAN
------------------------------------------------------------------------------
Bitmap Heap Scan on t_hash (cost=2270.75..20588.36 rows=5000 width=37)
    (actual time=74.592..74.593 rows=1 loops=1)
  Recheck Cond: (md5 ~~ '%e2345679a%'::text)
  Heap Blocks: exact=1
  -> Bitmap Index Scan on idx_gin (cost=0.00..2269.50 rows=5000 width=0)
      (actual time=74.584..74.584 rows=1 loops=1)
      Index Cond: (md5 ~~ '%e2345679a%'::text)
Planning Time: 0.066 ms
Execution Time: 74.665 ms
(7 rows)


Time: 75.031 ms
可以在75毫秒内运行查询，而不是4.7秒。这是一个重大的飞跃。一个索引的帮助——数据库性能的巨大飞跃。


如预期的那样，查询正好返回一行:


test=# SELECT * FROM t_hash WHERE md5 LIKE '%e2345679a%';
      id | md5
----------+----------------------------------
37211731 | dadb4b54e2345679a8861ab52e4128ea
(1 row)


Time: 74.487 ms
到目前为止，你所看到的是GIN索引解决了这个问题。但是，这里可能仍然需要第二个索引。GIN不会加速“=”操作符。因此，如果你正在执行一个普通的查找，你将需要第二个索引，如下面的例子所示:


test=# CREATE INDEX idx_btree ON t_hash (md5);
CREATE INDEX
Time: 274778.776 ms (04:34.779)
test=# \di+
List of relations
Schema |    Name  |  Type | Owner |  Table |    Size | Description
--------+-----------+-------+-------+--------+---------+-------------
public | idx_btree | index |    hs | t_hash | 2816 MB |
public |  idx_gist | index |    hs | t_hash | 2807 MB |
(2 rows)
需要一个btree来加速普通的比较。仅仅一个GIN索引是不够的:


test=# SELECT * FROM t_hash WHERE md5 = 'dadb4b54e2345679a8861ab52e4128ea';
      id | md5
----------+----------------------------------
37211731 | dadb4b54e2345679a8861ab52e4128ea
(1 row)
Time: 0.379 ms
最后
PostgreSQL提供了真正强大的索引策略。除了btree索引之外，还有很多东西需要发现。Gist和Gin也有它们的优点。GIN对于所有类型的全文操作特别有用，而Gist对于几何数据(GIS)非常理想。

数据库首席架构师：阮胜昌 MySQL 8.0 OCP、Oracle 11G OCP,PostgreSQL PCA,Oceanbase OBCA、PingCAP PCTA、软考中级数据库系统工程师、RHCE7.0、cisco CCNA认证
擅长主流数据库MySQL、Oracle、PostgreSQL运维与开发、数据恢复、安装配置、数据迁移、集群容灾、性能优化、故障应急处理、其它问题等
欢迎关注我的公众号【数库信息技术】，第一时间一起学习新知识！

转发链接：https://www.cnblogs.com/abclife/p/13843363.html