优化前

select a.*, b.类目, b.店铺,status from(
SELECT purchase_no, created, LEFT(created,7) 年月, goods_count, goods_stockin_count, provider_name, prop2 as 生产经理, goods_no,status
FROM dwd_purchase
where created >= "2023-01-01 00:00:00"
and status != 10
GROUP BY purchase_no) a
left join 款式基础信息 b on a.goods_no=b.款号

优化后

select a.*, b.类目, b.店铺,status from(
SELECT purchase_no, created, LEFT(created,7) 年月, goods_count, goods_stockin_count, provider_name, prop2 as 生产经理, goods_no,status
FROM dwd_purchase
where created >= "2023-01-01 00:00:00"
and status != 10
GROUP BY purchase_no) a
left join 款式基础信息 b
ON CONVERT(a.goods_no USING utf8)=b.款号

如上述类型(左连接)，数据库(MY SQL)，A表和B表其实字段类型都是一致的(varchar类型)，A表款号不是主键,B表款号是主键，只是加了一个convert转换为utf8,结果运行速度不到一秒就出来结果了，优化前的我没试过但是我运行了快一分钟没结果，后来我又试了一下，两个表关联字段都不是主键，加上ON CONVERT(a.字段名 USING utf8)=b.字段名这个，运行速度还是不到一秒出结果，我想问一下各位佬它的这个原理是什么，跟隐性转换，索引等又有什么关联吗？求各位佬告知😭

select a.,b. from(
SELECT purchase_no, created, LEFT(created,7) 年月, goods_count, goods_stockin_count, provider_name, prop2 as 生产经理, goods_no,status
FROM dwd_purchase
where created >= "2023-01-01 00:00:00"
and status != 10
GROUP BY purchase_no) a
left join
(select purchase_no FROM purchase_order
where created >= "2023-01-01 00:00:00"
and status != 10
GROUP BY purchase_no) b on CONVERT(a.purchase_no USING utf8)=b.purchase_no(这是后来测得都不是主键的，结果也是一秒不到，不加也是很慢)