本文笔者主要介绍下Postgresql中两种比较特殊的索引——函数索引和部分索引。函数索引，顾名思义，索引字段经过了某个函数的处理，以达到在某种特殊的场景下走索引的目的。而部分索引，就是针对表中的部分字段建立索引，而达到查询其中这部分数据走索引的目的。

一、函数索引

       函数索引，一种典型的应用场景，就是用表中的某一字符串类型的字段构建查询条件进行查询时，期望忽略大小写，笔者通过下面的例子来介绍下函数索引的用法。

       首先，创建一个users表，用来作为数据源：

create table users(id serial,name varchar(32),age int);

       创建一个function，用来向users表中插入随机字符串：

create or replace FUNCTION random_str() 
returns varchar(32)
as
$BODY$
DECLARE
  str varchar(128);
	rs varchar(32);
begin 
    str:='abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
		rs:=substr(str,ceil(random()*52)::int,1)||substr(str,ceil(random()*52)::int,1)||substr(str,ceil(random()*52)::int,1);
		return rs;
end;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

       接下来，向users表插入500W条数据：

postgres=# insert into users (name,age) select random_str(),ceil(random()*100) from generate_series(1,5000000);
INSERT 0 5000000

       我们从users表中查询名叫tom的用户，忽略大小写。

postgres=# explain (analyze,verbose,timing) select * from users where lower(name)='tom';
                                                            QUERY PLAN                                                            
----------------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..54704.48 rows=19760 width=12) (actual time=30.880..1639.766 rows=280 loops=1)
   Output: id, name, age
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on public.users  (cost=0.00..51728.48 rows=8233 width=12) (actual time=25.357..1452.396 rows=93 loops=3)
         Output: id, name, age
         Filter: (lower((users.name)::text) = 'tom'::text)
         Rows Removed by Filter: 1666573
         Worker 0: actual time=32.700..1360.812 rows=93 loops=1
         Worker 1: actual time=12.944..1364.588 rows=68 loops=1
 Planning Time: 2.196 ms
 Execution Time: 1640.027 ms
(12 rows)

       可以看到，在没有建立索引之前，从500W条数据中找到目标需要花费1640ms的时间。接下来，我们在users表上加上lower函数的索引。

 postgres=# create index users_name_lower_inx on users using btree(lower(name));
CREATE INDEX

       再继续上面的查询，发现这次只花了66ms，效率提升了将近30倍：

postgres=# explain (analyze,verbose,timing) select * from users where lower(name)='tom';
                                                             QUERY PLAN                                                              
-------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on public.users  (cost=470.18..28438.54 rows=25000 width=12) (actual time=3.507..66.492 rows=280 loops=1)
   Output: id, name, age
   Recheck Cond: (lower((users.name)::text) = 'tom'::text)
   Heap Blocks: exact=279
   ->  Bitmap Index Scan on users_name_lower_inx  (cost=0.00..463.93 rows=25000 width=0) (actual time=0.407..0.408 rows=280 loops=1)
         Index Cond: (lower((users.name)::text) = 'tom'::text)
 Planning Time: 0.515 ms
 Execution Time: 66.752 ms
(8 rows)

        函数索引的原理并不复杂，它并不是在查找索引时进行函数的计算，而是在创建索引时就针对每行的索引列值进行了函数计算，然后再以此再建立BTree索引。

二、部分索引

       以上就是函数索引，现在我们继续学习下部分索引，这里列举Postgresql官方给的三个很好的例子：

• 排除部分普通数值

       创建一个访问日志access_log表，用来记录访问用户的IP地址和url等关键信息，访问的IP地址按照业务上划分可以分为内部地址和外部地址，而外部地址量比较多，而且我们比较关心。内部地址我们没有那么关心，现在为了节省磁盘空间和索引效率，只对我们关心的外网地址建立部分索引。

       首先，创建这个access_log表：

CREATE TABLE access_log (
    url varchar,
    client_ip inet,
    ...
);

       创建部分索引，排除内部的IP地址：

CREATE INDEX access_log_client_ip_ix ON access_log (client_ip)
WHERE NOT (client_ip > inet '192.168.100.0' AND
           client_ip < inet '192.168.100.255');

       如下查询，会使用该索引：

