关于在 PostgreSQL 中使用不区分大小写的方式进行查询的最佳做法

众所周知，PostgreSQL 是大小写敏感的数据库，而 MSSQL 和 MySQL 是大小写不敏感的数据库，所以某些查询语句在这两类数据库上执行的结果将会是不一样的。

我们先在 PostgreSQL 中创建如下表：

CREATE TABLE blog_user (
    id INT PRIMARY KEY,
    nick_name VARCHAR(50) NOT NULL,
    pass TEXT NOT NULL
);
 
INSERT INTO blog_user VALUES (1, 'google',  '12345' );
INSERT INTO blog_user VALUES (2, 'microsoft', '12345' );
INSERT INTO blog_user VALUES (3, 'Google',  '12345' );
INSERT INTO blog_user VALUES (4, 'Microsoft', '12345' );

然后执行下面这个查询语句：

select * from blog_user where nick_name = 'Google';

查询结果如下：

可以看出只查出来了 nick_name 是“Google”的记录，并没有查出来 nick_name 是“google”的记录，而 MSSQL 和 MySQL 都查出来了“Google”和“google”这 2 条记录，这就是不一样的地方，那么下面就介绍几种不区分大小写的查询方式。

一、使用 lower() 函数

1、在没有创建索引的情况下进行查询

既然查询没匹配上 nick_name 是“google”的记录，那么肯定有办法能查出来，一个常见的做法就是使用lower()函数，将左右两边的字符串都转换成小写后再进行比较，所以就有了下面这个查询语句：

select * from blog_user where lower(nick_name) = lower('Google');

执行查询后得到如下结果：

可以看出这个结果，符合我们的预期。但是，到这一步并不能算完，因为我们还没有考虑索引的问题。所以，接下来我们来给 nick_name 建一个索引，看看 lower() 会不会对索引产生影响。建索引之前，我们先往表里面插入 10000 条记录：

2、在创建了索引后进行查询

我们先为 nick_name 创建一个常规的 B-Tree 索引：

CREATE INDEX idx_nick_name ON blog_user USING btree ("nick_name");

接下来，我们从这 10000 条记录中随机查询一条记录，然后查看该查询语句的查询计划：

explain (analyze,verbose,buffers) 
select * from blog_user where lower(nick_name) = lower('GHWgBhiTM');

然后得到查询计划：

可以看出索引失效了，查询引擎执行的是全表顺序扫描。然后我们去掉 lower() 函数再看看查询计划：

explain (analyze,verbose,buffers) 
select * from blog_user where nick_name = 'GHWgBhiTM';

可以看出，这回查询引擎使用了我们之前创建的索引。对比两个查询语句的执行时间，索引失效的情况下执行时间是 3.347ms，索引有效的情况下执行时间是 0.033ms，索引失效对查询性能的影响，已经大到完全不能视而不见了。

3、表达式索引

那么有没有办法能在使用 lower() 函数的时候不让索引失效呢，PostgreSQL 提供了一种表达式索引，它可以解决这个问题。

说表达式索引前，我们需要先了解常规索引，我们在创建常规索引的时候通常是针对基础数据表的一列或多列，而一列中的多行数据中可能既包含大写字符又包含小写字符，而 PostgreSQL 又是大小写敏感的数据库，那么创建出的常规索引自然也是大小写敏感的。如果在查询的时候，使用了类似 lower() 这样的函数对数据进行转换，就会破坏应用索引的条件，进而需要去全表扫描。那么，我们能不能创建一种大小写不敏感的索引呢，这个时候表达式索引就派上用场了。

表达式索引就是说我们的索引列不再是基础表的一列或多列，而是一个函数或一个标量表达式，而参数则可以是基础表的列。还是继续上面这个例子，如果我们需要给 nick_name 创建一个大小写不敏感的表达式索引，我们就可以按如下方式创建：

CREATE INDEX expr_idx_nick_name ON blog_user USING btree (lower(nick_name));

上面这个索引使用了 lower() 函数，在这个函数外面加了一个括号就表示将这个函数计算的结果作为索引列，而不再单是将 nick_name 作为索引列了，这样一来创建的索引就都是基于 nick_name 列的小写数据了。

然后再次使用 lower() 执行一次查询：

explain (analyze,verbose,buffers) 
select * from blog_user where lower(nick_name) = lower('GHWgBhiTM');

然后得到如下查询计划：

可以看出，这回我们创建的 expr_idx_nick_name 索引起作用了。

上面这种查询方式在 EFCore 中对应的就是下面这段代码：

var users = db.BlogUsers.Where(x => x.NickName.ToLower() == "GHWgBhiTM".ToLower());

