一、需求 互联网很多业务都有分页拉取数据的需求，例如： 1、微信消息过多时，拉取第N页消息 2、京东下单过多时，拉取第N页订单 3、浏览58同城，查看第N页帖子 这些业务场景对应的消息表，订单表，帖子表分页拉取需求有这样一些特点： 1、有一个业务主键id, 例如msg_id, order_id, tiezi_id 2、分页排序是按照非业务主键id来排序的，业务中经常按照时间time来排序order by 在数据量不大时，可以通过在排序字段time上建立索引，利用SQL提供的offset/limit功能就能满足分页查询需求： select * from t_msg order by time offset 200 limit 100 select * from t_order order by time offset 200 limit 100 select * from t_tiezi order by time offset 200 limit 100 此处假设一页数据为100条，均拉取第3页数据。 二、分库需求 高并发大流量的互联网架构，一般通过服务层来访问数据库，随着数据量的增大，数据库需要进行水平切分，分库后将数据分布到不同的数据库实例（甚至物理机器）上，以达到降低数据量，增加实例数的扩容目的。 一旦涉及分库，逃不开“分库依据”patition key的概念，使用哪一个字段来水平切分数据库呢：大部分的业务场景，会使用业务主键id。 确定了分库依据patition key后，接下来要确定的是分库算法：大部分的业务场景，会使用业务主键id取模的算法来分库，这样即能够保证每个库的数据分布是均匀的，又能够保证每个库的请求分布是均匀的，实在是简单实现负载均衡的好方法，此法在互联网架构中应用颇多。 举一个更具体的例子： 用户库user，水平切分后变为两个库，分库依据patition key是uid，分库算法是uid取模：uid%2余0的数据会落到db0，uid%2余1的数据会落到db1。 仍然是上述用户库的例子，如果业务要查询“最近注册的第3页用户”，该如何实现呢？单库上，可以 select * from t_user order by time offset 200 limit 100 变成两个库后，分库依据是uid，排序依据是time，数据库层失去了time排序的全局视野，数据分布在两个库上，此时该怎么办呢？ 如何满足“跨越多个水平切分数据库，且分库依据与排序依据为不同属性，并需要进行分页”的查询需求，实现 select * from T order by time offset X limit Y的跨库分页SQL，是本文将要讨论的技术问题。 二、全局视野法 如上图所述，服务层通过uid取模将数据分布到两个库上去之后，每个数据库都失去了全局视野，数据按照time局部排序之后，不管哪个分库的第3页数据，都不一定是全局排序的第3页数据。 那到底哪些数据才是全局排序的第3页数据呢，暂且分三种情况讨论。 2.1、极端情况，两个库的数据完全一样 如果两个库的数据完全相同，只需要每个库offset一半，再取半页，就是最终想要的数据（如上图中粉色部分数据）。 2.2、极端情况，结果数据来自一个库 也可能两个库的数据分布及其不均衡，例如db0的所有数据的time都大于db1的所有数据的time，则可能出现：一个库的第3页数据，就是全局排序后的第3页数据（如上图中粉色部分数据）。 2.3、一般情况，每个库数据各包含一部分 正常情况下，全局排序的第3页数据，每个库都会包含一部分（如上图中粉色部分数据）。 于不清楚到底是哪种情况，所以必须每个库都返回3页数据，所得到的6页数据在服务层进行内存排序，得到数据全局视野，再取第3页数据，便能够得到想要的全局分页数据。 再总结一下这个方案的步骤： （1）将order by time offset X limit Y，改写成order by time offset 0 limit X+Y （2）服务层将改写后的SQL语句发往各个分库：即例子中的各取3页数据 （3）假设共分为N个库，服务层将得到N*(X+Y)条数据：即例子中的6页数据 （4）服务层对得到的N*(X+Y)条数据进行内存排序，内存排序后再取偏移量X后的Y条记录，就是全局视野所需的一页数据 方案优点：通过服务层修改SQL语句，扩大数据召回量，能够得到全局视野，业务无损，精准返回所需数据。 方案缺点（显而易见）： （1）每个分库需要返回更多的数据，增大了网络传输量（耗网络）； （2）除了数据库按照time进行排序，服务层还需要进行二次排序，增大了服务层的计算量（耗CPU）； （3）最致命的，这个算法随着页码的增大，性能会急剧下降，这是因为SQL改写后每个分库要返回X+Y行数据：返回第3页，offset中的X=200；假如要返回第100页，offset中的X=9900，即每个分库要返回100页数据，数据量和排序量都将大增，性能平方级下降。 三、业务折衷法 “全局视野法”虽然性能较差，但其业务无损，数据精准，不失为一种方案，有没有性能更优的方案呢？ 技术方案需要折衷，在技术难度较大的情况下，业务需求的折衷能够极大的简化技术方案。 3.1、业务折衷一：禁止跳页查询 在数据量很大，翻页数很多的时候，很多产品并不提供“直接跳到指定页面”的功能，而只提供“下一页”的功能，这一个小小的业务折衷，就能极大的降低技术方案的复杂度。 如上图，不够跳页，那么第一次只能够查第一页： （1）将查询order by time offset 0 limit 100，改写成order by time where time>0 limit 100 （2）上述改写和offset 0 limit 100的效果相同，都是每个分库返回了一页数据（上图中粉色部分）； （3）服务层得到2页数据，内存排序，取出前100条数据，作为最终的第一页数据，这个全局的第一页数据，一般来说每个分库都包含一部分数据（如上图粉色部分）； 点击“下一页”时，需要拉取第二页数据，在第一页数据的基础之上，能够找到第一页数据time的最大值： 这个上一页记录的time_max，会作为第二页数据拉取的查询条件： （1）将查询order by time offset 100 limit 100，改写成order by time where time>$time_max limit 100 （2）这下不是返回2页数据了（“全局视野法，会改写成offset 0 limit 200”），每个分库还是返回一页数据（如上图中粉色部分）； （3）服务层得到2页数据，内存排序，取出前100条数据，作为最终的第2页数据，这个全局的第2页数据，一般来说也是每个分库都包含一部分数据（如上图粉色部分）； 如此往复，查询全局视野第100页数据时，不是将查询条件改写为offset 0 limit 9900+100（返回100页数据），而是改写为time>$time_max99 limit 100（仍返回一页数据），以保证数据的传输量和排序的数据量不会随着不断翻页而导致性能下降。 3.2、业务折衷二：允许数据精度损失 “全局视野法”能够返回业务无损的精确数据，在查询页数较大，例如第100页时，会有性能问题，此时业务上是否能够接受，返回的100页不是精准的数据，而允许有一些数据偏差呢？ 数据库分库-数据均衡原理 使用patition key进行分库，在数据量较大，数据分布足够随机的情况下，各分库所有非patition key属性，在各个分库上的数据分布，统计概率情况是一致的。 例如，在uid随机的情况下，使用uid取模分两库，db0和db1： （1）性别属性，如果db0库上的男性用户占比70%，则db1上男性用户占比也应为70% （2）年龄属性，如果db0库上18-28岁少女用户比例占比15%，则db1上少女用户比例也应为15% （3）时间属性，如果db0库上每天10:00之前登录的用户占比为20%，则db1上应该是相同的统计规律 … 利用这一原理，要查询全局100页数据，offset 9900 limit 100改写为offset 4950 limit 50，每个分库偏移4950（一半），获取50条数据（半页），得到的数据集的并集，基本能够认为，是全局数据的offset 9900 limit 100的数据，当然，这一页数据的精度，并不是精准的。 根据实际业务经验，用户都要查询第100页网页、帖子、邮件的数据了，这一页数据的精准性损失，业务上往往是可以接受的，但此时技术方案的复杂度便大大降低了，既不需要返回更多的数据，也不需要进行服务内存排序了。 四、终极武器-二次查询法 有没有一种技术方案，即能够满足业务的精确需要，无需业务折衷，又高性能的方法呢？这就是接下来要介绍的终极武器：“二次查询法”。 为了方便举例，假设一页只有5条数据，查询第200页的SQL语句为select * from T order by time offset 1000 limit 5; 4.1、步骤一：查询改写 将select * from T order by time offset 1000 limit 5 改写为select * from T order by time offset 500 limit 5 并投递给所有的分库，注意，这个offset的500，来自于全局offset的总偏移量1000，除以水平切分数据库个数2。 如果是3个分库，则可以改写为select * from T order by time offset 333 limit 5 假设这三个分库返回的数据(time, uid)如下： 可以看到，每个分库都是返回的按照time排序的一页数据。 4.2、步骤二：找到所返回3页全部数据的最小值 第一个库，5条数据的time最小值是1487501123 第二个库，5条数据的time最小值是1487501133 第三个库，5条数据的time最小值是1487501143 故，三页数据中，time最小值来自第一个库，time_min=1487501123，这个过程只需要比较各个分库第一条数据，时间复杂度很低。 4.3、步骤三：查询二次改写 第一次改写的SQL语句是select * from T order by time offset 333 limit 5 第二次要改写成一个between语句，between的起点是time_min，between的终点是原来每个分库各自返回数据的最大值： 第一个分库，第一次返回数据的最大值是1487501523。 所以查询改写为select * from T order by time where time between time_min and 1487501523 第二个分库，第一次返回数据的最大值是1487501323。 所以查询改写为select * from T order by time where time between time_min and 1487501323 第三个分库，第一次返回数据的最大值是1487501553。 所以查询改写为select * from T order by time where time between time_min and 1487501553 相对第一次查询，第二次查询条件放宽了，故第二次查询会返回比第一次查询结果集更多的数据，假设这三个分库返回的数据(time, uid)如下： 可以看到： 由于time_min来自原来的分库一，所以分库一的返回结果集和第一次查询相同（所以其实这次访问是可以省略的）； 分库二的结果集，比第一次多返回了1条数据，头部的1条记录（time最小的记录）是新的（上图中粉色记录）； 分库三的结果集，比第一次多返回了2条数据，头部的2条记录（time最小的2条记录）是新的（上图中粉色记录）； 4.4、步骤四：在每个结果集中虚拟一个time_min记录，找到time_min在全局的offset 在第一个库中，time_min在第一个库的offset是333 在第二个库中，(1487501133, uid_aa)的offset是333（根据第一次查询条件得出的），故虚拟time_min在第二个库的offset是331 在第三个库中，(1487501143, uid_aaa)的offset是333（根据第一次查询条件得出的），故虚拟time_min在第三个库的offset是330 综上，time_min在全局的offset是333+331+330=994 4.5、步骤五：既然得到了time_min在全局的offset，就相当于有了全局视野，根据第二次的结果集，就能够得到全局offset 1000 limit 5的记录 第二次查询在各个分库返回的结果集是有序的，又知道了time_min在全局的offset是994，一路排下来，容易知道全局offset 1000 limit 5的一页记录（上图中黄色记录）。 是不是非常巧妙？这种方法的优点是：可以精确的返回业务所需数据，每次返回的数据量都非常小，不会随着翻页增加数据的返回量。 不足是：需要进行两次数据库查询。 五、总结 方法一：全局视野法 （1）将order by time offset X limit Y，改写成order by time offset 0 limit X+Y （2）服务层对得到的N*(X+Y)条数据进行内存排序，内存排序后再取偏移量X后的Y条记录。 这种方法随着翻页的进行，性能越来越低。 方法二：业务折衷法-禁止跳页查询 （1）用正常的方法取得第一页数据，并得到第一页记录的time_max （2）每次翻页，将order by time offset X limit Y，改写成order by time where time>$time_max limit Y 以保证每次只返回一页数据，性能为常量。 方法三：业务折衷法-允许模糊数据 （1）将order by time offset X limit Y，改写成order by time offset X/N limit Y/N 方法四：二次查询法 （1）将order by time offset X limit Y，改写成order by time offset X/N limit Y （2）找到最小值time_min （3）between二次查询，order by time between $time_min and $time_i_max （4）设置虚拟time_min，找到time_min在各个分库的offset，从而得到time_min在全局的offset （5）得到了time_min在全局的offset，自然得到了全局的offset X limit Y
一、问题 假设页大小20，如从从库im_team_messsage0，im_team_messsage1查询，使用者编写的sql如下（只查询第1000行到第1010行）。 SELECT * FROM im_team_messsage ORDER BY send_at DESC LIMIT 1000, 10; shardingsphere的改写引擎会对此sql进行改写，可以看到ss将limit后解析成从第一行开始查询到第1010行。 SELECT * FROM im_team_messsage0 ORDER BY send_at DESC LIMIT 0, 1010; SELECT * FROM im_team_messsage1 ORDER BY send_at DESC LIMIT 0, 1010; 数据库端的查询压力都是差不多的, 因为都是要差不多要扫描1010行才能取得到数据。不同的是改写sql后, 客户端的内存消耗和网络消耗变大了。 shardingsphere巧妙地利用流式处理和优先级队列结合的方式(“流式归并”)，消除了客户端内存消耗的压力, 但是网络消耗的影响依然是无法消除。 二、如何避免使用limit带来的效率问题 有很多方法可以避免使用LIMIT进行分页。比如构建行记录数量与行偏移量的二级索引，或使用上次分页数据结尾ID作为下次查询条件的分页方式等。 2.1、使用上次分页数据结尾ID作为下次查询条件 前提：使用shardingsphere分布式id递增雪花算法保证id呈现连续性 使用上次分页数据结尾ID作为下次查询条件： 但同时需要注意的是，由于排序的需要，大量的数据仍然需要传输到ShardingSphere的内存空间。 因此，采用LIMIT这种方式分页，并非最佳实践。 由于LIMIT并不能通过索引查询数据，因此如果可以保证ID的连续性，通过ID进行分页是比较好的解决方案，例如： SELECT * FROM im_team_message WHERE id > 100000 AND id <= 100010 ORDER BY id; 或通过记录上次查询结果的最后一条记录的ID进行下一页的查询，例如： SELECT * FROM im_team_message WHERE id > 10000000 LIMIT 10; 同理，使用上次分页数据结尾的时间戳作为下次查询条件也可以（按照时间进行排序）。 2.2、二次查询法 这种方法能够满足业务的精确需要，无需业务折衷，又高性能的方法，缺点是业务代码复杂且需要进行两次数据库查询。 三、分页子查询（Mysql、Oracle） Oracle和SQLServer的分页都需要通过子查询来处理，ShardingSphere支持分页相关的子查询。 Oracle 支持使用rownum进行分页： SELECT * FROM (SELECT row_.*, rownum rownum_ FROM (SELECT o.order_id as order_id FROM t_order o JOIN t_order_item i ON o.order_id = i.order_id) row_ WHERE rownum <= ?) WHERE rownum > ? 目前不支持rownum + BETWEEN的分页方式。 MySQL和PostgreSQL都支持LIMIT分页，无需子查询： SELECT * FROM t_order o ORDER BY id LIMIT ? OFFSET ?
虽然 Apache ShardingSphere 希望能够完全兼容所有的SQL以及单机数据库，但分布式为数据库带来了更加复杂的场景。 Apache ShardingSphere 希望能够优先解决海量数据 OLTP 的问题，OLAP 的相关支持，会一点一点的逐渐完善。 一、SQL 支持项 路由至单数据节点 路由至多数据节点 不支持项 路由至多数据节点 对分片键进行操作 示例 支持的SQL 不支持的SQL DISTINCT支持情况详细说明 支持的SQL 不支持的SQL 1.1、支持项 1、路由至单数据节点 100%全兼容（目前仅MySQL，其他数据库完善中）。 2、路由至多数据节点 全面支持DML、DDL、DCL、TCL和部分DAL。支持分页、去重、排序、分组、聚合、关联查询（不支持跨库关联）。以下用最为复杂的DML举例： SELECT主语句 SELECT select_expr [, select_expr ...] FROM table_reference [, table_reference ...] [WHERE predicates] [GROUP BY {col_name | position} [ASC | DESC], ...] [ORDER BY {col_name | position} [ASC | DESC], ...] [LIMIT {[offset,] row_count | row_count OFFSET offset}] select_expr * | [DISTINCT] COLUMN_NAME [AS] [alias] | (MAX | MIN | SUM | AVG)(COLUMN_NAME | alias) [AS] [alias] | COUNT(* | COLUMN_NAME | alias) [AS] [alias] table_reference tbl_name [AS] alias] [index_hint_list] | table_reference ([INNER] | {LEFT|RIGHT} [OUTER]) JOIN table_factor [JOIN ON conditional_expr | USING (column_list)] 1.2、不支持项 1、路由至多数据节点 部分支持CASE WHEN CASE WHEN 中包含子查询不支持 CASE WHEN 中使用逻辑表名不支持（请使用表别名） 不支持 HAVING、UNION (ALL) 部分支持子查询 子查询中使用WHERE条件时，必须包含分片键，当外层查询中也包含分片键时，子查询和外层查询中的分片键必须保持一致 除了分页子查询的支持之外(详情请参考分页)，也支持同等模式的子查询。无论嵌套多少层，ShardingSphere都可以解析至第一个包含数据表的子查询，一旦在下层嵌套中再次找到包含数据表的子查询将直接抛出解析异常。 例如，以下子查询可以支持： SELECT COUNT(*) FROM (SELECT * FROM t_order) o; SELECT COUNT(*) FROM (SELECT * FROM t_order WHERE order_id = 1) o; SELECT COUNT(*) FROM (SELECT * FROM t_order WHERE order_id = 1) o WHERE o.order_id = 1; 以下子查询不支持： SELECT COUNT(*) FROM (SELECT * FROM t_order WHERE product_id = 1) o; SELECT COUNT(*) FROM (SELECT * FROM t_order WHERE order_id = 1) o WHERE o.order_id = 2;``` 简单来说，通过子查询进行非功能需求，在大部分情况下是可以支持的。比如分页、统计总数等；而通过子查询实现业务查询当前并不能支持。 由于归并的限制，子查询中包含聚合函数目前无法支持。 不支持包含schema的SQL。因为ShardingSphere的理念是像使用一个数据源一样使用多数据源，因此对SQL的访问都是在同一个逻辑schema之上。 2、对分片键进行操作 运算表达式和函数中的分片键会导致全路由。 假设create_time为分片键，则无法精确路由形如SQL： SELECT * FROM t_order WHERE to_date(create_time, 'yyyy-mm-dd') = '2019-01-01'; 由于ShardingSphere只能通过SQL字面提取用于分片的值，因此当分片键处于运算表达式或函数中时，ShardingSphere无法提前获取分片键位于数据库中的值，从而无法计算出真正的分片值。 当出现此类分片键处于运算表达式或函数中的SQL时，ShardingSphere将采用全路由的形式获取结果。 支持&不支持的 SQL： https://shardingsphere.apache.org/document/current/cn/features/sharding/use-norms/sql/ 二、分页 分页性能 性能瓶颈 ShardingSphere的优化 分页方案优化 分页子查询 完全支持MySQL、PostgreSQL和Oracle的分页查询，SQLServer由于分页查询较为复杂，仅部分支持。 2.1、分页性能 1、性能瓶颈 查询偏移量过大的分页会导致数据库获取数据性能低下，以MySQL为例： SELECT * FROM t_order ORDER BY id LIMIT 1000000, 10 这句SQL会使得MySQL在无法利用索引的情况下跳过1000000条记录后，再获取10条记录，其性能可想而知。 而在分库分表的情况下（假设分为2个库），为了保证数据的正确性，SQL会改写为： SELECT * FROM t_order ORDER BY id LIMIT 0, 1000010 即将偏移量前的记录全部取出，并仅获取排序后的最后10条记录。这会在数据库本身就执行很慢的情况下，进一步加剧性能瓶颈。 因为原SQL仅需要传输10条记录至客户端，而改写之后的SQL则会传输1,000,010 * 2的记录至客户端。 2.2、分页方案优化 由于LIMIT并不能通过索引查询数据，因此如果可以保证ID的连续性，通过ID进行分页是比较好的解决方案： SELECT * FROM t_order WHERE id > 100000 AND id <= 100010 ORDER BY id 或通过记录上次查询结果的最后一条记录的ID进行下一页的查询： SELECT * FROM t_order WHERE id > 100000 LIMIT 10 2.3、分页子查询 Oracle和SQLServer的分页都需要通过子查询来处理，ShardingSphere支持分页相关的子查询。 Oracle 支持使用rownum进行分页： SELECT * FROM (SELECT row_.*, rownum rownum_ FROM (SELECT o.order_id as order_id FROM t_order o JOIN t_order_item i ON o.order_id = i.order_id) row_ WHERE rownum <= ?) WHERE rownum > ? 目前不支持rownum + BETWEEN的分页方式。 SQLServer 支持使用TOP + ROW_NUMBER() OVER配合进行分页： SELECT * FROM (SELECT TOP (?) ROW_NUMBER() OVER (ORDER BY o.order_id DESC) AS rownum, * FROM t_order o) AS temp WHERE temp.rownum > ? ORDER BY temp.order_id 支持SQLServer 2012之后的OFFSET FETCH的分页方式： SELECT * FROM t_order o ORDER BY id OFFSET ? ROW FETCH NEXT ? ROWS ONLY 目前不支持使用WITH xxx AS (SELECT …)的方式进行分页。由于Hibernate自动生成的SQLServer分页语句使用了WITH语句，因此目前并不支持基于Hibernate的SQLServer分页。 目前也不支持使用两个TOP + 子查询的方式实现分页。 MySQL, PostgreSQL MySQL和PostgreSQL都支持LIMIT分页，无需子查询： SELECT * FROM t_order o ORDER BY id LIMIT ? OFFSET ? 三、解析器 特定SQL方言解析器 默认SQL方言解析器 RDL(Rule definition Language)方言解析器 ShardingSphere使用不同解析器支持SQL多种方言。对于未实现解析器的特定SQL方言，默认采用SQL92标准进行解析。 3.1、特定SQL方言解析器 PostgreSQL解析器 MySQL解析器 Oracle解析器 SQLServer解析器 注：MySQL解析器支持的方言包括MySQL、H2和MariaDB。 3.2、默认SQL方言解析器 其他SQL方言，如SQLite、Sybase、DB2和Informix等，默认采用SQL92标准进行解析。 3.3、RDL(Rule definition Language)方言解析器 ShardingSphere独有的RDL方言解析器。该解析器主要解析ShardingSphere内部的RDL方言，即自定义的SQL。请查阅RDL了解详情。 四、RDL 什么是 RDL? RDL使用实战 前置工作 启动ShardingProxy 创建分布式数据库和分片表 注意 4.1、什么是 RDL? RDL（Rule Definition Language）是ShardingSphere特有的内置SQL语言。用户可以使用RDL语言向SharidngSphere注入数据源资源、创建分片规则等，即向ShardingSphere注入数据库资源信息和分片规则信息。 RDL使得用户抛弃对传统Yaml或其他配置文件的依赖，像使用数据库一样，通过SQL进行资源信息的注入和规则的配置。 当前，RDL主要包括以下SQL内容： Create DATASOURCES，用于注入数据源信息。 // SQL CREATE DATASOURCES ( ds_key=host_name:host_port:db_name:user_name:pwd [, ds_key=host_name:host_port:db_name:user_name:pwd, ...] ) // Example CREATE datasources ( ds0=127.0.0.1:3306:demo_ds_0:root:pwd, ds1=127.0.0.1:3306:demo_ds_1:root:pwd) CREATE SHARDINGRULE，用于配置分片规则。 CREATE SHARDINGRULE ( sharding_table_name=sharding_algorithm(algorithm_property[, algothrim_property]) [, sharding_table_name=sharding_algorithm_type(algorithm_property[, algothrim_property]), ...] ) sharding_algorithm_type: {MOD | HASH_MODE} mod_algorithm_properties: sharding_column,shards_amount mod_hash_algorithm_properties: sharding_column,shards_amount // Example CREATE shardingrules ( t_order=hash_mod(order_id, 4), t_item=mod(item_id, 2) ) 4.2、RDL使用实战 1、前置工作 Start the service of MySQL instances Create MySQL databases (Viewed as the resources for ShardingProxy) Create a role or user with creating privileges for ShardingProxy Start the service of Zookeeper (For persisting configuration) 2、启动ShardingProxy Add governance and authentication setting item to the server.yaml (Please refer to the example in this file) Start the ShardingProxy (Instruction) 3、创建分布式数据库和分片表 连接到ShardingProxy 创建分布式数据库 CREATE DATABASE sharding_db; 使用新创建的数据库 USE sharding_db; 配置数据源信息 CREATE datasources ( ds0=127.0.0.1:3306:demo_ds_2:root:pwd, ds1=127.0.0.1:3306:demo_ds_3:root:pwd) 创建分片规则 CREATE shardingrule ( t_order=hash_mod(order_id, 4), t_item=mod(item_id, 2) ) 这里的 hash_mode 和 mod是自动分片算法的Key。详情请查阅 auto-sharding-algorithm。 创建切分表 CREATE TABLE `t_order` ( `order_id` int NOT NULL, `user_id` int NOT NULL, `status` varchar(45) DEFAULT NULL, PRIMARY KEY (`order_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 CREATE TABLE `t_item` ( `item_id` int NOT NULL, `order_id` int NOT NULL, `user_id` int NOT NULL, `status` varchar(45) DEFAULT NULL, PRIMARY KEY (`item_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 删除切分表 DROP TABLE t_order; DROP TABLE t_item; 删除分布式数据库 DROP DATABASE sharding_db 注意： 当前, DROP DB只会移除逻辑的分布式数据库，不会删除用户真实的数据库 (TODO)。 DROP TABLE会将逻辑分片表和数据库中真实的表全部删除。 CREATE DB只会创建逻辑的分布式数据库，所以需要用户提前创建好真实的数据库（TODO）。 自动分片算法会持续增加，从而覆盖用户各大分片场景 (TODO)。 重构ShardingAlgorithmPropertiesUtil（TODO）。 保证所有客户端完成RDL执行（TODO）。 增加 ALTER DB 和 ALTER TABLE的支持（TODO）。 五、ShardingSphere 配置说明 5.1、配置示例 spring: shardingsphere: datasource: #数据源配置 names: ds0,ds1 # 主数据源 ds0: type: com.alibaba.druid.pool.DruidDataSource driver-class-name: com.mysql.jdbc.Driver url: jdbc:mysql://localhost:3306/ds0?characterEncoding=utf-8 username: root password: root ds1: type: com.alibaba.druid.pool.DruidDataSource driver-class-name: com.mysql.jdbc.Driver url: jdbc:mysql://localhost:3306/ds1?characterEncoding=utf-8 username: root password: root sharding: tables: tab_user: #逻辑表 actual-data-nodes: ds$->{0..1}.tab_user$->{0..1} #表数量配置 database-strategy: inline: sharding-column: age #分库分片键 algorithm-expression: ds$->{age % 2} table-strategy: inline: sharding-column: id #分表分片键 algorithm-expression: tab_user$->{id % 3} # key-generator: #id生成策略 # column: id # type: SNOWFLAKE props: # 开启SQL显示，默认false sql: show: true tab_user ：是逻辑表名称，分表后创建的表应该是 tab_user_xxx。 actual-data-nodes ：是数据节点由数据源和数据表组成，也就是真实表，使用行表达式 {0..1} 表示有 tab_user0 到 tab_user1 共2张表；shardingsphere 不会自动创建表，需要使用 脚本定时或手动提前创建好。 table-strategy ：是分片策略，根据需求实现具体的分片策略，inline 为行表达式， sharding-column 为自定义分片。 algorithm-expression ：是算法表达式，根据 id 取模尾数为 n 的路由到后缀为 n 的表中（tab_usern）。 key-generator ：是主键生成，SNOWFLAKE 是Twitter的分布式 ID 生成算法。 5.2、时间字段分片（如日志表） sharding: tables: sys_log: # 逻辑表 actual-data-nodes: ds0.sys_log_20200116 # 默认表配置 table-strategy: standard: sharding-column: log_time # 分片列 preciseAlgorithmClassName: com.payn.config.SysLogDataTableShardingAlgorithm #精确分片 rangeAlgorithmClassName: com.payn.config.SysLogDataTableShardingAlgorithm #范围分片 key-generator: # id 生成策略 column: id type: SNOWFLAKE preciseAlgorithmClassName 和 rangeAlgorithmClassName 为分片支持和实现请查看 分片算法 ，下面是按天分片实现，按月分片只需要改动部分代码即可 5.3、关于查询： 分片后，如果查询 where 条件没有带分片字段的话会去扫描配置的所有真实数据表 = actual-data-nodes，最后将匹配到的数据合并为一个结果集返回，保存数据时找不到对应的表会报表不存在。 复杂SQL或者UNION可能会不支持，具体 SQL使用规范。
一、流程 ShardingSphere的3个产品的数据分片主要流程是完全一致的。 核心由 SQL解析 => 执行器优化 => SQL路由 => SQL改写 => SQL执行 => 结果归并的流程组成。 1.1、SQL解析 分为词法解析和语法解析。 先通过词法解析器将SQL拆分为一个个不可再分的单词。再使用语法解析器对SQL进行理解，并最终提炼出解析上下文。 解析上下文包括表、选择项、排序项、分组项、聚合函数、分页信息、查询条件以及可能需要修改的占位符的标记。 1.2、执行器优化 合并和优化分片条件，如OR等。 1.3、SQL路由 根据解析上下文匹配用户配置的分片策略，并生成路由路径。目前支持分片路由和广播路由。 1.4、SQL改写 将SQL改写为在真实数据库中可以正确执行的语句。SQL改写分为正确性改写和优化改写。 1.5、SQL执行 通过多线程执行器异步执行。 1.6、结果归并 将多个执行结果集归并以便于通过统一的JDBC接口输出。结果归并包括流式归并、内存归并和使用装饰者模式的追加归并这几种方式。 二、解析引擎 相对于其他编程语言，SQL是比较简单的。 不过，它依然是一门完善的编程语言，因此对SQL的语法进行解析，与解析其他编程语言（如：Java语言、C语言、Go语言等）并无本质区别。 2.1、抽象语法树 解析过程分为词法解析和语法解析。 词法解析器用于将SQL拆解为不可再分的原子符号，称为Token。并根据不同数据库方言所提供的字典，将其归类为关键字，表达式，字面量和操作符。 再使用语法解析器将SQL转换为抽象语法树。 例如，以下SQL： SELECT id, name FROM t_user WHERE status = 'ACTIVE' AND age > 18 解析之后的为抽象语法树见下图。 为了便于理解，抽象语法树中的关键字的Token用绿色表示，变量的Token用红色表示，灰色表示需要进一步拆分。 最后，通过对抽象语法树的遍历去提炼分片所需的上下文，并标记有可能需要改写的位置。 供分片使用的解析上下文包含查询选择项（Select Items）、表信息（Table）、分片条件（Sharding Condition）、自增主键信息（Auto increment Primary Key）、排序信息（Order By）、分组信息（Group By）以及分页信息（Limit、Rownum、Top）。 SQL的一次解析过程是不可逆的，一个个Token的按SQL原本的顺序依次进行解析，性能很高。 考虑到各种数据库SQL方言的异同，在解析模块提供了各类数据库的SQL方言字典。 2.2、SQL解析引擎 第三代SQL解析器则从3.0.x版本开始，ShardingSphere尝试使用ANTLR作为SQL解析的引擎，并计划根据DDL -> TCL -> DAL –> DCL -> DML –>DQL这个顺序，依次替换原有的解析引擎，目前仍处于替换迭代中。 使用ANTLR的原因是希望ShardingSphere的解析引擎能够更好的对SQL进行兼容。对于复杂的表达式、递归、子查询等语句，虽然ShardingSphere的分片核心并不关注，但是会影响对于SQL理解的友好度。 经过实例测试，ANTLR解析SQL的性能比自研的SQL解析引擎慢3-10倍左右。为了弥补这一差距，ShardingSphere将使用PreparedStatement的SQL解析的语法树放入缓存。 因此建议采用PreparedStatement这种SQL预编译的方式提升性能。 第三代SQL解析引擎的整体结构划分如下图所示。 三、路由引擎 根据解析上下文匹配数据库和表的分片策略，并生成路由路径。 对于携带分片键的SQL，根据分片键的不同可以划分为单片路由(分片键的操作符是等号)、多片路由(分片键的操作符是IN)和范围路由(分片键的操作符是BETWEEN)。 不携带分片键的SQL则采用广播路由。 分片策略通常可以采用由数据库内置或由用户方配置。 数据库内置的方案较为简单，内置的分片策略大致可分为尾数取模、哈希、范围、标签、时间等。 由用户方配置的分片策略则更加灵活，可以根据使用方需求定制复合分片策略。 如果配合数据自动迁移来使用，可以做到无需用户关注分片策略，自动由数据库中间层分片和平衡数据即可，进而做到使分布式数据库具有的弹性伸缩的能力。 在ShardingSphere的线路规划中，弹性伸缩将于4.x开启。 3.1、分片路由 用于根据分片键进行路由的场景，又细分为直接路由、标准路由和笛卡尔积路由这3种类型。 满足直接路由的条件相对苛刻，它需要通过Hint（使用HintAPI直接指定路由至库表）方式分片，并且是只分库不分表的前提下，则可以避免SQL解析和之后的结果归并。 因此它的兼容性最好，可以执行包括子查询、自定义函数等复杂情况的任意SQL。直接路由还可以用于分片键不在SQL中的场景。 标准路由是ShardingSphere最为推荐使用的分片方式，它的适用范围是不包含关联查询或仅包含绑定表之间关联查询的SQL。 当分片运算符是等于号时，路由结果将落入单库（表），当分片运算符是BETWEEN或IN时，则路由结果不一定落入唯一的库（表），因此一条逻辑SQL最终可能被拆分为多条用于执行的真实SQL。 举例说明，如果按照order_id的奇数和偶数进行数据分片，一个单表查询的SQL如下： SELECT * FROM t_order WHERE order_id IN (1, 2); 那么路由的结果应为： SELECT * FROM t_order_0 WHERE order_id IN (1, 2); SELECT * FROM t_order_1 WHERE order_id IN (1, 2); 绑定表的关联查询与单表查询复杂度和性能相当。举例说明，如果一个包含绑定表的关联查询的SQL如下： SELECT * FROM t_order o JOIN t_order_item i ON o.order_id=i.order_id WHERE order_id IN (1, 2); 那么路由的结果应为： SELECT * FROM t_order_0 o JOIN t_order_item_0 i ON o.order_id=i.order_id WHERE order_id IN (1, 2); SELECT * FROM t_order_1 o JOIN t_order_item_1 i ON o.order_id=i.order_id WHERE order_id IN (1, 2); 可以看到，SQL拆分的数目与单表是一致的。 笛卡尔路由是最复杂的情况，它无法根据绑定表的关系定位分片规则，因此非绑定表之间的关联查询需要拆解为笛卡尔积组合执行。 如果上个示例中的SQL并未配置绑定表关系，那么路由的结果应为： SELECT * FROM t_order_0 o JOIN t_order_item_0 i ON o.order_id=i.order_id WHERE order_id IN (1, 2); SELECT * FROM t_order_0 o JOIN t_order_item_1 i ON o.order_id=i.order_id WHERE order_id IN (1, 2); SELECT * FROM t_order_1 o JOIN t_order_item_0 i ON o.order_id=i.order_id WHERE order_id IN (1, 2); SELECT * FROM t_order_1 o JOIN t_order_item_1 i ON o.order_id=i.order_id WHERE order_id IN (1, 2); 笛卡尔路由查询性能较低，需谨慎使用。 3.2、广播路由 对于不携带分片键的SQL，则采取广播路由的方式。根据SQL类型又可以划分为全库路由、全库表路由、全实例路由、单播路由和阻断路由这5种类型。 全库路由用于处理对数据库的操作，包括用于库设置的SET类型的数据库管理命令，以及TCL这样的事务控制语句。 全库表路由用于处理对数据库中与其逻辑表相关的所有真实表的操作，主要包括不带分片键的DQL和DML，以及DDL等。 全实例路由用于DCL操作，授权语句针对的是数据库的实例。无论一个实例中包含多少个Schema，每个数据库的实例只执行一次。 单播路由用于获取某一真实表信息的场景，它仅需要从任意库中的任意真实表中获取数据即可。例如： DESCRIBE t_order; 阻断路由用于屏蔽SQL对数据库的操作，例如： USE order_db; 因为ShardingSphere采用的是逻辑Schema的方式，无需将切换数据库Schema的命令发送至数据库中。 路由引擎的整体结构划分如下图。 四、改写引擎 工程师面向逻辑库与逻辑表书写的SQL，并不能够直接在真实的数据库中执行，SQL改写用于将逻辑SQL改写为在真实数据库中可以正确执行的SQL。 它包括正确性改写和优化改写两部分。 4.1、正确性改写 在包含分表的场景中，需要将分表配置中的逻辑表名称改写为路由之后所获取的真实表名称。仅分库则不需要表名称的改写。除此之外，还包括补列和分页信息修正等内容。 1、标识符改写 需要改写的标识符包括表名称、索引名称以及Schema名称。 2、补列 需要在查询语句中补列通常由两种情况导致。 第一种情况是ShardingSphere需要在结果归并时获取相应数据，但该数据并能未通过查询的SQL返回。 这种情况主要是针对GROUP BY和ORDER BY。结果归并时，需要根据GROUP BY和ORDER BY的字段项进行分组和排序，但如果原始SQL的选择项中若并未包含分组项或排序项，则需要对原始SQL进行改写。 3、分页修正 越获取偏移量位置靠后数据，使用LIMIT分页方式的效率就越低。 有很多方法可以避免使用LIMIT进行分页。比如构建行记录数量与行偏移量的二级索引，或使用上次分页数据结尾ID作为下次查询条件的分页方式等。 分页信息修正时，如果使用占位符的方式书写SQL，则只需要改写参数列表即可，无需改写SQL本身。 4、批量拆分 在使用批量插入的SQL时，如果插入的数据是跨分片的，那么需要对SQL进行改写来防止将多余的数据写入到数据库中。 插入操作与查询操作的不同之处在于，查询语句中即使用了不存在于当前分片的分片键，也不会对数据产生影响；而插入操作则必须将多余的分片键删除。 4.2、优化改写 优化改写的目的是在不影响查询正确性的情况下，对性能进行提升的有效手段。它分为单节点优化和流式归并优化。 1、单节点优化 路由至单节点的SQL，则无需优化改写。 当获得一次查询的路由结果后，如果是路由至唯一的数据节点，则无需涉及到结果归并。因此补列和分页信息等改写都没有必要进行。 尤其是分页信息的改写，无需将数据从第1条开始取，大量的降低了对数据库的压力，并且节省了网络带宽的无谓消耗。 2、流式归并优化 它仅为包含GROUP BY的SQL增加ORDER BY以及和分组项相同的排序项和排序顺序，用于将内存归并转化为流式归并。 在结果归并的部分中，将对流式归并和内存归并进行详细说明。 改写引擎的整体结构划分如下图所示。 五、执行引擎 ShardingSphere采用一套自动化的执行引擎，负责将路由和改写完成之后的真实SQL安全且高效发送到底层数据源执行。 它不是简单地将SQL通过JDBC直接发送至数据源执行；也并非直接将执行请求放入线程池去并发执行。它更关注平衡数据源连接创建以及内存占用所产生的消耗，以及最大限度地合理利用并发等问题。 执行引擎的目标是自动化的平衡资源控制与执行效率。 5.1、连接模式 如果一条SQL在经过ShardingSphere的分片后，需要操作某数据库实例下的200张表。 那么，是选择创建200个连接并行执行，还是选择创建一个连接串行执行呢？效率与资源控制又应该如何抉择呢？ 针对上述场景，ShardingSphere提供了一种解决思路。 它提出了连接模式（Connection Mode）的概念，将其划分为内存限制模式（MEMORY_STRICTLY）和连接限制模式（CONNECTION_STRICTLY）这两种类型。 5.2、内存限制模式 使用此模式的前提是，ShardingSphere对一次操作所耗费的数据库连接数量不做限制。 如果实际执行的SQL需要对某数据库实例中的200张表做操作，则对每张表创建一个新的数据库连接，并通过多线程的方式并发处理，以达成执行效率最大化。 并且在SQL满足条件情况下，优先选择流式归并，以防止出现内存溢出或避免频繁垃圾回收情况。 5.3、连接限制模式 使用此模式的前提是，ShardingSphere严格控制对一次操作所耗费的数据库连接数量。 如果实际执行的SQL需要对某数据库实例中的200张表做操作，那么只会创建唯一的数据库连接，并对其200张表串行处理。 如果一次操作中的分片散落在不同的数据库，仍然采用多线程处理对不同库的操作，但每个库的每次操作仍然只创建一个唯一的数据库连接。 这样即可以防止对一次请求对数据库连接占用过多所带来的问题。该模式始终选择内存归并。 内存限制模式适用于OLAP操作，可以通过放宽对数据库连接的限制提升系统吞吐量； 连接限制模式适用于OLTP操作，OLTP通常带有分片键，会路由到单一的分片，因此严格控制数据库连接，以保证在线系统数据库资源能够被更多的应用所使用，是明智的选择。 5.4、自动化执行引擎 ShardingSphere最初将使用何种模式的决定权交由用户配置，让开发者依据自己业务的实际场景需求选择使用内存限制模式或连接限制模式。 为了降低用户的使用成本以及连接模式动态化这两个问题，ShardingSphere提炼出自动化执行引擎的思路，在其内部消化了连接模式概念。 用户无需了解所谓的内存限制模式和连接限制模式是什么，而是交由执行引擎根据当前场景自动选择最优的执行方案。 执行引擎分为准备和执行两个阶段。 1、准备阶段 此阶段用于准备执行的数据。它分为结果集分组和执行单元创建两个步骤。 结果集分组是实现内化连接模式概念的关键。执行引擎根据maxConnectionSizePerQuery配置项，结合当前路由结果，选择恰当的连接模式。 具体步骤如下： 将SQL的路由结果按照数据源的名称进行分组。 通过下图的公式，可以获得每个数据库实例在maxConnectionSizePerQuery的允许范围内，每个连接需要执行的SQL路由结果组，并计算出本次请求的最优连接模式。 举例说明，假设一次查询需要在某一数据源上获取两个数据库连接，并路由至同一个数据库的两个分表查询。 则有可能出现查询A已获取到该数据源的1个数据库连接，并等待获取另一个数据库连接；而查询B也已经在该数据源上获取到的一个数据库连接，并同样等待另一个数据库连接的获取。 如果数据库连接池的允许最大连接数是2，那么这2个查询请求将永久的等待下去。下图描绘了死锁的情况。 2、执行阶段 该阶段用于真正的执行SQL，它分为分组执行和归并结果集生成两个步骤。 分组执行将准备执行阶段生成的执行单元分组下发至底层并发执行引擎，并针对执行过程中的每个关键步骤发送事件。 如：执行开始事件、执行成功事件以及执行失败事件。执行引擎仅关注事件的发送，它并不关心事件的订阅者。 ShardingSphere的其他模块，如：分布式事务、调用链路追踪等，会订阅感兴趣的事件，并进行相应的处理。 ShardingSphere通过在执行准备阶段的获取的连接模式，生成内存归并结果集或流式归并结果集，并将其传递至结果归并引擎，以进行下一步的工作。 执行引擎的整体结构划分如下图所示。 六、归并引擎 将从各个数据节点获取的多数据结果集，组合成为一个结果集并正确的返回至请求客户端，称为结果归并。 ShardingSphere支持的结果归并从功能上分为遍历、排序、分组、分页和聚合5种类型，它们是组合而非互斥的关系。 从结构划分，可分为流式归并、内存归并和装饰者归并。流式归并和内存归并是互斥的，装饰者归并可以在流式归并和内存归并之上做进一步的处理。 由于从数据库中返回的结果集是逐条返回的，并不需要将所有的数据一次性加载至内存中，因此，在进行结果归并时，沿用数据库返回结果集的方式进行归并，能够极大减少内存的消耗，是归并方式的优先选择。 流式归并是指每一次从结果集中获取到的数据，都能够通过逐条获取的方式返回正确的单条数据，它与数据库原生的返回结果集的方式最为契合。遍历、排序以及流式分组都属于流式归并的一种。 内存归并则是需要将结果集的所有数据都遍历并存储在内存中，再通过统一的分组、排序以及聚合等计算之后，再将其封装成为逐条访问的数据结果集返回。 装饰者归并是对所有的结果集归并进行统一的功能增强，目前装饰者归并有分页归并和聚合归并这2种类型。 6.1、遍历归并 它是最为简单的归并方式。 只需将多个数据结果集合并为一个单向链表即可。在遍历完成链表中当前数据结果集之后，将链表元素后移一位，继续遍历下一个数据结果集即可。 6.2、排序归并 由于在 SQL 中存在 ORDER BY 语句，因此每个数据结果集自身是有序的，因此只需要将数据结果集当前游标指向的数据值进行排序即可。 这相当于对多个有序的数组进行排序，归并排序是最适合此场景的排序算法。 对于每个数据结果集中的数据有序，而多数据结果集整体无序的情况下，ShardingSphere 无需将所有的数据都加载至内存即可排序。 它使用的是流式归并的方式，每次 next 仅获取唯一正确的一条数据，极大的节省了内存的消耗。 6.3、分组归并 分组归并的情况最为复杂，它分为流式分组归并和内存分组归并。 流式分组归并要求 SQL 的排序项与分组项的字段以及排序类型（ASC 或 DESC）必须保持一致，否则只能通过内存归并才能保证其数据的正确性。 6.4、聚合归并 无论是流式分组归并还是内存分组归并，对聚合函数的处理都是一致的。 除了分组的 SQL 之外，不进行分组的 SQL 也可以使用聚合函数。 因此，聚合归并是在之前介绍的归并类的之上追加的归并能力，即装饰者模式。聚合函数可以归类为比较、累加和求平均值这 3 种类型。 6.5、分页归并 上文所述的所有归并类型都可能进行分页。 分页也是追加在其他归并类型之上的装饰器，ShardingSphere 通过装饰者模式来增加对数据结果集进行分页的能力。 分页归并负责将无需获取的数据过滤掉。 需要注意的是，由于排序的需要，大量的数据仍然需要传输到 ShardingSphere 的内存空间。 因此，采用 LIMIT 这种方式分页，并非最佳实践。 由于 LIMIT 并不能通过索引查询数据，因此如果可以保证 ID 的连续性，通过 ID 进行分页是比较好的解决方案，例如： SELECT * FROM t_order WHERE id > 100000 AND id <= 100010 ORDER BY id; 或通过记录上次查询结果的最后一条记录的 ID 进行下一页的查询，例如： SELECT * FROM t_order WHERE id > 10000000 LIMIT 10; 归并引擎的整体结构划分如下图。
一、SQL 1.1、逻辑表 水平拆分的数据库（表）的相同逻辑和数据结构表的总称。例：订单数据根据主键尾数拆分为10张表，分别是t_order_0到t_order_9，他们的逻辑表名为t_order。 1.2、真实表 在分片的数据库中真实存在的物理表。即上个示例中的t_order_0到t_order_9。 1.3、数据节点 数据分片的最小单元。由数据源名称和数据表组成，例：ds_0.t_order_0。 1.4、绑定表 指分片规则一致的主表和子表。例如：t_order表和t_order_item表，均按照order_id分片，则此两张表互为绑定表关系。绑定表之间的多表关联查询不会出现笛卡尔积关联，关联查询效率将大大提升。举例说明，如果SQL为： SELECT i.* FROM t_order o JOIN t_order_item i ON o.order_id=i.order_id WHERE o.order_id in (10, 11); 在不配置绑定表关系时，假设分片键order_id将数值10路由至第0片，将数值11路由至第1片，那么路由后的SQL应该为4条，它们呈现为笛卡尔积： SELECT i.* FROM t_order_0 o JOIN t_order_item_0 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11); SELECT i.* FROM t_order_0 o JOIN t_order_item_1 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11); SELECT i.* FROM t_order_1 o JOIN t_order_item_0 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11); SELECT i.* FROM t_order_1 o JOIN t_order_item_1 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11); 配置绑定表关系后，路由的SQL应该为2条： SELECT i.* FROM t_order_0 o JOIN t_order_item_0 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11); SELECT i.* FROM t_order_1 o JOIN t_order_item_1 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11); 1.5、广播表 指所有的分片数据源中都存在的表，表结构和表中的数据在每个数据库中均完全一致。适用于数据量不大且需要与海量数据的表进行关联查询的场景，例如：字典表。 1.6、逻辑索引 某些数据库（如：PostgreSQL）不允许同一个库存在名称相同索引，某些数据库（如：MySQL）则允许只要同一个表中不存在名称相同的索引即可。 逻辑索引用于同一个库不允许出现相同索引名称的分表场景，需要将同库不同表的索引名称改写为索引名 + 表名，改写之前的索引名称成为逻辑索引。 二、分片 2.1、分片键 用于分片的数据库字段，是将数据库(表)水平拆分的关键字段。例：将订单表中的订单主键的尾数取模分片，则订单主键为分片字段。 SQL中如果无分片字段，将执行全路由，性能较差。 除了对单分片字段的支持，ShardingSphere也支持根据多个字段进行分片。 2.2、分片算法 通过分片算法将数据分片，支持通过=、BETWEEN和IN分片。分片算法需要应用方开发者自行实现，可实现的灵活度非常高。 目前提供4种分片算法。由于分片算法和业务实现紧密相关，因此并未提供内置分片算法，而是通过分片策略将各种场景提炼出来，提供更高层级的抽象，并提供接口让应用开发者自行实现分片算法。 精确分片算法 对应PreciseShardingAlgorithm，用于处理使用单一键作为分片键的=与IN进行分片的场景。需要配合StandardShardingStrategy使用。 范围分片算法 对应RangeShardingAlgorithm，用于处理使用单一键作为分片键的BETWEEN AND进行分片的场景。需要配合StandardShardingStrategy使用。 复合分片算法 对应ComplexKeysShardingAlgorithm，用于处理使用多键作为分片键进行分片的场景，包含多个分片键的逻辑较复杂，需要应用开发者自行处理其中的复杂度。需要配合ComplexShardingStrategy使用。 Hint分片算法 对应HintShardingAlgorithm，用于处理使用Hint行分片的场景。需要配合HintShardingStrategy使用。 2.3、分片策略 包含分片键和分片算法，由于分片算法的独立性，将其独立抽离。真正可用于分片操作的是分片键 + 分片算法，也就是分片策略。目前提供5种分片策略。 标准分片策略 对应StandardShardingStrategy。提供对SQL语句中的=, IN和BETWEEN AND的分片操作支持。StandardShardingStrategy只支持单分片键，提供PreciseShardingAlgorithm和RangeShardingAlgorithm两个分片算法。PreciseShardingAlgorithm是必选的，用于处理=和IN的分片。RangeShardingAlgorithm是可选的，用于处理BETWEEN AND分片，如果不配置RangeShardingAlgorithm，SQL中的BETWEEN AND将按照全库路由处理。 复合分片策略 对应ComplexShardingStrategy。复合分片策略。提供对SQL语句中的=, IN和BETWEEN AND的分片操作支持。ComplexShardingStrategy支持多分片键，由于多分片键之间的关系复杂，因此并未进行过多的封装，而是直接将分片键值组合以及分片操作符透传至分片算法，完全由应用开发者实现，提供最大的灵活度。 行表达式分片策略 对应InlineShardingStrategy。使用Groovy的表达式，提供对SQL语句中的=和IN的分片操作支持，只支持单分片键。对于简单的分片算法，可以通过简单的配置使用，从而避免繁琐的Java代码开发，如: t_user_$->{u_id % 8} 表示t_user表根据u_id模8，而分成8张表，表名称为t_user_0到t_user_7。 Hint分片策略 对应HintShardingStrategy。通过Hint而非SQL解析的方式分片的策略。 不分片策略 对应NoneShardingStrategy。不分片的策略。 2.5、SQL Hint 对于分片字段非SQL决定，而由其他外置条件决定的场景，可使用SQL Hint灵活的注入分片字段。例：内部系统，按照员工登录主键分库，而数据库中并无此字段。SQL Hint支持通过Java API和SQL注释(待实现)两种方式使用。 三、配置 3.1、分片规则 分片规则配置的总入口。包含数据源配置、表配置、绑定表配置以及读写分离配置等。 3.2、数据源配置 真实数据源列表。 3.3、表配置 逻辑表名称、数据节点与分表规则的配置。 3.4、数据节点配置 用于配置逻辑表与真实表的映射关系。可分为均匀分布和自定义分布两种形式。 均匀分布 指数据表在每个数据源内呈现均匀分布的态势，例如： db0 ├── t_order0 └── t_order1 db1 ├── t_order0 └── t_order1 那么数据节点的配置如下： db0.t_order0, db0.t_order1, db1.t_order0, db1.t_order1 自定义分布 指数据表呈现有特定规则的分布，例如： db0 ├── t_order0 └── t_order1 db1 ├── t_order2 ├── t_order3 └── t_order4 那么数据节点的配置如下： db0.t_order0, db0.t_order1, db1.t_order2, db1.t_order3, db1.t_order4 3.5、分片策略配置 对于分片策略存有数据源分片策略和表分片策略两种维度。 数据源分片策略 对应于DatabaseShardingStrategy。用于配置数据被分配的目标数据源。 表分片策略 对应于TableShardingStrategy。用于配置数据被分配的目标表，该目标表存在与该数据的目标数据源内。故表分片策略是依赖与数据源分片策略的结果的。 两种策略的API完全相同。 3.6、自增主键生成策略 通过在客户端生成自增主键替换以数据库原生自增主键的方式，做到分布式主键无重复。 3.7、Config Map 配置分库分表数据源的元数据，可通过调用ConfigMapContext.getInstance()获取ConfigMap中的shardingConfig数据。例：如果机器权重不同则流量可能不同，可通过ConfigMap配置机器权重元数据。
一、概念 ShardingSphere是一套开源的分布式数据库中间件解决方案组成的生态圈，它由Sharding-JDBC、Sharding-Proxy 和 Sharding-Sidecar这3款相互独立的产品组成。 均提供标准化的数据分片、分布式事务 和 数据库治理功能，可适用于如Java同构、异构语言、云原生等各种多样化的应用场景。 二、Sharding-JDBC 定位为轻量级Java框架，在Java的JDBC层提供的额外服务。 它使用客户端直连数据库，以jar包形式提供服务，无需额外部署和依赖，可理解为增强版的JDBC驱动，完全兼容JDBC和各种ORM框架。 适用于任何基于Java的ORM框架，如：JPA, Hibernate, Mybatis, Spring JDBC Template或直接使用JDBC。 基于任何第三方的数据库连接池，如：DBCP, C3P0, BoneCP, Druid, HikariCP等。 支持任意实现JDBC规范的数据库。目前支持MySQL，Oracle，SQLServer和PostgreSQL。 三、Sharding-Proxy 定位为透明化的数据库代理端，提供封装了数据库二进制协议的服务端版本，用于完成对异构语言的支持。 目前先提供MySQL版本，它可以使用任何兼容MySQL协议的访问客户端(如：MySQL Command Client, MySQL Workbench等)操作数据，对DBA更加友好。 向应用程序完全透明，可直接当做MySQL使用。 适用于任何兼容MySQL协议的客户端。 四、Sharding-Sidecar（TBD） 定位为Kubernetes或Mesos的云原生数据库代理，以DaemonSet的形式代理所有对数据库的访问。 通过无中心、零侵入的方案提供与数据库交互的的啮合层，即Database Mesh，又可称数据网格。 Database Mesh的关注重点在于如何将分布式的数据访问应用与数据库有机串联起来，它更加关注的是交互，是将杂乱无章的应用与数据库之间的交互有效的梳理。使用Database Mesh，访问数据库的应用和数据库终将形成一个巨大的网格体系，应用和数据库只需在网格体系中对号入座即可，它们都是被啮合层所治理的对象。 五、混合架构 Sharding-JDBC采用无中心化架构，适用于Java开发的高性能的轻量级OLTP应用；Sharding-Proxy提供静态入口以及异构语言的支持，适用于OLAP应用以及对分片数据库进行管理和运维的场景。 ShardingSphere是多接入端共同组成的生态圈。 通过混合使用Sharding-JDBC和Sharding-Proxy，并采用同一注册中心统一配置分片策略，能够灵活的搭建适用于各种场景的应用系统，架构师可以更加自由的调整适合于当前业务的最佳系统架构。 六、功能列表 6.1、数据分片 分库 & 分表 读写分离 分片策略定制化 无中心化分布式主键 6.2、分布式事务 标准化事务接口 XA强一致事务 柔性事务 6.3、数据库治理 配置动态化 编排 & 治理 数据脱敏 可视化链路追踪 弹性伸缩(规划中)
一、问题 为什么要分库分表（设计高并发系统的时候，数据库层面该如何设计）？ 用过哪些分库分表中间件？不同的分库分表中间件都有什么优点和缺点？ 具体是如何对数据库如何进行垂直拆分或水平拆分的？ 二、为什么要分库分表(设计高并发系统的时候，数据库层面该如何设计？) 每天活跃用户数上千万，每天单表新增数据多达 50 万，一个表总数据量都已经达到了两三千万。 数据库磁盘容量不断消耗掉，高峰期并发达到惊人的 5000~8000 。 三、分表 单表数据量太大，会极大影响 sql 执行的性能。单表到几百万的时候，性能就会相对差一些了，需要分表了。 分表：把一个表的数据放到多个表中，查询的时候就查一个表。 比如按照用户 id 来分表，将一个用户的数据就放在一个表中。然后操作的时候你对一个用户就操作那个表就好了。 这样可以控制每个表的数据量在可控的范围内，比如每个表就固定在 200 万以内。 四、分库 一个数据库最多支撑并发 2000，需要扩容，而且一个健康的单库并发值最好保持在每秒 1000 左右，不要太大。 可以将一个库的数据拆分到多个库中，访问的时候就访问一个库。 | # | 分库分表前 | 分库分表后 | | ------------ | ---------------------------- | -------------------------------------------- | | 并发支撑情况 | MySQL 单机部署，扛不住高并发 | MySQL 从单机到多机，能承受的并发增加了多倍 | | 磁盘使用情况 | MySQL 单机磁盘容量几乎撑满 | 拆分为多个库，数据库服务器磁盘使用率大大降低 | | SQL 执行性能 | 单表数据量太大，SQL 越跑越慢 | 单表数据量减少，SQL 执行效率明显提升 | 五、分库分表中间件 常见中间件： Cobar TDDL Atlas Sharding-jdbc Mycat 5.1、Cobar 阿里 b2b 团队开发和开源的，属于 proxy 层方案，就是介于应用服务器和数据库服务器之间。应用程序通过 JDBC 驱动访问 Cobar 集群，Cobar 根据 SQL 和分库规则对 SQL 做分解，然后分发到 MySQL 集群不同的数据库实例上执行。早些年还可以用，但是最近几年都没更新了，基本没啥人用，差不多算是被抛弃的状态吧。而且不支持读写分离、存储过程、跨库 join 和分页等操作。 5.2、TDDL 淘宝团队开发的，属于 client 层方案。支持基本的 crud 语法和读写分离，但不支持 join、多表查询等语法。目前使用的也不多，因为还依赖淘宝的 diamond 配置管理系统。 5.3、Atlas 360 开源的，属于 proxy 层方案，以前是有一些公司在用的，但是确实有一个很大的问题就是社区最新的维护都在 5 年前了。所以，现在用的公司基本也很少了。 5.4、Sharding-jdbc 当当开源的，属于 client 层方案，是 ShardingSphere 的 client 层方案， ShardingSphere 还提供 proxy 层的方案 Sharding-Proxy。确实之前用的还比较多一些，因为 SQL 语法支持也比较多，没有太多限制，而且截至 2019.4，已经推出到了 4.0.0-RC1 版本，支持分库分表、读写分离、分布式 id 生成、柔性事务（最大努力送达型事务、TCC 事务）。而且确实之前使用的公司会比较多一些（这个在官网有登记使用的公司，可以看到从 2017 年一直到现在，是有不少公司在用的），目前社区也还一直在开发和维护，还算是比较活跃，个人认为算是一个现在也可以选择的方案。 5.5、Mycat 基于 Cobar 改造的，属于 proxy 层方案，支持的功能非常完善，而且目前应该是非常火的而且不断流行的数据库中间件，社区很活跃，也有一些公司开始在用了。但是确实相比于 Sharding jdbc 来说，年轻一些，经历的锤炼少一些。 六、总结 综上，现在其实建议考量的，就是 Sharding-jdbc 和 Mycat，这两个都可以去考虑使用。 Sharding-jdbc 这种 client 层方案的优点在于不用部署，运维成本低，不需要代理层的二次转发请求，性能很高，但是如果遇到升级啥的需要各个系统都重新升级版本再发布，各个系统都需要耦合 Sharding-jdbc 的依赖； Mycat 这种 proxy 层方案的缺点在于需要部署，自己运维一套中间件，运维成本高，但是好处在于对于各个项目是透明的，如果遇到升级之类的都是自己中间件那里搞就行了。 通常来说，这两个方案其实都可以选用，但是我个人建议中小型公司选用 Sharding-jdbc，client 层方案轻便，而且维护成本低，不需要额外增派人手，而且中小型公司系统复杂度会低一些，项目也没那么多；但是中大型公司最好还是选用 Mycat 这类 proxy 层方案，因为可能大公司系统和项目非常多，团队很大，人员充足，那么最好是专门弄个人来研究和维护 Mycat，然后大量项目直接透明使用即可。 七、如何对数据库如何进行垂直拆分或水平拆分 7.1、水平拆分 就是把一个表的数据给弄到多个库的多个表里去，但是每个库的表结构都一样，只不过每个库表放的数据是不同的，所有库表的数据加起来就是全部数据。水平拆分的意义，就是将数据均匀放更多的库里，然后用多个库来扛更高的并发，还有就是用多个库的存储容量来进行扩容。 7.2、垂直拆分 就是把一个有很多字段的表给拆分成多个表，或者是多个库上去。每个库表的结构都不一样，每个库表都包含部分字段。一般来说，会将较少的访问频率很高的字段放到一个表里去，然后将较多的访问频率很低的字段放到另外一个表里去。因为数据库是有缓存的，你访问频率高的行字段越少，就可以在缓存里缓存更多的行，性能就越好。这个一般在表层面做的较多一些。 还有表层面的拆分，就是分表，将一个表变成 N 个表，就是让每个表的数据量控制在一定范围内，保证 SQL 的性能。否则单表数据量越大，SQL 性能就越差。一般是 200 万行左右，不要太多，但是也得看具体怎么操作，也可能是 500 万，或者是 100 万。你的 SQL 越复杂，就最好让单表行数越少。 好了，无论分库还是分表，上面说的那些数据库中间件都是可以支持的。就是基本上那些中间件可以做到你分库分表之后，中间件可以根据你指定的某个字段值，比如说 userid，自动路由到对应的库上去，然后再自动路由到对应的表里去。 就得考虑一下，项目里该如何分库分表？一般来说，垂直拆分，可以在表层面来做，对一些字段特别多的表做一下拆分；水平拆分，如并发承载不了，或者是数据量太大，容量承载不了，你给拆了，按什么字段来拆，你自己想好；分表，你考虑一下，你如果哪怕是拆到每个库里去，并发和容量都 ok 了，但是每个库的表还是太大了，那么你就分表，将这个表分开，保证每个表的数据量并不是很大。 八、分库分表的方式 一种是按照 range 来分，就是每个库一段连续的数据，这个一般是按比如时间范围来的，但是这种一般较少用，因为很容易产生热点问题，大量的流量都打在最新的数据上了。 或者是按照某个字段 hash 一下均匀分散，这个较为常用。 range 来分，好处在于说，扩容的时候很简单，只要预备好，给每个月都准备一个库就可以了，到了一个新的月份的时候，自然而然，就会写新的库了；缺点，但是大部分的请求，都是访问最新的数据。实际生产用 range，要看场景。 hash 分发，好处在于说，可以平均分配每个库的数据量和请求压力；坏处在于说扩容起来比较麻烦，会有一个数据迁移的过程，之前的数据需要重新计算 hash 值重新分配到不同的库或表。