PostgreSQL创建“LIKE”的索引列 | Java提升营

Java提升营

探索编程的乐趣

PostgreSQL创建“LIKE”的索引列

原创 | 发表于 更新于 分类于 Valine： 5.2k 5 分钟

最近，我想在正在使用的应用程序添加基本的文本搜索。我花了一段时间才弄清楚索引LIKE的正确方法，尤其是对于复合列的索引。下面给大家介绍一下。

介绍

搜索通常是任何Web应用程序不可或缺的一部分，但它也是可能导致性能问题的部分之一。用户期望搜索快速且准确。诸如 Elastic Search 或 Solr 之类的工具非常擅长于对大型数据集进行快速智能搜索。

但是，我们并不需要应用程序中有如此重要的依赖关系。通常，正确地对数据库进行结构化和索引可以使您的查询保持良好且快速的状态。

我❤Postgres

我与PostgreSQL合作的次数越多，给我的印象就越深。每当我需要它的东西时，通常它已经存在了，我无法找到为LIKE查询建立索引列就是一个很好的例子。

我有一个非常简单的users数据库表，其中填充了100万行。

                      Table "public.users"
ColumnTypeModifiers
idintegernot null default nextval(‘users_id_seq’::regclass)
usernamecharacter varying
first_namecharacter varying
last_namecharacter varying

没有任何索引的情况下进行不区分大小写的ILIKE搜索非常慢：

1
2
3
4
5
6
7
8
9
10
11
12
 $> EXPLAIN ANALYSE SELECT COUNT(*) FROM users WHERE username ILIKE '%foo%';
                                                  QUERY PLAN
--------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=21927.24..21927.25 rows=1 width=0) (actual time=737.523..737.523 rows=1 loops=1)
   ->  Seq Scan on users  (cost=0.00..21927.00 rows=96 width=0) (actual time=737.520..737.520 rows=0 loops=1)
         Filter: ((username)::text ~~* '%foo%'::text)
         Rows Removed by Filter: 1000000
 Planning time: 0.373 ms
 Execution time: 737.593 ms
(6 rows)

Time: 738.404 ms

正则索引

您可能会认为，为该列使用标准btree索引将有助于此搜索-答案是不会。如您在下面看到的，查询速度同样慢。它正在执行全表扫描，并且完全忽略了索引。

如果我们进行比较搜索（例如）**WHERE username = ‘foo’**，则将使用新索引，但如果使用LIKE或进行部分匹配，则不会使用新索引：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
$> CREATE INDEX idx_users_username ON users (username);
Time: 15987.751 ms
$> EXPLAIN ANALYSE SELECT COUNT(*) FROM users WHERE username ILIKE '%foo%';

                                                  QUERY PLAN
--------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=21927.24..21927.25 rows=1 width=0) (actual time=752.271..752.271 rows=1 loops=1)
   ->  Seq Scan on users  (cost=0.00..21927.00 rows=96 width=0) (actual time=752.268..752.268 rows=0 loops=1)
         Filter: ((username)::text ~~* '%foo%'::text)
         Rows Removed by Filter: 1000000
 Planning time: 0.599 ms
 Execution time: 752.318 ms
(6 rows)

Time: 753.251 ms

Postgres pg_trgm

pg_trgm模块提供了用于根据三元组匹配确定ASCII字母数字文本的相似性的函数和运算符，以及支持快速搜索相似字符串的索引运算符类。

Postgres使用三字母组将字符串分解成较小的块并有效地对其进行索引。所述pg_trgm模块支持GIST或GIN索引和作为Postgres的版本9.1这些索引支持LIKE/ ILIKE查询。

要使用pg_trm模块，您需要启用扩展并创建传入gin_trgm_ops值的索引：

1
2
3
4
5
$> CREATE EXTENSION pg_trgm;
Time: 42.206 ms

$> CREATE INDEX trgm_idx_users_username ON users USING gin (username gin_trgm_ops);
Time: 7082.474 ms

