在编写查询时，我们应该始终花时间找到编写查询的最佳方式。
有时，这可能意味着使用表面上看起来速度不快但实际上速度很快的方法。
查询优化对于拥有高效的网站至关重要。
虽然查询优化也适用于报告和分析，但作为 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_namefrom employees inner join salaries using (emp_no)where salary > 50000
一般来说，使用 distinct 表明查询可以写得更好。
distinct 获取所有可能的行，并在查询过程结束时删除不需要的重复行。
distinct 是根据所有选定的行计算的。这可能意味着在某些情况下可能会返回重复的名称。
可能发生这种情况的一个示例是，如果我们包含一个员工的每一行都发生更改的列，例如 salary
select distinct employees.emp_no, first_name, last_name, salaryfrom employees inner join salaries using (emp_no)where salary > 50000
查询执行计划：
-> table scan on (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)
执行计划显示使用了临时表，成本较高。临时表的查询速度通常较慢。有时它们是必要的，但如果您能找到一种不使用临时表的查询方法，通常会更有效。
平均响应时间：745ms
使用 group by
确保唯一用户的常用方法是使用 group by
group by 通常比 distinct 更快。它不需要删除重复项的最后一步来完成查询计划
select employees.emp_no, first_name, last_namefrom employees inner join salaries using(emp_no)where salary > 50000group by employees.emp_no
查询执行计划：
-> table scan on (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)
虽然 group by 比 distinct 稍快，但执行计划是相同的。这种情况下它们之间的区别一般与内部查询优化器、查询缓存等有关。
虽然执行计划非常有用，但它们并不总能为您提供内部发生的全部情况，这会导致可能具有相同执行计划的查询之间存在细微的差异。
平均响应时间：721ms
使用子查询
虽然子查询通常被认为效率较低，但有时它们可以减少行数，从而使查询速度更快。
在本例中，我们将使用子查询来查找工资超过 50,000 美元的员工编号
select employees.emp_no, first_name, last_namefrom employeeswhere 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_namefrom employeeswhere 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 查询的详细内容，更多请关注创想鸟其它相关文章！
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 chuangxiangniao@163.com 举报，一经查实，本站将立刻删除。
发布者：程序猿，转转请注明出处：https://www.chuangxiangniao.com/p/55087.html
微信扫一扫 
支付宝扫一扫 
