Best practices - PolarDB - Alibaba Cloud Documentation Center

Common scenarios

You may need to connect to distributed databases in various scenarios. Based on the database and table usage, SQL characteristics, and performance and throughput requirements of old and new applications, PolarDB-X 2.0 divides usage scenarios into four common application types. The following table describes the application types.

Application type	Example	Description	SQL characteristic
Applications with a large amount of existing business	A business system that is used by a medical company or hospital for over ten years	A large number of databases and tables are used by existing business. The number of databases is greater than or equal to 10 or the number of tables is greater than or equal to 100. Various types of SQL queries are required. The resources of a standalone database are limited, and the response time (RT) of business queries becomes slower.	Databases are used by a large number of old applications, and a large number of databases or business tables used for existing business exist. A large number of old and complex SQL statements exist and cannot be modified.
Applications with existing and new business	An order management system of a seller that runs business for years and wants to develop new features	A relatively large number of databases and tables are used for existing business. The number of databases is greater than or equal to 2 or the number of tables is greater than or equal to 10. Large tables must be used for new business, and the resources of a standalone database are insufficient due to fast data growth.	A relatively large number of databases and tables are used for business. A large number of SQL statements used for existing business exist, and most of the statements cannot be modified. Some large tables and SQL statements used for new business can be modified and optimized.
New business applications developed based on a standalone MySQL database	A to-be-launched business system developed by a photography company	The number of databases and tables and the business scale are small. The number of databases is less than 2 or the number of tables is less than 10. Scalability is required because a large amount of incremental data is expected to be generated.	In the early stages of the business, immediate launch of the system is required. In later stages, fast iteration and optimization of the system are required. The databases and SQL statements used for the new business can be modified and optimized.
Business applications with high performance and throughput	The core transaction system of a large e-commerce company	The number of databases and tables is small, but the data size is large and the concurrency is high. The application is extremely sensitive to SQL query RT. The overall throughput and performance of the business is of great importance.	Only a few fixed types of SQL queries are performed. High concurrency and stable performance are required for SQL queries.

Users of the preceding application types face different challenges in different scenarios. When the users transform the applications to connect to distributed databases, they have different requirements.

To help users of the preceding application types efficiently use distributed databases to resolve business issues, different working modes are provided for the transparent distribution feature of PolarDB-X. The first time users connect to PolarDB-X databases, the users can select a working mode that meets their business requirements.

Recommend working mode in each scenario

The following table describes the working modes provided by the transparent distribution feature of PolarDB-X and their business effects.

Application type	Optimization objective	Pain point	Recommend working mode	Business effects
Application with a large amount of existing business	The resource limits caused by a standalone database need to be broken, especially the limits on CPUs and I/O. The SQL query RT needs to be optimized.	Hundreds or even thousands of databases and tables are used for existing business, and the JOIN operations between databases and tables are complex. The original SQL queries performed on business are complex and cannot be modified. High compatibility with the SQL statements of distributed databases is required.	Non-partitioned table sharding	The databases, tables, and SQL statements of the new business are compatible with those of the existing business to a maximum extent. The query performance almost remains unchanged. A large number of non-partitioned tables are distributed to different data nodes (DNs). This helps break through the resource limits caused by a standalone database, implement load balancing, and improve performance.
Applications with existing and new business	The resource limits caused by a standalone database need to be broken, especially the limits on CPUs, I/O, and disks. The SQL query performance of the business needs to remain unchanged. The disk space of large tables needs to be scaled out.	A large number of databases and tables used for existing business exist, and the JOIN operations between databases and tables are complex. The SQL queries performed on existing business are complex, and most of the queries cannot be modified. The amount of data in specific databases and tables, especially large tables, for new business increases rapidly.	Non-partitioned table sharding + manual partitioning Note After you set the working mode to non-partitioned table sharding, execute the `ALTER TABLE <table_name> PARTITION BY KEY(<column_name>) locality='';` statement to perform manual partitioning. For more information, see Locality.	The databases, tables, and SQL statements of the new business are compatible with those of the existing business to a maximum extent. The query performance almost remains unchanged. A large number of non-partitioned tables are distributed to different DNs. This helps break through the resource limits caused by a standalone database, implement load balancing, and improve performance. You can perform manual partitioning on large tables. This ensures the read and write performance while resolving the disk scalability issue.
New business applications developed based on standalone MySQL databases	Scalability is required. The requirements on business performance are not high.	The transformation costs need to be reduced, and the business needs to be immediately launched.	Automatic partitioning	All tables are automatically partitioned. This helps break through the resource limits caused by a standalone database. By default, all indexes are global indexes. This ensures the basic performance of non-primary key column-based queries.
Business applications with high performance and throughput	Linear scalability is required. High performance is required.	Linear scalability and a concurrency with tens of thousands or even hundreds of thousands of queries per second (QPS) are required. SQL queries need to be quick and stable because the business is sensitive to performance.	Manual partitioning	You can manually select the optimal partitioning solution for all tables based on your business scenario. You can modify SQL statements for business queries to meet the requirements for linear scalability.