如何使用 DistSQL 构建动态分布式数据库

了解数据分片场景，其中 DistSQL 的灵活性允许您创建分布式数据库。
3 位读者喜欢这篇文章。
diagram of planning a cloud
图片来源

Opensource.com

分布式数据库因多种原因而很常见。它们提高了可靠性、冗余性和性能。Apache ShardingSphere 是一个开源框架，使您能够将任何数据库转换为分布式数据库。自 ShardingSphere 5.0.0 版本发布以来，DistSQL（分布式 SQL）为 ShardingSphere 生态系统提供了动态管理。

在本文中，我将演示一个数据分片场景，其中 DistSQL 的灵活性允许您创建分布式数据库。同时，我将展示一些语法糖来简化操作流程，允许您的潜在用户选择他们喜欢的语法。

一系列 DistSQL 语句通过实际案例运行，为您提供一套完整的实用 DistSQL 分片管理方法，这些方法通过动态管理创建和维护分布式数据库。

Diagram of database sharding management options
图片来源

（Jiang Longtao，CC BY-SA 4.0）

什么是分片？

在数据库术语中，分片是将表划分为独立实体的过程。虽然表数据直接相关，但它通常存在于不同的物理数据库节点上，或者至少存在于独立的逻辑分区中。

实际案例示例

要跟随此示例进行操作，您必须具备以下组件，无论是在您的实验室中还是在您阅读本文时在您的脑海中

  • 两个分片表：t_order 和 t_order_item。
  • 对于这两个表，数据库分片使用 user_id 字段执行，表分片使用 order_id 字段执行。
  • 分片数为两个数据库乘以三个表。
Apache ShardingSphere databases
图片来源

（Jiang Longtao，CC BY-SA 4.0）

设置环境

1. 准备一个数据库（MySQL、MariaDB、PostgreSQL 或 openGauss）实例以进行访问。创建两个新数据库：demo_ds_0 和 demo_ds_1。

2. 部署 Apache ShardingSphere-Proxy 5.1.2 和 Apache ZooKeeper。ZooKeeper 充当治理中心并存储 ShardingSphere 元数据信息。

3. 按照以下方式配置 Proxy conf 目录中的 server.yaml

mode:
  type: Cluster
  repository:
    type: ZooKeeper
    props:
      namespace: governance_ds
      server-lists: localhost:2181 #ZooKeeper address
      retryIntervalMilliseconds: 500
      timeToLiveSeconds: 60
      maxRetries: 3
      operationTimeoutMilliseconds: 500
  overwrite: falserules:
  - !AUTHORITY
    users:
      - root@%:root

4. 启动 ShardingSphere-Proxy 并使用客户端将其连接到 Proxy，例如

$ mysql -h 127.0.0.1 -P 3307 -u root -p

5. 创建分布式数据库

CREATE DATABASE sharding_db;USE sharding_db;

添加存储资源

接下来，添加与数据库对应的存储资源

ADD RESOURCE ds_0 (
    HOST=127.0.0.1,
    PORT=3306,
    DB=demo_ds_0,
    USER=root,
    PASSWORD=123456
), ds_1(
    HOST=127.0.0.1,
    PORT=3306,
    DB=demo_ds_1,
    USER=root,
    PASSWORD=123456
);

查看存储资源

mysql> SHOW DATABASE RESOURCES\G;
******** 1. row ***************************
         name: ds_1
         type: MySQL
         host: 127.0.0.1
         port: 3306
           db: demo_ds_1
          -- Omit partial attributes
******** 2. row ***************************
         name: ds_0
         type: MySQL
         host: 127.0.0.1
         port: 3306
           db: demo_ds_0
          -- Omit partial attributes

在查询语句中添加可选的 \G 开关使输出格式易于阅读。

创建分片规则

ShardingSphere 的分片规则支持常规分片和自动分片。两种分片方法具有相同的效果。不同之处在于自动分片的配置更简洁，而常规分片更灵活和独立。

有关自动分片的更多详细信息，请参阅以下链接

