proposalbot
78ac771d84
DDM UMN 1st v Reviewed-by: Wagner, Fabian <fabian.wagner@t-systems.com> Co-authored-by: proposalbot <proposalbot@otc-service.com> Co-committed-by: proposalbot <proposalbot@otc-service.com>
9.8 KiB
9.8 KiB
- original_name
ddm_03_0038.html
Sharding Algorithm Overview
Supported Sharding Algorithms
DDM supports database sharding, table sharding, and a variety of sharding algorithms.
| Algorithm | Description | Database Sharding Supported | Table Sharding Supported |
|---|---|---|---|
| MOD_HASH | Performing a simple modulo operation | Yes | Yes |
| MOD_HASH_CI | Performing a simple modulo operation (case-insensitive) | Yes | Yes |
| HASH | Performing a simple modulo operation | Yes | Yes |
| RANGE | Performing a RANGE-based operation | Yes | No |
| RIGHT_SHIFT | Arithmetic right shifting of a sharding key value and then performing a modulo operation | Yes | Yes |
| YYYYMM | Getting a hash code for a YearMonth object and then performing a modulo operation | Yes | Yes |
| YYYYDD | Getting a hash code for a YearDay object and then performing a modulo operation | Yes | Yes |
| YYYYWEEK | Getting a hash code for a YearWeek object and then performing a modulo operation | Yes | Yes |
| MM | Getting a hash code for a MONTH object and then performing a modulo operation | No | Yes |
| DD | Getting a hash code for a DAY object and then performing a modulo operation | No | Yes |
| MMDD | Getting a hash code for a MonthDay object and then performing a modulo operation | No | Yes |
| WEEK | Getting a hash code for a WEEK object and then performing a modulo operation | No | Yes |
Note
- Database and table sharding keys cannot be left blank.
- In DDM, sharding of a logical table is defined by the sharding function (number of shards and routing algorithm) and the sharding key (MySQL data type).
- If a logical table uses different database and table sharding algorithms, DDM will perform full-shard or full-table scanning when you do not specify database and table conditions in SQL queries.
Data Type of Sharding Algorithms
Different sharding algorithms support different data types. The following table lists supported data types.
| Sharding Algorithm | TINYINT | SMALLINT | MEDIUMINT | INTEGER | INT | BIGINT | CHAR | VARCHAR | DATE | DATETIME | TIMESTAMP | Others |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| MOD_HASH | Y | Y | Y | Y | Y | Y | Y | Y | N | N | N | N |
| MOD_HASH_CI | Y | Y | Y | Y | Y | Y | Y | Y | N | N | N | N |
| HASH | Y | Y | Y | Y | Y | Y | Y | Y | N | N | N | N |
| RANGE | Y | Y | Y | Y | Y | Y | N | N | N | N | N | N |
| RIGHT_SHIFT | Y | Y | Y | Y | Y | Y | N | N | N | N | N | N |
| YYYYMM | N | N | N | N | N | N | N | N | Y | Y | Y | N |
| YYYYDD | N | N | N | N | N | N | N | N | Y | Y | Y | N |
| YYYYWEEK | N | N | N | N | N | N | N | N | Y | Y | Y | N |
| MM | N | N | N | N | N | N | N | N | Y | Y | Y | N |
| DD | N | N | N | N | N | N | N | N | Y | Y | Y | N |
| MMDD | N | N | N | N | N | N | N | N | Y | Y | Y | N |
| WEEK | N | N | N | N | N | N | N | N | Y | Y | Y | N |
Note
Y indicates that the data type is supported, and N indicates that the data type is not supported.
Table Creation Syntax of Sharding Algorithms
DDM is compatible with table creation syntax of MySQL databases and adds keyword partition_options for databases and tables sharding.
CREATE TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)
[table_options]
[partition_options]
partition_options:
DBPARTITION BY
{{RANGE|HASH|MOD_HASH|RIGHT_SHIFT|YYYYMM|YYYYWEEK|YYYYDD}([column])}
[TBPARTITION BY
{{HASH|MOD_HASH|UNI_HASH|RIGHT_SHIFT|YYYYMM|YYYYWEEK|YYYYDD}(column)}
[TBPARTITIONS num]
]