有意义的网站,网站建设用电脑,河南省建设部省厅网站,网站集约建设原因前言 MySQL 的分页查询在我们的开发过程中还是很常见的#xff0c;比如一些后台管理系统#xff0c;我们一般会有查询订单列表页、商品列表页等。
示例#xff1a;
SELECT * FROM goods order by create_time limit 0,10; 在了解order by和limit的工作原理之前#xff0c…前言 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。所以我们使用二级索引查询数据时如果查询的字段在二级索引没办法完全覆盖则需要回表。
order by 工作原理 准备工作 创建一张商品表并且给价格字段设置索引
REATE TABLE goods (id BIGINT PRIMARY 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 PROCEDURE IF EXISTS proc_batch_insert;
CREATE PROCEDURE proc_batch_insert()
BEGINDECLARE pre_name BIGINT;DECLARE priceVal INT;DECLARE i INT;SET pre_name1;SET priceVal30;SET i1;WHILE i 1000000 DOINSERT INTO goods(name,price,create_time,update_time) VALUES(CONCAT(商品,pre_name),(priceVali)%100,NOW(),NOW());SET pre_namepre_name1;SET ii1;END WHILE;
END $$delimiter ;
call proc_batch_insert();2.1 索引扫描排序 EXPLAIN SELECT * FROM goods where price 10 and price 13 ORDER BY price;
我们发现这条 sql 使用了索引 idx_price,索引结构如下 首先会根据二级索引idx_price进行查询找到满足price 10 and price 13区间的主键值因为我们的查询条件是SELECT *,所以需要回表查询到对应的行记录。由于ORDER BY price我们是需要对查询出来的结果按照价格从小到大进行排序。我们刚刚的数据就是从二级索引idx_price查询出来的本就是根据price字段排序的所以无需再排序直接把查询的数据返回给客户端就行了。 2.2 文件排序filesort 以下 3条 sql 语句都会使用文件排序
-- Using where; Using filesort
EXPLAIN SELECT * FROM goods where price 10 and price 30 ORDER BY price;
-- Using where; Using filesort
EXPLAIN SELECT * FROM goods where name like 商品 1% ORDER BY price;
-- Using index condition; Using filesort
EXPLAIN SELECT * FROM goods where price 10 and price 13 ORDER BY name;对于第二条 sql 我们很容易判断出来使用不了二级索引idx_price只能全表扫描查询出符合条件的行记录再去进行文件排序。
那么第一条 sql 只是查询范围比之前的更大了为什么就不走二级索引了呢
我们前面介绍执行流程时说道MySQL 会选择执行成本最低的执行计划。这条 sql 的查询范围是price 10 and price 30,满足这个条件的数据是很多的每一条数据都需要进行回表查询。这样大量的回表查询MySQL 认为是很慢的所以没有使用二级索引。
第三条 sql 虽然可以使用到二级索引idx_price但是需要排序的字段是name那么二级索引的作用就只是帮助我们加快查询而排序操作还是需要使用文件排序。
什么是文件排序呢
文件排序分成两种全字段排序、rowid 排序。接下来我们分别讲解这两种排序工作原理。
2.2.1 全字段排序 MySQL 会给每个线程分配一块内存用于排序 sort_buffer。sort_buffer_size就是 MySQL 为排序开辟内存sort_buffer的大小。
如果要排序的数据量小于 sort_buffer_size排序就在内存中完成。但如果排序数据量太大内存放不下则不得不利用磁盘临时文件来辅助排序。
EXPLAIN SELECT * FROM goods where price 10 and price 13 ORDER BY name;我们根据这条 sql 来分析查询过程
先根据二级索引idx_price查询出满足过滤条件的数据
根据主键 id 进行回表操作查询出对应的行记录
数据汇总到 sort_buffer 按照name进行排序
将排序好的结果集返回给客户端
2.2.2 rowid 排序 我们发现全字段排序会存在一个问题如果表中的字段非常多我们把整个行记录放入 sort_buffer 里面进行排序时能够放入的行记录就会很少排序性能差。
max_length_for_sort_data 是 MySQL 中专门控制用于排序的行数据的长度的一个参数。它的意思是如果单行的长度超过这个值MySQL 就认为单行太大要换一个算法。使用 rowid 排序需要排序的字段 主键 id 放入 sort_buffer 进行排序。 还是使用上述 sql 分析
可以使用二级索引所以先根据二级索引idx_price查询出满足过滤条件的数据 根据主键 id 进行回表操作查询出对应的行记录 将排序字段name和主键 id 一起放入 sort_buffer 进行排序 根据主键 id 再去主键索引查询全部字段返回给客户端 如果 MySQL 实在是担心排序内存太小会影响排序效率才会采用 rowid 排序算法这样排序过程中一次可以排序更多行但是需要再回到原表去取数据。如果 MySQL 认为内存足够大会优先选择全字段排序把需要的字段都放到 sort_buffer 中这样排序后就会直接从内存里面返回查询结果了不用再回到原表去取数据。
这也就体现了 MySQL 的一个设计思想如果内存够就要多利用内存尽量减少磁盘访问。
对于 InnoDB 表来说rowid 排序要求回表多造成磁盘读因此不会被优先选择。
2.2.3 排序算法 我们前面说在 sort_buffer 进行排序但是没有说明具体是什么排序算法其实我们这个排序算法是需要分情况的具体如下
若排序内容能全部放入内存则仅在内存中使用快速排序 若排序内容不能全部放入内存则分批次将排好序的内容放入文件然后将多个文件进行归并排序 若排序中包含 limit 语句则使用堆排序优化排序过程 3. limit 工作原理 Server 层维护了一个称作 limit_count 的变量用于统计已经跳过了多少条记录。limit m , n 工作原理就是先读取前面 mn 条记录然后抛弃前 m条读后面 n条想要的所以 m越大偏移量越大性能就越差。
EXPLAIN SELECT * FROM goods where price 10 and price 13 ORDER BY name limit 500,10;如上述 sql 语句MySQL 先查询 510 条数据按照ORDER BY的工作原理进行条件查询和排序最后汇总的结果在返回给客户端之前MySQL 会截取第 501 到 510 条数据最后把这 10 行记录返回给前端。
常见问题分析 4.1 排序字段使用非唯一字段导致乱序问题 我们平常使用的分页查询,如果没有用到索引排序底层的排序算法是堆排序由于是堆排序是不稳定排序会产生乱序问题。
分页查询商品表根据创建时间进行排序 SELECT * FROM goods order by create_time limit 0,10; 但是如果此时数据库的商品数据都是通过 Excel 导入进去的那么它们的创建时间都是一样的那就会乱序。 5 个商品的顺序是不一定的堆排序不稳定排序当我们从第一页到第二页时商品 3又到了第一页。
那么我们就一直找不到商品 3。对于这个问题我们可以改成按照主键 id 排序。
4.2 深度分页问题
SELECT * FROM goods ORDER BY price LIMIT 80000,10这样的 sql 就是深度分页了我们之前讲到MySQL 的底层会查询出 80010 条数据进行文件排序因为查询数量太多回表次数过多MySQL 便不使用二级索引然后再截取第 80001 到 第80010 条数据返回给客户端。
要解决这种深度分页问题首先应该在产品的设计方面避免这种情况还有就是我们在查询分页数据时应该需要根据时间做好限制减少数据以及对前端传进来的 start、limit 字段进行判断限制。如果还是需要深度分页就需要利用子查询来实现。
SELECT * FROM goods g
INNER JOIN
(SELECT id FROM goods ORDER BY price LIMIT 80000,10) AS d
ON g.idd.id;子查询使用二级索引查出满足条件的主键然后进行分页过滤出我们需要主键 id再去主键索引查询数据因为排序字段就是我们的二级索引字段所以查询出来的数据直接就是有序的无需再进行文件排序。
