|
|
51CTO旗下网站
|
|
移动端

MySQL 一千个不用 Null 的理由

下面咱们要聊的是 MySQL 里的 null,在大量的 MySQL 优化文章和书籍里都提到了字段尽可能用NOT NULL,而不是NULL,除非特殊情况。但却都只给结论不说明原因,犹如鸡汤不给勺子一样,让不少初学者对这个结论半信半疑或者云里雾里。本文今天就详细的剖析下使用 Null 的原因,并给出一些不用 Null 的理由。

作者:佚名来源:程序师|2018-02-06 08:32

港真,Null 貌似在哪里都是个头疼的问题,比如 Java 里让人头疼的 NullPointerException,为了避免猝不及防的空指针异常,千百年来程序猿们不得不在代码里小心翼翼的各种 if 判断,麻烦而又臃肿,为此 java8 引入了 Optional 来避免这一问题。

下面咱们要聊的是 MySQL 里的 null,在大量的 MySQL 优化文章和书籍里都提到了字段尽可能用NOT NULL,而不是NULL,除非特殊情况。但却都只给结论不说明原因,犹如鸡汤不给勺子一样,让不少初学者对这个结论半信半疑或者云里雾里。本文今天就详细的剖析下使用 Null 的原因,并给出一些不用 Null 的理由。

1、NULL 为什么这么多人用?

NULL是创建数据表时默认的,初级或不知情的或怕麻烦的程序员不会注意这点。

很多人员都以为not null 需要更多空间,其实这不是重点。

重点是很多程序员觉得NULL在开发中不用去判断插入数据,写sql语句的时候更方便快捷。

2、是不是以讹传讹?

MySQL 官网文档:

  • NULL columns require additional space in the rowto record whether their values are NULL. For MyISAM tables, each NULL columntakes one bit extra, rounded up to the nearest byte.

Mysql难以优化引用可空列查询,它会使索引、索引统计和值更加复杂。可空列需要更多的存储空间,还需要mysql内部进行特殊处理。可空列被索引后,每条记录都需要一个额外的字节,还能导致MYisam 中固定大小的索引变成可变大小的索引。

—— 出自《高性能mysql第二版》

照此分析,还真不是以讹传讹,这是有理论依据和出处的。

3、给我一个不用 Null 的理由?

  • (1)所有使用NULL值的情况,都可以通过一个有意义的值的表示,这样有利于代码的可读性和可维护性,并能从约束上增强业务数据的规范性。
  • (2)NULL值到非NULL的更新无法做到原地更新,更容易发生索引分裂,从而影响性能。

