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

Editor to share with you an example of SQL performance optimization, 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!

SQL performance optimization

1. The SELECT statement must specify the field name.

SELECT * adds a lot of unnecessary consumption (cpu, io, memory, network bandwidth); increases the possibility of using overlay indexes

When the table structure changes, the front break also needs to be updated. Therefore, it is required to put the field name directly after the select.

2. IN in the SQL statement should not contain too many values.

MySQL optimizes IN accordingly, that is, all the constants in IN are stored in an array that is sorted.

However, if the number is higher, the consumption is also relatively large. For consecutive values, don't use in if you can use between; or replace it with a connection.

3. Distinguish between in and exists,not in and not exists

Select * from table A where id in (select id from form B)

Equivalent to

Select * from table A where exists (select * from form B where table B.id = table A.id)

The difference between in and exists is mainly caused by the change of the driver order (which is the key to the performance change). If it is exists, then the outer layer table is the driver table and is accessed first, and if it is IN, then the subquery is executed first.

Therefore, IN is suitable for situations where the appearance is large and the inner table is small; EXISTS is suitable for situations where the appearance is small and the inner table is large.

4. Fuzzy query with% prefix is not recommended.

For example, LIKE "% name" or LIKE "% name%", this kind of query can cause the index to fail and perform a full table scan. But you can use LIKE "name%".

Avoid implicit type conversion:

Type conversion occurs when the type of the column field is inconsistent with the type of the parameter passed in the where clause. It is recommended to determine the parameter type in the where first.

5. For federated indexes, follow the leftmost prefix rule.

For columns, the index contains the field id,name,school, either directly with the id field or in the same order as id,name, but name;school cannot use this index.

Therefore, when creating a federated index, we must pay attention to the order of the index fields, and put the commonly used query fields first.

Summarize the above suggestions:

1. Avoid calculating the index field.

2. Avoid using not on index fields! =

3. Avoid using is null and is not null on index fields

3. Avoid data type conversions on index fields

4. Avoid using functions on index fields

5. Avoid using null values in indexed columns

6. The sentence rules of pairing and WHERE

7. Try to avoid using in, not in or having in the WHERE clause. You can use exist and not exist instead of in and not in

8. Do not declare numbers in character format, do not declare character values in numeric format, otherwise it will invalidate the index

These are all the contents of the article "examples of SQL performance Optimization". 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.