尝试从表中选择一个随机行,基于没有孔的自动增量主键。
表格模式:
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 | |更多推荐
发布评论