mysql - < created_at 的 mysql 索引而不是 in

Question

我想获取所有早于 x 的行，但具有特定 batch_id 的行除外

药片：

id, created_at, batch_id

询问：

SELECT * FROM t 
WHERE created_at < '2019-01-01' 
AND batch_id NOT IN (1,2,3)

即使我有 < 1M 行，查询也很慢。我在 (created_at)、(batch_id) 和 (created_at, batch_id) 上有索引。我希望复合索引使其更快，但数据库决定使用 created_at 代替。

每个 batch_id 大约有 100 行

标签： mysqlindexing

Answer 1

MySQL 索引的一般规则是它最多将索引用于一个范围条件，并且索引中的任何后续列都不能使搜索受益。

示例：如果您在 columns 上有一个索引(A, B, C)，那么：

WHERE A = 1 AND B = 2 AND C = 3 -- uses all three columns of index

WHERE A = 1 AND B = 2 AND C < 3 -- uses all three columns of index

WHERE A = 1 AND B < 2 AND C = 3 -- uses only A and B column of index

WHERE A < 1 AND B = 2 AND C = 3 -- uses only A column of index

在您的查询中，条件created_at < ...和batch_id not in (...)都是范围条件。也就是说，它们不是相等 ( =) 条件，并且除相等之外的任何类型的条件都算作用于此目的的范围条件。

反转索引列的顺序不会改变这一点。由于这两个条件都是范围条件，MySQL 将只使用两列之一的索引——索引的第一列，无论它是什么。

您看到 MySQL 切换到单列索引，created_at因为优化器知道无论如何它只能使用一列，并且它更喜欢使用更紧凑的索引，因为每页可以容纳更多的索引条目。

它选择索引是created_at因为人们认为它更具选择性。你说每个大约有 100 行，batch_id总共有 100 万行。所以batch_id NOT IN (1,2,3)只过滤掉 0.03% 的行。而条件 oncreated_at可能会过滤掉更多，使其成为更好的选择。

您说查询仍然很慢。你没有说它有多慢，或者你期望它有多快。也许你对表演抱有不切实际的期望。

也许您的数据库服务器需要更强大的硬件。你没有说你的服务器有什么规格。

也许您正在同一台服务器上运行其他要求苛刻的进程，并且它们正在与mysqld.

你没有说你配置了哪些 MySQL 调优参数。也许你的缓冲池太小了。你没有说你使用的是什么版本的 MySQL。