时间戳

首页 > 解决方案 > mysql：加入另一个表时未使用json数组上的索引

mysql - mysql：加入另一个表时未使用json数组上的索引

问题描述

我有 2 张桌子：

CREATE TABLE `directory` (
  `id` bigint NOT NULL,
  `datasets` json DEFAULT NULL
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_datasets` ((cast(`datasets` as unsigned array)))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

CREATE TABLE `dataset` (
  `id` bigint NOT NULL,
  `name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_name` (`name`,`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

下面的查询按预期使用两个表上的索引：

explain
SELECT * FROM dataset d inner join `directory` dir
on JSON_CONTAINS(dir.datasets, cast(d.id as json))
where d.id = 111;

id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
1   SIMPLE  d       const   PRIMARY PRIMARY 8   const   1   100.00  
1   SIMPLE  dir     range   idx_datasets    idx_datasets    9       2   100.00  Using where

但是，此查询仅在左表上使用索引

explain
SELECT * FROM dataset d inner join `directory` dir
on JSON_CONTAINS(dir.datasets, cast(d.id as json))
where d.name like '111';

id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
1   SIMPLE  d       range   idx_name    idx_name    259     1   100.00  Using index condition
1   SIMPLE  dir     ALL                 1000    100.00  Using where; Using join buffer (hash join)

有人可以解释这两个查询之间的区别吗？

我把条件“like”改成“=”，结果是一样的：

explain
SELECT * FROM dataset d inner join catalog dir
on JSON_CONTAINS(dir.datasets, cast(d.id as json))
where d.name = '111';

id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
1   SIMPLE  d       ref idx_name    idx_name    259 const   1   100.00  
1   SIMPLE  dir     ALL                 1000    100.00  Using where; Using join buffer (hash join)

标签： mysqljsonjoinindexing

解决方案

这是由第二个查询中的子句中的like表达式引起的WHERE

这就是为什么，在一些线程中解释：1- Equals(=) vs. LIKE 2- SQL 'like' vs '=' 性能

编辑看起来这里的问题是由于在第一个查询中您正在搜索主键，而在第二个查询中您没有

此问题中的更多详细信息： 主键字段上的 WHERE 子句的行为

推荐阅读