MySQL:随机id选择时索引无效

执行此操作时:

WITH abc AS (SELECT FLOOR(1 + RAND() * 1000) as  R)
SELECT * FROM t_test WHERE id = (SELECT R FROM abc);

您可能期望CTE只返回一条记录,并且可以使用索引。

但当添加EXPLAIN时,它显示 不可支票的 :

编辑:

刚刚在文档中找到了这个:

The difficulties stem from the fact that the RAND() function is evaluated once for every row of the table. To avoid multiple function evaluations, use one of these techniques

将包含不确定性函数的表达式移到单独的语句中,将值保存在变量中。在原始语句中,将表达式替换为对变量的引用,优化器可以将其视为常数值:

SET @keyval = FLOOR(1 + RAND() * 49);
UPDATE t SET col_a = some_expr WHERE id = @keyval;