mysql limit 分页查询调优

admin 2024-06-11 216 阅读 0评论

我们以一个非聚簇索引为例,来分析一下 Limit 的影响。假设我们有一张表 test ,它有两个字段 id 和 val ,其中 id 是主键,val 是非唯一非聚簇索引。表中有 500 万条数据,val 的值从 1 到 10 随机分布。我们执行以下语句:


select * from test where val=4 limit 300000,5;


这条语句的意思是查询 val 等于 4 的记录,并返回第 300001 到第 300005 条记录。Mysql 会怎么执行呢?


首先,Mysql 会选择 val 索引作为执行计划,因为它可以缩小查询范围。然后,Mysql 会从 val 索引的根节点开始查找,沿着 B+ 树向下搜索,直到找到第一个 val 等于 4 的叶子节点。接着,Mysql 会沿着叶子节点的指针向右移动,扫描所有 val 等于 4 的叶子节点,并记录它们对应的 id 值和数据记录地址。


由于我们要返回第 300001 到第 300005 条记录,所以 Mysql 必须扫描至少 300005 个叶子节点,才能确定哪些是我们需要的。这就导致了大量的随机 I/O 操作,在磁盘上读取索引页。

接下来,Mysql 还要根据叶子节点指向的数据记录地址,去访问数据页,获取查询所需的所有字段。由于我们要返回所有字段(select *),所以 Mysql 必须访问至少 300005 次数据页,才能获取到完整的数据记录。这又导致了大量的随机 I/O 操作,在磁盘上读取数据页。

最后,Mysql 还要对扫描到的数据记录进行排序和过滤,抛弃前面 300000 条无用的记录,只保留后面 5 条有用的记录。这就导致了大量的 CPU 和内存消耗,在内存中进行排序和过滤。

综上所述,Mysql 在执行这条语句时,需要做以下操作:

  • 扫描至少 300005 个索引页
  • 访问至少 300005 次数据页
  • 排序和过滤至少 300005 条数据记录


这些操作都是非常耗时和耗资源和时间的浪费。为了返回 5 条有用的记录,Mysql 不得不扫描和访问大量的无用的记录。这就是 Limit 会影响性能的原因。

那么,有没有办法优化这个问题呢?


答案是:有,但是需要根据具体的情况来选择合适的方法。下面,我们介绍几种常见的优化方法:

  • 使用索引覆盖扫描。如果我们只需要查询部分字段,而不是所有字段,我们可以尝试使用索引覆盖扫描,也就是让查询所需的所有字段都在索引中,这样就不需要再访问数据页,减少了随机 I/O 操作。例如,如果我们只需要查询 id 和 val 字段,我们可以执行以下语句:


select id,val from test where val=4 limit 300000,5;


这样,Mysql 只需要扫描索引页,而不需要访问数据页,提高了查询效率。


  • 使用子查询。如果我们不能使用索引覆盖扫描,或者查询字段较多,我们可以尝试使用子查询,也就是先用一个子查询找出我们需要的记录的 id 值,然后再用一个主查询根据 id 值获取其他字段。例如,我们可以执行以下语句:


select * from test where id in (select id from test where val=4 limit 300000,5);


这样,Mysql 先执行子查询,在 val 索引上进行范围扫描,并返回 5 个 id 值。然后,Mysql 再执行主查询,在 id 索引上进行点查找,并返回所有字段。这样,Mysql 只需要扫描 5 个数据页,而不是 300005 个数据页,提高了查询效率。


  • 使用分区表。如果我们的表非常大,或者数据分布不均匀,我们可以尝试使用分区表,也就是将一张大表分成多个小表,并按照某个字段或者范围进行划分。这样,Mysql 可以根据条件只访问部分分区表,而不是整张表,减少了扫描和访问的数据量。例如,如果我们按照 val 字段将 test 表分成 10 个分区表(test_1 到 test_10),每个分区表只存储 val 等于某个值的记录,我们可以执行以下语句:
select * from test_4 limit 300000,5;


这样,Mysql 只需要访问 test_4 这个分区表,而不需要访问其他分区表,提高了查询效率。


发表评论

快捷回复: 表情:
Addoil Applause Badlaugh Bomb Coffee Fabulous Facepalm Feces Frown Heyha Insidious KeepFighting NoProb PigHead Shocked Sinistersmile Slap Social Sweat Tolaugh Watermelon Witty Wow Yeah Yellowdog
提交
评论列表 (有 0 条评论, 216人围观)