join查询内使用where过滤与join查询内不使用where过滤

I、建表（id不为主键或者索引）

一、首先建表

1.      myorder

CREATETABLE `myorder` (

  `id` int(10) NOT NULL,

  `order_name` varchar(255) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf-8  COMMENT='订单表';

 

2.      myorderdetail

CREATETABLE `myorderdetail` (

  `id` int(10) NOT NULL,

  `oid` int(10) DEFAULT NULL,

  `product_name` varchar(255) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf-8 COMMENT='订单商品表';

 

二、分别在表1和表2插入1K，27W数据，使用到存储过程

BatchInsert(IN init INT, IN loop_time INT)

建存储过程语句：

CREATE DEFINER=`root`@`localhost` PROCEDURE `BatchInsert`(INinit INT, IN loop_time INT)

BEGIN

 DECLARE Var INT;

 DECLARE ID INT;

 DECLARE OID int;

 SET Var = 0;

 SET ID = init;

SET OID =init;

    WHILE Var < loop_time DO

    insert into 表名(字段1,字段2,…) values(字段1值,字段2值,…);

      SET ID = ID + 1;

      SET OID = OID + 1;

      SET Var = Var + 1;

      END WHILE;

END

 

三、业务逻辑

查询出myorder里在myorderdetail 里有的订单的记录，其中myorderdetail 的id需要在id>100 and id<270000。
a.直接join（join内不使用where过滤myorderdetail）

SELECT   * FROM myorder a  JOIN myorderdetailb ON a.id = b.oid

WHERE  b.id > 100 AND b.id < 270000

此时的查询时间19.21s

b. 在join时候在join子句中使用where 过滤

SELECT  *  FROM  myorder a JOIN(      

SELECT  * FROM myorderdetail  WHERE id > 100  AND id < 270000

) b ON a.id = b.oid

此时查询耗时 1.674s

 

所以：

由19.21s>1.67s,因此在join之前给予join先过滤是很有必要的。

 

 

II、更改二表的id为主键（唯一索引）

Sql:

ALTER TABLE ` 表名` ADDPRIMARY KEY ( `id` )

 

1．a.直接join（join内不使用where过滤myorderdetail）

SELECT   * FROM myorder a  JOIN myorderdetailb ON a.id = b.oid

WHERE  b.id > 100 AND b.id < 270000

此时的查询时间 0.982s（之前19.21s）

 

b. 在join时候在join子句中使用where 过滤

SELECT  *  FROM  myorder a JOIN(      

SELECT  * FROM myorderdetail  WHERE id > 100  AND id < 270000

) b ON a.id = b.oid

此时查询耗时 1.617s（之前1.674s）

 

III.更改2表的id为普通索引

1．a.直接join（join内不使用where过滤myorderdetail）

SELECT   *  FROM myorder a  JOIN myorderdetail b ON a.id = b.oid

WHERE  b.id > 100AND b.id < 270000

此时的查询时间 0.571s（之前19.21s）

b. 在join时候在join子句中使用where 过滤

SELECT  *  FROM  myorder a JOIN(      

SELECT  * FROM myorderdetail  WHERE id > 100  AND id < 270000

) b ON a.id = b.oid

此时查询耗时 1.746（之前1.674s）

总结：

主键（唯一索引）VS普通字段作为筛选条件

0.982sVS 19.21s（不在join里筛选查询）

1.617sVS 1.674s（在join里筛选查询）

普通索引VS普通字段作为筛选条件

0.571sVS 19.21s（不在join里筛选查询）

1.746s VS1.674s（在join里筛选查询）

 

结论:

 1.在关联条件不是主键或者索引的情况下，join语句还是要先筛选（在被join的表里where过滤）再join其他表。

2.在关联条件是主键或者索引的情况下，被join的表不需要被筛选（在被join的表里where过滤）再join其他表。

 

就本项目给予建议：

1.sql中尽量少使用子查询（opentable需要时间长）

2.根据项目实际情况

a.需要连接的字段上建索引，再单纯join

b.如果连接字段因为某些原因没建索引，则需要在join时候先筛选在join（即子查询），此时sql看起来长些，但效率会好很多。

 

原因如下：

     join里面子查询和普通join的explain执行计划比较可知道二这差距主要集中在opentables，差距比较大。