mysql join中被驱动表的关联列为json致使全表扫描优化

原创 tangyx 2023-09-21

221

当一条sql的执行时间为45742s，也就是12+个小时，你是否还能忍受？

下面我们来看一下代码和执行计划：

select t6.proc_no 单据号,t6.sponsor_name 提单人,t6.title 单据类型,t6.currency 币种,t6.money 单据金额,t6.crt_time,t6.upd_time,t6.belong_org_name as '申请人组织',
       case when t6.proc_status = '0' THEN '编辑中'
            when t6.proc_status = '1' THEN '审批中'
            when t6.proc_status = '2' THEN '已通过'
            when t6.proc_status = '3' THEN '已拒绝'
            when t6.proc_status = '4' THEN '已撤回'
            when t6.proc_status = '5' THEN '已回退'
            when t6.proc_status = '6' THEN '已删除'
            when t6.proc_status = '7' THEN '付款中'
            when t6.proc_status = '8' THEN '已作废'
            else '待定' end '流程状态',
        case when t6.doc_status = '0' THEN '编辑中'
            when t6.doc_status = '1' THEN '审批中'
            when t6.doc_status = '2' THEN '审批结束'
            when t6.doc_status = '3' THEN '已撤回'
            when t6.doc_status = '4' THEN '已作废'
            else '待定' end '单据状态'