SELECT *
FROM access_log
WHERE url = '/index.html' AND client_ip = inet '212.78.10.32';

如下查询，不会使用该索引：

SELECT *
FROM access_log
WHERE client_ip = inet '192.168.100.23';

• 排除部分不感兴趣的值

       假如有一个订单表，其中有一个布尔类型的参数billed表示订单是否已经付款，我们对未付款的订单比较感兴趣，并为此建立一个部分索引，建立索引的方式如下：

CREATE INDEX orders_unbilled_index ON orders (order_nr)
WHERE billed is not true;

       使用索引的一个有效的查询可能是：

SELECT * FROM orders WHERE billed is not true AND order_nr < 10000;

       索引也可以用于完全不涉及order_nr的查询：

SELECT * FROM orders WHERE billed is not true AND amount > 5000.00;

       但是，下面这个查询将不会使用该索引，因为查询条件中并没有使用billed is not true，订单有可能是未付款的也有可能时付款的。

SELECT * FROM orders WHERE order_nr = 3501;

• 设置一个部分唯一索引

        部分索引的第三个作用针对针对满足某一条件下的某些字段建立唯一性索引，比如有一个记录测试结果的表，当测试结果为成功时，要求每个课题和目标组合必须唯一；但是，如果测试结果为不成功时，没有这种要求。表的结构如下：

CREATE TABLE tests (
    subject text,
    target text,
    success boolean,
    ...
);

        创建的部分索引如下：

CREATE UNIQUE INDEX tests_success_constraint ON tests (subject, target)
    WHERE success;

       当有少数成功测试和很多不成功测试时这是一种特别有效的方法。

MySQL 8.0新特性专栏目录

《MySQL开发规范》过时了，视图查询性能提升了一万倍
你真的会用EXPLAIN么，SQL性能优化王者晋级之路
索引三剑客之降序索引和不可见索引
千呼万唤始出来，MySQL 8.0索引三剑客之函数索引
双重密码，MySQL 8.0创新特性
sql_mode兼容性，MySQL 8.0 升级踩过的坑
警惕参数变化，MySQL 8.0 升级避免再次踩坑

前言

独孤九剑，重剑无锋，大巧不工，通晓剑意，无所施而不可。三剑客之首，函数索引。
函数索引这个概念并不新颖，Oracle早在十年前的Oracle10g中就支持了函数索引，函数索引在Oracle数据库中使用相当广泛和成熟，而MySQL却一直没有开发相关的索引功能。不过好消息是，MySQL 终于在8.0版本引入了这一特性。真的是，千呼万唤始出来，不过好歹还是来了。

普通索引是对列值或列的前缀值进行索引，而MySQL 8.0.13之后支持函数索引，函数索引是对表中的列执行表达式计算后的结构进行索引，而不是对列或列前缀值。使用函数索引可以对未直接存储在表中的数据进行索引。

函数索引为应用程序提供了极大的便利和性能提升。

1. 创建和使用函数索引

1.1 创建函数索引

创建函数索引的语法跟普通索引差别并不大，唯一需要注意的是函数索引对应的表达式需要用（）括起来。

首先， 函数索引可以对单列的表达式进行索引，如下：

# 创建单列表达式索引
alter table t_wang add index idx_func(date(col1));

其次，函数索引也可以对多列组合的表达式进行索引，如下：

# 创建多列组合表达式索引
alter table t_wang add index idx_func((col1 + col2));

然后，函数索引也可以将表达式和普通列组合一起构成组合索引，如下：

# 创建组合索引
alter table t_wang add index idx_func(col1, (date(col1)));

最后，函数索引还可以跟其他选项，如unique 和 asc、desc排序一起使用，如下：

# 创建表达式排序索引
alter table t_wang add unique index idx_func(col1, (date(col1)) desc);

1.2 使用函数索引提升性能

函数索引可以对字段表达式进行索引，从而在SQL语句中包含表达式的情况下可以显著提升查询性能。

测试案例：

a) 创建测试表t_wang，导入一些测试数据，在时间列创建一个普通索引

