MySQL：为什么要比较主键和随机生成的不使用索引的数字？(MySQL : Why is comparing primary key to a randomly generated number no

MySQL：为什么要比较主键和随机生成的不使用索引的数字？(MySQL : Why is comparing primary key to a randomly generated number not using the index?)

尝试从表中选择一个随机行，基于没有孔的自动增量主键。

表格模式：

CREATE TABLE IF NOT EXISTS `testTable` ( `id` int(9) NOT NULL AUTO_INCREMENT, `data` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 ; INSERT INTO `testTable` (`id`, `data`) VALUES (1, 'hello'), (2, 'world'), (3, 'new'), (4, 'data'), (5, 'more and more'), (6, 'data '), (7, 'more rows here'), (8, 'most rows here'), (9, 'testing'), (10,'last');

查询：

1 / explain select * from testTable where id = ceil(Rand()*10) limit 1 ;

http://sqlfiddle.com/#!2/6e2b1/1

结果：

| ID | SELECT_TYPE | TABLE | TYPE | POSSIBLE_KEYS | KEY | KEY_LEN | REF | ROWS | EXTRA | -------------------------------------------------------------------------------------------------------- | 1 | SIMPLE | testTable | ALL | (null) | (null) | (null) | (null) | 10 | Using where |

2 / explain select * from testTable where id = 7 limit 1 ;

http://sqlfiddle.com/#!2/6e2b1/2

结果：

| ID | SELECT_TYPE | TABLE | TYPE | POSSIBLE_KEYS | KEY | KEY_LEN | REF | ROWS | EXTRA | --------------------------------------------------------------------------------------------------- | 1 | SIMPLE | testTable | const | PRIMARY | PRIMARY | 4 | const | 1 | |

为什么查询＃1不使用索引，当ceil(rand()*10)应该理想地估计为一个常量，然后可以将其与主键进行比较？ 优化器不应该这样工作吗？ 或者我错过了一些明显的东西。

Trying to select a random row from a table, based on autoincremented primary key with no holes.

The table schema :

CREATE TABLE IF NOT EXISTS `testTable` ( `id` int(9) NOT NULL AUTO_INCREMENT, `data` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 ; INSERT INTO `testTable` (`id`, `data`) VALUES (1, 'hello'), (2, 'world'), (3, 'new'), (4, 'data'), (5, 'more and more'), (6, 'data '), (7, 'more rows here'), (8, 'most rows here'), (9, 'testing'), (10,'last');

Queries:

1/ explain select * from testTable where id = ceil(Rand()*10) limit 1 ;

http://sqlfiddle.com/#!2/6e2b1/1

Result :

| ID | SELECT_TYPE | TABLE | TYPE | POSSIBLE_KEYS | KEY | KEY_LEN | REF | ROWS | EXTRA | -------------------------------------------------------------------------------------------------------- | 1 | SIMPLE | testTable | ALL | (null) | (null) | (null) | (null) | 10 | Using where |

2/ explain select * from testTable where id = 7 limit 1 ;

http://sqlfiddle.com/#!2/6e2b1/2

Result:

| ID | SELECT_TYPE | TABLE | TYPE | POSSIBLE_KEYS | KEY | KEY_LEN | REF | ROWS | EXTRA | --------------------------------------------------------------------------------------------------- | 1 | SIMPLE | testTable | const | PRIMARY | PRIMARY | 4 | const | 1 | |

Why is query#1 not using the index, when ceil(rand()*10) should ideally evaluate to a constant which can then be compared to the primary key ? Shouldn't the optimizer work that way ? Or am I missing something obvious here.

最满意答案

该键不能用于该查询，因为每行都会调用RAND()并且每次都返回不同的值。

你可以试试这个代码：

SET @rand_value := CEIL(RAND()*10); EXPLAIN SELECT * FROM testTable WHERE id = @rand_value;

它首先计算一个随机值并将其分配给一个变量，然后在查询中使用它。 正如无符号所指出的那样， LIMIT 1是无用的：由于该条件适用于主键，查询永远不会返回多于一行。

通过这个查询，输出结果是：

| ID | SELECT_TYPE | TABLE | TYPE | POSSIBLE_KEYS | KEY | KEY_LEN | REF | ROWS | EXTRA | --------------------------------------------------------------------------------------------------- | 1 | SIMPLE | testTable | const | PRIMARY | PRIMARY | 4 | const | 1 | |

The key can't be used with that query because RAND() is called for each row and returns a different value each time.

You may try this code instead:

SET @rand_value := CEIL(RAND()*10); EXPLAIN SELECT * FROM testTable WHERE id = @rand_value;

It first computes a random value and assigns it to a variable, then uses it in the query. As pointed out by aneroid, the LIMIT 1 is useless: since the condition applies to the primary key, the query will never return more than one row.

With this query, the output is:

| ID | SELECT_TYPE | TABLE | TYPE | POSSIBLE_KEYS | KEY | KEY_LEN | REF | ROWS | EXTRA | --------------------------------------------------------------------------------------------------- | 1 | SIMPLE | testTable | const | PRIMARY | PRIMARY | 4 | const | 1 | |