MariaDB/MySQL的null值条件和索引
2023-06-13 22:13 abce 阅读(145) 评论(0) 编辑 收藏 举报对于应用程序来说,像这样使用WHERE条件并不罕见:
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)
MySQL对NULL值可以使用索引、range检索优化。