优化随机查询:ORDER BY RAND()效率低;
验证解决方案时,
connection = DBUtil.getDBCon();
statement = connection.createStatement();
String sql1 = "select count(id),max(id) from test2";
String sql2 = "select FLOOR(RAND() * (SELECT MAX(id) FROM test2))";
String sql3 = "SELECT id FROM test2 WHERE id >= (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM test2))) ORDER BY id LIMIT 1";
resultSet = statement.executeQuery(sql1);
if(resultSet.next()){
System.out.println("第一条查询表里数据条数:\t"+resultSet.getInt(1)+"\t最大id:\t"+resultSet.getInt(2));
}
System.out.println("第二条查询表里随机一个id");
for(int i=0;i<20;i++){
resultSet = statement.executeQuery(sql2);
if(resultSet.next()){
System.out.print(resultSet.getInt(1)+"\t");
System.out.print((i+1)%5==0?"\n":"");
}
}
System.out.println("第三条理论上和第二条一样,只是为防止id断层");
for(int i=0;i<20;i++){
resultSet = statement.executeQuery(sql3);
if(resultSet.next()){
System.out.print(resultSet.getInt(1)+"\t");
System.out.print((i+1)%5==0?"\n":"");
}
}
输出结果却不太符合常理
第一条查询表里数据条数: 232515 最大id: 287971
第二条查询表里随机一个id
159313 238050 136370 255671 5331
123556 25844 46526 155099 59976
122554 144873 68733 197015 202935
135661 69501 228491 70042 240679
第三条理论上和第二条一样,只是为防止id断层
1021 991 660 571 1753
910 551 183 715 314
1166 554 427 688 509
318 1344 762 861 301
查询结果的id太小了
想不明白为什么