现在，运行相同的查询只需要一小部分时间。只需2ms，而不是753ms！从查询计划中我们可以看到它正在使用我们创建的新索引trgm_idx_users_username。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
$> EXPLAIN ANALYSE SELECT COUNT(*) FROM users WHERE username ILIKE '%foo%';

                                                  QUERY PLAN
--------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=369.12..369.13 rows=1 width=0) (actual time=0.030..0.030 rows=1 loops=1)
   ->  Bitmap Heap Scan on users  (cost=12.75..368.88 rows=96 width=0) (actual time=0.026..0.026 rows=0 loops=1)
         Recheck Cond: ((username)::text ~~* '%foo%'::text)
         ->  Bitmap Index Scan on trgm_idx_users_username  (cost=0.00..12.72 rows=96 width=0) (actual time=0.024..0.024 rows=0 loops=1)
               Index Cond: ((username)::text ~~* '%foo%'::text)
 Planning time: 0.636 ms
 Execution time: 0.095 ms
(7 rows)

Time: 2.333 ms

复合列

在上表中，我们的用户带有first_name和last_name。

假设我们可以在应用程序中按名称搜索用户-如果我在搜索字段中输入“John Doe”，希望它返回一个名为“John Doe”的用户（假设它存在）。

为此，我们需要同时使用first_name和last_name列，并将它们连接到查询中：

1
SELECT * FROM users WHERE first_name || ' ' || last_name ILIKE '%John Doe%'

如果在first_name和last_name列上都创建两个新索引，则此查询将不会使用它们。该查询使用的是复合列，因此各个列上的索引将无济于事。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
$> CREATE INDEX trgm_idx_users_first ON users USING gin (first_name gin_trgm_ops);
Time: 4577.637 ms
$> CREATE INDEX trgm_idx_users_last ON users USING gin (last_name gin_trgm_ops);
Time: 4770.507 ms
                                                  QUERY PLAN
--------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=27027.00..27027.01 rows=1 width=0) (actual time=1025.543..1025.544 rows=1 loops=1)
   ->  Seq Scan on users  (cost=0.00..26927.00 rows=40000 width=0) (actual time=1025.539..1025.539 rows=0 loops=1)
         Filter: ((((first_name)::text || ' '::text) || (last_name)::text) ~~* '%foo%'::text)
         Rows Removed by Filter: 1000000
 Planning time: 0.273 ms
 Execution time: 1025.591 ms
(6 rows)

Time: 1027.547 ms

相反，我们需要在复合列上创建索引。

1
2
$> CREATE INDEX index_users_full_name
             ON users using gin ((first_name || ' ' || last_name) gin_trgm_ops);

再次运行查询，我们可以看到使用了复合索引，我们回到了闪电般的快速状态。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
EXPLAIN ANALYSE SELECT COUNT(*) FROM users WHERE first_name || ' ' || last_name ILIKE '%foo%';

                                                  QUERY PLAN
--------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=10605.00..10605.01 rows=1 width=0) (actual time=0.020..0.020 rows=1 loops=1)
   ->  Bitmap Heap Scan on users  (cost=378.01..10505.00 rows=40000 width=0) (actual time=0.018..0.018 rows=0 loops=1)
         Recheck Cond: ((((first_name)::text || ' '::text) || (last_name)::text) ~~* '%foo%'::text)
         ->  Bitmap Index Scan on index_users_full_name  (cost=0.00..368.01 rows=40000 width=0) (actual time=0.016..0.016 rows=0 loops=1)
               Index Cond: ((((first_name)::text || ' '::text) || (last_name)::text) ~~* '%foo%'::text)
 Planning time: 0.338 ms
 Execution time: 0.080 ms
(7 rows)

Time: 0.975 ms

为什么我们不使用concat_ws,最初构建查询时，我使用了concat_ws（带分隔符的concat）字符串函数来连接first_name和last_name。

1
concat_ws(' ', first_name, last_name)

concat_ws由于它是不可变的，因此不能在索引中使用。

结论

挖掘Posgtres的丰富功能有助于避免在其他搜索工具上添加依赖项。该pg_trgm模块提供远远超过了文本列只是更好的索引。它提供了丰富的模糊搜索功能，并且可以处理全文搜索。

给老奴加个鸡腿吧 🍨.