浅谈MySQL分页查询的工作原理

角马

浅谈MySQL分页查询的工作原理 390 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 工作原理 准备工作创建一张商品表，并且给价格字段设置索引CREATE TABLE goods (        id BIGINT RIMARY KEY AUTO_INCREMENT,        name VARCHAR(255) NOT NULL comment '商品名称',        price DECIMAL(10,2) NOT NULL comment '售价',        create_time  DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL COMMENT '创建时间',        update_time  DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间');create index idx_price on goods (price);插入测试数据delimiter $DROP ROCEDURE IF EXISTS proc_batch_insert;CREATE ROCEDURE proc_batch_insert()BEGIN    DECLARE pre_name BIGINT;    DECLARE priceVal INT;    DECLARE i INT;    SET pre_name=1;    SET priceVal=30;    SET i=1;    WHILE i  10 and price  10 and price  10 and price  10 and price  10 and price  10 and price  10 and price