For a database, certain resource-intensive SQL queries can cause a series of performance issues, such as prolonged replication latency, connection interruptions, and even database instance crashes. These high-resource queries can significantly reduce the concurrency handling capability of the database cluster. In scenarios of sudden traffic spikes (whether due to business growth or network fluctuations), these queries can lead to severe SQL backlog and exceed the database's concurrency limits, negatively impacting business operations.
Although OceanBase Database does not restrict the number of join tables, the use of certain complex functions, such as regexp_substr, often results in lower execution efficiency, significantly affecting business performance.
To ensure database stability, scalability, and controllability, businesses should prioritize the use of simple SQL statements for basic operations like adding, deleting, modifying, and querying data. The key to evaluating the quality of SQL statements lies in their simplicity and whether they operate within a reasonable performance range. The "reasonable range" refers to maintaining low and stable performance overhead as the business and data scale expand, without consuming excessive CPU or I/O resources. For example, an SQL query based on the primary key maintains relatively consistent performance overhead when processing small amounts of data versus large amounts of data, such as querying one row versus one million rows. This stable performance allows businesses to reasonably estimate the need for database scaling based on 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, causing severe SQL backlog and a significant increase in instance concurrency, threatening business stability. Complex SQL queries can also cause noticeable fluctuations in the performance of the database cluster, reducing its concurrency capabilities and making the business uncontrollable.