Mysql千万级别数据如何做分页

一、先来看看对SQL优化的理解

SQL优化可分为两个部分，一个是设计阶段，另一个是查询阶段

设计阶段运用到的优化:
使用适当的数据库列类型和大小(需要保证表设计的字段长度足够使用，且最小。比如性别gender字段可以用tinyint(1)即可)。小点的话，可以节省硬盘空间和检索时间。

尽量从设计上采用单表查询解决业务问题。
在适当字段加入索引，能用唯一索引用唯一索引(性能比普通索引高)。

查询阶段涉及的优化：
尽可能*不用select ：让优化器无法完成索引覆盖扫描这类优化，而且还会增加额外的I/O、内存和CPU的消耗
** 慎用join操作：单张表查询可以减少锁的竞争，更容易应对业务的发展，方便对数据库进行拆分 (可用where in替代，剩余的在内存里面做拼装)
慎用子查询和临时表：未带索引的字段上的group by操作，UNION查询，部分order by操作，例如distinct函数和order by一起使用且distinct和order by同一个字段。
尽量*不适用limit*，部分场景可改用bewteen and

通过下图看下mysql执行过程:(摘抄)

执行计划可以用explain看到。一般比较慢是查询执行和存储引擎这两步，查询执行引擎执行技术后会进入存储引擎，存储引擎会来扫磁盘。其中mysql的索引是在存储引擎里面。查询执行计划等都是在内存中的，所以很快，而索引还有存储引擎是在磁盘中的，所以会慢。

二、平常的分页在数据量大的时候，你知道会引起哪些问题吗？

    后端开发中为了防止一次性加载太多数据导致内存、磁盘IO都开销过大，经常需要分页展示，这个时候就需要用到MySQL的LIMIT关键字。但你以为LIMIT分页就万事大吉了么，LIMIT在数据量大的时候极可能造成深度分页问题

先看下这个sql，执行后的查询情况:

从800万开始，检索一万条结果

一般电商大数据场景下，一个sql返回的结果不能超过10毫秒，上图这种写完提测，肯定是要被测试提BUG了。

那么，上述sql在查询的过程中，经历了什么呢？

** 步骤：**通过条件检索数据，然后将符合条件的数据全部都放到buffer里面，然后再内存中做排序和分页，去抓取我们所要的一万条数据，最终在内存中选择满足条件的一万条数据，返回结果。

如果我们满足条件的数据量很大，那么放在buffer中的数据量就会更大，这种性能，简直就是灾难级别的了。

三、针对深度分页的explain语句分析

通过explain分析深度分页查询问题 explain select * from user where age>10 and age<90000000 limit 8000000,10000;

执行计划Extra列可能出现的值及含义：

Using where：表示优化器需要通过索引回表查询数据。(回表，先定位主键值，再通过主键值定位行记录，性能上较之直接查询索引树定位行记录更慢)
Using index：即覆盖索引，表示直接访问索引就足够获取到所需要的数据，不需要通过索引回表，通常是通过将待查询字段建立联合索引实现。
Using index condition：在5.6版本后加入的新特性，即大名鼎鼎的索引下推，是MySQL关于减少回表次数的重大优化。
Using filesort:文件排序，这个一般在ORDER BY时候，数据量过大，MySQL会将所有数据召回内存中排序，比较消耗资源。

解决方案:

一、通过主键索引优化

在查询条件中带上主键索引 select * from user where id>#{maxId} and age>10 and age<90000000 order by age desc limit 10000;

maxId就是上一页中的最大主键Id。所以采用此方式的前提：1）主键必须自增不能是UUID并且前端除了传基本分页参数pageNo,pageSize外，还必须把每次上一页的最大Id带过来，2）该方式不支持随机跳页，也就是说只能上下翻页

二、Elastic Search搜索引擎优化（倒排索引）

实际上类似于淘宝这样的电商基本上都是把所有商品放进ES搜索引擎里的（那么海量的数据，放进MySQL是不可能的，放进Redis也不现实）。但即使用了ES搜索引擎，也还是有可能发生深度分页的问题的，这时怎么办呢？答案是通过游标scroll