Science and technology

Find out how to construct a dynamic distributed database with DistSQL

Distributed databases are widespread for a lot of causes. They enhance reliability, redundancy, and efficiency. Apache ShardingSphere is an open supply framework that lets you remodel any database right into a distributed database. Since the discharge of ShardingSphere 5.0.0, DistSQL (Distributed SQL) has offered dynamic administration for the ShardingSphere ecosystem.

In this text, I display a knowledge sharding situation wherein DistSQL’s flexibility permits you to create a distributed database. At the identical time, I present some syntax sugar to simplify working procedures, permitting your potential customers to decide on their most well-liked syntax.

A sequence of DistSQL statements are run via sensible instances to offer you an entire set of sensible DistSQL sharding administration strategies, which create and keep distributed databases via dynamic administration.

Image by:

(Jiang Longtao, CC BY-SA 4.0)

What is sharding?

In database terminology, sharding is the method of partitioning a desk into separate entities. While the desk information is immediately associated, it usually exists on totally different bodily database nodes or, on the very least, inside separate logical partitions.

Practical case instance

To comply with together with this instance, you have to have these elements in place, both in your lab or in your thoughts as you learn this text:

  • Two sharding tables: t_order and t_order_item.
  • For each tables, database shards are carried out with the user_id area, and desk shards with the order_id area.
  • The variety of shards is 2 databases occasions three tables.
Image by:

(Jiang Longtao, CC BY-SA 4.0)

Set up the atmosphere

1. Prepare a database (MySQL, MariaDB, PostgreSQL, or openGauss) occasion for entry. Create two new databases: demo_ds_0 and demo_ds_1.

2. Deploy Apache ShardingSphere-Proxy 5.1.2 and Apache ZooKeeper. ZooKeeper acts as a governance middle and shops ShardingSphere metadata info.

3. Configure server.yaml within the Proxy conf listing as follows:

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

4. Start ShardingSphere-Proxy and join it to Proxy utilizing a shopper, for instance:

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

5. Create a distributed database:

CREATE DATABASE sharding_db;USE sharding_db;

Add storage sources

Next, add storage sources comparable to the database:

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
);

View the storage sources:

mysql> SHOW DATABASE RESOURCESG;
******** 1. row ***************************
         title: ds_1
         sort: MySQL
         host: 127.0.0.1
         port: 3306
           db: demo_ds_1
          -- Omit partial attributes
******** 2. row ***************************
         title: ds_0
         sort: MySQL
         host: 127.0.0.1
         port: 3306
           db: demo_ds_0
          -- Omit partial attributes

Adding the optionally available G swap to the question assertion makes the output format straightforward to learn.

Create sharding guidelines

ShardingSphere’s sharding guidelines assist common sharding and computerized sharding. Both sharding strategies have the identical impact. The distinction is that the configuration of computerized sharding is extra concise, whereas common sharding is extra versatile and impartial.

Refer to the next hyperlinks for extra particulars on computerized sharding:

Next, it is time to undertake common sharding and use the INLINE expression algorithm to implement the sharding situations described within the necessities.

Primary key generator

The major key generator creates a safe and distinctive major key for a knowledge desk in a distributed situation. For particulars, confer with the doc Distributed Primary Key.

1. Create a major key generator:

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

2. Query the first key generator:

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

Sharding algorithm

1. Create a database sharding algorithm utilized by t_order and t_order_item in widespread:

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

2. Create totally different desk shards algorithms for t_order and t_order_item:

-- Modulo 3 based mostly on order_id in desk 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. Query the sharding algorithm:

mysql> SHOW SHARDING ALGORITHMS;
+---------------------+--------+---------------------------------------------------+
| title                | sort   | 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)

Create a default sharding technique

The sharding strategy consists of a sharding key and sharding algorithm, which on this case is databaseStrategy and tableStrategy. Because t_order and t_order_item have the identical database sharding area and sharding algorithm, create a default technique for use by all shard tables with no sharding technique configured.

1. Create a default database sharding technique:

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

2. Query default technique:

mysql> SHOW DEFAULT SHARDING STRATEGYG;
*************************** 1. row ***************************
                    title: TABLE
                    sort: NONE
         sharding_column:
 sharding_algorithm_name:
 sharding_algorithm_type:
sharding_algorithm_props:
*************************** 2. row ***************************
                    title: DATABASE
                    sort: 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)

