Shulou(Shulou.com)05/31 Report--

Editor to share with you the ways to tune the database SQL, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Way: 1, to create the index, try to avoid full table scan; 2, avoid calculation on the index; 3, try to use parameterized SQL;4, try to compress more than one SQL statement into a SQL; 5, replace HAVING sentence with where sentence; 6, connect multiple tables, use table aliases; 7, try to avoid using cursors and so on.

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

one。 Create an index

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

2. (1) create an index on fields that often need to be retrieved, such as by table field username, then you should create an index on the name field, and if you often need to search by employee department and employee job level, you should create an index on both employee department and employee job level fields.

(2) the performance improvement of index creation 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) the number of indexes in a table should not exceed 6. If there are too many indexes, it should be considered whether it is necessary to build indexes on some infrequently used columns. Index is not the more the better, the index can improve the efficiency of the corresponding select, but also reduce the efficiency of insert and update, because insert or update may rebuild the index, so how to build the index needs to be carefully considered, depending on the specific situation.

two。 Avoid using calculations on indexes

In the where sentence, if the index column is part of a computed or function, DBMS's optimizer will not use the index but the full table query, the function

It is a kind of computation, and EXISTS is usually used in both in and exists, because in does not walk the index

Low efficiency:

Select * from user where salary*22 > 11000 (salary is an index column)

High efficiency:

Select * from user where salary > 11000amp 22 (salary is the index column) three. Use precompiled queries

In the program, SQL is usually executed dynamically according to the user's input, so parameterized SQL should be used as much as possible, which can not only avoid SQL injection vulnerabilities.

Attack, the most important database will precompile these parameterized SQL, so that DBMS will optimize the query for the SQL statement when it is executed for the first time

And perform precompilation, so that the precompiled results will be directly used when the SQL is executed later, which can greatly improve the speed of execution.

four。 Try to compress multiple SQL statements into one SQL sentence.

Every time you execute SQL, you have to establish a network connection, verify permissions, optimize the query of SQL statements, and send the execution result.

It is very time-consuming, so you should try to avoid executing too many SQL statements, and do not use multiple statements that can be compressed to one SQL execution.

five。 Replace where sentences with HAVING sentences

Avoid using the HAVING sentence, because HAVING filters the result set only after all records have been retrieved, while where before aggregation

Brushing records, if you can limit the number of records through the where sentence, you can reduce the overhead. The conditions in HAVING are generally used for aggregate functions.

In addition, the condition should be written in the where sentence.

six。 Use the alias of the table

When joining multiple tables in a SQL statement, use the alias of the table and prefix the alias on each column name. In this way, the parsing time can be reduced and reduced.

There are fewer grammatical errors caused by the ambiguity of listing.

seven。 Replace union with union all

When the SQL statement requires two sets of query results to union, even if there are no duplicate records in the retrieval results, if you use the two result sets of union

A merge is also attempted and then sorted before the final result is output, so if it can be determined that there will be no duplicate records in the retrieval results, you should

Union all should be used so that efficiency can be improved.

eight。 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, but the benefits of temporary tables are far more than these. Temporary results are temporarily stored in temporary tables, and the subsequent query is in tempdb, which can avoid scanning the main table many times in the program, and greatly reduce the "shared lock" blocking "update lock" in program execution, reducing blocking and improving concurrent performance.

However, it is also necessary to avoid frequent creation and deletion of temporary tables to reduce the consumption of system table resources.

nine。 Use transaction begin translation only if necessary

A SQL statement in SQL Server is a transaction by default, and it is also the default commit after the execution of the statement. In fact, this is a minimized form of begin tran, like implying a begin tran at the beginning of each sentence and an commit at the end.

In some cases, we need to explicitly declare begin tran, for example, to do "insert, delete, change" operations, you need to modify several tables at the same time, requiring either several tables to be modified successfully or not. Begin tran can play this role, it can execute several SQL statements together, and then commit together. The advantage is that the data is consistent, but nothing is perfect. The price paid by Begin tran is that all resources locked by SQL statements cannot be released until commit is dropped before committing.

It can be seen that if Begin tran traps too many SQL statements, the performance of the database will be poor. Before the big transaction commits, other statements are bound to be blocked, resulting in a lot of block.

The principle used by Begin tran is that under 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, not necessarily begin tran.

ten。 Avoid using cursors as much as possible

Try to avoid returning a large amount of data to the client. If the amount of data is too large, you should consider whether the corresponding requirements are reasonable. Because cursors are inefficient, rewriting should be considered if the cursor operates on more than 10,000 rows of data.

eleven。 Using varchar/nvarchar instead of char/nchar is all the content of this article entitled "what are the ways to tune database SQL?" Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.