大数据翻页_MySQL大数据分页

前戏之前公司部门分享，如何在MySQL的数据量达到1KW条，并且在不分表不使用程序并发的方式尽可能提升大数据量分页的应用场景。

思考一个问题？首先我们知道，limit offset,N的时候，MySQL的查询效率特别的低，注意是在测试的表的数据量是1KW条，limit 5000000，N的时候，速度变的非常的慢，当然了offset特别小的时候，查询的速度没有什么差别。那我们来想一下什么原因造成的？

回答：原因就是MySQL并不是跳过offset行，然后单取N行，而是取offset+N行，然后放弃前offset行，返回N行，所以这样的话，查询的效率就特别的低，尤其是当offset特别大的时候，效率就非常的低下

优化的方法

1. 业务逻辑解决其实一般情况下，我们遇到这种大数据分页的时候，并不会真的将这些数据进行分页，因为从用户的角度来看，即便是分了好几百页，用户也只是点击最多前三页，我们想象，是不是这样的，比如：百度的分页才有79页，所以我们可以从业务逻辑的角度来控制这个分页

2. 不使用offset如果真的有那么一个变态的产品经理说，我们就是要将这1KW的数据进行分页，那怎么办？我们必须在保证查询效率的前提下来完成这个分页的需求

使用offset来进行分页一般正常的情况下，我们最先想到的分页语句如下：

mysql> select id, name from logs limit 5000000,10;

+---------+--------------------------------------------+

| id | name |

+---------+--------------------------------------------+

| 5554609 | 新闻 |

..................

| 5554618 | 股票信息 |

+---------+--------------------------------------------+

10 rows in set (5.33 sec)

不使用offset方法

mysql> select id, name from logs where id>=(SELECT id FROM logs LIMIT 5000000, 1) limit 10;

+---------+--------------------------------------------------------+

| id | name |

+---------+--------------------------------------------------------+

| 5000001 | 互联网金融 |

.................

| 5000002 | P2P理财 |

+---------+--------------------------------------------------------+

10 rows in set (0.00 sec)我们明显的观察到两者的时间差了整整的5倍多。

但我们同时也发现，2次查询的结果是不一样的，那这是因为我们的数据中间有被物理删除过，数据出现了空洞。

解决的方法 - 数据我们一般不进行物理删除，我们可以进行逻辑删除，最终在页面上显示数据的时候，逻辑删除的条目不显示即可。(一般来说，大网站的数据都是不物理删除的，只做逻辑删除)我们可以直接找出要查询的id，然后使用in，进行子查询 ``` 先通过 select id from logs limit 500000, 10;

检索出所有符合条件的 id，然后将id组装成 in 模式，二次查询

select * from logs where id in(5000001, 5000002, 5000003, 5000004, .....5000010); ``` 这种方式比较容易在业务层实现，方便控制和筛选

代码实现三种方式

/*** mysql 大数据量分页优化* @author sallency@osc 2016-5-11 16:48:44*/

$conn = new mysqli('localhost', 'root', '123456', 'sys');

/*** 400w数据分页优化*/

/*** 自己在业务层做写的* 先通过 order by 索引的特性将符合条件的 id 检索出来* 再次拼接成 in 条件进行检索 链接没断掉 虽然二次查询但不消耗再次建立链接的资源* 但遗憾的是你不能把它写成一条语句去执行 mysql 无法优化成我们想要的执行逻辑*/

$sql = "select sql_no_cache id from logs 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 logs 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 logs 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 secslimit 5000, 10

example_1:0.004 secs

example_2:0.008 secs

example_3:0.013 secslimit 50000,10 5W

example_1:0.029 secs

example_2:0.042 secs

example_3:0.109 secslimit 500000,10 50W

example_1:0.286 secs

example_2:0.346 secs

example_3:4.563 secslimit 3000000, 10 300w

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

如果有啥疑问，可以加我的微信咨询哦！