What are the several methods of database SQL tuning?

Method: 1. When creating an index, try to avoid full table scans; 2. Avoid using calculations on the index; 3. Try to use parameterized SQL; 4. Try to compress multiple SQL statements into one SQL statement. ; 5. Replace the HAVING clause with where clause; 6. When connecting multiple tables, use table aliases; 7. Try to avoid using cursors, etc.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

1. Create an index

1. To avoid full table scans, you should first consider creating indexes on the columns involved in where and order by

2. (1) Create an index on fields that often need to be retrieved. For example, if you want to retrieve based on the table field username, then you should create an index on the name field. If you often need to retrieve based on employee department and employee position level, then Indexes should be created on the two fields of employee department and employee position level.

(2) The performance improvement brought by creating an index to retrieval is often huge, so when you find that the retrieval speed is too slow, the first thing you should think of is to create an index.

(3) It is best not to have more than 6 indexes on a table. If there are too many, you should consider whether it is necessary to build indexes on some columns that are not commonly used. The more indexes, the better. Although the index can improve the efficiency of the corresponding select, it also reduces the efficiency of insert and update, because the index may be rebuilt during insert or update, so how to build the index needs to be carefully considered, depending on the specific situation. It depends.

2. Avoid using calculations on indexes

In the where clause, if the index column is part of a calculation or function, the DBMS optimizer will not use the index When using full table query, the function is a kind of calculation. EXISTS is usually used in in and exists because in does not use the index.

Low efficiency:

 select * from user where salary*22>11000(salary是索引列)

High efficiency:

 select * from user where salary>11000/22(salary是索引列)

3. Use precompiled queries

Programs usually dynamically execute SQL based on user input. At this time, parameterized SQL should be used as much as possible. This can not only avoid SQL injection vulnerability attacks, but also minimize Important databases will precompile these parameterized SQL, so that when it is executed for the first time, the DBMS will optimize the query and perform precompilation for the SQL statement, so that when the SQL is executed in the future, the precompiled result will be used directly. Can greatly improve the speed of execution.

4. Try to compress multiple SQL statements into one SQL statement

Every time you execute SQL, you must establish a network connection, perform permission verification, optimize the query of the SQL statement, and send it Execution results: This process is very time-consuming, so you should try to avoid executing too many SQL statements. If you can compress it into one SQL statement, do not use multiple statements to execute.

5. Replace the HAVING clause with the where clause

Avoid using the HAVING clause, because HAVING will only filter the result set after retrieving all records, while where will filter the result set before aggregation Select records. If you can limit the number of records through where clauses, you can reduce the overhead in this area. The conditions in HAVING are generally used for filtering aggregate functions. In addition, the conditions should be written in the where clause.

6. Use table aliases

When connecting multiple tables in a SQL statement, please use table aliases and prefix the alias to each column name. This can reduce parsing time and
reduce syntax errors caused by ambiguities in column names.

7. Replace union with union all

When the SQL statement requires union of two query result sets, even if there are no duplicate records in the retrieval results, if the two result sets of union are used It will also try to merge and then sort before outputting the final result. Therefore, if it can be judged that there will be no duplicate records in the search results, union all should be used, so that the efficiency will be improved.

8. Consider using "temporary tables" to temporarily store intermediate results

An important way to simplify SQL statements is to use temporary tables to temporarily store intermediate results. However, the benefits of temporary tables are far more than these. Temporarily store the temporary results in the temporary table, and subsequent queries will be in tempdb. This can avoid multiple scans of the main table in the program, and also greatly reduces the "shared lock" blocking "update lock" during program execution, reducing blocking and improving Improved concurrency performance.

But you must also avoid frequently creating and deleting temporary tables to reduce the consumption of system table resources.

9. Use transaction begin translation only when necessary

A SQL statement in SQL Server is a transaction by default, and it is committed by default after the statement is executed. In fact, this is a minimized form of begin tran, just like a begin tran is implied at the beginning of each statement, and a commit is implied at the end.

In some cases, we need to explicitly declare begin tran. For example, when performing "insert, delete, and modify" operations, we need to modify several tables at the same time. It is required that either all modifications of several tables are successful or none of them are successful. begin tran can play such a role. It can execute several SQL statements together and finally commit them together. The advantage is that data consistency is guaranteed, but nothing is perfect. The price paid by Begin tran is that before submission, all resources locked by SQL statements cannot be released until they are committed.

It can be seen that if Begin tran traps too many SQL statements, the performance of the database will be terrible. Before the large transaction is committed, other statements will inevitably be blocked, resulting in a lot of blocks.

The principle of using Begin tran is that on the premise of ensuring data consistency, the fewer SQL statements trapped by begin tran, the better! In some cases, triggers can be used to synchronize data, and begin tran is not necessarily used.

10. Try to avoid using cursors

Try to avoid returning large amounts of data to the client. If the amount of data is too large, you should consider whether the corresponding requirements are reasonable. Because the efficiency of cursors is poor, if the data operated by the cursor exceeds 10,000 rows, then rewriting should be considered.

11. Use varchar/nvarchar instead of char/nchar

For more programming-related knowledge, please visit: Introduction to Programming! !

The above is the detailed content of What are the several methods of database SQL tuning?. For more information, please follow other related articles on the PHP Chinese website!