关于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列加索引需要额外的磁盘空间，如果不是索引列就对磁盘空间没有影响