SQL笔试面试编程题-分页查询employees表，每5行一页，返回第2页的数据

抱歉，当我第一次接触数据库时，我对这个 SQL 分页查询感到困惑。
不过后来我明白了，所以我来说说我的经历吧。

当时我进了一家公司，负责运维。
公司使用的系统数据库表比较大，有一个员工表来存储员工信息。
领导让我写一个查询，以页面形式显示员工列表。
每页显示 5 人，按员工编号 emp_no 排序。

我一开始写了什么？直接使用 SELECT FROMEmployees LIMIT 5 OFFSET 5 结果跑了一圈发现每次的控制顺序都不一样。
后来老板告诉我，在对查询进行分页之前，需要先排序，不然每次结果都会乱，用户体验也不好。

我编辑了它并添加了 ORDER BY emp_no ASC。
好了，现在每次查到的员工编号已经排好序了。
但问题又回来了。
领导表示，第二页数据有时显示5 项，有时显示3 项，有时甚至是空白。
我想，哦，原来总行数还不到1 0行，所以第二页就不能显示5 行了。

接下来我做了一些研究，发现LIMIT 5 OFFSET 5 是标准SQL，兼容性最好。
一些数据库如 MySQL 也支持 LIMIT 5 .5 ，但我认为最好使用标准的。

之后，表格变得越来越大，有时有百万甚至几百万个数据点。
我发现每当我使用OFFSET跳过以前的数据时，性能都很差，尤其是当跳过几万或几十万条时，数据库必须扫描这么多数据然后再次跳过，效率非常低。

我想知道是否可以优化？接下来我想到了一个办法，不使用OFFSET，而是在WHERE条件中指定从哪里开始搜索。
例如，如果我知道上一页最后一个条目的员工编号，我可以从下一页的该编号 + 1 开始搜索。
代码大概是这样的：
sql 从员工中选择，其中 emp_no > 上一页最后一项的 emp_no SORT BY emp_no LIMIT 5 ;
这样数据库就直接从指定的编号开始查找，不会跳过之前的数据，效率高很多。
然而，这种方法需要在应用程序层面维护上一页最后一个数据的数量。

此外，某些数据库具有不同的分页语法。
例如，Oracle在早期版本中使用ROWNUM，在更高版本中使用FETCH FIRST...ROWS Only； SQL Server 仅使用 OFFSET...FETCH NEXT...ROWS。
因此，在编写SQL时，需要考虑您使用的具体数据库系统。

总的来说，分页查询的关键是要注意排序和性能优化。
我最初使用的是简单的LIMIT和OFFSET，但后来发现当数据量很大时，需要使用更高效的写法。
在过去的十年里，我遇到了很多陷阱，但我终于解决了。

如何在SQL中实现分页？LIMIT与OFFSET的正确用法

必须使用分页键、LIMIT+OFFSET、ORDERBY。
对于深页，Keyset比较好，ID用于跳过记录。

基本语法：
LIMIT页码OFFSET（页码-1 ）页码
LIMIT限制行数、OFFSET跳过行数、ORDERBY排序顺序。

深页慢，全表扫描快，使用Keyset，跳过ID。

WHERE ID > 上一页的最后一个ID LIMIT 页数
优秀的Keyset，深度快速，无页面跳转，使用“下一页”。

复杂查询分页，JOIN/JOIN+GROUPBY先过滤，再ORDERBY，最后LIMIT+OFFSET。

ORDERBY索引在WHERE/JOIN之后，性能良好。

优化：
索引位置/排序依据
解释查询计划，检查问题
总结：
ORDERBY 稳定结果
按键深页快速
优化索引查询和快速分页。

一对多关系下，如何高效分页查询并以多方属性作为筛选条件？

记得上次帮同事调试迁移接口时，他的SQL运行得像蜗牛一样慢，数据量大一点就崩溃了。
后来发现他将子查询嵌套了3 次，直接烧毁了数据库的CPU。

你看，假设有一个用户表和一个标签表，一个用户可以有七八个标签。
如果你写 SELECT FROM user WHERE tag='lively' AND tag='cheerful' ，这肯定不行，数据库会混乱。
但如果使用GROUP_CONCAT(t.tag) AS标签，然后将WHERE改为WHERE t.tag IN(“热闹”，“令人愉快”)，那么就正确了。

最重要的是GROUP BY u.id LIMIT 0, 1 0分页不能每次都从第一页开始计算。
我之前见过一个项目使用了 LIMIT 1 0 的迁移。
结果当数据量增大的时候，每次都要重新检查整个表，还被老板训斥了。
后来改成了中继指针，其中u.id>上一页的last id LIMIT 1 0，瞬间快了1 0倍。

等等，还有别的事。
在GROUP_CONCAT中，如果标签过多，例如用户有2 0个标签，则结果字符串可能过长，数据库可能会报错。
所以最好在组内添加一个长度限制，GROUP_CONCAT(t.tag SEPARATOR ',') (ORDER BY t.tag)，这样也可以按字母顺序排序。

我突然想到，如果标签有层级关系怎么办？例如，“热闹”下面还有“非常热闹”和“有点热闹”。
目前，GROUP_CONCAT 还不够。
您可能必须使用 JSON 函数来将标签转换为树结构。
但这取决于具体的数据库支持，MySQL 5 .7 或更高版本。

现在最烦人的就是模糊的查询。
例如，用户输入单词“alive”并想要搜索包含单词“alive”的所有标签。
这种场景其实不太适合JOIN+GROUP_CONCAT。
您必须先使用临时表或视图来过滤它。
看看并解释一下。
如果您看到全表扫描，则基本上意味着索引创建不正确。

说到这里，为什么内置表格设计不起作用？因为它违反了模型。
上次，新人直接把用户标签拼成字符串，存到user_tag字段中。
结果当标签改变时，他必须手动用UPDATE语句替换，这就造成了很多数据一致性问题。

最后一个例子很清楚。
三个标志加HAVING即可COUNT(DISTINCT t.tag)=3 精确过滤。
但如果你改变条件，比如“同时存在超过5 个标签”，这就不够了，必须使用子查询。

但是，如果标签表和用户表关联的条目数量非常多，比如平均每个用户关联2 0个标签，那么JOIN操作可能仍然会很慢。
目前，您可能需要考虑缓存，或者先使用Elasticsearch等搜索引擎进行过滤。

看看索引优化，user_id复合索引，flag确实很重要，但是加不加取决于表结构。
如果tag表本身有tag_index字段，则使用INDEX(user_id, tag_index)即可。

最后一个分页参数，LIMIT 1 0, 1 0为第二页，LIMIT 2 0, 1 0为第三页。
这个数字必须从0开始，而不是从1 开始。
上次，一个实习生输入LIMIT 1 , 1 0，花了三分钟扫描整个表。

所以，迁移查询看起来很简单，但实际上有很多技巧在里面。
你的方案总体来说是好的，但是具体的实现取决于数据量和业务的复杂程度。
例如，用户显然只关联了“活泼”和“开朗”标签，但 GROUP_CONCAT 将它们列在一起。
这种场景其实很常见。