fooage

fooage

1.048596

关于SQL执行细节的积累和反思
Feb 27, 2022
  • SQL

很久都没有整理博客了，近来在工作中有一些关于 SQL 查询语句的积累，而且有一个很大点在于工作中的思维模式和关注点跟学生时代的 ACM 比赛中有很大的差别。这些需要慢慢积累，并且一步步的适应这种转变。

漫谈查询语句

SQL 语句是一种数据库操作语言，它能让人通过简单易懂的查询语句来让数据库完成其表达的功能而不用在意其中执行的细节逻辑。但是发现一个问题，如果不能清楚其中执行细节就无从优化或者瞎优化，这就需要对其底层原理有所了解。

SQL 语句执行顺序

这种语言是一种易用性的语言，能够让非专业的人员也能够方便地操作数据库，但是简约并不简单，其执行顺序会和书写顺序有一定的差别，下面将以执行顺序来一一介绍。

  • FROM 语句

查询是基于数据库表的，所以显而易见的顺序就是首先要指定一张或多张表。

  • JOIN 语句

因为涉及到表的连接，之后才能进行相关的查询操作，所以连接运算也是会被前置的。

  • WHERE 语句

做完表的工作之后，就可以进行相关条件的筛选了，这一步可以筛出所要的数据。

  • GROUP BY 语句

现在已经获得数据了，如何对这些数据分组并合并是这个语句要做的事情。

  • HAVING 语句

为了处理分组之后的数据，可以通过它进一步制定分组的过滤条件。

  • SELECT 语句

最常用的语句竟是在这一步被执行的，这跟我们书写的顺序有较大的差异，但是也不难理解。因为到了这一步，数据和数据的组织形式才得到完全的确认，这样才能够被选择。

  • ORDER BY 语句

目前数据已经筛选好了，可以用排序来决定数据返回的表现形式是升序还是降序。

  • LIMIT 语句

这个语句在生产中十分有用，因为大量的数据是不能够一次读取的，数据的部分返回就离不开它。

语句执行的细节

在学校中学习了查询优化的原理，但并没有学习过如何实现。这点就是工业界和学校内知识的断层了，最经典的查询优化莫过于选择提前的优化策略，下面就会结合着这一策略来说。

-- This is one of the most common SQL queries.
SELECT * FROM ... WHERE ... AND ... AND ...;

可以看到上面查询语句中有多个选择条件，这些选择条件在 Oracle 数据库是从右向左执行的。所以应该把能筛除更多数据的条件放在 WHERE 语句条件中尽量靠右的位置。

-- Their answer is the same, but which one is faster?
SELECT * FROM ... LIMIT 10 OFFSET 10000;
SELECT * FROM ... WHERE id > 10000 LIMIT 10;

上面两句查询语句的结果是相同的，但是在数据量大的情况下他们的速度是天壤地别的。

首先是结论：下面的查询语句更快！

这是因为在数据库中存储的数据超过服务器内存的存储能力时，并不是所有的数据都已经被装在到了内存当中，如果为了实现上面查询语句的 OFFSET 效果，在不走索引的情况下数据库就会把所要数据之前的数据读入内存处理后才能够确认最后所要的数据。而下面的查询数据库已经明确的知道该从何开始，所以就通过索引从指定位置加载了几条数据，这样会快很多。

详细调查执行过程

很多时候是很难判断是全表扫描还是部分扫描的，并且数据库本身也会对 SQL 的执行顺序有一定的调整和优化，这时候就可以使用下面这种方法来获取语句的预期执行数据。只是预期执行的原因是数据库只是预执行了这条语句，但通过 EXPLAIN 得出的结果已经足以支撑对语句或索引的优化了。

-- Get detailed data of statement execution.
EXPLAIN SELECT * FROM ... WHERE ...;

这个使用方法有很多细节，觉得同事写的一篇 SQL 优化文章非常不错 https://juejin.cn/post/7174787791291842619。

工作的反思

在学校里度过了一段无悔的 ACM 比赛生涯没有荒废时光，没有特别大的成就，但是算法的思想和对代码时空复杂度的追求也不可磨灭的刻在了我的心中。工作后发现的一些经典常识是在算法之外的性能优化，着实让我有些醍醐灌顶的感觉。

节制使用协程

Golang 的协程可以说是大大减少了并发编程带给开发人员的负担，但是协程作为一种用户态执行的调度单位依然是不能够无损的调度并且占有一定资源的。所以特别是在循环次数不能够确定的情况下请使用协程池。

减少远程调用

不管是远程 RPC 还是 DB 操作调用，如果能够通过一次调用把数据全部获得并在应用中处理，就不要通过多次调用来实现同样的效果。一是网络是一种资源，本身就是有限的。二是多次调用要多次建立连接并多次解析数据，这样是耗时的。即使是有长链接也无法避免网络通信带来的额外时间和算力支出。

复杂逻辑处理

很多数据的处理逻辑尽量在服务中处理，因为大多数服务是可以横向扩展的。由目前的架构来看，存储往往是主从结构的，虽然可以有很多个 Slave 实例，但是写操作只能由 Master 来完成。这就说明简单的扩容很多时候不能缓解存储的压力，所以更应该珍惜存储的计算资源。