Database Design Specifications
All character storage and representation must use utf-8 or utf8mb4 encoding. Tables and fields must have comment information.
Avoid using large transactions. Use short transactions, lightweight operations, and sequential access instead. This means keeping transaction durations short, avoiding remote RPC calls within transactions, minimizing the amount of data processed in batches per transaction, and maintaining a consistent order when business tables are updated to ensure transactions follow the same locking sequence. Avoid using FOR UPDATE without an index, as it can easily lead to a full table range lock.
Note:
For example, executing multiple SELECT or UPDATE statements within a single transaction can severely impact concurrency if the transaction is high-frequency. This is because locks and other resources held by the transaction are only released upon ROLLBACK/COMMIT. However, data write consistency must also be evaluated.
Database and Table Specifications
TDSQL Boundless supports regular tables, partition tables, and synchronized tables. You must specify the table type when creating a table. These three types cannot be converted to each other, except when a single table is converted to a partition table.
Note:
TDSQL Boundless supports the following table types:
|
Regular Table (Single Table) | Small data volume, no distribution requirement | All data resides within a single RG. |
Partition Table | Large data volume, requires horizontal scaling | Data is distributed across multiple service groups according to rules. |
Synchronization table | System configurations, dimension tables, parameter tables, small tables with more reads than writes | Strongly synchronized replicas across all nodes. Write operations may experience latency under frequent writes, for example, a Lease may be blocked when a Follower fails. |
When a partition table is created, the primary key and all unique indexes must include the partition key. The recommended number of partitions is twice the expected maximum number of nodes. Too few partitions can lead to uneven data distribution during scaling, while too many partitions increase management overhead and introduce some performance degradation.
Note:
The partitioning types supported by TDSQL Boundless are the same as those in MySQL 8.0. The details are as follows, including primary partitions and subpartitions (subpartitions only support HASH and KEY modes):
|
HASH
| Uniform hashing, integer keys | Integer type |
KEY
| Uniform hashing, string keys | Any type |
RANGE
| Time range and ID range queries | Integer/Date |
LIST
| Categorization by enumerated values (such as region) | Integer |
A single table can be converted to a partition table online. However, modifying the partition definition at runtime may require a table-copying DDL operation. The duration of the DDL and its IO overhead increase with the amount of data.
ALTER TABLE t PARTITION BY HASH(id) PARTITIONS 16;
Do not create a synchronized table for a table that is frequently updated and written to.
Note:
For synchronized tables, a write operation returns only after it is strongly synchronized to all available Follower replicas. Each replica can provide strongly consistent reads. Consequently, this mechanism is not suitable for high-volume write scenarios. Write performance degrades linearly as the number of replicas increases, especially in large clusters. Exercise caution when using synchronized tables in large clusters. If any Follower node fails, write requests will be stalled for a lease period (approximately seconds).
Synchronized tables cannot be converted to regular tables or partition tables.
Once created, a broadcast synchronization log stream will not be destroyed, even if all synchronized tables are deleted.
In the SQL syntax for creating a synchronized table, SYNC_LEVEL = NODE(ALL) and DISTRIBUTION = NODE(ALL) currently only support ALL. You cannot specify a subset of nodes.
A primary key must be explicitly defined for a data table. It is recommended to use a composite primary key or a primary key with a random hash prefix.
Note:
Tables without a primary key can easily lead to write hotspots.
Using auto-incrementing or monotonically increasing columns (for example, a timestamp field) as the primary key is not recommended, as monotonic increase can cause write hotspots.
If the business must retain an auto-incrementing column, it is recommended to use it as a unique index. Otherwise, duplicate values may occur. Auto-increment values guarantee global uniqueness only, not continuity.
Note:
Auto-increment columns have a shard caching mechanism by default. The tdsql_auto_increment_batch_size parameter defaults to 100.
If the business requires continuous auto-increment, set tdsql_auto_increment_batch_size=1. Continuous auto-increment may cause hotspots and performance degradation.
It is recommended that a single table contain no more than 60 columns.
It is recommended that a single row of data not exceed 64 KB.
It is recommended to keep table names within 32 characters.
Use the decimal type to define decimal data types. Do not use float or double.
Note:
When data is stored, float and double data types can suffer from precision loss, which may lead to incorrect results during value comparisons. If the data range to be stored exceeds the range of the decimal type, it is recommended to split the data into integer and fractional parts for separate storage.
Avoid using reserved words, such as desc, range, match, and delayed. For details, see MySQL Official Reserved Words. In addition to MySQL reserved words, TDSQL Boundless adds the following: CLUSTERING, COROUTINE, DISTRIBUTION, POLICY, SEQUENCE_TABLE, FLASHBACK, TDSQL_AWR, UNLOAD. When you create a table, set default values for its fields and define them as NOT NULL to prevent null or missing values during data insertion. For numeric types, a default value of 0 is recommended. For character types such as VARCHAR, an empty string (for example, '') is recommended as the default value.
It is recommended that a table include two fields: create_time and update_time, both of the datetime type.
Note:
In the event of a database failure, you can determine the time when data was written to or modified in the database. In extreme cases, this can assist in making decisions for data recovery.
If the strings to be stored have nearly identical lengths, use the char fixed-length string type.
Fields can be appropriately duplicated across tables to avoid join queries and improve query performance, but data consistency must be considered.
Note:
Redundant fields should adhere to the following principles:
Fields that are not frequently modified.
Not varchar fields with excessive length or text fields.
Set an appropriate storage length (avoid using long-type fields such as LONG TEXT and BLOB). This not only saves database table space and index storage but, more importantly, improves search speed.
Note:
When using an ORDER BY .. LIMIT query, prioritize optimizing the query statement through indexes to improve execution efficiency.
When executing queries using ORDER BY, GROUP BY, or DISTINCT, keep the result set filtered by the WHERE clause within 1000 rows. Otherwise, query efficiency will be reduced.
When using ORDER BY, GROUP BY, or DISTINCT statements, prioritize using indexes to search for pre-sorted data. For example, a query such as WHERE a=1 ORDER BY b can utilize the key(a,b) index.
When using a JOIN query, make full use of indexes on the same table in the WHERE clause.
Note:
For example, `select t1.a, t2.b from t1, t2 where t1.a = t2.a and t1.b = 123 and t2.c = 4`.
If the t1.c and t2.c fields are identical, the index (b,c) on t1 will only utilize the b column. In this case, if you change the WHERE condition from t2.c=4 to t1.c=4, the full index can be utilized. This scenario may occur during field redundancy design (denormalization).
Prefer UNION ALL over UNION, and consider whether data deduplication is required. UNION ALL does not deduplicate data. Because it omits the sorting operation, it is faster than UNION. If your business does not require deduplication, prioritize using UNION ALL.
When pagination query logic is implemented in code, return directly if the COUNT is 0 to avoid executing subsequent pagination statements.
Avoid frequent COUNT operations on tables. Performing COUNT operations on large tables can be time-consuming, typically resulting in second-level response times. If you have a frequent need to COUNT tables, introduce a dedicated counter table to address this.
Use LIMIT 1 when you are certain that only one row will be returned. When you can determine the size of the result set while ensuring data accuracy, use a LIMIT query to return results as quickly as possible.
When you evaluate the efficiency of DELETE and UPDATE statements, you can convert them to SELECT statements and then run EXPLAIN. The EXPLAIN command helps analyze the execution plan and performance bottlenecks of SQL queries. However, note that frequently executing SELECT statements can degrade database performance. Therefore, when using the EXPLAIN command to analyze SQL queries, minimize the number of SELECT executions. During SQL query analysis, you must balance query efficiency against database performance, weigh the trade-offs, and select the optimal approach.
TRUNCATE TABLE is faster than DELETE and consumes fewer system and log resources. For full table deletion, use TRUNCATE TABLE.
Note:
TRUNCATE TABLE does not write the deleted data to the log file.
TRUNCATE TABLE is functionally equivalent to a DELETE statement without a WHERE clause.
TRUNCATE TABLE cannot be written in the same transaction as other DML statements.
Avoid using negative queries whenever possible to prevent full table scans.
Note:
A negative query refers to using negative operators, such as: NOT, !=, <>, NOT EXISTS, NOT IN, and NOT LIKE. If a negative query is used, the index structure cannot be leveraged for binary search, and only a full table scan can be performed.
Avoid performing JOIN operations on more than three tables. The data types of fields to be joined must be consistent.
Ensure that fields used in multi-table JOIN queries have indexes. In multi-table JOIN operations, select the table with the smaller result set as the driving table to JOIN other tables. Even for two-table JOINs, pay attention to table indexes and SQL performance.
Note:
When a partition table is queried, include the partition key in the WHERE clause to avoid a full partition scan.
Do not update the partition key of a partition table.
Note:
Do not modify the partition key value using UPDATE, as it triggers cross-partition data migration, which is inefficient and may fail.
Avoid SELECT * and explicitly list the required fields.
For simple SQL, prioritize splitting it.
Note:
For an OR condition such as: f_phone='10000' or f_mobile='10000', each field has its own index, but only one of them can be utilized. You can split it into two SQL statements or use union all.
When complex computations or business logic need to be performed in SQL, prioritize implementing them at the business layer.
For large-scale queries (such as full table scans), it is recommended to add LIMIT protection.
Use a reasonable pagination method to improve pagination efficiency. Do not use jump pagination for large pages.
Note:
For example, there is a pagination statement similar to the following:
SELECT * FROM table1 ORDER BY ftime DESC LIMIT 10000,10;
This pagination method results in significant I/O.
Recommended pagination method: pass in the boundary value from the previous pagination.
SELECT * FROM table1 WHERE ftime < last_time ORDER BY ftime DESC LIMIT 10;
Minimize the use of the IN operator, and ensure the number of elements in the set following IN does not exceed 500.
To reduce the number of interactions with the database, you can appropriately use batch SQL statements. For example: INSERT INTO … VALUES (XX),(XX),(XX)....(XX); It is recommended that the number of XX placeholders be kept within 100. For larger-scale data loading, it is recommended to use the BulkLoad mode (the tdstore_bulk_load parameter), which can improve performance by several times. For details, see About Data Import (BulkLoad Mode). Avoid using implicit type conversion.
Note:
The type conversion rules are detailed as follows:
1. When at least one of the two parameters is NULL, the comparison result is also NULL. A special case is that using the <=> operator to compare two NULLs returns 1. In both of these cases, type conversion is not required.
2. If both parameters are strings, they are compared as strings, and no type conversion is performed.
3. If both parameters are integers, they are compared as integers, and no type conversion is performed.
4. When a hexadecimal value is compared with a non-numeric value, it is treated as a binary string.
5. If a parameter is of type TIMESTAMP or DATETIME and the other parameter is a constant, the constant is converted to a timestamp.
6. If one parameter is of type decimal and the other is a decimal or an integer, the integer is converted to decimal before comparison. If the other parameter is a floating-point number, the decimal is converted to a floating-point number for comparison.
7. In all other cases, both parameters are converted to floating-point numbers before comparison.
8. If an index is built on a string type column and that column is compared with an integer value, it falls under rule 7 mentioned above. For example, if the f_phone column is defined as varchar but the WHERE clause uses f_phone in (098890), both parameters are treated as floating-point numbers. In this case, the string is converted to a float, which prevents MySQL from using the index and leads to performance issues. For a case like f_user_id ='1234567', it falls under rule 2 mentioned above, and the number is directly compared as a string.
When business requirements permit, a transaction should contain as few SQL statements as possible, ideally no more than five. This is because excessively long transactions can lead to issues such as prolonged data locking, MVCC version backlog, transaction waits, and excessive connection consumption.
When business requirements permit, avoid excessively large transactions. Keep individual transactions under 1 GB. For very large batch deletion operations, it is recommended to split them using BATCH LIMIT. Avoid using FOR UPDATE without an index: it can easily lead to a full table range lock.
Avoid using natural joins (natural join).
Note:
In a natural join, the join columns are not explicitly defined but are implicit, which can lead to issues that are difficult to understand and non-portable.
TDSQL Boundless supports two isolation levels: Read Committed (RC) and Repeatable Read (RR), with RR being the default.
Note:
RR is essentially Snapshot Isolation. It prevents phantom reads but may allow write skew (Write Skew).
Difference from MySQL under the RC level: TDSQL Boundless may still use range locks under RC (MySQL RC does not add gap locks).
Transaction data is fully cached in memory before commit. The feature for persisting large transactions to disk is still under development.
Three types of transaction initiation methods are supported.
BEGIN;
START TRANSACTION;
SET autocommit = 0;
Note:
A read snapshot (ReadView) is allocated when the first read/write SQL statement is executed, not at the BEGIN statement.
It supports partial rollback to a SAVEPOINT.
Pessimistic transactions are supported: SELECT ... FOR UPDATE (exclusive lock) / SELECT ... LOCK IN SHARE MODE (shared lock).
A pessimistic lock without an index is upgraded to a range lock, potentially locking the entire table. Therefore, ensure that your query conditions have an index.
Note:
In TDSQL Boundless, locks are based on logical keys rather than physical storage, which differs from InnoDB's behavior.
When a record does not exist, the behavior differs from MySQL and does not escalate to a gap lock.
Note:
Based on actual business requirements, reduce the use of ORDER BY queries that cannot be optimized by indexes. Statements such as ORDER BY, GROUP BY, and DISTINCT consume significant CPU resources.
When dealing with complex SQL statements, first design them with reference to existing indexes. Execute EXPLAIN to view the execution plan, leverage indexes, add more query restrictions, and confirm whether partition pruning is effective.
When using new SELECT, UPDATE, or DELETE statements, always use EXPLAIN to check index usage in the execution plan and try to avoid the following in the extra column: Using File Sort, Using Temporary. If the number of rows scanned in the execution plan exceeds 1000, evaluate whether to allow deployment. Perform daily slow log statistical analysis and handle slow log statements.
Note:
EXPLAIN Interpretation:
type: ALL, index, range, ref, eq_ref, const, system, NULL (listed from left to right, performance ranges from worst to best).
possible_keys: Indicates which index MySQL can use to find records in a table. If an index exists on a field involved in the query, it will be listed, but it may not be used by the query.
key: Indicates the key (index) that MySQL actually decides to use. If no index is selected, the key is NULL. To force MySQL to use or ignore the indexes listed in the possible_keys column, use FORCE INDEX, USE INDEX, or IGNORE INDEX in your query.
ref: Which columns or constants are used to look up values on the index columns.
rows: The estimated number of rows that need to be read to find the required records, based on table statistics and index usage.
Extra:
Using temporary: Indicates that MySQL needs to use a temporary table to store the result set, which is common in sorting and grouping queries.
Using filesort: In MySQL, a sorting operation that cannot be completed using an index is referred to as a "filesort".
Using index: Indicates that an index is used. If only Using index appears, it means the data table was not queried, and the query was completed using only the index table. This scenario is known as a covering index. If Using where also appears, it indicates that the index is used to locate and read records. While the index is still utilized, the data table must be accessed.
Using where: Indicates a conditional query. It appears when not all data from the table is read, or when all required data cannot be obtained solely through an index. If the type column is ALL or index and this information does not appear, you might be executing an incorrect query that returns all data.
Using functions on columns in the WHERE clause causes the index to become ineffective.
Note:
For example, in a condition like `WHERE left(name, 5) = 'zhang'`, the `left` function causes the index on the `name` column to become ineffective. You can modify this condition on the application side to avoid using the function. When the returned result set is small, the application side can filter the rows that meet the condition.
Note:
All DDL operations (such as creating tables and altering table structures) must be executed by DBAs using the Database Management Console (DMC) only after they are reviewed, and they should be deployed during off-peak business hours. When adding indexes to large tables, Online DDL is used by default, specifically with ALGORITHM=INPLACE (default), which does not block business reads and writes. It is recommended to monitor INFORMATION_SCHEMA.DDL_JOBS to check the DDL execution progress. Note:
1. You must perform a check before executing DDL:
1.1 Ensure sufficient storage space is available (INPLACE DDL requires temporary space).
1.2 Check for slow queries or long-running transactions.
1.3 Check whether the data distribution is balanced (reduce the degree of parallelism when severe skew occurs).
1.4 Confirm that the current CPU/IO load is within a reasonable range.
2. Reduce the degree of parallelism for DDL execution (default is 8) when severe data skew occurs.
SET max_parallel_ddl_degree = 4; -- or 2.
3. It is recommended to monitor the DDL progress during execution.
SELECT * FROM information_schema.ddl_jobs WHERE state != 'done';
Do not modify the primary key of a table after creation. Design the primary key properly when creating the table.
Note:
Adding or dropping a primary key is not supported using the Online method. Use the COPY method instead, which locks the table.
Online DDL operations, which require table locking and data copying, are not currently supported. It is recommended to reduce the degree of parallelism for DDL execution.
Note:
All primary key-related DDL operations (Add/Drop Primary Key)
EXCHANGE PARTITION
ANALYZE/CHECK PARTITION
Modify the character set (in certain cases).
Modify the column type (in certain cases).
Note:
Differences from MySQL 8.0:
TDSQL Boundless supports concurrent DML operations for PARTITION BY, Converting charset, and Changing column type, while MySQL does not.
Permissions require fine-grained control. Separate read and write permissions, and separate Ops and development permissions.
DDL operation logs are retained.