接下来，是时候采用常规分片并使用 INLINE 表达式算法来实现需求中描述的分片场景了。

主键生成器

主键生成器在分布式场景中为数据表创建安全且唯一的主键。有关详细信息，请参阅文档 分布式主键。

1. 创建主键生成器

CREATE SHARDING KEY GENERATOR snowflake_key_generator (
TYPE(NAME=SNOWFLAKE)
);

2. 查询主键生成器

mysql> SHOW SHARDING KEY GENERATORS;
+-------------------------+-----------+-------+
| name                    | type      | props |
+-------------------------+-----------+-------+
| snowflake_key_generator | snowflake | {}    |
+-------------------------+-----------+-------+
1 row in set (0.01 sec)

分片算法

1. 创建 t_order 和 t_order_item 共用的数据库分片算法

-- Modulo 2 based on user_id in database sharding
CREATE SHARDING ALGORITHM database_inline (
TYPE(NAME=INLINE,PROPERTIES("algorithm-expression"="ds_${user_id % 2}"))
);

2. 为 t_order 和 t_order_item 创建不同的表分片算法：

-- Modulo 3 based on order_id in table sharding
CREATE SHARDING ALGORITHM t_order_inline (
TYPE(NAME=INLINE,PROPERTIES("algorithm-expression"="t_order_${order_id % 3}"))
);
CREATE SHARDING ALGORITHM t_order_item_inline (
TYPE(NAME=INLINE,PROPERTIES("algorithm-expression"="t_order_item_${order_id % 3}"))
);

3. 查询分片算法

mysql> SHOW SHARDING ALGORITHMS;
+---------------------+--------+---------------------------------------------------+
| name                | type   | props                                             |
+---------------------+--------+---------------------------------------------------+
| database_inline     | inline | algorithm-expression=ds_${user_id % 2}            |
| t_order_inline      | inline | algorithm-expression=t_order_${order_id % 3}      |
| t_order_item_inline | inline | algorithm-expression=t_order_item_${order_id % 3} |
+---------------------+--------+---------------------------------------------------+
3 rows in set (0.00 sec)

创建默认分片策略

分片策略 由分片键和分片算法组成，在本例中为 databaseStrategy 和 tableStrategy。由于 t_order 和 t_order_item 具有相同的数据库分片字段和分片算法，因此创建默认策略以供所有未配置分片策略的分片表使用。

1. 创建默认数据库分片策略

CREATE DEFAULT SHARDING DATABASE STRATEGY (
TYPE=STANDARD,SHARDING_COLUMN=user_id,SHARDING_ALGORITHM=database_inline
);

2. 查询默认策略

mysql> SHOW DEFAULT SHARDING STRATEGY\G;
*************************** 1. row ***************************
                    name: TABLE
                    type: NONE
         sharding_column:
 sharding_algorithm_name:
 sharding_algorithm_type:
sharding_algorithm_props:
*************************** 2. row ***************************
                    name: DATABASE
                    type: STANDARD
         sharding_column: user_id
 sharding_algorithm_name: database_inline
 sharding_algorithm_type: inline
sharding_algorithm_props: {algorithm-expression=ds_${user_id % 2}}
2 rows in set (0.00 sec)

您尚未配置默认表分片策略，因此 TABLE 的默认策略为 NONE。

设置分片规则

主键生成器和分片算法都已准备就绪。现在您可以创建分片规则了。我下面演示的方法有点复杂，涉及多个步骤。在下一节中，我将向您展示如何一步创建分片规则，但现在，请见证通常是如何完成的。

首先，定义 t_order

CREATE SHARDING TABLE RULE t_order (
DATANODES("ds_${0..1}.t_order_${0..2}"),
TABLE_STRATEGY(TYPE=STANDARD,SHARDING_COLUMN=order_id,SHARDING_ALGORITHM=t_order_inline),
KEY_GENERATE_STRATEGY(COLUMN=order_id,KEY_GENERATOR=snowflake_key_generator)
);