FROM
    (select proc_no,sponsor_name,title,currency,money,proc_status,doc_status,belong_org_name,crt_time,upd_time from cfs_workflow.t_bpm_proc_record where company_no='52741414101'  and crt_time>='2023-01-01' an
d crt_time<'2023-07-01' AND doc_status='2' )t6
    WHERE t6.proc_no NOT in(select replace(data_json->'$.baseInfo.relatedApplyNo','"','') proc_no from cfs_form_center.t_bpm_form_data tbfd where company_no='52741414101' and proc_type=10 and crt_time>='2023-
01-01' and crt_time<'2023-07-01' and data_json->'$.baseInfo.relatedApplyNo' is not NULL)\G

编辑

被驱动表虽然走了company_no列的索引，但是该索引效率很低

编辑

该表预估行数91952，但是company_no的基数只有135，所以选择性并不好

另外一个问题是，两表关联被驱动表应该走连接列的索引才高效

子查询如下：

select replace(data_json->'$.baseInfo.relatedApplyNo','"','') proc_no from cfs_form_center.t_bpm_form_data tbfd where company_no='52741414101' and proc_type=10 and crt_time>='2023-01-01' and crt_time<'2023-07-01' and data_json->'$.baseInfo.relatedApplyNo' is not NULL

子查询中的关联列为data_json转换后的格式

编辑

JSON 字段不允许直接创建索引。即使支持，实际意义也不大，因为我们一般是基于JSON中的元素进行查询，很少会基于整个 JSON 文档。基于此问题，在MySQL 8.0.17及以后的版本中，InnoDB存储引擎支持JSON数组上的多值索引。除此之外还可以通过MySQL 5.7 引入的虚拟列，然后在虚拟列当中使用索引。我用的版本为5.7，所以这里使用虚拟列创建索引

创建虚拟列

ALTER TABLE cfs_form_center.t_bpm_form_data ADD COLUMN relatedApplyNo VARCHAR(64) GENERATED ALWAYS AS (REPLACE(data_json->'$.baseInfo.relatedApplyNo','"',''));

对虚拟列添加索引

ALTER TABLE cfs_form_center.t_bpm_form_data ADD INDEX idx_relatedApplyNo (relatedApplyNo) USING BTREE;

将原sql中的json替换为虚拟列

JSON 字段不允许直接创建索引。即使支持，实际意义也不大，因为我们一般是基于JSON中的元素进行查询，很少会基于整个 JSON 文档。基于此问题，在MySQL 8.0.17及以后的版本中，InnoDB存储引擎支持JSON数组上的多值索引。除此之外还可以通过MySQL 5.7 引入的虚拟列，然后在虚拟列当中使用索引。我用的版本为5.7，所以这里使用虚拟列创建索引
ALTER TABLE cfs_form_center.t_bpm_form_data ADD COLUMN relatedApplyNo VARCHAR(64) GENERATED ALWAYS AS (REPLACE(data_json->'$.baseInfo.relatedApplyNo','"',''));

ALTER TABLE cfs_form_center.t_bpm_form_data ADD INDEX idx_relatedApplyNo (relatedApplyNo) USING BTREE;
将原sql中的json替换为虚拟列
select t6.proc_no 单据号,t6.sponsor_name 提单人,t6.title 单据类型,t6.currency 币种,t6.money 单据金额,t6.crt_time,t6.upd_time,t6.belong_org_name as '申请人组织',
       case when t6.proc_status = '0' THEN '编辑中'
            when t6.proc_status = '1' THEN '审批中'
            when t6.proc_status = '2' THEN '已通过'
            when t6.proc_status = '3' THEN '已拒绝'
            when t6.proc_status = '4' THEN '已撤回'
            when t6.proc_status = '5' THEN '已回退'
            when t6.proc_status = '6' THEN '已删除'
            when t6.proc_status = '7' THEN '付款中'
            when t6.proc_status = '8' THEN '已作废'
            else '待定' end '流程状态',
        case when t6.doc_status = '0' THEN '编辑中'
            when t6.doc_status = '1' THEN '审批中'
            when t6.doc_status = '2' THEN '审批结束'
            when t6.doc_status = '3' THEN '已撤回'
            when t6.doc_status = '4' THEN '已作废'
            else '待定' end '单据状态'
FROM
    (select proc_no,sponsor_name,title,currency,money,proc_status,doc_status,belong_org_name,crt_time,upd_time from cfs_workflow.t_bpm_proc_record where company_no='52741414101'  and crt_time>='2023-01-01' an
d crt_time<'2023-07-01' AND doc_status='2' )t6
    WHERE t6.proc_no NOT in(select relatedApplyNo proc_no from cfs_form_center.t_bpm_form_data tbfd where company_no='52741414101' and proc_type=10 and crt_time>='2023-
01-01' and crt_time<'2023-07-01' and relatedApplyNo is not NULL)\G

但此时还是没走索引（执行计划如下所示）

编辑

是什么原因没有使用索引呢？

首先看一下是否是is null或is not null造成的，因为在某些情况下is null或is not null会导致索引失效

编辑

可以看到不是is null或is not null造成的

所以这里是not in导致没有使用到索引

那么not in还可以怎么等价改写呢？

sql改写为左连接

SELECT t6.proc_no 单据号,t6.sponsor_name 提单人,t6.title 单据类型,t6.currency 币种,t6.money 单据金额,t6.crt_time,t6.upd_time,t6.belong_org_name AS '申请人组织',
CASE WHEN t6.proc_status = '0' THEN '编辑中'
WHEN t6.proc_status = '1' THEN '审批中'
WHEN t6.proc_status = '2' THEN '已通过'
WHEN t6.proc_status = '3' THEN '已拒绝'
WHEN t6.proc_status = '4' THEN '已撤回'
WHEN t6.proc_status = '5' THEN '已回退'
WHEN t6.proc_status = '6' THEN '已删除'
WHEN t6.proc_status = '7' THEN '付款中'
WHEN t6.proc_status = '8' THEN '已作废'
ELSE '待定' END '流程状态',
CASE WHEN t6.doc_status = '0' THEN '编辑中'
WHEN t6.doc_status = '1' THEN '审批中'
WHEN t6.doc_status = '2' THEN '审批结束'
WHEN t6.doc_status = '3' THEN '已撤回'
WHEN t6.doc_status = '4' THEN '已作废'
ELSE '待定' END '单据状态'
FROM
(SELECT proc_no,sponsor_name,title,currency,money,proc_status,doc_status,belong_org_name,crt_time,upd_time FROM cfs_workflow.t_bpm_proc_record WHERE company_no='52741414101' 
AND crt_time>='2023-01-01' AND crt_time<'2023-07-01' AND doc_status='2' )t6
LEFT JOIN (SELECT relatedApplyNo proc_no FROM cfs_form_center.t_bpm_form_data tbfd WHERE company_no='52741414101' 
AND proc_type=10 AND crt_time>='2023-01-01' AND crt_time<'2023-07-01' AND relatedApplyNo is not null) t5 ON t6.proc_no=t5.proc_no WHERE
t5.proc_no IS NULL;

编辑

执行时间从45742s缩短为0.4s，是不是很惊喜？

本节知识点

1、json文本创建虚拟列建立索引可以解决json不能创建索引的问题

2、not in等价改写为left join可以解决被驱动表不走索引的问题

3、被驱动表要在连接列上创建索引才高效