注意:但把NULL列改为NOT NULL带来的性能提示很小,除非确定它带来了问题,否则不要把它当成优先的优化措施,最重要的是使用的列的类型的适当性。

  • (3)NULL值在timestamp类型下容易出问题,特别是没有启用参数explicit_defaults_for_timestamp
  • (4)NOT IN、!= 等负向条件查询在有 NULL 值的情况下返回永远为空结果,查询容易出错
  1. create table table_2 ( 
  2.      `id` INT (11) NOT NULL
  3.     user_name varchar(20) NOT NULL 
  4.  
  5.  
  6. create table table_3 ( 
  7.      `id` INT (11) NOT NULL
  8.     user_name varchar(20) 
  9.  
  10. insert into table_2 values (4,"zhaoliu_2_1"),(2,"lisi_2_1"),(3,"wangmazi_2_1"),(1,"zhangsan_2"),(2,"lisi_2_2"),(4,"zhaoliu_2_2"),(3,"wangmazi_2_2"
  11.  
  12. insert into table_3 values (1,"zhaoliu_2_1"),(2, null
  13.  
  14. -- 1、NOT IN子查询在有NULL值的情况下返回永远为空结果,查询容易出错 
  15. select user_name from table_2 where user_name not in (select user_name from table_3 where id!=1) 
  16.  
  17. mysql root@10.48.186.32:t_test_zz5431> select user_name from table_2 where user_name not 
  18.                                     -> in (select user_name from table_3 where id!=1); 
  19. +-------------+ 
  20. | user_name   | 
  21. |-------------| 
  22. +-------------+ 
  23. rows in set 
  24. Time: 0.008s 
  25. mysql root@10.48.186.32:t_test_zz5431> 
  26.  
  27. -- 2、单列索引不存null值,复合索引不存全为null的值,如果列允许为null,可能会得到“不符合预期”的结果集 
  28. -- 如果name允许为null,索引不存储null值,结果集中不会包含这些记录。所以,请使用not null约束以及默认值。 
  29. select * from table_3 where name != 'zhaoliu_2_1' 
  30.  
  31. -- 3、如果在两个字段进行拼接:比如题号+分数,首先要各字段进行非null判断,否则只要任意一个字段为空都会造成拼接的结果为null。 
  32. select CONCAT("1",nullfrom dual; -- 执行结果为null。 
  33.  
  34. -- 4、如果有 Null column 存在的情况下,count(Null column)需要格外注意,null 值不会参与统计。 
  35. mysql root@10.48.186.32:t_test_zz5431> select * from table_3; 
  36. +------+-------------+ 
  37. |   id | user_name   | 
  38. |------+-------------| 
  39. |    1 | zhaoliu_2_1 | 
  40. |    2 | <null>      | 
  41. |   21 | zhaoliu_2_1 | 
  42. |   22 | <null>      | 
  43. +------+-------------+ 
  44. rows in set 
  45. Time: 0.007s 
  46. mysql root@10.48.186.32:t_test_zz5431> select count(user_name) from table_3; 
  47. +--------------------+ 
  48. |   count(user_name) | 
  49. |--------------------| 
  50. |                  2 | 
  51. +--------------------+ 
  52. 1 row in set 
  53. Time: 0.007s 
  54.  
  55. -- 5、注意 Null 字段的判断方式, = null 将会得到错误的结果。 
  56. mysql root@localhost:cygwin> create index IDX_test on table_3 (user_name); 
  57. Query OK, 0 rows affected 
  58. Time: 0.040s 
  59. mysql root@localhost:cygwin>  select * from table_3 where user_name is null\G 
  60. ***************************[ 1. row ]*************************** 
  61. id        | 2 
  62. user_name | None 
  63.  
  64. 1 row in set 
  65. Time: 0.002s 
  66. mysql root@localhost:cygwin> select * from table_3 where user_name = null\G 
  67.  
  68. rows in set 
  69. Time: 0.002s 
  70. mysql root@localhost:cygwin> desc select * from table_3 where user_name = 'zhaoliu_2_1'\G 
  71. ***************************[ 1. row ]*************************** 
  72. id            | 1 
  73. select_type   | SIMPLE 
  74. table         | table_3 
  75. type          | ref 
  76. possible_keys | IDX_test 
  77. key           | IDX_test 
  78. key_len       | 23 
  79. ref           | const 
  80. rows          | 1 
  81. Extra         | Using where 
  82.  
  83. 1 row in set 
  84. Time: 0.006s 
  85. mysql root@localhost:cygwin> desc select * from table_3 where user_name = null\G 
  86. ***************************[ 1. row ]*************************** 
  87. id            | 1 
  88. select_type   | SIMPLE 
  89. table         | None 
  90. type          | None 
  91. possible_keys | None 
  92. key           | None 
  93. key_len       | None 
  94. ref           | None 
  95. rows          | None 
  96. Extra         | Impossible WHERE noticed after reading const tables 
  97.  
  98. 1 row in set 
  99. Time: 0.002s 
  100. mysql root@localhost:cygwin> desc select * from table_3 where user_name is null\G 
  101. ***************************[ 1. row ]*************************** 
  102. id            | 1 
  103. select_type   | SIMPLE 
  104. table         | table_3 
  105. type          | ref 
  106. possible_keys | IDX_test 
  107. key           | IDX_test 
  108. key_len       | 23 
  109. ref           | const 
  110. rows          | 1 
  111. Extra         | Using where 
  112.  
  113. 1 row in set 
  114. Time: 0.002s 
  115. mysql root@localhost:cygwin> 

(5)Null 列需要更多的存储空间:需要一个额外字节作为判断是否为 NULL 的标志位

  1. alter table table_3 add index idx_user_name (user_name); 
  2. alter table table_2 add index idx_user_name (user_name); 
  3. explain select * from table_2 where user_name='zhaoliu_2_1'
  4. explain select * from table_3 where user_name='zhaoliu_2_1'
图2:MySQL 一千个不用 Null 的理由

可以看到同样的 varchar(20) 长度,table_2 要比 table_3 索引长度大,这是因为:

  • 两张表的字符集不一样,且字段一个为 NULL 一个非 NULL。
图3:MySQL 一千个不用 Null 的理由

key_len 的计算规则和三个因素有关:数据类型、字符编码、是否为 NULL

key_len 62 == 20*3(utf8 3字节) + 2 (存储 varchar 变长字符长度 2字节,定长字段无需额外的字节)

key_len 83 == 20*4(utf8mb4 4字节) + 1 (是否为 Null 的标识) + 2 (存储 varchar 变长字符长度 2字节,定长字段无需额外的字节)

所以说索引字段最好不要为NULL,因为NULL会使索引、索引统计和值更加复杂,并且需要额外一个字节的存储空间。基于以上这些理由和原因,我想咱们不用 Null 的理由应该是够了 🙂

【编辑推荐】

  1. 利用ProxySQL实现MySQL的读写分离
  2. Oracle发布关于MySQL服务的重大安全漏洞说明
  3. 打通MySQL架构和业务的任督二脉,做个DBA高手!
  4. MySQL数据库开发常见问题及优化
  5. 这套方法论,彻底终结MySQL同步延迟问题!
【责任编辑:未丽燕 TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢

读 书 +更多

Visual C# 2005技术内幕

本书提供了.NET框架下C#编程的详尽指南。书中详细介绍了.NET框架中的核心概念、使用GDI+编写高级用户界面、多线程程序设计、使用ClickOnc...

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