[db] limit

[Alice52]

• 案例 1
• 案例 2
• Alice52/database#24

limit large

1. use right join
2. use sub-select with > rather than use limit directly

impact: 大于 10w+则会有显著的性能问题

1. pool buffer 污染
2. 慢查询, 消耗资源

solution: 避免回表

1. 延迟关联
2. 子查询 -- join
3. 可以将 id 传过来

4. 业务允许的情况下不显示分页, 或者最多显示 100 页

   -- 慢 & 加载了很多热点不是很高的数据页到buffer pool, 会造成buffer pool的污染, 占用buffer pool的空间
   SELECT id, title, ico, create_time, create_user
   FROM article_copy1
   where `status` = '1'
   ORDER BY id DES LIMIT 100000, 10
   
   -- solution01: 将上一次的最大的id传递给前端
   SELECT id, title, ico, create_time, create_user
   FROM article_copy1
   WHERE `status` = '1' and id < 2155652
   ORDER BY id DESC
   LIMIT 10;
   
   SELECT id, title, ico, create_time, create_user
   FROM article_copy1
   WHERE `status` = '1' and id >= (SELECT id FROM article_copy1  WHERE `status` = '1' ORDER BY id DESC LIMIT 100000, 1)
   ORDER BY id DESC
   LIMIT 10
   
   SELECT id, title, ico, create_time, create_user
   FROM article_copy1 c1
   join (SELECT id FROM article_copy1  WHERE `status` = '1' ORDER BY id DESC LIMIT 100000, 1) t on t.id=c1.id
   WHERE `status` = '1'
   ORDER BY id DESC
   LIMIT 10