The database is the development process must have, it is because the utilization rate of the database is 100%, so there will be a variety of database optimization, separation and so on a series of problems.

Today, I will introduce to you with their years of experience, right when to cast a brick to attract jade, please do not like spray, comments or can pay attention to me better

Negative queries cannot use indexes

Select name from user where id not in (1,3,4);

It should be changed to:

Select name from user where id in (2,5,6);

Leading fuzzy queries cannot use indexes

Such as:

select name from user where name like ‘%zhangsan’

Non-previous guidelines may:

select name from user where name like ‘zhangsan%’

It is recommended to consider using full-text indexing tools such as Lucene instead of frequent fuzzy queries.

You are not advised to create indexes if data differentiation is not obvious

For example, you are advised to create indexes only for gender fields in the User table, such as id cards, that can be clearly distinguished.

The default value of the field is not NULL

This can result in query results that are inconsistent with expectations.

An index cannot be hit by a calculation on a field

select name from user where FROM_UNIXTIME(create_time) < CURDATE();

It should be changed to:

select name from user where create_time < FROM_UNIXTIME(CURDATE());

Left-most prefix problem

Select * from user where username PWD = 1; select * from user where PWD = 1;

But the use of

select username from user where pwd =’axsedf1sd’

The index cannot be hit.

Returns if it is clear that there is only one record

select name from user where username=’zhangsan’ limit 1

Can improve efficiency, can stop the database cursor movement.

Don’t let the database do the casting for us

select name from user where telno=18722222222

In this case, data can be detected, but full table scan is performed.

Need to change to

select name from user where telno=’18722222222′

If the fields to be joined must be of the same type as those in the two tables

Otherwise, the index won’t hit.