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

This article mainly explains "what are the SQL optimization schemes". The content in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what are the SQL optimization schemes".

Avoid making null judgments.

Try to avoid judging the null value of the field in the where clause, otherwise it will cause the engine to give up using the index and do a full table scan. Here, it is best not to leave NULL for the database, but to populate the database with NOT NULL as much as possible.

Comments, descriptions, comments, and so on can be set to NULL, it is best not to use NULL. Do not mistakenly assume that NULL does not need space, such as char (100), which is fixed when the field is created. Whether the value is inserted or not (NULL is also included), it takes up 100 characters, and in the case of a variable-length field such as varchar, null does not take up space. You can set the default value of 0 on num to ensure that there is no null value for the num column in the table.

Second, do not use select *

Using select * will not only increase the parsing time, but also query the unneeded data at the same time, thus prolonging the data transmission time and consuming energy. For example, a field of text type is usually used to save some complicated things. If you use select *, the field will also be queried.

Third, be careful to use fuzzy query

When a fuzzy match starts with%, the column index is invalidated. The column index is valid if it does not start with%.

Do not use column numbers

Using column numbers will increase unnecessary parsing time.

Give priority to the use of UNION ALL and avoid using UNION

Because UNION compares the records of each query subset, it is usually much slower than UNION ALL. In general, be sure to use UNION ALL if you can meet the requirements with UNION ALL. There is also a situation where business can ensure that there are no duplicate records.

Avoid calculating index fields in where or order by statements

When an operation is performed on an index column, the index is invalidated. The right thing to do is to calculate the value and then pass it in.

7. Use not exist instead of not in

If the query statement uses not in, then both the inside and outside of the table are scanned without using the index, while the subquery of not extsts can still use the index on the table.

VIII. The difference between exist and in

In connects the outer and inner tables as hash, while exists connects the external tables as loop loops, each loop loop.

Then query the inner table. Therefore, in uses the outer index, and exists uses the index of the inner table. If the two tables of the query are of the same size, there is little difference between using in and exists. If one of the two tables is small and the other is large, the large subquery table uses exists and the small subquery table uses in.

Avoid doing the following on index columns

1. Avoid using IS NULL and IS NOT NULL on index columns.

two。 Avoid data type conversions on index columns. (for example, a field is of String type and the parameter is of int type.) when you use the above operation on an index column, the index will be invalidated, resulting in a full table scan.

Complex operations can be properly broken down into several steps

Sometimes there is an example of implementing a complex business through a SQL statement, nesting multi-level subqueries in order to implement a complex business. Causing SQL performance problems. In this case, you can consider splitting the SQL, through multiple SQL statements, or leaving some of the work that the program can do to the program.

Thank you for your reading, the above is the content of "what is the SQL optimization scheme?" after the study of this article, I believe you have a deeper understanding of what the SQL optimization scheme has, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.