高效mysql分页查询,mysql分库分表后_数据库

资料来源：网络整理时间：2023/3/5 14:48:27 共计：3565 浏览

高效mysql分页查询，mysql分库分表后？

mysql分库分表后，跨库跨表搜索如何排序？

数据库分库分表可以说是非常常见的一种应对单表数据量过大的手段了。例如：我们的订单表，通常情况下，我们会将运单表按照1个月、3个月、6个月以上的维度进行划分，自然也就会按照时间进行订单表的水平切分。

这种情况下的分库分表非常好处理，因为我们能够强制的按照时间线将订单存储到不同的库中。但是，有可能我们的电商系统用户量大，订单量多，一天就有几十万单，可能仅仅半个月，我们的订单量就会上千万，再加上订单的商品数据表，如果不分表，订单表可能就会把系统给拖垮。

那么我们就必须面临将1个月内的数据也要按照一定的规则进行分库分表。我们可以将订单表一分为二，分为了OrderDB1，OrderDB2，按照我们按照订单号来进行区分。订单号是单数，我们就放到OrderDB1中，订单号是双数，我们就放到OrderDB2中。如此一来，订单表的数据就被平均的分配到了两个数据库的表中了，单表的压力也就降低了。

而这样分库分表以后，我们的订单表如果需要进行分页的排序就非常困难了，两个数据库中的数据如何进行跨库的分页排序查询呢？

一般我们有三种方法，分别是：全局视野方式、允许精度损失方式、二次查找方式。

先说全局视野方式

通常情况下我们要查找第三页的100条订单数据，我们会写一个SQL

select * from T order by time offset 200 limit 100;

但是分库以后，这100条数据可能存在很多种方式。

有可能是平均分布（极端情况）

也有可能是全部来自一个库（极端情况）

还有可能是散乱分布的（通常情况）

由于情况根据我们的Order By条件有很多的可能，所以我们很难知道第三页的数据到底从哪个库的哪个位置开始取数。如果我们需要精准的取到数据，那么就必须重新还原单库的那种全局视野。

如何还原全局视野呢？

还是用我们要查询第三页的数据来举例，我们之所以失去了全局视野，是因为我们无法一次性得到所有的数据结果，那么还原全局视野的方式就是让我们能够得到所有的数据结果。因此，我们可以将两个库中的从第一页到第三页的全部数据查询出来，然后在内存中合并后再进行排序，然后就能够取出正确的第三页数据了。

自然，我们的sql也就发生了变化，从

select * from T order by time offset 200 limit 100;

改为

select * from T order by time offset 0 limit 100+200;

使用全局视野方式的好处很明显，就是数据绝对的精准。但是缺点也同样明显，当查询的数据量大时，内存的消耗就会变多，而且在页码增大的时候，查询效率会急速的下降。当我们有N个数据库，我们需要从查询X到X+Y区间的数据时，那么我们的内存中将会需要组合N*(X+Y)条数据然后排序。

既然全局查询的方式有缺点，那我们就来解决这个缺点，但是肯定会有一些其他方面的牺牲。

允许精度损失方式

允许精度损失其实非常的好理解，就是我不去管数据在两个DB中是如何的分布的，我只是平均的从两个库中排序后取出50条数据，然后组合成为100条进行显示。

当然，这种方式的精度就是根据你排序的条件和数据存储的方式不同而变化的了。假设我们的数据都是按照时间有序的存储的，我们的排序也是根据时间来进行排序的，那么我们得到的结果就会比较精准。

但如果我们的数据是随机插入多个DB的，我们要按照时间进行排序查找，或者我们的数据是按照时间顺序插入DB的，但是我们需要根据其他条件进行查找时，数据的精度就会很差。这就看我们对于业务的需要是什么样的了。

不过，使用这种方式查找，我们就可以不用考虑性能上的问题，查询的复杂程度很低，只要我们的业务没有过多的要求，那么使用这种查找方式是最为推荐的。

当然，如果你的业务不允许这样的情况出现，还需要满足交互、效率等等各种需求，那么，就我们还可以使用下面这个方式。

二次查询方式

这可以说是解决分库查询的究极武器了，能够保证数据的精准度、查询的效率、用户的交互页面，牺牲的只是小小的性能开销和一些代码难度的上升。

方式其实也不难，假设我们要查询第21页的数据，每页5条。这个时候，我们先假设数据是平均分布的，但是我们在每个库都查询全量的5条数据。也就是：

select * from T order by time offset 100 limit 5;

这时，我们得到的数据可能是这样的。

而两个DB中，最小的时间是1487500001【minTime】，这个时间记录下来。两个DB中各自的最大时间也记录下来，分别是DB1：1487500041【maxTime1】和 DB2：1487500061【maxTime2】。

这时，我们在使用时间去两个数据库中再次进行查询。

select * from T where time between minTime and maxTime1 order by time; select * from T where time between minTime and maxTime2 order by time;

由于之前minTime来自于DB1，因此，DB1的数据不会发生变化，但是DB2中的条件被放宽了，因此可能会查询出更多的数据。结果可能如下：

而两个结果集合并以后，相当于就获得了全局视野，也就可以很容易的找出这一页需要的5条数据了。

当然，我们还可以借助elasticsearch来完成分库的排序查找，由于elasticsearch引入了缓存机制，能够让查询更快。

版权说明：
本网站凡注明“广州京杭原创”的皆为本站原创文章，如需转载请注明出处！
本网转载皆注明出处，遵循行业规范，如发现作品内容版权或其它问题的，请与我们联系处理！
欢迎扫描右侧微信二维码与我们联系。

·上一条：mysql 查询去除相同,谁能用最简单的语言或者例子说下Mysql_数据库 | ·下一条：mysql 查询子串,sql怎样定位一个字符所在的位置_数据库