优化 SQL 查询

在编写查询时，我们应该始终花时间找到编写查询的最佳方式。

有时，这可能意味着使用表面上看起来速度不快但实际上速度很快的方法。

查询优化对于拥有高效的网站至关重要。

虽然查询优化也适用于报告和分析，但作为 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   (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_name
from
    employees
    inner join salaries using(emp_no)
where
    salary > 50000
group 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_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 查询的详细内容，更多请关注慧达安全导航其它相关文章！