为什么Left join一张视图和直接把查询语句拆出来，效率差10倍？
表关系单据表：jd，id主键，记录数5XX条
流程进度表:lcdj,id主键，jd_id(指向单据表的id，但是没有设置外键)，status（状态：0正常，-1删除），其他lcjd_id(流程进度id，对应code表id==lcjd.id的名称)，记录数22xx条
流程进度视图:v_cljd,对lcjd表中的lcjd.id代码用left join进行翻译
当前流程进度视图：select * from v_cljd where status=0,即v_lcjd中status=0的记录。
执行时间如下：
SELECT jd.id FROM jd LEFT JOIN v_dqlcjd ON jd.id = v_dqlcjd.jd_id
耗时 0.99
SELECT jd.id FROM jd LEFT JOIN v_lcjd ON jd.id = v_lcjd.jd_id where v_lcjd.`status` = 0
耗时0.006
mysql> show profiles;
+----------+------------+-----------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-----------------------------------------------------------------------------------------+
| 1 | 0.99802075 | SELECT jd.id FROM jd LEFT JOIN v_dqlcjd ON jd.id = v_dqlcjd.jd_id |
| 2 |0.0015355 | explain SELECT jd.id FROM jd LEFT JOIN v_dqlcjd ON jd.id = v_dqlcjd.jd_id |
| 3 | 0.006792 | SELECT jd.id FROM jd LEFT JOIN v_lcjd ON jd.id = v_lcjd.jd_id where v_lcjd.`status` = 0 |
+----------+------------+-----------------------------------------------------------------------------------------+
3 rows in set
mysql> explain SELECT jd.id FROM jd LEFT JOIN v_dqlcjd ON jd.id = v_dqlcjd.jd_id ;
+----+-------------+-------+--------+-----------------+-----------------+---------+-----------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-----------------+-----------------+---------+-----------------+------+-------------+
|1 | SIMPLE | jd | index| NULL | PRIMARY | 4 | NULL |567 | Using index |
|1 | SIMPLE | lcjd| ref | IND_LCJD_STATUS | IND_LCJD_STATUS | 5 | const | 10 | |
|1 | SIMPLE | clcjd | eq_ref | PRIMARY | PRIMARY | 4 | fz.lcjd.lcjd_id | 1 | Using index |
+----+-------------+-------+--------+-----------------+-----------------+---------+-----------------+------+-------------+
3 rows in set
mysql> explain SELECT jd.id FROM jd LEFT JOIN v_lcjd ON jd.id = v_lcjd.jd_id where v_lcjd.`status` = 0;
+----+-------------+-------+--------+-----------------+-----------------+---------+-----------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-----------------+-----------------+---------+-----------------+------+--------------------------+
|1 | SIMPLE | lcjd| ref | IND_LCJD_STATUS | IND_LCJD_STATUS | 5 | const |594 | Using where |
|1 | SIMPLE | jd | eq_ref | PRIMARY | PRIMARY | 4 | fz.lcjd.jd_id | 1 | Using index |
|1 | SIMPLE | clcjd | eq_ref | PRIMARY | PRIMARY | 4 | fz.lcjd.lcjd_id | 1 | Using where; Using index |
+----+-------------+-------+--------+-----------------+-----------------+---------+-----------------+------+--------------------------+
3 rows in set
为什么会有这样的差距？
页:
[1]