In-depth explanation of the impact of NULL on indexes in MySQL

Preface

I have read many blogs and heard many people, including our company's DBA, say that in MySql if a column contains null, then the index containing that column is invalid.

I flipped through "High Performance MySQL 2nd Edition" and "MySQL Technology Insider - InnoDB Storage Engine 2nd Edition", but didn't see any introduction to this. But after trying it locally, I found that the null column can use the index, whether it is a single-column index or a joint index, but it is limited to is null. Is not null does not use the index.

Later, I found the description in the official documentation that if a column contains null, the index can indeed be used. The address is: https://dev.mysql.com/doc/refman/5.7/en/is-null-optimization.html.

It works in both MySQL 5.6 and 5.7, and the storage engine is InnoDB.

The data is as follows:

1. Single column index

Create a single column index for the name column:

Query the rows where name is null:

Query the row whose name is 'test0' or null:

You can find that indexes can be used.

2. Joint Index

Add a joint index to age and name:

Query the rows where age is 14 and name is null:

You can find that the index is also used.

3. Others

Although MySQL can use indexes on columns containing nulls, it does not mean that nulls are the same as other data in the index.

It is not recommended to allow nulls on columns. It is best to restrict not null and set a default value, such as 0 and '' empty string. If it is a datetime type, you can set it to a special value such as '1970-01-01 00:00:00'.

For MySQL, null is a special value. Conceptually, NULL means "a missing unknown value" and it is treated somewhat differently from other values. For example: operators such as =, <, > cannot be used, the result of arithmetic operations on null is null, null rows are not included in count, null requires more storage space than an empty string, etc.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM.