MySQL如何实现类PostgreSQL部分索引？无需分表缩减索引体积

问题 #

我有一张存储原始JSON对象与type字段的表，JSON可能包含个人、企业等各类信息，同类型的JSON多数属性一致，但无法提前知晓所有对象类型，且同类型JSON结构也并非完全统一。

现在我需要为部分属性创建索引以实现搜索，例如按domain查询企业：

SELECT * FROM objects WHERE type = 'company' AND data->>'$.domain' = 'acme.com'

若创建type, data->>'$.domain'这样的联合索引会造成大量空间浪费，因为仅type为company的行需要该索引。在PostgreSQL中可使用部分索引解决此问题并大幅缩小索引体积，请问MySQL中是否有无需拆分表的类似解决方案？

MySQL的解决方案 #

1. 直接使用带WHERE条件的部分索引（MySQL 8.0.13+） #

从MySQL 8.0.13版本开始，官方支持创建带过滤条件的部分索引，逻辑和PostgreSQL的部分索引完全一致，只对符合WHERE条件的行构建索引，能有效节省存储空间。针对你的场景，创建索引的语句如下：

CREATE INDEX idx_company_domain ON objects (JSON_UNQUOTE(data->>'$.domain')) 
WHERE type = 'company';

这里用data->>'$.domain'（等价于JSON_UNQUOTE(data->'$.domain')）提取JSON字段中的domain字符串值，保证索引存储的是可直接比较的字符串类型。查询时依然可以使用原来的SQL语句，优化器会自动命中这个部分索引。

2. 低版本MySQL的替代方案（虚拟列+普通索引） #

如果你的MySQL版本低于8.0.13，可以通过生成虚拟列间接实现类似效果：
首先给表添加一个虚拟列，仅当type为company时提取domain值，其余情况为NULL：

ALTER TABLE objects ADD COLUMN company_domain VARCHAR(255) 
GENERATED ALWAYS AS (CASE WHEN type = 'company' THEN JSON_UNQUOTE(data->>'$.domain') ELSE NULL END) VIRTUAL;

然后给这个虚拟列创建普通索引：

CREATE INDEX idx_company_domain ON objects (company_domain);

由于MySQL索引会自动忽略NULL值，实际只有type为company的行会被纳入索引，同样能达到缩小索引体积的目的。查询时可以直接使用这个虚拟列来匹配：

SELECT * FROM objects WHERE type = 'company' AND company_domain = 'acme.com';

内容的提问来源于stack exchange，提问作者Chris