You haven’t configured the default desk sharding technique, so the default technique of TABLE is NONE.

Set sharding guidelines

The major key generator and sharding algorithm are each prepared. Now you’ll be able to create sharding guidelines. The methodology I display under is a little bit difficult and includes a number of steps. In the following part, I’ll present you how one can create sharding guidelines in only one step, however for now, witness the way it’s usually performed.

First, outline 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)
);

Here is an evidence of the values discovered above:

  • DATANODES specifies the information nodes of shard tables.
  • TABLE_STRATEGY specifies the desk technique, amongst which SHARDING_ALGORITHM makes use of created sharding algorithm t_order_inline.
  • KEY_GENERATE_STRATEGY specifies the first key era technique of the desk. Skip this configuration if major key era will not be required.

Next, outline 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)
);

Query the sharding guidelines to confirm what you have created:

mysql> SHOW SHARDING TABLE RULESG;
************************** 1. row ***************************
                           desk: 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 ***************************
                            desk: 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)

This seems proper up to now. You have now configured the sharding guidelines for t_order and t_order_item.

You can skip the steps for creating the first key generator, sharding algorithm, and default technique, and full the sharding guidelines in a single step. Here’s how one can make it simpler.

Sharding rule syntax

For occasion, if you wish to add a shard desk referred to as t_order_detail, you’ll be able to create sharding guidelines as follows:

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))
);

This assertion specifies a database sharding technique, desk technique, and first key era technique, but it surely does not use present algorithms. The DistSQL engine robotically makes use of the enter expression to create an algorithm for the sharding guidelines of t_order_detail.

Now there is a major key generator:

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

Display the sharding algorithm:

mysql> SHOW SHARDING ALGORITHMS;
+--------------------------------+--------+-----------------------------------------------------+
| title                           | sort   | 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)

And lastly, the sharding guidelines:

mysql> SHOW SHARDING TABLE RULESG;
*************************** 1. row ***************************
                            desk: 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 ***************************
                            desk: 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 ***************************
                            desk: 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)

In the CREATE SHARDING TABLE RULE assertion, DATABASE_STRATEGY, TABLE_STRATEGY, and KEY_GENERATE_STRATEGY can reuse present algorithms.

Alternatively, they are often outlined shortly via syntax. The distinction is that extra algorithm objects are created.

Configuration and verification

Once you’ve got created the configuration verification guidelines, you’ll be able to confirm them within the following methods.

1. Check node distribution:

DistSQL supplies SHOW SHARDING TABLE NODES for checking node distribution, and customers can shortly study the distribution of shard tables:

mysql> SHOW SHARDING TABLE NODES;

+----------------+------------------------------------------------------------------------------------------------------------------------------+

| title           | 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;
+--------------+------------------------------------------------------------------------------------------------------------------------------+
| title         | 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)

You can see that the node distribution of the shard desk is in step with what’s described within the requirement.

SQL preview

Previewing SQL can be a simple method to confirm configurations. Its syntax is PREVIEW SQL. First, make a question with no shard key, with all routes:

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)

Now specify user_id in a question with a single database route:

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)

Specify user_id and order_id with a single desk route:

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)

Single-table routes scan the fewest shard tables and provide the very best effectivity.

Query unused sources

During system upkeep, algorithms or storage sources which can be now not in use could have to be launched, or sources that have to be launched could have been referenced and can’t be deleted. DistSQL’s SHOW UNUSED RESOURCES command can remedy these issues:

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 RESOURCESG;
*************************** 1. row ***************************
                           title: ds_2
                           sort: 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)

Query unused major key generator

DistSQL can even show unused sharding key mills with the SHOW UNUSED SHARDING KEY GENERATORS:

mysql> SHOW SHARDING KEY GENERATORS;

+--------------------------+-----------+-------+

| title                     | sort      | 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 ineffective (
    -> TYPE(NAME=SNOWFLAKE)
    -> );
Query OK, 0 rows affected (0.04 sec)
mysql> SHOW UNUSED SHARDING KEY GENERATORS;
+---------+-----------+-------+
| title    | sort      | props |
+---------+-----------+-------+
| ineffective | snowflake |       |
+---------+-----------+-------+
1 row in set (0.01 sec)

Query unused sharding algorithm

DistSQL can reveal unused sharding algorithms with (you guessed it) the SHOW UNUSED SHARDING ALGORITHMS command:

mysql> SHOW SHARDING ALGORITHMS;

