数据库分库分表和带来的唯一ID、分页查询问题的解决

需求缘起（用一个公司的发展作为背景）

        1.还是个小公司的时候，注册用户就20w，每天活跃用户1w，每天最大单表数据量就1000，然后高峰期每秒并发请求最多就10，此时一个16核32G的服务器，每秒请求支撑在2000左右，负载合理，没有太大压力，基本没有宕机风险。

        2.当注册用户达到2000W，每天活跃用户数100W，每天单表新增数据量达到50W条，高峰期请求量达到1W。经过一段时间的运行，单标数据量会越来越多，带来的问题

 　　　  2.1 数据库服务器的IO，网络宽带，CPU负载，内存消耗都会达到非常高，服务器已经不堪重负

　　　　2.2 高峰时期，单表数据量本来就很大，加上数据库服务器负载太高，导致性能下降，此时SQL的性能就更差了，用户体验贼差， 点一个按钮要很久才有响应,如果服务器的配置再低一点的话，数据库可能直接宕机

 　　3. 实现一个基本的分库分表的思路，将一台数据库服务器变成5台数据库，就能有5个库，5个表，这样可以将表中的数据按照ID分别通过同一个映射方法，分布到这5个库中。此时写入数据的时候，需要借助数据库中间件，比如shardng-jdbc或者Mycat。查询的时候先通过一步映射到具体的数据库，再进行查询。

 　　4. 当用户量再次增长时，只能继续分表，比如将一张表拆分成1024张表，这样在操作数据的时候，需要两次路由，一次找到在哪个数据库，一次找到在哪张表。

 　　5. 除了分表，数据库还可以做主从架构，主服务器用以写入，从服务器用以查询，根据业务需求具体实现即可。

分库分表带来的问题

 　　1. 分库分表之后一个必然的问题，如何获取一个全局为一个ID？因为表中的数据是通过ID路由映射的，ID不能重复。

 　　2. 就算有了全局唯一的ID，那面对分页查询的需求，应该怎么处理呢？

 

　　唯一ID的生成

　　下面列举几种常见的唯一ID生成方案，需要满足两大核心需求：1.全局唯一  2趋势有序

 　　1. 用数据库的auto_increment（自增ID）来生成，每次通过写入数据库一条记录，利用数据库ID自增的特性获取唯一，有序的ID。

 　　　　优点：使用数据库原有的功能，相对简单；能够保证唯一；能够保证递增性；ID之间的步长是固定且可以自定义的

 　　　　缺点：可用性难以保证，当生成ID的那台服务器宕机，系统就玩不转了；由于写入是单点的，所以扩展性差，性能上限取决于数据库的写性能。

 　　2. 用UUID

 　　　　优点：简单方便；全球唯一，在遇见数据迁移、合并或者变更时可以从容应对；

 　　　　缺点：没有递增性；UUID是很长的字符串，作为主键对存储空间有一定要求，查询效率也较低。

 　　3. 使用Redis生成ID，主要利用Redis是单线程的，所以也可以用来生成唯一ID。当使用的是Redis集群的时候，比如集群中有5台Redis，初始化每台Redis的值为1,2,3,4,5，设置步长为5，并且确定一个不随机的负载均衡策略，能够保证有序，唯一。

 　　　　优点：不依赖数据库，灵活，且性能相对于数据库有一定提高；使用Redis集群策略还能排除单点故障问题；ID天然有序

 　　　　缺点：如果系统中没有Redis，还需要引入新的组件；编码和配置工作量大

　　 4. 使用Twitter的snowflake算法；其核心思想是一个64位long型ID，使用41bit作为毫秒数，10bit作为机器的ID（5个bit是数据中心，5个bit的机器ID），12bit作为毫秒内的流水号（意味着每个节点在每毫秒可以产生 4096 个 ID），最后还有一个符号位，永远是0。具体实现的代码可以参看https://github.com/twitter/snowflake。可以根据自身需求进行一定的修改。

