mysql - 为什么不在下面的sql中使用两个索引

Question

CREATE TABLE IF NOT EXISTS test (col1 INT, col2 INT);
ALTER TABLE test ADD INDEX idx_col1(col1);
ALTER TABLE test ADD INDEX idx_col2(col2);

EXPLAIN SELECT * FROM test WHERE col1>=0 AND col2<=2;

结果:

id select_type table type  possible_keys        key key_len ref
1  SIMPLE      test  range idx_col2,idx_col1    idx_col2    5   1

为什么只使用 idx_col2，而不同时使用。

Answer 1

这是涉及范围的选择的经典问题。

索引查找的结果是表中的一行或多行。当 SQL 决定如何处理您的选择时，它必须首先决定使用哪个索引。在你的例子中是 idx_col2。结果，它将知道满足 (col2<=2) 的所有行。

它必须从这些行中删除不满足 (col1>=0) 的行。然而，索引 idx_col1 在这里没有多大帮助，因为它指的是整个表，而不仅仅是指仍然有问题的行。对 idx_col1 的索引范围扫描将返回所有 满足谓词的行，SQL 必须找到由 col1 和 找到的行col2 索引范围扫描。

在几乎所有情况下，这都不划算。如果从 col2 扫描返回的行数很少，那么 SQL 不妨遍历所有这些行并检查 (col1>=0)，而不使用索引。如果行数很大，查找匹配行的成本会很高。它必须考虑从 col2 扫描返回的所有行，并检查 col1 扫描中是否有匹配的行。这比简单地检查它们是否满足 (col1>=0) 更昂贵。

一般来说，常规的 b-tree 索引和范围(关于不同的列)不能很好地协同工作。一些 RDBMS 有一种特殊的“空间”索引，它允许涉及范围的高效查询。