以下是对上述值的解释

  • DATANODES 指定分片表的数据节点。
  • TABLE_STRATEGY 指定表策略，其中 SHARDING_ALGORITHM 使用创建的分片算法 t_order_inline。
  • KEY_GENERATE_STRATEGY 指定表的主键生成策略。如果不需要主键生成，请跳过此配置。

接下来，定义 t_order_item

CREATE SHARDING TABLE RULE t_order_item (
DATANODES("ds_${0..1}.t_order_item_${0..2}"),
TABLE_STRATEGY(TYPE=STANDARD,SHARDING_COLUMN=order_id,SHARDING_ALGORITHM=t_order_item_inline),
KEY_GENERATE_STRATEGY(COLUMN=order_item_id,KEY_GENERATOR=snowflake_key_generator)
);

查询分片规则以验证您创建的内容

mysql> SHOW SHARDING TABLE RULES\G;
************************** 1. row ***************************
                           table: t_order
               actual_data_nodes: ds_${0..1}.t_order_${0..2}
             actual_data_sources:
          database_strategy_type: STANDARD
        database_sharding_column: user_id
database_sharding_algorithm_type: inline
database_sharding_algorithm_props: algorithm-expression=ds_${user_id % 2}
              table_strategy_type: STANDARD
            table_sharding_column: order_id
    table_sharding_algorithm_type: inline
   table_sharding_algorithm_props: algorithm-expression=t_order_${order_id % 3}
              key_generate_column: order_id
               key_generator_type: snowflake
              key_generator_props:
*************************** 2. row ***************************
                            table: t_order_item
                actual_data_nodes: ds_${0..1}.t_order_item_${0..2}
              actual_data_sources:
           database_strategy_type: STANDARD
         database_sharding_column: user_id
 database_sharding_algorithm_type: inline
database_sharding_algorithm_props: algorithm-expression=ds_${user_id % 2}
              table_strategy_type: STANDARD
            table_sharding_column: order_id
    table_sharding_algorithm_type: inline
   table_sharding_algorithm_props: algorithm-expression=t_order_item_${order_id % 3}
              key_generate_column: order_item_id
               key_generator_type: snowflake
              key_generator_props:
2 rows in set (0.00 sec)

到目前为止，看起来不错。您现在已经为 t_order 和 t_order_item 配置了分片规则。

您可以跳过创建主键生成器、分片算法和默认策略的步骤，一步完成分片规则。以下是如何使其更轻松的方法。

分片规则语法

例如，如果您想添加一个名为 t_order_detail 的分片表，您可以按如下方式创建分片规则

CREATE SHARDING TABLE RULE t_order_detail (
DATANODES("ds_${0..1}.t_order_detail_${0..1}"),
DATABASE_STRATEGY(TYPE=STANDARD,SHARDING_COLUMN=user_id,SHARDING_ALGORITHM(TYPE(NAME=INLINE,PROPERTIES("algorithm-expression"="ds_${user_id % 2}")))),
TABLE_STRATEGY(TYPE=STANDARD,SHARDING_COLUMN=order_id,SHARDING_ALGORITHM(TYPE(NAME=INLINE,PROPERTIES("algorithm-expression"="t_order_detail_${order_id % 3}")))),
KEY_GENERATE_STRATEGY(COLUMN=detail_id,TYPE(NAME=snowflake))
);

此语句指定了数据库分片策略、表策略和主键生成策略，但它不使用现有算法。DistSQL 引擎自动使用输入表达式为 t_order_detail 的分片规则创建算法。

现在有一个主键生成器

mysql> SHOW SHARDING KEY GENERATORS;
+--------------------------+-----------+-------+
| name                     | type      | props |
+--------------------------+-----------+-------+
| snowflake_key_generator  | snowflake | {}    |
| t_order_detail_snowflake | snowflake | {}    |
+--------------------------+-----------+-------+
2 rows in set (0.00 sec)

显示分片算法

