MySQL请求使用JSON索引查询数据量不准确

本文摘要:背景描述通过SQL 语句查询#xff0c;使用JSON索引的情况下#xff0c;查询结果不准确#xff0c;远远大于预期。如查询SQL#xff1a;SELECT count(1) FROM 表 where time#61;#39;2022-8-22#39; ;结果#xff1a;398......

背景描述


通过SQL 语句查询,使用JSON索引的情况下,查询结果不准确,远远大于预期。

如查询SQL:

SELECT count(1) FROMwhere time='2022-8-22' ;

结果:3981392

实际结果应该是100W左右


知识点


Json 类型简单介绍


有一种叫做JSON (JavaScript Object Notation) 的轻量级数据交换格式能够替代XML的工作。它就是JSON。

数据格式比较简单, 易于读写, 格式都是压缩的, 占用带宽小,易于解析这种语言。

示例:

json = {"name1":"test", "list":["a", "d", "c"]}


列表数据类型


“列表”是一个值,它包含多个字构成的序列。 “列表值”指的是列表本身,而不是指列表中的值。 列表中的值称为表项,表项用逗号隔开。

示例:

list = ["a", "b", "c"]


组合索引数据结构

  1. 如图所示,下面是两条记录。
  2. 若通过全表扫描,将返回2条记录。
  3. 若通过日期字段与JSON 字段,组合索引进行扫描,这时日期字段将与json 列表中每一个元素一一匹配,所以将返回6条记录。


image

复现业务场景

  1. 创建表
  2. 插入准备数据
  3. 通过全表扫描,得到真实的表中记录条目
  4. 通过json 类型的组合索引扫描,得到json 列表元素组合的条目。
  5. 通过page 的数据结构( information_schema.INNODB_BUFFER_PAGE),确定页中记录的条目数量。

1. 创建表

CREATE TABLE `t1` (
  `MoveTime` datetime NOT NULL,
  `NodeTree` json DEFAULT NULL,
  `SaleCount` bigint NOT NULL DEFAULT '0',
  KEY `Idx_MoveTime_SalueCount_Tree1` (`MoveTime`,(cast(json_extract(`NodeTree`,_utf8mb4'$.node') as char(32) array)),`SaleCount`) USING BTREE
) ENGINE=InnoDB;

2. 准备数据

INSERT INTO t1(`MoveTime`, `NodeTree`, `SaleCount`) 
VALUES ('2022-08-22 00:00:00', '{"node": ["393459011", "industrial", "12900351", "12899801", "office-products", "1069242"]}', '549');
INSERT INTO t1(`MoveTime`, `NodeTree`, `SaleCount` )
VALUES ('2022-08-22 00:00:00', '{"node": ["166099011", "166092011", "toys-and-games"]}', '11978');
INSERT INTO t1(`MoveTime`, `NodeTree`, `SaleCount`)
VALUES ('2022-08-22 00:00:00', '{"node": ["1069462", "1069454", "1069242", "office-products", "490790011", "12899801"]}', '2972');

3. 验证


  1. 通过全表扫描验证
select count(1) from t1 where MoveTime = '2022-08-22';
explain select count(1) from t1 where MoveTime = '2022-08-22';

image

通过结果,可以看到通过全表扫描查看到的结果是按照表中的记录数进行统计。


  1. 通过带有 json 数据类型索引进行验证
select count(1) from t1 force index(Idx_MoveTime_SalueCount_Tree1) where MoveTime = '2022-08-22';
explain select count(1) from t1 force index(Idx_MoveTime_SalueCount_Tree1) where MoveTime = '2022-08-22';


image


4. 查看数据页 page 中的记录条目数量

select table_name,index_name,number_records,data_size from information_schema.INNODB_BUFFER_PAGE where table_name like '`db01`.`t1`';


image


5. 结论

当json 数据为列表时,使用联合索引会,其它字段与列表匹配,会产生一对多的关系。从而最终统计数量就会按符合列表的数量统计。通过查看 information_schema.INNODB_BUFFER_PAGE ,对应的索引的记录数,可以确定,低层数据结构就是这么设计的。



解决方案

  1. 建议合理使用 json 索引。
  2. 可以针对不同的统计信息,指定不同的索引,进行统计。



适用版本

适用MySQL 5.7以上版本

标签