首页 文章

当我在哪里使用rand时，为什么我的mysql回答“不使用密钥”

提问于
浏览 次
2

我有一张有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 . 因此，计划进行完整的索引扫描，事实上，它比以前的速度慢得多 .

然后，我想请教你如何用索引查找随机值 .

谢谢

mysql

3 回答

  • 0

    在SQL中使用 rand() 通常是一种使查询变慢的可靠方法 . 这里的一个共同主题是人们在 ORDER BY 中使用它来获得随机序列 . 它很慢，因为它不仅丢弃索引，而且还读取整个表 .

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

    我的理论是，MySQL的优化器可能是错误的 - 它在内部查询中看到函数，并且错误地判断它不能使用索引 .

    我可以建议解决的唯一问题是使用 force index 来推动MySQL使用你想要的索引 .

    回复于
  • 0

    请参阅rand()的定义 .

    如果我理解正确，你试图从数据库中获取随机记录 . 如果是这种情况，请再次从rand（）定义：

    ORDER BY RAND（）结合LIMIT对于从一组行中选择随机样本非常有用：SELECT * FROM table1，table2 WHERE a = b AND c <d - > ORDER BY RAND（）LIMIT 1000;

    回复于
  • 1

    它's a limitation of the MySQL optimizer, that it can'告诉子查询只返回一个值，它必须假设子查询返回多行具有不可预测的值，甚至可能是user_id的所有值 . 因此它决定它只是进行索引扫描 .

    这是一个解决方法：

    mysql> explain select user_id from MY_TABLE use index (PRIMARY)  
where user_id = ( select ceil( rand() ) );

    请注意，MySQL的RAND（）函数返回 0 <= v < 1.0 范围内的值 . 如果你CEIL（）它，你'll likely get the value 1. Therefore you' ll几乎总是得到user_id = 1的行 . 如果你没有't have such a row in your table, you'将获得一个空的设置结果 . 您当然不会在所有用户中随机选择用户 .

    要解决该问题，您必须将rand（）乘以不同user_id值的数量 . 这会带来您可能存在间隙的问题，因此随机选择的值将与任何现有的user_id都不匹配 .

    你的评论：

    当您获得索引扫描时，您总是会将可能的键视为NULL（即“type”是“index”） .

    我在类似的表上尝试了解释查询，看起来优化器无法确定子查询是一个常量表达式 . 您可以通过计算应用程序代码中的随机数，然后将结果用作查询中的常量值来解决此限制：

    select user_id from MY_TABLE use index (PRIMARY)  
where user_id = $random;
    回复于

相关问题