mysql> SHOW SHARDING ALGORITHMS;
+--------------------------------+--------+-----------------------------------------------------+
| name                           | type   | props                                               |
+--------------------------------+--------+-----------------------------------------------------+
| database_inline                | inline | algorithm-expression=ds_${user_id % 2}              |
| t_order_inline                 | inline | algorithm-expression=t_order_${order_id % 3}        |
| t_order_item_inline            | inline | algorithm-expression=t_order_item_${order_id % 3}   |
| t_order_detail_database_inline | inline | algorithm-expression=ds_${user_id % 2}              |
| t_order_detail_table_inline    | inline | algorithm-expression=t_order_detail_${order_id % 3} |
+--------------------------------+--------+-----------------------------------------------------+
5 rows in set (0.00 sec)

最后，分片规则

mysql> SHOW SHARDING TABLE RULES\G;
*************************** 1. row ***************************
                            table: t_order
                actual_data_nodes: ds_${0..1}.t_order_${0..2}
              actual_data_sources:
           database_strategy_type: STANDARD
         database_sharding_column: user_id
 database_sharding_algorithm_type: inline
database_sharding_algorithm_props: algorithm-expression=ds_${user_id % 2}
              table_strategy_type: STANDARD
            table_sharding_column: order_id
    table_sharding_algorithm_type: inline
   table_sharding_algorithm_props: algorithm-expression=t_order_${order_id % 3}
              key_generate_column: order_id
               key_generator_type: snowflake
              key_generator_props:
*************************** 2. row ***************************
                            table: t_order_item
                actual_data_nodes: ds_${0..1}.t_order_item_${0..2}
              actual_data_sources:
           database_strategy_type: STANDARD
         database_sharding_column: user_id
 database_sharding_algorithm_type: inline
database_sharding_algorithm_props: algorithm-expression=ds_${user_id % 2}
              table_strategy_type: STANDARD
            table_sharding_column: order_id
    table_sharding_algorithm_type: inline
   table_sharding_algorithm_props: algorithm-expression=t_order_item_${order_id % 3}
              key_generate_column: order_item_id
               key_generator_type: snowflake
              key_generator_props:
*************************** 3. row ***************************
                            table: t_order_detail
                actual_data_nodes: ds_${0..1}.t_order_detail_${0..1}
              actual_data_sources:
           database_strategy_type: STANDARD
         database_sharding_column: user_id
 database_sharding_algorithm_type: inline
database_sharding_algorithm_props: algorithm-expression=ds_${user_id % 2}
              table_strategy_type: STANDARD
            table_sharding_column: order_id
    table_sharding_algorithm_type: inline
   table_sharding_algorithm_props: algorithm-expression=t_order_detail_${order_id % 3}
              key_generate_column: detail_id
               key_generator_type: snowflake
              key_generator_props:
3 rows in set (0.01 sec)

在 CREATE SHARDING TABLE RULE 语句中，DATABASE_STRATEGY、TABLE_STRATEGY 和 KEY_GENERATE_STRATEGY 可以重用现有算法。

或者，可以通过语法快速定义它们。不同之处在于创建了额外的算法对象。

配置和验证

创建配置验证规则后，您可以通过以下方式验证它们。

1. 检查节点分布

DistSQL 提供 SHOW SHARDING TABLE NODES 用于检查节点分布，用户可以快速了解分片表的分布

mysql> SHOW SHARDING TABLE NODES;
+----------------+------------------------------------------------------------------------------------------------------------------------------+
| name           | nodes                                                                                                                        |
+----------------+------------------------------------------------------------------------------------------------------------------------------+
| t_order        | ds_0.t_order_0, ds_0.t_order_1, ds_0.t_order_2, ds_1.t_order_0, ds_1.t_order_1, ds_1.t_order_2                               |
| t_order_item   | ds_0.t_order_item_0, ds_0.t_order_item_1, ds_0.t_order_item_2, ds_1.t_order_item_0, ds_1.t_order_item_1, ds_1.t_order_item_2 |
| t_order_detail | ds_0.t_order_detail_0, ds_0.t_order_detail_1, ds_1.t_order_detail_0, ds_1.t_order_detail_1                                   |
+----------------+------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.01 sec)

