In a database, some resource-intensive SQL queries may cause a series of performance issues. For example, such queries may cause an increase in the time required for data replication between the leader and the follower, disconnections, or breakdown of the database instance. These SQL queries occupy resources for a long time, compromising the parallel processing capabilities of the database cluster. When the business traffic surges due to business growth or network jitters, these resource-consuming queries will intensify SQL accumulation and may drain the parallel processing capabilities of the database, thereby affecting your business.
OceanBase Database does not limit the number of tables to be joined. However, it has poor performance in executing some complex functions, such as regexp_substr related to regular expressions, which significantly compromises the business performance.
To ensure database stability, scalability, and controllability, you need to use simple SQL statements for basic addition, deletion, modification, and query operations. Simplicity and performance overhead are key metrics for measuring SQL statements in a business system. You must ensure that as the business volume and data scale increase, the performance overheads of SQL statements can remain low and stable without causing high CPU utilization or I/O resource usage. For example, the performance overhead of an SQL statement for primary key-based queries does not change much when it processes a small amount of data and a very large amount of data, such as one row of data and 10 million rows of data. Such queries have stable performance. This way, you can reasonably estimate the database scale-out requirements incurred from traffic growth, so as to support future business development.
If most business queries rely on complex SQL statements with high performance overheads, slight changes in the database frontend or network may cause performance issues. As a result, SQL statements are accumulated and the number of concurrent instances surges, threatening business stability. In a word, complex SQL statements can cause significant performance fluctuations in a cluster, resulting in low concurrency and uncontrollable business.