Does null affect MySQL performance by ' Optimizing military ' or ' TCM theory '? Talk with the test data and play Golang

This is a creation in Article, where the information may have evolved or changed.

Some things about the recent company spec database, the old program told me that NULL is not allowed in MySQL, that columns with null values cannot be indexed and have a significant impact on performance. The first time I heard of this, I am doubtful that the previous null and index did not feel wrong, it may be the amount of data small bar. So I went to Google a meal, but did not find the authority of the statement.

v2ex--The legend that NULL index does not take effect is true?

by

Shiny:
Due to the complexity of the database, baseless assertion space is very large, fast catching up with TCM regimen. Avoid using NULL for reasons that are mentioned in high-performance MySQL for a while. We suggest that you read more books and watch less artifice on the Internet. The book has been deliberately turned out to extract the following for reference:
To avoid NULL as much as possible
To do so, define the field as not NULL. Even if the application does not need to save NULL (no value), there are many tables that contain nullable columns (Nullable column), just because it is the default option. Unless you really want to save NULL, the column is defined as NOT NULL.
MySQL makes it difficult to optimize queries that reference nullable columns, which make indexes, index statistics, and values more complex. Empty columns require more storage space, and special processing is required within MySQL. When a nullable column is indexed, an extra byte is required for each record, which can also cause a fixed-size index in MyISAM (for example, an index on an integer column) to become a variable-size index.
Even if you want to store a field with "No value" in the table, you might not use NULL. Consider using 0, a special value, or an empty string to replace it.
Changing the null column to NOT NULL brings a small performance boost, so do not treat it as a priority optimization unless you are sure it introduces a problem. Then, if you plan to index the column, you should try to avoid setting it to nullable.

Stackoverflow--null in MySQL (Performance & Storage)

by Arian Acosta:
Advantage of using NULLS over Empty Strings or zeros:
1 NULL requires 1 byte
1 Empty String requires 1 byte (assuming VARCHAR)
1 Zero requires 4 bytes (assuming INT)
You start to see the savings here:
8 NULLs require 1 byte
8 Empty Strings require 8 bytes
8 Zeros Require bytes
On the other hand, I suggest using NULLs over empty strings or zeros, because they ' re more organized, portable, and Requir e less space. To improve performance and save space, focus on using the proper data types, indexes, and queries instead of weird tricks.

Chinese and English users are arguing about this, in the group asked the old-timers, senior said MySQL this thing is very iffy, or write performance test is the most reliable, say well start to write it.

Test environment

• MySQL 5.7
• Golang 1.9.2
• Mac OS 10.13.1

Test ideas

• Build two similar tables, have id,name,number three columns, give name index, two table difference is a allow null one not_null
• Randomly generate a number of data, including some name is empty data
• Insert the same data into two tables, the difference between null is a null one with '
• Test select * FROM table WHERE NAME =? The performance

Test code

Https://github.com/win5do/pla ...

Go to the directory where the files run: Go test-v-run=none-bench=. -benchmem

Test results

mock100w result of the data, NULL has no effect at all

The storage footprint is the same, and null occupies space ' also occupies space

Write test code hit the pit

Why use Golang, because recently in learning Golang, by writing code to deepen the understanding of the language. And Golang comes with test tools, it is very convenient to do performance testing.

The first pit, previously useless Golang connection database, write create statement always prompt syntax error, carefully inspected several times, no error AH. Later turned over the document found that the connection was added with the multistatements=true parameter to use multiple lines of SQL.

The second pit is the INSERT statement stitching too long will cause the write pipe broken,mock50000 line is no problem, but 100000 lines of error, the solution is to block insert, 10000 lines plug.

The third pit is the one that you want to close after query.

Conclusion

In the current stable version of mysql5.7, NULL has no effect on the index (lazy, 5.5 did not test, do not want to go to the loading and unloading MySQL, there are empty fill up). Do not kill a stick of NULL, the use of the use, the semantics of NULL is very good, with ORM use basic painless. such as the time column, do not use NULL for a 0000-00-00 don't get too sore.

The old saying goes:

Premature optimization is the source of all evil.

The optimization of the theory of Chinese medicine alone is hopeless

More importantly, writing code with Golang is really cool.