关于NULL和NOT NULL性能实践

一直听说设计数据库的时候尽可能设置成NOT NULL，那么到底设置成NULL和NOT NULL有什么区别呢？
今天刚好有时间就探究一下：
主要测试的内容有：
1、测试NULL对索引的影响
2、测试NULL对组合索引的影响
3、测试NULL对磁盘空间的影响

测试环境为：
系统环境：Centos 5.5 64 bit
软件环境：MYSQL 5.1.50

表结构为:
CREATE TABLE `zhang` (
  `id` int(11) NOT NULL DEFAULT '0',
  `name` varchar(20) DEFAULT NULL,
  `address` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name_address` (`name`,`address`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

插入数据的存储过程为：
DROP PROCEDURE IF EXISTS add_data ;
DELIMITER //
CREATE PROCEDURE add_data (IN c_count INT,OUT result INT,OUT starttime DATETIME,OUT endtime DATETIME)
BEGIN
DECLARE c_name VARCHAR(20) DEFAULT 'zhang';
DECLARE c_address VARCHAR(20) DEFAULT 'jiangxi';
SELECT NOW() INTO starttime;
WHILE c_count <= 100000 DO
INSERT INTO zhang (id,`name`,`address`) VALUES(c_count,CONCAT(c_name,c_count),CONCAT(c_address,c_count));
SET c_count=c_count+1;
END WHILE;
SELECT NOW() INTO endtime;
SELECT MAX(id) INTO result FROM zhang;
END
//

调用存储过程插入数据：
CALL add_data(1,@result,@starttime,@endtime);

测死NULL对单个索引的影响：
NULL值很少的情况
ALTER TABLE zhang DROP INDEX idx_name_address,ADD INDEX (`name`);
UPDATE zhang SET `name`=NULL WHERE `name`='zhang99991' ;
EXPLAIN SELECT * FROM zhang WHERE `name`  IS NULL;
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
|  1 | SIMPLE      | zhang | ref  | name          | name | 63      | const |    1 | Using where |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
可以用到索引
EXPLAIN SELECT * FROM zhang WHERE `name`  IS NOT NULL;
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | zhang | ALL  | name          | NULL | NULL    | NULL | 100216 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
用不到索引
接下来看看含有很多NULL值的情况：
UPDATE zhang SET `name`=NULL LIMIT 60000 ;
EXPLAIN SELECT * FROM zhang WHERE `name` IS NULL;
+----+-------------+-------+------+---------------+------+---------+-------+-------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows  | Extra       |
+----+-------------+-------+------+---------------+------+---------+-------+-------+-------------+
|  1 | SIMPLE      | zhang | ref  | name          | name | 63      | const | 52420 | Using where |
+----+-------------+-------+------+---------------+------+---------+-------+-------+-------------+
能用到索引
EXPLAIN SELECT * FROM zhang WHERE `name` IS NOT NULL;
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | zhang | ALL  | name          | NULL | NULL    | NULL | 104841 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
不能用到索引


测试NULL对组合索引的影响：
UPDATE zhang SET `address`=NULL WHERE `name`='zhang99991' AND address='jiangxi99991';
EXPLAIN SELECT * FROM zhang WHERE `name`='zhang99991' AND `address` IS NULL;
+----+-------------+-------+------+------------------+------------------+---------+-------------+------+--------------------------+
| id | select_type | table | type | possible_keys    | key              | key_len | ref         | rows | Extra                    |
+----+-------------+-------+------+------------------+------------------+---------+-------------+------+--------------------------+
|  1 | SIMPLE      | zhang | ref  | idx_name_address | idx_name_address | 126     | const,const |    1 | Using where; Using index |
+----+-------------+-------+------+------------------+------------------+---------+-------------+------+--------------------------+
可以是用到索引
UPDATE zhang SET `name`=NULL WHERE `name`='zhang99992' AND address='jiangxi99992';
EXPLAIN SELECT * FROM zhang WHERE `name` IS NULL AND address='jiangxi99992';
+----+-------------+-------+------+------------------+------------------+---------+-------------+------+--------------------------+
| id | select_type | table | type | possible_keys    | key              | key_len | ref         | rows | Extra                    |
+----+-------------+-------+------+------------------+------------------+---------+-------------+------+--------------------------+
|  1 | SIMPLE      | zhang | ref  | idx_name_address | idx_name_address | 126     | const,const |    1 | Using where; Using index |
+----+-------------+-------+------+------------------+------------------+---------+-------------+------+--------------------------+
还是能用到索引
EXPLAIN SELECT * FROM zhang WHERE `name` IS NULL;
+----+-------------+-------+------+------------------+------------------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys    | key              | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+------------------+------------------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | zhang | ref  | idx_name_address | idx_name_address | 63      | const |    1 | Using where; Using index |
+----+-------------+-------+------+------------------+------------------+---------+-------+------+--------------------------+
还是能用到索引
UPDATE zhang SET address=NULL WHERE `name` IS NULL;
EXPLAIN SELECT * FROM zhang WHERE `name` IS NULL AND address IS NULL;
+----+-------------+-------+------+------------------+------------------+---------+-------------+------+--------------------------+
| id | select_type | table | type | possible_keys    | key              | key_len | ref         | rows | Extra                    |
+----+-------------+-------+------+------------------+------------------+---------+-------------+------+--------------------------+
|  1 | SIMPLE      | zhang | ref  | idx_name_address | idx_name_address | 126     | const,const |    1 | Using where; Using index |
+----+-------------+-------+------+------------------+------------------+---------+-------------+------+--------------------------+
还是能用到索引
再看一下join查询，没有NULL的使用索引情况
EXPLAIN SELECT * FROM zhang a INNER JOIN zhang2 b ON  a.`name`=b.`name`;        
+----+-------------+-------+------+------------------+------------------+---------+--------------+--------+--------------------------+
| id | select_type | table | type | possible_keys    | key              | key_len | ref          | rows   | Extra                    |
+----+-------------+-------+------+------------------+------------------+---------+--------------+--------+--------------------------+
|  1 | SIMPLE      | a     | ALL  | name             | NULL             | NULL    | NULL         | 100420 |                          |
|  1 | SIMPLE      | b     | ref  | idx_name_address | idx_name_address | 63      | zhang.a.name |      1 | Using where; Using index |
+----+-------------+-------+------+------------------+------------------+---------+--------------+--------+--------------------------+
添加NULL值以后对索引的使用情况
UPDATE zhang SET `name`=NULL LIMIT 50000;
UPDATE zhang2 SET `name`=NULL LIMIT 60000;
EXPLAIN SELECT * FROM zhang a INNER JOIN zhang2 b ON  a.`name`=b.`name`;
+----+-------------+-------+------+------------------+------------------+---------+--------------+-------+--------------------------+
| id | select_type | table | type | possible_keys    | key              | key_len | ref          | rows  | Extra                    |
+----+-------------+-------+------+------------------+------------------+---------+--------------+-------+--------------------------+
|  1 | SIMPLE      | a     | ALL  | name             | NULL             | NULL    | NULL         | 99844 |                          |
|  1 | SIMPLE      | b     | ref  | idx_name_address | idx_name_address | 63      | zhang.a.name |     1 | Using where; Using index |
+----+-------------+-------+------+------------------+------------------+---------+--------------+-------+--------------------------+


通过分别对单个索引和组合索引的测试得出结论NULL对组合索引没有什么太大影响

测试NULL对磁盘空间的影响：
没有NULL的情况
-rw-rw---- 1 mysql mysql 16777216 Mar 28 18:28 zhang.ibd
UPDATE zhang SET address=NULL LIMIT 50000;
将address列一半的值更改成NULL以后，占用磁盘空间的情况
-rw-rw---- 1 mysql mysql 18874368 Mar 28 18:31 zhang.ibd
发现占用的磁盘空间增长了不少。得出结论NULL
去掉索引再测试
UPDATE zhang SET address='jiangxi2' WHERE address IS NULL;
ALTER TABLE zhang DROP INDEX idx_name_address;
-rw-rw---- 1 mysql mysql 12582912 Mar 28 18:33 zhang.ibd
UPDATE zhang SET address=NULL LIMIT 50000;再查看磁盘空间的使用情况：
-rw-rw---- 1 mysql mysql 12582912 Mar 28 18:33 zhang.ibd
发现磁盘空间没有变化，因此综合两个测试可以得出结论：对NULL列加索引需要额外的磁盘空间，如果不是索引列就对磁盘空间没有影响