MySQL大数据分页查询优化 3百万数据分页查询实例对比

面对一个百万级别的单表，除了需要合理的表结构设计，还必须对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 找回