Compatible with frequently used MySQL syntax elements
These include MySQL's language structures, character sets, time zones, data types, common functions, preprocessing protocols, sorts, joins (JOIN), stored procedures, indices, partitions, transactions, control commands as well as common DDL, DML, and DCL commands and database access APIs.
(Distributed) Transaction Support
TDSQL supports distributed transactions through two-phase commit, making it ideal for scenarios such as money transfer, billing, and payment. Its cross-node transaction performance is about 70% of a single node, which is roughly 56% higher than that of XA, an open-source distributed transaction protocol. Currently, this capability is exclusive to MySQL 5.7.
(Distributed) JOIN Support
TDSQL supports JOIN (joint query) operations across physical nodes, namely, distributed joins. If a join-related table has a shardkey equality condition, thanks to the consistency principle of sharding, this part of data will be automatically stored to the same physical node. In this case, it is equivalent to performing a standalone join which has the best performance. If cross-physical node data is involved, the proxy will first pull data from other nodes and cache it, in which case performance will be compromised due to network data transfer.
TDSQL supports storing JSON formatted data, making JSON processing more efficient and enabling early detection of errors. If you want to use JSON types and do not want to compromise on traditional databases capabilities such as data consistency, transactions, and joins, TDSQL will be a good choice. The JSON support of TDSQL is based on MySQL, which slightly differs from MongoDB in terms of usage. If interested, you can check out the JSON capability comparison between TDSQL and MongoDB.
Three Table Creation Solutions
Sharded table: a horizontally split table. This solution is usually applicable to large and rapidly growing tables storing more than 20 million rows of data (over 50 GB);
Broadcast table: all operations are broadcast to all logical shards, which means that each shard has the full data of the table. This solution is usually suitable for configuration tables or tables that require frequent joins with fewer data updates. A broadcast table can converge joint queries (joins) and transactions between two tables to a single node for improved performance;
Single table: a table that does not need to be sharded. This solution is usually ideal for database tables with a small amount of data. As a single table is not split, it is fully compatible with MySQL during use.
Globally Unique Numerical Sequence
This is similar to AUTO_INCREMENT. The auto-increment sequence provides a globally unique numerical ID service to ensure the global uniqueness of data entries such as unique keys and primary keys in a distributed environment.
SQL statements can be passed through to a specified target shard through annotation (hint) to improve performance and operating flexibility.
TDSQL also supports table partitioning on top of sharding, i.e., adding an additional layer of logical partition (aka secondary partition) to the horizontally sharded table. This feature can balance data distribution and access while improving query efficiency, and are ideal for business scenarios where historical data needs to be deleted regularly, such as game match scores and IoT sensor data.