For a database, certain resource-intensive SQL queries can lead to a series of performance issues, such as long delays in primary-secondary replication, connection interruptions, and even database instance crashes. These energy-consuming queries occupy resources for extended periods, thereby reducing the concurrent processing capability of the database cluster. When business traffic surges—whether due to organic growth or network jitter—the heavy-resource queries can cause severe SQL backlog and may exceed the database's inherent concurrency limits, adversely impacting normal business operations.
Although OceanBase AI Database does not limit the number of joined tables, the execution efficiency of certain complex functions, such as those related to regular expressions like regexp_substr, is typically low, which can significantly impact business performance.
To ensure database stability, scalability, and controllability, businesses should use simple SQL statements as much as possible for basic insert, delete, update, and query operations. The key to evaluating whether an SQL statement is good in a business context lies in its simplicity and whether it operates within a reasonable performance range. Here, "reasonable range" refers to the ability of the SQL statement to maintain low and stable performance overhead as the business and data scale out, without consuming excessive CPU or I/O resources. For example, an SQL statement that queries by primary key exhibits little change in performance overhead when processing small amounts of data versus processing millions of records—whether it's one row or ten million rows. Such queries provide stable performance, enabling businesses to reasonably estimate database scaling requirements based on anticipated traffic growth, ensuring the database can support future business expansion.
However, if business queries heavily rely on complex SQL statements with high performance overhead, even minor changes in the database frontend or network can lead to performance issues. This results in SQL statement backlogs and a sharp increase in instance concurrency, posing a threat to business stability. Complex SQL queries can also cause significant performance fluctuations in the database cluster, reducing its concurrency capability and making business operations unpredictable.