为啥有些limit那么慢？

「爱情、让人受尽委屈。」 2023-05-21 08:44 12阅读 0赞

### 文章目录 ###

*  前言
 *  limit原理
 *  回表
 *  如何避免回表
 *  参考博客

# 前言 #

很多后台会用到分页，我们可以想到pageHelper还有sql的limit。当用户量一大之后，在搜索 后面数据的时候就会特别慢。接下来解密一下原因~

# limit原理 #

比如说limit100,10，它会去查询110条数据，然后返回最后10条数据。当你查询 limit 10000,10，也就是说需要查询10010条数据。我们可以看到越往后需要查询的数据量越来越大的，导致时间越来越长。

**图示**

1.初始时候的数据  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODMzNjY1OA_size_16_color_FFFFFF_t_70]  
2.limit查询数据  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODMzNjY1OA_size_16_color_FFFFFF_t_70 1]  
就是扫描所有的数据，在索引找到符合的数据，然后再去查询table里头查询索引对应的数据。这个过程叫回表。

# 回表 #

当使用普通索引的时候，它会先在索引那查询所有符合条件的索引，然后再回到表中根据索引查询对应的数据出来。所以这里有两次查询表的过程，简称回表。

# 如何避免回表 #

**原来sql**

SELECT id from activity_user_info LIMIT 200000,20;

**sql**

SELECT a1.* from activity_user_info a1 RIGHT JOIN (
    SELECT id from activity_user_info LIMIT 200000,20
    ) a2 on  a1.id=a2.id

**我们来explain一下**

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODMzNjY1OA_size_16_color_FFFFFF_t_70 2]  
我们可以看到用了索引，查了336778多行。

**再看看优化后的sql**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODMzNjY1OA_size_16_color_FFFFFF_t_70 3]

怎么理解呢？一开始是内部查询，就是select id，命中主键索引，然后联表去查表对应索引的数据，返回数据。  
这里应该不叫避免回表，应该是避免回表查询很多数据，而且命中主键聚集索引，所以说第一步蛮快的。

在第一次没有优化的sql的时候，它会加载200020行数据到buffer pool中，但是优化之后的sql只会加载20行到buffer pool，mysql底层我不太熟悉，但是按照这结果我来大胆的说这个过程：没有优化的是low出200020行数据再进行筛选，第二步优化的sql是筛选完再去low数据到缓存。

# 参考博客 #

*  [一次 SQL 查询优化原理分析][SQL]
 *  [MySQL ORDER BY / LIMIT performance: late row lookups][MySQL ORDER BY _ LIMIT performance_ late row lookups]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODMzNjY1OA_size_16_color_FFFFFF_t_70]: /images/20230521/e3613a4f76fb4491b35c22a873118109.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODMzNjY1OA_size_16_color_FFFFFF_t_70 1]: /images/20230521/7501eaaace8e44f3ac241e9884672c07.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODMzNjY1OA_size_16_color_FFFFFF_t_70 2]: /images/20230521/fb710020588c4f52b17d2922478f2c7c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODMzNjY1OA_size_16_color_FFFFFF_t_70 3]: /images/20230521/122852c5c457445cb2e2ca58bb09925d.png
[SQL]: https://mp.weixin.qq.com/s?__biz=MzAxNjk4ODE4OQ==&mid=2247488658&idx=1&sn=f4074f705ed1f71adc85dfe3b69ebcc0&chksm=9bed35e0ac9abcf64570f66d76bbc23632b2ec8269902e907c3804b92f0893678f5e6eb98e46&mpshare=1&scene=1&srcid=&sharer_sharetime=1587042300352&sharer_shareid=4dd79bc68128b1d1eb4483208fe94733#rd
[MySQL ORDER BY _ LIMIT performance_ late row lookups]: https://explainextended.com/2009/10/23/mysql-order-by-limit-performance-late-row-lookups/