我们正在尝试使用MySQL 8.0.17中的新多变量索引(MVI)功能来优化一些有问题的查询,但遇到一个问题,即它不会像我们那样使用MVI多列索引需要它。
我们正在处理的潜在问题是“标签”之一,因此我们有一个表,其中包含200万行,其中每行都用5-20个标签“标记”,并且标签的基数在0.001%的范围内到20%结构可以描述如下:
CREATE TABLE data (
id int not null primary key,b int not null,c int not null
);
CREATE TABLE tags (
tag_id int not null,data_id int not null,name char(16),primary key (tag_id,data_id),foreign key (data_id) references data (id)
);
今天,我们正在创建包含rows (2M) * tags (10) => 20M
行的物化视图,在该行中我们可以创建基于tag + order的索引。但是,这有一个问题,我们需要使用DISTINCT或GROUP BY来在查询多个标签时删除重复项,这又意味着我们必须在按顺序索引或按组索引之间进行选择。下面的SQL简化了表结构,并提供了解决问题的极端示例。
create table mv_test (
id int not null,tag_id int not null,c int not null,PRIMARY KEY (tag_id,id),INDEX (b)
);
SELECT id FROM mv_test WHERE tag_id IN (1,2,3) GROUP BY id ORDER BY b LIMIT 100000,1;
我们希望使用MVI解决以下问题。
create table mvi_test (
id int auto_increment not null primary key,tag_ids json not null,INDEX idx1 ( (CAST(tag_ids->'$' AS UNSIGNED ARRAY)),b )
);
SELECT id FROM mvi_test WHERE json_overlaps('[1,3]',tag_ids->'$') ORDER BY b LIMIT 100000,1;
但是,我们无法创建索引和查询同时使用tag_ids
上的MVI和b
上的范围的地方。
我们看到一些查询参数以不同的方式响应。
- 在标签非常具体的地方(例如少于1000行),排序不是问题,优化器使用MVI。 (即
- 在标记非常常见的地方(例如,超过20%的行),
b
上的范围索引就足够了,可以跳过MVI并且查询也足够快(即0.00s-0.3s) - 在标签比较常见的地方(例如,占行的5%)并且偏移较高,MySQL仍然使用
b
上的范围,但实际上却放弃了大多数行进行全表扫描。 (需要几秒钟)
通过强制索引,我们可以手动调整示例查询并在大多数情况下(即小于1s)获得可接受的性能,但是我们无法找到一种方法来确定对每种参数组合使用哪个索引。同样,优化器似乎可以更好地做到这一点,并且看起来比它更聪明是徒劳的。
所以问题是:您可以创建一个多列索引,其中一部分是MVI,另一部分用于范围扫描吗?当然,对于其他更普遍问题的解决方案,也应予以赞赏。