MySQL "join on like"不使用索引

Question

DB-MySQL 5.7

我有一个相对较大的表，它必须根据"层次"索引自行连接。

因此，可连接的"id"字段是VARCHAR，看起来像1/2221933年1月222日1/222/4441/222/444/555

表创建命令：

CREATE TABLE `referrals` (
`id` varchar(60) NOT NULL,
`level` int(11) NOT NULL DEFAULT '1',
`commission` decimal(32,16) NOT NULL DEFAULT '0.0000000000000000',
`commission_currency_id` varchar(10) NOT NULL,
PRIMARY KEY (`id`,`level`,`commission_currency_id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8

为了根据级别检索数据，我计划使用以下命令：

-- Does not utilize the index
select  r1.id,
sum(r1.commission),
sum(r2.commission) as commissionAcc,
from    referrals r1
left join referrals r2
on r2.id like concat(r1.id, '/%')
where r1.id like concat('10000', '/%') and r1.`level` = 1
group by r1.id
limit 10;

我突然发现，像concat(r1.id，'/%'(这样的r2.id上的左联接引用r2不使用索引。但是，如果我在类似的语句中用字符串替换r1.id，则使用索引

-- Utilizes the index
select  r1.id,
sum(r1.commission),
sum(r2.commission) as commissionAcc,
from    referrals r1
left join referrals r2
on r2.id like concat('10000/10001', '/%')
where r1.id like concat('10000', '/%') and r1.`level` = 1
group by r1.id
limit 10;

我就是不明白为什么。我也试过：

-- Does not utilize the index
left join referrals r2
on left(r2.id, 6) = r1.id

没有成功。强制索引也没有帮助。我看到的唯一解决方案是创建一个依赖于生成列的逻辑，但这种方法将占用大量空间。

在这种情况下，有什么解决办法可以让MySQL使用索引吗？提前谢谢。

Answer 1

我偶然发现了完全相同的问题。我有一个文件夹的层次结构，我将其与邻接列表模型一起存储在内部(请参阅Joe Celko，sql中的树和层次结构以了解智能(。

我的解决方案是将带有JOIN的单个查询拆分为两个单独的请求：首先获取父文件夹，然后通过使用LIKE"PATHOFPARENT%"查询包含完整路径的列来选择所有子文件夹，然后使用索引。

在您的情况下，可能需要创建表锁以确保完整性。