mysql - 为什么当我在 where 中使用 rand 时我的 mysql 回答 "not using key"

Question

我有一个包含 4,000,000 条记录的表。创建该表:(user_id int, partner_id int, PRIMARY_KEY (user_id)) engine=InnoDB;我想测试 select 100 条记录的性能。然后，我测试了以下内容:

mysql> explain select user_id from MY_TABLE use index (PRIMARY)  where user_id IN ( 1 );
+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table    | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------------+
|  1 | PRIMARY     | MY_TABLE | const | PRIMARY       | PRIMARY | 4       | const |    1 | Using index |
+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------------+
1 row in set, 1 warning (0.00 sec)

这没关系。但是，这个查询是由 mysql 缓冲的。所以，这个测试在第一次测试之后就没有了。

然后，我想到了一个按随机值选择的sql。我测试了以下内容:

mysql> explain select user_id from MY_TABLE use index (PRIMARY)  where user_id IN ( select ceil( rand() ) );
+----+-------------+----------+-------+---------------+---------+---------+------+---------+--------------------------+
| id | select_type | table    | type  | possible_keys | key     | key_len | ref  | rows    | Extra                    |
+----+-------------+----------+-------+---------------+---------+---------+------+---------+--------------------------+
|  1 | PRIMARY     | MY_TABLE | index | NULL          | PRIMARY | 4       | NULL | 3998727 | Using where; Using index |
+----+-------------+----------+-------+---------------+---------+---------+------+---------+--------------------------+

但是，这很糟糕。Explain 显示 possible_keys 为 NULL。因此，计划进行全索引扫描，实际上，它比以前慢了很多。

然后，我想请你教我如何写索引查找的随机值。

谢谢

Answer 1

在 SQL 中使用 rand() 通常是降低查询速度的必经之路。这里的一个共同主题是人们在 ORDER BY 中使用它来获得随机序列。它很慢，因为它不仅会丢弃索引，还会读取整个表。

但是在您的情况下，函数调用在子查询中的事实应该允许外部查询仍然使用其索引。事实上，这似乎并不奇怪(所以我给这个问题投了 +1 票)。

我的理论是，也许 MySQL 的优化器弄错了——它看到了内部查询中的函数，并错误地决定它不能使用索引。

我唯一可以建议解决的问题是使用 force index 来插入 MySQL 使用您想要的索引。