SQL is a language that a programmer is exposed to very much, but many times, we will find that some SQL execution efficiency is unusually poor, causing the burden of the database. By analyzing these SQL problems, we can find many problems that we usually ignore when writing SQL.

Today, we are going to talk about these bad habits that need to be broken.

Use negative conditional queries as little as possible

Suppose we have an Order table with a field called Status that has four values: 0= to be paid, 1= to be shipped, 2= to be received, and 3= done.

At this point, we want to query all orders that have been paid, and many people will write SQL like this:

select * from Order where Status ! = 0Copy the code

This is a bad habit. Negative conditional queries (e.g.! =, not in, not exists) cannot be indexed, and the efficiency of the query deteriorates dramatically when the data in the Order table reaches a certain level.

So, the correct way to write it is:

Select * from Order where Status in (1,2,3)Copy the code

Minimize the use of leading fuzzy queries

Suppose we want to query the order of the user based on OrderNo. If we want to query directly with SQL, try not to use the leading fuzzy query, i.e.

select * from Order where OrderNo like '%param'Copy the code

or

select * from Order where OrderNo like '%param%'Copy the code

Because, the leading fuzzy query is unable to hit the index, so, will be the whole database to search, the efficiency is quite poor, and the non-leading fuzzy query is able to use the index.

Therefore, we try not to put wildcards in front of each other, but instead look like this:

select * from Order where OrderNo like 'param%'Copy the code

Try not to perform operations on conditional fields

If we have a request to query the order data for the whole year of 2018, we need to use CreateTime to retrieve the order data. However, some programmers prefer to write SQL like this:

select * from Order where Year(CreateTime)=2018Copy the code

However, each time it is executed, the query is unusually slow, causing a large number of requests to hang or even time out. This is because even though we created the index on CreateTime, if we used an operator function, the query would still perform a full table retrieval.

So, we can change it to this:

select * from Order where CreateTime > '2018-1-1 00:00:00'Copy the code

Special care is required when querying for columns that allow Null values

When creating a table field, try not to allow Null if the field needs to be used as an index. Because single-column indexes do not store Null values, and compound indexes do not store values where all index columns are Null, null-allowed columns may result in an “unexpected” result set.

For example, we have a User table with a UserName field that records the User’s name and adds an index.

Now we execute a query like this:

select * from User where UserName ! = 'Xiaoqian'Copy the code

But here’s the result

The data whose UserName is Null is not included. Therefore, if we want to include this user, it is best to set a default value.

Compound indexes that are used in order

Login is definitely one of the most used queries, and for efficiency, we’ve added a composite index for LoginID and Password.

When we use

select * from User where LoginID = '{LoginID}' and Password = '{Password}'select * from User where Password = '{Password}' and LoginID = '{LoginID}'Copy the code

When querying, it is the hit index that can be prepared. When we use:

select * from User where LoginID = '{LoginID}' Copy the code

When querying, it can also hit the index. But when we use

select * from User where Password = '{Password}' Copy the code

An index cannot be hit during a query. Why?

This is because the composite index is very sensitive to the order of the query, so the conformity index contains several rules, including full column matching and left-most prefix matching.

When all columns match, the order of the query is different, but the query optimizer adjusts the order to fit the index, so reversing the order is fine.

However, if all columns do not match, the left-most prefix must match, that is, the order must be sorted from left to right. So if we create index <LoginID, Password>, where Password = ‘{Password}’ does not meet the left-most prefix rule, can not match the index.

When the result is unique, don’t be bored

Normally, we do not design the User table with LoginID as the primary key, but LoginID does verify uniqueness in the business logic, so if we use

select * from User where LoginID = '{LoginID}'Copy the code

There must be only one result when you query. However, the database does not know, and even if it finds that only one result, it will continue until it has scanned all the data.

Therefore, when executing such a query, we can optimize it to:

select * from User where LoginID = '{LoginID}' limit 1Copy the code

This way, when the query reaches the result, it will not continue.

Finally, all of the above examples are pits

Use Select * as little or as little as possible. Our queries have a purpose, just like logging in. All we need to know is that the result will return.

Original: https://www.toutiao.com/i6606857619468452355/