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

SQL command optimization needs to remember what are 9 points, for this problem, this article describes in detail the corresponding analysis and solutions, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

The basic language for interacting with the database is sql, and the database needs to perform many steps every time it parses and executes sql statements. Take sql server as an example, when a database receives a query statement, the parser will scan the sql statement and divide it into logical units (such as keywords, expressions, operators, and identifiers) and generate a query tree. Finally, the query optimizer will analyze all the methods that can access the source table of the database, and select a set of steps that return the result set fastest and consume less resources. The query tree is then updated to accurately record this step, which is then handed over to the database engine to execute, and the query results are returned to the user. It can be seen that every time the database engine executes sql commands, there will be a lot of overhead. If the quality of the submitted sql is not high or even there are logic errors, it will cause unnecessary overhead and time waste. To avoid this, the following principles should be noted when using the sql command:

1. Field extraction should follow the principle of "how much you need, how much you need", avoid "select *", and try to use "select Field 1, Field 2, Field 3." Practice has proved that every less field is extracted, the database extraction speed will be improved accordingly. The speed of improvement is also determined by the size of the field you discard.

2. Try to use exists instead of select count (*) to determine whether there is a record. The optimizer supports short circuit when optimizing exists predicates. As long as you find a row, you don't need to scan any other rows to determine whether the table contains rows. The count function is used only when counting the number of rows of all rows in the table.

3. Try to use (not) exists instead of (not) in operation, the sql performance of in is always low.

Statement select dname,deptno from dept where deptno not in (select deptno from emp where dept.deptno=emo.deptno)-statement select dname,deptno from dept where not exists (select deptno from emp where dept.deptno=emo.deptno)

4. Use not in as much as possible and replace it with left outer join.

5. Try not to use or. Using or will cause full table scan, which will greatly reduce the query efficiency.

6. Pay attention to the writing of the where clause, we must consider the sentence order, and determine the order of the conditional clause according to the index order and scope size, so as to make the field order consistent with the index order as far as possible, and the range from large to small.

7. Try to use "> =" instead of ">"

8. Understand the index structure of the table before writing the sql statement. Effective use of indexes can avoid unnecessary full table scans and shorten query time. You should avoid using is null,! in the where clause. The =, not, not exist, not in, not like, and other commands usually cause a full table scan to invalidate the index.

9. In the where clause, any operation on the column (function, calculation, etc.) causes the index to fail. These operations should be moved to the right of the equal sign as far as possible, such as where substring (id,1,1) ='a', it should be written as where id like'a% operations where result*10 > 30 should be written as where result > 30.

The basic principle of optimizing sql commands is to minimize type conversion and computation, make full use of table indexes, and reduce the number of full table scans.

This is the answer to the 9 questions about SQL command optimization that need to be remembered. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

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.