mysql联表取is null优化_机智的MySQL优化器 --- is null

【介绍】

工作的越久越到的的问题越多，就越是觉得一些“老话”历久弥新；由于最近的学习计划是深入的学习一遍MySQL优化器；学习过程中的一些成果

也会发布到这里，一来是为了整理自己已经知道的和新学到的，二来是为了给自己的网站做个友情连接

【is null 优化】

如果我们在定义表的时候就给不能为null的列加上not null 那么就将是一个非常好的实践，想想如果接下来有查询要查找col is null的话，因为mysql

已经知道col不可能为null 所以MySQL会直接把这个优化掉，返回空结果集；理由是根本不会存在col is null的行

【看一下is null 有多吊吧】

第一步：建立一个测试表

create table t(id int not null auto_increment primary key, x int not null,y int);create index idx_t_x on t(x); --x 是not null 的

create index idx_t_y on t(y); --y 是可以为空的

insert into t(x,y) values(1,null),(2,2),(3,3);

第二步：观察MySQL针对null 和 not null的列的处理是有本质区别的

explain select x from t where x is null;+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+

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

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

| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |

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

1 row in set, 1 warning (0.00 sec)

由于x 不可能为空，所以当查询条件是x is null的情况下MySQL不用去查就直接返回了空结果集，正确+省事

mysql> explain select x from t where y is null;+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+

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

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

| 1 | SIMPLE | t | NULL | ref | idx_t_y | idx_t_y | 5 | const | 1 | 100.00 | Using index condition |

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

1 row in set, 1 warning (0.00 sec)

由于y是可以为null的、所以表的访问过程就变成了先读索引再回表(ref),就算y中的每一行都有值，其过程还是要比上面的x is null的查询要多做不少

第三步：不管列上有没有索引只要表定义中指定了条件不为null那么针对is null查询还是可以得到优化

alter table t add column z int;

explainselect x from t where z is null;+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

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

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

| 1 | SIMPLE | t | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |

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

1 row in set, 1 warning (0.00 sec)

由于z上没有索引也没有定义成 not null 所以针对 z is null只能直接全表扫描、下面看一下定义了not null的情况

alter table t drop columnz;alter table t add column z int not null; --在这种没有指定默认值的情况下、int类型默认为0

explain select * from t where z is null;+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+

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

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

| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |

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

1 row in set, 1 warning (0.00 sec)

【总结】

not null 应该算是关系模板中域完整性的一部分吧，这个已经是关系型数据库中的一部分；在定义表的时候就应该尽可能的把完整性加进去，这样优化

器得到的信息更多，做出的选择也更加机智。

【我的个站点】

---