　　　　优点：不依赖数据库，灵活方便，性能优于数据库；ID按照时间在单机上是递增的

　　　　缺点：单机上递增，但是当分布式环境下每台机器的时钟不可能完全同步，有时并不能做做全局递增。

　　 5. 使用zookeeper生成唯一ID，主要通过znode数据版本来生成序列号，可以生成32为和64为的数据版本号。很少使用，因为是多步调用API，并发情况下还需要考虑分布式锁，不是很理想。

　　 6. MongoDB的ObjectID，和snowflake算法类似。4字节Unix时间戳，3字节机器编码，2字节进程编码，3字节随机数

　　分库分表下的分页查询

　　假设有一张用户表，经过分库分表之后，现在均匀分布在2台服务器实例上。业务需要查询“最近注册的第3页用户”，虽然数据库有分库用的全局的ID，但是没有排序条件time的全局视野，此时应该怎么做呢？

 　　1. 全局视野法：因为不清楚按照时间排序之后的第三页数据到底是如何分布在数据库上的，所以必须每个库都返回3页数据，所得到的6页数据在服务层进行内存排序，得到全局视野，再取第3页数据。

 　　　　优点：通过服务层修改，扩大数据查询量，得到全局视野，业务无损，精确

 　　　　缺点（显而易见）：每个分库都需要返回更多的数据，增大网络传输量；除了数据库要按照time排序，服务层也需要二次排序，损耗性能；随着页码的增大，性能极具下降，数据量和排序量都将大增，性能平方级下降。

 　　 2. 业务折中

 　　　　2.1 禁止跳页查询，不提供“直接跳到指定页面”的功能，只提供下一页的功能。极大的降低技术方案的复杂度。第一页的选取方法和全局视野法一样，但是点击下一页时：

 　　　　　　2.1.1先找到上一页的time的最大值，作为第二页数据拉去的查询条件，只取每页的记录数，

 　　　　　　2.2.2这样服务层还是获得两页数据，再做一次排序，获取一页数据。

 　　　　　　2.2.3改进了不会因为页码增大而导致数据的传输量和排序量增大

 　　 3. 允许数据精度丢失：需要考虑业务员上是否接受在页码较大是返回的数据不是精准的数据。

 　　　　3.1在数据量较大，且ID映射分布足够随机的话，应该是满足等概率分布的情况的，所以取一页数据，我们在每个数据库中取前半页。

 　　　　3.2当然这样的到的结果并不是精准的，但是当实际业务可以接受的话， 此时的技术方案的复杂度变大大降低。也不需要服务层内存排序了。

 　　 4. 二次查询法：既满足业务的精确需求，也无需业务折中。现在假设每页显示10条数据，要查第三页，数据分了两个库。 正常的语句是 select * from table order by time offset 20 limit 10，取偏移20个之后的10个

 　　　　4.1首次查询查询每个库的select * from table order by time offset 10 limit 10；得到10条数据。这里的offset是总offset/分库数

 　　　　4.2 服务层得到来自两个分库的结果集，得到最小的time，也就是最顶层的time，这个time满足最少有10条记录在它前面，然后分别记录每个库的最大time

 　　　　4.3 分别再次查询最小time->每个库上一次的最大time的数据，得到每个库的查询结果

 　　　　4.4 在每个集合的最小time都是相同的，所以可以得到该最小time在整个数据库中的offset，加起来就是这个最小time在全局库的offset位置。

 　　　　4.5 再将第二次查询的结果集拼起来和得到的最小time的offset，推导出 offset 20 limit 10的一页记录。

 　　　　优点：可以精确得到业务数据，且每次返回的数据量都非常小，不会随着页码增加而数据量增大。

 　　　　缺点：需要进行两次数据库查询

转载于:https://www.cnblogs.com/hanzhong/p/10440286.html