在编写查询时，我们应该始终花时间找到编写查询的最佳方式。
有时，这可能意味着使用表面上看起来速度不快但实际上速度很快的方法。
查询优化对于拥有高效的网站至关重要。
虽然查询优化也适用于报告和分析，但作为 web 服务一部分运行的查询是网站用户最关注的查询。
在本文中，我使用 mysql 测试员工数据库：https://dev.mysql.com/doc/employee/en/
模式
create table `employees` (
`emp_no` int not null,
`birth_date` date not null,
`first_name` varchar(14) not null,
`last_name` varchar(16) not null,
`gender` enum('m','f') not null,
`hire_date` date not null,
primary key (`emp_no`),
key `name` (`first_name`,`last_name`)
)
create table `salaries` (
`emp_no` int not null,
`salary` int not null,
`from_date` date not null,
`to_date` date not null,
primary key (`emp_no`,`from_date`),
key `salary` (`emp_no`,`salary`)
)
薪水表可以多次包含同一员工，每次员工薪水发生变化时，薪水表中都会出现一个新行。
任务
此查询的任务是返回年收入超过 50,000 美元的员工编号、名字、姓氏的唯一列表。
除了选择数据之外，我们还需要确保没有重复的员工。
使用 distinct
select distinct
employees.emp_no,
first_name,
last_name
from
employees
inner join salaries using (emp_no)
where
salary > 50000
一般来说，使用 distinct 表明查询可以写得更好。
distinct 获取所有可能的行，并在查询过程结束时删除不需要的重复行。
distinct 是根据所有选定的行计算的。这可能意味着在某些情况下可能会返回重复的名称。
可能发生这种情况的一个示例是，如果我们包含一个员工的每一行都发生更改的列，例如 salary
select distinct
employees.emp_no,
first_name,
last_name,
salary
from
employees
inner join salaries using (emp_no)
where
salary > 50000
查询执行计划：
-> table scan on <temporary> (cost=241946..245972 rows=321886)
└─> temporary table with deduplication (cost=241946..241946 rows=321886)
└─> nested loop inner join (cost=209757 rows=321886)
├─> filter: (salaries.salary > 50000) (cost=97097 rows=321886)
│ └─> index scan on salaries using salary (cost=97097 rows=965756)
└─> single-row index lookup on employees using primary (emp_no=salaries.emp_no) (cost=0.25 rows=1)
</temporary>执行计划显示使用了临时表，成本较高。临时表的查询速度通常较慢。有时它们是必要的，但如果您能找到一种不使用临时表的查询方法，通常会更有效。
平均响应时间：745ms
使用 group by
确保唯一用户的常用方法是使用 group by
group by 通常比 distinct 更快。它不需要删除重复项的最后一步来完成查询计划
select
employees.emp_no,
first_name,
last_name
from
employees
inner join salaries using(emp_no)
where
salary > 50000
group by
employees.emp_no
查询执行计划：
-> table scan on <temporary> (cost=241946..245972 rows=321886)
└─> temporary table with deduplication (cost=241946..241946 rows=321886)
└─> nested loop inner join (cost=209757 rows=321886)
├─> filter: (salaries.salary > 50000) (cost=97097 rows=321886)
│ └─> index scan on salaries using salary (cost=97097 rows=965756)
└─> single-row index lookup on employees using primary (emp_no=salaries.emp_no) (cost=0.25 rows=1)
</temporary>虽然 group by 比 distinct 稍快，但执行计划是相同的。这种情况下它们之间的区别一般与内部查询优化器、查询缓存等有关。
虽然执行计划非常有用，但它们并不总能为您提供内部发生的全部情况，这会导致可能具有相同执行计划的查询之间存在细微的差异。
平均响应时间：721ms
使用子查询
虽然子查询通常被认为效率较低，但有时它们可以减少行数，从而使查询速度更快。
在本例中，我们将使用子查询来查找工资超过 50,000 美元的员工编号
select
employees.emp_no,
first_name,
last_name
from
employees
where
emp_no in(
select
emp_no from salaries
where
salary > 50000)
使用该方法，查询时间显着下降。
查询执行计划：
-> nested loop inner join (cost=89029 rows=33961)
├─> remove duplicates from input sorted on salary (cost=5161 rows=33961)
│ └─> filter: (salaries.salary > 50000) (cost=5161 rows=33961)
│ └─> index scan on salaries using salary (cost=5161 rows=965756)
└─> single-row index lookup on employees using primary (emp_no=salaries.emp_no) (cost=80472 rows=1)
在这里您将看到查询不再使用临时表，而是使用更简单的计划，成本值更低。
这些因素导致响应时间更快。
平均响应时间： 234ms
虽然使用子查询显着提高了查询性能，但我们也许可以通过使用 exists 子句获得更好的结果，这比子查询中使用的 in 语句具有一些优势。
使用存在
使用 exists 时，查询一旦找到匹配项就会提前终止。在这种情况下，一旦找到特定员工，它就会提前终止。
虽然某个员工的工资表中有多行，但如果找到匹配的行，则不需要继续检查该特定员工是否存在，因此它会停止查找该员工并继续寻找下一个员工一个。
select
employees.emp_no,
first_name,
last_name
from
employees
where
exists (
select
1
from
salaries
where
salaries.emp_no = employees.emp_no
and salary > 50000)
我们在此查询中使用 select 1 因为 exists 只返回 true 或 false，而不返回该行包含的内容。
虽然我们可以使用 select emp_no 或 select *，但返回常量可以使查询的意图更清晰，并且在某些情况下可以更高效。
查询执行计划：
-> nested loop inner join (cost=89029 rows=33961)
├─> remove duplicates from input sorted on salary (cost=5161 rows=33961)
│ └─> filter: (salaries.salary > 50000) (cost=5161 rows=33961)
│ └─> index scan on salaries using salary (cost=5161 rows=965756)
└─> single-row index lookup on employees using primary (emp_no=salaries.emp_no) (cost=80472 rows=1)
虽然此查询计划与子查询查询计划相同，但提前终止可以提高执行时间。
平均响应时间：220ms
概括
独特：745ms
分组依据：721ms
子查询：234ms
存在：220ms
使用子查询并不总是最有效的查询方法，但是，在这种情况下，它可以显着改善您的查询。
虽然仅更改查询可以帮助修复缓慢的查询，但还可以考虑其他优化。
创建更好的索引也可以帮助解决缓慢的查询问题，但是添加索引应该保留在重写查询无法帮助查询提高效率的时候。
对自己的数据尝试不同的查询策略非常重要。虽然 exists 是查询此数据集时最有效的策略，但其他数据集的结果可能有所不同，因此请尝试各种查询，看看哪一个最适合您。
以上就是优化 SQL 查询的详细内容，更多请关注编程网其它相关文章！
