后端开发的SQL查询接口，你会如何实现？（一）

后端开发的SQL查询接口，你会如何实现？（一）

　　点击上方“逆锋起笔”，公众号回复 编程资源

领取大佬们推荐的学习资料

作者：jasonGeng88

编辑：陶家龙

出处：https://github.com/jasonGeng88/blog

　　如果你是做后端开发的，肯定已经实现了列表查询接口。当然，有些查询条件很简单，一条SQL就搞定了。

　　但是有些查询条件极其复杂，再加上库表的各种不合理的设计，使得查询界面特别难写，然后加班什么的。不用说（不知道你有没有这种感觉~）。

　　让我们从一个例子开始。这是购物网站 的搜索条件。如果要实现这样的搜索界面，您会如何实现？

　　当然你说在搜索引擎的帮助下，比如Elasticsearch，你可以实现它。但是这里我想说的是，如果你想自己实现呢？

　　从上图可以看出，搜索分为6个类别，每个类别又分为子类别。

　　中间，大类的条件是交点。每个子类都有单选、多选、自定义的情况，最终输出满足条件的结果集。

　　好的，现在需求明确了，我们将开始实施它们。

　　实现 1

　　最先出现的是学生A，他是SQL写作的“专家”。小A自信地说：“不就是一个查询接口吗？条件很多，不过以我丰富的SQL经验，这对我来说还是不难的。”

　　于是我写了如下一段代码（这里以MySQL为例）：

　　select ... from table_1

left join table_2

left join table_3

left join (select ... from table_x where ...) tmp_1

...

where ...

order by ...

limit m,n

　　代码在测试环境中运行，结果似乎匹配，所以我准备预发布。有了这个预告，问题就开始暴露了。

　　预发布是为了让在线环境尽可能的逼真，所以数据量自然要比测试大很多。那么对于如此复杂的SQL，其执行效率可想而知。考生果断的把A的代码打回去了。

　　实现 2

　　总结小A失败的教训，小B开始优化SQL，先通过explain关键字进行SQL性能分析，在添加索引的地方添加索引。

　　同时将一个复杂的SQL拆分成多个SQL，计算结果在程序内存中进行计算。

　　伪代码如下：

　　$result_1 = query('select ... from table_1 where ...');

$result_2 = query('select ... from table_2 where ...');

$result_3 = query('select ... from table_3 where ...');

...

$result = array_intersect($result_1, $result_2, $result_3, ...);

　　这种方案在性能上明显比第一种好很多，但是在功能验收的时候，产品经理还是觉得查询速度不够快。

　　小B自己知道，每次查询都会多次查询数据库，有历史原因，有些条件不能单表查询，查询等待时间在所难免。

　　实现 3

　　小C从上述方案中看到了优化的空间。他发现小B的思路没有问题，拆分复杂的条件，计算每个子维度的结果集，最后合并所有子结果集，得到最终想要的结果。

　　所以他突然想知道是否可以提前缓存每个子维度的结果集，这样查询的时候就可以直接取到想要的子集，而不用每次都去查数据库。

　　这里，小C使用Redis来存储缓存数据。使用它的主要原因是它提供了多种数据结构，在Redis中很容易进行集合交集操作。

　　具体方案，如图：

　　对于每个条件，计算出的结果集ID预先存储在对应的Key中，选择的数据结构为Set。

　　查询操作包括：

　　这其实就是所谓的反向索引。在这里您会发现缺少价格条件。从需求可以看出，价格条件是一个区间，而且是无穷大。

　　所以上面提到的穷举条件的Key-Value方法是做不到的。这里我们使用Redis的另一种数据结构来实现，有序集（Sorted Set）：

　　将所有产品加入到一个以Key为价格的有序集合中，值为产品ID，每个值对应的score就是产品价格的值。

　　这样，在Redis的有序集合中，可以使用ZRANGEBYSCORE命令，根据score（价格）区间获取对应的结果集。

　　至此，第三种方案的优化已经全部结束，数据查询和计算已经通过缓存的方式分离了。

　　在每次搜索中，您只需要搜索 Redis 几次即可获得结果。查询速度满足验收要求。微信公众号搜索，开始写作，关注和回复编程资源，接收各种经典学习资料。

　　扩展

　　①分页

　　这里你可能发现了一个严重的功能缺陷，列表查询怎么可能没有分页。是的，让我们马上看看Redis是如何实现分页的。

　　分页主要涉及排序。为简单起见，我们以创建时间为例。如图：

　　图中蓝色部分是以创建时间为分数的商品有序集合，蓝色下方的结果集是条件计算的结果。通过ZINTERSTORE命令，将结果集的权重赋值为0，商品时间结果为1，取交集得到的结果集被赋予一个新的有序的创建时间点集。对新结果集的操作可以得到分页所需的所有数据：

　　②数据更新

　　更新索引数据有两种方式。一种是通过修改商品数据立即触发更新操作，另一种是通过定时脚本进行批量更新。

　　这里需要注意的是，关于索引内容的更新，如果Key被暴力删除，则需要重新设置Key。

　　因为Redis中的这两个操作不会原子地执行，所以中间可能会有空白。建议仅从集合中移除无效元素并添加新元素。

　　③性能优化

　　Redis 是内存级操作，所以单次查询会很快。但是如果我们的实现会执行多个Redis操作，Redis的多次连接时间可能是不必要的时间消耗。通过使用 MULTI 命令，打开一个事务，将 Redis 的多个操作放在一个事务中，最后通过 EXEC 原子地执行。

　　注意：这里所谓的事务只是在一个连接中执行多个操作。如果在执行过程中发生故障，则不会回滚。

　　总结

　　这只是一个简单的使用Redis优化查询和搜索的demo。与现有的开源搜索引擎相比，重量更轻，学习成本相应更低。其次，它的一些想法类似于开源搜索引擎。如果添加词分析，也可以实现类似的全文搜索功能。

支持下