• 机智的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;
    
    explain select 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 column z;
    
    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 应该算是关系模板中域完整性的一部分吧,这个已经是关系型数据库中的一部分;在定义表的时候就应该尽可能的把完整性加进去,这样优化

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

    【我的个站点】

      www.sqlpy.com

    ---

  • 相关阅读:
    Windows API 之 Windows Service
    揭开Socket编程的面纱 (一)
    开发中“错误: 意外地调用了方法或属性访问。” 和 第一行错误 的IE 两个问题( JQ 进行转义字符 , 分页JS 调用 时参数问题。)
    结合MSDN理解windows service 服务安装的三个类。
    VFW基础知识(一些定义性质的。从CSDN中得到的。)
    初次接触WIN FORM,深入事件、委托、方法 ,深入看不到的C#探索。
    C#: +(特性 ) + Attitude C#(类)前面或者(方法)前面 (中括号)定义
    VFW系列教程经典
    依赖注入
    Windows Service:SC 和 InstallUtil 区别
  • 原文地址:https://www.cnblogs.com/JiangLe/p/10281001.html
Copyright © 2020-2023  润新知