初始条件 Mysql数据库
假设有表um_truck(车辆表)和表um_driver(司机表)
其中um_truck表中truck_id主键,truck_no车牌号,driver_id车辆的司机(外键),即司机表的主键。
Um_driver 表中driver_id主键,driver_name司机名称
1、表引擎为innodb,由于innodb引擎允许外键索引,因此当执行
(A)SELECT *FROM um_driver d JOIN um_truck t ON d.driver_id=t.driver_id
(B) SELECT *FROM um_driver d JOIN(SELECT * FROM um_truck )t ON d.driver_id=t.driver_id
时,A语句的执行效率优于B
同样
(A)SELECT *FROM um_driver d left JOIN um_truck t ON d.driver_id=t.driver_id
(B) SELECT *FROM um_driver d left JOIN(SELECT * FROM um_truck )t ON d.driver_id=t.driver_id
A语句的执行效率也是优于B的。
2、当表引擎为MyISAM或者表未设计外键索引时
(A)SELECT *FROM um_driver d JOIN um_truck t ON d.driver_id=t.driver_id
(B) SELECT *FROM um_driver d JOIN(SELECT * FROM um_truck )t ON d.driver_id=t.driver_id
执行内连接,A语句的执行效率等于B语句
当执行
(A)SELECT *FROM um_driver d left JOIN um_truck t ON d.driver_id=t.driver_id
(B)SELECT *FROM um_driver d left JOIN(SELECT * FROM um_truck )t ON d.driver_id=t.driver_id
左连接时,B语句的执行效率是高于A的。另外子查询中的列名显示越多,响应速度越慢
Mysql关于Join连接查询的响应速度问题
最新推荐文章于 2023-03-15 14:14:49 发布