MySQL生产实战之全表扫描比走索引快16倍？

对公司研发进行了几场培训，效果很好，一般慢查询他们已经能够直接优化了，但今天又收到研发的一个慢SQL,看执行计划很好，但执行起来很慢。我拿到SQL展开研究，最后发现强制走全表会快得多。我们一起来看看这个慢SQL

EXPLAIN
WITH RECURSIVE g_tnode_all AS (
SELECT a.gid,a.tnode,a.pnode,a.profileid FROM bas_goods a
WHERE a.profileid=200004255 AND a.tnode in ('10002','10006') 
UNION ALL
SELECT  bg.gid,bg.tnode,bg.pnode,bg.profileid FROM bas_goods bg 
INNER JOIN g_tnode_all ga ON bg.pnode=ga.tnode AND bg.profileid=ga.profileid 
WHERE bg.profileid=200004255
),g_tnode AS(
SELECT DISTINCT gid FROM g_tnode_all)
SELECT * FROM g_tnode;
复制

结果返849行，需要16.4秒

看一下执行计划

因为此查询是一个递归查询，我第一个想到的是不是递归层次太多，所以我第一时间就改写一下语句，看一下最多递归了几次

WITH RECURSIVE g_tnode_all AS (
SELECT a.gid,a.tnode,a.pnode,a.profileid, 0 as i FROM bas_goods a
WHERE a.profileid=200004255 AND a.tnode in ('10002','10006') 
UNION ALL
SELECT  bg.gid,bg.tnode,bg.pnode,bg.profileid,i+1 as i  FROM bas_goods bg 
INNER JOIN g_tnode_all ga ON bg.pnode=ga.tnode AND bg.profileid=ga.profileid 
WHERE bg.profileid=200004255
),g_tnode AS(
SELECT DISTINCT gid,i FROM g_tnode_all)
SELECT * FROM g_tnode;
复制

改造后，最多递归了3次

那和递归关系不大，再回归到执行计划上

如上图bg表的组合索引 IDX_profileid_tnode 通过key_len判断，只走到第一个字段 profileid
我以前专门写过一篇文章，对于组合索引一定要关注key_len 看原文点这里

而这个表profileid字段所有值是一样的。所以看似走了索引，实际是全表扫描，但这种全表扫描，又没有利用到join buffer即BNL,可以说是最原始的循环嵌套扫描了。

我强制bg表不走索引来看看结果，8.0.20后可以用hint no_index()

EXPLAIN
WITH RECURSIVE g_tnode_all AS (
SELECT a.gid,a.tnode,a.pnode,a.profileid, 0 as i FROM bas_goods a
WHERE a.profileid=200004255 AND a.tnode in ('10002','10006') 
UNION ALL
SELECT  /*+ no_index(bg) */  bg.gid,bg.tnode,bg.pnode,bg.profileid,i+1 as i  FROM bas_goods bg 
INNER JOIN g_tnode_all ga ON bg.pnode=ga.tnode AND bg.profileid=ga.profileid 
WHERE bg.profileid=200004255
),g_tnode AS(
SELECT DISTINCT gid,i FROM g_tnode_all)
SELECT * FROM g_tnode;
复制

如上图 强制bg表全表扫描，用到了BNL连接算法

改造后的SQL用时1.4S 性能提升10多倍

除了上面改造手段外，我在本地还尝试过其它方法

1、bg表上关联两个字段没有索引，建上索引 idx_profileid_pnode(profileid,pnode)
2、8.0.20版本前没有 no_index hint 那可以用ignore index 来强制走全表
3、我在本地环境执行以下语句后，mysql可以直接走全表扫描BNL,不需要我用hint.

ALTER TABLE bas_goods Engine=InnoDB, STATS_SAMPLE_PAGES=30;
analyze table bas_goods;
复制

另外。我强制使用BNL() hint的时候无效，在网上查了资料，mysql走索引的优先级比BNL hint更高，所以不起作用，但我对这种说法保有怀疑态度，在官网的确也没有搜到比较权威的资料。有知晓的大佬可以评论区回复。