mysql数据5.6.0和5.1.7的null字段索引测试
如果保函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>
本文出自 “心愿” 博客，请务必保留此出处http://xinyuan8.blog.51cto.com/677906/1632282
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