mysql> SHOW SHARDING TABLE NODES t_order_item;
+--------------+------------------------------------------------------------------------------------------------------------------------------+
| name         | nodes                                                                                                                        |
+--------------+------------------------------------------------------------------------------------------------------------------------------+
| t_order_item | ds_0.t_order_item_0, ds_0.t_order_item_1, ds_0.t_order_item_2, ds_1.t_order_item_0, ds_1.t_order_item_1, ds_1.t_order_item_2 |
+--------------+------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

您可以看到分片表的节点分布与需求中描述的一致。

SQL 预览

预览 SQL 也是验证配置的一种简单方法。其语法为 PREVIEW SQL。首先，进行一个没有分片键的查询，使用所有路由

mysql> PREVIEW SELECT * FROM t_order;
+------------------+---------------------------------------------------------------------------------------------+
| data_source_name | actual_sql                                                                                  |
+------------------+---------------------------------------------------------------------------------------------+
| ds_0             | SELECT * FROM t_order_0 UNION ALL SELECT * FROM t_order_1 UNION ALL SELECT * FROM t_order_2 |
| ds_1             | SELECT * FROM t_order_0 UNION ALL SELECT * FROM t_order_1 UNION ALL SELECT * FROM t_order_2 |
+------------------+---------------------------------------------------------------------------------------------+
2 rows in set (0.13 sec)

mysql> PREVIEW SELECT * FROM t_order_item;
+------------------+------------------------------------------------------------------------------------------------------------+
| data_source_name | actual_sql                                                                                                 |
+------------------+------------------------------------------------------------------------------------------------------------+
| ds_0             | SELECT * FROM t_order_item_0 UNION ALL SELECT * FROM t_order_item_1 UNION ALL SELECT * FROM t_order_item_2 |
| ds_1             | SELECT * FROM t_order_item_0 UNION ALL SELECT * FROM t_order_item_1 UNION ALL SELECT * FROM t_order_item_2 |
+------------------+------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

现在在查询中指定 user_id，使用单个数据库路由

mysql> PREVIEW SELECT * FROM t_order WHERE user_id = 1;
+------------------+---------------------------------------------------------------------------------------------------------------------------------------------------+
| data_source_name | actual_sql                                                                                                                                        |
+------------------+---------------------------------------------------------------------------------------------------------------------------------------------------+
| ds_1             | SELECT * FROM t_order_0 WHERE user_id = 1 UNION ALL SELECT * FROM t_order_1 WHERE user_id = 1 UNION ALL SELECT * FROM t_order_2 WHERE user_id = 1 |
+------------------+---------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.14 sec)

mysql> PREVIEW SELECT * FROM t_order_item WHERE user_id = 2;
+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| data_source_name | actual_sql                                                                                                                                                       |
+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ds_0             | SELECT * FROM t_order_item_0 WHERE user_id = 2 UNION ALL SELECT * FROM t_order_item_1 WHERE user_id = 2 UNION ALL SELECT * FROM t_order_item_2 WHERE user_id = 2 |
+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

指定 user_id 和 order_id，使用单个表路由

mysql> PREVIEW SELECT * FROM t_order WHERE user_id = 1 AND order_id = 1;
+------------------+------------------------------------------------------------+
| data_source_name | actual_sql                                                 |
+------------------+------------------------------------------------------------+
| ds_1             | SELECT * FROM t_order_1 WHERE user_id = 1 AND order_id = 1 |
+------------------+------------------------------------------------------------+
1 row in set (0.04 sec)

mysql> PREVIEW SELECT * FROM t_order_item WHERE user_id = 2 AND order_id = 5;
+------------------+-----------------------------------------------------------------+
| data_source_name | actual_sql                                                      |
+------------------+-----------------------------------------------------------------+
| ds_0             | SELECT * FROM t_order_item_2 WHERE user_id = 2 AND order_id = 5 |
+------------------+-----------------------------------------------------------------+
1 row in set (0.01 sec)

单表路由扫描的分片表最少，效率最高。

