mysql数据5.6.0和5.1.7的null字段索引测试

jyhrte

如果保函null字段是数字 is null 和is not null都不走索引

如果保函null字段是字符 is null不走索引 和is not null会走索引
字符类型可以默认'' 数字类型可以默认0



1、数据库为5.6.0版本测试


mysql> select @@version;
+------------+
| @@version  |
+------------+
| 5.6.10-log |
+------------+
1 row in set (0.00 sec)

mysql>

mysql>  create table test_innodb (id int(11) unsigned NOT NULL primary key AUTO_INCREMENT,name varchar(100)) engine=innodb;
Query OK, 0 rows affected (0.16 sec)

mysql> show create table test_innodb\G
*************************** 1. row ***************************
       Table: test_innodb
Create Table: CREATE TABLE `test_innodb` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> insert into test_innodb(name) values('hlf'),('huanglf');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> show create table test_innodb\G                        
*************************** 1. row ***************************
       Table: test_innodb
Create Table: CREATE TABLE `test_innodb` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> optimize table test_innodb;
+------------------+----------+----------+-------------------------------------------------------------------+
| Table            | Op       | Msg_type | Msg_text                                                          |
+------------------+----------+----------+-------------------------------------------------------------------+
| test.test_innodb | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.test_innodb | optimize | status   | OK                                                                |
+------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.30 sec)

mysql> show create table test_innodb\G
*************************** 1. row ***************************
       Table: test_innodb
Create Table: CREATE TABLE `test_innodb` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql>


#####################################




增加列并测试null字段索引


