Shulou(Shulou.com)05/31 Report--

This article mainly introduces "MySQL null value storage and null performance impact". In daily operation, I believe many people have doubts about MySQL null value storage and null performance impact. Xiaobian consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts about "MySQL null value storage and null performance impact". Next, please follow the editor to study!

Storage of MySQL null value

Mysql > create table mytest (T1 varchar (10), T2 varchar (10), T3 varchar (10), T4 varchar (10)) engine=innodb charset = latin1 row_format=compact;Query OK, 0 rows affected (0.08 sec) mysql > insert into mytest values Query OK, 1 row affected (0.01 sec) mysql > insert into mytest values; Query OK, 1 row affected (0.00 sec)

After preparing the test data, execute the shell command:

/ usr/local/mysql/test# hexdump-C-v mytest.ibd > / tmp/mytest.txt0000c070 73 75 70 72 65 6d 75 6d 03 02 02 01 0000 00 10 | supremum. |-> one line 16-byte 0000c080 00 25 0000 00 03 b9 0000 0000 02 49 01 82 |.% I... | 0000c090 00 01 4a 01 10 61 62 62 62 63 63 63 03 02 | J. abbbbccc. | 0000c0a0 01 0000 00 18 00 23 0000 00 03 b9 01 0000 00 |. | 0000c0b0 49 02 83 0000 01 4b 01 10 61 65 65 66 | .I.. K..aeeeef | 0000c0c0 66 66 03 01 06 0000 20 ff a6 0000 00 03 b9 02 | ff. | | 0000c0d0 0000 00 02 49 03 84 0000 01 4c 01 10 61 66 66 |.... I.L..aff | 0000c0e0 66 0000 0000 0000 00 | f. | explanation: the first line of data: 03 02 02 01 / * variable length field * /-the four fields in the table are of type varchar, and there is no NULL data, and each field is less than 255. 00 / * NULL mark bit The first row has no null data * / 00 00 10 00 25 / * record header information, fixed 5 bytes * / 00 00 00 03 b9 00 / * RowID, fixed 6 bytes, table has no primary key * / 00 00 02 49 01 / * transaction ID, fixed 6 bytes * / 82 00 00 01 4a 01 10 / * rollback pointer Fixed 7 bytes * / 61 62 62 62 63 63 / * column data * / the second row of data is the same as the first row of data (color matching). The third row of data (with null values) corresponds to the color interpreted in the first row: 03 02 02 01 VS 03 01-when the value is NULL, the variable length field list does not take up storage space. 61 62 62 62 63 63 63 VS 61 66 66 66-NULL values are not stored and do not occupy space conclusion: when the value is NULL, the variable length field list does not take up storage space. The NULL value is not stored and does not take up space, but requires a flag bit (one per line). Conclusion: the column data information shows that NULL data and''data do not take up any space. For the information of variable length field list, it is concluded that' 'although the data does not need any storage space, it still needs to occupy one byte in the variable length field list, and the null value does not need to be occupied. "it's just that NULL will have an extra flag bit. So there is an optimization saying: "if you can set NOT NULL in a database table, set it to NOT NULL as far as possible, unless you really need NULL to be worth it."

2. The influence of MySQL null performance

(1)。 Null values are not stored in B-tree indexing, so if the field of the index can be NULL, the efficiency of the index will be much lower.

(2)。 The judgment or filtering of null values is prone to confusion in the business.

At this point, the study on "MySQL null value storage and null performance impact" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.