Some ideas and principles about SQL optimization (1)

I. Purpose of Optimization

Application optimization can be divided into two aspects: source code optimization andSQL statement Optimization. Due to changes in program logic, source code optimization is costly in terms of time cost and risk (especially for systems in use ). On the other hand, source code optimization has limited effect on improving the database system performance, because the database operations performed by applications are ultimately performed by SQL statements on the database.

There are some direct causes for SQL statement optimization:

1. SQL statements are the only way to operate databases (data). The execution of applications is ultimately attributed to the execution of SQL statements, the efficiency of SQL statements plays a decisive role in the performance of the database system.

2. SQL statements consume 70% ~ 90% of database resources.

3. SQL statements are independent of program design logic. Optimization of SQL statements does not affect program logic. Compared with optimization of program source code, optimization of SQL statements has a low cost of time and risk.

4. SQL statements can be written in different ways. The performance of different statements may vary greatly.

5. SQL statements are easy to learn and difficult to master. The performance of SQL statements is often related to the database structure and number of records of the actual running system. There is no general rule to improve the performance.

Ii. Database optimization ideas

1. Create an index for the key fields.

2. Using Stored Procedures makes SQL more flexible and efficient.

3. Back up the database and clear junk data.

4. SQL statement syntax optimization.

5. Clear and delete logs.

Iii. SQL statement Optimization Principles

Do not assume that only SELECT statements are queries. In fact, DML (INSERT, UPDATE, and DELETE) Statements with any WHERE conditions contain query requirements. In subsequent articles, when it comes to queries, it is not necessarily a SELECT statement, it may also be the query part of the DML statement.

We know that SQL statements are different from statements in other languages (such as C). They are non-procedural (non-procedural) statements, that is, when you want to obtain data, you do not need to tell the database through which way to retrieve data, such as whether to retrieve data through indexes or whether to retrieve each row of data in the table, then, the data is retrieved through a one-to-one comparison (full table scan ).

To implement a query, the kernel must customize a query policy for each query, or generate an execution plan for retrieving Qualified Data ). Typically, for the same query, several execution plans may meet the requirements to obtain qualified data. For example, tables that participate in the connection can have different connection methods, depending on the connection conditions and the connection methods used by the optimizer. To select the optimal execution plan among multiple execution plans, the optimizer must use some actual indicators to measure the resources used by each Execution Plan (I/0 times, CPU, etc ), these resources are what we call the cost (cost ). If an execution plan uses a large amount of resources, it is costly to use the execution plan. The optimizer selects the execution plan with the smallest cost as the execution plan that actually executes the query, and discards other execution plans.