SQL performance optimization

Foreword: Today I will introduce to you a more important issue, SQL performance optimization.

How to make SQL statements more efficient when operating the database is a very important issue. Below I will summarize the performance optimization issues for you.

SQL performance optimization

1. The SELECT statement must specify the field name

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

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

2. The value contained in IN in the SQL statement should not be too many

MySQL has made corresponding optimizations for IN, that is, all the constants in IN are stored in an array, and this array is In order.

But if the value is large, the consumption will be relatively large. For continuous values, do not use in if you can use between; or use connection instead.

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

select * from 表A 
where id in (select id from 表B)

is equivalent to

select * from 表A 
where exists(select * from 表B where 表B.id=表A.id)

The distinction between in and exists is mainly caused by the driver The order changes (this is the key to performance changes). If it is exists, then the outer table is the driving table and is accessed first. If it is IN, then the subquery is executed first.

So IN is suitable for situations where the outer surface is large and the inner table is small; EXISTS is suitable for situations where the outer surface is small but the inner table is large.

4. It is not recommended to use % prefix fuzzy query

For example, LIKE “%name” or LIKE “%name%”, this kind of query will cause the index to fail. Full table scan. But LIKE "name%" can be used.

Avoid implicit type conversion:

The type conversion that occurs when the type of the column field in the where clause is inconsistent with the type of the parameter passed in, it is recommended to determine it first Parameter type in where

5. For joint indexes, the leftmost prefix rule must be followed

For example, the index contains fields id, name, school, You can use the id field directly, or in the order of id, name, but name; school cannot use this index.

So when creating a joint index, you must pay attention to the order of the index fields, and put the commonly used query fields first

To summarize the above suggestions:

1. Avoid calculation operations on index fields

2. Avoid using not <> !=

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

3. Avoid data type conversion on index fields

4. Avoid using functions on index fields

5. Avoid using null values ​​in indexed columns

6. Statement rules for WHERE

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

8. Do not declare numbers in character format. Do not declare character values ​​in numeric format, otherwise the index will be invalid.

The above are some problems summarized for everyone. For more questions, please visit the corresponding tutorials on the PHP Chinese website ：https://www.php.cn/course/list/51/type/2.html

The above is the detailed content of SQL performance optimization. For more information, please follow other related articles on the PHP Chinese website!