# 查看表结构，测试表的时间列有个普通索引
MySQL [test]> show create table t_wang\G
*************************** 1. row ***************************
       Table: t_wang
 CREATE TABLE `t_wang` (
  `id` int NOT NULL,
  `name` char(30) NOT NULL,
  `fmodify_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_normal_time` (`fmodify_time`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

b) 查询在4月份有修改过的人名；虽然时间列存在普通索引，但是查看执行计划为全表扫描。

# 测试查询4月份有修改过的人名
MySQL [test]> explain select name from t_wang where month(fmodify_time) = 4;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_wang | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+

c) 在测试表上添加一个函数索引，表达式为month(fmodify_time)

# 添加函数索引
alter table t_wang add index `idx_func_time`((month(fmodify_time)));

MySQL [test]> show create table t_wang\G
*************************** 1. row ***************************
       Table: t_wang
Create Table: CREATE TABLE `t_wang` (
  `id` int NOT NULL,
  `name` char(30) NOT NULL,
  `fmodify_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_normal_time` (`fmodify_time`),
  KEY `idx_func_time` ((month(`fmodify_time`)))
) ENGINE=InnoDB DEFAULT CHARSET=latin1

d) 再次查询4月份有修改过的人名；执行计划为索引扫描

MySQL [test]> explain select name from t_wang where month(fmodify_time) = 4;
+----+-------------+--------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key           | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t_wang | NULL       | ref  | idx_func_time | idx_func_time | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+---------------+---------+-------+------+----------+-------+

2. 函数索引的实现

2.1 函数索引的具体实现

MySQL 8.0 函数索引实际上是基于5.7版本引入的虚拟列（virtual generated columns）来实现的。实际上在MySQL 8.0中创建函数索引时，MySQL会自动在表上创建一个隐藏的虚拟列，然后在虚拟列上创建索引。这里注意：虚拟列是不实际占用空间的，但是函数索引是需要实际占据空间的。

隐藏的虚拟列我们可以通过以下方式查看，Extra列有个明显的VIRTUAL GENERATED标识。

# 查看隐藏虚拟列
MySQL [test]> show extended columns from t_wang; 
+----------------------------------+----------+------+-----+---------+-------------------+
| Field                            | Type     | Null | Key | Default | Extra             |
+----------------------------------+----------+------+-----+---------+-------------------+
| id                               | int      | NO   | PRI | NULL    |                   |
| fmodify_time                     | datetime | YES  | MUL | NULL    |                   |
| name                             | char(30) | YES  |     | wang    |                   |
| bd47a6cb20076f31a47803e887053c31 | date     | YES  | MUL | NULL    | VIRTUAL GENERATED |
| DB_TRX_ID                        |          | NO   |     | NULL    |                   |
| DB_ROLL_PTR                      |          | NO   |     | NULL    |                   |
+----------------------------------+----------+------+-----+---------+-------------------+

既然函数索引是基于虚拟列来实现的，那么虚拟列的一些限制在函数索引同样适用。

• 每增加一个函数索引都同时会增加一列虚拟列并计入表列总数的限制；
• 函数索引不可以作为主键索引；
• 函数索引不能是空间或全文索引；
• 函数索引不能用作外键索引约束。

2.2 函数索引与前缀索引的对比

前缀索引，即只对字段的前几个字符进行索引，在优化字段查询效率的同时减小索引长度。

# 创建前缀索引
alter table t_wang add index `idx_prefix` (name(4));

MySQL 8.0引入的函数索引同样可以实现这个能力，使用SUBSTRING()函数取字段的前几个字符作为表达式建立索引。

