Shulou(Shulou.com)06/01 Report--

Original: https://www.enmotech.com/web/detail/1/837/1.html

This time, we will explain two small knowledge points related to SQL query. Mastering these knowledge points can help you avoid stepping on the pit and improve the efficiency of the query.

1. The value of the allowed field is null, which often leads to disaster

First of all, prepare some data first, and then demonstrate.

Create table animal (

Id int

Name char (20)

Index (id)

) engine=innodb

Index (id) indicates that the field id is indexed, and both id and name are allowed to be null.

Then insert four pieces of data, and the id of the last piece of data is.

Insert into animal (id, name) values (1, 'cat')

Insert into animal (id, name) values (2, 'dog')

Insert into animal (id, name) values (3, 'pig')

Insert into animal (id, name) values (null, 'nameless animal')

The data in the table at this time is

At this point, we query the animals with id! = 1 in the table.

Select * from animal where id! = 1

The results are as follows:

At this time, we only found two rows of data, which should be three rows according to reason, but the line of id = null has not been matched. Maybe you have heard that null is not equal to any other value. If null! = 1 is reasonable, but the reality is very cruel, it just won't be matched.

Therefore, the value of the field is not allowed to be null, otherwise the result may not match the expectation.

Anyway, I've stepped on this hole before. I don't know if anyone has stepped on it.

But what if someone sets the allowed value to null? If this is the case, for the lookup of! =, you can add an extra clause of or id is null (note that it is is null, not = null, because id = null will not match the row with the value null). That is,

Select * from animal where id! = 1 or id is null

The results are as follows:

two。 Replace or with union whenever possible

(1) just now we have indexed the field id. If we do the equivalent operation, we will usually use the index operation. If you don't believe it, you can see:

Explain select * from animal where id = 1

The results are as follows:

As you can see from the execution plan, the equivalent lookup on id can be indexed (as you might expect), where

Type = ref: indicates that the index is not unique

Rows = 1: predict scan one line

(2) can id is null go to the index? The answer is yes, as shown in the picture

Explain select * from animal where id is null

Among them

Type = ref: indicates that the index is not unique

Rows = 1: predict scan one line

(3) then the problem arises, if we want to find the animal with id = 1 or id = null, we may use the or statement to connect, that is,

Select * from animal where id = 1 or id is null

So will this statement go out of the index?

Whether you have taken the index or not, just look at the implementation plan, as shown in the picture.

Explain select * from animal where id = 1 or id is null

Where:

Ref = ALL: indicates full table scan

Rows = 4: predict scan 4 rows (while our entire table has only 4 rows of records)

As can be seen from the execution plan, it is very possible to use or not to walk the index, which will greatly reduce the rate of query, so it is generally not recommended to use the or clause to join conditions.

So how to solve it?

You can actually replace or with union, which is as follows:

Select * from animal where id = 1 union select * from animal where id is null.

At this point, we walk through the index twice, find out all the rows of id = 1 and all id = null, then use a temporary table to store the final results, and finally scan the temporary table.

Want to know more about databases and cloud technologies?

Come and follow the official account of "data and Cloud" and the official website of "Yunhe Enmo". We look forward to learning and making progress with you!

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.