• 首页
  • vue
  • TypeScript
  • JavaScript
  • scss
  • css3
  • html5
  • php
  • MySQL
  • redis
  • jQuery
  • IS NULL优化

    MySQL能够执行在相同的优化,它可以使用。例如,MySQL可以使用索引和范围来搜索 with 。col_nameIS NULLcol_name=constant_valueNULLIS NULL

    例子:

    SELECT * FROM tbl_name WHERE key_col IS NULL;
    
    SELECT * FROM tbl_name WHERE key_col <=> NULL;
    
    SELECT * FROM tbl_name
      WHERE key_col=const1 OR key_col=const2 OR key_col IS NULL;
    

    如果WHERE子句包含声明为的列的条件,则该表达式将被优化。在可能仍然会产生该列的情况下(例如,如果它来自a右侧的表)则不会进行此优化。col_nameIS NULLNOT NULLNULLLEFT JOIN

    MySQL还可以优化组合,这种形式在已解决的子查询中很常见。显示何时使用此优化。col_name= expr OR col_name IS NULLEXPLAINref_or_null

    此优化可以处理IS NULL任何关键部分。

    假设在列和b表上都有索引,则对查询进行一些优化的示例t2

    SELECT * FROM t1 WHERE t1. =expr OR t1.  IS NULL;
    
    SELECT * FROM t1, t2 WHERE t1. =t2.  OR t2.  IS NULL;
    
    SELECT * FROM t1, t2
      WHERE (t1. =t2.  OR t2.  IS NULL) AND t2.b=t1.b;
    
    SELECT * FROM t1, t2
      WHERE t1. =t2.  AND (t2.b=t1.b OR t2.b IS NULL);
    
    SELECT * FROM t1, t2
      WHERE (t1. =t2.  AND t2.  IS NULL AND ...)
      OR (t1. =t2.  AND t2.  IS NULL AND ...);
    

    ref_or_null通过首先读取参考键,然后单独搜索具有NULL键值的行来工作。

    优化只能处理一个IS NULL级别。在以下查询中,MySQL仅在表达式上使用键查找(t1.a=t2.a AND t2.a IS NULL),而不能在上使用键部分b

    SELECT * FROM t1, t2
      WHERE (t1. =t2.  AND t2.  IS NULL)
    OR (t1.b=t2.b AND t2.b IS NULL);