mysql null 走索引_mysql 索引列为Null的走不走索引及null在统计时的问题

要尽可能地把字段定义为 NOT NULL，即使应用程序无须保存 NULL(没有值)，也有许多表包含了可空列(Nullable Column)

这仅仅是因为它为默认选项。除非真的要保存 NULL，否则就把列定义为 NOT NULL

MySQL难以优化引用了可空列的查询,它会使索引、索引统计和值更加复杂。

可空列需要更多的储存空间，还需要在MySQL内部进行特殊处理。当可空列被索引的时候，

每条记录都需要一个额外的字节，还可能导致 MyISAM 中固定大小的索引(例如一个整数列上的索引)变成可变大小的索引。

即使要在表中储存「没有值」的字段，还是有可能不使用 NULL 的，考虑使用 0、特殊值或空字符串来代替它。

把 NULL 列改为 NOT NULL 带来的性能提升很小，所以除非确定它引入了问题，否则就不要把它当作优先的优化措施。

然后，如果计划对列进行索引，就要尽量避免把它设置为可空,虽然在mysql里 Null值的列也是走索引的

mysql> SELECT 1 IS NULL, 1 IS NOT NULL;+-----------+---------------+

| 1 IS NULL | 1 IS NOT NULL |

+-----------+---------------+

| 0 | 1 |

+-----------+---------------+

1 row in setmysql> SELECT 0 IS NULL, 0 IS NOT NULL, '' IS NULL, '' IS NOT NULL;+-----------+---------------+------------+----------------+

| 0 IS NULL | 0 IS NOT NULL | '' IS NULL | '' IS NOT NULL |

+-----------+---------------+------------+----------------+

| 0 | 1 | 0 | 1 |

+-----------+---------------+------------+----------------+

测试

CREATE TABLE`test_null` (

`id`int(11) DEFAULT NULL,

`mark`varchar(20) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;create procedure test_null(in num int)BEGIN

DECLARE i int;set i=1;while (i

DOif mod(i,10)!=0 then

insert into test_null values (i,concat('aaa',i));else

insert into test_null values (null,concat('aaa',i));end if;set i=i+1;END while;END;

call test_null(10000);

mysql> select count(*) fromtest_null;+----------+

| count(*) |

+----------+

| 9999 |

+----------+

没加任何索引时

mysql> explain SELECT * from test_null WHERE id is null;+----+-------------+-----------+------------+------+---------------+------+---------+------+-------+----------+-------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+-----------+------------+------+---------------+------+---------+------+-------+----------+-------------+

| 1 | SIMPLE | test_null | NULL | ALL | NULL | NULL | NULL | NULL | 10105 | 10 | Using where |

+----+-------------+-----------+------------+------+---------------+------+---------+------+-------+----------+-------------+

在id上加普通索引create index idx_test_null ontest_null(id);

mysql> explain SELECT * from test_null WHERE id is null;+----+-------------+-----------+------------+------+---------------+---------------+---------+-------+------+----------+-----------------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+-----------+------------+------+---------------+---------------+---------+-------+------+----------+-----------------------+

| 1 | SIMPLE | test_null | NULL | ref | idx_test_null | idx_test_null | 5 | const | 999 | 100 | Using index condition |

+----+-------------+-----------+------------+------+---------------+---------------+---------+-------+------+----------+-----------------------+

null值也是走索引的

null在count统计时时的问题

create table test (id int,val int);INSERT INTO `test` VALUES ('1', '11');INSERT INTO `test` VALUES ('1', '111');INSERT INTO `test` VALUES ('2', '2');INSERT INTO `test` VALUES ('2', '22');INSERT INTO `test` VALUES ('2', '222');

1条语句统计id=1,id=2的个数

一般错误写法

select count(id=1) ,count(id=2) from test;

mysql> select count(id=1) ,count(id=2) fromtest;+-------------+-------------+

| count(id=1) | count(id=2) |

+-------------+-------------+

| 5 | 5 |

+-------------+-------------+

需要注意count只不会统计null的列，0的会统计

mysql> select 1 or null as or1,1 and null as and1 ,0 and null as and0 ,0 or null asnull0;+------+------+------+-------+

| or1 | and1 | and0 | null0 |

+------+------+------+-------+

| 1 | NULL | 0 | NULL |

+------+------+------+-------+

mysql> select id=1 ,id=2 fromtest;+------+------+

| id=1 | id=2 |

+------+------+

| 1 | 0 |

| 1 | 0 |

| 0 | 1 |

| 0 | 1 |

| 0 | 1 |

+------+------+

要把0值变为null,count时不计算即可

mysql> select count(id=1) ,count(id=2) fromtest;+-------------+-------------+

| count(id=1) | count(id=2) |

+-------------+-------------+

| 5 | 5 |

+-------------+-------------+

mysql> select id=1 or null,id=2 or null fromtest;+--------------+--------------+

| id=1 or null | id=2 or null |

+--------------+--------------+

| 1 | NULL |

| 1 | NULL |

| NULL | 1 |

| NULL | 1 |

| NULL | 1 |

+--------------+--------------+

所以正确的写法是

mysql> select count(id=1 or null),count(id=2 or null) fromtest;+---------------------+---------------------+

| count(id=1 or null) | count(id=2 or null) |

+---------------------+---------------------+

| 2 | 3 |

+---------------------+---------------------+

1 row in set (0.00 sec)

select id,count(id) from test where id in(1,2) GROUP BY id

常数与null的运算

DROP TABLE IF EXISTS`test1`;CREATE TABLE`test1` (

`id`int(11) DEFAULT NULL,

`a`int(11) DEFAULT NULL,

`b`int(11) DEFAULT NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1;--------------------------------Records of test1------------------------------

INSERT INTO `test1` VALUES ('1', '5', '1');INSERT INTO `test1` VALUES ('2', '6', null);INSERT INTO `test1` VALUES ('3', '4', '2');INSERT INTO `test1` VALUES ('4', '7', null);INSERT INTO `test1` VALUES ('5', null, null);

查询 id,a-b的数量(剩余计算)

错误写法

mysql> SELECT id ,(a-b) as remain fromtest1;+------+--------+

| id | remain |

+------+--------+

| 1 | 4 |

| 2 | NULL |

| 3 | 2 |

| 4 | NULL |

| 5 | NULL |

+------+--------+

正确写法

mysql> SELECT id ,(IFNULL(a,0)-IFNULL(b,0)) as remain fromtest1;+------+--------+

| id | remain |

+------+--------+

| 1 | 4 |

| 2 | 6 |

| 3 | 2 |

| 4 | 7 |

| 5 | 0 |

+------+--------+