优化关联查询

三个表的创建语句

CREATE TABLE `test`.`afx_output_source_item` (
  `cheadguid` INT NOT NULL,
  `goodsid` INT NULL,
  `goodsno` VARCHAR(45) NULL,
  `goodsname` VARCHAR(45) NULL,
  `model` VARCHAR(45) NULL,
  `goodstaxno` VARCHAR(45) NULL,
  PRIMARY KEY (`cheadguid`));

CREATE TABLE `test`.`afx_output_goods` (
  `goodsno` VARCHAR(45) NULL,
  `goodstaxno` VARCHAR(45) NULL,
  `cguid` INT NULL);

CREATE TABLE `test`.`afx_output_source_head` (
  `cguid` INT NOT NULL,
  `orderno` VARCHAR(45) NULL,
  `orgncode` VARCHAR(45) NULL,
  `itype` INT NULL,
  `status` INT NULL,
  `createdate` DATETIME NULL,
  `createuserid` INT NULL,
  `updateuserid` INT NULL,
  `orderdate` DATETIME NULL,
  `voidStatus` INT NULL,
  `errorStatus` INT NULL,
  PRIMARY KEY (`cguid`));

原始的关联查询

select * from afx_output_source_item a ,afx_output_goods b, afx_output_source_head c where b.goodsno=a.goodsno and a.model is null and a.cheadguid=c.cguid and c.orderdate>='2024-04-25'

优化后的：

select * from afx_output_goods b inner join   (select * from afx_output_source_item where model is null) a  on b.goodsno=a.goodsno inner join
(select * from afx_output_source_head where orderdate>='2024-04-25')c on a.cheadguid=c.cguid

先过滤表中数据，然后再关联。