实现一条SQL语句查出页面数据和数据条数

后端在一次请求周期中使用多次数据库查询的好处在于操作简单、可读性高、使代码逻辑更加清晰明了。
尽管如此，后端应用与数据库进行网络传输的时间消耗往往是个不可忽视的因素，若多次简单查库的总时间经常会大于单次复杂查询的时间，那作为后端开发者在进行对数据的增删改查操作时，就应当尽量减少查询数据库的次数。

一、使用单条SQL一次查询出分页的数据及总数

有一个非常普遍的场景：分页查询数据，并返回符合查询条件的总条数。
很多人的实现方式都是分两次进行查询，甚至是通用的PageHelper插件也是如此。

那有没有什么方式，可以只用一次查询同时得到总数和页面数据呢？

如果使用MySQL 8以上的数据库，那还真有。借助于MySQL 8 的 WITH AS 语句可以做到：

WITH a AS (
		SELECT columnA, columnB, columnC
		FROM my_table WHERE <your condition>
) SELECT * FROM a LIMIT <page_start>, <page_size>
  UNION ALL
  SELECT COUNT(*) AS columnA, NULL AS columnB, NULL AS columnC
  FROM a

以上查询中，先是把符合查询条件的全部结果集作为中间表a，之后再从此结果集中通过LIMIT获取需要的某页数据，同时专门对临时表a的总记录数做一个单独查询(借用了columnA存储总记录数)。
合并后的该SQL语句查询返回的结果列表中，最后一行就是包含数据总数信息的行，其他行就是需要查询的目标页面数据集。

使用的时候需要注意两个互相UNION的语句，选择的列需要相同。因此即便查询COUNT(*)的语句实际不需要其他的列，在语句中也必须写上。这里可以给它们都赋NULL。

理论上说，使用这种 UNIOIN ALL 的方式，一次性从数据库查想要的任何数据组合都可以，只需要让返回列相同就行

二、一些难以规避的需要执行多次查询的场景

这些场景我还没有找到什么好的优化方法，不知道大家是怎么处理的。

1. 权限校验；用户发起的CRUD操作在某些场景下需要在执行前进行权限校验，仅在校验通过后才可执行。若操作权限与操作对象本身有关，则需要查询数据当前的状态和属性；
2. 合法性校验：类似于权限校验。若操作对象对操作的支持与否需要对象本身属性或其他对象决定，那也需要先查询数据库；
3. 涉及多个库表的操作：操作对象涉及多个数据库表，并且那些操作不能通过库表的外键级联来实现的。

以上是自己在做开发时的一些心得和体会，欢迎大家评论留言！