Why MySQL database avoids NULL as much as possible

Many tables in MySQL contain columns that can be NULL (empty values), even if the application does not need to save NULL, because NULL is the default attribute of the column. But we often see opinions in some MySQL performance optimization books or blogs: try not to use NULL values ​​in data columns, use 0, -1 or other special identifiers to replace NULL values, unless you really need to store NULL values, so why? What would be the benefit if replaced? What are the problems at the same time? Then look at the following:

(1) If the query contains columns that can be NULL, it is more difficult for MySQL to optimize because columns that can be NULL make indexes, index statistics, and value comparisons more complicated.

(2) Composite indexes containing NULL are invalid.

(3) Columns that can be NULL will use more storage space and also require special processing in MySQL.

(4) When a NULL-capable column is indexed, each index record requires an extra byte, which may even cause a fixed-size index (for example, an index on only one integer column) to become a variable-size index in MyISAM.

Reasons

Reason 1 does not require evidence

First, create a new environment. The sql statement is as follows

create table nulltesttable(
id int primary key,
name_not_null varchar(10) not null,
name_null varchar(10)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
alter table nulltesttable add index idx_nulltesttable_name_not_null(name_not_null);
alter table nulltesttable add index idx_nulltesttable_name_null(name_null);

explain select * from nulltesttable where name_not_null='name'; // explain1
explain select * from nulltesttable where name_null='name'; // explain2

From the SQL execution, we can see that key_len = 32 in explain1 and key_len = 33 in explain2
The origin of 32 in explain1 is: 10 (field length) * 3 (length occupied by utf8 character encoding) + 2 (length occupied by varchar as variable length)
Explain2's 32 comes from: 10 (field length) * 3 (length occupied by utf8 character encoding) + 2 (length occupied by varchar as variable length) + 1 (length occupied by null marker)

Concatenates two strings. If the string contains a null value, the result is returned as null.

insert into nulltesttable(id,name_not_null,name_null) values(1,'one',null);
insert into nulltesttable(id,name_not_null,name_null) values(2,'two','three');
select concat(name_not_null,name_null) from nulltesttable where id = 1; -- out: null
select concat(name_not_null,name_null) from nulltesttable where id = 2; -- out: twothree

If a field allows null values ​​and is indexed, the following query may return incorrect results.

select * from nulltesttable where name_null <> 'three' -- out: null
select count(name_null) from nulltesttable -- out: 1 

Generally, the performance improvement of changing a NULL-capable column to NOT NULL is small, so there is no need to first find and modify this situation in an existing schema unless you are sure that this will cause problems. However, if you plan to create an index on a column, you should try to avoid designing it to be NULL-able.

Don't be afraid to use NULL when you really need to identify an unknown value. In some cases, using NULL might be a better choice than using a magic constant. Choosing an impossible value from a particular type's value range, such as using -1 to represent an unknown number, can make the code much more complicated and easily introduce bugs, and can also make things a mess (Note: Mysql stores NULL values ​​in the index, but Oracle does not).

Of course there are exceptions. InnoDB uses a separate bit to store NULL values, so it has good space efficiency for sparse data (many values ​​are NULL, and only a few rows have non-NULL values ​​in the column). This does not apply to MyISAM.

Therefore, any design and consideration should pay attention to actual needs.

This concludes this article on why MySQL databases try to avoid NULL. For more information about MySQL avoiding NULL, please search for previous articles on 123WORDPRESS.COM or continue browsing the following related articles. I hope you will support 123WORDPRESS.COM in the future!