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

This article mainly explains "whether NULL in MYSQL is indexed or not". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Next, let the editor take you to learn "whether NULL in MYSQL is indexed or not".

First of all, standardize something.

1 what is the format of the data table (DYNAMIC)

2 the characteristics of this format and how NULL handles it in such a storage format

The storage format of lines like Dynamic has some features.

1 all strings with values greater than 4 are dynamic

2 there is a bitmap before each row, and the bitmap indicates which columns contain empty strings or zeros. If the string column is zero in length after the trailing space is deleted, or if the value of the numeric column is zero, mark it in the bitmap instead of saving it to disk. The non-empty string is saved as length bytes plus the contents of the string.

3 empty columns require extra space in the row to record whether their values are empty.

To sum up from the above, NULL needs extra space to save it, and NULL wastes more space than the default value of''or 0'in your field.

In MYSQL5.7, NULL can be used to represent NULL, but it is abandoned in MYSQL 8, and NULL is represented by NULL.

Here is a question about where the basis for not being empty in the design of the table comes from when it comes to the bad NULL. We can explain it according to a paragraph in the official document.

There are a few sentences, do not use NULL, you can

1 better use of INDEX

2. Save space.

3 accelerate the operation of SQL

OK, let's do an experiment to see how to prove whether it is possible to use NULL or not.

From the above figure, the query null value can be indexed. Then let's just write it. We need is not null.

It does seem that is not null can't go to the index, but is it really the case, is it a full table scan caused by quantity, or is it a problem caused by is not null?

The following experiments show that is not null can also go to the index, and is not null can not go to the index and the usual situation is the same, mainly depends on the cost cost calculation.

If is null or is null is used in the OR condition, it can be indexed from the figure below.

According to the principle of the index, the value of the secondary index can be NULL, and the column value of the index is NULL. For the secondary index record, where the NULL value of the B+ tree exists, it is generally the leftmost part of the B+ tree.

We continue to test, and if we combine some of the conditions with is not null to query, we will also follow the relevant index from the following figure.

So the above experiments prove that both is null and is not null can go to the relevant index, either the cost of the relevant INDEX is higher than that of the full table scan, or the index may fail, or there is something wrong with the statistical analysis.

At this point, I believe you have a deeper understanding of "whether the NULL in MYSQL is indexed or not". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.