背景描述
通过SQL 语句查询，使用JSON索引的情况下，查询结果不准确，远远大于预期。
如查询SQL：
SELECTcount(1)FROM 表 wheretime='2022-8-22';
结果：3981392
实际结果应该是100W左右
知识点
Json 类型简单介绍
有一种叫做JSON (JavaScript Object Notation) 的轻量级数据交换格式能够替代XML的工作。它就是JSON。
数据格式比较简单， 易于读写， 格式都是压缩的， 占用带宽小，易于解析这种语言。
示例：
json = {"name1":"test", "list":["a", "d", "c"]}
列表数据类型
“列表”是一个值,它包含多个字构成的序列。 “列表值”指的是列表本身,而不是指列表中的值。 列表中的值称为表项,表项用逗号隔开。
示例：
list= ["a", "b", "c"]
组合索引数据结构
- 如图所示，下面是两条记录。
- 若通过全表扫描，将返回2条记录。
- 若通过日期字段与JSON 字段，组合索引进行扫描，这时日期字段将与json 列表中每一个元素一一匹配，所以将返回6条记录。
复现业务场景
- 创建表
- 插入准备数据
- 通过全表扫描，得到真实的表中记录条目
- 通过json 类型的组合索引扫描，得到json 列表元素组合的条目。
- 通过page 的数据结构（ information_schema.INNODB_BUFFER_PAGE），确定页中记录的条目数量。
1. 创建表
CREATETABLE `t1` ( `MoveTime` datetimeNOTNULL, `NodeTree` json DEFAULT NULL, `SaleCount` bigintNOTNULL DEFAULT '0', KEY `Idx_MoveTime_SalueCount_Tree1` (`MoveTime`,(cast(json_extract(`NodeTree`,_utf8mb4'$.node')aschar(32) array)),`SaleCount`) USING BTREE ) ENGINE=InnoDB;
2. 准备数据
INSERTINTO t1(`MoveTime`, `NodeTree`, `SaleCount`)VALUES('2022-08-22 00:00:00','{"node": ["393459011", "industrial", "12900351", "12899801", "office-products", "1069242"]}','549');INSERTINTO t1(`MoveTime`, `NodeTree`, `SaleCount` )VALUES('2022-08-22 00:00:00','{"node": ["166099011", "166092011", "toys-and-games"]}','11978');INSERTINTO t1(`MoveTime`, `NodeTree`, `SaleCount`)VALUES('2022-08-22 00:00:00','{"node": ["1069462", "1069454", "1069242", "office-products", "490790011", "12899801"]}','2972');
3. 验证
- 通过全表扫描验证
selectcount(1)from t1 where MoveTime ='2022-08-22';explain selectcount(1)from t1 where MoveTime ='2022-08-22';
通过结果，可以看到通过全表扫描查看到的结果是按照表中的记录数进行统计。
- 通过带有 json 数据类型索引进行验证
selectcount(1)from t1 force index(Idx_MoveTime_SalueCount_Tree1)where MoveTime ='2022-08-22';explain selectcount(1)from t1 force index(Idx_MoveTime_SalueCount_Tree1)where MoveTime ='2022-08-22';
4. 查看数据页 page 中的记录条目数量
select table_name,index_name,number_records,data_size from information_schema.INNODB_BUFFER_PAGEwhere table_name like'`db01`.`t1`';
5. 结论
当json 数据为列表时，使用联合索引会，其它字段与列表匹配，会产生一对多的关系。从而最终统计数量就会按符合列表的数量统计。通过查看 information_schema.INNODB_BUFFER_PAGE ，对应的索引的记录数，可以确定，低层数据结构就是这么设计的。
解决方案
- 建议合理使用 json 索引。
- 可以针对不同的统计信息，指定不同的索引，进行统计。
适用版本
适用MySQL 5.7以上版本
