从MySQl数据库中随机获取10条数据很简单：

SELECT * FROM db_contents ORDER BY RAND() LIMIT 10

但是如果一个表的数据基数很大，从百万级、千万级数据中随机产生10条数据，如果还用 rand() 那就会相当的慢，再考虑到并发。建议轻易不要用rand()，测试从100万数据里随机获取10条数据，用时9s左右，这个耗费是很惊人的。

获取MySQl的MAX(id)和MIN(id)消耗几乎是0，测试在116万数据的基数下，我们利用MAX(id)：

SELECT * FROM db_contents WHERE cid >= ( SELECT FLOOR( RAND() * ( SELECT MAX( cid ) FROM db_contents ) ) ) ORDER BY cid LIMIT 10;

这样，随机产生10条数据仅消耗0.005s。然后，把SQL完善一下，加上MIN(cid)：

SELECT * FROM db_contents WHERE cid >= (SELECT FLOOR( RAND() * ((SELECT MAX(cid) FROM  db_contents)-(SELECT MIN(cid) FROM db_contents)) + (SELECT MIN(cid) FROM db_contents))) ORDER BY cid LIMIT 10;

再把SQL改成JOIN语法：

SELECT * FROM  db_contents AS c1 JOIN (SELECT ROUND(RAND() * ((SELECT MAX(cid) FROM  db_contents)-(SELECT MIN(cid) FROM db_contents))+(SELECT MIN(cid) FROM db_contents)) AS cid) AS c2 WHERE c1.cid >= c2.cid ORDER BY c1.cid LIMIT 10;

最后，对这4个语句分别进行查询，查询10次的平均效率：

1. 花费时间 9.151 秒

2. 花费时间 0.005 秒

3. 花费时间 0.013 秒

4. 花费时间 0.005 秒

有可能是数据量太小的原因，后3条查询的速率差异不太明显，理论上应该是逐条增强的！！

写于2018年04月22日，由 archive.org 找回

面对一个百万级别的单表，除了需要合理的表结构设计，还必须对SQL进行精细的优化。以下是对300w数据快速分页查询实例的几种方法对比：

/**
 *  300w数据分页优化
*/
/**
 * mysql的索引对于in语句同样有效，网上说in无法用索引是错误的~
 * 先通过 order by 索引的特性将符合条件的 id 检索出来
 * 再次拼接成 in 条件进行检索 链接没断掉 虽然二次查询但不消耗再次建立链接的资源
 */
$sql = "select sql_no_cache id from sys_data order by id limit 3000020, 10";
$start = microtime(true);
$result = $conn->query($sql);
$id_arr = array_column($result->fetch_all(), 0);
$id_set_str = implode(',', $id_arr);
$sql = "select * from sys_data where id in($id_set_str)";
$result = $conn->query($sql);
$data_1 = $result->fetch_all();
$end = microtime(true);
echo "example_1:";
echo number_format($end - $start, 3) . ' secs' . PHP_EOL;

/**
 * 业界流传的经典方法
 * 通过子查询查处符合条件的 id，取偏移量获得记录
 */
$sql = "select sql_no_cache * from sys_data where id > (select id from sys_data order by id limit 3000020, 1) limit 10";
$start = microtime(true);
$result = $conn->query($sql);
$data_2 = $result->fetch_all();
$end = microtime(true);
echo "example_2:";
echo number_format($end - $start, 3) . ' secs' . PHP_EOL;

/**
 * 普通方法
 * 此类方法针对对数据量比较小的时候还可以应对
 * 数据量十万时可能order by 就无法启用索引了 具体我也没测
 */
$sql = "select sql_no_cache * from sys_data order by id limit 3000020, 10";
$start = microtime(true);
$result = $conn->query($sql);
$data_3 = $result->fetch_all();
$end = microtime(true);
echo "example_3:";
echo number_format($end - $start, 3) . ' secs' . PHP_EOL;

测试结果：

limit 500, 10

example_1:0.006 secs

example_2:0.015 secs

example_3:0.003 secs

limit 5000, 10

example_1:0.004 secs

example_2:0.008 secs

example_3:0.013 secs

limit 50000,10

example_1:0.029 secs

example_2:0.042 secs

example_3:0.109 secs

limit 500000,10

example_1:0.286 secs

example_2:0.346 secs

example_3:4.563 secs

limit 3000000, 10

300w 第一次

example_1:1.356 secs

example_2:1.936 secs

example_3:6.097 secs

300w 第二次

example_1:1.013 secs

example_2:1.534 secs

example_3:6.306 secs

300w 第三次

example_1:1.248 secs

example_2:1.924 secs

example_3:5.769 secs

300w 第四次

example_1:0.782 secs

example_2:1.582 secs

example_3:5.324 secs

limit 34105450, 10

example_1:17.915 secs

example_2:51.673 secs

example_3:209.536 secs

写于2018年03月04日，由 archive.org 找回