mysql limit 优化

 

关于mysql limit查询优化思路

 

1. 思路一：先全部走索引（避免其它数据读取，或者此时索引都在内存中），拿到主键，再查一遍

• 场景：
  1.二级索引 KEY `ind_wlb_ext_order_item_itemid` (`seller_id`,`item_id`,`status`)
  2.seller_id = 2023990209, 有42000条数据
• 执行
  – SELECT * FROM wlb_ext_order_item where seller_id = 2023990209 limit 100, 1000;     // 0.046ms
  – SELECT * FROM wlb_ext_order_item where seller_id = 2023990209 limit 1000, 1000;   // 0.063ms
  – SELECT * FROM wlb_ext_order_item where seller_id = 2023990209 limit 40000, 1000; // 0.279ms
• 优化
  – SELECT * from wlb_ext_order_item m, (SELECT id from wlb_ext_order_item where seller_id = 2023990209 limit 40000, 1000) t WHERE m.id = t.id;  // 0.062ms

explain查看执行计划：
1.SELECT * FROM wlb_ext_order_item where seller_id = 2023990209 limit 40000, 1000;   // 0.279ms

 

2.SELECT * from wlb_ext_order_item m, (SELECT id from wlb_ext_order_item where seller_id = 2023990209 limit 40000, 1000) t WHERE m.id = t.id;  // 0.062ms

 

 

 

 

 

2. 思路二：利用主键自身的顺序特性

http://www.fuchaoqun.com/2009/04/efficient-pagination-using-mysql/