ECDaddy

Menu

MySQL大数据量分页查询优化 百万级快速分页

/**
 *  400w数据分页优化
 */
/**
 * mysql的索引竟然对于in语句同样有效,看来网上说in无法用索引是错误的!
 * 先通过 order by 索引的特性将符合条件的 id 检索出来
 * 再次拼接成 in 条件进行检索 链接没断掉 虽然二次查询但不消耗再次建立链接的资源
 * 但遗憾的是你不能把它写成一条语句去执行 mysql 无法优化成我们想要的执行逻辑
 */
$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

关于对MySQL的SQL_NO_CACHE的理解和用法举例

— 于 共写了1873个字
— 文内使用到的标签:

发表评论

电子邮件地址不会被公开。 必填项已用*标注