查询未使用的资源

在系统维护期间，可能需要释放不再使用的算法或存储资源，或者可能已引用需要释放的资源而无法删除。DistSQL 的 SHOW UNUSED RESOURCES 命令可以解决这些问题

mysql> ADD RESOURCE ds_2 (
    ->     HOST=127.0.0.1,
    ->     PORT=3306,
    ->     DB=demo_ds_2,
    ->     USER=root,
    ->     PASSWORD=123456
    -> );
Query OK, 0 rows affected (0.07 sec)

mysql> SHOW UNUSED RESOURCES\G;
*************************** 1. row ***************************
                           name: ds_2
                           type: MySQL
                           host: 127.0.0.1
                           port: 3306
                             db: demo_ds_2
connection_timeout_milliseconds: 30000
      idle_timeout_milliseconds: 60000
      max_lifetime_milliseconds: 2100000
                  max_pool_size: 50
                  min_pool_size: 1
                      read_only: false
               other_attributes: {"dataSourceProperties":{"cacheServerConfiguration":"true","elideSetAutoCommits":"true","useServerPrepStmts":"true","cachePrepStmts":"true","useSSL":"false","rewriteBatchedStatements":"true","cacheResultSetMetadata":"false","useLocalSessionState":"true","maintainTimeStats":"false","prepStmtCacheSize":"200000","tinyInt1isBit":"false","prepStmtCacheSqlLimit":"2048","serverTimezone":"UTC","netTimeoutForStreamingResults":"0","zeroDateTimeBehavior":"round"},"healthCheckProperties":{},"initializationFailTimeout":1,"validationTimeout":5000,"leakDetectionThreshold":0,"poolName":"HikariPool-8","registerMbeans":false,"allowPoolSuspension":false,"autoCommit":true,"isolateInternalQueries":false}
1 row in set (0.03 sec)

查询未使用的主键生成器

DistSQL 还可以使用 SHOW UNUSED SHARDING KEY GENERATORS 显示未使用的分片键生成器

mysql> SHOW SHARDING KEY GENERATORS;
+--------------------------+-----------+-------+
| name                     | type      | props |
+--------------------------+-----------+-------+
| snowflake_key_generator  | snowflake | {}    |
| t_order_detail_snowflake | snowflake | {}    |
+--------------------------+-----------+-------+
2 rows in set (0.00 sec)

mysql> SHOW UNUSED SHARDING KEY GENERATORS;
Empty set (0.01 sec)

