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

This article mainly explains "how to optimize SQL". 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 how to optimize SQL.

First, avoid making null judgment. 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 * if you use select *, it 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 cautious about using fuzzy queries when fuzzy matching starts with%, the column index will be invalidated. The column index is valid if it does not start with%.

Fourth, do not use column numbers to use column numbers, it will increase unnecessary parsing time.

Fifth, give priority to using UNION ALL and avoid using UNION because UNION will compare the records of each query subset, so 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 the index field in the where statement or order by statement when the index column is operated on, the index will become invalid. 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 all the tables are scanned inside and outside without using the index, while the subquery of not extsts can still use the index on the table.

8. The difference between exist and in in connects the outer table with the inner table as hash, while exists uses the external table as a loop loop, and each loop loop queries 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 operations on index columns. 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 divided into several steps. Sometimes there are examples of realizing complex business through a SQL statement. In order to achieve complex business, nested multi-level subqueries. 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 "how to optimize SQL". After the study of this article, I believe you have a deeper understanding of how to optimize SQL, 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.