关于 Bitmap Heap Scan 和 Bitmap Index Scan 的含义，可以参考如下几个网站：

二、使用 CITEXT 类型

CITEXT 是一种不区分大小写的字符串类型，它内部通过调用 lower() 函数将字符串转换成小写后进行比较。

使用方式如下：

-- 使用 CITEXT 类型前必须先安装这个类型的扩展，否则会提示 CITEXT 类型不存在。一个数据库只需创建一个该类型的扩展。
CREATE EXTENSION IF NOT EXISTS CITEXT WITH SCHEMA public;
 
CREATE TABLE app_user (
    id INT PRIMARY KEY,
    nick_name CITEXT NOT NULL,
    pass TEXT NOT NULL
);
 
INSERT INTO app_user VALUES (1, 'google',  '12345' );
INSERT INTO app_user VALUES (2, 'microsoft', '12345' );
INSERT INTO app_user VALUES (3, 'Google',  '12345' );
INSERT INTO app_user VALUES (4, 'Microsoft', '12345' );
 
SELECT * FROM app_user WHERE nick_name = 'Google';

查询结果如下：

从查询结果来看，符合我们的预期。然后，我们创建一个常规索引：

CREATE INDEX idx_app_user_nick_name ON app_user USING btree ("nick_name");

从表中任取一个 nick_name 的值作为查询条件再次执行查询：

explain (analyze,verbose,buffers) 
SELECT * FROM app_user WHERE nick_name = ' VknjbVqkP';

得到查询计划：

可以看出我们创建的常规索引起作用了。不过，在查询的过程中我发现一个现象，就是当满足我们查询条件的数据在数据库中是唯一一个的时候，查询计划使用的是 Index Scan。当满足我们查询条件的数据在数据库中存在多个的时候，查询计划使用的 Bitmap Heap Scan，例如当我们查询“Microsoft”时就使用的是 Bitmap Heap Scan，因为在不区分大小写的情况下“Microsoft”在数据库中有 2 条。

上面这种查询方式在 EFCore 中对应的就是下面这段代码：

var users = db.AppUsers.Where(x => x.NickName == "VknjbVqkP");

Index Scan 与 Bitmap Heap Scan 的区别参见 https://blog.csdn.net/sunny_day_day/article/details/116209657

三、使用 ILike 运算符

ILike运算符存在于Npgsql.EntityFrameworkCore.PostgreSQL包中，它是 PostgreSQL 特有的运算符，其工作方式与 Like 运算符类似，但是不同的是 ILike 运算符是执行不区分大小写的匹配。如果你需要执行不区分大小写的 Like 模式匹配，就可以用这个试试。

例如，我想查询 nick_name 是以“soft”结尾的数据，就是下面这段代码：

var users = db.AppUsers.Where(x => EF.Functions.ILike(x.NickName, "%soft"));

然后我们通过调用下面这段代码获取生成的 SQL 语句：

var sql = users.ToQueryString();
Console.WriteLine(sql);

那么输出的 SQL 语句如下：

SELECT a.id, a.nick_name, a.pass
FROM app_user AS a
WHERE a.nick_name ILIKE '%soft';

可以看出生成的 SQL 语句中使用了 ILike 运算符，那么我们来看一下用了这个运算符后查询计划是什么样的：

可以看出 ILike 无法使用索引，进行了全表扫描。或许有的人会说，当“%”在最右边的时候会走索引，那么我们就来看看，我选择一个已经建过索引的值来进行匹配，就取“Google”好了，我们先来确认一下“Google”是否已经被索引了：

可以看出“Google”已经被索引了，于是我们在 ILike 后面使用“Google%”来作为查询条件看看查询计划：

可以看出用“Google%”并不会触发索引扫描。所以，ILike 仅适用于不区分大小写的模糊匹配，不适合于不区分大小写的精确匹配，并且它还无法使用索引。

四、总结

综上所述，这三种不区分大小写的查询方式，都能实现功能。但是，就不区分大小写查询某一确定的值而已，使用 lower() 和使用 CITEXT 的查询性能不相上下，使用 ILike 性能最差，所以 ILike 目前仅适用于模糊查询。既然 lower() 和 CITEXT 查询性能不相上下，那就要从使用方式上看谁更好了。lower() 需要在使用的时候调用 ToLower() 方法，频繁调用会导致分配大量的字符串对象，很明显不合适，所以目前综合各方面利弊，使用 CITEXT 是进行不区分大小写查询的最佳方式，因为它既保证了查询性能，又兼顾了内存分配，而且代码更简洁。