网站解析,本地网站开发公司,瓷砖 中企动力 网站建设,网站后台是什么在实际的数据库应用场景中#xff0c;我们常常会遇到需要进行分页查询的需求。对于少量数据的分页查询#xff0c;MySQL 可以轻松应对。然而#xff0c;当我们需要进行深度分页#xff08;即从大量数据的中间位置开始获取少量数据#xff09;时#xff0c;就会面临性能严…在实际的数据库应用场景中我们常常会遇到需要进行分页查询的需求。对于少量数据的分页查询MySQL 可以轻松应对。然而当我们需要进行深度分页即从大量数据的中间位置开始获取少量数据时就会面临性能严重下降的问题。本文将深入探讨 MySQL 深度分页的问题并介绍子查询和滚动 ID 这两种有效的解决方式。
深度分页问题背景
深度分页通常是指使用 LIMIT m, n 语句进行查询其中 m 是偏移量n 是要返回的记录数。当 m 的值非常大时就会出现深度分页的情况。例如
select * from user where sex女 order by age limit 100000000, 10;这条 SQL 语句的目的是从 user 表中筛选出性别为女的记录按照年龄排序然后跳过前面的 100000000 条记录返回接下来的 10 条记录。
深度分页性能问题原因
LIMIT 主要在 Server 层执行而不是存储引擎层。在 Server 层执行器调用存储引擎 API 获取数据时会先获取到全部符合条件的数据然后在发送给客户端的时候才会进行 LIMIT 操作。具体来说Server 层在执行上述 SQL 时每获取到一条数据会查看该数据是否是第 100000001 条数据如果不是就不会发送到客户端只进行 limit_count 计数直到获取到第 100000001 条数据才开始发送到客户端。
此外如果查询的字段需要回表扫描每一次查询都会拿着 age 列的二级索引查到的主键值去回表。对于 LIMIT 10000000, 10 这样的查询就会回表 10000000 次效率极低。我们可以使用 EXPLAIN 查看查询计划来进一步分析
explain select * from user where sex 女 order by age limit 10000000, 10;会发现这条记录通常不会走age索引,而是选择全表扫描文件排序,这是因为优化器认为选择age的效率甚至比不上全表扫描文件排序 所以当翻页靠后时,查询会变得很慢,因为随着偏移量的增加,我们需要排序和扫描的非目标行数据也会越来越多,这些数据再扫描后都会被丢弃。
解决深度分页问题的方法
子查询优化
子查询优化的核心思想是先在二级索引上获取需要的主键 ID然后再根据这些 ID 去主键索引中获取完整的数据避免了直接在主键索引上进行大量的偏移操作。
示例代码如下
SELECT *
FROM USER
WHERE sex 女 AND id (SELECT id FROM USER WHERE sex 女 ORDER BY age LIMIT 10000000,1)
ORDER BY age LIMIT 10;在这个查询中子查询 SELECT id FROM user WHERE sex ‘女’ ORDER BY age LIMIT 10000000, 1 会在 age 列的二级索引上操作由于二级索引只包含索引列和主键值数据量相对较小跳过 10000000 条记录的开销也会小很多。主查询再根据子查询返回的 id 在主键索引上定位到起始位置获取接下来的 10 条记录减少了扫描的数据量和回表次数从而提高了查询性能。
滚动 ID 方式
滚动 ID 方式不支持跳页但可以在顺序分页的场景下有效解决深度分页问题。其原理是记录上一页的最后一条记录的 ID然后在查询下一页时只查询 ID 大于该记录 ID 的数据。
示例代码如下
假设上一页的最后一条记录的 ID 是 last_id
SELECT * FROM user WHERE sex 女 AND id last_id ORDER BY age LIMIT 10;在实际应用中当用户请求第一页数据时正常执行查询
SELECT * FROM user WHERE sex 女 ORDER BY age LIMIT 10;记录下这一页的最后一条记录的 ID当用户请求下一页数据时使用上述滚动 ID 的查询语句。这种方式避免了大量的偏移操作每次查询只需要从上次记录的 ID 之后开始扫描大大提高了查询效率。
总结
深度分页是 MySQL 中一个常见且棘手的问题使用子查询和滚动 ID 这两种方式可以在不同的场景下有效解决该问题。子查询适用于需要跳页的场景通过在二级索引上操作减少回表次数和扫描数据量滚动 ID 方式适用于顺序分页的场景通过记录上一页的最后一条记录的 ID 避免大量的偏移操作。在实际应用中我们需要根据具体的业务需求选择合适的解决方案。
