MySQL分页查询的实现方式及性能优化技巧

1. 前言

MySQL 的分页查询在我们的开发过程中还是很常见的，比如一些后台管理系统，我们一般会有查询订单列表页、商品列表页等。

示例：

SELECT *  FROM  `goods`   order  by create_time   limit  0, 10;

在了解order by和limit的工作原理之前，我们首先回顾下 MySQL 的执行流程和索引结构。

注：

下面没有特别说明默认 MySQL 的引擎为 InnoDB

为讲述方便使用 select * ，生产环境不建议使用

1.1. 执行流程

MySQL 可以分为 Server 层和存储引擎层两部分，对于这个就不展开讲了。只需要知道一条 SQL 语句是从客户端发起请求到 Server 层，Server 层处理之后选出成本最低的执行计划去存储引擎层进行数据查询，查询出来的数据返回给 Server 层处理，最后返回给客户端。（存储引擎层根据扫描区间定位拿到数据给到 Server 层，剩下的过滤、排序、分页等操作是在 Server 层载进行处理的）。

1.2 索引结构

InnoDB 存储引擎的索引是一颗 B+ 树，只有主键索引树会存储全部的行记录数据，二级索引只会存储该记录对应的主键 id。所以我们使用二级索引查询数据时，如果查询的字段在二级索引没办法完全覆盖，则需要回表。

2. order by 工作原理

  准备工作

创建一张商品表，并且给价格字段设置索引

插入测试数据

2.1 索引扫描排序

EXPLAIN  SELECT *  FROM goods