{"id":1962,"date":"2023-04-01T10:26:59","date_gmt":"2023-04-01T02:26:59","guid":{"rendered":"https:\/\/www.appblog.cn\/?p=1962"},"modified":"2023-04-22T08:35:52","modified_gmt":"2023-04-22T00:35:52","slug":"sharding-sphere-sharding-proxy-sub-database-and-sub-table","status":"publish","type":"post","link":"https:\/\/www.appblog.cn\/index.php\/2023\/04\/01\/sharding-sphere-sharding-proxy-sub-database-and-sub-table\/","title":{"rendered":"Sharding-Sphere\uff1aSharding-Proxy\u5206\u5e93\u5206\u8868"},"content":{"rendered":"<h2>Sharding-Sphere\u8bf4\u660e<\/h2>\n<p>Sharding-Sphere\u63d0\u4f9b3\u6b3e\u4ea7\u54c1\uff0c\u4ee5\u4e0b\u662f\u5176\u4e2d\u4e24\u6b3e<\/p>\n<ul>\n<li>Sharding-JDBC \u5206\u5e93\u5206\u8868\u3001\u8bfb\u5199\u5206\u79bb\uff08\u53c2\u8003\u524d\u4e24\u7bc7\u6587\u7ae0\uff09<\/li>\n<li>Sharding-Proxy \u5206\u5e93\u5206\u8868\u3001\u8bfb\u5199\u5206\u79bb\uff0c\u5b83\u548cmycat\u7c7b\u4f3c\uff0c\u5c5e\u4e8e\u4e2d\u95f4\u4ef6\u4ee3\u7406\u5c42\uff0c\u5b83\u7c7b\u4f3c\u4e00\u4e2a\u6570\u636e\u5e93\uff0c\u4ee3\u7406\u540e\u9762\u7684\u5206\u5e93\u5206\u8868\u7684\u591a\u4e2a\u6570\u636e\u5e93\uff0c\u5b83\u5c4f\u853d\u4e86\u540e\u7aef\u591a\u4e2a\u6570\u636e\u5e93\u7684\u590d\u6742\u6027\uff0c\u5e94\u7528\u5f00\u53d1\u65f6\u76f4\u63a5\u8fde\u63a5 Sharding-Proxy \u5373\u53ef<\/li>\n<\/ul>\n<p><!-- more --><\/p>\n<h2>Sharding-JDBC\u4e0eSharding-Proxy\u5bf9\u6bd4<\/h2>\n<ul>\n<li>\u5982\u679c\u4f60\u7684\u5e94\u7528\u53ea\u8bfb\u5199\u5206\u79bb\u3001\u6216\u5c11\u91cf\u7684\u5206\u51fa\u4e86\u51e0\u4e2a\u5e93\uff0c\u4f7f\u7528Sharding-JDBC\u5373\u53ef\uff0c\u7b80\u5355\u3001\u65b9\u4fbf<\/li>\n<li>\u5982\u679c\u4f60\u7684\u5e94\u7528\u6709\u8fdb\u884c\u670d\u52a1\u5316\uff0c\u4e14\u6709\u5f88\u591a\u670d\u52a1\uff0c\u6bcf\u4e2a\u670d\u52a1\u8fde\u63a5\u6240\u6709\u6570\u636e\u5e93\uff0c\u4ee5MySQL\u4e3a\u4f8b\uff0c\u5206\u5e93\u5206\u8868\u540e\u670920\u4e2a\u6570\u636e\u5e93\uff0c\u6709100\u4e2a\u670d\u52a1\u9700\u8981\u8fde\u63a5\uff0c\u6bcf\u4e2a\u670d\u52a1\u7684\u8fde\u63a5\u6c60\u8bbe\u7f6e10\u4e2a\u8fde\u63a5\uff0c\u90a3\u4e48\u6bcf\u4e2aMySQL\u5b9e\u4f8b\u5c06\u8981\u63d0\u4f9b 20 <em> 100 <\/em> 10 = 20000 \u4e2a\u8fde\u63a5\uff0cMySQL \u9ed8\u8ba4\u8fde\u63a5\u6570\u662f 100\uff0c\u6700\u5927\u8fde\u63a5\u6570\u662f 16384\uff0c\u90a3\u4e48\u8fde\u63a5\u6570\u4e0d\u591f\u7528\uff0c\u5982\u679c\u4f60\u8d2d\u4e70\u7684\u4e91\u6570\u636e\u5e93\uff0c\u53ea\u80fd\u5347\u7ea7\u914d\u7f6e\uff0c\u6765\u63d0\u4f9b\u66f4\u591a\u7684\u8fde\u63a5\u6570\uff0c\u5c06\u6765\u4f60\u7684\u670d\u52a1\u66f4\u591a\u600e\u4e48\u529e\uff0c\u4f7f\u7528proxy\u4ee3\u7406\uff0c\u5e94\u7528\u670d\u52a1\u53ea\u8981\u8fde\u63a5<code>Sharding-Proxy<\/code>\u4ee3\u7406\u5c42\u6765\u89e3\u51b3\u8fde\u63a5\u6570\u95ee\u9898\uff0c\u4f46\u4ee3\u7406\u4f1a\u6709\u70b9\u6027\u80fd\u635f\u5931<\/li>\n<li><code>Sharding-Proxy<\/code>\u8fde\u63a5\u6570\u6d88\u8017\u4f4e\uff0c\u53ef\u4ee5\u652f\u6301\u5f88\u591a\u670d\u52a1\u8fdb\u884c\u8fde\u63a5\uff0c\u652f\u6301\u4efb\u610f\u5f00\u53d1\u8bed\u8a00\uff0c\u4f46\u662f\u4f1a\u6709\u4e00\u5b9a\u6027\u80fd\u635f\u8017\uff0c\u56e0\u4e3a\u591a\u4e86\u4e00\u5c42\u4ee3\u7406<\/li>\n<\/ul>\n<p><img decoding=\"async\" src=\"http:\/\/www.yezhou.me\/AppBlog\/images\/Java\/Sharding-JDBC\u4e0eSharding-Proxy\u5bf9\u6bd4.png\" alt=\"Sharding-JDBC\u4e0eSharding-Proxy\u5bf9\u6bd4\" \/><\/p>\n<h2>Sharding-Proxy\u67b6\u6784\u56fe<\/h2>\n<p><img decoding=\"async\" src=\"http:\/\/www.yezhou.me\/AppBlog\/images\/Java\/Sharding-Proxy\u67b6\u6784\u56fe.png\" alt=\"Sharding-Proxy\u67b6\u6784\u56fe\" \/><\/p>\n<h2>\u5b89\u88c5\u3001\u8fd0\u884cSharding-Proxy<\/h2>\n<p>\uff081\uff09\u4e0b\u8f7dSharding-Proxy\uff1a<a target=\"_blank\" rel=\"noopener\" href=\"https:\/\/shardingsphere.apache.org\/document\/current\/cn\/downloads\/\">https:\/\/shardingsphere.apache.org\/document\/current\/cn\/downloads\/<\/a><\/p>\n<p>\uff082\uff09\u6309\u4f7f\u7528\u624b\u518c\uff0c\u914d\u7f6e\u3001\u542f\u52a8Sharding-Proxy\uff1a<a target=\"_blank\" rel=\"noopener\" href=\"https:\/\/shardingsphere.apache.org\/document\/current\/cn\/manual\/sharding-proxy\/usage\/\">https:\/\/shardingsphere.apache.org\/document\/current\/cn\/manual\/sharding-proxy\/usage\/<\/a><\/p>\n<p>\uff083\uff09\u4e0a\u4f20\u670d\u52a1\u5668\uff0c\u89e3\u538b\uff0c\u8fdb\u5165conf\u76ee\u5f55\uff0c\u67092\u4e2a\u91cd\u8981\u7684\u914d\u7f6e\u6587\u4ef6\uff1a<code>server.yaml<\/code>\u548c<code>config-sharding.yaml<\/code><\/p>\n<p>\uff084\uff09\u914d\u7f6e<code>server.yaml<\/code>\uff0c\u628a\u4e0b\u9762\u7684\u914d\u7f6e\u7684<code>#<\/code>\u6ce8\u91ca\u6253\u5f00\uff0c\u6539\u4e3a\u7b26\u5408\u81ea\u5df1\u7684\u914d\u7f6e<\/p>\n<pre><code class=\"language-yml\"># \u5e94\u7528\u8fde\u63a5 Sharding-Proxy \u65f6\u9700\u8981\u7684\u8d26\u6237\u548c\u5bc6\u7801\nauthentication:\n  username: root\n  password: root\n\nprops:\n  max.connections.size.per.query: 1\n  acceptor.size: 4  # CPU\u6838\u5fc3\u6570 * 2\n  executor.size: 2  # CPU\u6838\u5fc3\u6570\n  proxy.frontend.flush.threshold: 128  # The default value is 128.\n    # LOCAL: Proxy will run with LOCAL transaction.\n    # XA: Proxy will run with XA transaction.\n    # BASE: Proxy will run with B.A.S.E transaction.\n  proxy.transaction.type: LOCAL\n  proxy.opentracing.enabled: false\n  sql.show: false<\/code><\/pre>\n<p>\uff085\uff09\u914d\u7f6e<code>config-sharding.yaml<\/code>\uff0c\u628a\u6700\u4e0b\u9762MySQL\u7684\u6ce8\u91ca\u6539\u4e3a\u7b26\u5408\u81ea\u5df1\u7684\u914d\u7f6e<\/p>\n<pre><code class=\"language-yml\"># \u5e94\u7528\u8fde\u63a5 Sharding-Proxy \u7684\u6570\u636e\u5e93\u540d\u79f0\nschemaName: sharding_db\n\ndataSources:\n  ds0:\n    url: jdbc:mysql:\/\/localhost:3306\/ds0?useUnicode=true&amp;characterEncoding=UTF-8&amp;useSSL=false\n    username: root\n    password: 123456\n    connectionTimeoutMilliseconds: 30000\n    idleTimeoutMilliseconds: 60000\n    maxLifetimeMilliseconds: 1800000\n    maxPoolSize: 30\n  ds1:\n    url: jdbc:mysql:\/\/localhost:3306\/ds1?useUnicode=true&amp;characterEncoding=UTF-8&amp;useSSL=false\n    username: root\n    password: 123456\n    connectionTimeoutMilliseconds: 30000\n    idleTimeoutMilliseconds: 60000\n    maxLifetimeMilliseconds: 1800000\n    maxPoolSize: 30\n\nshardingRule:\n  tables:\n    t_order:\n      actualDataNodes: ds${0..1}.t_order${0..1}\n      tableStrategy:\n        inline:\n          shardingColumn: order_id\n          algorithmExpression: t_order${order_id % 2}\n#      keyGenerator:\n#        type: SNOWFLAKE\n#        column: order_id\n    t_order_item:\n      actualDataNodes: ds${0..1}.t_order_item${0..1}\n      tableStrategy:\n        inline:\n          shardingColumn: order_id\n          algorithmExpression: t_order_item${order_id % 2}\n#      keyGenerator:\n#        type: SNOWFLAKE\n#        column: order_item_id\n  bindingTables:\n    - t_order,t_order_item\n  defaultDatabaseStrategy:\n    inline:\n      shardingColumn: member_id\n      algorithmExpression: ds${member_id % 2}\n  defaultTableStrategy:\n    none:<\/code><\/pre>\n<p>\uff086\uff09\u542f\u52a8 Sharding-Proxy \uff0c\u5982\u679c\u662fwindows\u7cfb\u7edf\uff0c\u53cc\u51fb<code>start.bat<\/code>\uff0c\u542f\u52a8\u540esharding-proxy\u76ee\u5f55\u4f1a\u6709\u65e5\u5fd7\u6587\u4ef6<code>logs\/stdout.log<\/code><\/p>\n<pre><code class=\"language-bash\">cd \/usr\/local\/sharding-proxy\/bin\nsh start.sh\n\n#\u9ed8\u8ba4\u7aef\u53e3 3307 \uff0c\u53ef\u81ea\u5b9a\u4e49\u7aef\u53e3\u4f7f\u7528\nsh start.sh 3308\n\n#\u67e5\u770b\u65e5\u5fd7\u89c2\u5bdf\u542f\u52a8\u662f\u5426\u6210\u529f\ntail -f \/usr\/local\/sharding-proxy\/logs\/stdout.log<\/code><\/pre>\n<p>\uff087\uff09\u4f7f\u7528 Navicat Mysql \u5ba2\u6237\u7aef\u8fde\u63a5 Sharding-Proxy\uff0c\u7136\u540e\u521b\u5efa\u8868\uff0c\u8be5\u4ee3\u7406\u5c42\u4f1a\u6839\u636e\u5206\u5e93\u5206\u8868\u89c4\u5219\u81ea\u52a8\u5728\u540e\u7aef\u5bf9\u5e94\u7684\u5206\u5e93\u4e2d\u521b\u5efa\u8868<\/p>\n<h2>SpringBoot\u63a5\u5165 Sharing-Proxy<\/h2>\n<p>\uff081\uff09\u65b0\u5efa\u9879\u76ee\uff0c\u5f15\u5165\u4f9d\u8d56\uff0c\u4ee5<code>spring-boot-starter-data-jpa<\/code>\u64cd\u4f5c<code>sharding-proxy<\/code>\u4e3a\u4f8b\uff1a<\/p>\n<pre><code class=\"language-xml\">&lt;dependency&gt;\n    &lt;groupId&gt;org.springframework.boot&lt;\/groupId&gt;\n    &lt;artifactId&gt;spring-boot-starter-web&lt;\/artifactId&gt;\n&lt;\/dependency&gt;\n&lt;dependency&gt;\n    &lt;groupId&gt;org.springframework.boot&lt;\/groupId&gt;\n    &lt;artifactId&gt;spring-boot-starter-data-jpa&lt;\/artifactId&gt;\n&lt;\/dependency&gt;\n&lt;dependency&gt;\n    &lt;groupId&gt;mysql&lt;\/groupId&gt;\n    &lt;artifactId&gt;mysql-connector-java&lt;\/artifactId&gt;\n    &lt;version&gt;5.1.47&lt;\/version&gt;\n&lt;\/dependency&gt;\n&lt;dependency&gt;\n    &lt;groupId&gt;com.zaxxer&lt;\/groupId&gt;\n    &lt;artifactId&gt;HikariCP&lt;\/artifactId&gt;\n    &lt;version&gt;3.3.0&lt;\/version&gt;\n&lt;\/dependency&gt;\n&lt;dependency&gt;\n    &lt;groupId&gt;org.projectlombok&lt;\/groupId&gt;\n    &lt;artifactId&gt;lombok&lt;\/artifactId&gt;\n    &lt;optional&gt;true&lt;\/optional&gt;\n&lt;\/dependency&gt;<\/code><\/pre>\n<p>\uff082\uff09\u4fee\u6539<code>application.properties<\/code>\uff0c\u6570\u636e\u5e93\u8fde\u63a5\u5730\u5740\u4e3a<code>sharding-proxy<\/code>\u4ee3\u7406\u5730\u5740<\/p>\n<pre><code>server.port=8073\nspring.jpa.hibernate.ddl-auto=none\nspring.jpa.database=mysql\nspring.jpa.show-sql=true\nspring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5Dialect\n\n# sharding-proxy \u4e0a\u914d\u7f6e\u7684\u5730\u5740\u3001\u5e10\u53f7\u3001\u5bc6\u7801\nspring.datasource.driver-class-name=com.mysql.jdbc.Driver\nspring.datasource.url=jdbc:mysql:\/\/localhost:3307\/sharding_db?useUnicode=true&amp;characterEncoding=UTF-8&amp;useSSL=false\nspring.datasource.username=root\nspring.datasource.password=root\n\n# Hikari will use the above plus the following to setup connection pooling\nspring.datasource.type=com.zaxxer.hikari.HikariDataSource\nspring.datasource.hikari.minimum-idle=10\nspring.datasource.hikari.maximum-pool-size=25\nspring.datasource.hikari.auto-commit=true\nspring.datasource.hikari.idle-timeout=30000\nspring.datasource.hikari.pool-name=HikariCP\nspring.datasource.hikari.max-lifetime=1800000\nspring.datasource.hikari.connection-timeout=30000\nspring.datasource.hikari.connection-test-query=SELECT 1<\/code><\/pre>\n<p>\uff083\uff09JPA\u5b9e\u73b0\u589e\u5220\u6539\u67e5<\/p>\n<pre><code class=\"language-java\">@Data\n@Entity\n@Table(name = &quot;t_order&quot;)\npublic class Order {\n\n    @Id\n    @Column(name = &quot;order_id&quot;)\n    private Long orderId;\n\n    @Column(name = &quot;member_id&quot;)\n    private Long memberId;\n\n    @Column(name = &quot;order_code&quot;)\n    private String orderCode;\n\n    @Column(name = &quot;order_amount&quot;)\n    private String orderAmount;\n\n    @Column(name = &quot;status&quot;)\n    private String status;\n\n    @Column(name = &quot;create_time&quot;)\n    private Date createTime;\n}\n\n@Repository\npublic inte\nrface OrderRepository extends JpaRepository&lt;Order , Long&gt; {\n\n}<\/code><\/pre>\n<p>\uff084\uff09OrderController\u63d0\u4f9b\u6570\u636e\u64cd\u4f5c\u63a5\u53e3<\/p>\n<pre><code class=\"language-java\">@RestController\n@RequestMapping(&quot;order&quot;)\npublic class OrderController {\n\n    @Autowired private OrderRepository orderRepository;\n\n    @RequestMapping(&quot;add&quot;)\n    public Order add(){\n        Order order = new Order();\n        order.setOrderId(IdWorker.getLongId());\n        order.setMemberId(IdWorker.getLongId());\n        order.setCreateTime(new Date());\n        order.setOrderAmount(&quot;330.2&quot;);\n        order.setOrderCode(&quot;abc&quot;);\n        order.setStatus(&quot;1&quot;);\n        return orderRepository.save(order);\n    }\n\n    @GetMapping(&quot;findById\/{orderId}&quot;)\n    public Order findById(@PathVariable long orderId){\n        return orderRepository.findById(orderId).get();\n    }\n\n    @GetMapping(&quot;findAll&quot;)\n    public List&lt;Order&gt; findAll(){\n        return orderRepository.findAll();\n    }\n}<\/code><\/pre>\n<p>\uff085\uff09\u542f\u52a8\u9879\u76ee\uff0c\u9a8c\u8bc1\u8fd9\u4e9b\u63a5\u53e3\uff0c\u4e0e\u5bf9\u5e94\u7684\u6570\u636e\u662f\u5426\u6309\u5206\u5e93\u5206\u8868\u89c4\u5219\u63d2\u5165<\/p>\n<p>\u65b0\u589eorder\uff0c<a target=\"_blank\" rel=\"noopener\" href=\"http:\/\/localhost:8073\/order\/add\">http:\/\/localhost:8073\/order\/add<\/a> \uff0c\u591a\u65b0\u589e\u51e0\u6761\u6570\u636e\uff0c\u5728sharding-proxy\u4ee3\u7406\u5c42\u67e5\u8be2order\u8868\uff0c\u53ef\u4ee5\u770b\u5230\u65b0\u589e\u7ed3\u679c<\/p>\n<p>\u67e5\u8be2\u63a5\u53e3\uff1a<a target=\"_blank\" rel=\"noopener\" href=\"http:\/\/localhost:8073\/order\/findById\/xxxxxx\">http:\/\/localhost:8073\/order\/findById\/xxxxxx<\/a><br \/>\n\u67e5\u8be2\u6240\u6709\uff1a<a target=\"_blank\" rel=\"noopener\" href=\"http:\/\/localhost:8073\/order\/findAll\">http:\/\/localhost:8073\/order\/findAll<\/a><\/p>\n<p>\uff086\uff09\u5355\u72ec\u8fde\u63a5\u540e\u7aef\u7684<code>ds0<\/code>\u3001<code>ds1<\/code>\u6570\u636e\u5e93\uff0c\u67e5\u770b\u521a\u521a\u65b0\u589e\u7684\u6570\u636e\u662f\u5426\u5206\u5230\u8fd9\u4e24\u4e2a\u5e93\u4e2d\uff0c\u53ef\u4ee5\u770b\u5230\u786e\u5b9e\u5b9e\u73b0\u5206\u5e93\u5206\u8868\uff0c\u4f7f\u7528 sharding-proxy \u4ee3\u7406\u8fdb\u884c\u5206\u5e93\u5206\u8868\u9a8c\u8bc1\u6210\u529f<\/p>\n<p>\u53c2\u8003\uff1a<a target=\"_blank\" rel=\"noopener\" href=\"https:\/\/gitee.com\/zhuyu1991\/spring-cloud\/tree\/master\/sharding-business\/sharding-proxy\">https:\/\/gitee.com\/zhuyu1991\/spring-cloud\/tree\/master\/sharding-business\/sharding-proxy<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Sharding-Sphere\u8bf4\u660e Sharding-Sphere\u63d0\u4f9b3\u6b3e\u4ea7\u54c1\uff0c\u4ee5\u4e0b\u662f\u5176\u4e2d\u4e24\u6b3e Shardin [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[491],"tags":[493,492,299],"class_list":["post-1962","post","type-post","status-publish","format-standard","hentry","category-sharding-sphere","tag-sharding-proxy","tag-sharding-sphere","tag-299"],"_links":{"self":[{"href":"https:\/\/www.appblog.cn\/index.php\/wp-json\/wp\/v2\/posts\/1962","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.appblog.cn\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.appblog.cn\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.appblog.cn\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.appblog.cn\/index.php\/wp-json\/wp\/v2\/comments?post=1962"}],"version-history":[{"count":0,"href":"https:\/\/www.appblog.cn\/index.php\/wp-json\/wp\/v2\/posts\/1962\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.appblog.cn\/index.php\/wp-json\/wp\/v2\/media?parent=1962"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.appblog.cn\/index.php\/wp-json\/wp\/v2\/categories?post=1962"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.appblog.cn\/index.php\/wp-json\/wp\/v2\/tags?post=1962"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}