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

This article mainly explains "What are the ten optimization techniques of SQL". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let Xiaobian take you to learn "What are the ten optimization skills of SQL"!

Avoid making null judgments. Try to avoid making null value judgments on fields in where clauses, otherwise the engine will abandon the use of indexes and perform full table scans. It is best not to leave NULL for the database, and use NOT NULL to fill the database as much as possible. Remarks, descriptions, comments, etc. can be set to NULL, preferably not NULL. Do not mistakenly think that NULL does not need space, such as char(100) type, when the field is established, the space is fixed. Regardless of whether a value is inserted (NULL is included), it takes up 100 characters. If it is a variable length field such as varchar, null takes up no space. You can set a default value of 0 on num to ensure that there are no null values in the num column of the table.

Second, do not use select * Use select * will increase the parsing time, in addition to the unnecessary data will be queried at the same time, thus extending the data transmission time, consuming energy. For example, text type fields are usually used to store some complicated things. If select * is used, the field will also be queried.

When fuzzy matching starts with %, the column index will be invalid. The column index is valid unless it starts with %.

4. Do not use column numbers. Using column numbers will increase unnecessary parsing time.

UNION ALL is preferred to avoid UNION because UNION compares the records of each query subset, so it is usually much slower than UNION ALL. In general, use UNION ALL if that works. There is also a situation where the business can ensure that duplicate records do not occur.

Avoid calculating index fields in where statements or order by statements When an operation is performed on an index column, the index will be invalid. The correct approach is to calculate the value and pass it in.

7. Use not exist instead of not in If the query statement uses not in, then the inner and outer tables are scanned in full, without using the index; and the subqueries of not extsts can still use the index on the table.

8. The difference between exist and in is to hash the outer table and the inner table, while exists is to loop the outer table, and query the inner table every time the loop loops. Therefore, in uses the index of the outer table, and exists uses the index of the inner table. If the two tables in the query are of equal size, there is little difference between in and exists. If one of the two tables is small and the other is large, then the large subquery table is used as exists and the small subquery table is used as in.

Avoid using IS NULL and IS NOT NULL on index columns. 2. Avoid data type conversions on index columns. (For example, a field is of type String, and the parameter is of type int when passed.) When the above operation is used on the index column, the index will be invalid, resulting in a full table scan.

X. Complex operations can be considered to be broken down into several steps. Sometimes there are examples of implementing complex services through an SQL statement. In order to implement complex services, nested multi-level sub-queries. This causes SQL performance problems. For this situation, consider splitting SQL, implementing it through multiple SQL statements, or handing over some of the work that the program can do to the program.

At this point, I believe that everyone has a deeper understanding of "what are the ten optimization techniques of SQL", so let's actually operate it! Here is the website, more related content can enter the relevant channels for inquiry, pay attention to us, continue to learn!

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.