mysql> alter table test_INNODB ADD COLUMN numb int(8) default null;
Query OK, 0 rows affected (0.55 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
mysql>
mysql> alter table test_INNODB drop COLUMN numb ;
Query OK, 0 rows affected (0.55 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table test_innodb;
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                                                                                 |
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_innodb | CREATE TABLE `test_innodb` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 |
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table test_INNODB ADD COLUMN numb int(8) default null,add column addr varchar(100) default null;
Query OK, 0 rows affected (0.55 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table test_innodb;
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                                                                                                                                                   |
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_innodb | CREATE TABLE `test_innodb` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `numb` int(8) DEFAULT NULL,
  `addr` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 |
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

mysql> alter table test_innodb add index idx_numb(numb);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
mysql> alter table test_innodb add index idx_addr(addr);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table test_innodb;
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                                                                                                                                                                                                         |
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_innodb | CREATE TABLE `test_innodb` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `numb` int(8) DEFAULT NULL,
  `addr` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_numb` (`numb`),
  KEY `idx_addr` (`addr`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 |
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>


mysql> insert into test_innodb(name,numb,addr) values('huanglingfei',80,'chengdu'),('wanger'),('mazhi','guizhou'),('zhangsan',60);
ERROR 1136 (21S01): Column count doesn't match value count at row 2
mysql>
mysql> insert into test_innodb(name,numb,addr) values('huanglingfei',80,'chengdu');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql>
mysql>
mysql>
mysql> insert into test_innodb(name) values('wanger');         
Query OK, 1 row affected (0.00 sec)
mysql>




插入错误类型是出现警告并把插字符串自动替换为数字0

mysql> insert into test_innodb(name,numb) values('mazhi','guizhou'),('zhangsan',60);               
Query OK, 2 rows affected, 1 warning (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 1

mysql> select * from test_innodb;
+----+--------------+------+---------+
| id | name         | numb | addr    |
+----+--------------+------+---------+
|  1 | hlf          | NULL | NULL    |
|  2 | huanglf      | NULL | NULL    |
|  3 | huanglingfei |   80 | chengdu |
|  4 | wanger       | NULL | NULL    |
|  5 | mazhi        |    0 | NULL    |
|  6 | zhangsan     |   60 | NULL    |
+----+--------------+------+---------+
6 rows in set (0.00 sec)

mysql>


mysql> explain select * from test_innodb where id in(3,4,5);
+----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table       | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | test_innodb | range | PRIMARY       | PRIMARY | 4       | NULL |    3 | Using where |
+----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> select * from test_innodb where id in(3,4,5);
+----+--------------+------+---------+
| id | name         | numb | addr    |
+----+--------------+------+---------+
|  3 | huanglingfei |   80 | chengdu |
|  4 | wanger       | NULL | NULL    |
|  5 | mazhi        |    0 | NULL    |
+----+--------------+------+---------+
3 rows in set (0.00 sec)

mysql>



数字字段


mysql>
mysql> explain select * from test_innodb where numb=80;
+----+-------------+-------------+------+---------------+----------+---------+-------+------+-------+
| id | select_type | table       | type | possible_keys | key      | key_len | ref   | rows | Extra |
+----+-------------+-------------+------+---------------+----------+---------+-------+------+-------+
|  1 | SIMPLE      | test_innodb | ref  | idx_numb      | idx_numb | 5       | const |    1 | NULL  |
+----+-------------+-------------+------+---------------+----------+---------+-------+------+-------+
1 row in set (0.00 sec)

mysql>




mysql> explain select * from test_innodb where numb is not null;
+----+-------------+-------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table       | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | test_innodb | ALL  | idx_numb      | NULL | NULL    | NULL |    6 | Using where |
+----+-------------+-------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql>
mysql>
mysql> select * from test_innodb where numb is not null;        
+----+--------------+------+---------+
| id | name         | numb | addr    |
+----+--------------+------+---------+
|  3 | huanglingfei |   80 | chengdu |
|  5 | mazhi        |    0 | NULL    |
|  6 | zhangsan     |   60 | NULL    |
+----+--------------+------+---------+
3 rows in set (0.00 sec)

mysql>
mysql> explain select * from test_innodb where numb is null;   
+----+-------------+-------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table       | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | test_innodb | ALL  | idx_numb      | NULL | NULL    | NULL |    6 | Using where |
+----+-------------+-------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql>
mysql> select * from test_innodb where numb is null;        
+----+---------+------+------+
| id | name    | numb | addr |
+----+---------+------+------+
|  1 | hlf     | NULL | NULL |
|  2 | huanglf | NULL | NULL |
|  4 | wanger  | NULL | NULL |
+----+---------+------+------+
3 rows in set (0.00 sec)

mysql>





字符字段 is not null 居然走了索引


mysql> explain select * from test_innodb where  addr='chengdu';
+----+-------------+-------------+------+---------------+----------+---------+-------+------+-----------------------+
| id | select_type | table       | type | possible_keys | key      | key_len | ref   | rows | Extra                 |
+----+-------------+-------------+------+---------------+----------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | test_innodb | ref  | idx_addr      | idx_addr | 303     | const |    1 | Using index condition |
+----+-------------+-------------+------+---------------+----------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

mysql>
mysql> select * from test_innodb where  addr='chengdu';        
+----+--------------+------+---------+
| id | name         | numb | addr    |
+----+--------------+------+---------+
|  3 | huanglingfei |   80 | chengdu |
+----+--------------+------+---------+
1 row in set (0.00 sec)

mysql>


mysql> explain select * from test_innodb where  addr is null;   
+----+-------------+-------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table       | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | test_innodb | ALL  | idx_addr      | NULL | NULL    | NULL |    6 | Using where |
+----+-------------+-------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql>
mysql>
mysql>
mysql> select * from test_innodb where  addr is null;
+----+----------+------+------+
| id | name     | numb | addr |
+----+----------+------+------+
|  1 | hlf      | NULL | NULL |
|  2 | huanglf  | NULL | NULL |
|  4 | wanger   | NULL | NULL |
|  5 | mazhi    |    0 | NULL |
|  6 | zhangsan |   60 | NULL |
+----+----------+------+------+
5 rows in set (0.00 sec)

mysql>



mysql> explain select * from test_innodb where  addr is not null;
+----+-------------+-------------+-------+---------------+----------+---------+------+------+-----------------------+
| id | select_type | table       | type  | possible_keys | key      | key_len | ref  | rows | Extra                 |
+----+-------------+-------------+-------+---------------+----------+---------+------+------+-----------------------+
|  1 | SIMPLE      | test_innodb | range | idx_addr      | idx_addr | 303     | NULL |    1 | Using index condition |
+----+-------------+-------------+-------+---------------+----------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

mysql>
mysql>
mysql> select * from test_innodb where  addr is not null;        
+----+--------------+------+---------+
| id | name         | numb | addr    |
+----+--------------+------+---------+
|  3 | huanglingfei |   80 | chengdu |
+----+--------------+------+---------+
1 row in set (0.00 sec)

mysql>




2、数据库为5.1.7版本测试：


mysql> select @@version;
+------------+
| @@version  |
+------------+
| 5.1.73-log |
+------------+
1 row in set (0.00 sec)

mysql>

create table test_innodb (id int(11) unsigned NOT NULL primary key AUTO_INCREMENT,
name varchar(100),
numb int(8) default null,
addr varchar(100) default null,
key idx_numb (numb),
key idx_addr (addr)
) engine=innodb;

mysql> create table test_innodb (id int(11) unsigned NOT NULL primary key AUTO_INCREMENT,
    -> name varchar(100),
    -> numb int(8) default null,
    -> addr varchar(100) default null,
    -> key idx_numb (numb),
    -> key idx_addr (addr)
    -> ) engine=innodb;
Query OK, 0 rows affected (0.25 sec)

mysql>

insert into test_innodb(name) values('hlf'),('huanglf');
insert into test_innodb(name,numb,addr) values('huanglingfei',80,'chengdu');
insert into test_innodb(name) values('wanger');         
insert into test_innodb(name,numb) values('mazhi','guizhou'),('zhangsan',60);      


mysql> insert into test_innodb(name) values('hlf'),('huanglf');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into test_innodb(name,numb,addr) values('huanglingfei',80,'chengdu');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_innodb(name) values('wanger');         
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_innodb(name,numb) values('mazhi','guizhou'),('zhangsan',60);  
ERROR 1366 (HY000): Incorrect integer value: 'guizhou' for column 'numb' at row 1
mysql>

mysql> insert into test_innodb(name,numb) values('mazhi','0'),('zhangsan',60);         
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from test_innodb;                                               
+----+--------------+------+---------+
| id | name         | numb | addr    |
+----+--------------+------+---------+
|  1 | hlf          | NULL | NULL    |
|  2 | huanglf      | NULL | NULL    |
|  3 | huanglingfei |   80 | chengdu |
|  4 | wanger       | NULL | NULL    |
|  5 | mazhi        |    0 | NULL    |
|  6 | zhangsan     |   60 | NULL    |
+----+--------------+------+---------+
6 rows in set (0.00 sec)

mysql>


从结果看字符字段is not null 也是会走索引的

mysql> explain select * from test_innodb where numb=80;
+----+-------------+-------------+------+---------------+----------+---------+-------+------+-------------+
| id | select_type | table       | type | possible_keys | key      | key_len | ref   | rows | Extra       |
+----+-------------+-------------+------+---------------+----------+---------+-------+------+-------------+
|  1 | SIMPLE      | test_innodb | ref  | idx_numb      | idx_numb | 5       | const |    1 | Using where |
+----+-------------+-------------+------+---------------+----------+---------+-------+------+-------------+
1 row in set (0.00 sec)

mysql>
mysql> explain select * from test_innodb where numb is null;
+----+-------------+-------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table       | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | test_innodb | ALL  | idx_numb      | NULL | NULL    | NULL |    6 | Using where |
+----+-------------+-------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from test_innodb where numb is not null;
+----+-------------+-------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table       | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | test_innodb | ALL  | idx_numb      | NULL | NULL    | NULL |    6 | Using where |
+----+-------------+-------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql>
mysql> explain select * from test_innodb where addr is null;        
+----+-------------+-------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table       | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | test_innodb | ALL  | idx_addr      | NULL | NULL    | NULL |    6 | Using where |
+----+-------------+-------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql>
mysql>
mysql> explain select * from test_innodb where addr is not null;
+----+-------------+-------------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table       | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
+----+-------------+-------------+-------+---------------+----------+---------+------+------+-------------+
|  1 | SIMPLE      | test_innodb | range | idx_addr      | idx_addr | 303     | NULL |    1 | Using where |
+----+-------------+-------------+-------+---------------+----------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql>