如何“逻辑删除”，你的逻辑是否删除呢？

MySQL目前常用的分页方法有两种：

1.借助limit实现分页，句型为“SELECT*FROM表名limit开始记录数,每页条数”；

2.借助字段索引实现分页，句型为“SELECT*FROM表名WHERE数组名>(页数*10)LIMIT条数”；

通常使用第一种形式居多，适用于数据量不大的场景：

SELECT * FROM user LIMIT 0,10;#0是开始的记录数，10是条数

倘若换成第二种写法：

SELECT * FROM user WHERE id > (0*10) LIMIT 10

id是字段。若果是第X页共Y条：（X从0开始估算）

SELECT * FROM user WHERE id > (X*10) LIMIT Y

其实，这些写法存在一定问题，假如第0页的id=5的数据被删掉了，还会造成查询第0页的数据和第1页的数据有重合，第0页是1-4，6-11（默认一页10条数据，由于limit10，所以会查询到id=11），第二页就是11-20，可见id=11重合了。

PS：其实关于id不连续的问题sql 查不到数据 返回0，你可以“逻辑删掉”，降低一个is_del的属性，当is_del=1时表明该数据“已经删掉”。

并且对于大量数据来说，这些问题是可以忽略的。

主要的是，这些大量数据的表，是机会不会去对其进行删掉甚至更改操作的。

这么为何大量数据使用第二种更合理呢？我们使用MySQL的关键字explain来看一下大约。

EXPLAIN SELECT * FROM test LIMIT 0,10

其他参数不做说明，就看type:扫描类型

效率从最好到最坏依次是：system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL

可见limit的分页疗效最差。

其实，实际当中你可以降低一些查询参数，让查询不再走全表扫描。

做对比的话就不降低查询条件了。

EXPLAIN SELECT * FROM test WHERE id > (0*10) LIMIT 10

这个走的是range范围查询显著很多了。

但这实际上还是通过外键的查询，取巧了而已。而且字段有外键索引，查询更快。

通常情况下，得保证查询起码达到range级别，最好能达到ref。

第一种方法和第二种方法当数据量不多的时侯，是第一种方法占优势，虽然没有数据重合的问题，但是查询的速率也没有显著差异。并且当数据量起来了，差异就很显著了，

SELECT * FROM test LIMIT 0,10

和

SELECT * FROM test LIMIT 100,10

查询速率是不一样的sql 查不到数据 返回0，查询了第100页时，实际上前99页都早已被扫描过。

所以第二种的方法更适用于大量数据的场景。

这么关于第一种，再多说一些。你可以通过降低一些查询参数去限制type，并且参数加的不好甚至还不如不加！牵连到回表问题。

关于回表：

先了解一些B+树：

在这个树形结构里，我们须要关注的是，最下边一层节点，也就是叶子结点。而这个叶子结点里放的信息会按照当前的索引是字段还是非字段有所不同。

例如执行：

select * from page where user_name = "小白10";

会通过非字段索引去查询user_name为”小白10″的数据，之后在叶子结点里找到”小白10″的数据对应的字段为10。

此时回表到字段索引中做查询，最后定位到字段为10的行数据。

但不管是字段还是非字段索引，她们的叶子结点数据都是有序的。诸如在字段索引中，那些数据是按照字段id的大小，从小到大，进行排序的。

所以说，假如你加的查询参数是无索引，是无序的，这么就是“不如不加”。

这么第一种怎样的去优化呢？

里面select前面带的是星号*，也就是要求获得行

select * from page  where id >=(6000000) order by id limit 10;

数据的所有数组信息。

我们结合第二种可以得到一种优化，例如执行的是：

select * from page order by id limit 6000000, 10;

因为此次的offset=6000000，会在innodb里的字段索引中获取到第0到（6000000+10）条完整行数据，从引擎层获取到好多无用的数据，而获取的那些无用数据都是要历时的。

当select旁边是*号时，就须要拷贝完整的行信息，拷贝完整数据跟只拷贝行数据里的其中一两个列数组历时是不同的，这就让本来就历时的操作显得愈发离谱。

由于上面的offset条数据最后都是不要的，即使将完整数组都拷贝来了又有哪些用呢，所以我们可以将sql句子更改成下边这样：

select * from page  where id >=(select id from page  order by id limit 6000000, 1) order by id limit 10;

前面这条sql句子，上面先执行子查询selectidfrompageorderbyidlimit6000000,1,这个操作，虽然也是将在innodb中的字段索引中获取到6000000+1条数据，之后server层会抛弃前6000000条，只保留最后一条数据的id。

但不同的地方在于，在返回server层的过程中，只会拷贝数据行内的id这一列，而不会拷贝数据行的所有列，当数据量较大时，这部份的历时还是比较显著的。

在领到了里面的id以后，假定这个id恰好等于6000000，那sql就弄成了

select * from page  where id >=(6000000) order by id limit 10;

这样innodb再走一次字段索引，通过B+树快速定位到id=6000000的行数据，时间复杂度是lg(n)，之后向后取10条数据。

这样性能确实是提高了，亲测能快一倍左右，属于那个历时从3s弄成1.5s的操作。

这······

属实有些杯水车薪，有点搓，属于没办法中的办法。

基于非字段索引的limit执行过程

前面提及的是字段索引的执行过程，我们再来看下基于非字段索引的limit执行过程。

例如下边的sql句子

select * from page order by user_name  limit 0, 10;

server层会调用innodb的插口，在innodb里的非字段索引中获取到第0条数据对应的字段id后，回表到字段索引中找到对应的完整行数据，之后返回给server层，server层将其放在结果集中，返回给顾客端。

而当offset>0时，且offset的值较小时，逻辑也类似，区别在于，offset>0时会扔掉后面的offset条数据。

也就是说非字段索引的limit过程，比字段索引的limit过程，多了个回表的消耗。

但当offset显得十分大时，例如600万，此时执行explain。

非字段索引offset值超大时走全表扫描

可以看见type那一栏显示的是ALL，也就是全表扫描。

这是由于server层的优化器，会在执行器执行sql句子前，判定下哪种执行计划的代价更小。

很显著，优化器在听到非字段索引的600w次回表以后，摇了摇头，还不如全表一条条记录去判定算了，于是选择了全表扫描。

因而，当limitoffset过大时，非字段索引查询十分容易弄成全表扫描。是真·性能杀手。

这些情况也能通过一些方法去优化。例如

select * from page t1, (select id from page order by user_name limit 6000000, 100) t2  WHERE t1.id = t2.id;

通过 select id from page order by user_name limit 6000000, 100 。先走innodb层的user_name非主键索引取出id，因为只拿主键id， 不需要回表 ，所以这块性能会稍微快点，在返回server层之后，同样抛弃前600w条数据，保留最后的100个id。然后再用这100个id去跟t1表做id匹配，此时走的是主键索引，将匹配到的100条行数据返回。这样就绕开了之前的600w条数据的回表。
当然，跟上面的case一样，还是没有解决要白拿600w条数据然后抛弃的问题，这也是非常挫的优化。
像这种，当offset变得超大时，比如到了百万千万的量级，问题就突然变得严肃了。
这里就产生了个专门的术语，叫 深度分页 。

深度分页问题

深度分页问题，是个很难受的问题，难受就想吐在，这个问题，它虽然无解。

不管你是用mysql还是es，你都只能通过一些手段去”减弱”问题的严重性。

遇见这个问题，我们就该回过头来想想。

为何我们的代码会形成深度分页问题？

它背后的原始需求是哪些，我们可以依据这个做一些规避。

假如你是想取出全表的数据

有些需求是这样的，我们有一张数据库表，但我们希望将这个数据库表里的所有数据取出，异构到es，或则hive里，这时侯倘若直接执行

select * from page;

这个sql一执行，狗看了都摇头。

由于数据量较大，mysql根本没办法一次性获取到全部数据，妥妥超晨报错。

于是不少mysql小白会通过limitoffsetsize分页的方式去分批获取，刚开始都是好的，等渐渐地，哪天数据表显得奇大无比，就有可能出现上面提及的深度分页问题。

这些场景是最好解决的。

我们可以将所有的数据按照id字段进行排序，之后分批次取，将当前批次的最大id作为上次筛选的条件进行查询。

可以看下伪代码

batch获取数据

这个操作，可以通过外键索引，每次定位到id在哪，之后往前遍历100个数据，这样不管是多少万的数据，查询性能都很稳定。

batch分批获取user表

若果是给用户做分页展示

假如深度分页背后的原始需求只是产品总监希望做一个展示页的功能，例如商品展示页，这么我们就应当好好跟产品总监battle一下了。

哪些样的翻页，须要翻到10多万之后，这显著是不合理的需求。

是不是可以改一下需求，让它更接近用户的使用行为？

例如，我们在使用微软搜索时听到的翻页功能。

通常来说，微软搜索基本上都在20页以内，作为一个用户，我就极少会翻到第10页然后。

作为参考。

假如我们要做搜索或筛选类的页面的话，就别用mysql了，用es，而且也须要控制展示的结果数，例如一万以内，这样不至于让分页过深。

假如由于各类缘由，必须使用mysql。那同样，也须要控制下返回结果数目，例如数目1k以内。

这样才能勉强支持各类翻页，跳页（例如忽然跳到第6页之后再跳到第106页）。