mysql左连接多个字段使用concat函数索引失效

原创已于 2022-12-13 15:17:22 修改 · 2.5k 阅读 ·

本内容遵循CC 4.0 BY-SA版权协议

爱程序的小姚

关注

标签

#mysql #数据库

分类数据库

于 2022-12-13 15:14:02 首次发布

mysql 专栏收录该内容

1 篇文章

订阅专栏

本文探讨了MySQL中如何优化涉及多字段连接查询的索引使用，包括避免使用concat函数导致索引失效的问题，并介绍了一种利用虚拟列实现高效索引连接的方法。

 select
    sf.*,
    sfd.status,
    sfd.remark,

    from wms_storage_fee sf

    left join wms_storage_fee_detail sfd on 
concat(sfd.client_no,sfd.year,sfd.month) = concat(sf.client_no,sf.year,sf.month)

需求是需要 sf表左连接sfd表需要同时连接3个字段，我当时想到的是使用 concat方法进行字符串拼接然后连接查询，发现查询速度慢了许多，用explan工具分析，发现连接的表没有走走索引，走了全表扫描

我开始只在 client_no 字段加了索引，发现没有走索引后我把三个索引都加上，发现也不走索引，然后我又尝试加了个复合索引，还是不起作用，后面查了下知道，用了mysql的函数是不会走索引的，后面换了个写法

 select
    sf.*,
    sfd.status,
    sfd.remark,

    from wms_storage_fee sf

    left join wms_storage_fee_detail sfd on 
  sfd.client_no = sf.client_no and sfd.year = sf.year and sfd.month = sf.month

这种写法把需要写很多and ，后面研究发现一个新的写法，建虚拟列与虚拟索引

alter table wms_storage_fee  add column client_no_year_month CHAR(50) GENERATED ALWAYS AS (concat(client_no,`year`,`month`));			
			
alter table wms_storage_fee_detail  add column client_no_year_month CHAR(50) GENERATED ALWAYS AS (concat(client_no,`year`,`month`));			
						
						
alter table wms_storage_fee add INDEX index_client_no_year_month(client_no_year_month);
alter table wms_storage_fee_detail add INDEX 
index_client_no_year_month(client_no_year_month);

就是把两个表都加上一个虚拟列与虚拟索引，然后连接时都有虚拟列连接，这样都走索引了

 select
    sf.*,
    sfd.status,
    sfd.remark,

    from wms_storage_fee sf

    left join wms_storage_fee_detail sfd on 
  sfd.client_no_year_month = sf.client_no_year_month

它和写多个and的区别还是有的，我现在这个场景还是都能使用，但是有个情况想要走索引只能建虚拟列，比如我sf表连接sfd表这个三个字段 client_no,`year`,`month`，只需要client_no的部分片段进行连接，这是只能通过虚拟列不能用and

alter table wms_storage_fee_detail add column client_no_year_month CHAR(50) GENERATED ALWAYS AS (SUBSTRING_INDEX(client_no, '-', 1));

client_no 格式 221125002-37000这种，我只需要221125002这部分进行连接

最后分析发现用了虚拟索引