ddd_cargo表:
ddd_location表:
针对各类数据库,其是有自己的优化引擎,针对SQL语句会尝试优化,但优化的结果,有时并不是系统想要的。
因此在优化进入深水区后,需要了解到数据库进行优化后的SQL语句是什么??我们可以通过下面的方法查看(需要命令行连入数据库mysql -u root -p sq-test):
–explain执行对应SQL语句
explain select * from ddd_location,ddd_cargo where ddd_cargo.destinationLocation_code=ddd_location.code;
–查看
show warnings;
如图:
自优化后:
select `sq-test`.`ddd_location`.`code` AS `code`,`sq-test`.`ddd_location`.`name` AS `name`,`sq-test`.`ddd_cargo`.`id`
AS `id`,`sq-test`.`ddd_cargo`.`sender_phone` AS `sender_phone`,`sq-test`.`ddd_cargo`.`description` AS `description`,`sq-test`.`ddd_cargo`.`originLocation_code` AS `originLocation_code`,`sq-test`.`ddd_cargo`.`destinationLocation_code` AS `destinationLocation_code`,`sq-test`.`ddd_cargo`.`created_at` AS `created_at`,`sq-test`.`ddd_cargo`.`updated_at` AS `updated_at` from `sq-test`.`ddd_location`
join `sq-test`.`ddd_cargo`
where (`sq-test`.`ddd_cargo`.`destinationLocation_code` = `sq-test`.`ddd_location`.`code`)
可以看到mysql优化器将普通的多表查询的sql优化成了连接查询,提升效率。
再试一个更明显的:
explain select ddd_cargo.sender_phone from ddd_location,ddd_cargo where ddd_cargo.destinationLocation_code=ddd_location.code;
自优化后:
select `sq-test`.`ddd_cargo`.`sender_phone` AS `sender_phone` from `sq-test`.`ddd_location`
join `sq-test`.`ddd_cargo`
where (`sq-test`.`ddd_cargo`.`destinationLocation_code` = `sq-test`.`ddd_location`.`code`)
需求:尝试查询已有货物的运送目的地
explain select * from `sq-test`.ddd_location where code in( select `sq-test`.ddd_cargo.destinationLocation_code from `sq-test`.ddd_cargo);
自优化后SQL:
select `sq-test`.`ddd_location`.`code` AS `code`,`sq-test`.`ddd_location`.`name` AS `name` from `sq-test`.`ddd_location`
semi join (`sq-test`.`ddd_cargo`)
where (`sq-test`.`ddd_location`.`code` = `sq-test`.`ddd_cargo`.`destinationLocation_code`)
可以发现其是先join再where查询的,从SQL优化角度看,先子查询,再in效率会有提升。
改写后的SQL:
select temp.* from
(select distinct(`sq-test`.ddd_cargo.destinationLocation_code) from `sq-test`.ddd_cargo) temp
inner join `sq-test`.ddd_location
on temp.destinationLocation_code=ddd_location.code;
留言与评论(共有 0 条评论) “” |