在本地测试中我发现了一件令我惊讶的事情,在300w数据量对json字段里面值进行筛选的时间都在100ms左右!
那么为什么mysql json查询这么快呢?
json并非纯文本
mysql将json字符串储存为紧凑的二进制格式,每个 JSON 文档都有一个类型码(Type Code)和一个数据部分,并作为内部的树状结构(其中包括嵌套元素的层次关系)。
这种格式使得 MySQL 能够直接读取和解析 JSON 数据,而不需要像文本 JSON 一样逐字符或者反序列化解析,从而提高了读取和操作的效率
在json_extract查询中,通过路径参数访问偏移表,然后根据偏移表直接跳到提取数据位置。
路径缓存
此外,在8.0还对路径进行了缓存,这避免重复解析 JSON 文档。这种方式在大量查询相同 JSON 路径时显著提升了性能。
json字段也可以作为表字段
mysql 支持在表中创建基于 json 列的虚拟列和生成列
这些列可以基于 json 提取的结果,提前计算和存储需要的部分数据。这样可以避免在每次查询时都重新解析整个 json 文档,提高查询效率。
比如以下就是根据json字段data中的name创建的生成列
ALTER TABLE my_table ADD COLUMN name VARCHAR(255) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(data, '$.name'))) STORED;
甚至mysql还支持为生成列创建索引,以及在8.0直接为json创建全文索引