mysql> CREATE SHARDING KEY GENERATOR useless (
    -> TYPE(NAME=SNOWFLAKE)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> SHOW UNUSED SHARDING KEY GENERATORS;
+---------+-----------+-------+
| name    | type      | props |
+---------+-----------+-------+
| useless | snowflake |       |
+---------+-----------+-------+
1 row in set (0.01 sec)

查询未使用的分片算法

DistSQL 可以使用（您猜对了）SHOW UNUSED SHARDING ALGORITHMS 命令显示未使用的分片算法

mysql> SHOW SHARDING ALGORITHMS;
+--------------------------------+--------+-----------------------------------------------------+
| name                           | type   | props                                               |
+--------------------------------+--------+-----------------------------------------------------+
| database_inline                | inline | algorithm-expression=ds_${user_id % 2}              |
| t_order_inline                 | inline | algorithm-expression=t_order_${order_id % 3}        |
| t_order_item_inline            | inline | algorithm-expression=t_order_item_${order_id % 3}   |
| t_order_detail_database_inline | inline | algorithm-expression=ds_${user_id % 2}              |
| t_order_detail_table_inline    | inline | algorithm-expression=t_order_detail_${order_id % 3} |
+--------------------------------+--------+-----------------------------------------------------+
5 rows in set (0.00 sec)

mysql> CREATE SHARDING ALGORITHM useless (
    -> TYPE(NAME=INLINE,PROPERTIES("algorithm-expression"="ds_${user_id % 2}"))
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> SHOW UNUSED SHARDING ALGORITHMS;
+---------+--------+----------------------------------------+
| name    | type   | props                                  |
+---------+--------+----------------------------------------+
| useless | inline | algorithm-expression=ds_${user_id % 2} |
+---------+--------+----------------------------------------+
1 row in set (0.00 sec)

查询使用目标存储资源的规则

您还可以使用 SHOW RULES USED RESOURCE 查看规则中使用的资源。可以使用资源查询所有规则，而不仅仅限于分片规则。

mysql> DROP RESOURCE ds_0;
ERROR 1101 (C1101): Resource [ds_0] is still used by [ShardingRule].

mysql> SHOW RULES USED RESOURCE ds_0;
+----------+----------------+
| type     | name           |
+----------+----------------+
| sharding | t_order        |
| sharding | t_order_item   |
| sharding | t_order_detail |
+----------+----------------+
3 rows in set (0.00 sec)

查询使用目标主键生成器的分片规则

您可以使用 SHOW SHARDING TABLE RULES USED KEY GENERATOR 查找使用键生成器的分片规则

mysql> SHOW SHARDING KEY GENERATORS;
+--------------------------+-----------+-------+
| name                     | type      | props |
+--------------------------+-----------+-------+
| snowflake_key_generator  | snowflake | {}    |
| t_order_detail_snowflake | snowflake | {}    |
| useless                  | snowflake | {}    |
+--------------------------+-----------+-------+
3 rows in set (0.00 sec)

mysql> DROP SHARDING KEY GENERATOR snowflake_key_generator;
ERROR 1121 (C1121): Sharding key generator `[snowflake_key_generator]` in database `sharding_db` are still in used.

mysql> SHOW SHARDING TABLE RULES USED KEY GENERATOR snowflake_key_generator;
+-------+--------------+
| type  | name         |
+-------+--------------+
| table | t_order      |
| table | t_order_item |
+-------+--------------+
2 rows in set (0.00 sec)

查询使用目标算法的分片规则

使用 SHOW SHARDING TABLE RULES USED ALGORITHM 显示使用目标算法的分片规则

mysql> SHOW SHARDING ALGORITHMS;
+--------------------------------+--------+-----------------------------------------------------+
| name                           | type   | props                                               |
+--------------------------------+--------+-----------------------------------------------------+
| database_inline                | inline | algorithm-expression=ds_${user_id % 2}              |
| t_order_inline                 | inline | algorithm-expression=t_order_${order_id % 3}        |
| t_order_item_inline            | inline | algorithm-expression=t_order_item_${order_id % 3}   |
| t_order_detail_database_inline | inline | algorithm-expression=ds_${user_id % 2}              |
| t_order_detail_table_inline    | inline | algorithm-expression=t_order_detail_${order_id % 3} |
| useless                        | inline | algorithm-expression=ds_${user_id % 2}              |
+--------------------------------+--------+-----------------------------------------------------+
6 rows in set (0.00 sec)

mysql> DROP SHARDING ALGORITHM t_order_detail_table_inline;
ERROR 1116 (C1116): Sharding algorithms `[t_order_detail_table_inline]` in database `sharding_db` are still in used.

mysql> SHOW SHARDING TABLE RULES USED ALGORITHM t_order_detail_table_inline;
+-------+----------------+
| type  | name           |
+-------+----------------+
| table | t_order_detail |
+-------+----------------+
1 row in set (0.00 sec)

使分片更好

DistSQL 提供了灵活的语法来帮助简化操作。除了 INLINE 算法之外，DistSQL 还支持标准分片、复合分片、HINT 分片和自定义分片算法。

如果您对 Apache ShardingSphere 有任何问题或建议，请随时在 ShardingSphereGitHub 上发布。

标签
数据库
Raigor Jiang
Smile
Apache ShardingSphere Committer GitHub：https://github.com/RaigorJiang 电子邮件：jianglongtao@apache.org
更多关于我

评论已关闭。

相关内容

Creative Commons License本作品根据 Creative Commons Attribution-Share Alike 4.0 International License 许可。
© . All rights reserved.