# 创建函数索引
alter table t_wang add index `idx_substring` ((substring(name, 1, 4));

前缀索引和函数索引，同样都可以对字段前缀进行索引，但是要想利用函数索引提升查询性能，需要在查询语句的谓词部分使用与函数定义相同的函数才行。也就是说，这里如果我们想让执行计划走到函数索引，查询语句需要这样写：select * from t_wang where substring(name, 1, 4) = ‘wang’ 。而前缀索引并没有这个限制。

不过借助SUBSTRING()函数，函数索引甚至可以实现对字段的任意子集，甚至是对不同字段的交集、并集等建立索引，使用上更加灵活，可以适用各种不同场景。

3. 函数索引在JSON数据查询的应用

函数索引为应用程序提供了很大的便利，我们可以通过调整查询条件来优化查询性能、缩小结果集、减少数据传输等。函数索引同样在 JSON 数据存取方面同样可以有类似效果。我们可以在JSON列创建函数索引来简化键值对的查询。

不过这里要注意：JSON的操作符->>对应的是JSON_UNQUOTE()函数，该函数返回的是带有排序规则 utf8mb4_bin 的字符串； 而使用CAST()函数返回的是带有排序规则 utf8mb4_0900_ai_ci（系统默认排序规则） 的字符串。所以，在查询时需要进行转义以利用到函数索引。

譬如，直接检查JSON的key->value键值对：

# 方式1:
CREATE TABLE employees (
  data JSON,
  INDEX idx ((CAST(data->>"$.name" AS CHAR(30)) COLLATE utf8mb4_bin))
);

SELECT * FROM employees WHERE data->>'$.name' = 'James';

# 或者方式2：
CREATE TABLE employees (
  data JSON,
  INDEX idx ((CAST(data->>"$.name" AS CHAR(30))))
);

SELECT * FROM employees WHERE CAST(data->>'$.name' AS CHAR(30)) = 'James';

4. 思考：表达式优化查询的几种方式

我们假设以下场景：在一个允许名字变更的系统中，我们要查询出每年的4月份有修改过的人名，有几种方式可以实现？

# 我们使用上面例子中的表结构
 CREATE TABLE `t_wang` (
  `id` int NOT NULL,
  `name` char(30) NOT NULL,
  `fmodify_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

方式一: 使用时间列上普通索引

我们可以在时间列上加上普通索引，然后将系统上线使用以来的每年4月数据都遍历一下。

# 在fmodify_time列加个普通索引
alter table t_wang add index idx_normal_time(fmodify_time);

# 对应查询语句
select name from t_wang where (fmodify_time between '2015-04-01 00:00:00' and '2015-05-01 00:00:00') or (fmodify_time between '2016-04-01 00:00:00' and '2016-05-01 00:00:00') or ... or (fmodify_time between '2022-04-01 00:00:00' and '2022-05-01 00:00:00');   

方式二：使用虚拟列，在虚拟列上添加索引

我们可以仍然使用MySQL 5.7的虚拟列来优化查询，在表上添加一个虚拟列，然后在虚拟列添加一个普通索引。

# 添加虚拟列和对应索引
alter table t_wang add column `ftime_generated` int GENERATED ALWAYS AS (month(fmodify_time));
alter table t_wang add index `idx_generated_time` (ftime_generated);

# 对应查看语句
select name from t_wang where ftime_generated = 4;

方式三：使用函数索引

我们可以使用MySQL 8.0引入的函数索引，在时间列上添加一个函数索引。

# 添加函数索引
alter table t_wang add index `idx_functional_time` ((month(fmodify_time)));

# 对应查询语句
select name from t_wang where month(fmodify_time) = 4;

方式四：使用表达式默认值

实际上我们还可以有另外一种方式来优化查询，同样是在MySQL 8.0还引入了另外一个特性，表达式默认值。

MySQL 8.0.13开始，字段的DEFAULT 子句中指定的默认值可以是常量或表达式。将基于列的表达式计算值作为默认值，可以实现类似虚拟列的能力。

# 向表中添加一列，将时间列的表达式作为该列的默认值；然后再在该列添加一个普通索引。
alter table t_wang add column `ftime_default` int NOT NULL DEFAULT (month(fmodify_time));
alter table t_wang add index `idx_default_time` (ftime_default);

# 对应查询语句
select name from t_wang where ftime_default = 4;

总结

MySQL 8.0引入了函数索引这一新特性，提高了业务程序的便利性和性能。有了函数索引，业务不需要手动在表上添加虚拟列，就能够享受虚拟列带来的性能提升。同其他类似实现方式相比，譬如虚拟列、表达式默认值，函数索引更加简洁和易于维护。函数索引还可以用于JSON数据的查询。MySQL 8.0在索引方面引入的新特性可不止函数索引这一项，还有倒序索引和不可见索引，这些特性对业务查询也是大有裨益的。

喜欢的同学麻烦点个关注和点赞