请选择 进入手机版 | 继续访问电脑版

湖南新梦想

 找回密码
 立即注册
搜索
热搜: 活动 交友 discuz
查看: 61|回复: 0

SQL优化这五个极简法则,直接让查询原地起飞!(二)

[复制链接]

3395

主题

3795

帖子

1万

积分

论坛元老

Rank: 8Rank: 8

积分
13388
发表于 2022-8-15 17:39:47 | 显示全部楼层 |阅读模式
 四、不要使用 OFFSET 实现分页
 分页查询的原理就是先跳过指定的行数,再返回 Top-N 记录。分页查询的示意图如下:
数据库一般支持 FETCH/LIMIT 以及 OFFSET 实现 Top-N 排行榜和分页查询。当表中的数据量很大时,这种方式的分页查询可能会导致性能问题。以 MySQL 为例:
  -- MySQL
  SELECT *
    FROM large_table
   ORDER BY id
   LIMIT 10 OFFSET N;


  以上查询随着 OFFSET 的增加,速度会越来越慢;因为即使我们只需要返回 10 条记录,数据库仍然需要访问并且过滤掉 N(比如 1000000)行记录,即使通过索引也会涉及不必要的扫描操作。
  对于以上分页查询,更好的方法是记住上一次获取到的最大 id,然后在下一次查询中作为条件传入:
  -- MySQL
  SELECT *
    FROM large_table
   WHERE id > last_id
   ORDER BY id
   LIMIT 10;


  如果 id 字段上存在索引,这种分页查询的方式可以基本不受数据量的影响。
  五、了解 SQL 子句的逻辑执行顺序
  以下是 SQL 中各个子句的语法顺序,前面括号内的数字代表了它们的逻辑执行顺序:
  (6)SELECT [DISTINCT | ALL] col1, col2, agg_func(col3) AS alias
  (1)  FROM t1 JOIN t2
  (2)    ON (join_conditions)
  (3) WHERE where_conditions
  (4) GROUP BY col1, col2
  (5)HAVING having_condition
  (7) UNION [ALL]
     ...
  (8) ORDER BY col1 ASC,col2 DESC
  (9)OFFSET m ROWS FETCH NEXT num_rows ROWS ONLY;


  也就是说,SQL 并不是按照编写顺序先执行 SELECT,然后再执行 FROM 子句。从逻辑上讲,SQL 语句的执行顺序如下:
  首先,FROM 和 JOIN 是 SQL 语句执行的第一步。它们的逻辑结果是一个笛卡尔积,决定了接下来要操作的数据集。注意逻辑执行顺序并不代表物理执行顺序,实际上数据库在获取表中的数据之前会使用 ON 和 WHERE 过滤条件进行优化访问;
  其次,应用 ON 条件对上一步的结果进行过滤并生成新的数据集;
  然后,执行 WHERE 子句对上一步的数据集再次进行过滤。WHERE 和 ON 大多数情况下的效果相同,但是外连接查询有所区别,我们将会在下文给出示例;
  接着,基于 GROUP BY 子句指定的表达式进行分组;同时,对于每个分组计算聚合函数 agg_func 的结果。经过 GROUP BY 处理之后,数据集的结构就发生了变化,只保留了分组字段和聚合函数的结果;
  如果存在 GROUP BY 子句,可以利用 HAVING 针对分组后的结果进一步进行过滤,通常是针对聚合函数的结果进行过滤;
  接下来,SELECT 可以指定要返回的列;如果指定了 DISTINCT 关键字,需要对结果集进行去重操作。另外还会为指定了 AS 的字段生成别名;
  如果还有集合操作符(UNION、INTERSECT、EXCEPT)和其他的 SELECT 语句,执行该查询并且合并两个结果集。对于集合操作中的多个 SELECT 语句,数据库通常可以支持并发执行;
  然后,应用 ORDER BY 子句对结果进行排序。如果存在 GROUP BY 子句或者 DISTINCT 关键字,只能使用分组字段和聚合函数进行排序;否则,可以使用 FROM 和 JOIN 表中的任何字段排序;
  最后,OFFSET 和 FETCH(LIMIT、TOP)限定了最终返回的行数。
  了解 SQL 逻辑执行顺序可以帮助我们进行 SQL 优化。例如 WHERE 子句在 HAVING 子句之前执行,因此我们应该尽量使用 WHERE 进行数据过滤,避免无谓的操作;除非业务需要针对聚合函数的结果进行过滤。
  除此之外,理解 SQL 的逻辑执行顺序还可以帮助我们避免一些常见的错误,例如以下语句:
  -- 错误示例
  SELECT emp_name AS empname
    FROM employee
   WHERE empname ='张飞';


  该语句的错误在于 WHERE 条件中引用了列别名;从上面的逻辑顺序可以看出,执行 WHERE 条件时还没有执行 SELECT 子句,也就没有生成字段的别名。
  另外一个需要注意的操作就是 GROUP BY,例如:
  -- GROUP BY 错误示例
  SELECT dept_id, emp_name, AVG(salary)
    FROM employee
   GROUP BY dept_id;


  由于经过 GROUP BY 处理之后结果集只保留了分组字段和聚合函数的结果,示例中的 emp_name 字段已经不存在;从业务逻辑上来说,按照部门分组统计之后再显示某个员工的姓名没有意义。如果需要同时显示员工信息和所在部门的汇总,可以使用窗口函数。
  如果使用了 GROUP BY 分组,之后的 SELECT、ORDER BY 等只能引用分组字段或者聚合函数;否则,可以引用 FROM 和 JOIN 表中的任何字段。
  还有一些逻辑问题可能不会直接导致查询出错,但是会返回不正确的结果;例如外连接查询中的 ON 和 WHERE 条件。以下是一个左外连接查询的示例:
  SELECT e.emp_name, d.dept_name
    FROM employee e
    LEFT JOIN department d ON (e.dept_id = d.dept_id)
   WHERE e.emp_name ='张飞';
  emp_name|dept_name|
  --------|---------|
  张飞     |行政管理部|
  SELECT e.emp_name, d.dept_name
    FROM employee e
    LEFT JOIN department d ON (e.dept_id = d.dept_id AND e.emp_name ='张飞');
  emp_name|dept_name|
  --------|---------|
  刘备     |   [NULL]|
  关羽     |   [NULL]|
  张飞     |行政管理部|
  诸葛亮   |   [NULL]|
  ...


  第一个查询在 ON 子句中指定了连接的条件,同时通过 WHERE 子句找出了“张飞”的信息。
  第二个查询将所有的过滤条件都放在 ON 子句中,结果返回了所有的员工信息。这是因为左外连接会返回左表中的全部数据,即使 ON 子句中指定了员工姓名也不会生效;而 WHERE 条件在逻辑上是对连接操作之后的结果进行过滤。
  总结
  SQL 优化本质上是了解优化器的的工作原理,并且为此创建合适的索引和正确的语句;同时,当优化器不够智能的时候,手动让它智能。


回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|Archiver|手机版|小黑屋|湖南新梦想 ( 湘ICP备18019834号-2 )

GMT+8, 2022-9-28 04:23 , Processed in 0.057232 second(s), 19 queries .

Powered by Discuz! X3.4 Licensed

Copyright © 2001-2020, Tencent Cloud.

快速回复 返回顶部 返回列表