MariaDB/MySQL的null值条件和索引

2023-06-13 22:13 abce 阅读(162) 评论(0) 编辑收藏举报

对于应用程序来说，像这样使用WHERE条件并不罕见:

1	`WHERE` `status =` `'DELETED'` `OR` `status` `IS` `NULL`

如果运行EXPLAIN，这样的条件通常只会导致type列显示为ref_or_null。然而，如果没有NULL检查，它将显示为ref。

但是，这是否意味着执行过程中只会发生一个细小的变化，而查询仍然会非常快呢?答案是:视情况而定。这个回答很令人沮丧，但是通过下面的解释也许可以减轻这种沮丧。

创建了一个略多于350万行的测试表。按照这个顺序在列(a, b)上建立了一个索引，只在(b)上建立了另一个索引。然后运行了以下查询，运行感觉很好。

mysql> SELECT COUNT(*) FROM t WHERE (a = 2 OR a IS NULL) AND (b = 5);
+----------+
| COUNT(*) |
+----------+
|      212 |
+----------+
1 row in set (0.01 sec)
 
mysql> SELECT COUNT(*) FROM t WHERE (a = 2) AND (b = 5 OR b IS NULL);
+----------+     
| COUNT(*) |
+----------+
|      120 |
+----------+
1 row in set (0.01 sec)

可以看到，如果对索引中的任一列上添加一个is null条件，查询仍然很快。问题是，如果在多个列上使用is null呢？

mysql> SELECT COUNT(*) FROM t WHERE (a = 2 OR a IS NULL) AND (b =  5 OR b IS NULL);
+----------+
| COUNT(*) |
+----------+
|  1466664 |
+----------+
1 row in set (1 min 21.32 sec)

太慢了!你可能认为这取决于行数。选择了更多的行，也许这就是慢的原因。我们可以很容易地测试它:我运行UPDATE将所有null替换为0值，并重复查询:

mysql> SELECT COUNT(*) FROM t WHERE (a = 2 OR a IS NULL) AND (b =  5 OR b IS NULL);
+----------+
| COUNT(*) |
+----------+
|      120 |
+----------+
1 row in set (0.02 sec)

非常快!但是，这种变慢真的正常吗?让我们看看如果我们查0值会发生什么:

mysql> SELECT COUNT(*) FROM t WHERE (a = 2 OR a = 0) AND (b =  5 OR b = 0);
+----------+
| COUNT(*) |
+----------+
|  2457536 |
+----------+
1 row in set (1.93 sec)

这次选择了更多的行，但是查询所用的时间不到2秒。仍然很慢，但这是一个巨大的进步:之前的版本需要81秒!

查询优化器
这两个查询之间的差异是什么呢？
第一个查询不能正确的使用索引，因为ref_or_null不能用在多个列上。采取的操作是在b上使用了索引：

mysql> EXPLAIN SELECT COUNT(*) FROM t WHERE (a = 2 OR a IS NULL) AND (b =  5 OR b IS NULL) G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
   partitions: NULL
         type: ref_or_null
possible_keys: idx_b,idx_a_b
          key: idx_b
      key_len: 10
          ref: NULL
         rows: 1815359
     filtered: 11.43
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

第二个查询是在每个列上查询常规的值，因此在正确的索引上使用了range检索：

mysql> EXPLAIN SELECT COUNT(*) FROM t WHERE (a = 2 OR a = 0) AND (b =  5 OR b = 0) G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
   partitions: NULL
         type: range
possible_keys: idx_b,idx_a_b
          key: idx_a_b
      key_len: 10
          ref: NULL
         rows: 1908763
     filtered: 100.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

range检索可以涉及来自同一索引的多个列。Extra列确认仅通过读取idx_a_b索引来执行查询。

有关此优化的更多细节，请参阅MySQL文档中的IS NULL优化页面。

解决方案是使用常规值而不是NULL。逻辑上的意思是"没有"，但它不是NULL。通常情况下，用0表示，或者空字符串" "，或者UNIX纪元的开始'1970-01-01 00:00:00'。

你可能对查询优化的这一方面不熟悉，但是可能至少会高兴地知道"=优先与>"的优化规则适用于以下两个查询。换句话说，它不受ref_or_null限制的影响。

mysql> EXPLAIN SELECT COUNT(*) FROM t WHERE (a = 2000 OR a = 0) AND (b >  5 OR b = 0) G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
   partitions: NULL
         type: range
possible_keys: idx_b,idx_a_b
          key: idx_a_b
      key_len: 10
          ref: NULL
         rows: 1761559
     filtered: 100.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.01 sec)
 
mysql> EXPLAIN SELECT COUNT(*) FROM t WHERE (a = 2000 OR a IS NULL) AND (b >  5 OR b IS NULL) G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
   partitions: NULL
         type: range
possible_keys: idx_b,idx_a_b
          key: idx_a_b
      key_len: 10
          ref: NULL
         rows: 4
     filtered: 100.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)