+--------------------------------+--------+-----------------------------------------------------+

| title                           | sort   | 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 ineffective (
    -> TYPE(NAME=INLINE,PROPERTIES("algorithm-expression"="ds_${user_id % 2}"))
    -> );
Query OK, 0 rows affected (0.04 sec)
mysql> SHOW UNUSED SHARDING ALGORITHMS;
+---------+--------+----------------------------------------+
| title    | sort   | props                                  |
+---------+--------+----------------------------------------+
| ineffective | inline | algorithm-expression=ds_${user_id % 2} |
+---------+--------+----------------------------------------+
1 row in set (0.00 sec)

Query guidelines that use the goal storage sources

You can even see used sources inside guidelines with SHOW RULES USED RESOURCE. All guidelines that use a useful resource could be queried, not restricted to the sharding rule.

mysql> DROP RESOURCE ds_0;

ERROR 1101 (C1101): Resource [ds_0] continues to be utilized by [ShardingRule].
mysql> SHOW RULES USED RESOURCE ds_0;
+----------+----------------+
| sort     | title           |
+----------+----------------+
| sharding | t_order        |
| sharding | t_order_item   |
| sharding | t_order_detail |
+----------+----------------+
3 rows in set (0.00 sec)

Query sharding guidelines that use the goal major key generator

You can discover sharding guidelines utilizing a key generator with SHOW SHARDING TABLE RULES USED KEY GENERATOR:

mysql> SHOW SHARDING KEY GENERATORS;

+--------------------------+-----------+-------+

| title                     | sort      | props |

+--------------------------+-----------+-------+

| snowflake_key_generator  | snowflake | {}    |

| t_order_detail_snowflake | snowflake | {}    |

| ineffective                  | 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 nonetheless in used.
mysql> SHOW SHARDING TABLE RULES USED KEY GENERATOR snowflake_key_generator;
+-------+--------------+
| sort  | title         |
+-------+--------------+
| desk | t_order      |
| desk | t_order_item |
+-------+--------------+
2 rows in set (0.00 sec)

Query sharding guidelines that use the goal algorithm

Show sharding guidelines utilizing a goal algorithm with SHOW SHARDING TABLE RULES USED ALGORITHM:

mysql> SHOW SHARDING ALGORITHMS;

+--------------------------------+--------+-----------------------------------------------------+

| title                           | sort   | 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} |

| ineffective                        | 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 nonetheless in used.
mysql> SHOW SHARDING TABLE RULES USED ALGORITHM t_order_detail_table_inline;
+-------+----------------+
| sort  | title           |
+-------+----------------+
| desk | t_order_detail |
+-------+----------------+
1 row in set (0.00 sec)

Make sharding higher

DistSQL supplies a versatile syntax to assist simplify operations. In addition to the INLINE algorithm, DistSQL helps commonplace sharding, compound sharding, HINT sharding, and customized sharding algorithms.

If you’ve got any questions or options about Apache ShardingSphere, please be at liberty to submit them on ShardingSphereGitHub.

Related Items:, , , ,

Recommended for you

Most Popular

26.9K
Lifestyle

No-Go For Idaho: Back To The Drawing Board On State-Based Health Plans
16.3K
Lifestyle

Purveyors Of Black-Market Pharmaceuticals Target Immigrants
11.0K
Science and technology

Send and obtain Gmail from the Linux command line
7.0K
Politics

U.S. Navy Confirms First Test of New Mach 5 Hypersonic Missile
6.9K
Politics

Deadlier M4A1 Carbine Reaching More and More U.S. Army Troops
5.8K
Lifestyle

How A Drug Company Under Pressure For High Prices Ratchets Up Political Activity
5.7K
Lifestyle

FDA Raids Florida Stores That Consumers Use To Buy Drugs From Canada
5.4K
Lifestyle

No Cash, No Heart. Transplant Centers Require Proof Of Payment.
5.3K
Science and technology

Hubble Telescope Spots Active Incoming Comet, Not Planet Niburu, Some 1.5 Billion Miles From Sun
4.8K
Lifestyle

California Firm Running Physician Practices Is Closing Down as Scrutiny Ramps Up

breakingExpress.com features the latest multimedia technologies, from live video streaming to audio packages to searchable archives of news features and background information. The site is updated continuously throughout the day.

Copyright © 2017 Breaking Express, Green Media Corporation

To Top