SQL优化-数据库自优化

一、表结构

ddd_cargo表：

ddd_location表：

二、MySQL自优化

针对各类数据库，其是有自己的优化引擎，针对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;